Question

You are required to create a database for a fictitious Online clothes store called ClothesInc (think David Jones or Zara) for Task 1. Write an SQL script that builds a database to match the relational model below. These SQL statements in the script must be provided in the correct order. The solution will be demonstrated in one or more SQLite screen snapshots that include the Execute SQL tab SQL code and the results of the executed statements.

The ClothesInc relational model is as follows: Note: Primary keys are denoted by bold and underline and foreign keys are in italics.
Store (storeId, sName, sAddress, sPostCode)
StoreEmployee (employeeId, eName, eAddress, ePostCode, eEmail, mobPh, storeId)
Customer (customerId, cName, cMobilePh, cEmail, cStartDate)
Order (orderId, oDate, total, GST, deliveryAddress, orderStatus, customerId )
OrderDetail (orderId, productId, quantity, retailPrice)
Payment (paymentId, type, amount, pDate, bankTransactNo, orderId)
Product (productId, size, colour, style, quantityOnHand, reorderQty, retailPrice, supplierId)
ProductSupplier (productId, supplierId)
Supplier (supplierId, supplierName)
FOREIGN KEYS
StoreEmployee(storeId) references Store (storeId)
Order(customerid) references Customer(customerid)
OrderDetail (orderId) references Order (orderId)
OrderDetail (productId) references Product (productId)
Payment (orderId) references Order (orderID)
Product (supplierId) references Supplier (supplierId)
ProductSupplier (productId) references Product (productId)
ProductSupplier (supplierId) references Supplier (supplierId)
Other Constraints and Remarks
All primary and foreign key attributes are strings comprising five
All PostCodes are strings comprising four digits.
INTEGER type must be used for total, GST, retailPrice, quantityOnHand, reorderQty, and amount.
TEXT type must be used for other attributes
sName, eName, cName, sName must contain a value.

Answer 1

The answer provides an SQL script to create a database for a fictional online clothes store in accordance with the provided relational model, including table creation with primary and foreign key constraints.

Step-by-step explanation:

To create a database for an online clothes store called ClothesInc, we will follow the relational model provided and write the appropriate SQL script to set up the database schema:

SQL Script for Creating Database

ClothesInc Database Setup:

1. Create Tables with Primary Keys

CREATE TABLE Store (
storeId TEXT PRIMARY KEY,
sName TEXT NOT NULL,
sAddress TEXT,
sPostCode TEXT
);

CREATE TABLE StoreEmployee (
employeeId TEXT PRIMARY KEY,
eName TEXT,
eAddress TEXT,
ePostCode TEXT,
eEmail TEXT,
mobPh TEXT,
storeId TEXT,
FOREIGN KEY (storeId) REFERENCES Store (storeId)
);

CREATE TABLE Customer (
customerId TEXT PRIMARY KEY,
cName TEXT,
cMobilePh TEXT,
cEmail TEXT,
cStartDate TEXT
);

CREATE TABLE Order (
orderId TEXT PRIMARY KEY,
oDate TEXT,
total INTEGER,
GST INTEGER,
deliveryAddress TEXT,
orderStatus TEXT,
customerId TEXT,
FOREIGN KEY (customerId) REFERENCES Customer (customerId)
);

CREATE TABLE OrderDetail (
orderId TEXT,
productId TEXT,
quantity INTEGER,
retailPrice INTEGER,
PRIMARY KEY (orderId, productId),
FOREIGN KEY (orderId) REFERENCES Order (orderId),
FOREIGN KEY (productId) REFERENCES Product (productId)
);

CREATE TABLE Payment (
paymentId TEXT PRIMARY KEY,
type TEXT,
amount INTEGER,
pDate TEXT,
bankTransactNo TEXT,
orderId TEXT,
FOREIGN KEY (orderId) REFERENCES Order (orderId)
);

CREATE TABLE Product (
productId TEXT PRIMARY KEY,
size TEXT,
colour TEXT,
style TEXT,
quantityOnHand INTEGER,
reorderQty INTEGER,
retailPrice INTEGER,
supplierId TEXT,
FOREIGN KEY (supplierId) REFERENCES Supplier (supplierId)
);

CREATE TABLE Supplier (
supplierId TEXT PRIMARY KEY,
supplierName TEXT
);

CREATE TABLE ProductSupplier (
productId TEXT,
supplierId TEXT,
PRIMARY KEY (productId, supplierId),
FOREIGN KEY (productId) REFERENCES Product (productId),
FOREIGN KEY (supplierId) REFERENCES Supplier (supplierId)
);

1. Create Relationships Between Tables Using Foreign Keys

As shown in the SQL code above, the foreign key relationships between tables have been established using the FOREIGN KEY clause within the table creation statements for each table that has foreign key dependencies.

Ensure that the data types and constraints are in accordance with the provided relational model, particularly that primary keys and foreign keys are strings and that certain integer types are used as specified.