-
Notifications
You must be signed in to change notification settings - Fork 1
/
create_tables.sql
278 lines (251 loc) · 8.14 KB
/
create_tables.sql
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
/*
Team 1: Parker, Jake, and Brendan
INFO 445 AU17
description: This script will create BookPromotions and its tables.
*/
/******************create database************************/
use master
if exists (select name from sysdatabases where name='g1_BookPromotionsDB')
drop database g1_BookPromotionsDB
go
create database g1_BookPromotionsDB
go
USE g1_BookPromotionsDB
GO
/******************Editions table*************************/
create table Editions (
EditionID int
identity(1,1)
not null,
EditionName nvarchar(100)
not null,
EditionDescription nvarchar(500)
constraint pk_Editions primary key (EditionID)
);
/******************Formats table**************************/
create table Formats (
FormatID int
identity(1,1)
not null,
FormatName nvarchar(100)
not null,
FormatDescription nvarchar(500)
constraint pk_Formats primary key (FormatID)
);
/******************Books table****************************/
create table Books (
BookID int
identity(1,1)
not null,
BookTitle nvarchar(100)
not null,
BookDescription nvarchar(500)
constraint pk_Books primary key (BookID)
);
/******************ReleaseDays table**********************/
create table ReleaseDays (
ReleaseDayID int
identity(1,1)
not null,
ReleaseDayDate date
not null,
BookID int
not null,
EditionID int
not null
foreign key
references Editions(EditionID),
FormatID int
not null
foreign key
references Formats(FormatID)
constraint pk_ReleaseDays primary key (ReleaseDayID),
constraint fk_ReleaseDays_Books foreign key (BookID) references Books (BookID),
constraint fk_ReleaseDays_Editions foreign key (EditionID) references Editions (EditionID),
constraint fk_ReleaseDays_Formats foreign key (FormatID) references Formats (FormatID)
);
/******************Series table***************************/
create table Series (
SeriesID int
identity(1,1)
not null,
SeriesName nvarchar(100)
not null,
SeriesDescription nvarchar(500)
constraint pk_Series primary key (SeriesID)
);
/******************BooksSeries table*********************/
create table BooksSeries (
BooksSeriesID int
identity(1,1)
not null,
BookID int
not null,
SeriesID int
not null
constraint pk_BooksSeries primary key (BooksSeriesID),
constraint fk_BooksSeries_Books foreign key (BookID) references Books(BookID),
constraint fk_BooksSeries_Series foreign key (SeriesID) references Series(SeriesID)
);
/******************Authors table**************************/
create table Authors (
AuthorID int
identity(1,1)
not null,
AuthorFirstName nvarchar(100)
not null,
AuthorLastName nvarchar(100)
not null,
AuthorDOB date
constraint pk_Authors primary key (AuthorID)
);
/******************BooksAuthors table*********************/
create table BooksAuthors (
BooksAuthorID int
identity(1,1)
not null,
BookID int
not null,
AuthorID int
not null
constraint pk_BooksAuthors primary key (BooksAuthorID),
constraint fk_BooksAuthors_Books foreign key (BookID) references Books(BookID),
constraint fk_BooksAuthors_Authors foreign key (AuthorID) references Authors(AuthorID)
);
/******************Genres table***************************/
create table Genres (
GenreID int
identity(1,1)
not null,
GenreName nvarchar(100)
not null,
GenreDescription nvarchar(500)
constraint pk_Genres primary key (GenreID)
);
/******************GenresBooks table**********************/
create table GenresBooks (
GenreBookID int
identity(1,1)
not null,
GenreID int
not null,
BookID int
not null
constraint pk_GenresBooks primary key (GenreBookID),
constraint fk_GenresBooks_Genres foreign key (GenreID) references Genres (GenreID),
constraint fk_GenresBooks_Books foreign key (BookID) references Books (BookID)
);
/******************States table***************************/
CREATE TABLE States (
StateID int
IDENTITY(1,1)
NOT NULL,
StateAbbreviation varchar(5)
NOT NULL,
StateName varchar(100)
NOT NULL
CONSTRAINT pk_States PRIMARY KEY (StateID)
);
/******************Cities table***************************/
CREATE TABLE Cities (
CityID int IDENTITY(1,1) NOT NULL,
CityName varchar(100) NOT NULL,
StateID int NOT NULL
CONSTRAINT pk_Cities PRIMARY KEY (CityID),
CONSTRAINT fk_Cities_States FOREIGN KEY (StateID) REFERENCES States (StateID)
);
/******************Retailers table************************/
CREATE TABLE Retailers (
RetailerID int IDENTITY(1,1) NOT NULL,
RetailerName varchar(100) NOT NULL,
RetailerDesc varchar(250) NOT NULL
CONSTRAINT pk_Retailers PRIMARY KEY (RetailerID)
);
/******************Locations table************************/
CREATE TABLE Locations (
LocationID int IDENTITY(1,1) NOT NULL,
LocationName varchar(100) NOT NULL,
StreetAddress varchar(100) NOT NULL,
CityID int NOT NULL,
ZipCode varchar(10) NOT NULL,
RetailerID int NOT NULL
CONSTRAINT pk_Locations PRIMARY KEY (LocationID),
CONSTRAINT fk_Locations_Cities FOREIGN KEY (CityID) REFERENCES Cities (CityID),
CONSTRAINT fk_Locations_Retailers FOREIGN KEY (RetailerID) REFERENCES Retailers (RetailerID)
);
/******************RetailersLocations*********************/
create table RetailersLocations (
RetailerLocationID int
identity(1,1)
not null,
RetailerID int
not null,
LocationID int
not null
constraint pk_RetailersLocations primary key (RetailerLocationID),
constraint fk_RetailersLocations_Retailers foreign key (RetailerID) references Retailers(RetailerID),
constraint fk_RetailersLocations_Locations foreign key (LocationID) references Locations(LocationID)
);
/******************BooksRetailersLocations*************************/
CREATE TABLE BooksRetailersLocations (
BookRetailerLocationID int IDENTITY(1,1) NOT NULL,
BookID int NOT NULL,
RetailerLocationID int NOT NULL
CONSTRAINT pk_BooksRetailersLocations PRIMARY KEY (BookRetailerLocationID),
CONSTRAINT fk_BooksRetailersLocations_Books FOREIGN KEY (BookID) REFERENCES Books (BookID),
CONSTRAINT fk_BooksRetailersLocations_RetailersLocations FOREIGN KEY (RetailerLocationID) REFERENCES RetailersLocations (RetailerLocationID)
);
/******************DailySalesData table*******************/
CREATE TABLE DailySalesData (
DailySalesID int IDENTITY(1,1) NOT NULL,
BookRetailerLocationID int NOT NULL,
UnitsSold int NOT NULL,
DollarsSold money NOT NULL,
UtcDate date NOT NULL
CONSTRAINT pk_DailySalesData PRIMARY KEY (DailySalesID),
CONSTRAINT fk_DailySalesData_BooksRetailersLocations FOREIGN KEY (BookRetailerLocationID) REFERENCES BooksRetailersLocations (BookRetailerLocationID)
);
/******************Promos table***************************/
CREATE TABLE Promos (
PromoID int IDENTITY(1,1) NOT NULL,
PromoName varchar(150) NOT NULL,
PromoBudget money NOT NULL,
PromoDesc varchar(250) NOT NULL,
PromoStartDate date NOT NULL,
PromoEndDate date NOT NULL
CONSTRAINT pk_Promos PRIMARY KEY (PromoID)
);
/******************BooksPromos table**********************/
CREATE TABLE BooksPromos (
BookPromoID int IDENTITY(1,1) NOT NULL,
BookID int NOT NULL,
PromoID int NOT NULL
CONSTRAINT pk_BooksPromos PRIMARY KEY (BookPromoID),
CONSTRAINT fk_BooksPromos_Books FOREIGN KEY (BookID) REFERENCES Books (BookID),
CONSTRAINT fk_BookPromos_Promos FOREIGN KEY (PromoID) REFERENCES Promos (PromoID)
);
/******************Expenses table*************************/
CREATE TABLE Expenses (
ExpenseID int IDENTITY(1,1) NOT NULL,
ExpenseName varchar(100) NOT NULL,
ExpenseDesc varchar(250) NOT NULL,
BookPromoID int NOT NULL
CONSTRAINT pk_Expense PRIMARY KEY (ExpenseID),
CONSTRAINT fk_Expense_BooksPromos FOREIGN KEY (BookPromoID) REFERENCES BooksPromos (BookPromoID)
);
/******************Categories table***********************/
CREATE TABLE Categories (
CategoryID int IDENTITY(1,1) NOT NULL,
CategoryName varchar(100) NOT NULL,
CategoryDesc varchar(250) NOT NULL
CONSTRAINT pk_CategoryID PRIMARY KEY (CategoryID)
);
/******************PromosCategories table******************/
CREATE TABLE PromosCategories (
PromoCategoryID int IDENTITY(1,1) NOT NULL,
PromoID int NOT NULL,
CategoryID int NOT NULL
CONSTRAINT pk_PromoCategories PRIMARY KEY (PromoCategoryID),
CONSTRAINT fk_PromoCategories_Promo FOREIGN KEY (PromoID) REFERENCES Promos (PromoID),
CONSTRAINT fk_PromoCategories_Categories FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID)
);