Creating a Database with SQL script. What would the database look like for the f

Question

Creating a Database with SQL script. What would the database look like for the following relational model?

OKTOMOOK RELATIONAL MODEL

Branch (branchNumber, branchName, streetNo, streetName, branchCity, branchState, numberEmployees)

Publisher (publisherCode, publisherName, publisherCity, publisherState)

Author (authorID, firstName, lastName)

Book (ISBN, title, publisherCode, genre, retailPrice, paperback)

Wrote (ISBN, authorID, sequenceNumber)

Inventory (ISBN, branchNumber, quantityInStock)

FOREIGN KEYS

• Book(publisherCode) is dependent on Publisher (publisherCode)

• Wrote (ISBN) is dependent on Book (ISBN)

• Wrote (authorID) is dependent on Author (authorID)

• Inventory (ISBN) is dependent on Book (ISBN)

• Inventory (branchNumber) is dependent on Branch (branchNumber)

OTHER CONSTRAINTS

• The domain of Publisher(state) is [QLD, VIC, NSW, WA, TAS, NT, SA].

• The domain of Book(genre) is [Non-Fiction, Science Fiction, Fantasy, Crime, Mystery, Young Adult, Romance, General Fiction]

• ISBN must be a 13-digit number and may begin with a zero.

• The publisher name and book title are both mandatory.

• Paperback must be either True or False.

• The default quantity in stock is 0.

Explanation / Answer

Hi,

CREATE TABLE BRANCH(BRANCHNUMBER VARCHAR(10) PRIMARY KEY,BRANCHNAME VARCHAR(20) , STREETNO VARCHAR(10), STREETNAME VARCHAR(20), BRANCHCITY VARCHAR(15), BRANCHSTATE VARCHAR(10), NUMBEREMPLOYEES NUMBER(10));

CREATE TABLE PUBLISHER(PUBLISHERCODE VARCHAR(10) PRIMARY KEY, PUBLISHERNAME VARCHAR(10), PUBLISHERCITY VARCHAR(10), PUBLISHERSTATE VARCHAR(10), CHECK(PUBLISHERSTATE IN('QLD', 'VIC', 'NSW', 'WA', 'TAS', 'NT','SA'))

);

CREATE TABLE AUTHOR(AUTHORID VARCHAR(10) PRIMARY KEY, FIRSTNAME VARCHAR(10), LASTNAME(20));

CREATE TABLE BOOK(ISBN VARCHAR(20) PRIMARY KEY, TITLE VARCHAR(10) NOT NULL, PUBLISHERCODE VARCHAR(10) REFERENCES PUBLISHER(PUBLISHERCODE) NOT NULL, GENRE VARCHAR(10), RETAILPRICE NUMBER(10,2), PAPERBACK VARCHAR(10), CHECK(GENRE IN('Non-Fiction', 'Science Fiction', 'Fantasy', 'Crime', 'Mystery', 'Young Adult', 'Romance', 'General Fiction')), CHECK(LENGTH(ISBN)=13 AND SUBSTR(ISBN,1,1) BETWEEN '0' AND '9'),CHECK(PAPERBACK IN('TRUE','FALSE'))

);

CREATE TABLE WROTE(ISBN VARCHAR(20) REFERENCES BOOK(ISBN), AUTHORID VARCHAR(10) REFERENCES AUTHOR(AUTHORID),SEQUENCENUMBER NUMBER(20));

CREATE TABLE INVENTORY(ISBN VARCHAR(20) REFERENCES BOOK(ISBN), BRANCHNUMBER VARCHAR(10) REFERENCES BRANCH(BRANCHNUMBER), QUANTITYINSTOCK NUMBER(10) DEFAULT 0);

Thanks and Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.