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.
- Your MariaDB account is on the Linux machine inf08104.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
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.
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] ✓
- [registration, module, 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');
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);
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