-
Notifications
You must be signed in to change notification settings - Fork 1
/
createTables.sql
68 lines (54 loc) · 1.58 KB
/
createTables.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
create table users (
studentNum NUMBER(8) PRIMARY KEY,
email VARCHAR(50),
password VARCHAR(20),
uname VARCHAR(20)
);
create table sellers (
studentNum NUMBER(8) NOT NULL,
PRIMARY KEY (studentNum),
FOREIGN KEY (studentNum) REFERENCES users
);
create table textbooks (
ISBN NUMBER(13) PRIMARY KEY,
image VARCHAR(100),
title VARCHAR(50)
);
create table authors_in_textbook (
aname VARCHAR(15),
ISBN NUMBER(13) PRIMARY KEY
REFERENCES textbooks(ISBN)
ON DELETE CASCADE
);
create table course (
courseCode VARCHAR(4) NOT NULL,
courseNum NUMBER(3) NOT NULL,
PRIMARY KEY (courseCode, courseNum)
);
create table course_of_textbook (
ISBN NUMBER(13) NOT NULL,
courseCode VARCHAR(4) NOT NULL,
courseNum NUMBER(3) NOT NULL,
PRIMARY KEY (ISBN, courseCode, courseNum),
FOREIGN KEY (ISBN) REFERENCES textbooks,
FOREIGN KEY (courseCode, courseNum) REFERENCES course
);
create table posting (
postID NUMBER(8),
price NUMBER(8),
description VARCHAR(1000),
image VARCHAR(100),
sold VARCHAR(1),
ISBN NUMBER(13) NOT NULL,
studentNum NUMBER(8) NOT NULL,
PRIMARY KEY (postID),
FOREIGN KEY (ISBN) REFERENCES textbooks,
FOREIGN KEY (studentNum) REFERENCES sellers
);
create table sellers_sell_textbook (
studentNum NUMBER(8) NOT NULL,
ISBN NUMBER(13) NOT NULL,
PRIMARY KEY (studentNum, ISBN),
FOREIGN KEY (studentNum) REFERENCES sellers,
FOREIGN KEY (ISBN) REFERENCES textbooks
);