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.
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.
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, DoB 1989-07-23
- Emma Watson, DoB 1990-04-15
- 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
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