ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Date Received 1st
Submission date
submission
Date Received 2nd
Re-submission Date
submission
Student Name Nguyen Van Manh Student ID BHAF200048
Class PBTIT17101 Assessor name Dinh Thi Minh Nguyet
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:
Signature & Date:
Content
I. Database Design
1. Scenario of the chosen system
2. SRS (System Requirement Specification)
3. Logical design (ERD)
4. Explanation
1. Explain briefly about ERD (entities, their relationship). Give reasons why ERD is suitable for
the
2. 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:
III. Design Evaluation (D1)
I. Database Design
- A database is a collection of data stored in a computer and structured to keep the data in a form and
place that is easily accessible to users of the database who wish to exploit the uses of the database. Thus
data management/purpose of the database is easy when one understands what is database since it supports
manipulation of data and electronic storage. Data can be organized into files or into tables with columns,
rows and index to make it easy to find and define a database. (What is Database? An Easy Guide In 5
Points, 2021)
Picture of What is Database
-Database Design is a collection of processes that facilitate the designing, development, implementation and
maintenance of enterprise data management systems. Properly designed database are easy to maintain, improves
data consistency and are cost effective in terms of disk storage space. The database designer decides how the data
elements correlate and what data must be stored. (Bourgeois and Bourgeois, 2021)
-The main objectives of database design in DBMS are to produce logical and physical designs models of the
proposed database system.
1. Scenario of the chosen system
Currently in the 4.0 era, data storage is always an essential part of many fields, including
business, healthcare, education, etc. People have used the database in daily life, but most of them
don't know how it stores that data and how it is handled.
As a Database Developer for a large IT consultancy company, I want to help FPT University
solve the problems of university management. So, I decided to develop the Student CMS System
to manage the university easier.
Student CMS System created by a number of basic steps, such as the System Requirement
Specification (SRS), User Interfaces of System and Entity Relationship Map (EDR). With the
CSM System, all students can follow all school announcements and search for study materials of
all subjects.
2. 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. It includes a variety of elements (see below) that attempts to define the
intended functionality required by the customer to satisfy their different users.
-- In this assignment, I received a request from FPT about build a CMS [Link] allows for
notification management and resource access of subjects.
- - Manage announcements including: title, main content, announcement poster, posting date,
category.
- Manage notification categories: category code, category name, category description. For example
scientific research portfolio, general information catalog, etc.
- Manage subject information including: subject code, subject name, description, course material. In
which, a subject can have 1 to many documents
- Manage learning resources including information such as: resource code, resource name, genre, main
content, resource file, path (if any). In which each resource belongs to only one category such as
textbooks, reference books, lecture slides.
- Manage resource categories including information such as category name, general description.
3. Logical Design (ERD)
- ERD stands for Entity Relation Diagram.
- An entity relationship diagram (ERD), also known as an entity relationship model, is a graphical
representation that depicts relationships among people, objects, places, concepts or events within an information
technology (IT) system. (What is Entity Relationship Diagram (ERD)? - Definition from [Link], 2021)
- My ERD includes 6 entity: notification, category, subject infomation, resource subject, learning resources and
resource category
4. Explanation
Entity Relationship Diagram, also known as ERD, ER Diagram or ER model, is a type of structural diagram
for use in database design. An ERD contains different symbols and connectors that visualize two important
information: The major entities within the system scope, and the inter-relationships among these entities.
(What is Entity Relationship Diagram (ERD)?, 2021)
- My ERD includes 6 entity: notification, category, subject infomation, resource subject, learning resources and
resource category. And each entity has its own attributes.
-Category entity includes 8 attributes are categoryid, categoryname, cdesciption where StdID is primary key and
categotyid is foreign key of notification entity.
- Entity notification includes attributes are notificationid, ntitle, ncontent, nposter,npostingdate and categoryid
where notificationid is primary key.
-Subject infomation entity includes attributes are subjectid, subjectname, sdescription,sductument where
subjectid is primaty key:
-Resource category entity includes attributes are resourcename, rdescription where resourccename is primary
key.
-Learning resources entity includes 8 attributes are resourceid, resourcename, rcategory, rcontent, rfile ang rpath
where resourcename is primary key and categotyid is foreign key of recource category entity.
- Resource subject entity includes 2 attributes are subjectid and resourceid. Both of these attributes are
primary keys. subjectid foreign key of sybject infomation entity and resouceid foreign key of learning
resource entity.
5. Physical Design
Definition
- Physical database design is the process of transforming a data model into the physical data structure
of a particular database management system.
- Physical database design translates the logical data model into a set of SQL statements that define
the database. For relational database systems, it is relatively easy to translate from a logical data
model into a physical database.
The attention while doing physical design
11 important database designing rules which I follow:
Rule 1: What is the nature of the application (OLTP or OLAP)?
Rule 2: Break your data in to logical pieces, make life simpler.
Rule 3: Do not get overdosed with rule 2.
Rule 4: Treat duplicate non-uniform data as your biggest enemy.
Rule 5: Watch for data separated by separators.
Rule 6: Watch for partial dependencies.
Rule 7: Choose derived columns preciously.
Rule 8: Do not be hard on avoiding redundancy, if performance is the key.
Rule 9: Multidimensional data is a different beast altogether.
Rule 10: Centralize name value table design.
Rule 11: For unlimited hierarchical data self-reference PK and FK.
(koirala, 2021)
Create database by SQL Server
Create CMS system database
-Create table category database
And add caregory database
-Create table notification database
And add notification database
-Create table subject infomation database
And add subject infomation database
- Create table learning resource database
And add learning resource database
-Create table resource subject database
II. Data Validation and Normalization (M1)
1. User interfaces of your system
2. Data Validation
3. Normalization
III. Design Evaluation (D1)
IV. References
Jigsaw Academy. 2021. What is Database? An Easy Guide In 5 Points. [online] Available at:
<[Link] [Accessed 27 June 2021].
Bourgeois, D. and Bourgeois, D., 2021. Chapter 4: Data and Databases. [online] [Link].
Available at: <[Link] [Accessed 21 June 2021].
[Link]. 2021. What is Entity Relationship Diagram (ERD)?. [online] Available at:
<[Link] [Accessed 28
June 2021].
Koirala, S., 2021. 11 important database designing rules which I follow. [online] [Link]. Available
at: <[Link]
[Accessed 22 June 2021].
