Homework 5
CS122, Spring 2004
Due: Wednesday, June 2
Please send the solutions to csun@calstatela.edu
by email. Make sure to use CS122
Spring 04 HW5 as the subject of the email, and include in the
email
your name so I don't have to guess names from
email addresses.
0. Read Chapter 0, and 11 of the textbook.
1. Consider the following table:
EMPID
|
LAST
|
FIRST
|
DEPTID
|
DEPTNAME
|
DEPENDENTNO
|
DEPENDENTSSN
|
DEPENDENTDOB
|
where
- EMPID -- employee's ID
- LAST -- employee's last name
- FIRST -- employee's first name
- DEPTID -- employee's department number
- DEPTNAME -- employee's department name
- DEPENDENTNO -- employee's number of dependents
- DEPENDENTSSN -- dependent's Social Security number
- DEPENDENTDOB -- dependent's date of birth
(a) (5pt) What's the primary key of the table?
(b) (15pt) Normalize the table to 3NF. The answer to this question should be in the form:
table_name: <field_name, ...>
...
(c) (5pt) Explain whether your 3NF design is also BCNF.
(d) (15pt) Write the CREATE TABLE statements for the tables in (b) with proper primary key and foreign key constraints.