uni/COMP2400/createbooks.sql

CREATE TABLE distributor
(
Distname VARCHAR(50),
Telephone NUMBER(10),
MailAddress VARCHAR(100),
Fax NUMBER(10),
CONSTRAINT distname_pk PRIMARY KEY (distname)
);

CREATE TABLE department_contact
(
ContactPerson VARCHAR(50),
Extension NUMBER(5),
CONSTRAINT department_contact_pk PRIMARY KEY (ContactPerson)
);

CREATE TABLE department
(
DeptName VARCHAR(50),
ContactPerson VARCHAR(50),
CONSTRAINT department_pk PRIMARY KEY (DeptName),
CONSTRAINT department_contact_fk FOREIGN KEY (ContactPerson) REFERENCES department_contact
);

CREATE TABLE publisher
(
PubName VARCHAR(50),
PubAddress VARCHAR(100),
Distname VARCHAR(50),
CONSTRAINT publisher_pk PRIMARY KEY (PubName),
CONSTRAINT publisher_distributor_fk FOREIGN KEY (Distname) REFERENCES distributor
);

CREATE TABLE book
(
ISBN NUMBER(10),
Title VARCHAR(50),
QtyInStock NUMBER(3),
WholesalePrice FLOAT,
RetailPrice FLOAT,
PubName VARCHAR(50),
CONSTRAINT book_pk PRIMARY KEY (ISBN),
CONSTRAINT book_publisher_fk FOREIGN KEY (PubName) REFERENCES publisher
);

CREATE TABLE storeorder
(
OrderNo NUMBER(6),
OrderDate DATE,
OrderAdvice VARCHAR(16),
ISBN NUMBER(10),
OrderQty NUMBER(3),
QtyReceived NUMBER(3),
DistName VARCHAR(50),
CONSTRAINT storeorder_pk PRIMARY KEY (OrderNo),
CONSTRAINT storeorder_distributor_fk FOREIGN KEY (DistName) REFERENCES distributor,
CONSTRAINT storeorder_orderadvice_ck CHECK (OrderAdvice IN 
('arrived', 'on-order', 'unavailable', 'out-of-stock', 'partially-filled'))
);

CREATE TABLE course
(
CourseCode VARCHAR(8),
CourseName VARCHAR(100),
DeptName VARCHAR(50),
CONSTRAINT course_pk PRIMARY KEY (CourseCode),
CONSTRAINT course_department_fk FOREIGN KEY (DeptName) REFERENCES department
);

CREATE TABLE courseoffering
(
CourseCode VARCHAR(8),
Year NUMBER(4),
Semester NUMBER(1),
Lecturer VARCHAR(50),
EstEnrolment NUMBER(3),
CONSTRAINT courseoffering_pk PRIMARY KEY (CourseCode, Year, Semester),
CONSTRAINT courseoffering_course_fk FOREIGN KEY (CourseCode) REFERENCES course
);

CREATE TABLE orders
(
Name VARCHAR(50),
ISBN NUMBER(10),
OrderDate DATE,
Deposit FLOAT,
OrderStatus VARCHAR(12),
CONSTRAINT orders_pk PRIMARY KEY (Name, ISBN),
CONSTRAINT orders_isbn_fk FOREIGN KEY (ISBN) REFERENCES book,
CONSTRAINT orderstatus_ck CHECK (OrderStatus IN 
('arrived', 'on-order', 'unavailable', 'out-of-stock'))
);

CREATE TABLE course_textbooks 
(
CourseCode VARCHAR(8),
Year NUMBER(4),
Semester NUMBER(1),
ISBN NUMBER(10),
QtyRequired NUMBER(3),
Status VARCHAR(11),
CONSTRAINT course_textbooks_pk PRIMARY KEY (CourseCode, Year, Semester, ISBN),
CONSTRAINT course_textbooks_coursecode_fk FOREIGN KEY (CourseCode) REFERENCES course,
CONSTRAINT course_textbooks_isbn_fk FOREIGN KEY (ISBN) REFERENCES book,
CONSTRAINT status_ck CHECK (Status IN ('prescribed', 'recommended'))
);

CREATE TABLE authorship
(
ISBN NUMBER(10),
Firstname VARCHAR(25),
Surname VARCHAR(25),
CONSTRAINT authorship_pk PRIMARY KEY (ISBN, Firstname, Surname),
CONSTRAINT authorship_book_fk FOREIGN KEY (ISBN) REFERENCES book
);

CREATE TABLE text_book
(
ISBN NUMBER(10),
CONSTRAINT text_book_pk PRIMARY KEY (ISBN),
CONSTRAINT text_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
);

CREATE TABLE fiction_book
(
ISBN NUMBER(10),
Category VARCHAR(20),
CONSTRAINT fiction_book_pk PRIMARY KEY (ISBN),
CONSTRAINT fiction_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
);

CREATE TABLE nonfiction_book
(
ISBN NUMBER(10),
Category VARCHAR(20),
CONSTRAINT nonfiction_book_pk PRIMARY KEY (ISBN),
CONSTRAINT nonfiction_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
);
1	apollock	1.1	CREATE TABLE distributor
2			(
3			Distname VARCHAR(50),
4			Telephone NUMBER(10),
5			MailAddress VARCHAR(100),
6			Fax NUMBER(10),
7			CONSTRAINT distname_pk PRIMARY KEY (distname)
8			);
9
10			CREATE TABLE department_contact
11			(
12			ContactPerson VARCHAR(50),
13			Extension NUMBER(5),
14			CONSTRAINT department_contact_pk PRIMARY KEY (ContactPerson)
15			);
16
17			CREATE TABLE department
18			(
19			DeptName VARCHAR(50),
20			ContactPerson VARCHAR(50),
21			CONSTRAINT department_pk PRIMARY KEY (DeptName),
22			CONSTRAINT department_contact_fk FOREIGN KEY (ContactPerson) REFERENCES department_contact
23			);
24
25			CREATE TABLE publisher
26			(
27			PubName VARCHAR(50),
28			PubAddress VARCHAR(100),
29			Distname VARCHAR(50),
30			CONSTRAINT publisher_pk PRIMARY KEY (PubName),
31			CONSTRAINT publisher_distributor_fk FOREIGN KEY (Distname) REFERENCES distributor
32			);
33
34			CREATE TABLE book
35			(
36			ISBN NUMBER(10),
37			Title VARCHAR(50),
38			QtyInStock NUMBER(3),
39			WholesalePrice FLOAT,
40			RetailPrice FLOAT,
41			PubName VARCHAR(50),
42			CONSTRAINT book_pk PRIMARY KEY (ISBN),
43			CONSTRAINT book_publisher_fk FOREIGN KEY (PubName) REFERENCES publisher
44			);
45
46			CREATE TABLE storeorder
47			(
48			OrderNo NUMBER(6),
49			OrderDate DATE,
50			OrderAdvice VARCHAR(16),
51			ISBN NUMBER(10),
52			OrderQty NUMBER(3),
53			QtyReceived NUMBER(3),
54			DistName VARCHAR(50),
55			CONSTRAINT storeorder_pk PRIMARY KEY (OrderNo),
56			CONSTRAINT storeorder_distributor_fk FOREIGN KEY (DistName) REFERENCES distributor,
57			CONSTRAINT storeorder_orderadvice_ck CHECK (OrderAdvice IN
58			('arrived', 'on-order', 'unavailable', 'out-of-stock', 'partially-filled'))
59			);
60
61			CREATE TABLE course
62			(
63			CourseCode VARCHAR(8),
64			CourseName VARCHAR(100),
65			DeptName VARCHAR(50),
66			CONSTRAINT course_pk PRIMARY KEY (CourseCode),
67			CONSTRAINT course_department_fk FOREIGN KEY (DeptName) REFERENCES department
68			);
69
70			CREATE TABLE courseoffering
71			(
72			CourseCode VARCHAR(8),
73			Year NUMBER(4),
74			Semester NUMBER(1),
75			Lecturer VARCHAR(50),
76			EstEnrolment NUMBER(3),
77			CONSTRAINT courseoffering_pk PRIMARY KEY (CourseCode, Year, Semester),
78			CONSTRAINT courseoffering_course_fk FOREIGN KEY (CourseCode) REFERENCES course
79			);
80
81			CREATE TABLE orders
82			(
83			Name VARCHAR(50),
84			ISBN NUMBER(10),
85			OrderDate DATE,
86			Deposit FLOAT,
87			OrderStatus VARCHAR(12),
88			CONSTRAINT orders_pk PRIMARY KEY (Name, ISBN),
89			CONSTRAINT orders_isbn_fk FOREIGN KEY (ISBN) REFERENCES book,
90			CONSTRAINT orderstatus_ck CHECK (OrderStatus IN
91			('arrived', 'on-order', 'unavailable', 'out-of-stock'))
92			);
93
94			CREATE TABLE course_textbooks
95			(
96			CourseCode VARCHAR(8),
97			Year NUMBER(4),
98			Semester NUMBER(1),
99			ISBN NUMBER(10),
100			QtyRequired NUMBER(3),
101			Status VARCHAR(11),
102			CONSTRAINT course_textbooks_pk PRIMARY KEY (CourseCode, Year, Semester, ISBN),
103			CONSTRAINT course_textbooks_coursecode_fk FOREIGN KEY (CourseCode) REFERENCES course,
104			CONSTRAINT course_textbooks_isbn_fk FOREIGN KEY (ISBN) REFERENCES book,
105			CONSTRAINT status_ck CHECK (Status IN ('prescribed', 'recommended'))
106			);
107
108			CREATE TABLE authorship
109			(
110			ISBN NUMBER(10),
111			Firstname VARCHAR(25),
112			Surname VARCHAR(25),
113			CONSTRAINT authorship_pk PRIMARY KEY (ISBN, Firstname, Surname),
114			CONSTRAINT authorship_book_fk FOREIGN KEY (ISBN) REFERENCES book
115			);
116
117			CREATE TABLE text_book
118			(
119			ISBN NUMBER(10),
120			CONSTRAINT text_book_pk PRIMARY KEY (ISBN),
121			CONSTRAINT text_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
122			);
123
124			CREATE TABLE fiction_book
125			(
126			ISBN NUMBER(10),
127			Category VARCHAR(20),
128			CONSTRAINT fiction_book_pk PRIMARY KEY (ISBN),
129			CONSTRAINT fiction_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
130			);
131
132			CREATE TABLE nonfiction_book
133			(
134			ISBN NUMBER(10),
135			Category VARCHAR(20),
136			CONSTRAINT nonfiction_book_pk PRIMARY KEY (ISBN),
137			CONSTRAINT nonfiction_book_isbn_fk FOREIGN KEY (ISBN) REFERENCES book
138			);