Database/init.sql at main · RickyMarin/Database · 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
CREATE TABLE Topping (
	tid int(10) NOT NULL AUTO_INCREMENT,
	tname varchar(30) NOT NULL,
	PRIMARY KEY (tid),
	UNIQUE (tname));

CREATE TABLE Size (
	sid int(10) NOT NULL AUTO_INCREMENT,
	sname varchar(30) NOT NULL,
	PRIMARY KEY (sid),
	UNIQUE (sname));

CREATE TABLE Users(
	uemail varchar(256) NOT NULL,
	uname varchar(30) NOT NULL,
	upass varchar(256) NOT NULL,
	uaddrstr varchar(30) NOT NULL,
	uaddrcity varchar(20) NOT NULL,
	uaddrstate varchar(14) NOT NULL,
	uaddrzip int(5) NOT NULL,
	upoints int(6) NOT NULL DEFAULT 0 CHECK (upoints >= 0),
    PRIMARY KEY (uemail));

CREATE TABLE Orders (
	orderNum int(10) NOT NULL AUTO_INCREMENT,
	uemail varchar(40) NOT NULL,
    tid int(10) NOT NULL,
    sid int(10) NOT NULL,
    eid int(10) NOT NULL,
    cheese varchar(20) NOT NULL,
    quantity varchar(7) NOT NULL,
	deliver int(1) NOT NULL DEFAULT 1 CHECK (deliver = 0 OR deliver = 1),
	cost int(3) NOT NULL DEFAULT 15 CHECK (cost >= 0),
	orderStatus int(1) NOT NULL DEFAULT 0 CHECK (orderStatus = 0 OR orderStatus = 1),
	PRIMARY KEY (orderNum),
	FOREIGN KEY (uemail) REFERENCES Users(uemail),
    FOREIGN KEY (tid) REFERENCES Topping(tid),
    FOREIGN KEY (sid) REFERENCES Size(sid)),
    FOREIGN KEY (eid) REFERENCES Employee(eid);


CREATE TABLE UsersPhone (
	uemail varchar(40) NOT NULL,
    uphone varchar(10) NOT NULL CHECK (length(uphone) = 10),
    numtype varchar(10) NOT NULL,
    PRIMARY KEY (uemail, uphone),
    FOREIGN KEY (uemail) REFERENCES Users(uemail));

CREATE TABLE Request (
    rid int(10) NOT NULL AUTO_INCREMENT,
    uemail varchar(40) NOT NULL,
    rnote varchar(200) NOT NULL,
    PRIMARY KEY (rid),
    FOREIGN KEY (uemail) REFERENCES Users(uemail));

CREATE TABLE Location (
	lid int(10) NOT NULL AUTO_INCREMENT,
	laddrstr varchar(30) NOT NULL,
	laddrcity varchar(20) NOT NULL,
	laddrstate varchar(14) NOT NULL,
	laddrzip varchar(5) NOT NULL,
    PRIMARY KEY (lid));

CREATE TABLE Employee (
    eid int(10) NOT NULL AUTO_INCREMENT,
    lid int(10) NOT NULL,
    ename varchar(30) NOT NULL,
    job varchar(15) NOT NULL,
    PRIMARY KEY (eid),
    FOREIGN KEY (lid) REFERENCES Location(lid));

CREATE TABLE Cashier (
    eid int(10) NOT NULL,
    exp int(10) NOT NULL CHECK (exp >= 0),
    PRIMARY KEY (eid),
    FOREIGN KEY (eid) REFERENCES Employee(eid));

CREATE TABLE Driver (
    eid int(10) NOT NULL,
    ephone varchar(10) NOT NULL CHECK (length(ephone) = 10),
    PRIMARY KEY (eid),
    FOREIGN KEY (eid) REFERENCES Employee(eid));