DDL Student Records

From SQLZOO
Revision as of 10:45, 20 October 2016 by Andr3w (talk | contribs) (The plan)
Jump to: navigation, 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.

SITS ERD.png

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

);