ddata 324 hw 2

CREATE TABLE Patient ( SSN VARCHAR ( 11 ) NOT NULL PRIMARY KEY , name VARCHAR ( 50 ) NOT NULL , age INT NOT NULL , phySSN VARCHAR ( 11 ) NOT NULL , FOREIGN KEY (phySSN) REFERENCES Pri_physician(SSN) ); CREATE TABLE Pri_physician ( SSN VARCHAR ( 11 ) NOT NULL PRIMARY KEY , name VARCHAR ( 50 ) NOT NULL , specialty VARCHAR ( 50 ) NOT NULL , experience INT NOT NULL , FOREIGN KEY (SSN) REFERENCES Doctor(SSN) ); CREATE TABLE Doctor ( SSN VARCHAR ( 11 ) NOT NULL PRIMARY KEY , name VARCHAR ( 50 ) NOT NULL , experience INT NOT NULL , presi VARCHAR ( 11 ) NOT NULL , FOREIGN KEY (presi) REFERENCES Clinic(SSN) ); CREATE TABLE Clinic ( SSN VARCHAR ( 11 ) NOT NULL PRIMARY KEY , clinic_name VARCHAR ( 50 ) NOT NULL , phone_num VARCHAR ( 15 ) NOT NULL , address VARCHAR ( 100 ) NOT NULL ); CREATE TABLE Prescription ( date DATE NOT NULL , SSN VARCHAR ( 11 ) NOT NULL , drug_name VARCHAR ( 50 ) NOT NULL , PRIMARY KEY ( date , SSN, drug_name), FOREIGN KEY (SSN) REFERENCES Doctor(SSN), FOREIGN KEY (drug_name) REFERENCES Drug(drug_name)

); CREATE TABLE Pharmacy ( name VARCHAR ( 50 ) NOT NULL PRIMARY KEY , phone_num VARCHAR ( 15 ) NOT NULL , address VARCHAR ( 100 ) NOT NULL , city VARCHAR ( 50 ) NOT NULL ); CREATE TABLE Sell ( name VARCHAR ( 50 ) NOT NULL , drug_name VARCHAR ( 50 ) NOT NULL , price FLOAT NOT NULL , PRIMARY KEY ( name , drug_name), FOREIGN KEY ( name ) REFERENCES Pharmacy( name ), FOREIGN KEY (drug_name) REFERENCES Drug(drug_name) ); CREATE TABLE Drug ( drug_name VARCHAR ( 50 ) NOT NULL PRIMARY KEY , formula VARCHAR ( 100 ) NOT NULL ); CREATE TABLE Online_pharmacy ( name VARCHAR ( 50 ) NOT NULL PRIMARY KEY , WebURL VARCHAR ( 100 ) NOT NULL , FOREIGN KEY ( name ) REFERENCES Pharmacy( name ) ); CREATE TABLE InStore_pharmacy ( name VARCHAR ( 50 ) NOT NULL PRIMARY KEY , FOREIGN KEY ( name ) REFERENCES Pharmacy( name ) );

2. a. (10%) For the operations given below, indicate whether execution of the operation would violate some “primary key” or “integrity constraints”. If your answer is yes, specify the constraints (from the above list) that would be violated. i. Insert tuple (a1,b10, d20, 35) into R3. 1. No conflicts noticed ii. Insert tuple (s500, d20, 75) into R4.

1. Conflict, R4k references r2d. both aappear to be unique entries. There would be a tuple in R2D wit D=20. b. (15%) For the operations given below, indicate whether execution of the operation would violate any “foreign key constraints”. If your answer is yes, specify the constraints (from the above list) that would be violated. Apply “CASCADE” policy for delete operations, and apply “SET NULL” policy for update operations. Update the tables after applying those policies. (You may either redraw the tables or mention which tuples/attributes are deleted/updated at each table. For updates rewrite the updated tuples.) Make the changes on the original tables for each operation below. i. i) Delete tuple (d30, 150, 300) from R2. 1. This violates R3O , which references R2D. in using CASCADE, all tuples in R3 with O=30 would be deleted, and if SET NULL, all said tuples will have O set to NULL. ii. ii) Update tuple (s400,30,555) in R5 with values (6000,60,66) 1. Violates constraint 3, if cascade is applied all Tuples in R1 with C=s400 will be deleted, and same for SET NULL iii. iii)Update tuple (s100, 20, 555) in R5 with values (6000,60,666) 1. conflics with r4J referencing R5S. All tuples in R4 with J=s100 would be affected, deleted or set to NULL c. (5%) If all tuples in R5 are deleted, what tuples will R2 and R3 contain? i. Tupes R 1 and R4 wit C and J reespectiveely referencing R will be seet to Null. R2 and R3 will remain unchanged.