uni/COMP2400/books.PartA

Distributor(Distname, Telephone, MailAddress, Fax)
        PK=Distname
        Candidate keys: Distname
        FD={
                Distname -> Telephone,
                Distname -> MailAddress,
                Distname -> Fax
        }
        NF=BCNF

Department(DeptName, ContactPerson)
        PK=DeptName
        FK=ContactPerson references Department_Contact
        Candidate keys: DeptName
        FD={
                DeptName -> ContactPerson,
        }
        NF=BCNF

Department_Contact(ContactPerson, Extension)
        PK=ContactPerson
        Candidate keys: ContactPerson
        FD={
                ContactPerson -> Extension
        }
        NF=BCNF

Publisher(PubName, PubAddress, Distname)
        PK=PubName
        FK=Distname references Distributor
        Candidate keys: PubName, PubAddress
        FD={
                PubName -> PubAddress,
                PubName -> DistName
        }
        NF=BCNF

Book(ISBN, Title, QtyInStock, WholesalePrice, RetailPrice, PubName)
        PK=ISBN
        FK=PubName references Publisher
        Candidate keys: ISBN
        FD={
                ISBN -> Title,
                ISBN -> QtyInStock,
                ISBN -> WholesalePrice,
                ISBN -> RetailPrice,
                ISBN -> PubName
        }
        NF=BCNF

StoreOrder(OrderAdvice, OrderQty, QtyReceived, OrderDate, Order#, Distname, ISBN)
        PK=Order#
        FK=Distname references Distributor
        FK=ISBN references Book
        Candidate keys: Order#
        FD={
                Order# -> OrderAdvice,
                Order# -> OrderQty,
                Order# -> QtyReceived,
                Order# -> OrderDate,
                Order# -> ISBN,
                Order# -> Distname
        }
        NF=BCNF

Course(CourseCode, CourseName, DeptName)
        PK=CourseCode
        FK=DeptName references Department
        Candidate keys: CourseCode, CourseName
        FD={
                CourseCode -> CourseName,
                CourseCode -> DeptName,
                CourseName -> DeptName
        }
        NF=BCNF

CourseOffering(Year, Semester, Lecturer, EstEnrolment, CourseCode)
        PK=CourseCode, Year, Semester
        FK=CourseCode references Course
        Candidate keys: {CourseCode, Year, Semester}
        FD={
                Year, Semester, CourseCode -> Lecturer,
                Year, Semester, CourseCode -> EstEnrolment
        }
        NF=BCNF

Orders(Name, ISBN, OrderDate, Deposit, OrderStatus)
        PK=Name, ISBN
        FK=Name references Customer
        FK=ISBN references Book
        Candidate keys: {Name, ISBN}
        FD={
                Name, ISBN -> OrderDate,
                Name, ISBN -> Deposit,
                Name, ISBN -> OrderStatus
        }
        NF=BCNF

Course_Textbooks(CourseCode, Year, Semester, ISBN, QtyRequired, Prescribe/Recommended)
        PK=CourseCode, Year, Semester, ISBN
        FK=ISBN references Text_Book
        Candidate keys: {CourseCode, Year, Semester, ISBN}
        FD={
                CourseCode, Year, Semester -> ISBN,
                CourseCode, Year, Semester, ISBN -> QtyRequired,
                CourseCode, Year, Semester, ISBN -> Prescribe/Recommended
        }
        NF=BCNF

Authorship(ISBN, Firstname, Surname)
        PK=ISBN, Firstname, Surname
        FK=ISBN references Book
        FK=Firstname, Surname references Author
        Candidate keys: {ISBN, Firstname, Surname}
        FD={
                ISBN -> Firstname,
                ISBN -> Surname
        }
        NF=BCNF

Text_book(ISBN)
        PK=ISBN
        FK=ISBN references Book
        Candidate keys: ISBN
        FD={
        }
        NF=BCNF

Fiction_Book(ISBN, Category)
        PK=ISBN
        FK=ISBN references Book
        Candidate keys: ISBN
        FD={
                ISBN -> Category
        }
        NF=BCNF

Nonfiction_Book(ISBN, Category)
        PK=ISBN
        FK=ISBN references Book
        Candidate keys: ISBN
        FD={
                ISBN -> Category
        }
        NF=BCNF

a) as CourseCode is a key of the Course relation, the same Course cannot appear twice with different DeptNames
b) the combined key of CourseCode, Year and Semester allows for a course to be offered any semester of any year
c) As the key is all three attributes of the Authorship relation, the same ISBN may appear more than once in the Author relation, and so can the same author. The combination can only appear once.
d) Make the Deposit attribute not null and have a check to ensure it is greater than zero
e) As the key is ISBN and Customer attributes, an order cannot be for multiple books or customers
f) As Order# is the key, a single order can only be for one ISBN
g) Publishers have distributors, but distributors have no direct linkage to publishers, so this is possible
h) this can be achieved with a check constraint, or by a foreign key referencing a table of possible values the attribute may have
i) Similarly, this can be achieved with a check constraint or by a foreign key referencing a table of possible values the attribute may have
j) Similarly, this can be achieved with a check constraint or by a foreign key referencing a table of possible values the attribute may have
k) I am unsure how to implement this without using a trigger

Queries (problematic ones indicated with a *):

1) Should be doable by joining Book and Author (if necessary)

2)*This would be a bit cumbersome, because it would require querying each
book sub-type table by ISBN. This could be improved by having an attribute
that identified the type of book, rather than using sub-types.

3) Should be doable by joining Course_Textbooks, Text_book, Book and
possibly Course, depending on what the query is based on.

4)*This would be impossible, as there is no distinction between a course
that has no textbook and a course that hasn't yet stated a textbook.  This
could be worked around by requiring a null value for the
course/semester/year combinations that do not require a textbook, and then
testing for the absence of a row for any given course/semester/year.

5) This should be doable by joining Course_Textbooks, Text_book, Book and
possibly Course, depending on what the query is based on.

6) This should be doable by joining Course_Textbooks, Text_book, Book,
StoreOrder and possibly Course, depending on what the query is based on.

7) This should be doable by querying the Book relation.

8) This should be doable by querying the Book relation.

9) This should be doable by querying Orders (possibly joined with Book)
depending on what data is required.

10)*This would be difficult, given that book edition information isn't
specifically stored as an attribute.

11) This should be doable by querying Orders, possibly joined with Book,
depending on what data is required.

12) This should be doable by querying Course_Textbooks joined with
CourseOffering.

13) This should be doable by joining the Book relation with all the Book
sub-type relations and returning the category attribute.
1	Distributor(Distname, Telephone, MailAddress, Fax)
2	PK=Distname
3	Candidate keys: Distname
4	FD={
5	Distname -> Telephone,
6	Distname -> MailAddress,
7	Distname -> Fax
8	}
9	NF=BCNF
10
11	Department(DeptName, ContactPerson)
12	PK=DeptName
13	FK=ContactPerson references Department_Contact
14	Candidate keys: DeptName
15	FD={
16	DeptName -> ContactPerson,
17	}
18	NF=BCNF
19
20	Department_Contact(ContactPerson, Extension)
21	PK=ContactPerson
22	Candidate keys: ContactPerson
23	FD={
24	ContactPerson -> Extension
25	}
26	NF=BCNF
27
28	Publisher(PubName, PubAddress, Distname)
29	PK=PubName
30	FK=Distname references Distributor
31	Candidate keys: PubName, PubAddress
32	FD={
33	PubName -> PubAddress,
34	PubName -> DistName
35	}
36	NF=BCNF
37
38	Book(ISBN, Title, QtyInStock, WholesalePrice, RetailPrice, PubName)
39	PK=ISBN
40	FK=PubName references Publisher
41	Candidate keys: ISBN
42	FD={
43	ISBN -> Title,
44	ISBN -> QtyInStock,
45	ISBN -> WholesalePrice,
46	ISBN -> RetailPrice,
47	ISBN -> PubName
48	}
49	NF=BCNF
50
51	StoreOrder(OrderAdvice, OrderQty, QtyReceived, OrderDate, Order#, Distname, ISBN)
52	PK=Order#
53	FK=Distname references Distributor
54	FK=ISBN references Book
55	Candidate keys: Order#
56	FD={
57	Order# -> OrderAdvice,
58	Order# -> OrderQty,
59	Order# -> QtyReceived,
60	Order# -> OrderDate,
61	Order# -> ISBN,
62	Order# -> Distname
63	}
64	NF=BCNF
65
66	Course(CourseCode, CourseName, DeptName)
67	PK=CourseCode
68	FK=DeptName references Department
69	Candidate keys: CourseCode, CourseName
70	FD={
71	CourseCode -> CourseName,
72	CourseCode -> DeptName,
73	CourseName -> DeptName
74	}
75	NF=BCNF
76
77	CourseOffering(Year, Semester, Lecturer, EstEnrolment, CourseCode)
78	PK=CourseCode, Year, Semester
79	FK=CourseCode references Course
80	Candidate keys: {CourseCode, Year, Semester}
81	FD={
82	Year, Semester, CourseCode -> Lecturer,
83	Year, Semester, CourseCode -> EstEnrolment
84	}
85	NF=BCNF
86
87	Orders(Name, ISBN, OrderDate, Deposit, OrderStatus)
88	PK=Name, ISBN
89	FK=Name references Customer
90	FK=ISBN references Book
91	Candidate keys: {Name, ISBN}
92	FD={
93	Name, ISBN -> OrderDate,
94	Name, ISBN -> Deposit,
95	Name, ISBN -> OrderStatus
96	}
97	NF=BCNF
98
99	Course_Textbooks(CourseCode, Year, Semester, ISBN, QtyRequired, Prescribe/Recommended)
100	PK=CourseCode, Year, Semester, ISBN
101	FK=ISBN references Text_Book
102	Candidate keys: {CourseCode, Year, Semester, ISBN}
103	FD={
104	CourseCode, Year, Semester -> ISBN,
105	CourseCode, Year, Semester, ISBN -> QtyRequired,
106	CourseCode, Year, Semester, ISBN -> Prescribe/Recommended
107	}
108	NF=BCNF
109
110	Authorship(ISBN, Firstname, Surname)
111	PK=ISBN, Firstname, Surname
112	FK=ISBN references Book
113	FK=Firstname, Surname references Author
114	Candidate keys: {ISBN, Firstname, Surname}
115	FD={
116	ISBN -> Firstname,
117	ISBN -> Surname
118	}
119	NF=BCNF
120
121	Text_book(ISBN)
122	PK=ISBN
123	FK=ISBN references Book
124	Candidate keys: ISBN
125	FD={
126	}
127	NF=BCNF
128
129	Fiction_Book(ISBN, Category)
130	PK=ISBN
131	FK=ISBN references Book
132	Candidate keys: ISBN
133	FD={
134	ISBN -> Category
135	}
136	NF=BCNF
137
138	Nonfiction_Book(ISBN, Category)
139	PK=ISBN
140	FK=ISBN references Book
141	Candidate keys: ISBN
142	FD={
143	ISBN -> Category
144	}
145	NF=BCNF
146
147	a) as CourseCode is a key of the Course relation, the same Course cannot appear twice with different DeptNames
148	b) the combined key of CourseCode, Year and Semester allows for a course to be offered any semester of any year
149	c) As the key is all three attributes of the Authorship relation, the same ISBN may appear more than once in the Author relation, and so can the same author. The combination can only appear once.
150	d) Make the Deposit attribute not null and have a check to ensure it is greater than zero
151	e) As the key is ISBN and Customer attributes, an order cannot be for multiple books or customers
152	f) As Order# is the key, a single order can only be for one ISBN
153	g) Publishers have distributors, but distributors have no direct linkage to publishers, so this is possible
154	h) this can be achieved with a check constraint, or by a foreign key referencing a table of possible values the attribute may have
155	i) Similarly, this can be achieved with a check constraint or by a foreign key referencing a table of possible values the attribute may have
156	j) Similarly, this can be achieved with a check constraint or by a foreign key referencing a table of possible values the attribute may have
157	k) I am unsure how to implement this without using a trigger
158
159	Queries (problematic ones indicated with a *):
160
161	1) Should be doable by joining Book and Author (if necessary)
162
163	2)*This would be a bit cumbersome, because it would require querying each
164	book sub-type table by ISBN. This could be improved by having an attribute
165	that identified the type of book, rather than using sub-types.
166
167	3) Should be doable by joining Course_Textbooks, Text_book, Book and
168	possibly Course, depending on what the query is based on.
169
170	4)*This would be impossible, as there is no distinction between a course
171	that has no textbook and a course that hasn't yet stated a textbook. This
172	could be worked around by requiring a null value for the
173	course/semester/year combinations that do not require a textbook, and then
174	testing for the absence of a row for any given course/semester/year.
175
176	5) This should be doable by joining Course_Textbooks, Text_book, Book and
177	possibly Course, depending on what the query is based on.
178
179	6) This should be doable by joining Course_Textbooks, Text_book, Book,
180	StoreOrder and possibly Course, depending on what the query is based on.
181
182	7) This should be doable by querying the Book relation.
183
184	8) This should be doable by querying the Book relation.
185
186	9) This should be doable by querying Orders (possibly joined with Book)
187	depending on what data is required.
188
189	10)*This would be difficult, given that book edition information isn't
190	specifically stored as an attribute.
191
192	11) This should be doable by querying Orders, possibly joined with Book,
193	depending on what data is required.
194
195	12) This should be doable by querying Course_Textbooks joined with
196	CourseOffering.
197
198	13) This should be doable by joining the Book relation with all the Book
199	sub-type relations and returning the category attribute.