Company Database.
Assignment
You need to implement a database that stores information about departments, employees, and projects.
You need to store the following pieces of information.
- About departments:
- department name
- head of the department
- parent department (the department this department is a sub-department)
- possible sub-departments
- location (address)
- number of employees
- About employees:
- SSN
- name
- date of birth
- date when he/she was hired
- gender
- office phone
- department he/she works for
- salary
- bonus
- skills
- hours spent on each project he/she worked on
- About projects:
- project name
- head of the project
- names of employees participating in the project
- number of hours spent by these employees
Create normalized Oracle tables to keep data corresponding to the structure above and don't forget
to put additional restrictions:
- salary should be positive
- an employee cannot be older that 80 and younger that 14
- an employee cannot work for more than one department
- an employee must work for some department
- an employee cannot be hired before he/she was born
- when a department is removed all employers of the department are fired
- head of a department can retire or be fired
- a department cannot have more than one parent department
- each project must have an employee who is the head of this project
- project cannot have more than one head
- an employee can participate in more than one project
Each table you create must be commented in the Oracle dictionary (use the COMMENT ON statement).
Submitting the project
To submit the project you need:
- create sub-directory named ist466 in your home directory (use the
mkdir dirname command to do that and
cd dirname to go to the new directory)
- create a file named project1.sql located in the new directory. This file should
contain all SQL commands you need to execute to complete the assignment
Hint: to execute the command file from SQL*Plus use the @ command. For example,
to execute file test.sql run
SQL> @test
- The commands in the file also has to be commented. Comments in .sql file should start
with the keyword REM (comments end at the end of the line).
Good luck!