{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}


354.3-08.SampleFinalExam - CMPT-354 D1 Fall 2008 Instructor...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
CMPT-354 D1 Fall 2008 Instructor: Martin Ester TA: Gustavo Frigo Sample Final Exam Introduction The first part of the final exam will cover the same topics as the midterm exam. This sample final exam therefore covers only the second part of the final exam that goes beyond the topics of the midterm exam. Problem 1 Translate the following ER diagram of a pharma database into an equivalent relational schema. Pharmacy Drug Sells Makes age Person address name sin trade_name Prescription Doctor ISA specialty Patient Patient AND Doctor COVERS Person Patient NOT OVERLAPS Doctor Pharma_ company formula date quantity comp_ name price name address
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Your relational schema should satisfy the following two design criteria: The number of tables should be minimal. As many integrity constraints from the ER diagram as possible should be expressed. a) Write down the complete SQL statements to create the relational schema, including PRIMARY KEY, FOREIGN KEY and NOT NULL constraints. For the FOREIGN KEY constraints, you do not need to specify the reactions to violating updates. CREATE TABLE Patient ( sin INTEGER, name CHAR (20), address CHAR(20), age INTEGER, PRIMARY KEY (sin)); CREATE TABLE Doctor ( sin INTEGER, name CHAR (20), address CHAR(20), specialty CHAR(20), PRIMARY KEY (sin)); CREATE TABLE Prescription ( patient_sin INTEGER, doctor_sin INTEGER, drug_company CHAR(20), drug_name CHAR(20), date DATE, quantity INTEGER, PRIMARY KEY (patient_sin, doctor_sin, drug_company, drug_name), FOREIGN KEY (patient_sin) REFERENCES Patient, FOREIGN KEY (doctor_sin) REFERENCES Doctor, FOREIGN KEY (drug_company, drug_name) REFERENCES Drug); CREATE TABLE Drug ( comp_name CHAR(20), trade_name CHAR(20), formula CHAR(20), PRIMARY KEY (comp_name, trade_name), FOREIGN KEY (comp_name) REFERENCES Pharm_company); CREATE TABLE Pharm_company (comp_name CHAR(20), PRIMARY KEY (comp_name); CREATE TABLE Pharmacy (name CHAR(20), Address CHAR(20), PRIMARY KEY (name));
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}