Question 1: The Relational model.

Consider the relations Book and Author below with some sample data:

Semantics of attributes is self-explanatory. Some additional notes are as follows:

• isbn is a unique number that is assigned to each book that is published.

• A book may have one or more authors and an author can write more than one book.

• A book has only one publisher, and a publisher has an address (pubAddr). Each author has some description (authorBio).

Each question below is worth 6 marks.

a) Discuss likely data integrity constraints on attributes and specify all candidate keys and the primary key for each relation based on these constraints.

b) Specify any foreign keys for each relation and explain the primary key that the foreign keys reference.

c) Give the schema for each relation, and underline the primary key for each relation schema.

d) Based on the schema for relation Author, would the following INSERT statement

INSERT INTO BOOK VALUES(104, NULL, ‘RMIT Professor') execute successfully? Explain your answer using data integrity constraints.

e) (research question) Discuss any issues you see in the given relation schemas under the data integrity constraints. Hint: Consider updates to relation instances.

Question 2. ER model.

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 buses that can be identified by their registration number and it is important to record the number of seats, capacity 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 an end terminus and the distance of the route.

• The driver's 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 buses will be in for servicing.

When this happens, they are not removed from the database; they are just left un-assigned.

• 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 buses are assigned to each route. A given bus on a particular route is allocated a shift.

• Several mechanics work for the company. Their name and unique certificate number are required in the database. When a bus is being serviced, several machanics will work on it at the same time to get the job done faster. To get maximum efficiency from the mechanics, each mechanic has two or more jobs on the go at any one time.

According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram.

Explain any concepts in the description that can not be expressed in the ER diagram.

Your ER diagram must use notations in the lecture notes and should not be hand drawn. ER diagrams using other notations will receive zero mark for this question. You should use the ER diagramming tool Dia, which can be downloaded from the course Blackboard ("Database and Tools ! ER Diagram Tools").

Some common errors in ER diagrams:

• If the scenario is set in say, a club, you do not need an entity for the club. The arrangement you depict in the ER diagram is the club.

• Entities do not connect to other entities with straight lines. Entities have relationships to/with other entities so there has to be a relationship between connected entities.

• Every entity must have a primary key.

• The names of all relationships and entities must be unique.

• There are no foreign keys in ER diagrams. They come about as part of the mapping process into a relational schema.

Question 3. SQL

Schema for the Academics database is as follows:

DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)

ACADEMIC(acnum, deptnum*, famname, givename, initials, title)

PAPER(panum, title)

AUTHOR(panum*, acnum*)

FIELD(fieldnum, id, title)

INTEREST(fieldnum*, acnum*, descrip)

Some notes on the Academics database:

• An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department.

• Research papers (PAPER) are often authored by several academics, and of course an academic often write several papers (AUTHOR).

• A research field (FIELD) often attracts many academics and an academic can have interests in several research fields (INTEREST).

Primary keys are underlined and foreign keys are marked with *. The SQL script for defining and populating the database academics.sql can be downloaded from the course Blackboard ("Databases and Tools ! Databases").

Write one SQL query for each question below; otherwise you will receive zero for the question. Each component of an SQL statement must be on a separate line. For example, SELECT * FROM Department WHERE State='VIC';

Do not include the result of the query or the script used to create the tables. Your query should not produce duplicates in output but use DISTINCT only if necessary.

In addition to the lecture notes, you need to study by yourself the SQL*Plus tutorial on the course Blackboard ("Using Oracle ! SQL*Plus tutorial") for Oracle syntax and useful functions.

a) List the first name and last name of professors in the University of Melbourne in the database. Output should be sorted in the alphabetical order of last name and then first name. Note: The title of professors may be recorded as "Professor" or "Prof" in the database and can have upper or lower case letters.

b) In how many institutions (identified by instname) are there Computer Science-related departments? Return the number. Note: Computer Science-related departments are departments whose name contains the word "Computer" or "Computing" in upper or lower case letters.

c) Name the field number (fieldnum) and title for research fields whose title contains 'data'. Output should be in the decreasing order of fieldnum. Note that 'data' may be in upper or lower case letters.

d) How many departments do not have the state information? Return the number.

e) List the deptnum, instname and state for institutions whose departments do have the state information. Output should be in alphabetical order of states and then increasing order of deptnum.

Question 4.

a) Give the CREATE TABLE statements to define the Book and Author relations in Question 1, including the primary key and any foreign key definitions.

b) Give the INSERT statements to insert the given sample data into the Book and Author relations.

c) Consider Question 1(e). Propose a different design for relation schemas to keep data for books and authors. Discuss the pros and cons of your design in comparison with the given relation schemas.

Questions below are regarding the Academics database.

d) Write an SQL query to find out how many Computer Science departments are not in Victoria. Return the number.

e) Write a sequence of at most two SQL queries to produce the list of first name and last name for staff in the department "RMIT CS" (department description). The list should be in the alphabetical order of last name and then first name.