/[cvs.andrew.net.au]/uni/COMP2400/createbooks.sql
ViewVC logotype

Contents of /uni/COMP2400/createbooks.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1 - (show annotations)
Mon Sep 27 02:10:06 2004 UTC (17 years, 10 months ago) by apollock
Branch: MAIN
CVS Tags: HEAD
Pretty much at the submission stage

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

  ViewVC Help
Powered by ViewVC 1.1.22