Consider the following enterprise, which includes books, authors and publishers. Authors are people with normal attributes, like name, date of birth, etc., but in addition they wrote one or more books. A book has the usual attributes, such as title, ISBN, publication date, etc. Publishers are companies that publish books. They have an address, phone numbers (typically more than one), name, etc.

Consider the following enterprise, which includes books, authors and publishers. Authors are
people with normal attributes, like name, date of birth, etc., but in addition they wrote one
or more books. A book has the usual attributes, such as title, ISBN, publication date, etc.
Publishers are companies that publish books. They have an address, phone numbers (typically
more than one), name, etc.

(a) Represent the above as an E-R diagram; include all relevant constraints.

(b)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.

SOLUTION:

(a) Assumptions: the author name is unique and the publisher name is unique.

E R diagram:

(b)




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


)

Participation constraints not present in SQL:

i. Author has to write at least one book.

ii. Book has to be written by at least one author.

Note that we could have combined Publisher and Publisher Phone in one table

(by just adding the attribute phone). But then the referential integrity constraint that

Book.pname references Publisher.pname will no longer be a foreign key constraint

in Book, because pname will no longer be a key of Publisher (the key will be (pname,

phone)). So, we would have to use an assertion to express this more general inclusion

dependency.