QUESTION 1 (20 MARKS)

1.        Codd listed 8 services that should be provided by any full-scale DBMS. List any FOUR (4) of these services. (4 marks)

2.        Define each of the following terms, using examples of your own to illustrate your answer:

•   Logical data independence

•   Physical data independence (4 marks)

3.         Define each of the following terms, using examples of your own to illustrate your answer:

•   entity integrity constraint

•   referential integrity constraint (4 marks)

4.        The following questions relate to databases and Business Intelligence.

(a)       What is data cleaning (data cleansing), and why is it particularly important in

preparing data for a data warehouse?

(b)       Explain the concept of ‘drill down’ in an OLAP cube. (4 marks)

5.         Explain the requirements for databases that NoSQL databases have arisen to support. List two categories of NoSQL databases. (4 marks)

QUESTION 2 (20 MARKS)

1.        This question is based on the relations shown below:

PROPERTY

LEASE

(a)       Write a relational algebra expression that lists the property number, city, lease

start and lease end dates for all the properties located in the city of Perth.

Show the relations (structure and data) that would result from the following relational algebra expressions on the relations below:

(b)       PROPERTY LEFT JOIN Property.PropertyNo=Lease.PropertyNo LEASE

(c)       PropertyNo (PROPERTY)   MINUS  PropertyNo (LEASE) (6 marks)

2.        The relation below represents information about computer parts and vendors that sell them.

(a)       What is the candidate key of the relation?

(b)       What normal form is the relation in? Explain your reasoning.

(c)       Convert the relation to a set of relations each in Third Normal Form (3NF). Indicate all primary keys and foreign keys. (6 marks)

3.         This question is based on the tables listed below:

PILOT

FLIGHT

AIRCRAFT

Give SQL statements for each of the following operations.

(a)         List the PilotCode, LastName and CharterDate of all pilots who piloted a

flight during May 2018.

(b)         List each type of aircraft, and the total number of aircraft of that type.

(c)         Change aircraft 1484P’s total hours to 13000.

(d)         Delete any aircraft that have never been used in a flight. (8 marks)

QUESTION 3 (20 MARKS)

1.        Create an entity-relationship diagram (ERD) for a database based on the following description. Use the crow’s feet notation that we used during this semester.

A database is required for a small plumbing business. A customer will contact the       business with a request for work to be carried out. A customer has a unique customer  number, a name, and a billing address (street, suburb, state, postcode).  A work order  is created from the customer request. A work order has a unique work order number, a creation date, a completion required date, an actual completion date, an optional          supervising employee, an address where the work is undertaken, and will involve one or more tasks. Each task has a unique task ID, a task name, an hourly rate, and estimated hours. Tasks are standardised across work orders so that the same task may  be performed on many work orders. Each task on a work order has a status (not started, in progress, or completed), actual hours, and a completion date.

Your ERD should show all entities, attributes, relationships, maximum cardinality and minimum cardinalty (participation). Indicate all primary keys and foreign keys. Include a legend to your diagram. (8 marks)

2.        Tuning the database to improve performance by adding indexes and optimising query execution is an important ongoing aspect of the physical database design.

(a)       Which attributes (apart from the primary key) are likely to BENEFIT from

being indexed?

(b)       Which attributes would you generally AVOID indexing, and why?

(c)       The query optimiser has a choice ofjoin strategies that can be used in executing a query. Briefly describe how  the nested loop join works. (6 marks)

3.         (a)       Explain what a view is, and give two reasons why you might create a view.

(b)       What is meant by saying a view is updateable? (4 marks)

4.         Give an SQL statement that would give the user group STUDENTS the ability to read, update and add records to the table ARTIST. (2 marks)