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.

Ace your studies with our custom writing services! We've got your back for top grades and timely submissions, so you can say goodbye to the stress. Trust us to get you there!


Order a Similar Paper Order a Different Paper

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)

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.
CS631.101 (Fall 2021) Assignment 2 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) 5

Writerbay.net

Looking for top-notch essay writing services? We've got you covered! Connect with our writing experts today. Placing your order is easy, taking less than 5 minutes. Click below to get started.


Order a Similar Paper Order a Different Paper