Submission Requirements:

⦁ This assignment is due Wednesday November 1, 2021 at 6pm.

⦁ The late penalty is 10 points per day.

⦁ It must be submitted via Canvas.

⦁ Submit one document only, unzipped.

⦁ Handwritten problems will not be accepted unless permission is granted by ME.

⦁ Do not use SQL or any other DB language. It is not needed and will be ignored.

All answers must be explained.

Problem 1 (25 points):

Given Relation J = {Q,R,S,T,U,V,W,X,Y,Z}

and given these functional dependencies:

{W}  {Q,R}

{T}  {V}

{S}  {X}

{U,W}  {Z}

(U,Y}  {Q,S,T}

⦁ Determine the Primary Key of J.

⦁ Is {U, W, S, T } a key? Explain your answer.

⦁ Is this table in first normal form?

⦁ Is the table below in Second Normal Form? Explain. The primary key is underlined.

{U,W Z,Q,R}

⦁ Is the table below in Third Normal Form? Explain. The primary key is underlined.

{Y, U, X, S, T]

Problem 2: (15 points)

Given the Relation M = {D,E,F,G,H} with the following dependencies:

{D,E}G

{F,G}H

{G,H}D

⦁ Is {D,E} a candidate key of M? Explain

⦁ Is {D,E,F} a candidate key of M? Explain

⦁ Is {D,E,F,H} a candidate key of M? Explain

Problem 3 (20 points):

Given the following table defining Relation F:

SID Fred Barney Wilma

885 ZZT YA 13

896 141 YA 88

907 CI7 DA 66

118 LBJ NO 22

129 ZZT YA 44

340 CI7 DA 44

How would you characterize the existence of the  Functional Dependencies for each relationship listed below (a-e).  Explain why in each case.

⦁ SID  Barney

⦁ Barney  Wilma

⦁ Wilma  SID

⦁ Fred  Wilma

⦁ Fred  Barney

⦁ Explain why {SID, Wilma} is not a candidate key for the table presented.

Problem 4 (10 points):

You are given a table of union member dependents. The table has attributes union member ID (UID), dependent first name (DFN), dependent last name (DLN), dependent phone number(DPH), and dependent sex(DSX). Assume each union member has a unique identifier (UID) and only one phone number on record. Also, assume each union member can have multiple dependents, but those dependents first name will always be unique per union member(but not necessarily unique across the whole table, i.e. No union member will have two dependents with the same first name but two union members may have dependents named John).

What would the primary key be? Design the table accordingly, but you may not create any new columns. Show the attributes and a few sample records (enough to make your thinking clear).

Problem 5 (10 points):

Similar to Problem 4 but not identical.

You are given a table of musician’s dependents with attributes: Musician ID(MID), dependent first name (DFN), dependent last name (DLN), dependent phone number (DPH), and dependent sex (DSX).Assume each musician has a unique musician identifier (MID) and only one phone number listed. Also, assume each musician can have multiple dependents, but those dependents first name may or may not be unique per musician.

What would the primary key be? Design the table accordingly, but in this problem you may create new columns. You may not use SSN or a surrogate key as a primary key. Show the attributes and a few sample records (enough to make your thinking clear).

Problem 6 (10 points):

Using the Employee table in the company database below, identify the single valued Candidate Keys if we are told that we cannot use SSN AND if we know the table will never change. Use the employee table attached at the end of the assignment. Do not worry about referential integrity violations created by removing SSN.

Problem 7 (10 points): Assume that you are the data base administrator of the legal firm Huckster and Finagleman, Inc.  Also assume that the Ambulance Chasing department went bankrupt and is no longer part of the firm. While reviewing the records you come across the Ambulance Chasing department’s pension database table which has the fields shown in the table below. Assume the table is 20 records and will not be used in connection with any other table.

Table: AmbulanceChasing

Attribute Characteristics

Lawyers First Name lawyer’s first name

LawyersLastName (Primary Key) lawyer’s last (family) name

PSSN lawyer’s social security number

Paddress lawyer’s address

PBD lawyer’s birth date

PSal lawyer’s salary

Since the department has been terminated the table will NEVER have another record added to it.

During a meeting with your database team, Frazier Crane questioned the uniqueness of the primary key in the table. Daphne Moon explained that when the database was designed in 2015, the department had already been terminated and the table could never have a record added to it. Since the last name was unique and it is illegal to use Social Security Number as the primary key, Daphne’s team agreed that LawyersLastName would suffice as the primary key.

Frazier pointed out that even if there are no records ever added to this table, there is still a problem with Daphne’s logic relating to the using LawyersLastName as the Primary Key.

⦁ What is the problem with the Daphne’s logic? (3 points)

⦁ Create a unique primary key containing the attribute LawyersLastName that follows the definition of the term “key”. You may add attributes to the  table. Be very specific regarding the change you have made.(7 points)