CS411 Database Systems

1
CS411Database Systems
08 Midterm Review

• Kazuhiro Minami

2
Announcements

• Thanks for the feedback. We will address your
  comments as possible as we can.
• Homework 2 solution will be posted today
• We plan to return homework 2 next Monday
• I will extend my office hour next Monday
• 10am - 12pm
• 3pm - 5pm

3
Midterm Details

• 930am next Tuesday on October 14
• 75 minutes (regular class time slot)
• Please arrive early
• Bring UIUC photoid, pen, pencil, eraser
• Closed book, no notes

4
Questions during exam

• We will not answer technical questions.
• You can ask non-technical clarification
  questions.
• If you are not clear about assumptions in a
  problem, state your own assumptions and answer
  accordingly.

5
Answers

• Put your NetID on every page
• Write your answers in the exam sheet
• Make sure your work is concise and clear
• Show your working
• Will not penalize for minor SQL syntax errors
  (this is subjective but in your favor)
• Your writing must be legible

6
Coverage

• ER Data Model
• Relational Data Model
• Relational Algebra
• SQL
• Constraints and Trigger

7
Problems

• True/False questions
• ER model
• FD and normalization
• Relational algebra
• SQL

8
Problem 1 True/False Questions

• Are the following statements true?
• In a relational model, a relation can have many
  superkeys but only one key.
• Relations that are in 3NF must also be in BCNF.
• Intersection is one of the six basic relational
  operators in relational algebra.
• A subquery can reference attributes in the outer
  query.
• An attribute declared as UNIQUE can have NULL as
  its value.
• In SQL, an INSERT statement always requires all
  column values to be specified.
• SQL applies bag semantics for intersect, union,
  and difference operations by default.

FALSE
FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
9
ER Model

• Can you create an ER diagram from a
  specification?
• Entity sets, attributes, and relationships
• Roles and multiplicity in relationships
• Constraints keys, referncial integrity, etc.
• Make sure to understand
• Weak entity set
• Subclasses and isa relationships
• Can you convert an ER diagram to relations?
• How to convert entity sets and relationships?
• Do you know how to handle the special cases
  below?
• Many-one relationships
• Weak entity set
• Subclass entities

10

Subclasses in ER Diagrams
name
category
price
Product
isa
isa
Educational Product
Software Product
Age Group
platforms
11
Weak Entity Set
University
Student
student-ID
name
name
Q What is a key of Student?
12
Combining Two Relations

name
category
price
Start Year
name
makes
Company
Product
Stock price
No need for Makes. Just modify Product
name category price StartYear
companyName gizmo gadgets 19.99
1963 gizmoWorks
13
Normalization
14
Finding Keys
Homework 1, Problem 4d Consider a relation with
schema R(A,B,C,D) with functional dependencies
(FDs) BC ? A, AD ? B, C D ? B, AC ? D. Find
all the candidate keys of R.
Remember that a key is formally defined with
respect to FDs of R
Q What are keys for relation R?

• A set of attributes S is a key if
• S ? ABCD.
• There is no proper subset S of S (i.e., S ? S)
• where S ? ABCD

Attribute closure of S contains all the
attributes determined by S
Q How do we determine whether S ? ABCD?
Check if S A, B, C, D
Q By the way, which S should we try?
We dont know!
15
Finding Keys
Homework 1, Problem 4d Consider a relation with
schema R(A,B,C,D) with functional dependencies
(FDs) BC ? A, AD ? B, C D ? B, AC ? D. Find
all the candidate keys of R.
Strategy compute the closure of every subset of
attributes of R

• Start with A, C
• Apply AC ? D and get A, C, D
• Apply CD ? B and get A, C, D, B

A A B B C C D D A,
B A, B A, C A, C, D, B A, D A,
D, B B, C B, C, A, D B, D B, D C,
D C, D, B, A
A, B, C A, B, C, D A, B, D A, B,
D A, C, D A, B, C, D B, C, D A, B,
C, D A, B, C, D A, B, C, D
Q What are super keys?
Q What are keys?
A, C, B, C, C, D
16
Typical Questions in Normal Forms

• Definitions of 3NF , BCNF, 4NF
• Given a relation R with some FDs
• Is relation R in 3NF, BCNF, or 4NF?
• Decompose R into a set of relations in BCNF
• many other possible questions

17
BCNF Decomposition Algorithm

• Find functional dependencies in R
• Find keys by computing attribute closures
• Find a dependency A ? B that violates BCNF
  condition (i.e., A is not a superkey)
• Expand B by computing the closure of A (i.e., A)
• Decompose a relation into two relations T1(A ? B)
  and T2(others in R)
• Repeat this process until all the relations are
  in BCNF

18
Decompose R into a set of relations in BCNF

• R(A, B, C, D) with FDs AB ? C, C ? D, and D ? A

First, check whether R is in BCNF.
A relation R is in BCNF if whenever there is a
nontrivial FD A1 ... An ? B for R, A1 ... An
is a superkey for R.
19
1. Find keys of R

• R(A, B, C, D) with FDs AB ? C, C ? D, and D ? A

Strategy Compute the closure of every subset of
attributes in R
A A B B C C, D, A D D,
A A, B A, B, C, D A, C A, C, D A,
D A, D B, C B, C, D, A B, D B,
D, A
C, D C, D, A A, B, C A, B, C, D A,
B, D A, B, C, D A, C, D A, C, D B,
C, D A, B, C, D A, B, C, D A, B, C, D
20
2. Check whether R is in BCNF

• R(A, B, C, D) with FDs AB ? C, C ? D, and D ? A

Compare attributes on the left of each rule with
two keys A,B and B, C
Q Are there any FDs violating BCNF condition?
C ? D and D ? A
21
3. Decompose R
Lets pick C ? D and decompose R(A, B, C, D) into
R1 and R2.
Q What are the schemas of R1 and R2?
Q Is R2 in BCNF?
Yes, but why?
Q Is R1 in BCNF?
We need to repeat the same process.
22
4. Find all non-trivial dependencies in R1(A, B,
C)
Do we only need to consider this FD?

• R1(A, B, C) with FDs AB ? C, C ? D, and D ? A

• Start with C
• Apply C ? D, and get C, D
• Apply D ? A and get C, D, A
• Project C, D, A onto A, B, C and get C, A

Compute the closure of every subset of attributes
in R1
Q What are non FDs?
A A B B C C, A A, B A,
B, C A, C A, C B, C B, C, A A, B,
C A, B, C
C ? A, AB ? C, BC ? A
23
5. Decompose R1

• R1(A, B, C) with FDs C ? A, AB ? C, and BC ? A

A, B and B, C.
Q What are keys of R1?
Q Which FD violate BCNF condition?
C ? A
We decompose R1 into R11(A, C) and R12(B, C),
which are in BCNF. Thus, R is decomposed into
R2(C, D), R11(A, C), and R12(B, C).
Q By the way, is R1 in 3NF?
Yes because A is a prime.
24
Homework 2, Problem 1

• (b) Which products are available only at a single
  store?

T3 Inventory Relation
T31 Inventory Relation
25
Homework 2, Problem 1
T3 Inventory Relation
T31 Inventory Relation
2. Compute products available at multiple stores
A pT3.ProductName(T3 ? T3.ProductName
T31.ProductName AND T3.StoreID !
T31.StoreID T31)
26
Homework 2, Problem 1
T3 Inventory Relation
A Products available at multile stores
3. Subtract stores in A from stores in T3
B pT3.ProductName - A
B Products available at a single store
27
Homework 2, Problem 1

• (d) Find all the store names whose products in
  their inventories are a subset of the inventory
  of some other store?

T3 Inventory Relation
A

• Compute all the possible pair
• of ProductName and StoreName

A pProductNameT3 ? pStoreNameT3
28
Homework 2, Problem 1
T3 Inventory Relation
A
Bread is NOT available at Meijer
2. Compute a list of ProductName-StoreName pair
(p, s) where product p is NOT available at store
s.
Negative Inventory Relation B A - T3
29
Homework 2, Problem 1
T3 Inventory Relation
B Negative Inventory Relation
Meijer is NOT a superset of WalMart
3. Take the natural join of T3 and B and project
onto a pair of stores
C pT3.StoreName, B.StoreName (T3 ? B)
C
30
Homework 2, Problem 1
T3 Inventory Relation
4. Compute all the possible pair of two different
stores
T31 T3,
D(store1, store2) pStoreName T3
?T3.StoreName ! T31.StoreName pStoreName
T31
D
31
C(store1, store2) where store1 ? store2
D(store1, store2) - C(store1, store2)
where store1 ? store2
All the possible pair of two stores D(store1,
store2)
32
Homework 2, Problem 1
C
5. Substract D from C
E D - C
D
Schnucks is a subset of Meijer
33
Sample SQL Problems
Consider the following relations that the online
bookstore, CS411.com, maintains. Underlined
attributes are keys of those relations. The
attributes cid and isbn in Buy relation are
foreign keys referring to cid in Customer and
isbn in Book respectively. The attribute isbn in
Author is a foreign key referring to isbn in
Book. Book(isbn, title, publisher, price)
Author(assn, aname, isbn) Customer(cid, cname,
state, city, zipcode) Buy(tid, cid, isbn, year,
month, day)
(i) Make a list of the names of customers who
live in Illinois and spent more than 5,000 in
year 2000.
34
SELECT Customer.cname FROM Buy, Customer, Book
WHERE Customer.cid Buy.cid AND Buy.isbn
Book.isbn AND Customer.state ?Illinois AND
Buy.year 2000 GROUP BY Customer.cid Having
SUM(Book.price) gt 5000
35
Book(isbn, title, publisher, price) Author(assn,
aname, isbn) Customer(cid, cname, state, city,
zipcode) Buy(tid, cid, isbn, year, month, day)
(2) Create a view FriendsOfBob that contains a
list of people (i.e., a list of cid attribute
values in Customer) who share a common interest
with Bob whose cid 12345. We consider that two
persons share a common interest if they purchased
more than 20 same books before. (3) Make a list
of recommended books (i.e., a list of isbn
attribute values in Book) for Bob using view
FriendsOfBob. We recommend a book for Bob if his
possible friend in FriendsOfBob bought that book
before and Bob has not bought that book yet.
36
(2)
CREATE VIEW FriendsOfBob AS SELECT Buy.cid AS
cid FROM Buy WHERE Buy.cid ltgt 12345 AND
Buy.isbn IN (SELECT Buy.isbn
FROM Buy WHERE Buy.cid
12345) GROUP BY Buy.cid HAVING
COUNT(Buy.isbn) gt 20
A list of books Bob bought
37
(3)
SELECT Buy.isbn FROM FriendsOfBob, Buy WHERE
FriendsOfBob.cid Buy.cid AND Buy.isbn NOTIN
(SELECT Buy.isbn FROM Buy WHERE Buy.cid
12345)