Database Management System Assignment - Database Design and SQL

This database management system assignment presents a comprehensive solution for designing and implementing a database for Western Melbourne University. The assignment begins with a list of business rules, followed by ER diagrams illustrating the relationships between entities such as Students, Staffs, Teachers, Enrollment, and Results. A detailed data dictionary defines the attributes of each entity, including data types and key constraints. The solution progresses through normalization, transforming the initial database schema into 3NF to eliminate data redundancy and ensure data integrity. The database design outlines the structure of the tables, specifying attributes, data types, and primary keys. SQL scripts are provided for creating tables, including the department, emergency, enrollment, results, staffs, students, teachers, and unit tables, along with insert queries to populate the tables with sample data. Additionally, the assignment includes SQL views for enrollment and assessments, and concludes with screenshots of the database structure, table structures, and table data, providing a complete and practical demonstration of database design and implementation principles. The solution also includes a bibliography.

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'),