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	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	);