· 4 years ago · Jul 12, 2021, 01:10 AM
1USE master; -- To drop a database, you must be "using" a different database
2GO
3
4DROP DATABASE IF EXISTS LAB_4_Alex_Soutas; -- Example: Lab_4_JacobCase
5CREATE DATABASE LAB_4_Alex_Soutas;
6
7USE LAB_4_Alex_Soutas;
8
9-- Create the Customers table
10DROP TABLE IF EXISTS Customers;
11CREATE TABLE Customers
12( CustomerID SMALLINT NOT NULL IDENTITY(1,1),
13 FirstName VARCHAR(25) NOT NULL,
14 MiddleName VARCHAR(25) DEFAULT NULL,
15 LastName VARCHAR(25) NOT NULL,
16 StreetAddress VARCHAR(35) NOT NULL,
17 City VARCHAR(25) NOT NULL,
18 State CHAR(2) NOT NULL,
19 ZipCode VARCHAR(10) NOT NULL,
20 CONSTRAINT PK_Customers_CustomerID PRIMARY KEY ( CustomerID )
21);
22
23
24-- Create the CustomerEmails table
25DROP TABLE IF EXISTS CustomerEmails;
26CREATE TABLE CustomerEmails
27( CustomerID SMALLINT NOT NULL,
28 EmailAddress VARCHAR(254) NOT NULL,
29 PrimaryEmailFlag CHAR(1) NOT NULL DEFAULT 'Y',
30 CONSTRAINT PK_CustomerEmails_CustomerID_EmailAddress PRIMARY KEY ( CustomerID, EmailAddress ),
31 CONSTRAINT FK_CustomerEmails_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES Customers ( CustomerID ),
32 CONSTRAINT CHECK_CustomerEmails_PrimaryEmailFlag CHECK ( PrimaryEmailFlag IN ( 'Y', 'N' ) )
33);
34
35
36-- Create the Employees table
37DROP TABLE IF EXISTS Employees;
38CREATE TABLE Employees
39( EmployeeID SMALLINT NOT NULL IDENTITY(1,1),
40 SSN SMALLINT NOT NULL,
41 FirstName VARCHAR(30) NOT NULL,
42 MiddleName VARCHAR(30) DEFAULT NULL,
43 LastName VARCHAR(30) NOT NULL,
44 StreetAddress VARCHAR(35) NOT NULL,
45 City VARCHAR(30) NOT NULL,
46 [State] CHAR(2) NOT NULL,
47 ZipCode VARCHAR(10) NOT NULL,
48 Title VARCHAR(25) NOT NULL,
49 Salary DECIMAL(8,2) NOT NULL,
50 CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY ( EmployeeID )
51);
52
53
54-- Create the Orders table
55DROP TABLE IF EXISTS Orders;
56CREATE TABLE Orders
57( OrderID INT NOT NULL IDENTITY(1,1),
58 EmployeeID SMALLINT NOT NULL,
59 CustomerID SMALLINT NOT NULL,
60 OrderDate DATE NOT NULL,
61 OrderTime TIME NOT NULL,
62 CONSTRAINT PK_Orders_OrderID PRIMARY KEY ( OrderID ),
63 CONSTRAINT FK_Orders_EmployeeID FOREIGN KEY ( EmployeeID ) REFERENCES Employees ( EmployeeID ),
64 CONSTRAINT FK_Orders_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES Customers ( CustomerID )
65);
66
67
68-- Create the Statuses table
69DROP TABLE IF EXISTS Statuses;
70CREATE TABLE Statuses
71( StatusID TINYINT NOT NULL IDENTITY(1,1),
72 StatusName VARCHAR(25) NOT NULL,
73 StatusDescription VARCHAR(100) NOT NULL,
74 CONSTRAINT PK_Statuses_StatusID PRIMARY KEY ( StatusID )
75);
76
77
78-- Create the OrderStatuses table
79DROP TABLE IF EXISTS OrderStatuses;
80CREATE TABLE OrderStatuses
81( OrderID INT NOT NULL,
82 StatusID TINYINT NOT NULL,
83 StatusDate DATE NOT NULL,
84 StatusTime TIME NOT NULL,
85 CONSTRAINT PK_OrderStatuses_OrderID_StatusID PRIMARY KEY ( OrderID, StatusID ),
86 CONSTRAINT FK_OrderStatuses_OrderID FOREIGN KEY ( OrderID ) REFERENCES Orders ( OrderID ),
87 CONSTRAINT FK_OrderStatuses_StatusID FOREIGN KEY ( StatusID ) REFERENCES Statuses ( StatusID )
88);
89
90
91-- Create the Dependents table
92DROP TABLE IF EXISTS Dependents;
93CREATE TABLE Dependents
94( EmployeeID INT NOT NULL,
95 DependentNumber INT NOT NULL,
96 FirstName VARCHAR(25) NOT NULL,
97 LastName VARCHAR(25) NOT NULL,
98 BirthDate DATE NOT NULL,
99 SSN INT(10) NOT NULL,
100 CONSTRAINT PK_Dependents_EmployeeID PRIMARY KEY ( EmployeeID ),
101 CONSTRAINT FK_Dependents_EmployeeID_DependentNumber FOREIGN KEY ( EmployeeID, DependentNumber )
102
103);
104
105-- Create the OrderLines table
106DROP TABLE IF EXISTS OrderLines;
107CREATE TABLE OrderLines
108( OrderID INT NOT NULL,
109 ProductID INT NOT NULL,
110 Price DECIMAL NOT NULL,
111 Quantity SMALLINT NOT NULL,
112CONSTRAINT PK_OrderLines_OrderID_ProductID PRIMARY KEY ( OrderID, ProductID ),
113CONSTRAINT FK_OrderLines_OrderID_ProductID FOREIGN KEY ( OrderID, ProductID )
114
115);
116
117-- Create the Products table
118DROP TABLE IF EXISTS Products;
119CREATE TABLE Products
120( ProductID SMALLINT NOT NULL,
121 ProductName VARCHAR(25) NOT NULL,
122 ProductDescription VARCHAR(255) NOT NULL,
123 SalesPrice DECIMAL NOT NULL,
124 InventoryOnHand SMALLINT NOT NULL,
125CONSTRAINT PK_Products_ProductID PRIMARY KEY ( ProductID )
126);
127
128-- Create the ProductPriceHistory table
129DROP TABLE IF EXISTS ProductPriceHistory;
130CREATE TABLE ProductPriceHistory
131(
132
133);
134
135-- Create the ProductVendors table
136DROP TABLE IF EXISTS ProductVendors;
137CREATE TABLE ProductVendors
138(
139
140);
141
142-- Create the Vendors table
143DROP TABLE IF EXISTS Vendors;
144CREATE TABLE Vendors
145(
146
147);
148
149