Question

Following is a relation:

MEETING (AttorneyID, AttorneyName, AttorneyPhone, ClientID, ClientName, ClientPhone, MeetingDate, MeetingTime, MeetingRoom, MeetingDuration)

Assumption:

• One attorney may have many meetings.
• One client may have many meetings.
• One attorney can have at most one meeting in one day with the same client.

Questions and Answers:

1. List all functional dependencies.
2. Among the list of (1), list partial dependencies (if none, type N/A).
3. Among the list of (1), list transitive dependencies (if none, type N/A).
4. Describe the new database design in 3NF.

TABLE (PrimaryKey, ForeignKey (FK), Attribute1, Attribute2, Attribute3)

Answer 1

The attributes of a table are said to be dependent on each other when an attribute of a table uniquely identifies another attribute of the same table.

Functional Dependencies here are

AttorneyID-->AttorneyName, AttorneyPhone

ClientID-->ClientName, ClientPhone

In the meeting table, The primary key is the combination of AttorneyID , ClientID so the client name only depends on ClientID, not on AttorneyID. Hence its partial dependencies. Same case with Attorney name.

A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For e.g.

AttorneyName -> ClientID is a transitive dependency if the following three functional dependencies hold true:

• AttorneyName->AttorneyID
• AttorneyID does not ->AttorneyName
• AttorneyID->ClientID