P1-ntlarissa/createDB.sql at master · 220321-NET/P1-ntlarissa · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
CREATE PROCEDURE dropAllTable
AS
	DROP TABLE Inventories, Orders, CustomerAddress, Products, Customers, Managers, StoreFront, Addresses
GO

CREATE PROCEDURE createAllTable
AS
CREATE TABLE Customers (
	customerID INT PRIMARY KEY IDENTITY(1, 1),
	customerFirstName VARCHAR(50) NOT NULL,
	customerLastName VARCHAR(50) NOT NULL,
	customerUserName VARCHAR(50) NOT NULL UNIQUE,
	customerPassword VARCHAR(70) NOT NULL
)
INSERT INTO Customers(customerFirstName, customerLastName, customerUserName, customerPassword) VALUES
('Lara', ' Tchani', 'l.tchani37@gmail.com', 'User1234');

CREATE TABLE Addresses (
	addressID INT PRIMARY KEY IDENTITY(1, 1),
	addressLine1 VARCHAR(50)  NULL,
	addressLine2 VARCHAR(50),
	addressCity VARCHAR(50)  NULL,
	addressState VARCHAR(50)  NULL,
	addressCountry VARCHAR(50)  NULL,
	addressZipCode VARCHAR(50)  NULL,
)
INSERT INTO Addresses(addressLine1, addressLine2, addressCity, addressState, addressCountry,addressZipCode) VALUES
('11160 VEIRS MILL RD', ' SUITE DPT4', 'Wheaton-Glenmont', 'MD','USA', '20902');

CREATE TABLE CustomerAddress (
	customerAddressID INT PRIMARY KEY IDENTITY(1, 1),
    modifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
	addressID INT FOREIGN KEY REFERENCES Addresses(addressID) ON DELETE CASCADE,
	customerID INT FOREIGN KEY REFERENCES Customers(customerID) ON DELETE CASCADE,
)


CREATE TABLE StoreFront (
	storeID INT PRIMARY KEY IDENTITY(1, 1),
	storeName VARCHAR(50) NOT NULL,
	addressLine1 VARCHAR(50)  NULL,
	addressLine2 VARCHAR(50),
	addressCity VARCHAR(50)  NULL,
	addressState VARCHAR(50)  NULL,
	addressCountry VARCHAR(50)  NULL,
	addressZipCode VARCHAR(50)  NULL,
)

INSERT INTO StoreFront(storeName, addressLine1, addressLine2, addressCity, addressState, addressCountry,addressZipCode) VALUES
('Costco', '11160 VEIRS MILL RD', ' SUITE DPT4', 'Wheaton-Glenmont', 'MD','USA', '20902');
CREATE TABLE Products (
	productID INT PRIMARY KEY IDENTITY(1, 1),
	productQuantity FLOAT  NULL,
	productPrice FLOAT  NULL,
	productRef VARCHAR(max)  NULL,
	productName VARCHAR(50) NOT NULL,
	storeID INT FOREIGN KEY REFERENCES StoreFront(storeID),
)

CREATE TABLE Orders (
	orderID INT PRIMARY KEY IDENTITY(1, 1),
    orderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
	quantity FLOAT  NULL,
	price FLOAT  NULL,
	total FLOAT  NULL,
	orderRef VARCHAR(50)  NULL,
	productRef VARCHAR(max)  NULL,
	productName VARCHAR(50) NOT NULL,
	productID INT FOREIGN KEY REFERENCES Products(productID) ON DELETE CASCADE,
	customerID INT FOREIGN KEY REFERENCES Customers(customerID) ON DELETE CASCADE,
)
CREATE TABLE Managers (
	managerID INT PRIMARY KEY IDENTITY(1, 1),
	managerFirstName VARCHAR(50) NOT NULL,
	managerLastName VARCHAR(50) NOT NULL,
	managerUserName VARCHAR(50) NOT NULL UNIQUE,
	managerPassword VARCHAR(70) NOT NULL,
	storeID INT FOREIGN KEY REFERENCES StoreFront(storeID) ON DELETE CASCADE,
)

INSERT INTO Managers(managerFirstName, managerLastName, managerUserName, managerPassword, storeID) VALUES
('Lara', ' Tchani', 'l.tchani37@gmail.com', 'Admin1234',1);

CREATE TABLE Inventories (
	inventoryID INT PRIMARY KEY IDENTITY(1, 1),
    inventoryDate DATETIME DEFAULT CURRENT_TIMESTAMP,
	quantity FLOAT  NULL,
	productID INT FOREIGN KEY REFERENCES Products(productID) ON DELETE CASCADE,
	managerID  INT FOREIGN KEY REFERENCES Managers(managerID ) ON DELETE CASCADE,
)

GO

 EXEC dropAllTable
 EXEC createAllTable