DDL Student Records

Creating a database: Student record database.

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

Windows and Linux

If you are on windows, accessing a Linux machine you can use putty and winscp to control MySQL.

Napier Students:

Your MariaDB account is on the Linux machine set08120.napier.ac.uk - use putty to connect to this server, use your Napier 8-digit account number.
You can use putty to get the command prompt, from that use ./mysql.sh to access the MariaDB prompt.
Alternatively you may use PHPMyAdmin - you will find your password in the file mysql.sh

Using MySQL from Linux

Use the command exit to get out of MySQL
Use the command exit to get out of putty

Using PHPMyAdmin

http://enudb.soc-web-liv-02.napier.ac.uk/ is the PHPMyAdmin account

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

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.

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
);

Add some students to the database

Add the following students:

Daniel Radcliffe, matric 40001010 DoB 1989-07-23
Emma Watson, matric 40001011 DoB 1990-04-15
Rupert Grint, matric 40001012 DoB 1988-10-24

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 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,
  credits INT NOT NULL DEFAULT 20
);

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

INSERT INTO module(module_code, module_title, level) VALUES('HUF07101', 'Herbology', 7);
INSERT INTO module(module_code, module_title, level) VALUES('SLY07102', 'Defence Against the Dark Arts', 7);
INSERT INTO module(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.

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

CREATE TABLE registration(
  matric_no CHAR(8),
  module_code CHAR(8),
  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 Defence Against the Dark Arts, 40 in Herbology and does not yet have a mark for History of Magic
Emma got 99 in Defence Against the Dark Arts, did not take Herbology and has no mark for History of Magic
Ron got 20 in Defence Against the Dark Arts, 20 in Herbology and is not registered for History of Magic

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);

Run some queries

Produce the results for SLY07102. For each student show the surname, firstname, result and 'F' 'P' or 'M'

F for a mark of 39 or less
P for a mark between 40 and 69
M for a mark of 70 or more

DDL Extended Student Records Tutorial

Your server today is: digitalocean-sqlzoo