RMIT Classification: Trusted
Part 1: Conceptual Model (using Entity-relationship model)
Part 2: 7-step Mapping Process
Step 1: Strong entities à relations
Movie (MovieID, Title)
Genre (Name, Description)
Actor (Name, DoB, BirthPlace)
Director (Name, DoB, BirthPlace)
Novel (Title, Author, PubYear)
Step 2: Weak entities à relations Skip
Step 3: Map 1:1 relationships à either merge relations, add foreign keys, etc
Skip
Step 4: 1:M relationships à add foreign keys Movie ((MovieID, Title, NovelTitle*)
Step 5: M:N relationships à add new relation Directs (MovieID, DirectorName)
Appears (MovieID, ActorName)
Classified (MovieID, GenreName, Notes)
Step 6: Multi-valued attributes à separate relations Skip
Step 7: Higher-degree relationships à add new relation Skip
Final schema:
Movie (MovieID, Title, NovelTitle*) ß 3NF Genre (Name, Description)
Actor (Name, DoB, BirthPlace)
RMIT Classification: Trusted
Director (Name, DoB, BirthPlace)
Novel (Title, Author, PubYear)
Directs (MovieID, DirectorName). ß already 3NF Appears(MovieID,ActorName). ßalready3NF Classified (MovieID, GenreName, Notes)
Part 3: Top-down Normalisation Process
Movie (MovieID, Title, NovelTitle*)
Consider the following FDs among the attributes in this relation. FD1: MovieID à Title
FD2: Title à MovieID ß this is not a valid FD
FD3: MovieID à NovelTitle
FD4: NovelTitle à MovieID ß this is not a valid FD
Valid FDs
FD1: MovieID à Title FD3: MovieID à NovelTitle
Normalisation Process
Testing for 1NF à No multivalued attributes à 1 NF
Testing for 2NF
PK is single attrib à 2NF
Testing for 3NF
Two FDs FD1 and FD3, but both have PK in the left-hand side.
à Movies is in 3NF.
Repeat the above normalisation process for other relations, except Directs and Appears. These two are already in 3NF, as there are no non-primary key attributes to test with.
RMIT Classification: Trusted
Book (BookTitle, AuthorName, BookType, DeweyNo, SubjectArea, ListPrice, AuthorAff, Publisher)
FD1: BookTitleàBookType, DeweyNo, ListPrice, Publisher FD2: DeweyNoàSubjectArea
FD3: AuthorNameàAuthorAffiliation
RMIT Classification: Trusted
Employee (SSN, Fname, Minit, Lname, BDATE, Address, Sex, Position, Salary, SuperSSN*, Dnumber*)
DeptLocations (Dnumber, Location)
Department (Dnumber, Dname, MGRSSN*, MGRStartDate)
WorksOn (Pno, ESSN, Hours)
Project (Pno, Pname, Plocation, Dno)
Dependent (ESSN*, DependentName, Sex, Bdate, Relationship)
RMIT Classification: Trusted
Employee (SSN, Fname, Minit, Lname, BDATE, Address, Sex, Position, Salary, SuperSSN*)
RMIT Classification: Trusted
Department(Dnumber, Dname, MGRSSN*, MGRStDate, Location1, Location2, Location3, ESSN*)
Project (Pno, ESSN*, Pname, Plocation, Dno*, Hours)
RMIT Classification: Trusted
Dependent (ESSN*, DependentName, Sex, Bdate, Relationship)
RMIT Classification: Trusted