Question

PART A: The schema for the Jefferson Dance Company is shown below.

CUSTOMER (CUSTID, NAME, ZIP, BALANCE)

DANCESTEP (STEPID, NAME)

GLESSONS_TAKEN (GLESSONID, CUSTID)

GROUP_LESSON (GLESSONID, INSTID, FEE, DATE, TIME, STEPID)

INSTRUCTOR (INSTID, NAME, ZIP)

MASTERED_STEPS (INSTID, STEPID)

PRIVATE_LESSON (PLESSONID, CUSTID, INSTID, FEE, DATE, TIME, STEPID)

Normalize this database using the following example for the CUSTOMER table as a guide.

            Table: CUSTOMER (CUSTID, NAME, ZIP, BALANCE)

            Functional Dependencies:

                        CUSTID ® NAME, ZIP, BALANCE

            Key: CUSTID

Description                                                                                  YES            NO

8 pts    Identify key(s)                                                                  _____            _____

8 pts    Identify functional dependencies                                     _____            _____

PART B: Are the following tables in 3NF? If not, decompose them into 3NF. Attach a separate sheet of paper with answers clearly delineated for each table (include key and dependency information).

1. STUDENT RELATION

Student_ID	Student_Name	Dorm	Room&Board_Fee
100	Boyce	Randolph	1200
150	Codd	Ingersoll	1100
200	Elmasri	Randolph	1200
250	Navathe	Pitkin	1100
300	Date	Randolph	1200

Primary key: Student ID

Candidate key: Student Name

Business rules: The room and board fee is the same for everyone living in a specific dorm, but different dorms have different fees.

Description                                                                                  YES            NO

2 pts    Identify key(s)                                                                  _____            _____

2 pts    Identify functional dependencies                                     _____            _____

2. EMPLOYEE RELATION

EMP_ID	EMP_NAME	COURSE#	FEE
E100	McFadden	C100	10
E100	McFadden	C220	15
E200	Hoffer	C220	20
E300	Prescott	C100	15
E300	Prescott	C330	25

Primary key: EMP_ID/COURSE#

Description                                                                                  YES            NO

2 pts    Identify key(s)                                                                  _____            _____

2 pts    Identify functional dependencies                                     _____            _____

3. MOVIE RELATION

Title	Year	Length	Studio Name	Star Name
Spider-man	2002	121	Columbia	Tobey Maguire
Casablanca	1942	102	Warner Bros	Ingrid Bergman
Star Wars	1977	124	Fox	Carrie Fisher
Star Wars	1977	124	Fox	Mark Hamill
Star Wars	1977	124	Fox	Harrison Ford
Man of Steel	2013	143	Warner Bros	Amy Adams
Great Gatsby	1974	144	Paramount	Robert Redford
Great Gatsby	2013	143	Warner Bros	Leonardo DiCaprio
Spider-man	2012	136	Columbia	Andrew Garfield

Primary key: Title, Year, Star Name

Business rules: Movie titles are unique, unless they are remakes of the original movie.

Description                                                                                  YES            NO

2 pts    Identify key(s)                                                                  _____            _____

2 pts    Identify functional dependencies                                     _____            _____

Answer 1

PART A:

CUSTOMER (CUSTID, NAME, ZIP, BALANCE)

Table: CUSTOMER (CUSTID, NAME, ZIP, BALANCE)

            Functional Dependencies:

                        CUSTID ® NAME, ZIP, BALANCE

            Key: CUSTID

Table : DANCESTEP (STEPID, NAME)

Functional Dependencies:

STEPID ® NAME

            Key: STEPID

GLESSONS_TAKEN (GLESSONID, CUSTID)

Functional Dependencies:

GLESSONID ® CUSTID

CUSTID ® GLESSONID

            Key: GLESSONID, CUSTID

GROUP_LESSON (GLESSONID, INSTID, FEE, DATE, TIME, STEPID)

Functional Dependencies:

                        GLESSONID, INSTID ® FEE, DATE, TIME, STEPID

            Key: GLESSONID, INSTID

INSTRUCTOR (INSTID, NAME, ZIP)

Functional Dependencies:

INSTID ® NAME, ZIP

            Key:INSTID

MASTERED_STEPS (INSTID, STEPID)

Functional Dependencies:

INSTID ® STEPID

STEPID ® INSTID

            Key:INSTID, STEPID

PRIVATE_LESSON (PLESSONID, CUSTID, INSTID, FEE, DATE, TIME, STEPID)

Functional Dependencies:

LESSONID, CUSTID, INSTID ® FEE, DATE, TIME, STEPID

            Key: LESSONID, CUSTID, INSTID

PART B:

1. STUDENT RELATION

Not in 3NF

Transitive Dependency exists:

Dorm -> Room&Board_Fee

Relations in 3NF

Student(Student_ID, Student_Name, Dorm)

Dorm(Dorm, Room&Board_Fee)

underlined are primary keys and italicised are foreign keys

2. EMPLOYEE RELATION

Yes the table is in 3NF

Keys: EMP_ID,COURSE# Functional Dependency EMP_ID,COURSE# -> EMP_Name,Fee

3. MOVIE RELATION

Yes the table is in 3NF

Keys : Title, Year, Star Name

Functional dependency

Title, Year, Star Name -> Length, StudioName

Do ask if any doubt. Please upvote.