-
Notifications
You must be signed in to change notification settings - Fork 1
/
backpackerSQL.txt
128 lines (110 loc) · 4.54 KB
/
backpackerSQL.txt
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
CREATE TABLE Account (
AccountId int NOT NULL AUTO_INCREMENT,
name varchar (40) NOT NULL,
username varchar (40) NOT NULL,
password varchar(40) NOT NULL ,
email varchar(40) NOT NULL,
PRIMARY KEY (AccountId))
;
CREATE TABLE Gear (
GearId int NOT NULL AUTO_INCREMENT,
GearType varchar(40) NOT NULL,
name varchar(40) NOT NULL,
weight decimal,
rating int,
PRIMARY KEY (GearId)
);
CREATE TABLE Location (
LocationId int NOT NULL AUTO_INCREMENT ,
name varchar(40) NOT NULL,
description varchar(200),
PRIMARY KEY (LocationId)
);
CREATE TABLE Trip(
TripId int NOT NULL AUTO_INCREMENT,
name varchar(40) NOT NULL,
description varchar(200),
startdate DATE,
enddate DATE,
AccountId int NOT NULL,
PRIMARY KEY (TripId),
CONSTRAINT `AccountTrip` FOREIGN KEY (`AccountId`) REFERENCES `backpacker`.`account`(`AccountId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE City (
CityId int NOT NULL AUTO_INCREMENT,
name varchar(40),
PRIMARY KEY (CityId)
);
CREATE TABLE State (
StateId int NOT NULL AUTO_INCREMENT,
name varchar(40),
PRIMARY KEY (StateId)
);
CREATE TABLE Country (
CountryId int NOT NULL AUTO_INCREMENT,
name varchar(40),
PRIMARY KEY (CountryId)
);
CREATE TABLE List (
ListId int NOT NULL AUTO_INCREMENT,
name varchar(40) NOT NULL,
AccountId int NOT NULL,
PRIMARY KEY (ListId),
CONSTRAINT `AccountList` FOREIGN KEY (`AccountId`) REFERENCES `backpacker`.`account`(`AccountId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE GearList (
ListId int NOT NULL,
GearId int NOT NULL,
PRIMARY KEY (GearId,ListId),
CONSTRAINT `ListGearList` FOREIGN KEY (`ListId`) REFERENCES `backpacker`.`List` (`ListId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `GearGearList` FOREIGN KEY (`GearId`) REFERENCES `backpacker`.`Gear` (`GearId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE StateCountry (
StateId int NOT NULL,
CountryId int NOT NULL,
PRIMARY KEY (StateId,CountryId),
CONSTRAINT `StateSC` FOREIGN KEY (`StateId`) REFERENCES `backpacker`.`State` (`StateId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `CountrySC` FOREIGN KEY (`CountryId`) REFERENCES `backpacker`.`Country` (`CountryId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE CityState (
StateId int NOT NULL,
CityId int NOT NULL,
PRIMARY KEY (CityID,StateId),
CONSTRAINT `StateCS` FOREIGN KEY (`StateId`) REFERENCES `backpacker`.`State` (`StateId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `CityCS` FOREIGN KEY (`CityId`) REFERENCES `backpacker`.`City` (`CityId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE LocationCity (
LocationId int NOT NULL,
CityId int NOT NULL,
PRIMARY KEY (LocationId,CityId),
CONSTRAINT `CityLC` FOREIGN KEY (`CityId`) REFERENCES `backpacker`.`City` (`CityId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `LocationLC` FOREIGN KEY (`LocationId`) REFERENCES `backpacker`.`Location` (`LocationId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE GearAccount (
AccountId int NOT NULL,
GearId int NOT NULL,
PRIMARY KEY (GearId,AccountId),
CONSTRAINT `AccountGA` FOREIGN KEY (`AccountId`) REFERENCES `backpacker`.`account`(`AccountId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `GearGA` FOREIGN KEY (`GearId`) REFERENCES `backpacker`.`Gear` (`GearId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE TripLocationList (
TripId int NOT NULL,
LocationId int NOT NULL,
PRIMARY KEY (TripId,LocationId),
CONSTRAINT `TripTLL` FOREIGN KEY (`TripId`) REFERENCES `backpacker`.`Trip` (`TripId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `LocationTLL` FOREIGN KEY (`LocationId`) REFERENCES `backpacker`.`Location` (`LocationId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE TripGearList(
TripId int NOT NULL,
ListId int NOT NULL,
PRIMARY KEY (TripId,ListId),
CONSTRAINT `TripTGL` FOREIGN KEY (`TripId`) REFERENCES `backpacker`.`Trip` (`TripId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `ListTGL` FOREIGN KEY (`ListId`) REFERENCES `backpacker`.`List` (`ListId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE TABLE LocationAccount (
AccountId int NOT NULL,
LocationId int NOT NULL,
PRIMARY KEY (LocationId,AccountId),
CONSTRAINT `LocationLA` FOREIGN KEY (`LocationId`) REFERENCES `backpacker`.`Location` (`LocationId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `AccountLA` FOREIGN KEY (`AccountId`) REFERENCES `backpacker`.`account`(`AccountId`) ON DELETE RESTRICT ON UPDATE RESTRICT
);