A property agent wishes to set up a small database to manage his appointments, An appointment made is for one client to view one or more properties. A client may have multiple appointments, and a property may be viewed in multiple appointments.

 

If there are more than one viewer (e.s. family) for an appointment, only one client details (contract and name) are recorded against the appointment. A client name is not unique but client contact can identify a client

 

You are given a table:

Viewing (propertyAddress, propertyType, askingPrice, clientName, clientContact,, appointmentDatetime)

a

Formulate and list the functional and multi-valued dependencies (MVD). Include at least one MVD that is subsumed and at least one MVD that is not subsumed.

 

b

Draw dependency graph and propose candidate key(s) for the table. Show composite keys in brackets

 

 

c

1. For each MVD in answer to question 2(a), explain why it subsumed or not subsumed.
2. Normalized the table to Boyce Codd Normal Form (BCNF) and fourth Normal Form (4NF) by applying the BCNF approach in the course text.

 

Show how you apply the steps to arrive at the tables, explain whether the structure of each derived table at each iteration is already in BCNF and 4NF (if application)

 

d

Assemble your BCNF and 4NF tables and constraints clearly by given each table and the referential integrity constraint(s). if any, in the form described below:

 

Tables in this form:

TABLE-NAME (col-name1, colname2,...., col-namen)

-indicate (PK)against the primary key and

-indicate (FK) against the foreign keys, if any

 

Referential integrity constraint(s), if any, together with the child table in this form

TABLE-NAMEx.col-namex1 ....col-namexn must exist in

TABLE-NAMEy.colnamey1....col-nameyn