DDL Student Records

Jump to navigation Jump to search

Creating a database: Student record database.

In this tutorial you will learn how to create tables and set up links between tables.

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. There are a number of modules, identified by a module code. 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

  • The primary key is matric_no. Matric numbers are exactly 8 characters.
  • Use up to 50 characters for names.
  • There is a specific data type for dates.
  matric_no CHAR(8) PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE

Add some students to the database

Add the following students:

  • Daniel Radcliffe, DoB 1989-07-23
  • Emma Watson, DoB 1990-04-15
  • Rupert Grint, 1988-10-24

CREATE module

A module has the following columns

  • module_code (primary key, 8 characters)
  • module_title (up to 50 characters)
  • level (integer)
  • credits (integer default value is 20)
CREATE TABLE `module`(
  module_code CHAR(8) PRIMARY KEY,
  module_title VARCHAR(50) NOT NULL,
  level INT NOT NULL,

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

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.

Make sure you include a composite primary key and two foreign keys.

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)

Add some data

  • Daniel got 90 in Defense Against the Dark Arts, 40 in Herbology and does not yet have a mark for History of Magic
  • Emma got 99 in Defense Against the Dark Arts, 100 in Herbology and does not yet have a mark for History of Magic
  • Daniel got 20 in Defense Against the Dark Arts, 20 in Herbology and does not yet have a mark for History of Magic