Database Design and Development Report - GCS0903B - Assignment 1

This report details the design and development of a relational database system for FPT University, focusing on managing student grades. It begins by outlining the system requirements specification (SRS), including user roles and business processes, followed by the logical design represented through an Entity Relationship Diagram (ERD). The report then explains the relationships between the tables: Student, Lecturer, Class, Grade, and Subject. Finally, it presents the physical design, including table structures with field names, data types, and primary/foreign key designations. The report covers the scenario of tracking student grades and the need for an efficient system to manage student information, teacher details, and subject scores within the university. The design includes considerations for data validation and normalization to ensure data integrity and efficiency. The report is a comprehensive design for a fully functional system.

1
Assignment 1
Subject: Database Design and Development
Student Name: Nguyen Hoai Bao
Class: GCS0903B
Student ID: GCS200283
Lecturer: Nguyen Van Son

2
ASSIGNMENT 1 FRONT SHEET
Grading Grid
P1 M1 D1
x
Qualification TEC Level 5 HND Diploma in Computing
Unit number and
title Unit 04: Database Design & Development
Submission date Date Received 1st submission
Re-submission Date Date Received 2nd
submission
Student Name NGUYEN HOAI BAO Student ID GCS200283
Class GES0903B Assessor name NGUYEN VAN SON
Student declaration
I certify that the assignment submission is entirely my own work and I fully understand the consequences of
plagiarism. I understand that making a false declaration is a form of malpractice.
Student’s signature BAO

3
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:

4
ASSIGNMENT 1 BRIEF
Qualification BTEC Level 5 HND Diploma in Computing
Unit number Unit 04: Database Design & Development
Assignment title
Academic Year
Unit Tutor
Issue date Submission date
IV name and date

5
Submission Format:
Format: This assignment is an Individual assignment and specifically including 1 document:
You must use font Calibri size 12, set number of the pages and use multiple line spacing at
1.3. Margins must be: left: 1.25 cm; right: 1 cm; top: 1 cm and bottom: 1 cm. The reference
follows Harvard referencing system. The recommended word limit is 2.000-2.500 words. You
will not be penalized for exceeding the total word limit. The cover page of the report has to be
the Assignment front sheet 1.
Submission Students are compulsory to submit the assignment in due date and in a way requested by the
Tutors. The form of submission will be a soft copy posted on http://cms.greenwich.edu.vn/
Note: The Assignment must be your own work, and not copied by or from another student or from
books etc. If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you
must reference your sources, using the Harvard style. Make sure that you know how to reference properly,
and that understand the guidelines on plagiarism. If you do not, you definitely get fail
Unit Learning Outcomes:
LO1 Use an appropriate design tool to design a relational database system for a substantial problem.
LO2 Develop a fully functional relational database system, based on an existing system design.
LO3 Test the system against user and system requirements.
LO4 Produce technical and user documentation
Assignment Brief and Guidance:
You are employed as a Database Developer for a large IT consultancy company. The company has been
approached by FPT university which is expanding due to the growth of the number of students. FPT is
currently facing difficulties in dealing with managing the university. It decided to develop several academic
systems to manage the university easier including: Online Library system, Student Grading System,
Attendance System, CMS System, Scheduling System, Enrolment Systems, and so on.
You are tasked to select one of those systems to develop database for FPT university. Your tasks are to:
Work with FPT to find out about current requirements for each system
Analyze the requirements and produce clear statements of user and system requirements.
Design a relational database system using appropriate design tools and techniques
Develop a fully functional relational database system, based on an existing system design.
Test the system against user and system requirements.
Produce technical and user documentation
Part 1 (Assignment 1)
Before you start the development process, your manager has asked you to produce a report for the CEO of
FPT, containing:

6
1. Clear statements of user and system requirements. The system must have at least 2 user roles, including
business processes and statistical reports for FPT university managers.
2. The design of the relational database system using appropriate design tools and techniques. It should
contain at least four interrelated tables.
You would prefer to produce a more detailed document, so you will produce a comprehensive design for a
fully functional system which will include interface and output designs, data validations and cover data
normalization.
Your manager would like on the report your assessment of the effectiveness of the design in relation to user
and system requirements.
Part 2 (Assignment 2)
Once the designs have been accepted by your manager you have been asked to:
1. Develop the database system using evidence of user interface, output and data validations and querying
across multiple tables.
You want to include more than just the basics so you will implement a fully functional database system which
will include system security and database maintenance features.
2. You have decided to implement a query language into the relational database system. The developed
system will be demonstrated to your manager in the report including:
• Assessing whether meaningful data has been extracted through the use of query tools to produce
appropriate management information.
• Evaluating the effectiveness of the database solution in relation to user and system requirements,
and suggest improvements.
3. Once the system has been developed, you will test the system and your manager will complete a witness
statement indicating how your tests are performing against user and system requirements.
Besides, you will produce a brief report assessing the effectiveness of the testing, including an explanation
of the choice of test data used.
4. Lastly you will produce technical and user documentation which will be given to the company.
You want to provide some graphical representations for ease of reference in the technical guide, so you have
decided to produce a technical and user documentation for a fully functional system, including diagrams
showing movement of data through the system, and flowcharts describing how the system works.

7
TABLE OF CONTENT
1. SCENARIO ....................................................................................................................... 8
2. SYSTEM REQUIREMENT SPECIFICATION (SRS) ............................................................... 8
3. LOGICAL DESIGN (ERD) ................................................................................................... 9
4. EXPLANATION ................................................................................................................. 9
5. PHYSICAL DESIGN ......................................................................................................... 10
1. Student Table ........................................................................................................... 10
2. Lecturer Table ........................................................................................................... 10
3. Class Table ................................................................................................................ 10
4. Grade Table .............................................................................................................. 10
5. Subject Table ............................................................................................................ 11

8
1. SCENARIO
The use and application of databases to solve a problem is now very widespread
and widely used all over the world. I'm going to create a database today, and the
scenario I've chosen is to track student grades. Notebook for students. Because students
are overburdened and lack the required resources to manage their grades. As a result,
there was a need to maintain student lists, which necessitated the creation of a database
to store student information, including student IDs and names. Transcripts that include
information about the student, the teacher, the subject, and the grade. Information about
teachers and students is combined into a single database for storage.In addition, a
transcript of information detailing any subject's score is required. In short, the
professional university's database system for managing students' grades is an amazing
management tool.
2. SYSTEM REQUIREMENT SPECIFICATION (SRS)
The school's preparatory office will oversee the student score administration
structure. The preparation division will keep track of student grades by topic, educator,
and other factors. The code for each student. The school is the most personally
accountable for the grades.
The student management system will include 5 tables: Student, Lecturer,
Subject Class, Grade and Subject. Each table will have different functions, components,
and relationships.
➢ Table Student will be knowing the code number, name and everybody is, out
but for now the information method as address, number of phone or an email of
each student.
➢ Table Lecturer will provide the instructor's code number, name, phone number,
and email address to help students connect with each instructor, know which
subject has an instructor for easy management. clutch system management.
➢ Table Class will have subject name, coding subject and link with coding teacher
and coding subject. Can be distinguished through the subject of code, the
teacher of the code.
➢ Table Grade will be a place to store student scores, including grades. Only code
students, coding subjects, and code instructors can access to see that student's
grades.
➢ Finally, the subject table includes sub code, sub name, signal number, course
start date and end date to help students check the course time, signal number
and subject name that they will be studying. learn.

9
3. LOGICAL DESIGN
➢ Within the student administration information table that we are doing, there will
be tables: Student table, Lecturer table, Grade table, Subject table, and Class
table.
➢ Student relation: with student id as primary key, gender, phone number, email,
and student name.
➢ Lecturer relation: with lecturer id as primary key, lecturer name, lecturer email,
and gender.
➢ Class relation: with class id as primary key, class name. Lecturer id and student
id are foreign key.
➢ Grade relation: with student id and subject id are primary key. Student id and
subject id are foreign key.
➢ Subject relation: with subject id as primary key. Subject name and class id are
foreign key.
Figure 1: Entity Relationship Diagram
4. EXPLANATION
Starting with the students table and moving on to the classes table, there is a
many-to-many association because a student can have many classes and a class can
have many students. So, create an intermediate table to link the two tables by a one-to-
many relationship
Next is the teacher table and the class table, here we use a one-to-many
relationship because a teacher can have many classes, but a class cannot have many
teachers.

10
The third relationship is a one-to-many relationship between the class table and
the subject table. We can see that a subject can have several classes, but a class cannot
have many subjects.
The fourth relationship is between the topics table and the transcript; because a
subject will have multiple transcripts from many students, and each student transcript
will represent a subject separately, we will utilize a one-to-many relationship.
Finally, because one student has numerous transcripts but only one transcript,
the student table and student transcript will employ a one-to-many relationship.
5. PHYSICAL DESIGN
1. Student
Field Name Data Type Allow Nulls Description
StuId Int Not null PK
StuName Nvarchar(50) Not null
Gender Bit Not null
Phone Nvarchar(50) Not null
Email Nvarchar(100) Not null
2. Lecturer
Field Name Data Type Allow Nulls Description
LecId Int Not null PK
LecName Nvarchar(50) Not null
Gender Bit Not null
Email Nvarchar(100) Not null
3. Class
Field Name Data Type Allow Nulls Description
ClassId Int Not null PK
ClassName Nvarchar(50) Not null
LecId Int Not null FK
StuId Int Not null FK
4. Grade
Field Name Data Type Allow Nulls Description
StuId Int Not null PK, FK
SubId Int Not null PK, FK
Score Int Not null

11
5. Subject
Field Name Data Type Allow Nulls Description
SubId Int Not null PK
SubName Nvarchar(50) Not null FK
ClassId Int Not null FK
REFERENCES
[1] From Wikipedia, the free encyclopedia – 2021.
https://en.wikipedia.org/wiki/Database