1 attachmentsSlide 1 of 1

  • attachment_1attachment_1

UNFORMATTED ATTACHMENT PREVIEW

CSI 410. Database Systems – Spring 2021 Homework 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.

Do you similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com. We assure you of a well written and plagiarism free papers delivered within your specified deadline.