Task 1 [6 marks] Oktomook Book Store Database A SQL script is a set of SQL comma

Question

Task 1 [6 marks] Oktomook Book Store Database A SQL script is a set of SQL commands saved as a SQL file. If you are already running MySQL, you can execute a SQL script file using the source command or you can import it in Workbench. Write an SQL script that builds a database to match the relational model provided to you. These SQL statements in the script must be provided in the correct order. Marks will be awarded for the following: 1. Creating the database (1 mark) 2. Successfully creating new tables (1 mark) 3. Including all attributes (1 mark) 4. Including constraints (1 mark) 5. Correctly creating Primary Keys (1 mark) 6. Correctly creating Foreign Keys (1 mark) You are required to create a database for the fictitious book store Oktomook for Task 1. The database is based on the model below:

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)

3 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

# create a data base

CREATE DATABASE OctomookRelationalModel;

#use the database for further operation

USE OctomookRelationalModel;

#Create table Branch

#for string input I am using varchar2.You can also use varchar & for integer input I take INT.

#NOT NULL is a constraint, used to describe that This field cannot be null.

CREATE TABLE Branch

(

brachNumber varchar2(255) NOT NULL,

branchName varchar2(255) NOT NULL,

streetNo INT NOT NULL,

streetName varchar2(255) NOT NULL,

branchCity varchar2(255) NOT NULL,

branchState varchar2(255) NOT NULL,

numberEmployees INT NOT NULL,

PRIMARY KEY(branchNumber)

);

#create table Publisher

#domain constraint is used as ENUM command

CREATE TABLE Publisher

(

publisherCode varchar2(255) NOT NULL,

publisherName varchar2(255) NOT NULL,

publisherCity varchar2(255) NOT NULL,

publisherState varchar2(255) ENUM(“QLD”,”VIC”,”NSW”,”WA”,”TAS”,”NT”,”SA”),

PRIMARY KEY(publisherCode)

);

#create Table Author

CREATE TABLE Author

(

authorID INT NOT NULL,

firstName varchar2(255) NOT NULL,

lastName varchar2(255) NOT NULL,

PRIMARY KEY(authorID)

);

#create table Book

# to add foreign key constraint I wrote FOREIGN KEY(keyName) references ReferanceTableName(PrimaryKey)

#check constraint used

#domain constraint used as ENUM command

CREATE TABLE Book

(

ISBN varchar2(255) NOT NULL,

title varchar2(255) NOT NULL,

publisherCode varchar2(255) NOT NULL,

genre varchar2(255) ENUM (“Non-Fiction”,”Science Fiction”,”Fantacy”,”Crime”,”Mystery”,”Young Adult”,”Romance”,”General Fiction”),

retailPrice INT NOT NULL,

paperback varchar2(255) ENUM (“True”,”False”),

PRIMARY KEY(ISBN),

FOREIGN KEY(publisherCode) references Publisher(publisherCode),

CHECK( ISBN=13)

);

#create table wrote

# to add foreign key constraint I wrote FOREIGN KEY(keyName) references ReferanceTableName(PrimaryKey)

CREATE TABLE Wrote

(

ISBN varchar2(255) NOT NULL,

authorID INT NOT NULL,

sequenceNumber varchar2(255) NOT NULL,

PRIMARY KEY (sequenceNumber),

FOREIGN KEY(ISBN) references Book(ISBN),

FOREIGN KEY(authorID) references Author(authorID),

CHECK( ISBN=13)

);

#create Table inventory

# for default constraint I used “DEFAULT” command.

CREATE TABLE Inventory

(

ISBN varchar2(255) NOT NULL,

branchNumber varchar2(255) NOT NULL,

quantityInStock INT NOT NULL DEFAULT 0,

PRIMARY KEY(quantityInStock)

FOREIGN KEY(ISBN) references Book(ISBN),

FOREIGN KEY(branchNumber) references Branch(branchNumber)

CHECK( ISBN=13)

);

So,All Questions Answered:

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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