DDL Extended Student Records Tutorial

This tutorial builds on the Student Record System

DROP TABLE registration;
DROP TABLE `module`;
DROP TABLE student;
CREATE TABLE student(
  matric_no CHAR(8) PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE
);
INSERT INTO student VALUES ('40001010','Daniel','Radcliffe','1989-07-23');
INSERT INTO student VALUES ('40001011','Emma','Watson','1990-04-15');
INSERT INTO student VALUES ('40001012','Rupert','Grint','1988-10-24');

CREATE TABLE `module`(
  module_code CHAR(8) PRIMARY KEY,
  module_title VARCHAR(50) NOT NULL,
  level INT NOT NULL,
  credits INT NOT NULL DEFAULT 20
);
INSERT INTO `module` VALUES ('HUF07101','Herbology');
INSERT INTO `module` VALUES ('SLY07102','Defence Against the Dark Arts');
INSERT INTO `module` VALUES ('HUF08102','History of Magic');

CREATE TABLE registration(
  matric_no CHAR(8),
  module_code CHAR(8),
  module_title VARCHAR(50) NOT NULL,
  result DECIMAL(4,1),
  PRIMARY KEY (matric_no,module_code),
  FOREIGN KEY (matric_no) REFERENCES student(matric_no),
  FOREIGN KEY (module_code) REFERENCES `module`(module_code)
);
INSERT INTO registration VALUES ('40001010','SLY07102',90);
INSERT INTO registration VALUES ('40001010','HUF07101',40);
INSERT INTO registration VALUES ('40001010','HUF08102',null);
INSERT INTO registration VALUES ('40001011','SLY07102',99);
INSERT INTO registration VALUES ('40001011','HUF08102',null);
INSERT INTO registration VALUES ('40001012','SLY07102',20);
INSERT INTO registration VALUES ('40001012','HUF07101',20);

Including historical records

One problem with the existing database is that it does not allow for historical records. In this tutorial you will fix this.

The table modinst

Modules are run in sessions that are labelled with the academic year (for example 2016/7) and the trimester (for example TR1). The table modinst records which module is running.

+-----------+------+---+
|module_code|ayr   |tri|
+-----------+------+---+
|HUF08102   |2015/6|TR1|
|SLY07102   |2015/6|TR2|
|HUF07101   |2015/6|TR2|
|HUF08102   |2016/7|TR1|
|SLY07102   |2016/7|TR2|
|HUF07101   |2016/7|TR2|
+-----------+------+---+

Create the table modinst - be sure to include the foreign key to the module table. The primary key should be all three columns.

ALTER the results table

The results table should include the session in which the mark was gained.

All of the marks in the original table were gained in session 2015/6 TR2. The Herbology registration is for 2016/7 TR1.

• Add two columns to the registration table for ayr and tri.
• Set values for these columns as mentioned
• Add a new foreign key from results to modinst