Database ER Diagram

Assessm
ent 2:
Database
March 31
201
9
Student ID:
Student name:

Report
ER Diagram
Assumptions
1. A student can have more than one subject.
2. A teacher can monitor only one school at a time. The monitor can be changed
in future.

Data Dictionary
Table Name Attribute Data type Size Constraints
subject title char 20 Primary Key
type char 50
school schoolCode char 20 Primary Key
name char 50
address char 100
phone char 20
teacher tfn char 20 Primary Key
firstName char 50
lastName char 50
gender char 1
qualification char 100
schoolCode char 20 Foreign Key
references
school
(schoolCode)
student studentNumbe
r
char 20 Primary Key
firstName char 50
lastName char 50
gender char 1
dob date
schoolCode char 20 Foreign Key
references
school
(schoolCode)
manager tfn char 20 Primary Key
Foreign Key
references
teacher (tfn)
schoolCode char 20 Foreign Key
references
school
(schoolCode)
startDate date
subjectStudent studentNumbe
r
char 20 Primary Key
Foreign Key
references
student
(studentNumbe
r)

title char 20 Primary Key
Foreign Key
references
subject (title)
subjectTeache
r
tfn char 20 Primary Key
Foreign Key
references
teacher (tfn)
title char 20 Primary Key
Foreign Key
references
subject (title)
teachingHours double
Database Implementation
Create Tables
DROP Database If EXISTS school;
create database school;
use school;
CREATE TABLE IF NOT EXISTS school (
schoolCode VARCHAR(20) NOT NULL,
schoolName VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL,
PRIMARY KEY (schoolCode))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS student (
studentNumber VARCHAR(20) NOT NULL,
FirstName VARCHAR(50) NOT NULL,