Database Design & Development Assignment 2

This assignment showcases the skills learned in Database Design & Development. It includes user interfaces, data validation, and queries for various aspects such as students, teachers, classes, subjects, courses, and marks. The ERD, test plan, and documentation are also provided.

ASSIGNMENT 2 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date DateReceived1stsubmission
Re-submissionDate DateReceived2ndsubmission
Student Name Nguyễn Việt Hà Student ID GCH20003
Class GCH0903 Assessor name Vương Thị
Student declaration
I certify that the assignment submission is entirely my own work and I fully understand the consequences of plag
making a false declaration is a form of malpractice.
Student’s signature
Grading grid
P2 P3 P4 P5 M2 M3 M4 M4

 Summative Feedback: Resubmission Feedback:
Grade: AssessorSignature: Date:
InternalVerifier’sComments:
Signature&Date:

Table of Contents
I. Introduction ......................................................................................................................... 6
II. User interfaces, data validation & query ......................................................................................... 6
III. Test plan ..................................................................................................................................... 50
IV. Documentation ....................................................................................................................... 51

Table of Figures
Figure 1: ERD ................................................................................................................................................................. 6
Figure 2: Login interface ............................................................................................................................................... 7
Figure 3: All User’s Menu interface .............................................................................................................................. 8
Figure 4: Admin Student interface ................................................................................................................................ 9
Figure 5: Add Student interface .................................................................................................................................. 10
Figure 6: Edit student interface .................................................................................................................................. 11
Figure 7: Delete student ............................................................................................................................................. 12
Figure 8:User Student interface ................................................................................................................................. 13
Figure 9: Admin Teacher interface ............................................................................................................................. 14
Figure 10: Add Teacher interface................................................................................................................................ 15
Figure 11: Edit teacher interface ................................................................................................................................ 16
Figure 12: Delete Teacher ........................................................................................................................................... 17
Figure 13: User Teacher interface............................................................................................................................... 18
Figure 14: Admin Class interface ................................................................................................................................ 19
Figure 15: Add class interface ..................................................................................................................................... 20
Figure 16: Edit class interface ..................................................................................................................................... 21
Figure 17: Delete Class ................................................................................................................................................ 22
Figure 18: User Classes interface ................................................................................................................................ 23
Figure 19: Admin Subject interface ............................................................................................................................ 24
Figure 20: Add Subject interface................................................................................................................................. 25
Figure 21: Edit Subject interface ................................................................................................................................. 26
Figure 22: Delete subject ............................................................................................................................................ 27
Figure 23: User Subject interface................................................................................................................................ 28
Figure 24: Admin Course interface ............................................................................................................................. 29
Figure 25: Add Course interface ................................................................................................................................. 30
Figure 26: Edit Course interface ................................................................................................................................. 31
Figure 27: Delete Course............................................................................................................................................. 32
Figure 28: User Course interface ................................................................................................................................ 33
Figure 29:All User’s Mark interface ........................................................................................................................... 34
Figure 30: Admin Pass interface ................................................................................................................................. 35
Figure 31: Add Pass interface ..................................................................................................................................... 36
Figure 32: Edit Pass interface ...................................................................................................................................... 37
Figure 33: Delete Pass ................................................................................................................................................. 38
Figure 34: User Pass interface .................................................................................................................................... 39
Figure 35: Admin Merit interface ............................................................................................................................... 40
Figure 36: Add Merit interface ................................................................................................................................... 41
Figure 37: Edit Merit interface .................................................................................................................................... 42
Figure 38: Delete Merit ............................................................................................................................................... 43
Figure 39: User Merit interface .................................................................................................................................. 44
Figure 40: Admin Distinction interface ....................................................................................................................... 45

Figure 41: Add Distinction interface ........................................................................................................................... 46
Figure 42: Edit Distinction interface ........................................................................................................................... 47
Figure 43: Delete Distinction ...................................................................................................................................... 48
Figure 44: User Distinction interface .......................................................................................................................... 49
Figure 45: Diagram ...................................................................................................................................................... 53
Figure 46: ERD ............................................................................................................................................................. 54

I. Introduction
In this assignment, I going to demonstrate all of the skill that I have learned through the past few weeks
and presented the code that I have been working along with the interfaces and data validation that I have
done in the first assignment down below.
II. User interfaces, data validation & query
ERD:
Figure 1: ERD

1. All user Login interface:
Figure 2: Login interface
Code:
Insert Data:
Query:

2. All user Menu interface:
Figure 3: All User’s Menu interface

3. Student interface
a. Admin interface:
Admin’s Student interfaces
Figure 4: Admin Student interface
Code:

Add student interface:
Figure 5: Add Student interface
Code:
insert into student values ('GCH1444','Ha','2002/6/24','0123555559','hanguyen');
insert into student values ('GCH2407','Nguyen','2012/7/24','0444456789','vietha');
insert into student values ('GCH1554','Viet','2022/7/24','0133356789','haviet');

Edit student interface:
Figure 6: Edit student interface
Code:
update student set Username='haviet' where student_Name='Viet';

Delete student:
Figure 7: Delete student
Code:
delete from student where Student_Name='Ha';

b. User interface
Student interface:
Figure 8:User Student interface
Code:

Insert data:
insert into student values ('GCH1444','Ha','2002/6/24','0123555559','hanguyen');
insert into student values ('GCH2407','Nguyen','2012/7/24','0444456789','vietha');
insert into student values ('GCH1554','Viet','2022/7/24','0133356789','haviet');
Query:
select * from student;
4. Teacher interface:
a. Admin interface:
Admin’s teacher interface:
Figure 9: Admin Teacher interface

Code:
create table teacher(
Teacher_no char(7) primary key not null check(Teacher_no like 'GCH[0-9][0-9][0-9][0-9]'),
Teacher_fullname nvarchar(100) not null,
Teacher_DOB date not null,
Teacher_phone char(10) not null check(Teacher_phone like '0[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-
9][0-9]'),
Username varchar(50) not null foreign key references login(Username),
);
Add teacher interface:
Figure 10: Add Teacher interface
Code:
insert into teacher values ('GCH1234','Kass','2002/7/24','0123456789','hanguyen');
insert into teacher values ('GCH2344','Jack','2002/7/29','0225476789','vietha');
insert into teacher values ('GCH3456','David FOLORUNSO
BABAJIDE','2002/7/29','0112456789','haviet');

Edit Teacher:
Figure 11: Edit teacher interface
Code:
update teacher set Username='vietha' where Teacher_fullname='Jack';

Delete Teacher:
Figure 12: Delete Teacher
Code:
delete from teacher where Teacher_Name='Kass';

b. User interface
User ‘s Teacher interface:
Figure 13: User Teacher interface
Code:
Insert data:
insert into teacher values ('GCH1234','Kass','2002/7/24','0123456789','hanguyen');
insert into teacher values ('GCH2344','Jack','2002/7/29','0225476789','vietha');
insert into teacher values ('GCH3456','David FOLORUNSO
BABAJIDE','2002/7/29','0112456789','haviet');
Query:
select * from teacher;

5. Classes interface:
a. Admin interface:
Admin class interface:
Figure 14: Admin Class interface
Code:
create table class(
Class_no char(7) primary key not null check(Class_no like 'GCH[0-9][0-9][0-9][0-9]'),
Start_date date not null,
End_date date not null,
Slots nvarchar(50) not null,
);

Add Class:
Figure 15: Add class interface
Code:
insert into class values ('GCH6969','2002/7/24','2020/7/24','Dong Quan');
insert into class values ('GCH9696','2002/7/24','2020/7/24','D-Tech');
insert into class values ('GCH2456','2002/7/24','2020/7/24','Dong Quan');

Edit Class:
Figure 16: Edit class interface
Code:
update class set Slots='D-Tech' where Class_no='GCH2456';

Delete class:
Figure 17: Delete Class
Code:
delete from class where Class_no='GCH2456';

b. User interface
Figure 18: User Classes interface
Code:
Insert data:
insert into class values ('GCH6969','2002/7/24','2020/7/24','Dong Quan');
insert into class values ('GCH9696','2002/7/24','2020/7/24','D-Tech');
insert into class values ('GCH2456','2002/7/24','2020/7/24','Dong Quan');
Query:
select * from class;

6. Subject interface
a. Admin interface:
Admin’s subject interface:
Figure 19: Admin Subject interface
Code:
create table subject(
Subject_no char(7) primary key not null check(Subject_no like 'GCH[0-9][0-9][0-9][0-9]'),
Sub_name nvarchar(100) not null,
Sub_level nvarchar(10) not null,
Slots int not null,
);

Add subject interface:
Figure 20: Add Subject interface
Code:
insert into subject values ('GCH3333','Database','Junior','50');
insert into subject values ('GCH2222','Internet','Senior','69');
insert into subject values ('GCH2000','Sex Education','Junior','96');

Edit subject interface:
Figure 21: Edit Subject interface
Code:
update subject set Sub_name='Networking' where Slots='96';

Delete Subject:
Figure 22: Delete subject
Code:
delete from subject where Slots='96';

b. User interface
Subject interface:
Figure 23: User Subject interface
Code:
Insert data:
insert into subject values ('GCH3333','Database','Junior','50');
insert into subject values ('GCH2222','Internet','Senior','69');
insert into subject values ('GCH2000','Sex Education','Junior','96');
Query:
select * from subject;

7. Course interface
a. Admin interface
Admin’s course interface:
Figure 24: Admin Course interface
Code:
create table course(
course_no char(7) primary key not null check(course_no like 'GCH[0-9][0-9][0-9][0-9]'),
Subject_no char(7) not null foreign key references subject(Subject_no),
Teacher_no char(7) not null foreign key references teacher(Teacher_no),
Class_no char(7) not null foreign key references class(Class_no),
Start_date date not null,
End_date date not null,
Semester char(15) not null,
Room int not null,
Building char(5) not null,
);

Add course interface:
Figure 25: Add Course interface
Code:
insert into course values ('GCH2002','GCH3333',
'GCH1234','GCH6969','1002/7/24','2012/7/24','Spring 2020','6699','A69');
insert into course values ('GCH2202','GCH2222',
'GCH2344','GCH9696','1802/7/24','2022/7/24','Autumm 2020','1600','B69');
insert into course values ('GCH9966','GCH2000',
'GCH3456','GCH2456','1902/7/24','2032/7/24','Winter 2020','6000','C69');

Edit course interface:
Figure 26: Edit Course interface
Code:
update course set Room='1000' where Building='A69';

Delete Course:
Figure 27: Delete Course
Code:
delete from course where Room='6699';

b. User interface
User’s Course interface:
Figure 28: User Course interface
Code:
Insert data:
Query:

8. All user’s Mark interface:
Figure 29:All User’s Mark interface
Code:
Insert data:
insert into mark values ('11112','GCH1444','GCH2002','Pass');
insert into mark values ('11122','GCH2407','GCH2202','Merit');
insert into mark values ('11222','GCH1554','GCH9966','D');
Query:
select * from mark;

9. Pass interface
a. Admin interface
Admin’s Pass interface:
Figure 30: Admin Pass interface
Code:
create table pass(
P_no char(1) not null primary key,
p_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);

Add Pass interface:
Figure 31: Add Pass interface
Code:
insert into pass values ('P','65','11112');

Edit Pass interface:
Figure 32: Edit Pass interface
Code:
Update pass set p_mark='66' where Mark_id='11112';

Delete Pass:
Figure 33: Delete Pass
Code: Delete from pass where Mark_id='11112';

b. User interface
Pass interface:
r
Figure 34: User Pass interface
Code:
drop table if exists pass;
create table pass(
P_no int not null primary key,
p_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);
Insert data:
insert into pass values ('1','65','11112');
Query:
select * from pass;

10.Merit interface:
a. Admin interface
Admin’s Merit interface:
Figure 35: Admin Merit interface
Code:
create table merit(
M_no char(1) not null primary key,
M_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);

Add Merit interface:
Figure 36: Add Merit interface
Code:
insert into merit values ('M','77','11122');

Edit Merit interface:
Figure 37: Edit Merit interface
Code:
Update merit set M_mark='76' where Mark_id='11122';

Delete Merit:
Figure 38: Delete Merit
Code:
Delete from merit where Mark_id='11122';

b. User interface
Merit interface:
Figure 39: User Merit interface
Code:
drop table if exists merit;
create table merit(
M_no int not null primary key,
M_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);
Insert data:
insert into merit values ('4','77','11122');
Query:

11.Distinction interface
a. Admin interface
Admin’s Distinction interface:
Figure 40: Admin Distinction interface
Code:
create table distinction(
D_no char(1) not null primary key,
D_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);

Add Distinction interface:
Figure 41: Add Distinction interface
Code:
insert into distinction values ('D','99','11222');

Edit Distinction interface:
Figure 42: Edit Distinction interface
Code:
Update distinction set D_mark='98' where Mark_id='11222';

Delete Distinction:
Figure 43: Delete Distinction
Code:
Delete from distinction where Mark_id='11222';

b. User interface
Distinction interface:
Figure 44: User Distinction interface
Code:
drop table if exists distinction;
create table distinction(
D_no int not null primary key,
D_mark int not null,
Mark_id int not null foreign key references mark(Mark_id),
);
Insert data:
insert into distinction values ('3','99','11222')
Query:

III. Test plan
Test Test Title Input Details Status
1 Insert into
login’s
information
insert into login values
(‘teacher',’10000’);
Passed
Delete login’s
information
delete from login where username = 'teacher' Passed
Update login’s
information
update login set Password = '77777' where username = 'teacher' Passed
2 Insert into
subject’s
information
insert into subject values
('GCH3333','Database','Junior','50');
Passed
Delete subject’s
information
delete from subject where subject_no = 'GCH2000' Passed
Update subject’s
information
update subject set Slots = '77' where subject_no = 'GCH2000' Passed
3 Insert into class’s
information
insert into class values
('GCH6969','2002/7/24','2020/7/24','Dong Quan');
Passed
Delete class’s
information
delete from class where class_no = 'GCH6969' Passed
Update class’s
information
update class set Slots = 'D-Tech' where class_no = 'GCH6969' Passed
4 Insert into
teacher’s
information
insert into teacher values ('GCH2344','Jack','2002/7/29','0225476789','vietha'); Passed
Delete teacher’s
information
delete from teacher where Teacher_fullname='Jack'; Passed
Update teacher’s
information
update teacher set Username='vietha' where Teacher_fullname='Jack'; Passed

5 Insert into
student’s
information
insert into student values ('GCH1444','Ha','2002/6/24','0123555559','hanguyen'); Passed
Delete student’s
information
delete from teacher where student_Name='Viet'; Passed
Update student’s
information
update student set Username='haviet' where student_Name='Viet'; Passed
6 Insert into
Course’s
information
insert into course values ('GCH2002','GCH3333',
'GCH1234','GCH6969','1002/7/24','2012/7/24','Spring 2020','6699','A69'); Passed
Delete Course’s
information
delete from course where course_no='GCH2002'; Passed
Update Course’s
information
update course set Semester='Summer 2020' where course_no='GCH2002'; Passed
7 Insert into mark’s
information
insert into mark values ('11112','GCH1444','GCH2002','Pass'); Passed
Delete mark’s
information
delete from mark where Mark_id= '11222'; Passed
Update mark’s
information
update mark set Status='D' where Mark_id= '11222'; Passed
8 Insert into pass’s
information
insert into pass values ('1','65','11112'); Passed
Delete pass’s
information
delete from pass where P_no= '1'; Passed
Update pass’s
information
update pass set p_mark='77' where P_no= '1'; Passed

9 Insert into merit’s
information
insert into merit values ('4','77','11122'); Passed
Delete merit’s
information
delete from merit where M_no= '4'; Passed
Update merit’s
information
update merit set p_mark='77' where M_no= '4'; Passed
10 Insert into
distinction’s
information
insert into distinction values ('3','99','11222'); Passed
Delete distinction’s
information
delete from distinction where P_no= '3'; Passed
Update distinction’s
information
update distinction set p_mark='77' where P_no= '3'; Passed
IV. Documentation
a. Scenario: 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
because of the fact that FPT university doesn’t have a Student Grading System to manage their
student’s score. Therefore, the company assigned you to developed a Student Grading System
Database in order for the FPT university to managed their student’s scores. And in order to do
so, you are tasked with FPT university to find the requirements for the Student Grading System
such as storing: student’s name, student’s grade, student’s ID, student’s class, …. After that,
you will need to analyze the requirements of the customer and produce clear statements of user
and system requirements. Next up is to design a relational database system using appropriate
design tools and techniques. The next step is to develop a fully functional relational database
system, based on an existing system design. After you have created the fully functional
program, you will need to test the system against user and system requirements. And the final
step is to produce technical and user documentation.
b. Scope of the system
- Time: 1st June, 2020 to 31th July, 2020
- Budget: 1000 US Dollars
- Users: Teachers
- Campus: FPT university, Ha Noi
- Modules: RTX 9000s

c. Objectives
- Build a new system that meets requirements of high speed, can serve at least 5000 students
and staff.
Diagram:
Figure 45: Diagram

ERD:
Figure 46: ERD
Explain ERD
- Explain attributes:
• PK: SubjectNo (PK) in Subject,
CourseNo (PK) in Course,
TeacherNo (PK) in Teacher,
ClassNo (PK) in Class,
MarkNo (PK) in Mark,
StudentNo (PK) in Student,
PNo (PK) in Pass,
MNo (PK) in Merit,
DNo (PK) in Distinction,

- Explain relationship:
• Subject, Teachers and Classes can enroll in several courses, but 1 course is
enrollment is registered by only 1 subject, 1 teacher and 1 Classes.
• Students can have several marks, but each course can give only 1 mark per student.
• Pass, Merit and Distinction can be set to many marks, but each mark can assign
only one Pass, Merit or Distinction.
Class:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
ClassNo char(10) NOT NULL Primary Key GCHxxxxx
Ex: GCH6969
Start Year int(4) NOT NULL xxxx
Ex: 2019
End Year int(4) NOT NULL Xxxx
Ex: 2021
Campus nvarchar(50) NOT NULL xxxxxx
Ex: Dong Quan
Teacher:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
TeacherNo char(10) NOT NULL Primary Key GCHxxxxx
Ex: GCH9696
tFullname nvarchar(100) NOT NULL xxxxx xxxx xx
Ex: Bui Xuan Huan
tDOB date NOT NULL yyyy/mm/dd
Ex: 2002/12/21
tPhone int(11) NOT NULL xxxxxxxxxxxx
Ex: 09123456789
Subject:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
SubjectNo char(7) NOT NULL Primary Key GCHxxxxx
Ex: GCH1620
SubName nvarchar(100) NOT NULL xxxxx xxxx
Ex Database
SubLevel nvarchar(10) NOT NULL xxxxxx
Ex: Senior
Slots int(2) NOT NULL xx
Ex: 10

Course:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
CourseNo char(7) NOT NULL Primary Key GCHxxxxx
Ex: GCH2002
SubjectNo char(7) NOT NULL Foreign Key GCHxxxxx
Ex: GCH1620
TeacherNo char(10) NOT NULL Foreign Key GCHxxxxx
Ex: GCH9696
ClassNo char(10) NOT NULL Foreign Key GCHxxxxx
Ex: GCH6969
Start Date date NOT NULL yyyy/mm/dd
Ex: 2020/12/21
End Date date NOT NULL yyyy/mm/dd
Ex: 2077/12/21
Semester Char(15) NOT NULL xxxxx xxxx
Ex: Spring 2020
Room int(4) NOT NULL xxxx
Ex: 6699
Building Char(5) NOT NULL xxxx
Ex: A09
Student:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
StudentNo char(8) NOT NULL Primary Key GCHxxxxx
Ex: GCH20035
StuDOB date NOT NULL yyyy/mm/dd
Ex: 2021/12/21
StuName nvarchar(100) NOT NULL xxxxx xxxx xx
Ex: Ngo Ba Kha
Mark:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
MarkID int(5) NOT NULL Primary Key xxxxx
Ex: 24077
StudentNo char(8) NOT NULL Foreign Key GCHxxxxx
Ex: GCH20035
CourseNo char(7) NOT NULL Foreign Key GCHxxxxx
Ex: GCH2002
Status char(6) NOT NULL xxxxxx
Ex: Marked

Pass:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
PNo char(1) NOT NULL Primary Key x
Ex: P
pMark Int(2) NOT NULL Xx
Ex: 77
MarkID int(5) NOT NULL Foreign Key Xxxxx
Ex: 24077
Merit:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
MNo char(1) NOT NULL Primary Key x
Ex: M
mMark Int(2) NOT NULL Xx
Ex: 77
MarkID int(5) NOT NULL Foreign Key Xxxxx
Ex: 24077
Distinction:
Attributes Data type Null PK/FK Format/Constraint/DomainValue
DNo char(1) NOT NULL Primary Key x
Ex: D
dMark Int(2) NOT NULL xx
Ex: 77
MarkID int(5) NOT NULL Foreign Key xxxxx
Ex: 24077

Login interface:
This interface will need users to sign in or sign up their account in order to login and user the Grading system
interface.

Menu interface:
This interface allows user to choose an option out of 6 options (student, teacher, class, course, mark).
Admin’s student interface:
This interface allows admin to add, edit or delete students.

Add student interface:
This interface allows admin to add in student information.
Edit student interface:
This interface allows admin to edit student information.

User’s Student interface:
This interface allows users to see all student information.
Admin’s teacher interface:
This interface allows admin to add, edit or delete teachers.

Add teacher interface:
This interface allows admin to add in student information.
Edit teacher interface:
This interface allows admin edit in student information.

User’s Teacher interface:
This interface allows users to see all student information.
Admin’s Classes interface:
This interface allows admin to add, edit or delete classes.

Add Classes interface:
This interface allows admin to add in Class information.
Edit Class interface:
This interface allows admin edit in Class information.

User’s Class interface:
This interface allows users to see all Classes information.
Admin’s subject interface:
This interface allows admin to add, edit or delete subjects.

Add subject interface:
This interface allows admin to add in subject information.
Edit subject interface:
This interface allows admin edit in Subject information.

Subject interface:
This Subjects interface shows the subject name, the number of slots, their level and ID.
Admin’s course interface:
This interface allows admin to add, edit or delete courses.

Add course interface:
This interface allows admin to add in course information.
Edit Course interface:
This interface allows admin edit in Course information.

Course interface:
This interface shows all courses.
All user Mark interface:
This mark interface illustrates the students mark status and have three options down below for them to choose.

Admin’s Pass interface:
This interface allows admin to add in, delete or edit the mark if their status is Pass.
Add Pass interface:
This interface allows admin to add in the mark.

Edit Pass interface:
This interface allows admin edit in Pass information.
Pass interface:
This interface allows user the see their score if their status is Pass.

Admin’s Merit interface:
This interface allows admin to add in, delete or edit the mark if their status is Merit.
Add Merit interface:
This interface allows admin to add in the Merit mark.

Edit Merit interface:
This interface allows admin to edit in Pass information.
Merit interface:
This interface allows user the see their score if their status is Merit.

Admin’s Distinction interface:
This interface allows admin to add in, delete or edit the mark if their status is Distinction.
Add Distinction interface:
This interface allows admin to add in the Distinction marks.

Edit distinction interface:
This interface allows admin to edit in Pass information.
Distinction interface:
This interface allows user the see their score if their status is Distinction.