Programming Database Final Question

Database final

Content will base on these four homework. I uploaded with answers.

You need to read through these four homework to make sure your knowledge can totally handle all of it. The format of final would be same as these four homework. You need to try to make you answers style similar to the homework’s answer.

I also uploaded lecture slides, you need to read through all of knowledge on it.

Time is 8:00 am to 9:30 am at 5/14 edt. We will meet at that time.

CSI 410. Database Systems – Spring 2021
Final Examination – May 14, 2021
This examination has questions for a total of 100 points. Write your student ID, name, and all answers and
upload a digital image of your work on Blackboard.
Question I (30 points total) A database for an online bookstore needs to store data about books (identified
by isbn, with title and price as attributes), authors (identified by author id, with name and url as attributes)
and publishers (identified by publisher name, with address and url as attributes). Each book is written by
one or more authors and published by exactly one publisher (publishing company). Each author can write
multiple books. Each publisher can publish multiple books.
(a) (15 points) Draw an E-R diagram that captures all of the information provided above.
(b) (15 points) Define tables using SQL that correspond to the E-R diagram in (a). You must define the
smallest number of tables all in BCNF (no proof required). For each table, specify primary and foreign
keys as well as necessary constraints. Assume that the following two tables are already defined:
create table author (
author_id varchar ( 3 0 ) ,
name varchar ( 3 0 ) ,
url varchar ( 5 0 ) ,
primary key ( author_id ) ) ;
create table publisher (
publisher_name varchar ( 2 0 ) ,
address varchar ( 5 0 ) ,
url varchar ( 5 0 ) ,
primary key ( publisher_name ) ) ;
Question II (40 points total) Consider a set of functional dependencies F = {A → C, AC → B, B → AD}
that hold on a relation schema R(A, B, C, D).
(a) (10 points) Determine whether or not A in the right-hand side of dependency B → AD is extraneous.
Justify your answer while emphasizing (i) which/what functional dependency needs to be derived/inferred from (ii) which/what set of functional dependencies.
(b) (10 points) Determine whether or not C in the left-hand side of dependency AC → B is extraneous. Justify your answer while emphasizing (i) which/what functional dependency needs to be derived/inferred
from (ii) which/what set of functional dependencies.
(c) (10 points) Is R in BCNF? Justify your answer using the closure of each relevant set of attributes. If R
is not in BCNF, decompose R into BCNF.
(d) (10 points) Determine whether or not AB is a candidate key. Justify your answer using the closure
of each relevant set of attributes.
Question III (10 points) Consider a situation where hash join is applied to relations R and S. Assume
that (1) each of relations R and S contains 108 disk blocks, (2) the hash join partitions S into 20 buckets in
a manner where each bucket fits into the main memory, and (3) the last block of each bucket from R and S
is completely filled. Calculate the total number of block transfers during the hash join. Justify your answer.
1
Question IV (10 points) Consider relations R(A, B, C) and S(D, E) and the following query:
select A , E
from R , S
where R . C = S . D and E > 100 and B = ‘John ‘
We want to obtain a tree-style relational expression (see below) that (1) is equivalent to the above query
and (2) performs selections and projections as early as possible. In the following figure, find predicates to
put in blank (a) and blank (b). Also, find a minimal list of attributes to put in blank (c) and explain why
each of these attributes is necessary.
⇧A,E
⇥R.C=S.D
B,C
B>E
(c)
(a)
B>E
(b)
S
R
Question V (10 points) Student X argues that ΠB (R 1 S) = ΠB (ΠB (R) 1 S) for any arbitrary relations R
(with attributes including B) and S. Student Y disagrees. Who is correct? Justify your answer considering
a case where relation R is as follows:
A
1
B
1
2
Homework 4
1a 15 not using the notation explained in class; no relationship sets; total participation missing
1b 19 total participation from appointment to patient is missing
1c 10
2 8 need to have duplicate names in the table
3a 6 incorrect canonical cover; need to specify from which set of FDs each closure is calculated; need to show the minimality of the canonical cover
3b 6 see the posted solution
30 6 need to show that the left hand side of A->E is not a superkey in (A, E, G)
3d 8 need to present a table