CSI 410 Doctor Appointment Database Systems Questions

User Generated

xlyr2046

Programming

CSI 410

CSI

Description

Please answer all question in the file and follow all instructions.

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. Feel free to add comments or suggestions if any. 2
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.
View attached explanation and answer. Let me know if you have any questions.

Homework Assignment IV
Problem 1:
a. ER diagram:

b. List of tables:
Doctor:
Doctor_id - this is a unique ID of a doctor (Primary key)
Doctor_Name - this is the name of a doctor
Doctor_id

Doctor_Name

1

John

2

Nancy

3

Billy

4

Max

5

Lizzy

Table creation:
CREATE TABLE Doctor (
Doctor_ID int,
Doctor_Name varchar(255),
CONSTRAINT PK_Doctor PRIMARY KEY (Doctor_ID)
);

Table Insertion:
INSERT INTO Doctor (Doctor_ID,Doctor_name) VALUES (1, 'John');
INSERT INTO Doctor (Doctor_ID,Doctor_name) VALUES (2, 'Nancy');
INSERT INTO Doctor (Doctor_ID,Doctor_name) VALUES (3, 'Billy');
INSERT INTO Doctor (Doctor_ID,Doctor_name) VALUES (4, 'Max');
INSERT INTO Doctor (Doctor_ID,Doctor_name) VALUES (5, 'Lizzy');

Table Selection:
SELECT * FROM Doctor;

Patients:
Patient_id - this is a unique ID for each patient (Primary key)
Patient_name - this is the name of the patient
Zipcode - this is the zipcode of the patient
Patient_id

Patient_name

Zipcode

101

Lily

10023

102

Stewart

10045

103

Ben

10523

104

July

17542

105

Jack

10045

106

Emily

15245

107

Hannah

10045

108

Grace

10023

Table creation:
CREATE TABLE Patients (
Pa...


Anonymous
This is great! Exactly what I wanted.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags