For each of the following, explain why or why not a relation is in a particular

Question

For each of the following, explain why or why not a relation is in a particular normal form, underline primary keys and indicate which fields are foreign keys and which relation they are keys into.

1.PetStore(storeBranchName, storeAddr, storeManager,(customerName, customerAddr, customerPhone,(petName, petBreed, petSex, price) ) )

FDs

storeBranchName storeAddr, storeManager customerName customerAddr, customerPhone customerName, petName petBreed, petSex customerName,storeBranchName petName petBreed price

Is this relation in 1NF? If not, why isn't it? Then put it in 1NF.

Is this relation in 2NF? If not, why isn't it? Then put it in 2NF.

Is this relation in 3NF? If not, why isn't it? Then put it in 3NF.

2.R(A,B,C,D,E) Fds

A A,B,C

C D

A,C E

a. Is this relation in 1NF? Why or why not? If not, put it into 1NF. 2b. Is it now in 2NF? Why or why not? If not, put it into 2NF.

2c. Is it now in 3NF? Why or why not? If not, put it into 2NF.

3.price(product,sName,uPrice,qty,storeLocation,unitWeight)

Fds

product, sname qty sName storeLocation product uprice, unitWeight

Is this relation in 1NF? If not, why isn't it? Then put it in 1NF.

Is this relation in 2NF? If not, why isn't it? Then put it in 2NF.

c.Is this relation in 3NF? If not, why isn't it? Then put it in 3NF.

4.person(personId, name, country,(phone, type)) FDs

personId name, country, phone

name country phone type

Is this relation in 1NF? If not, why isn't it? Then put it in 1NF.

Is this relation in 2NF? If not, why isn't it? Then put it in 2NF.

Is this relation in 3NF? If not, why isn't it? Then put it in 3NF.

5.carTheft(VID,vehicleType,(ownerID,ownerName),

(suspectId,suspectName))

FDs

VID vehicleType, ownerID ownerId ownerName suspectID suspectName

Is this relation in 1NF? If not, why isn't it? Then put it in 1NF.

Is this relation in 2NF? If not, why isn't it? Then put it in 2NF.

Is this relation in 3NF? If not, why isn't it? Then put it in 3NF.

Explanation / Answer

Petstore(storeBranchName, storeAddr, storeManager,(customerName, customerAddr, customerPhone,(petName, petBreed, petSex, price) ) )

This relation is not in 1NF. This is because there are multiple values of customerName, customerAddr, customerPhone,petName, petBreed, petSex, price exists for one storeBranch.

1NF

PetStore(storeBranchName, storeAddr, storeManager,customerName, customerAddr, customerPhone,petName, petBreed, petSex, price)

The relation is not in 2NF as all non key attributes are not fully functionally dependent on primary key.

2NF

Store(storeBranchName, storeAddr, storeManager)

Customer(customerName, customerAddr, customerPhone)

CustomerPet(customerName,petName,petBreed,petSex)

CustomerStorePet(customerName,storeBranchName,petName)

The relation is not in 3NF as there are transitive dependencies between non key attributes petBreed price

S

Store(storeBranchName, storeAddr, storeManager)

Customer(customerName, customerAddr, customerPhone)

CustomerPet(customerName,petName,petBreed,petSex)

CustomerStorePet(customerName,storeBranchName,petName)

PetPrice(petBreed,price)

underlined attributes are primary keys and Italicised are foreign keys. Some attributes are both.

2

a. Yes, the relation is in 1NF as all attributes are atomic.

b. No, the relation is not in 2NF as all non primary keys are not fully functionally dependent on primary key A.

2NF

R1(A,B,C)

R2(C,D)

R3(A,C,E)

underlined are primary keys and Italicised are foreign keys. Some attributes are both.

c. Yes, the relations are now in 3NF as there are no transitive dependencies.

3.

a. Yes, the relation is in 1NF as there are no non atomic attributes.

b. No, the relation is not in 2NF as all non primary keys are not fully functionally dependent on primary key product.

2NF

ProductStore(product,sName,qty)

Store(sName,storeLocation)

Product(product,uprice,unitWeight)

c. Yes, the relations are in 3NF now.

4.

4.person(personId, name, country,(phone, type)) FDs

personId name, country, phone

name country phone type

The relation is not in 1NF as phone and type attributes are repeating .

1NF

person(personId, name, country,phone)

No, the relation is not in 2NF as all non primary keys are not fully functionally dependent on primary key product.

2NF

Person(personId,name,country,phone)

Name(name,country)

Phone(phone,type)

underlined are primary keys and Italicised are foreign keys. Some attributes are both.

c. Yes, the relations are now in 3NF as there are no transitive dependencies.

5.

carTheft(VID,vehicleType,(ownerID,ownerName),(suspectId,suspectName))

FDs

VID vehicleType, ownerID ownerId ownerName suspectID suspectName

The relation is not in 1NF as ownerID,ownerName,suspectId,sustepectName attributes are repeating .

1NF

carTheft(VID,vehicleType,ownerID,ownerName,suspectId,suspectName)

b. No, the relation is not in 2NF as all non primary keys are not fully functionally dependent on primary key product.

2NF

Vehicle(VID , vehicleType)

Owner(VID,ownerID ,ownerName)

Suspect(VID,suspectID , suspectName)

underlined are primary keys and Italicised are foreign keys. Some attributes are both.

c. Yes, the relations are now in 3NF as there are no transitive dependencies.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.