Consider the relation PROPERTY(propertyid,address,city,zip,tax-base,tenant) givi
ID: 3802249 • Letter: C
Question
Consider the relation PROPERTY(propertyid,address,city,zip,tax-base,tenant) giving, for each rental property in a company, the property identifier, the address (street number and name), city, zip code, tax, and tenant name.
The following FDs hold:
propertyid address,city,zip
zip tax-base
The propertyid does not determine tenant because a property can have several tenants.
(a) Give all the keys of PROPERTY.
(b) What normal form is PROPERTY as given?
(c) Decompose PROPERTY into 3NF relations. Make sure the decomposition is lossless.
Explanation / Answer
a) all keys of property is
primary key (property_id, tenant_id)
2) Property is in 2 nf since for a single property there can be mulutple tenant so it is partial depandent on primary key
3) to decompose the property into 3nf
create table property (
address varchar2(100) ,
city varchar2(100),
zip varchar2(100),
property_id number,
primary key(property_id)
)
create table tenant (
rax_base number,
tenant_id number,
tenant_name varchar2(100),
property_id number,
primary key (tenant_id),
foreign key (property_id) refrences property(property_id)
)