8

Higher Nationals in Computing

DATABASE DESIGN AND

DEVELOPMENT
ASSIGNMENT
No.1

Learner’s name: VO NGUYEN DUY NAM

Assessor name: NGUYEN VAN SON
Class: GCS1001B
Learner’s ID: GCS200888
Subject’s ID: 1622
Assignment due:19/2/2022 Assignment submitted:
 ASSIGNMENT 1 FRONT SHEET

Qualification BTEC Level 5 HND Diploma in Computing

Unit number and

Unit 04: Database Design & Development
title

Date Received 1st

Submission date
submission

Date Received 2nd

Re-submission Date
submission

Student Name Student ID

Class Assessor name

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

Grading grid

P1 M1 D1

 Summative Feedback:  Resubmission Feedback:\

Grade: Assessor Signature: Date:

Lecturer Signature:

2|Page
 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

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.

3|Page
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:
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.3. Develop the database system using evidence of user interface, output and data
validations and querying across multiple tables.

4|Page
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.

Learning Outcomes and Assessment Criteria

Pass Merit Distinction

LO1 Use an appropriate design tool to design a relational database system for a substantial problem

P1 Design a relational M1 Produce a comprehensive D1 Assess the effectiveness of the

database system using design for a fully functional design in relation to user and
appropriate design tools and system which includes interface system requirements.
techniques, containing at least
and output designs, data
four interrelated tables, with
validations and data
clear statements of user and
normalisation.
system requirements.

5|Page
 ASSIGNMENT ANSWER

I. DATABASE DESIGN (P1)

1. Scenario of the chosen system
After the cooperation of FPT university and NVIDIA Inc, it is essential for me to design database for
FPT university’s education system. FPT is concerned about the growth of the number of students in a
few next semesters. As I observe opinions and complaints from most students from FPT university,
most of them mention about the traditional library. Due to the increase in students, The traditional
system can’t meet student needs . Here are some reasons about it:

+ Because of the pandemic, many students aren’t be able to borrow books or get its information on
the spot

+ With more and more students borrow books which lead to information overload to the library,
incorrect information like wrong borrow date, extraordinary expenses, …. Usually appear

+ Traditional library with outdated methods

So I decide to develop an efficient fully functional database system for FPT university which I call

Online Library system with specific IDs and many features . Online Library system will replace the
traditional library in the upcoming future

I will explain this system thoroughly in the remain pages

2. SRS (System Requirement Specification)

3. Logical design (ERD)

4. Explanation
Explain briefly about ERD (entities, their relationship). Give reasons why ERD is suitable for
the system

5. Physical design

II. DATA VALIDATION AND NORMALIZATION (M1)

1. User Interfaces of your system

2. Data Validation (included in physical design)

3. Normalization:
Explain why each of the table is 3rd Normal Form, if not, explain why.
6|Page
III. DESIGN EVALUATION (D1)
-Explain in details and with critical review, how your ERD design (entities, relationships) can
meets the user and system requirements.
Scenario
After the cooperation of FPT university and NVIDIA Inc, it is essential for me to design database for
FPT university’s education system. FPT is concerned about the growth of the number of students in a
few next semesters. As I observe opinions and complaints from most students from FPT university,
most of them mention about the traditional library. Due to the increase in students, The traditional
system can’t meet student needs . Here are some reasons about it:
+ Because of the pandemic, many students aren’t be able to borrow books or get its information on
the spot
+ With more and more students borrow books which lead to information overload to the library,
incorrect information like wrong borrow date, extraordinary expenses, …. Usually appear
+ Traditional library with outdated methods
So I decide to develop an efficient fully functional database system for FPT university which I call
Online Library system with specific IDs and many features . Online Library system will replace the
traditional library in the upcoming future
I will explain this system thoroughly in the remain pages

7|Page
 TABLE OF CONTENTS
I. DATABASE DESIGN

1. SRS (System Requirement Specification)......................................................... 9-10

2. Logical design (ERD)........................................................................................... 11-18
2.1. What is logical design (ERD)............................................................................... 11

2.2. Entities................................................................................................................. 11

2.3. Types of entities.................................................................................................. 11-13

2.4. Attribute............................................................................................................... 13

2.5. Type of attributes................................................................................................. 13

2.6. Entity relationship................................................................................................. 13

2.7. Types of relationships.......................................................................................... 13-14
2.8. Online library system logical design (ERD)......................................................... 15-16
2 Physical Design............................................................................................ 16
3.1. Physical database design..................................................................... 16

8|Page
 ASSIGNMENT 1
I. DATABASE DESIGN

1. SRS (System Requirement Specification)

- A System Requirements Specification (SRS) (also known as a Software Requirements
Specification) is a document or set of documentation that describes the features and
behavior of a system or software application.

Figure 1. Online library system (KUMAR, 2019)

- The Online Library System is intended to automate the library activities such as creating
a new borrower, giving books to the borrowers, maintaining the details of all the item that
were available in the books . This also helps the librarians by providing information such
as borrow date, book titles, ….

- System requirements:

+ Students are allowed to log in to the online library system with student ID

+ Database of student information storage systems such as student names, student phone
numbers, gmail students

+ The system helps manuals to create, update and exploit information easier. Data will be
updated regularly and will not be copied. Using databases to help store them and easily
manage them.

+ The system allows students to check the information of the book they borrowed, the loan date,
return date and buy rental books

+ The system needs to ensure that no two students have the same ID and librarian and books

+ After the student logs in and searches for boolid, baborid, libraryid. The system will be able to
display all the information at the same time

+ The system can demonstrate all tables at the same time

9|Page
+ Detecting system when students enter wrong data types

- Usecase Diagram:

Figure 2. Usecase Diagram

10 | P a g e
2.Logical design (ERD)

2.1. What is logical design (ERD)

Figure 3. Example of logical design

- Logical design is the second

stage in the database design
process. The logical design goal
is to design an enterprise-wide
database based on a specific
data model but independent of
physical-level details. Logical
design requires that all objects
in the conceptual model be
mapped to the specific
constructs used by the selected
database model

2.2. Entities

- An entity is an object that exists. It doesn’t

have to do anything; it just has to exist. In
database administration, an entity can be
a single thing, person, place, or object.
Data can be stored about such entities. A
design tool that allows database
administrators to view the relationships
between several entities is called the entity
relationship diagram (ERD)
Figure 4.Entities example
- In the Figure 3, we can see that Employee, Health Plan enrolment are entities

2.3. Types of entities

- According to expertise knowledge, There are 4 types of entities:

+ Domain entities

- Entities represent domain objects and are primarily defined by their

identity, continuity, and persistence over time, and not only by the
attributes that comprise them. An object primarily defined by its
identity is called an Entity. Entities are very important in the domain
model, since they are the base for a model

11 | P a g e
- Now look at the figure 2, There are some domain entities such as users, coach,
habits,…

+ Linking entities

Figure 5. Linking entities example

- Linking entities sometime use to solve m-n relationship in database

- As you can see from figure 4, Linking entity is Primary Key Which involve to 2
other Primary Keys : Student ID and Subject ID

+ Lookup entities

- Lookup entities are essentially utility entities

Figure 6.Lookup entity example

- In this case, CarMake and CarModel describe a single entity set each. Their
functional dependencies are CarMakeId -> CarMakeName and CarModelId ->
CarModelName

+ Weak entities

12 | P a g e
 - In a relational database, a weak entity is an entity that cannot be uniquely
identified by its attributes alone; therefore, it must use a foreign key in conjunction
with its attributes to create a primary key. It dependent on other entities for its
meaning
- Grade is a weak entity in figure 4 because Student ID and Subject ID define its
meaning.

2.4. Attribute
Figure 7.Table Habits

- In relational databases, attributes are the describing

characteristics or properties that define all items pertaining to
a certain category applied to all cells of a column. The rows,
instead, are called tuples, and represent data sets applied to
a single entity to uniquely identify each item. Attributes are,
therefore, the characteristics of every individual tuple that
help describe its unique properties.
- Attribute in table Comments (Figure 6) is HabitName.

2.5 Type of attributes

• Composite attribute: is an attribute composed of several other simple attributes. For

example, the Address attribute of an Employee entity could consist of the Street, City,
Postal code and Country attributes.

• Multivalued attribute: is an attribute where more than one description can be provided.
For example, an Employee entity may have more than one Email ID attributes in the
same cell.

• Key attribute or primary attribute: is an ID, key, letter or number that uniquely identifies
that item. For example, it can be the number of a certain invoice (e.g. the individual ID of
that invoice). A table that contains a single key attribute is considered a strong entity.
However, a table might contain more than one key attribute if it’s derived from other
tables.

• Derived attribute: as the name implies, these are derived from other attributes, either
directly or through specific formula results. For example, the Age attribute of an
Employee could be derived from the Date of Birth attribute. In other instances, a formula
might calculate the VAT of a certain payment, so that whenever the cell with the attribute
Payment is filled, the cell with the derived attribute VAT automatically calculates its value.

2.6. Entity relationship

- The process of logical design involves arranging data into a series of logical relationships
called entities and attributes. An entity represents a chunk of information. In relational
databases, an entity often maps to a table. An attribute is a component of an entity and
helps define the uniqueness of the entity.
2.7. Types of relationships
- This include 3 types of relationships

13 | P a g e
 + One to one relationship:

- Each row in the primary entity, there can be no more than 1 related row in the secondary
entity
- One to one relationship crow’s foot

Figure 8. One or none Figure 9. Only one

+ One to many relationship:

- Each record in the primary entity there can be many associated records in the secondary
entity
- One to many relationship crow’s foot

Figure 10. None or more Figure 11. One or More

+ Many to many relationship:

- Each record in a primary entity can have many related records in a secondary
entity
- Each record in the secondary entity can have many related records in the primary
entity
- Many to many relationship Crow’s foot

Figure 12. Many to many

14 | P a g e
 2.8. Online library system logical design (ERD)

Here is the logical design for the system:

- Use Draw.io to create ERD

- So I will list all tables of the system:

+ My system include 5 tables: Student, Borrow, Book, Librarian and BorrowDetail

+ Every student has an unique number Which is called Student ID so as Book, Author
and Borrow

+ Table Student display Student ID, Student’s Name, Student Gmail, Student Phone
Number

+Table Book include Book ID, Book title, BookAuthor, BookPublisher

+Table Librarian include Librarian ID, Full Name, Gmail and Phone Number

+Table Borrow Books include Borrow ID, Student ID, Librarian ID, Borrow Date , Return
Date

+Table BorrowDetail include BorrowID, BookID, quantity and Book rental cost

Figure 13. Online library system logical design (ERD)

Why ERD suitable for this system:

+ An entity-relationship diagram, or ER diagram, is essential for modeling the data stored in a

database. It is the basic design upon which a database is built. ER diagrams specify what data
we will store: the entities and their attributes. They also show how entities relate to other
entities.

+ Visual representation: it makes database easier to design and being able to identify the flow of
data and the operation of the entire system is made

15 | P a g e
+ Effective communication: Because of using given symbols to represent different types of
information. This symbol allow designer to understand database properly

+ Easy to understand: Designer can easily design entity relationship diagram so all individuals
can also understand them.

3. Physical Design
3.6. Physical database design

Table: Student
Field name  Data type  Allow Description 
nulls 
studentID   Nvarchar(10)  No    Primary Key
studentFullName Nvarchar(30)  No   Name of the student
studentGmail Nvarchar(40) Yes    Student Gmail
studentPhoneNumber Nvarchar(10)  Yes    Student Phone Number
  Table: Librarian
Field name  Data type  Allow nulls  Description 
librarianID  Nvarchar(10)  No    Primary Key
librarianName  Nvarchar(20)  No   Name of the librarian
librarianGmail Nvarchar(30) Yes    Librarian Gmail
librarianPhoneNumber Nvarchar(20)  Yes    Librarian Phone Number
Table: Borrow
Field name  Data type  Allow nulls  Description 
borrowID  Nvarchar(10)    No   Primary Key
studentID Nvarchar(10)    No   Foreign Key
librarianID Nvarchar(10)     No   Foreign Key
borrowDate Date   Yes   Book borrow date
returnDate Date Yes Book return date
  Table: Book
Field name  Data type  Allow nulls  Description 
bookID  Nvarchar(10)  No    Primary Key
bookTitle Nvarchar(30)  Yes   Bok title
bookCategory Nvarchar(20)  Yes  Book category
bookPublisher Nvarchar(20)  Yes Book Pubisher
Table: BorrowDetail
Field name  Data type  Allow nulls  Description 
bookID Nvarchar(10)  No  Primary key, Foreign key 
borrowID Nvarchar(10)  No  Primary key, Foreign key 
quantity  int  Yes   amount of borrow books
BookRentalCost Int Yes Book Rental Cost

16 | P a g e
17 | P a g e