CASE STUDY 4: LESSONS (SOLUTION) [43]

I. REQUIREMENTS

A. Narrative

A lesson has a name (L).

Lessons are offered on a Day (D) and Period (P).

Lessons are held in a Classroom (C) with a Teacher (T).

Each lesson has only one Day, Period, Classroom, Teacher

II. ANALYSIS

A. Modeling

1. Make an E/R Diagram (any many-to-many relationships?)

2. FD Diagram

LESSONS [44]

III. DESIGN

A. Preliminary Design

1. Data Design

b. External: relation schema

Consider the entire relation: (L,T,C,D,P,S)

What uniquely identifies each record?

Is this relation in 3NF?

Are there redundancies?

Consider these relations:

Sched(L, T, C, D, P)
  Candidate Key (L)
  Candidate Key (T, D, P)
  Candidate Key (C, D, P)

Study(S, L)
  Candidate Key (S, L)

What is the purpose of (S,L)?

Are these relations in 3NF?

II.A.1 E/R Diagram

Lessons <<-->> Students

Teachers -->> Lessons

Classroom -->> Lessons

III.A.1.b Relation Schema

What uniquely identifies each record?

DPCS, DPTS, DPS, LS

Is this relation in 3NF?

No. Everything is prime which suggests 3NF but its not in 2NF: L $\rightarrow$ DPCT

Are there redundancies?

Yes, the many-to-many relationship between students and lessons generates the same teacher, classroom, day, period

Is Sched and Study in 3NF?

Yes, the first one has candidate key $\rightarrow$ prime

The second is just SL $\rightarrow$ nothing but there could be grades.