Determine if the following tables satisfy BCNF. If a table satisfies or violates

Question

Determine if the following tables satisfy BCNF. If a table satisfies or violates BCNF, say so, give the reason briefly, and produce the BCNF tables.. Do not normalize it to 2NF first. Normalize the table to BCNF directly. You must take the primary key (underlined) and additional functional dependencies as given and cannot assume further function dependencies. You must underline the primary key for each table you write in order to receive full credits.

a). CarServices: (part#, tranid, partname, partType, charge, invoice#)

Additional FDs:    part #® partname, partType

partType ® charge

tranId ® invoice #

BCNF?

b). ParkingTicket: (TicketNum, StudentId, StudentName, Plate#, Code, Fine)

Additional FDs:    StudentId ® StudentName, Plate#

Plate # ® StudentId

Code ® Fine

BCNF?

c). PTA_Meeting: (MeetingNum, AgendaItem, Date, AgendaSponsor, SponsorPhone, MeetingLoc, Capacity, Resolution)

Additional FDs:    MeetingNum ® Date, MeetingLoc

MeetingLoc ® Capacity

AgendaSponsor ® SponsorPhone

BCNF?

Explanation / Answer

To check weather given relations are in BCNF, the steps are :

Find candidate keys by using clouser properties.
Check all FD's satisfies BCNF definition.

a)
Relations schema is:
CarServices: (part#, tranid, partname, partType, charge, invoice#)
Functioanl dependencies are: part #® partname, partType
partType ® charge
tranId ® invoice #
Let us assume,
X:={CarServices}
F+= {part#--->part#,partname,partType,charge;
part#,transid--->part#,partname,partType,charge,transid,invoice #}

This relation contains only one candidate key of {part#,transid}. For this relational schema left hand side FDs does not include {part#,transid}, so that, this relation is not in BCNF.

By decomposing above relational schema to BCNF,the tables should be as follows:
(part#,partname,partType,charge,transid) and (transid,invoice) are in BCNF.

b) Relational schema is:
ParkingTicket: (TicketNum, StudentId, StudentName, Plate#, Code, Fine)
FD's are: StudentId--> StudentName, Plate#
Plate #-->StudentId
Code--> Fine
F+={StudentID-->StudentName, Plate#;
(StudentID,Code)---->StudentId,StudentName, Plate#,Code,Fine;
(Plate#,Code)---->StudentId,StudentName, Plate#,Code,Fine;
(StudentID,Code,TicketNum)---->StudentID,StudentName,Code,TicketNum,Plate#,Fine;
(Plate#,Code,TicketNum)---->StudentID,StudentName,Code,TicketNum,Plate#,Fine}

The candidate keys are {(StudentID,Code,TicketNum);(Plate#,Code,TicketNum)}. For given relational schema left hand side of any FDs does not include {(StudentID,Code,TicketNum);(Plate#,Code,TicketNum)}, so that, this relation is not in BCNF.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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