-
Notifications
You must be signed in to change notification settings - Fork 2
/
SkiAPI.sql
40 lines (36 loc) · 1.05 KB
/
SkiAPI.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
# Create the schema if necessary.
CREATE SCHEMA IF NOT EXISTS SkiAPI;
USE SkiAPI;
# Drop tables if necessary.
DROP TABLE IF EXISTS Seasons;
DROP TABLE IF EXISTS Resorts;
DROP TABLE IF EXISTS LiftRides;
# Create tables if necessary.
CREATE TABLE Resorts (
ResortId INT UNSIGNED NOT NULL AUTO_INCREMENT,
ResortName VARCHAR(255) NOT NULL,
CONSTRAINT pk_Resorts_ResortId
PRIMARY KEY (ResortId)
);
CREATE TABLE Seasons (
SeasonId INT UNSIGNED NOT NULL AUTO_INCREMENT,
Season INT NOT NULL,
ResortId INT UNSIGNED,
CONSTRAINT pk_Seasons_SeasonId
PRIMARY KEY (SeasonId),
CONSTRAINT fk_Seasons_Resorts_ResortId
FOREIGN KEY (ResortId)
REFERENCES Resorts (ResortId)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unique_resort_season UNIQUE (ResortId, Season)# row updated/deleted if data in parent table updated/deleted
);
CREATE TABLE LiftRides (
ResortId INT NOT NULL,
SeasonId INT NOT NULL,
DayId INT NOT NULL,
SkierId INT NOT NULL,
StartTime INT NOT NULL,
LiftId INT NOT NULL,
Vertical INT NOT NULL,
PRIMARY KEY (ResortId, SeasonId, DayId, SkierId, StartTime)
);