Database Management System Design and Development

The provided assignment is focused on designing and developing a database management system for a university. It involves inserting courses, instructors, and students into the system, as well as generating reports based on specific criteria. The assignment also requires the creation of a user-friendly interface for student registration and customization of reports based on user input. This document provides a detailed guide to completing this assignment.

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

DATABASE MANAGEMENT SYSTEM1
Table of Contents
Activity 1:........................................................................................................................................2
Activity 1.1:.................................................................................................................................2
Activity 1.2:.................................................................................................................................3
Activity 2:........................................................................................................................................4
Activity 3:........................................................................................................................................5
Activity 4:........................................................................................................................................6
Activity 5:......................................................................................................................................11
Bibliography:.................................................................................................................................13

DATABASE MANAGEMENT SYSTEM2
Activity 1:
Activity 1.1:
Business Name: The XYZ Education Institution
Nature of Business: The institution is consisted of Student Enrolment System which
allows a student to enroll in the institution. The XYZ Education Institution has a number of
majors and campuses. Each of the tutors are assigned to at least a single major. The organization
give grades against the major to every single student enrolled.
Scope and Size of Business: The scope of the business is to provide education to each
student based on the major he/she has selected. Having different campuses for different majors.
Providing a student to select different courses for a single major the student has selected. The
XYZ institution is a small and medium sized organization. The organization has low amount of
teachers and courses. The client base is also medium. The organization also operates its business
form a single location.
Description of the Business Model: The organization business model is very simple. The
organization allows a student to register into the system along with the documents that prove his
educational qualification. Based on the qualification and an interview, the institution allows the
student to enroll in the preferred major. The student transfers the fee of the Major while enrolled
in the institution.
Customer Services: The client in this institution’s perspective is the students. The student
pay for the major they have enrolled in. The organization provides classes for each of curses that
the student has selected.

DATABASE MANAGEMENT SYSTEM3
Customer Business Problems: The database will store all the data in a central location
and for a very long time. It allows easy access to data while maintaining redundancy of data. The
tracking of the student request will be very easy to understand.
Activity 1.2:
The business reporting requirements are as following.
1. Student Grade Report
2. Student Enrollment Report as per Major
3. List of Teachers Associated with Each Course
4. The Majors in the Campus
5. The student registered

DATABASE MANAGEMENT SYSTEM4
Activity 2:
Figure 1: Entity Relationship Table
(Source: Created by Author)
The data set are as following.
i. Student_ID
ii. Student_Name
iii. Phone_Number
iv. Enrolled
v. Campus_Address

DATABASE MANAGEMENT SYSTEM5
vi. Major
vii. Major_Enroll_Date
viii. Course_ID
ix. Course_title
x. Instructor_Name
xi. Instructor_Location
xii. Grade
Figure 2: Dependency Diagrams
(Source: Created by Author)
Activity 3:
The data dictionary is described below.
Attribute Data Type Range Example
Student_ID varchar 40 Std1
Student_Name varchar 40 Mikayla Barker
Phone_Number int 035343 4846
Enrolled varchar 40 Yes
Campus_Address varchar 40 Albury
Major varchar 40 BIT

DATABASE MANAGEMENT SYSTEM6
Major_Enroll_Date Date 2017-11-17
Course_ID varchar 40 ITC114
Course_title varchar 40 Database Sys
Instructor_Name varchar 40 Bob
Instructor_Location varchar 40 York
Grade varchar 40 HD
Activity 4:
Create Table:
Create Table Student (
Student_ID varchar(40) Not Null Primary Key,
Student_Name varchar(40),
Phone_Number int,
Enrolled varchar(40)
);
Create Table Major_List (
Major varchar(40) Not Null Primary Key,

DATABASE MANAGEMENT SYSTEM7
Campus_Address varchar(40)
);
Create Student_Major (
Student_ID varchar(40) Not Null Primary Key,
Major varchar(40) Not Null Primary Key,
Major_Enroll_Date DATE,
FOREIGN KEY (Student_ID) REFERENCES Student (Student_ID),
FOREIGN KEY (Major) REFERENCES Major_Taken (Major)
);
Create Table Course (
Course_ID varchar(40) Not Null Primary Key,
Course_title varchar(40),
Major varchar(40),
FOREIGN KEY (Major) REFERENCES Major_Taken (Major)
);
Create Table Instructor (
Instructor_Name varchar(40) Not Null Primary Key,
Instructor_Location varchar(40),

DATABASE MANAGEMENT SYSTEM8
Course_ID varchar(40),
FOREIGN KEY (Course_ID) REFERENCES Course (Course_ID)
);
Create Table Student_Grade (
Student_ID varchar(40),
Course_ID varchar(40),
Grade varchar(40),
FOREIGN KEY (Student_ID) REFERENCES Student (Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Course (Course_ID)
);
Populate Tables:
Insert Into Student Values (`std1`, `Lily Medley`, 40726458, `Yes`);
Insert Into Student Values (`std2`, ` Christian Wetherspoon`, 62188809, `No`);
Insert Into Student Values (`std3`, ` Finn Langslow`, 53190297, `Yes`);
Insert Into Student Values (`std4`, ` Marcus Lea`, 82691123, `Yes`);
Insert Into Student Values (`std5`, ` Brodie Wicks`, 53778750, `Yes`);
Insert Into Student Values (`std6`, ` Lucas Sellheim`, 67271694, `Yes`);
Insert Into Student Values (`std7`, ` Molly Brydon`, 46300164, `No`);

DATABASE MANAGEMENT SYSTEM9
Insert Into Student Values (`std8`, ` Hunter Dew`, 53008294, `Yes`);
Insert Into Student Values (`std9`, ` Jorja Jarman`, 53434103, `Yes`);
Insert Into Student Values (`std10`, ` Alica Money`, 40049771, `Yes`);
Insert Into Major Values (`BIT`, `York`);
Insert Into Major Values (`Account`, `Stanford`);
Insert Into Major Values (`Finance`, `D.C.`);
Insert Into Major Values (`Science`, `Holmberg`);
Insert Into Major Values (`Arts`, `Shire`);
Insert Into Major Values (`Fine Arts`, `Vince`);
Insert Into Major Values (`Sociology`, `Land1`);
Insert Into Major Values (`Psychology`, `Land2`);
Insert Into Major Values (`Film`, `Holly`);
Insert Into Major Values (`Music`, `Del`);
Insert Into Student_Major Values (`std1`, `Music`, `2018-01-11`);
Insert Into Student_Major Values (`std2`, `Fine Arts`, `2018-02-05`);
Insert Into Student_Major Values (`std3`, `Arts`, `2018-02-03`);
Insert Into Student_Major Values (`std4`, `BIT`, `2018-01-28`);
Insert Into Student_Major Values (`std5`, ` Psychology`, `2018-02-13`);

DATABASE MANAGEMENT SYSTEM10
Insert Into Student_Major Values (`std6`, ` Science`, `2018-01-11`);
Insert Into Student_Major Values (`std8`, ` Account`, `2018-01-11`);
Insert Into Student_Major Values (`std9`, ` Film`, `2018-02-11`);
Insert Into Student_Major Values (`std10`, ` Sociology`, `2018-02-01`);
Insert Into Course Values (`Sc111`, `Maths`, `Science`);
Insert Into Course Values (`SC112`, `Physics`, `Science`);
Insert Into Course Values (`ICT114`, `Database Sys`, ` BIT`);
Insert Into Course Values (`ICT112`, `Networking`, `BIT`);
Insert Into Course Values (`Psc113`, `forensic psychology`, `Psychology`);
Insert Into Course Values (`A111`, `History`, `Arts`);
Insert Into Course Values (`A116`, `Literature`, `Arts`);
Insert Into Course Values (`F111`, `Course for Direction`, ` Fiml`);
Insert Into Course Values (`F112`, `Acting`, `Film`);
Insert Into Course Values (`F113`, `Cinematography`, `Film`);
Insert Into Instructor Values (`Harrison Mortlock `, `70 Blairgowrie Avenue`, `F112`);
Insert Into Instructor Values (`Isla Burdekin`, `30 Moruya Road`, ` ICT114`);
Insert Into Instructor Values (`Natasha Gledson`, `93 Beach Street`, `Psc113`);
Insert Into Instructor Values (`Lauren Ancher`, `93 Kogil Street`, `ICT113`);

DATABASE MANAGEMENT SYSTEM11
Insert Into Instructor Values (`Anthony Goethe`, `88 Marx Hill Road`, `Sc111`);
Insert Into Instructor Values (`Brayden Packham`, `97 Balonne Street`, `F113`);
Insert Into Instructor Values (`Gemma Du Rieu`, `54 Sale Street`, `ICT112`);
Insert Into Instructor Values (`Stephanie Hudd`, `59 Savages Road`, `A111`);
Insert Into Instructor Values (`Elizabeth Smalley`, `40 Hunter Street`, `Psc113`);
Insert Into Instructor Values (`Elizabeth Smalley`, `40 Hunter Street`, `F111`);
Insert Into Student_Grade Values (`std1`, `F112`, `HD`);
Reporting:
Finding Students who have submitted application but rejected: Select * From Student
Where Enrolled = `No`;
Find student’s Major who have submitted in February: Select Major From
Student_Major Where Major_Enroll_Date > `2018-01-31`;
Activity 5:
1. The user interface in the student registration section will be consisting of text
boxes and input fields. The text boxes will indicate which data to be provided in
which input field. The radio buttons will also be used.
2. The interface will be created using simple colors like cream color, light blue and
many others.
3. The contents will be intact and meaningful.
4. The teachers will be entered into the system by the admin through the interface

DATABASE MANAGEMENT SYSTEM12
5. The interface will be customized as per the reports that will be shown