Database class supplementary reading

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

Lecture 1: IntroductionCB, Chapters 1 and 2
Lecture 2: Relational model and normalisationCB, Chapter 3
Lecture 3: Entity-relationship modelCB, Chapter 11
Lecture 4: Relational algebra and relational calculusCB, Chapter 4
Lecture 5: (continued)CB, Chapter 4
Lecture 6: SQLCB, Chapter 5
Lecture 7: (continued)CB, Chapter 5
Lecture 8: Physical representationsCB, Appendix C
Lecture 9: TransactionsCB, Chapter 19

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:

Student

SRegSNameSHallStaffID
20283CoatesWellesley22463
25477BlissCavendish30953
40935HowellsRussell42442
62206MaconchyManners20735
72978RawsthorneCavendish22463
78883RutterWellesley20735

Lecturer

StaffIDLNameLPhone
20735Corelli2345
22463Caine6789
30953Doyle5678
42442Haggard4567
54746Kipling3456

Unit

UCodeUTitleUMax
ABC12Operating Systems50
ABC14Database Design150
ABC27Pascal Progamming90
ABC28Software Engineering150

Teaches

StaffIDUCode
20735ABC12
20735ABC14
22463ABC28
42442ABC27
42442ABC28
54746ABC14

Enrolment

SRegUCodeDate
20283ABC1401/10/2001
20283ABC2801/10/2001
25477ABC1201/10/2001
25477ABC1401/10/2001
40935ABC1401/10/2001
40935ABC2701/10/2001
62206ABC1401/10/2001
62206ABC2801/10/2001
72978ABC1201/10/2001
72978ABC1401/10/2001
72978ABC2801/10/2001
78883ABC1401/10/2001
78883ABC2701/10/2001

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:

(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.

Query nameInformation to be displayed
HallCountList the names of the halls, and the number of students in each.
SbyNList the names of the students whose names begin with M, or any later letter of the alphabet.
SbyRList the names of the students whose registration numbers are 40000 or greater.
TNamePhoneList the names of the students, the names of their tutors, and the phone numbers of the tutors.
LandPhoneList the lecturers and their phone numbers.
ULT100List the titles of the units that can take less than 100 students.
WhoTeachesWhatList the names of the lecturers and the units they teach.
WhoTeachesHowManyList the names of the lecturers and the number of units they teach.
Sand UnitsList the students and the courses they are enrolled on.

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.
The whole report should not exceed ten sides of A4 paper, and should be submitted by noon on Monday 22 July 2002.

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:


Created 3 December 2001. Last updated 19 June 2002.