Oracle Project (Part 2)



Oracle Project (Part 2)

Deadline: 15/12/2010

For your college database created in part 1

Create a user college_admin who is the administrator for the database.

Recreate all objects in the college_admin schema.

Create new table with the following columns

Table: departments_heads

|Column |Type |Null? |Constraint |

|Head_ID |Number |No |Foreign key |

|Head_Assisst_ID |Number |No |Foreign key |

|Department |Varchar(30) |No |Primary key |

Create a new column in the teachers schema called Head_ID for each teacher in the teacher table.

✓ Allow any department head to :

insert in teacher table his teachers only.

insert in courses table the courses that belongs to his department.

Select the data from the teacher_course_students view.

✓ Allow any head assistant to :

change the teacher and course description for any course in the courses table

that belongs to his department.

✓ Allow any teacher to :

enter the grade of any student in teacher_course_student view.

✓ Allow any students to :

only view his data from student_courses.

Hints :

❖ For each teacher user :

user name is T followed by the id.

Password is the teacher First Name.

❖ Each Student user :

user name is S followed by the id.

Password is the Student First Name.

❖ To grant a dba privilege to user use the statement:

Grant dba to user_name.

(Note this grant must be done from user system " college_admin").

❖ To allow college_admin to create tables use the statement:

Create user user_name identified by password quota unlimited on users.

❖ To retrieve the user name that you entered from you can use the keyword user.

For example enter to oracle database from user HR then execute the statement

Select user from dual;

You will observe that the returned value is HR

Use This Sample Database:

Teachers Table:

|ID |First Name |Last Name |Head_ID |

|1 |Ahmad |Saleem |6 |

|2 |Mahmoud |Ali |6 |

|3 |Fouad |Kamel |6 |

|4 |Waleed |Soliman |6 |

|5 |Ali |Wesam |6 |

|6 |Ahmad |Waleed |null |

Courses Table:

|Course_ID |Course Name |Course Year |Number of hours |Description |Teacher ID |

|10 |Programming |2 |3 |Study Programming |1 |

| | | | |concepts: data types, | |

| | | | |arrays, methods | |

|10 |Programming lab |2 |1 |Practices the concepts|2 |

| | | | |in programming course | |

|12 |Algorithms |3 |3 |Study how to design an|3 |

| | | | |algorithm | |

|12 |Algorithms Discussion |3 |0 | |5 |

|14 |Database |4 |3 |Study Basic concepts |6 |

| | | | |of database design | |

|14 |Database lab |4 |1 |Study how to write SQL|2 |

|14 |Database discussion |4 |0 | |2 |

|15 |Advance Database |5 |4 |Advance topics in |4 |

| | | | |database | |

Students Table:

|ID |First Name |Last Name |Course Id |Course Year |Number of hours |grade |

|1 |Ahmad |Fahd |14 |4 |3 |87 |

|2 |Kmal |Raed |15 |5 |4 |92 |

|3 |Wesam |Ahmad |12 |3 |3 |84 |

|4 |Ali |Ismaeel |10 |2 |3 |85 |

|5 |Ahmad |Fahd |14 |4 |1 |87 |

|6 |Ahmad |Fahd |14 |4 |0 | |

|7 |Ali |Ismaeel |10 |2 |1 |88 |

|8 |Wesam |Ahmad |12 |3 |1 | |

Department_heads

|Head_ID |Head_Assisst_ID |Department |

|6 |4 |Compute |

Important Notes:

1- Group of two students will be allowed (Maximum).

2- Copy projects will be graded to zero.

3- Each Project must be well commented on the report.

4- All SQL Statements you use must be added to the report.

5- Each Project will be discussed.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download