Student Records, Logical Design

The database is intended to record the grades of students studying modules at a University.

There are a number of students, identified by a matric number. A typical student is Daniel Radcliffe who has matric number 40001010 and DoB 1989-07-23

There are a number of modules, identified by a module code it also has a module name. A typical module is HUF07101 - Herbology

Each student studies many modules and will get a result for each. Each module is studied by many students.

The plan

We will create three tables, one for each of the entities in the diagram shown above. Where there is a "parent/child" relation (a one to many) we must create the ONE before we create the MANY. That means we should create the table in the order

[student, module, registration] ✓

[module, student, registration] ✓

but not

[registration, module, student] ✕

CREATE student

You need to create a table with these columns: matric_no, first_name, last_name, date_of_birth

Add some modules

Add the following modules, they are all 20 credits - the first two digits let you know the level:

HUF07101, Herbology

SLY07102, Defense Against the Dark Arts

HUF08102, History of Magic

INSERTINTOmodule(module_code,module_title,level)VALUES('HUF07101','Herbology',7);INSERTINTOmodule(module_code,module_title,level)VALUES('SLY07102','Defence Against the Dark Arts',7);INSERTINTOmodule(module_code,module_title,level)VALUES('HUF08102','History of Magic',8);

CREATE registration

The registration table has three columns matric_no, module_code, result - the matric_no and module_code types should match the tables you have just created. Result should be a number with one decimal place.