db review session. er diagrams 1 where does the salary attribute belong? actor id name address...
TRANSCRIPT
![Page 1: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/1.jpg)
DB Review Session
![Page 2: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/2.jpg)
ER Diagrams 1
Where does the salary attribute belong?
Actorid
name
address
birthday
Acted In Movie
title
type
year
salary
![Page 3: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/3.jpg)
id
name
Actor Acted In Movie
The entities in a relationship set identify the relationship
role
title
ER Diagrams 2
![Page 4: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/4.jpg)
id
name
Actor Acted In Movie
The entities in a relationship set identify the relationship
How would we store information about a person who acted in one movie in several roles?
titleRole
title
ER Diagrams 2
![Page 5: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/5.jpg)
ER Diagrams 3
Personid
name
age
FatherOf
Where would you put the arrow?
father
child
![Page 6: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/6.jpg)
ER Diagrams 3
Personid
name
age
FatherOf
father
child
![Page 7: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/7.jpg)
ER Diagrams 4
Directorid
name
produced Movie title
Actorid name
What does this mean?
A movie has at most one actor and one director
![Page 8: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/8.jpg)
What does this diagram mean?
Directorid
name
Directed Movie title
A movie has exactly one director.
A director directs at least one movie.
ER Diagrams 5
![Page 9: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/9.jpg)
Copy
copy number condition
Copy Of
Borrowed
Person
id
Booktitle
author
isbnOwned By
Libraryname
ER Diagrams 6
![Page 10: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/10.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
P2
pno
sno
=
Division 1
![Page 11: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/11.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
P2
pno
sno
=
Division 1
S1S2S3S4
![Page 12: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/12.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
P2
P4
pno sno
=
Division 2
![Page 13: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/13.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
P2
P4
pno sno
=
Division 2
S1S4
![Page 14: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/14.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
P1
P2
P4
pno sno
=
Division 3
![Page 15: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/15.jpg)
snopno
S1
S1
S1
S1
S2
S2
S3
S4
S4
P1
P2
P3
P4
P1
P2
P2
P2
P4
pno sno
=
Division 3
S1P1
P2
P4
![Page 16: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/16.jpg)
SQL Aggregation 1
SELECT B.bid, COUNT(*)
FROM Boats B, Reserves R
WHERE R.bid=B.bid and B.color=‘red’
GROUP BY B.bid
What does this query return?
Tuples:
(id of a reserved red boat, # of reservations of the red boat)
![Page 17: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/17.jpg)
SELECT B.bid, COUNT(*)
FROM Boats B, Reserves R
WHERE R.bid=B.bid
GROUP BY B.bid, B.color
HAVING B.color=‘red’
SQL Aggregation 2
What if we put the condition into the HAVING clause?
We have also to put the color in the grouping list!
![Page 18: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/18.jpg)
Aggregation 3
SELECT color
FROM Boats B
GROUP BY color
HAVING max(count(bid))
What is wrong with this?
How would you fix it?
The Color for which there are the most boats
![Page 19: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/19.jpg)
SELECT color
FROM Boats B
GROUP BY color
HAVING count(bid) >= ALL
(SELECT count(bid)
FROM Boats
GROUP BY Color)
SQL Aggregation 3
The Color for which there are the most boats
![Page 20: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/20.jpg)
Compute Closure(X, F)
C := X
while there is a V W in F such that (V C)and (W C) do
C := C Wreturn C
Attribute Closure 1
![Page 21: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/21.jpg)
R=ABCDE F={ABC, CEB, DA, BCE}
Attribute Closure 2
{A}+ =
{A,B}+ =
{B,D}+ =
{A}
{A,B,C,E}
{A,B,C,D,E}
![Page 22: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/22.jpg)
IsDependencyPreserving(F,R1…k)for each X->Y in F do
if not IsPreserved(X,Y,R1…k)return false
return true
Dependency Preservation 1a
![Page 23: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/23.jpg)
/* check if X->Y is preserved */IsPreserved(X,Y,R1…k)Z:=Xwhile changes to Z occur do for i=1 to k do Z:= Z ((Z Ri)+ Ri) if YZ return trueelse return false
Dependency Preservation 1b
![Page 24: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/24.jpg)
Is the following decomposition dependency preserving?
R = ABCDE F = {A -> ABCDE, BC -> A, DE -> C} R1 = ABDE, R2 = DEC
Dependency Preservation 2
No! BC->A is not preserved
![Page 25: DB Review Session. ER Diagrams 1 Where does the salary attribute belong? Actor id name address birthday Acted In Movie title type year salary](https://reader035.vdocuments.us/reader035/viewer/2022062515/56649c765503460f9492a9b9/html5/thumbnails/25.jpg)
R = ABC. For each F below, decide whether R is in BCNF/3NF:
Normal Forms
F = {} F = {A -> B} F = {A -> B, A -> C} F = {A -> B, B -> C} F = {A -> B, BC -> A}