Database class supplementary reading

CB = Connolly and Begg, Database Systems: A Practical approach to design, implementation, and management, Third Edition, Addison-Wesley, 2000.

Database class lab notes

Lab 1

Problem 1

Use MS Access to create a database for the E-R diagram in Lecture 3, and fill it with some appropriate data.

If you don't know how to use MS Access, here is a brief guide.

1. Start MS Access.
2. In the panel "Create a new database using" select "Blank Access database".
3. Choose a file name for your database, and click on "Create".
4. Double click "Create table in Design view".
5. Enter a "Field Name" (SReg), and move the cursor to the "Data Type" column.
6. Select a data type from the drop-down list (Text).
7. In the lower panel set "Field Size" to "5", and "Required" to "Yes"
8. Right click on the field name cell, and select "Primary Key"
9. Fill in the definitions of the other fields in the "Student" table in a similar way.
10. When the definitions are finished, close the window, save the changes, and choose a name (Student) for the table.
11. Notice that the Student table is now in the "University : Database" window.
12. Double click on the icon for the Student table to open it, and fill it with suitable data.
13. Create the other tables, and fill them with suitable data.

Here is some suggested data for the tables:

Problem 2

Consider what would be suitable attributes for tables to manage a collection of CDs devoted to the works of J. S. Bach. Each CD has an external identifier of up to ten characters. On each CD the tracks are numbered sequentially from 1 upwards, and each CD has a total playing time expressed in minutes and seconds. Each track contains the whole, or part, of a work of JSB, performed by certain artists (conductor, soloists, choir, orchestra, instrumentalists, and so on). In some performances the instrument used (organ, piano, harpsichord, clavichord, and so on) needs to be recorded. Some performances in the collection include arrangements of Bach's works by other composers (like Busoni, and Hess) and this needs to be recorded in the appropriate cases. Each of Bach's works has an identifying BWV number in the range 1 to 1087, and a title, which sometimes mentions a key signature (any one of twelve major or twelve minor keys).

The sort of processing that the owner of the collection expects to be able to do includes, but is not limited to, the following:

• List all the tracks in which Andras Schiff figures as performer.
• List all the tracks that contain performances of organ works in B minor.
• List the tracks that contains works within a certain range of BWV numbers.
• List the BWV numbers and titles of the six Brandenburg Concertos.
• List the BWV numbers and titles of the six English Suites, and list the tracks for these suites in which Robert Levin is the pianist.
• List all the tracks of organ works performed by Alan Spedding.
• List all the tracks of organ works performed on the organ in Beverley Minster.
• List all the artists, and for each give the number of works they perofrm in, and the number of tracks on which they appear.

(Draw an entity relationship diagram first, if you think it will help.)

Problem 3

(If you have time.) Create an entity relationship diagram for any (small) organisation that you can imagine. Design the tables, create the database, and fill it with suitable data.

Lab 2

Use Microsoft Access to create and execute SQL queries on the databases you created in Lab 1.

If you don't know how to do queries in Access, here is a brief guide. We first create a query to list the students in Cavendish hall as follows:

1. Start Microsoft Access, and choose the University database you created in Lab 1.
2. Select "Queries" in the "Objects" panel, and double click on "Create query in Design view".
3. In the "Show Table" window, click on "Student", and then click on "Add".
4. Close the "Show Table" window.
5. In the first column of the grid, open the drop down list, and select "SName".
6. In the second column of the grid, open the drop down list, and select "SHall".
7. Click on the box in the "Show" row of this column to remove the tick (check mark).
8. In the "Criteria" box of the second column, enter "Cavendish".
9. Close the query, agree to save it, and name it "HallQuery".
10. An icon for "HallQuery" has been created in the "University : Database" window. Double click on the query to run it, and the result appears.
11. Click on "View", then "SQL View", and the SQL for the query is shown. It is not quite the same as the SQL you might have expected for this query, but the differences are not great. There are more parentheses and square brackets than you would expect, and the table name qualifiers on the attribute names are more than you would expect. You can remove them, save the changes, and then run the query again. The results should be the same as before.

To try out SQL statements, copy existing queries, and then change the SQL in them. The procedure for copying a query is as follows:

1. In the "University : Database" window, and the "Queries" view, right click on "HallQuery".
2. Select "Copy", right click elsewhere in the window, select "Paste", and supply a new name for the query.

Create SQL queries to get the following information from the "University" database.

Lab 3

Use Microsoft Access to create and execute more SQL for the databases that you created in Lab 1. These SQL statements should add new rows to an existing table, modify the values of some of the attributes in rows already in the table, and delete rows already in a table.

You might find it convenient to parametrise some of the values in the SQL, so that you can supply them when you execute the statement.

Assignment

Choose an application area with which you are familiar, something from your experience or your interests, and create a database to manage data appropriate to that application area.

You should submit a report about your database with the following contents in order:

1. An informal description of the application area, of the data that you plan to manage in the database, and a list of the transactions that you plan to support with the database.
2. An entity-relationship diagram showing the entities that you have identified in the application area, and their relationships. There should not be too many entities, and there should be at least one many-to-many relationship in the diagram.
3. A relational data model of the data you are going to use. You should list the tables that you will use. For each table you should list the attributes that will be the columns of the table, and say which attribute or combination of attributes is the primary key. For each attribute you should specify its domain. You should also support your relational model with some samples showing typical data that might appear in the tables during the operation of the database.
4. A list of SQL statements that you will use to perform the operations described in the transaction list. Make clear which SQL statement corresponds to which operation in the list.

Try to choose something that is big enough to show that you have learned something from the course, but not so big that you can't complete it in time, or that produces a report that is too big or tedious to read. Examples that you might like to consider include:

• A small library of the kind used as examples in the lectures
• A collection of cookery books and the recipes in them
• A collection of stamps, postcards, wines, porcelain, ...
• Files on hard disk, and backup copies
• A sports club (golf, tennis ...)
• A local football league
• A wardrobe
• A small travel agency

Created 3 December 2001. Last updated 19 June 2002.