The final project is to design a database for Sacred Hearts Personnel (Human Resources) Department. The database handles the staffing of Academic program. The design needs to be close to the structure of Sacred Hearts operation, but does not need to be factual. The primary purpose is to work through the design guide line presented for Relational database, starting with business rules.
The starting point of investigation is the academic section of Sacred Hearts website, you will be the principle designer and interpret the needs and the business rules of the operation. Please include the individual departments, colleges and US campuses in your design. Examples of employees may be taken from the book and modified to include Sacred Hearts Academic program.
The work will be submitted in two parts.
An ERD diagram to indicate the design of the Database and checked against the normalization rules in chapter 6. In order to present a simple and manageable design, one to one relationship should be checked as whether they can be combined into a single entity. Many to Many relationships cannot be implemented in Relational Database and needs to be converted into one to many relationships. See example on page 136 figure 4.24 Student and class relationship for creating a composite entity. The diagram should be as complete as the examples in chapter 5, include PK, FK and attribute names.
A script to create and insert values into tables. An example of a script can be found on Blackboard under information section(create.sql, midterm.sql). Various constraints should be applied as appropriate to ensure integrity. At least three rows should be inserted, you may encounter rejection during insertion. E.g. Insert a foreign key before the other table is populated. During grading, the script will be checked against the ERD diagram, e.g. Not null should be applied to the FK of a relationship with minimum of at least one. Incorrect data will also be inserted to check against the proper application of constraints.
To guard against interference from the other work that you do on 11g, create a separate tablespace. Multiple tablespace can also be created for individual user if a computer is shared.
The following describe the commands used in the next page, these commands are used by Database Adminstrators to create accounts.
Create tablespace creates a new tablespace that is independent of the others, duplicate table name on other tablespace have no conflict here.
Grant connect create a new user hr (Human Resources), password is also hr.
Alter user default tablespace specifies the tablespace for the user, the tablespace should be for one project only.
Alter user account unlock, unlocks the account. The account hr was lock during other alter user commands and needs to be unlocked.
The following is an example, you may have to create a folder of your own first, the dbf file will be created by Oracle.
SQL> connect system/< your password>
SQL> create tablespace shu datafile 'c:userschandataspaceshu.dbf' size 10m;
SQL> grant connect, resource to hr identified by hr;
SQL> alter user hr default tablespace shu;
SQL> alter user hr account unlock;
SQL> connect hr/hr
To remove the user and the tablespace, use the following commands.
SQL> connect system/< your password>
SQL> revoke connect, resource from hr;
SQL> drop tablespace shu;