Database Design and Implementation

This assignment focuses on database design and implementation. Students are tasked with creating a database schema for a university system, including tables for students, teachers, units, enrollments, results, and emergency contacts. The assignment also requires the creation of SQL INSERT statements to populate the database and VIEW queries to simplify data access.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student:
Name of the University:
Author Note

1
DATABASE MANAGEMENT SYSTEM
List of Business Rules
The list of business for the Western Melbourne University is provided below;
 The database is required to store all the basic information about the students.
 The database is required to store all the basic information about the staffs of the
university.
 The database is required to store the information about the enrollment of the students
 The database is required to store all the information about the teachers in the university.
 The database is required to store all the assessments and the results of the students in the
university.
 The database is required to store all the information about the units in the semesters.

2
DATABASE MANAGEMENT SYSTEM
ER Diagrams
Data dictionary
Students (studentsID (pk), StudentName, Address, ContactNumber, Email, EmergencyName,
emergencyContact, Realtionship)
Staffs (StaffID (pk), StaffName, StaffAdress)
Teachers (TeacherID (pk), TeacherName, TeacherAddress, DepartmentID, DepartmentName)

3
DATABASE MANAGEMENT SYSTEM
Enrollment (EnrollmentID (pk), studentID, StudentName, StaffID, DepartmentID, UnitID,
UnitName, TotalPayment)
Results (StudentID, UnitID, Grade)
Functional Dependencies
Normalization
The following are tables are already provided in the first normal form. Hence,
The 1NF for the database:
Students (studentsID (pk), StudentName, Address, ContactNumber, Email, EmergencyName,
emergencyContact, Realtionship)
Staffs (StaffID (pk), StaffName, StaffAdress)
Teachers (TeacherID (pk), TeacherName, TeacherAddress, DepartmentID, DepartmentName)
Enrollment (EnrollmentID (pk), studentID, StudentName, StaffID, DepartmentID, UnitID,
UnitName, TotalPayment)
Results (StudentID, UnitID, Grade)
The 2NF for the database:
Students (studentsID (pk), StudentName, Address, ContactNumber, Email)
Emergency (EmergencyName, emergencyContact, Realtionship)
Staffs (StaffID (pk), StaffName, StaffAdress)

4
DATABASE MANAGEMENT SYSTEM
Teachers (TeacherID (pk), TeacherName, TeacherAddress, DepartmentID, DepartmentName)
Enrollment (EnrollmentID (pk), studentID, StudentName, StaffID, DepartmentID, UnitID,
UnitName, TotalPayment)
Results (StudentID, UnitID, Grade)
The 3NF for the database:
Students (studentsID (pk), StudentName, Address, ContactNumber, Email, EmergencyID (fk),
Realtionship)
Emergency (EmergencyID (pk), EmergencyName, EmergencyContact)
Staffs (StaffID (pk), StaffName, StaffAdress)
Teachers (TeacherID (pk), TeacherName, TeacherAddress, DepartmentID (fk))
Department (DepartmentID, DepartmentName)
Enrollment (EnrollmentID (pk), studentID (fk), StaffID (fk), DepartmentID (fk), UnitID (fk),
TotalPayment)
Unit (UnitID (pk), UnitName, UnitPrice)
Results (ResultID (pk), StudentID(fk) , UnitID (fk), Grade)
Database Design
The database design and the data definitions for the tables that are to be implemented is
provided below.
Students

5
DATABASE MANAGEMENT SYSTEM
Attributes Type Size Key
StudentID VARCHAR 10 Primary Key
StudentName VARCHAR 20
Address VARCHAR 50
ContactNumber VARCHAR 20
Email VARCHAR 50
EmergencyID VARCHAR 10
Realtionship VARCHAR 10
Emergency
Attributes Type Size Key
EmergencyID VARCHAR 10 Primary Key
EmergencyName VARCHAR 20
Emergencyaddress VARCHAR 50
Staffs
Attributes Type Size Key
StaffID VARCHAR 10 Primary Key
StaffName VARCHAR 20
StaffAddress VARCHAR 50

6
DATABASE MANAGEMENT SYSTEM
Teachers
Attributes Type Size Key
TeachersID VARCHAR 10 Primary Key
TeachersName VARCHAR 20
TeachersAddress VARCHAR 50
DepartmentID VARCHAR 10
Department
Attributes Type Size Key
DepartementID VARCHAR 10 Primary Key
DepartmentName VARCHAR 20
Enrollment
Attributes Type Size Key
EnrollmentID VARCHAR 10 Primary Key
StudentID VARCHAR 10
StaffID VARCHAR 10
DepartmentID VARCHAR 10
UnitID VARCHAR 10
TotalPayment VARCHAR 20

7
DATABASE MANAGEMENT SYSTEM
Unit
Attributes Type Size Key
UnitID VARCHAR 10 Primary Key
UnitName VARCHAR 20
UnitPrice VARCHAR 20
Result
Attributes Type Size Key
ResultID VARCHAR 10 Primary Key
StudentID VARCHAR 10
UnitID VARCHAR 10
Grade VARCHAR 10
SQL for table
Create query
USE `western_melbourne_university`;
CREATE TABLE `department` (
`DepartmentId` varchar(10) NOT NULL,
`DepartmentName` varchar(20) NOT NULL

8
DATABASE MANAGEMENT SYSTEM
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `emergency` (
`EmergencyId` varchar(10) NOT NULL,
`EmergencyName` varchar(20) NOT NULL,
`EmergencyAddress` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `enrollment` (
`EnrollmentId` varchar(10) NOT NULL,
`StudentId` varchar(10) NOT NULL,
`StaffId` varchar(10) NOT NULL,
`DepartmentId` varchar(10) NOT NULL,
`UnitId` varchar(10) NOT NULL,
`TotalPayment` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `results` (
`ResultId` varchar(10) NOT NULL,
`StudentId` varchar(10) NOT NULL,
`UnitId` varchar(10) NOT NULL,

9
DATABASE MANAGEMENT SYSTEM
`Grade` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `staffs` (
`StaffId` varchar(10) NOT NULL,
`StaffName` varchar(20) NOT NULL,
`StaffAddress` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `students` (
`StudentsId` varchar(10) NOT NULL,
`StudentsName` varchar(20) NOT NULL,
`Address` varchar(50) NOT NULL,
`Email` varchar(50) NOT NULL,
`EmergencyId` varchar(10) NOT NULL,
`Realtionship` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `teachers` (
`TeacherId` varchar(10) NOT NULL,
`TeacherName` varchar(20) NOT NULL,

10
DATABASE MANAGEMENT SYSTEM
`TeacherAddress` varchar(50) NOT NULL,
`DepartmentId` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `unit` (
`UnitId` varchar(10) NOT NULL,
`UnitName` varchar(20) NOT NULL,
`UnitPrice` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `department`
ADD PRIMARY KEY (`DepartmentId`);
ALTER TABLE `emergency`
ADD PRIMARY KEY (`EmergencyId`);
ALTER TABLE `enrollment`
ADD PRIMARY KEY (`EnrollmentId`);
ALTER TABLE `results`

11
DATABASE MANAGEMENT SYSTEM
ADD PRIMARY KEY (`ResultId`);
ALTER TABLE `staffs`
ADD PRIMARY KEY (`StaffId`);
ALTER TABLE `students`
ADD PRIMARY KEY (`StudentsId`);
ALTER TABLE `teachers`
ADD PRIMARY KEY (`TeacherId`);
ALTER TABLE `unit`
ADD PRIMARY KEY (`UnitId`);
Insert Query
INSERT INTO `department` (`DepartmentId`, `DepartmentName`) VALUES
('D01', 'Art'),
('D02', 'Science'),
('D03', 'Engineering'),

12
DATABASE MANAGEMENT SYSTEM
('D04', 'Law'),
('D05', 'Business'),
('D06', 'Education'),
('D07', 'Information Technolo');
INSERT INTO `emergency` (`EmergencyId`, `EmergencyName`, `EmergencyAddress`)
VALUES
('E01', 'Kevin Prince', '34 Melbourne road'),
('E02', 'Ben White', '21 Melbourne street'),
('E03', 'Tim Cahill', '21 Melbourne street'),
('E04', 'Steve Davies', '43 Melbourne road'),
('E05', 'Tim Cahill', '21 Melbourne street');
INSERT INTO `enrollment` (`EnrollmentId`, `StudentId`, `StaffId`, `DepartmentId`, `UnitId`,
`TotalPayment`) VALUES
('E01', 'ST01', 'S01', 'D01', 'U01', '$11200'),

13
DATABASE MANAGEMENT SYSTEM
('E02', 'ST02', 'S02', 'D02', 'U02', '$10400'),
('E03', 'ST03', 'S03', 'D03', 'U03', '$11600'),
('E04', 'ST04', 'S04', 'D04', 'U04', '$10600'),
('E05', 'ST05', 'S05', 'D05', 'U05', '$15000');
INSERT INTO `results` (`ResultId`, `StudentId`, `UnitId`, `Grade`) VALUES
('1', 'ST01', 'U01', 'P'),
('2', 'ST02', 'U02', 'D'),
('3', 'ST03', 'U03', 'C'),
('4', 'ST04', 'U04', 'P'),
('5', 'ST05', 'U05', 'HD');
INSERT INTO `staffs` (`StaffId`, `StaffName`, `StaffAddress`) VALUES
('S01', 'Harry Kane', '37 Melbourne Street'),
('S02', 'Min son', '41 Melbourne Street'),
('S03', 'Delli Alli', '56 Melbourne Road'),

14
DATABASE MANAGEMENT SYSTEM
('S04', 'Demba Dembele', '91 Melbourne Street'),
('S05', 'Erik Kane', '44 Melbourne Road');
INSERT INTO `students` (`StudentsId`, `StudentsName`, `Address`, `Email`, `EmergencyId`,
`Realtionship`) VALUES
('ST01', 'Alvaro Morata', '34 Melbourne street', '[e-mail]', 'E01', 'Father'),
('ST02', 'David Luiz', '81 Melbourne road', '[e-mail]', 'E02', 'Brother'),
('ST03', 'Gary Cahill', '21 Melbourne street', '[e-mail]', 'E03', 'Uncle'),
('ST04', 'Eden Hazard', '43 Melbourne road', '[e-mail]', 'E04', 'Brother'),
('ST05', 'Cesar Azpilicueta', '21 Melbourne street', '[e-mail]', 'E05', 'GrandFathe');
INSERT INTO `teachers` (`TeacherId`, `TeacherName`, `TeacherAddress`, `DepartmentId`)
VALUES
('T01', 'David Silva', '98 Melbourne road', 'D01'),
('T02', 'Raheem Sterling', '42 Melbourne Road', 'D02'),
('T03', 'Leroy Sane', '55 Melbourne Road', 'D03'),
('T04', 'Ilkay Gundongan', '71 Melbourne street', 'D04'),

15
DATABASE MANAGEMENT SYSTEM
('T05', 'Bernardo Silva', '36 Melbourne Street', 'D04'),
('T06', 'John Stones', '76 Melbourne Road', 'D05'),
('T07', 'James Milner', '42 Melbourne Road', 'D06'),
('T08', 'Vincent Kompany', '41 Melbourne Road', 'D07'),
('T09', 'Sergio Aguero', '53 Melbourne Street', 'D03'),
('T10', 'Joe Hart', '51 Melbourne Street', 'D02');
INSERT INTO `unit` (`UnitId`, `UnitName`, `UnitPrice`) VALUES
('U01', 'Unit B', '$2800'),
('U02', 'Unit A', '$2600'),
('U03', 'Unit c', '$2900'),
('U04', 'Unit D', '$2650'),
('U05', 'Unit E', '$2500');
Create View Query
CREATE VIEW ENROLL AS SELECT * FROM enrollment;
CREATE VIEW Assessments AS SELECT * FROM results;

16
DATABASE MANAGEMENT SYSTEM
Screenshots
Database structure
Table Structures

17
DATABASE MANAGEMENT SYSTEM

18
DATABASE MANAGEMENT SYSTEM
Table data

19
DATABASE MANAGEMENT SYSTEM