CSI 410 George Mason University Database System Computer Programming Task

CSI 410. Database Systems – Spring 2021Homework Assignment IV
The deadline for this assignment is 11:59 PM, May 3, 2021. Submissions after this deadline
will not be accepted. Each student is required to enter the UAlbany Blackboard system and then
upload a .pdf file (in the form of [first name]_[last name].pdf) that contains answers to Problems
1-3.
The total grade for this assignment is 100 points. If you find any error or have questions or
suggestions, please contact the instructor (jhh@cs.albany.edu).
Problem 1. (50 points) A database for a hospital needs to store data about medical doctors
(identified by doctor_ID, with doctor_name as an attribute), patients (identified by patient_ID,
with patient_name and ZIP_code as attributes), and appointments (identified by appointment_ID,
with time and date as attributes). An appointment is for exactly one patient and at most one (i.e.,
0 or 1) doctor. A patient may have multiple appointments. Similarly, a doctor may have multiple
appointments.
(a) (20 points) Draw an E/R diagram that captures all of the above information.
(b) (20 points) Define tables using SQL that correspond to the E-R diagram in (a). You must
define the smallest number of tables while ensuring that these tables are in BCNF (you do
not need to prove that the resulting tables are in BCNF). For each table, specify primary and
foreign keys. Also, add all relevant integrity constraints.
(c) (10 points) List all of the nontrivial functional dependencies that you can find from the tables
in (b). Please omit functional dependencies that are redundant (for example, if a → b is
included, do not add ac → bc).
Problem 2. (10 points) Consider the statement that α → β implies β → α. Determine whether
or not this statement is true. If true, prove it using the definition of functional dependency: α → β
if and only if t1 [α] = t2 [α] ⇒ t1 [β] = t2 [β]. Otherwise, give an example (i.e., a table containing
rows and columns) which shows that the statement is false.
Problem 3. (40 points) Consider a relation schema R(A, B, C, D, E, G) and a set of functional
dependencies F = {A → C, AD → CE, B → ACD, C → B}.
(a) (10 points) Show the steps of computing a canonical cover for F.
(b) (10 points) Compute the closure of AG and then determine whether or not AG is a candidate
key.
(c) (10 points) Determine whether or not (A, E, G) is in BCNF and justify your answer using the
transitive closure of a set of attributes. If (A, E, G) is not in BCNF, find a BCNF decomposition of it.
(d) (10 points) Assume that (A, E, G) is decomposed into (A, G) and (E, G). Given the above
functional dependencies, is this decomposition always lossless? If so, prove this. Otherwise,
explain it using an example (i.e., a case of decomposing a table containg rows and columns
into two tables).
1
After solving the above problems, please state the amount of time spent for this assignment.
Feel free to add comments or suggestions if any.
2