Task 1 [6 marks] Warehouse Database A SQL script is a set of SQL commands saved

Question

Task 1 [6 marks] Warehouse 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)

For task 1, you are required to create a database for the fictitious online book store, Oktomook. This store operates from many warehouse locations. The database is based on the model below:

Warehouse relational model

Warehouse (warehouseNumber, warehouseName, streetNumber, streetName, city, suburb, state, postcode, warehouseHeadName, numberEmployees)

Publisher (publisherCode, publisherName, publisherCity, publisherState, publisherEmail)

Author (authorNumber, authorName, authorEmail)

Item (itemCode, itemTitle, publisherCode, itemType, stockPrice, ISBN)

ItemWriters (itemCode, authorNumber, writerSequenceNumber)

Inventory (itemCode, warehouseNumber, unitsOnHand)

FOREIGN KEYS

Item(publisherCode) is dependent on Publisher(publisherCode)

ItemWriters(authorNumber) is depending on Author(authorNumber)

ItemWriters(itemCode) is dependent on Item(itemCode)

Inventory(itemCode) is depending on Item(itemCode)

Inventory(warehouseNumber) is dependent on Warehouse(warehouseNumber)

OTHER CONSTRAINTS

Warehouses are located in the following cities: Sydney, Brisbane, Melbourne

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

The publisher name and item title are both mandatory

The default number of employees quantity is 1

itemCode may be a combination of letters and number.

The possible item types are: paperback, eBook, other

Explanation / Answer

Creating Database->

create database Oktomook;

use Oktomook;

Creating table Warehouse->

create table Warehouse(warehouseNumber int not null,

warehouseName varchar(50),

streetNumber int ,

streetName varchar(30),

city varchar(30) check(city in('Sydney','Brishbane','Melbourne')),

subrub varchar(30),

state varchar(30),

postCode int,

warehouseHeadName varchar(40),

numberEmployee int default 1,

primary key(warehouseNumber)

);

Creating table Publisher->

create table Publisher(publisherCode int not null,

publisherName varchar(50) not null,

publisherCity varchar(30),

PublisherState varchar(30),

postCode int,

publisherEmail varchar(40),

primary key(publisherCode)

);

Creating table Author->

create table Author(authorNumber int not null,

authorName varchar(50),

authorEmail varchar(40),

primary key(authorNumber)

);

Creating table Item->

create table Item(itemCode varchar(20) not null,

itemTitle varchar(50) not null,

publisherCode int,

itemType varchar(30) check(itemType in ('paperback','eBook','other')),

stockPrice int,

ISBN varchar(13) check(length(ISBN)=13),

primary key(itemCode),

foreign key(publisherCode) references publisher

);

Creating table ItemWriters->

create table ItemWriter(itemCode varchar(20) not null,

authorNumber int not null,

writerSequenceNumber int,

foreign key(itemCode) references Item(itemCode),

foreign key (authorNumber) references Author(authorNumber)

);

Creating table Invetory->

create table Inventory(itemCode varchar(20) not null,

warehouseNumber int not null,

unitsOnHand int,

foreign key(itemCode) references Item(itemCode),

foreign key (authorNumber) references WareHouse(warehouseNumber)

);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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