Question

you will write your solutions to problems A - G.

You will turn in this assignment by uploading a PDF file that contains your answers.

Using these relations:

CUSTOMER(CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy)

SALE(SaleID, CustomerID, InvoiceDate, PreTaxTotal, Tax, Total)

SALE_ITEM(SaleID, SaleItemID, PurchaseID, SalePrice)

PURCHASE(PurchaseID, PurchaseItem, PurchasePrice, PurchaseDate, VendorID)

VENDOR(VendorID, Vendor, Phone)

A.

1. List all functional dependencies.

2. List any multivalued dependencies.

3. List all candidate keys.

4. List all primary keys.

5. List all foreign keys.

6. State any assumptions you make as you list these components.

B. List questions you would ask the owners of the Queen Anne Curiosity Shop to verify your assumptions.

C. If there were any multivalued dependencies, create the tables needed to eliminate these dependencies.

D. Do these data have the multivlue, multicolumn problem? If so, how would you deal with it?

E. Do these data have the inconsistent data problem? If so, how would you deal with it?

F. Do these data have a null (missing) value problem? If so, how would you deal with it?

G.Do these data have the general--purpose remarks value problem? If so, how would you deal with it?

Answer 1

1)

Functional dependencies

CUSTOMER

CustomerID -> { LastName, FirstName, EmailAddress, Address, ZIP, Phone, ReferredBy }

EmailAddress -> { EncryptedPassword }

Zip -> { City, State }

SALE

SaleID -> { CustomerID, InvoiceDate, PreTaxTotal, Tax, Total }

SALE_ITEM

{ SaleID ,SaleItemID } -> { PurchaseID, SalePrice }

PURCHASE

{ PurchaseID , PurchaseItem } -> { PurchasePrice, PurchaseDate, VendorID }

VENDOR

VendorID -> { Vendor, Phone }

2)

Multivalued dependency

Phone in CUSTOMER table

Phone in VENDOR table

3)

Candidate keys

CUSTOMER

CK = { CustomerID } , { EmailAddress } , {Zip}

SALE

CK = { SaleID }

SALE_ITEM

CK = { SaleID ,SaleItemID }

PURCHASE

CK = { PurchaseID , PurchaseItem }

VENDOR

CK = {VendorID }

4)

Primary keys

CUSTOMER

PK = { CustomerID }  

SALE

PK = { SaleID }

SALE_ITEM

PK = { SaleID ,SaleItemID }

PURCHASE

PK = { PurchaseID , PurchaseItem }

VENDOR

PK = {VendorID }

5)

Foreign keys

SALE

FK = CustomerID,

SALE_ITEM

FK = SaleID , PurchaseID

PURCHASE

FK = VendorID

*************END**************PLS GIVE ME GOOD RATING****************************