A hospital employs surgeons who operate on patients. Patients and surgeons are described by (unique) id's (integers), names and addresses. Each patient undergoes a single operation that is scheduled on a particular day (of type DATE) and performed by a single surgeon. A surgeon can only perform one operation a day.
i. Give an ER diagram that describes this enterprise.
ii. Translate your diagram into a schema using SQL. Include all primary and foreign
key constraints.
Solution:
i. 
ii.
CREATE TABLE Patient (
Id INTEGER,
Name CHAR(20),
Address CHAR (100),
PRIMARY KEY Id )
CREATE TABLE Surgeon (
Id INTEGER,
Name CHAR(20),
Address CHAR(100),
PRIMARY KEY Id )
CREATE TABLE Operation (
Pid INTEGER,
Sid INTEGER,
Date DATE,
PRIMARY KEY Pid,
UNIQUE (Sid, Date),
FOREIGN KEY (Pid) REFERENCES Patient(Id),
FOREIGN KEY (Sid) REFERENCES Surgeon(Id))
CREATE ASSERTION FullPatientParticipation
CHECK (NOT EXISTS(SELECT P.Id FROM Patient P
EXCEPT
SELECT O.Id FROM Operation O ))
The assertion could be replaced by the following constraint.
FOREIGN KEY Id REFERENCES Operation Pid
You might also like to view...
The Insert WordArt command can be found under the ________ tab
Fill in the blank(s) with correct word
After you convert the text to fills, you can still edit the fills as text.
Answer the following statement true (T) or false (F)
The World Wide Web was completed and made available to all researchers at CERN in August 1991, the date that marks the birth of the web.
Answer the following statement true (T) or false (F)
____________ is an SQL standard that is an alternative to the ANSI/ISO standard.
a. X/OPEN b. SAA c. FIPS d. ODBC e. All of these are alternatives to the ANSI/ISO standard.