Q1

Suppose you have created the following relational tables for the below case.

User:

UID, name, street, city, zip

Buyer:

UID (refers user), AID (refers Agent), HID (houses being watched by the buyer).

Seller:

UID, (refers user), AID (refers agent), HID (houses listed for sale by this seller), sold,

street, city, zip, listing_date, price, square_footage, year, AppID (appointment ID), BID (ID

of buyer who makes appointment to see the house being listed), time (time of

appointment).

Agent:

UID, (refers user)

User_Phone:

UID, phone

A. For each table , specify whether it is first normal form, whether it is third normal

form, and explain why.

B. Please normalize all tables that are not in the third normal form to the third

normal form.

C. List an example of an Insertion anomaly in the table above.

D. List an example of a Modification anomaly in the table above.

E. List an example of a Deletion anomaly in the table above.

F. Identify the Functional Dependency in the table above.

G. Convert the following table into 3NF.