Question

Task 01: There is a real estate information company that keeps track of all of properties sold in the county of San Francisco and the buyers of each property. They have two tables that have the following structure: PROPERTY(PropNum, type, price) Primary Key: PropNum BUYER(BuyerNum, name, address) Primary key: Buyer Num Note that there is currently no relationship between the tables. Here is the data (see below). PROPERTY PropNum type price (121-33-456 Residential 999856 156-22-556 Residential 899655 111-33-444 Commercial 1233555 BUYER BuyerNum Name Address 4455 Amy McIntosh 123 Giradelli Rd 4456 M. S. Nguyen 444 Golden Gate Cir 6663 Kim Linus 11143 Lumbard Ave 7771 Tux Tovalds 33 49th St. A county clerk needs to be able to find which buyer is buying which property, so that clerk creates a relationship between the two tables, like this:

Where is the redundancy in the data in these tables?

Provide what you consider to be the best database design for the data provided and explain why it is the best design.
Hint: Once you load your design with data verify that the primary key contraint is enforced.

Answer 1

There is redundancy in property table. For one PropNum there are two Buyernum and two propNmae have same buyer.And Property table do not have a primary key(It has composite key)

So in order to remove redundany we have to join the two tables(property and Buyer)by creating a third table.

Table1

Property(PropName,Type,Price)

Primary key-PropNum

Table2

Buyer(BuyerNum,Name,Address)

Primary key-BuyerNum

Table3

PropertyBuyer(PropNum,BuyerNum)

Foreign Key-PropNum

PropNum	BuyerNum
121-33-456	4456
156-22-556	4455
156-22-556	6663
111-33-444	6663
999-00-344	7771

Now there is no redundancy and any functional dependencies .So this is the best database design.