Question 1.
a)
Book:
- Likely data integrity constraints: isbn can not be null, each row (for a book) has a unique isbn, each publisher has one pubAddr.
- {isbn} is only candidate key and therefore the primary key.
Author:
- Likely data integrity constraints: isbn must be a valid isbn from Book; an author has one authBio
- {isbn, author} is the only candidate key and therefore the primary key.
b) Author.isbn is a foreign key referencing Book.isbn
c)
Book(isbn,...)
Author(isbn*, author, authorbio)
d) No. It violates the primary key entity integrity constraint.
e) Both relations have data redundancy and therefore hard to maintain data integrity. For example when the Pearson publisher moves to a new address, if one occurrence is not updated with the new address, there will be non-sensical data in the database.
Question 2.
A small bus company wants a simple database to track some aspects of its operation. They describe key elements of their requirements in the following points . Use the following information to understand their requirements and draw an ER diagram.
• The company has busses that can be identified by their registration number and its important to record the number of seat, the capacity of the motor and the last service date.
• Bus routes are known by their Route Number and all route numbers start with the capital letter "R". All routes have a start terminus and a end terminus and the distance of the route.
• The drivers details are name, their start date with the company, their licence number and its expiry date.
• Each bus driver is allocated a bus and only that driver will drive that bus. However, at any given time, some drivers will be on leave and some busses will be in for servicing.
• A bus is allocated to just one route.
• For OH&S reasons, the company runs different shifts (Morning, Afternoon, Night). To cover all the shifts, multiple busses are assigned to each route. A given bus on a particular route is allocated a shift.
• Several mechanics work for the company. There name and unique certificate number are required in the database. A bus can be worked on by any of the mechanics and all mechanics are qualified to service all buses.
Question 3.
a)
select givename, famname
from academic
where upper(title) like 'PROF%'
and deptnum = 125
order by famname, givename;
Marking Comments:
- May use a join query with depatment
- May use deptnum of 125 or 124 or 142 or any combination of these. (due to confusion with question).
- May use two test for title.
b)
Marking: must use Distinct.
select count(distinct instname)
from department
where upper(deptname) like '%COMPUTER%'
or upper(deptname) like '%COMPUTING%' ;
c)
select fieldnum, title
from field
where upper(title) like '%DATA%'
order by fieldnum desc;
d)
select count(*)
from department
where state is null;
e)
select deptnum, instname, state
from department
where state is not null
order by state,deptnum;