Database Design: Normalisation - Exercise 7, Creating Class lists

Navigation:

---

Home

Database Design Home

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

This exercise requires you to develop the Student Records database to be able to store lists of Classes that are taught. In addition, you will need to produce reports of the names of students attending each class (a class register).

 

The two tables shown here (Figure 7.10 and Figure 7.11) are your starting point.

 

Classes

CLASSREF	TITLE	SEMESTER	ROOM	DAY
1	Introduction to Database Design	1	1	Monday
2	Introduction to Database Design	1	1	Tuesday
3	Key Skills - Internet	1	1	Monday
4	Key Skills - Internet	1	2	Wednesday
5	Key Skills - Statistics	1	8	Wednesday
6	Computer Systems	2	3	Friday

Figure 7.10: The CLASSES table

 

The table CLASSES holds details of each class, the title, which semester it is taught in, which room it is in and which day of the week it is timetabled for (is this a look up field?).

Many students can and (hopefully) do attend many classes, each class has many students. We need another table. This table will be a sort of class register table (in any event, it will hold details of which students attend which classes).

 

The REGISTER table consists of two foreign keys, CLASSID and SREF. SREF cross references with SREF in the table STUDREC and CLASSID links to the CLASSREF field in the CLASSES table.

 

NOTE: it is possible to have two (or more) fields combined as a key field. If you do this in the REGISTER table with CLASSID and SREF the Access DBMS will prevent you from accidentally entering the same student on the class more than once. Both fields are selected by holding the SHIFT key down and then clicking on each field in turn (then click on the key field icon as usual).

 

• Create the two tables CLASSES and REGISTER.
• Enter the data shown in Figure 7.10 and Figure 7.11.
• Set up the appropriate relationships. You have to list all classes whether or not they have any students in them, and you must only list students if they are members of a class.
• Create a query called CLASS LISTS to list all classes and students in those classes
• Create a report called CLASS LISTS that is similar to the one shown below.
• Provide a brief account of how relational techniques enable you to implement the class lists report. You should contrast this with the the difficulties inherent in using a flat file design. Do not write more than 600 words (be clear that, in order to achieve at NOC Level 3, your written work must meet the overarching Level 3 criteria - in particular, you should show evidence of reading outside of the taught sessions and you should provide a bibliography).

Note:

• That the report lists students in alphabetical order within each class.
• The report is divided into semesters.
• There is a calculated field that gives the total number of students on each class.
• There is a new page after each class.
• You should submit a printed copy of your report for marking.

 

   

Class Lists Report - page 1

Class Lists
SEMESTER 1
TITLE	Introduction to database design
ROOM	1
DAY	Monday
	SNAME	INIT	DOB
	ARNOTT	K	01-Aug-60
	CARTER	J	21-Mar-54
	ISHEMO	S	05-Dec-50
	JONES	A	10-Nov-48
	LANGLEY	S	21-Aug-57
	OSMAN	TJ	29-Sep-53
	WILSON	H	07-Jul-62
Total Number of Students for Introduction to database design = 7
18 December 2001			Page 1 of 5

Class Lists Report - Page 2

TITLE	Introduction to database design
ROOM	1
DAY	Tuesday
	SNAME	INIT	DOB
	ARNOTT	A	23-Aug-54
	ARNOTT	B	23-May-62
	BROWN	J	29-Sep-58
	GREEN	N	30-Sep-58
	HEY	N	10-Oct-55
	JACKSON	H	21-Apr-41
	WILSON	K	13-Mar-65
Total Number of Students for Introduction to database design = 7
18 December 2001			Page 2 of 5

 

Class Lists Report - Page 3

TITLE	Key Skills - Internet
ROOM	1
DAY	Monday
	SNAME	INIT	DOB
	BROWN	J	29-Sep-58
	GREEN	J	06-Aug-69
	LANGLEY	S	21-Aug-57
	OSMAN	TJ	29-Sep-53
	WILSON	H	07-Jul-62
	WILSON	K	13-Mar-65
Total Number of Students for Key Skills - Internet = 6
18 December 2001			Page 3 of 5

Class Lists Report - Page 4

TITLE	Key Skills - Statistics
ROOM	8
DAY	Wednesday
	SNAME	INIT	DOB
	CARTER	J	21-Mar-54
	GREEN	J	09-Aug-45
	OSMAN	TJ	29-Sep-53
Total Number of Students for Key Skills - Statistics = 3
18 December 2001			Page 4 of 5

 

Class Lists Report - Page 5

SEMESTER 2
TITLE	Computer Systems
ROOM	3
DAY	Friday
	SNAME	INIT	DOB
	GREEN	J	09-Aug-45
	HEY	N	10-Oct-55
	WATSON	F	03-Mar-58
Total Number of Students for Computer Systems = 3
18 December 2001			Page 5 of 5