Normilation Database Question where the primary keys are underlined the foriegn

Question

Normilation Database Question where the primary keys are underlined the foriegn keys are Italics

Normilation process consists of the 1)candidate keys 2) functional dependencys 3) the Referential integrity

This relation is about home appliances, such as refrigerators, stoves, ovens, washers, dryers, etc.

APPLIANCE (ApplianceType, BrandName, ModelNumber, SerialNumber, OwnerName, OwnerAddress, OwnerPhone)

Assumptions:

Each model number is unique to its brand and type. For example, a Kenmore washer could have a model number of KEN-WASH-990.

Serial numbers are just numbers, like 12345678, so could be duplicated.

Owner phone numbers are unique.

State any other assumptions you make.

Relation 2:

This relation is about playgroups for children. Any child can belong to any playgroup. Each playgroup meets at a certain parent’s home. That parent is the playgroup leader.

PLAYGROUP (ParentName, ParentEmail, ParentAddress, ChildName, PlayGroup, GroupLeader)

Assumptions:

Each parent has a unique e-mail address.

Each playgroup has a unique name.

Each playgroup has only one leader.

State any other assumptions you make.

Explanation / Answer

Relation 1:

APPLIANCE (ApplianceType, BrandName, ModelNumber, SerialNumber, OwnerName, OwnerAddress, OwnerPhone)

Functional Depenedency:

ModelNumber -> BrandName,ApplianceType

OwnerPhone -> OwnerName,OwnerAddress

ModelNumber ->-> SerialNumber(multivalue Dependendency)

Assumption : A particular Model Number belongs to an Owner

OwnerPhone -> ModelNumber

Relations in 3NF

APPLIANCE(ModelNumber , BrandName,ApplianceType)

OWNER(OwnerPhone ,OwnerName,OwnerAddress,ModelNumber)

MODELSERIAL(ModelNumber , SerialNumber)

underlined are primary keys and italicized are foreign keys

2.

PLAYGROUP (ParentName, ParentEmail, ParentAddress, ChildName, PlayGroup, GroupLeader)

Functional dependencies:

ParentEmail -> ParentName,ParentAddress,ChildName

PlayGroup ->GroupLeader

Assumption : Each Child belongs to a PlayGroup. A PlayGroup can have many Children

PlayGroup ->-> ChildName (multivalue dependency)

Relations in 3NF

PARENT(ParentEmail , ParentName,ParentAddress,ChildName)

PLAYGROUP(PlayGroup ,GroupLeader)

PLAYGROUPCHILDREN(PlayGroup , ChildName)

underlined are primary keys and italicised are foreign keys

Please upvote if its is helpful. Do ask if you have queries.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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