Database Design and Implementation: ER Diagram, SQL, and Tables

This database report details the design and implementation of a relational database. It begins with assumptions and an ER diagram illustrating the relationships between entities such as schools, students, teachers, and subjects. A data dictionary defines the attributes and constraints for each ...Read More

Assessment 2: Database
March 31
2019
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 studentNumber 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 studentNumber char 20 Primary Key
Foreign Key
references student
(studentNumber)
title char 20 Primary Key
Foreign Key
references subject

(title)
subjectTeacher 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,
LastName VARCHAR(50) NOT NULL,

gender VARCHAR(1) NOT NULL,
dob datetime NOT NULL,
schoolCode VARCHAR(20) NOT NULL,
PRIMARY KEY (studentNumber),
FOREIGN KEY (schoolCode)
REFERENCES school(schoolCode))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS teacher (
tfn VARCHAR(20) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
gender VARCHAR(1) NOT NULL,
qualification VARCHAR(100) NOT NULL,
schoolCode VARCHAR(20) NOT NULL,
PRIMARY KEY (tfn),
FOREIGN KEY (schoolCode)
REFERENCES school(schoolCode))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS subjects (
title VARCHAR(20) NOT NULL,
subjectType VARCHAR(50) NOT NULL,
PRIMARY KEY (title))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS manager (
tfn VARCHAR(20) NOT NULL,

schoolCode VARCHAR(20) NOT NULL,
startDate date NOT NULL,
PRIMARY KEY (tfn),
FOREIGN KEY (tfn)
REFERENCES teacher(tfn),
FOREIGN KEY (schoolCode)
REFERENCES school(schoolCode))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS subjectSudent (
studentNumber VARCHAR(20) NOT NULL,
title VARCHAR(20) NOT NULL,
PRIMARY KEY (studentNumber, title),
FOREIGN KEY (studentNumber)
REFERENCES student(studentNumber),
FOREIGN KEY (title)
REFERENCES subjects(title))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS subjectTeacher (
tfn VARCHAR(20) NOT NULL,
title VARCHAR(20) NOT NULL,
teachingHours double NOT NULL,
PRIMARY KEY (tfn, title),
FOREIGN KEY (tfn)
REFERENCES teacher(tfn),
FOREIGN KEY (title)

REFERENCES subjects(title))
ENGINE = InnoDB;
Insert Statements
Insert into School Table
insert into school (schoolCode, schoolName, address, phone) values
('S101','St. Marry','10 Soldier Street, TAS','6745883838'),
('S102','St. Joseph','5 Yellow Street, NJ','6745883838'),
('S103','St. Thomson','36 White Street, TAS','6745883838'),
('S104','St. Thomas','10 Mango Street, TAS','6745883838'),
('S105','St. Francis','11 Soldier Street, TAS','6745883838');
Insert into Student Table
insert into student (studentNumber, FirstName, LastName, gender, dob, schoolCode) values
('101','John','Smith','M',STR_TO_DATE("07-04-1999","%m-%d-%Y"),'S101'),
('102','WILLIAM','Jainani','M',STR_TO_DATE("04-05-1990","%m-%d-%Y"),'S102'),
('103','Rose','Kelly','F',STR_TO_DATE("01-01-1989","%m-%d-%Y"),'S101'),
('104','Lee','Karporrzis','M',STR_TO_DATE("03-04-1999","%m-%d-%Y"),'S101'),
('105','Noel','Ritter','M',STR_TO_DATE("07-09-1979","%m-%d-%Y"),'S101');

Insert into Teacher Table
insert into teacher (tfn, FirstName, LastName, gender, qualification, schoolCode) values
('2345671845','Mack','Cooper','M','M.Sc. (C.S.)','S101'),
('5624572464','Nike','Kelvin','M','B.Sc. (C.S.)','S102'),
('7837373773','Katherine','Parker','F','M.Sc. Botany','S101'),
('6238428747','Joy','William','M','M. Tech','S101'),
('3805995987','Noah','Black','M','M.Sc. Botany','S101');

Insert into Subjects Table
insert into subjects(title, subjectType) values
('Botany', 'Biology'),
('Geology', 'Biology'),
('Python', 'Computer'),
('Java', 'Computer'),
('C++', 'Computer');
Insert into Manager Table
insert into manager (tfn, schoolCode, startDate) values
('2345671845','S101',STR_TO_DATE("07-04-1999","%m-%d-%Y")),
('5624572464','S102',STR_TO_DATE("03-01-1999","%m-%d-%Y")),
('3805995987','S101',STR_TO_DATE("01-08-2009","%m-%d-%Y")),
('6238428747','S101',STR_TO_DATE("02-02-2010","%m-%d-%Y")),
('7837373773','S101',STR_TO_DATE("09-05-2015","%m-%d-%Y"));

Insert into subjectStudent Table
insert into subjectSudent (studentNumber, title) values
('101','Botany'),
('101','Geology'),
('102','Botany'),
('102','Geology'),
('103','Python'),
('104','Java'),
('105','C++');

Insert into subjectTeacher Table
insert into subjectTeacher (tfn, title, teachingHours) values
('2345671845','Botany',2),
('5624572464','Geology',1),
('3805995987','Botany',1),
('6238428747','Python',2),
('7837373773','C++',2);

References
P. Litwin. (n.d.). Fundamentals of Relational Database Design. Retrieved from:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign
.aspx.
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams. Retrieved from:
http://creately.com/blog/diagrams/er-diagrams-tutorial/.
Oracle (2000). Drawing the Entity Relationship Diagram. Retrieved from:
http://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/05_dev1.htm.