VVj3CjZU

1CREATE DATABASE IF NOT EXISTS `warehouse_database`;
2USE warehouse_database;
3
4DROP TABLE IF EXISTS `warehouses`;
5CREATE TABLE `warehouses` (
6  `warehouseNumber` int(11) NOT NULL AUTO_INCREMENT,
7  `warehouseName` varchar(50) NOT NULL,
8  `streetNumber` varchar(20) NOT NULL,
9  `streetName` varchar(50) NOT NULL,
10  `city` enum('Sydney', 'Brisbane', 'Melbourne') NOT NULL,
11  `postcode` char(4) NOT NULL,
12  `warehouseHeadname` varchar(50) DEFAULT NULL,
13  `numberEmployees` int DEFAULT 1,
14  PRIMARY KEY (`warehouseNumber`)
15);
16  
17DROP TABLE IF EXISTS `publishers`;
18CREATE TABLE `publishers` (
19  `publisherCode` int(11) NOT NULL AUTO_INCREMENT,
20  `publisherName` varchar(50) NOT NULL,
21  `publisherCity` varchar(50) NOT NULL,
22  `publisherState` varchar(50) NOT NULL,
23  `publisherEmail` varchar(50) NOT NULL,
24  PRIMARY KEY (`publisherCode`)
25);
26
27DROP TABLE IF EXISTS `authors`;
28CREATE TABLE `authors` (
29  `authorNumber` int(11) NOT NULL AUTO_INCREMENT,
30  `authorName` varchar(50) NOT NULL,
31  `authorEmail` varchar(50) DEFAULT NULL,
32  PRIMARY KEY (`authorNumber`)
33);
34
35DROP TABLE IF EXISTS `items`;
36CREATE TABLE `items` (
37  `itemCode` char(11) NOT NULL,
38  `itemTitle` varchar(50) NOT NULL,
39  `publisherCode` int(11) NOT NULL,
40  `itemType` enum('paperback', 'eBook', 'other'),authors
41  `stockPrice` decimal NOT NULL,
42  `ISBN` int(13) NOT NULL,
43  PRIMARY KEY (`itemCode`),
44  FOREIGN KEY (`publisherCode`) REFERENCES `publishers` (`publisherCode`)
45);
46
47DROP TABLE IF EXISTS `itemwriters`;
48CREATE TABLE `itemwriters` (
49  `itemCode` char(11) NOT NULL,
50  `authorNumber` int(11) NOT NULL,
51  `writerSequenceNumber` int(11) NOT NULL,
52  PRIMARY KEY (`itemCode`, `authorNumber`),
53);
54  
55DROP TABLE IF EXISTS `inventory`;
56CREATE TABLE `inventory` (
57  `itemCode` char(11) NOT NULL,
58  `warehouseNumber` int(11) NOT NULL,
59  `unitsOnHand` int NOT NULL,
60  PRIMARY KEY (`itemCode`, `warehouseNumber`)
61);