You will get errors on the way, but make sure to bypass them
Be ready to research
1. Delete the database on your machine and completely uninstall the oracle software
2. Silently install the oracle software
3. Silently create a database
4. Create two permanent tablespaces with names tb_1 and tb_2 both with size 5m
5. Create a role called baba
6. Grant create session, create table, create view, create index, create sequence, create synonym to baba
7. Create two users with names aku and kafui, make sure aku has 3m on tb_1 and tb_2, whilst kafui has 2m on tb_1 and tb_2
8. Grant baba to aku and kafui
9. Log in as aku
10. Create table department
(
dept_id varchar2(5) CONSTRAINT dept_id_pk PRIMARY KEY,
dept_name varchar2(25),
location varchar2(25) NOT NULL
) tablespace tb_1;
11. Create table job
(
Job_id varchar2(5) job_pk PRIMARY KEY,
Job_name varchar2(25)
) tablespace tb_1;
12. Create table employees
(
Id number(10) id_uk UNIQUE,
name varchar2(30),
dept_id varchar2(5),
jod_id varchar2(5),
salary number(5,2) CHECK > 500,
CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id)
REFERENCES department (dept_id),
CONSTRAINT emp_job_pk FOREIGN KEY (job_id)
REFERENCES job(job_id)
) tablespace tb_1
13. Create view report
As select b.name, a.dept_name, c.job_name , b.salary
From department a, employees b, job c
Where a.dept_id=b.dept_id
and b.job_id=c.job_id;
14. create sequence emp_id_seq
Increment by 10
Start with 10
Maxvalue 9999
Nocache
Nocycle;
15. Create index dept_name_idx on departments (dept_id) tablespace tb_2;
16. Create index emp_name_idx on empoyees (id) tablespace tb_2;
17. Create index job_name_idx on jobs(job_id) tablespace tb_2;
NOTE ALL THE ERRORS RECORDED FROM 15 TO 17
18. Grant select on all the tables belonging to aku to kafui
19. Log in as kafui and create the following objects
20. Create synonym dept for aku.departments;
21. Create synonym emp for aku.employees;
22. Create synonym job for aku.job;
23. Log in as sys and check the objects owned by aku and kafui
select owner,object_name from dba_objects where owner in ('AKU','KAFUI');
24. Log in as aku and check its objects
25. select table_name,tablespace_name,num_rows,blocks,empty_blocks,avg_space from
user_tables;
26. select index_name,table_name,tablespace_name,leaf_blocks,num_rows from
user_indexes;
27. select view_name,text from user_views
28. select synonym_name,table_name,table_owner from user_synonyms;
29. select sequence_name,min_value,max_value from user_sequences;
30. Log in as kafui and repeat step 25 to 29
31. When you log in as aku, you realise that step 26, gave you some indexes you didn't create. Can you explain, why.(NOTE: It has something to do with the constraints)
32. With reference to step 30, those indexes reside in tablespace tb_1, I want you to move all those indexes to tablespace tb_2
33. create a new tablespace tb_3 of size 2m, and move the table employees into it.
34. Pause and drink some ..... and continue
35. Let us check akua's contraints
36. select constraint_name,constraint_type,table_name,search_condition from user_constraints;
37. when u look at the result above, there are two constraints names which is system generated, becos we didn't supply names for those constraints. Those with nice names, we gave it to them.Now kindly change the constraints on employees with salary > 500 to emp_sal_check and the one on department with location IS NOT NULL to dept_loc_null
38. Repeat step 36
39. Log in as aku, and let us work on the sequence
40. select emp_id_seq.currval from dual;
(note the error and research on it)
41. select emp_id_seq.nextval from dual; and repeat step 35
42. let us insert into the table, employees
43. insert into employees values(emp_id_seq.currval,'ELVIS',1,5,500);
(note the error and change just a value above and bypass the constraint violation)
44. re-insert the values again, now you should be getting parent key (department) not found
45. To overcome this error, insert into the department the value 1 plus department name
46. insert into department values(1,'IT','Accra');
commit;
47. Repeat corrected query at step 43 and look at the new error. We need to insert into the job
48. insert into job values (5,'DBA');
commit;
49. Repeat corrected query at step 43. HURRRRRRRRRRRRAY!!!!!!!
50. select * from report;
48. Kindly look
