db review session
DESCRIPTION
DB Review Session. salary. ER Diagrams 1. title. birthday. id. Movie. Actor. year. Acted In. name. type. address. Where does the salary attribute belong?. role. ER Diagrams 2. The entities in a relationship set identify the relationship. id. Actor. Movie. Acted In. title. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: DB Review Session](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/html5/thumbnails/1.jpg)
DB Review Session
![Page 2: DB Review Session](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/html5/thumbnails/5.jpg)
ER Diagrams 3
Personid
name
age
FatherOf
Where would you put the arrow?
father
child
![Page 6: DB Review Session](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/html5/thumbnails/6.jpg)
ER Diagrams 3
Personid
name
age
FatherOf
father
child
![Page 7: DB Review Session](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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](https://reader036.vdocuments.us/reader036/viewer/2022062322/568150be550346895dbedcbf/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}