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