exam 2 review - northeastern university · 2018. 1. 4. · exam 2 review lecture 11 october 31,...
TRANSCRIPT
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Exam 2 Review
Lecture 11
October 31, 2017
Exam 2 Review
1
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Format
• 3 problems, with multiple sub-parts
• No notes, calculators, books, computers, phones, etc. may be used
October 31, 2017
Exam 2 Review
4
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
ContentConceptual/Logical database design…
– ER Diagrams–Mapping ER Diagrams to Relations– Normalization
October 31, 2017
Exam 2 Review
5
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
ER Diagrams & Mapping• Conceptual design: goals, approaches• All the notation we covered– Entities: weak/strong– Attributes: composite, multi-valued, derived,
keys– Relationships: cardinality, structural, attributes– Specialization/Generalization– When to use!
• Mapping to tables– Multiple methods for specialization/generalization
October 31, 2017
Exam 2 Review
6
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Exercise• Describe in words the
following ERD– How can you identify
an instance of E?
• Map E to relation(s)– What are the primary
key(s)?– What happens to
other key(s)?
October 31, 2017
Exam 2 Review
7
mv E
a1
id2
a4a3
id1
d
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Answer• All E’s have an a1, an id1, an id2 composed
of a3 and a4, and some number of mv’s. By combining these you can determine the E’s d.
• An E can be uniquely identified by either its id1, or the combination of a3 and a4.
October 31, 2017
Exam 2 Review
8
id1 a1 a3 a4E
id1 mv
MV
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
ExerciseProduce an ERD & corresponding relational schema
• An S has an sa and can be uniquely identified by its sid
• Each S must R2 with a single O2, whereas each O2 may R2 with any number of S’s. When an S R2’s an O2, it is important to note the corresponding ra2
• An O2 has an x and can be uniquely identified by its o2_id, which is comprised of p1 and p2
• A W is identified by its corresponding S, in combination with its own wid, consisting of a wa and wb
• Each W can R1 with any number of O1’s, and likewise each O1 can R1with any number of W’s. Each R1 interaction has a corresponding ra1
• An O1 is uniquely identified by its o1_id and also has an x
October 31, 2017
Exam 2 Review
9
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Answer (1)
October 31, 2017
Exam 2 Review
10
S
sid sa
O2
R2 ra2
o2_id
x p1 p2
ID W
R1
O1o1_id
x
ra1
sidwb
wa
N
1
N1
N
M
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Answer (2)
October 31, 2017
Exam 2 Review
11
sid sa p1 p2 ra2S
p1 p2 xO2
sid wa wbW
o1_sid sid wa wb ra1R1
o1_sid xO1
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
1
Exercise
October 31, 2017
Exam 2 Review
12
P
PID
C1 C2
Ud
PA
A1 A2
R
E
EID EA
N
MapthisERDtorelationsintwo differentways.Prosandconsofeach?
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Answer
October 31, 2017
Exam 2 Review
13
pid paP
pid a1C1
pid a2 eidC2
eid eaE
eid eaE
pid pa a1C1
pid pa a2 eidC2
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Normalization• What are the goals of normalization?– Spurious tuples? Additive decomposition?– Modification anomalies? Examples!
• Functional dependencies– Definition, relationship to keys– Trivial, transitive, full
• Normal forms– What do 1NF/2NF/3NF require?– Decomposition algorithm
October 31, 2017
Exam 2 Review
14
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Exercise
October 31, 2017
Exam 2 Review
15
Non-Trivial FDs Key(s)
S T U V W
S ! T
TU ! WVW ! S
UV SUVWUV T
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
ExerciseWhich NF? Why? Decompose to 3NF.
October 31, 2017
Exam 2 Review
16
X Y Z
M N O P
Foo
Bar
-
CS5200 – Database Management Systems Fall 2017 Derbinsky
Answer• Foo is in 2NF– 1NF (single PK attr)– Y is tFD on PK– Post: single PK/np
• Bar is in 1NF– P is not fFD on PK– Post:
• Bar: N fFD on PK, single PK/np
• O: single PK/np
October 31, 2017
Exam 2 Review
17
X ZFoo
Z YZ
M N OBar
O PO