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 ISBN
TYPE CHAR(10)

CHECK( VALUE BETWEEN '0000000000' AND '9999999999').

CREATE DOMAIN SEX
TYPE CHAR(1)

CHECK ( VALUE IN ('M','F'))

CREATE DOMAIN PHONE
TYPE INTEGER

CHECK ( VALUE > 999999999 AND VALUE < 10000000000 )

CREATE TABLE Book (

ISBN ISBN
TYPE,

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 SEX
TYPE,

PRIMARY KEY (AName)

)

CREATE TABLE Publisher (

PName CHAR(60),

Address CHAR(60),

PRIMARY KEY (PName)

)

CREATE TABLE Publisher
Phone (

PName CHAR(60),

Phone PHONE
TYPE,

PRIMARY KEY (PName, Phone),

FOREIGN KEY (PName) REFERENCES Publisher

)

CREATE TABLE Wrote (

ISBN ISBN
TYPE,



AName CHAR(60),

PRIMARY KEY (ISBN, AName),

FOREIGN KEY (ISBN) REFERENCES Books,

FOREIGN KEY (AName) REFERENCES Author

)