University Databases

Courses at a university consist of modules. Each module is taught by one or more lecturers and each lecturer teaches at least one module. Each module has a book list consisting of one or more books. A book may be used on more than one module.

Modules have a code and a name. The code consists of two letters followed by 4 digits, for example CP1051 is called An Introduction to Visual Basic.

Each lecturer has a telephone extension number and an office; these may be shared with other lecturers. The telephone extension is a four-digit number and all offices have a five-character identification consisting of two letters and three digits, for example, MU217. Other than the names of lecturers, no personal details of lecturers are to be held.

A database is required that holds details of lecturers, modules and books. It has already been decided to hold the data in the following five tables.

• Lecturer – holds details of each lecturer.
• Module – holds details of each module.
• LecturerModule – acts as a link between the Lecturer and Module tables. Book – holds details of each book.
• ModuleBook – acts as a link between the Module and Book tables.

(a) Table Design

(i) Design the Lecturer table. You must state the name, data type and purpose of each attribute in the table. You should also specify the key for the table.

You may use a screen dump (or dumps) of your design as evidence. [5]

(ii) Design the Module table. You must state the name, data type and purpose of each attribute in the table. You should also specify the key for the table.

You may use a screen dump (or dumps) of your design as evidence. [3]

(iii) Design the LecturerModule table. You must state the name, data type and purpose of each attribute in the table. You should also specify the key for the table.

You may use a screen dump (or dumps) of your design as evidence. [3]

(iv) Design the Book table. You must state the name, data type and purpose of each attribute in the table. You should also specify the key for the table.

You may use a screen dump (or dumps) of your design as evidence. [4]

(v) Design the ModuleBook table. You must state the name, data type and purpose of each attribute in the table. You should also specify the key for the table.

You may use a screen dump (or dumps) of your design as evidence. [3]

(b) Validation

Create validation checks to ensure that

1. the office identification is valid;
2. the module code is valid;
3. the telephone extension number is valid;
4. only valid data is put in the LecturerModule table;
5. only valid data is put in the ModuleBook table.

You should include evidence that you have created all five validations. [5]

(c) Data Input

Create data for your five tables. You should include at least 5 modules, 10 books and 15 lecturers. Make sure that the data is suitable for testing the rest of this task.

You may use screen dumps of your tables as evidence. [10]

Parts (d) and (e) ask you to produce reports. Marks will be awarded for the layout and contents of the reports.

(d) Reports

Create a report that lists the details of all the lecturers for each module.

Include a copy of the report as evidence. You should also include evidence showing how your solution created the report. [5]

(e) Reports 2

Create a report that lists full details of all the books used by a lecturer. The identity of the lecturer should be input by the user when the report is requested.

Include evidence that the user has input the Lecturer ID and a copy of the report produced. You should also include evidence showing how your solution created the report. [5]

This task was worth 43 Marks.  It was a software development task and an implementation task that orginally appeared in OCR 2507 Task 1 Jun 2005.  A similar task worth 39 Marks appeared in 2008.  All rights and copyright to OCR.  Please refer to the OCR Copyright Statement for further information.

IMAGE CREDIT: https://en.wikipedia.org/wiki/University