Question 1
The table shown below displays the details of the roles played by actor/actresses in
films.
FilmN FTitle Director Actor AName Role TimeOnScr
o No een
F1100 Happy Jim Alan A1020 Sheila Toner Jean 15.45
Days Simson
F1100 Happy Jim Alan A1222 Peter Watt Tom 23.38
Days Kinder
F1100 Happy Jim Alan A1020 Sheila Toner Sil Simson 22.56
Days
F1109 Snake Sue A1567 Steve Tim Rosey 19.56
Bite Ramsay McDonald
Sue A1222 Peter Watt Archie 10.44
Ramsay Bold
a) Identify the primary key, and then describe and illustrate the process of
normalization by converting the table to Third Normal Form (3NF),
1NF
FilmN FTitle Director Actor AName Role TimeOnScr
o No een
Primary Key (FilmNo, ActorNo, Role)
Partial Dependency
FilmNo -> FTitle, Director
ActorNo -> AName
Full Dependency
(FilmNo, ActorNo, Role) -> TimeOnScreen
2NF
Film
FilmN FTitle Director
o
Actor
Actor AName
No
TimeOnScreen
This study source was downloaded by 100000822046281 from CourseHero.com on 05-10-2023 08:15:42 GMT -05:00
https://www.coursehero.com/file/41692638/Normalization-Exercise1-Answer2doc/
FilmN Actor Role TimeOnScr
o No een
Transitive Dependency
No Transitive Dependency
3NF
No new table because there is no Transitive Dependency
3NF same as 2NF
Insertion anomaly:
We cannot add a new actor record unless the new actor plays at least one role in a film.
The primary key of this table is (FilmNo, ActorNo, Role). FilmNo and Role cannot have
null value.
Update anomaly:
If film F1100 Happy Days’ director is changed, we need to update three rows.
Deletion anomaly:
If row 4 record is deleted, the actor A1567, Steve McDonald details will be removed
indirectly. We cannot find his record anymore.
Question 2
Figure 1 shows a table of student residence data. Assume that the primary key of this
relation is MatricNo. There’s no doubt that this table contains redundancy as exhibited
by the value of CollegeLocation and CollegeCost. Base from your basic
understanding of the normalization process, transform this table to Third Normal Form
(3NF).
Student-College
MatricNo StudentName CollegeName CollegeLocation CollegeCost
100 Sakinah Alfa Persiaran 1 300
200 Jamil Beta Lebuh Silikon 350
300 Ah Lim Gamma Persiaran 2 375
400 Subramaniam Beta Lebuh Silikon 350
500 Wiranto Alfa Persiaran 1 300
600 Zahari Gamma Persiaran 2 375
700 Mohamad Alfa Persiaran 1 300
Figure 1
This study source was downloaded by 100000822046281 from CourseHero.com on 05-10-2023 08:15:42 GMT -05:00
https://www.coursehero.com/file/41692638/Normalization-Exercise1-Answer2doc/
1NF
MatricNo StudentName CollegeName CollegeLocation CollegeCost
Primary Key (MatricNo)
Partial Dependency
No Partial Dependency because these is No Composite Primary Key.
Full dependency
MatricNo -> StudentName, CollegeName, collegeLocation, CollegeCost
2NF
No new table because there is no Partial Dependency.
MatricNo StudentName CollegeName CollegeLocation CollegeCost
Transitive Dependency
CollegeName -> CollegeLocation, CollegeCost
3NF
CollegeName CollegeLocation CollegeCost
MatricNo StudentName CollegeName*
Question 1
This study source was downloaded by 100000822046281 from CourseHero.com on 05-10-2023 08:15:42 GMT -05:00
https://www.coursehero.com/file/41692638/Normalization-Exercise1-Answer2doc/
Question 2
This study source was downloaded by 100000822046281 from CourseHero.com on 05-10-2023 08:15:42 GMT -05:00
https://www.coursehero.com/file/41692638/Normalization-Exercise1-Answer2doc/
Powered by TCPDF (www.tcpdf.org)
