Translate the above diagram into the relational model by supplying the appropriate CREATE TABLE statements. Note that ISBN is a 10-digit string (which can have leading zeros), sex can have only two values, 'M' or 'F', and a phone number is a 10 digit number that never starts with a zero. Specify these as domains.
Specify all the key and foreign key constraints. Try to preserve as many participation
constraints as possible. List all the participation constraints that are present in the E-R
diagram, but not in its translation to SQL.
CREATE DOMAIN ISBNTYPE CHAR(10)
CHECK( VALUE BETWEEN '0000000000' AND '9999999999').
CREATE DOMAIN SEXTYPE CHAR(1)
CHECK ( VALUE IN ('M','F'))
CREATE DOMAIN PHONETYPE INTEGER
CHECK ( VALUE > 999999999 AND VALUE < 10000000000 )
CREATE TABLE Book (
ISBN ISBNTYPE,
Title CHAR(60),
PublicationDate DATE,
PName CHAR(60) NOT NULL,
PRIMARY KEY (ISBN),
FOREIGN KEY (PName) REFERENCES Publisher
)
CREATE TABLE Author (
AName CHAR(60),
DOB DATE,
Sex SEXTYPE,
PRIMARY KEY (AName)
)
CREATE TABLE Publisher (
PName CHAR(60),
Address CHAR(60),
PRIMARY KEY (PName)
)
CREATE TABLE PublisherPhone (
PName CHAR(60),
Phone PHONETYPE,
PRIMARY KEY (PName, Phone),
FOREIGN KEY (PName) REFERENCES Publisher
)
CREATE TABLE Wrote (
ISBN ISBNTYPE,
AName CHAR(60),
PRIMARY KEY (ISBN, AName),
FOREIGN KEY (ISBN) REFERENCES Books,
FOREIGN KEY (AName) REFERENCES Author
)
You might also like to view...
Which is not a process ink color?
A. green B. cyan C. magenta D. yellow
Properties and methods belonging to each object instantiated from a class are known as ____.
A. instance properties and instance methods B. validated properties and validated methods C. compiled properties and compiled methods D. interpreted properties and interpreted methods
With a(n) _________ scan, if the port is closed, the response is an RST. If the port is open, the response is a SYN/ACK
a. FIN b. XMAS c. SYN d. ACK
Which aggregate function is used to find the average of values in a field?
A. Avg B. Average C. Mean D. Mode