IEEM 230 Industrial Data Systems

Fall 2000, Assignment 2

1. The following ER diagram was provided as the model solution to assignment 1. For this ER diagram, use the informal mapping rules to write all the schemas. For each schema, identify the key attribute(s), as well as all the foreign keys.

All primary key attributes are underlined. Foreign Keys (FK) are listed for each schema.

STAFF( IDno, Name, Fee, Job-Type)

SCHEDULE( Day, Session)

CONSULT_REPORT( ReportNo, Time, Remarks, DoctorID, PatientID)

FK1: DoctorID refers STAFF.IDno; FK2: PatientID refers PATIENT.HKID

INVOICE( InvoiceNo, Date, ConsultRepNo)

FK1: ConsultRepNo refers CONSULT_REPORT.ReportNo

MEDICINE( Name, Price)

MEDICINE-BATCH( Name, ExpiryDate, Quantity)

FK1: Name refers MEDICINE.Name

PATIENT( HKID, Name, InsuranceCo, PolicyNo, PolicyExpDate)

STAFF-SCHEDULE( StaffID, SchDay, SchSession)

FK1: StaffID refers STAFF.IDno;
FK2 (SchDay, SchSession) refers (SCHEDULE.Day, SCHEDULE.Session)

PRESCRIPTION( ConsultRepNo, MedicineName, Qty)

FK1: ConsultRepNo refers CONSULT_REPORT.Report.No;
FK2: MedicineName refers MEDICINE.Name

2. Write the conceptual schemas for the tables corresponding to the following ER diagram (use the informal rules for mapping from an ER diagram to Schemas). For each schema, mark the primary key, and also the foreign key(s).

A( a1, a2)

R( xa1, xa2, ya1, ya2, r1)

FK1: (xa1, xa2) refers (A.a1, A.a2)

FK2: (ya1, ya2) refers (A.a1, A.a2)

Notes: One schema for regular entity A; One schema for N:M relation, R. In the schema R, we need to get the key of the N-side (we call it xa1, xa2), and the key of the M-side (called ya1, ya2). Also included is the attribute r1.

3. Prove/Disprove the following statements about functional dependencies (all upper case letters denote a set of attributes):

(a) If P --> Q and AQ --> R, then AP --> R

Statement is TRUE.

P --> Q (given) [1]

AP --> AQ (augmentating both sides by A) [2]

AQ --> R (given) [3]

AP --> R ( transitive law applied to [2] and [3]). QED.

(b) If AB --> AC, then B --> C

The statement is FALSE.

Counterexample: (we use the CAR schema, and let:

A = LicenseNo, B = State, C = Model;

AB --> C (given) [1]

AB --> A (reflexive law) [2]

Thus AB --> AC (union law).

But B -/-> C, for instance, it is possible to find two different cars, both registered in the same state, say New York, but with different Models (e.g. one is a Toyota Corolla, another is a BMW 323ci).

4. Given a schema R( A, B, C, D, E), and the following set of FDs:

A --> E, E --> CD, BC --> A, D --> B

(a) Determine all the candidate keys.

A --> E, E --> CD, so A --> CDE (transitive); A --> D (decomposition), D --> B (given), so A --> B (transitive);
So A --> BCDE; Therefore A is a candidate key.
NOTE: any superset of A cannot be a candidate key, since it will not be minimal)

E --> CD, so E --> C, E --> D [1]
D --> B (given); so E --> B (using [1] and transitive law) [2]
E --> BC (from [1], [2] and union) [3]
E --> A (transitive law using [3] and BC --> A (given)) [4]
From [1], [2], [3], [4] and union law, E --> ABCD.
So E is a candidate key.
NOTE: any proper superset of E will not be a candidate key, since it will not be minimal.

B is not a candidate key, since B alone cannot determine any other attribute;
C is not a candidate key, since C alone cannot determine any other attribute;
BC --> A (given), and A --> ADE (since A is a candidate key).
Thus, by transitive law, BC --> ADE.
Therefore BC is a minimal superkey, so BC is a candidate key.

D alone is not a candidate key [ D -/-> A];
CD --> B (since D --> B), and CD --> C (reflexive), thus CD --> BC, and BC --> ABE (since BC is key)
Thus, CD is a candidate key.

You can verify that no other subset of ABCDE is a candidate key. Thus there are four candidate keys: A, E, BC, CD.

(b) Select a primary key for the schema. Using this key, is the schema in 2NF ? 3NF ?

(i) A is the primary key: R is in 2NF (trivial); R is not in 3NF (A --> E and E --> CD)

(ii) E is the primary key: R is in 2NF (trivial); R is not in 3NF (E --> D, and D --> B).

(iii) BC is primary key: R is in 2NF (B -/-> A, B -/-> C, B -/-> D, B -/-> E, C -/-> A, C -/-> B, C -/-> D, C -/-> E)
R is not in 3NF, since BC --> A, and A --> E.

(iv) CD is primary key: R is not in 2NF (since D --> B); R is therefore not in 3NF.

(c) Is the above schema in generalized 2NF ?

No, since it is not in 2NF with respect to candidate key CD.