Exercise 2
==========

{A}+ = {A}
{B}+ = {B}
{A, B}+ = {A, B, C, D, E}
{C}+ = {C, A, B, D, E}
{C, D}+ = {C, D, A, B, E}
{E}+ = {E, D}
{D}+ = {D}


Exercise 3
==========

Assumptions:
- department name is not unique
- project name is not unique

FDs:

{eid} -> {lname, fname, did, dept}
{did} -> {dept}
{pid} -> {project}
{eid, pid} -> all attributes

{eid, pid}+ = all attributes => {eid, pid} is a superkey, but since it is also minimal, it is a CK too.

CK: {eid, pid}

Prime attributes: eid, pid
Non-prime attributes: lname, fname, did, dept, project, hours

-----------------------------
Let's look how this changes if we make different assumptions:
- department name is unique
- project name is unique

FDs:

{eid} -> {lname, fname, did, dept}
{did} -> {dept}
{dept} -> {did} (<= department name is unique) 
{pid} -> {project}
{project} -> {pid} (<= project name is unique)
{eid, pid} -> all attributes
{eid, project} -> all attributes

{eid, pid}+ = all attributes => {eid, pid} is a superkey, and since it is also minimal, it is a CK too.
{eid, project}+ = all attribute => {eid, project} is a superkey, and since it is also minimal, it is a CK too.

Prime attributes: eid, pid, project
Non-prime attributes: lname, fname, did, dept, hours

We can choose e.g. {eid, pid} to be the PK.


Exercise 4
==========
DentistFullName and PatientFullName are composite attributes violating 1NF, we can split them into DentistLastName, DentistFirstName and PatientLastName, PatientFirstName (feel free to add middle names too).

FDs:

{DentistNo} -> {DentistLastName, DentistFirstName}
{PatientSSN} -> {PatientLastName, PatientFirstName}
{Date, Time, SurgeryNo} -> all attributes
{Date, Time, DentistNo} -> all attributes
{Date, Time, PatientSSN} -> all attributes

CKs:

{Date, Time, SurgeryNo}
{Date, Time, DentistNo}
{Date, Time, PatientSSN}

(We can choose any of these to be the PK.)

Prime attributes: {DentistNo, PatientSSN, Date, Time, SurgeryNo}
Non-prime attributes: {DentistLastName, DentistFirstName, PatientLastName, PatientFirstName}

NFs:

After splitting the name attributes the Booking relation is in 1NF, but not in 2NF because of the first two above-mentioned FDs: {DentistNo} is a proper subset of CK {Date, Time, DentistNo} and {PatientSSN} is a proper subset of CK {Date, Time, PatientSSN}.

Normalization:

Booking(DentistNo, PatientSSN, Date, Time, SurgeryNo)
- PK e.g. Date, Time, SurgeryNo
- DentistNo is a FK ref. Dentist(DentistNo)
- PatientSSN is a FK ref. Patient(PatientSSN)
Dentist(DentistNo, DentistLastName, DentistFirstName)
- PK DentistNo
Patient(PatientSSN, PatientLastName, PatientFirstName)
- PK PatientSSN

(Homework: check that all three are in 3NF.)