The purpose of this exercise is to give you an opportunity to create the tables for Bearcat Incorporated. The tables themselves are based on the relations that appear in the following figure.
L1: EMPLOYEE (Emp_fname, Emp_minit, Emp_lname, Emp_nametag, Emp_emp_e#a, Emp_emp_e#n, Emp_address, Emp_salary, Emp_pl_name, Emp_gender, Emp_datehired, Emp_e#a, Emp_e#n)
# EMPLOYEE.{Emp_emp_e#a, Emp_emp_e#n} ⊆ EMPLOYEE.{Emp_e#a, Emp_e#n} or ( EMPLOYEE.{Emp_pl_name} ⊆ PLANT.{Pl_name}
L2: PLANT (Pl_p#, Pl_budget, Pl_name, Pl_emp_e#a, Pl_emp_e#n, Pl_mgrstdte)
# PLANT.{Pl_emp_e#a, Pl_emp_e#n} ⊆ EMPLOYEE.{Emp_e#a, Emp_e#n}
L3: BUILDING (Bld_building, Bld_pl_p#)
# BUILDING.{Bld_pl_p#} ⊆ PLANT.{Pl_p#}
L4: PROJECT (Prj_name, Prj_location, Prj_p#, Prj_pl_p#)
# PROJECT.{Prj_pl_p#} ⊆ PLANT.{Pl_p#} or (
L5: ASSIGNMENT (Asg_prj_p#, Asg_emp_e#a, Asg_emp_e#n, Asg_hrs)
# ASSIGNMENT.{Asg_prj_p#} ⊆ PROJECT.{Prj_p#}
ASSIGNMENT.{Asg_emp_e#a, Asg_emp_e#n} ⊆ EMPLOYEE.{Emp_e#a, Emp_e#n}
L6: DEPENDENT (Dep_sex, Dep_brthdte, Dep_name, Dep_relhow, Dep_emp_e#a, Dep_emp_e#n)
# DEPENDENT.{Dep_emp_e#a, Dep_emp_e#n} ⊆ EMPLOYEE.{Emp_e#a, Emp_e#n} L7: BCU_ACCOUNT (Bcu_dep_name, Bcu_dep_relhow, Bcu_dep_emp_e#a, Bcu_dep_emp_e#n, Bcu_acct_type, Bcu_acct#, Bcu-balance, Bk_emp_e#a, Bcu_emp_e#n)
# BCU_AsdCCOUNT.{Bcu_emp_e#a, Bcu_emp_e#n} ⊆ EMPLOYEE.{Emp_e#a, Emp_e#n} or (
BCU_ACCOUNT.{Bcu_dep_name, Bcu_dep_relhow, Bcu_dep_emp_e#a, Bcu_dep_emp_e#n} ⊆ DEPENDENT.{ Dep_name, Dep_relhow, Dep_emp_e#a, Dep_emp_e#n} or
L8: PARTICIPATION (Par_dep_name, Par_dep_relhow, Par_dep_emp_e#a, Par_dep_emp_e#n, Par_hob_name, Par_anncost, Par_hrsweek)
# PARTICIPATION.{Par_hob_name}⊆ HOBBY.{Hob_name} PARTICIPATION.{Par_dep_name, Par_dep_relhow, Par_dep_emp_e#a, Par_dep_emp_e#n}⊆ DEPENDENT.{ Dep_name, Dep_relhow, Dep_emp_e#a, Dep_emp_e#n}
L9: HOBBY (Hob_name, Hob_Ioact, Hob_giact)
In addition to the primary key constraints shown in the figure, these tables contain the following constraints (i.e., business rules).
PLANT Table
• No two plants can have the same name.
• Plant numbers are allowed to range between 10 and 20 inclusive.
EMPLOYEE Table
• Each employee must have a first name and a last name.
• Employee salaries can range between $35,000 and $90,000 inclusive.
• Valid genders are 'M' and 'F'.
• Each employee must work in an existing plant.
• The supervisor of an employee must be an existing employee.
• No two employees can have the same first name, middle initial, last name, and nametag combination.
BUILDING Table
• Each building must be part of an existing plant.
PROJECT Table
• Projects are located in the following cities (Bellaire, Blue Ash, Mason, Stafford, and Sugarland).
• Each project must be associated with an existing plant.
• Project numbers range from 1 to 40 inclusive.
ASSIGNMENT Table
• Each assignment must be associated with an existing employee and an existing project.
DEPENDENT Table
• The sex of a dependent can be ('M', 'F', 'm', or 'f').
• A dependent must be a dependent of an existing employee.
• A dependent can be related to an employee in the following ways:
(A dependent can be the employee's spouse.
(A dependent who is a mother or daughter must be a female.
(A dependent who is a father or son must be a male.
BCU_ACCOUNT Table
• A bcu_account can belong to either an employee, a dependent, or (an employee and a dependent).
• Valid account types are 'C', 'S', or 'I'.
HOBBY Table
• Valid values for the indoor/outdoor attribute are 'I' or 'O'.
• Valid values for the group/individual attribute are 'G' or 'I'.
PARTICIPATION Table
• A participation must involve an existing hobby and an existing dependent.
Once the tables have been created, they must be tested to make sure that the table and column definitions allow for entry of only valid data. For example, it should not be possible to insert two plants with the same name. Test data to give the tables you have created a thorough test is stored in the file insertdata.sql1. If you have defined your constraints properly, some of these insert statements will successfully insert a row into a table. On the other hand, some of the insert statements should fail because the data they contain violate one of the constraints associated with the table.
At the end of running the test data through your table definitions, there should be 4 rows in the PLANT table, 7 rows in the BUILDING table, 13 rows in the EMPLOYEE table, 9 rows in the PROJECT table, 7 rows in the ASSIGNMENT table, 6 rows in the DEPENDENT table, 6 rows in the BCU_ACCOUNT table, 7 rows in the HOBBY table, and 4 rows in the PARTICIPATION table.
You need not make any changes to the insertdata.sql file. This file was "seeded" with errors in order to test whether your create table statements handle all of the necessary constraints?