Unit 04: Database Design & Development

1622 database assignment
Database Programming
FPT University
16 pag.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

ASSIGNMENT 1 BRIEF
Qualification BTEC Level 5 HND Diploma in Computing
Unit number Unit 04: Database Design & Development
Assignment title
Academic Year 2021
Unit Tutor Thai Thi Thanh Thao
Issue date 13th January 2021 Submission date 4th February 2021
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
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

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:
Page 1
You are employed as a Database Developer for a large IT consultancy company namely CST. Your company
has many projects in many fields. And now, with your new position you need to develop several systems
for CST such as: Online Library system, Student Grading System, Online shopping system, HealthCare
system, ...And CST wants to develop some new systems to start up from any staff’s idea in CST.
You are tasked to select one of those systems to develop database for CST or choose a new one. Your
tasks are to:
Work with CST 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 CST, 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 CST managers.
2. The design of the relational database system using appropriate design tools and techniques. It should
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

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:
Page 2
• 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.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

Page 3
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
database system using
appropriate design tools and
techniques, containing at
least four interrelated
tables, with clear
statements of user and
system requirements.
M1 Produce a comprehensive
design for a fully functional
system which includes interface
and output designs, data
validations and data
normalisation.
D1 Assess the effectiveness of the
design in relation to user and system
requirements.
Page 4
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

Table of Contents
P1 Design a relational database using appropriate tools and techniques, containing at least four interrelated
tables, with clear statements of user and system requirement. ................................................................................ 6
1. Data and database definition ........................................................................................................................ 6
2. Database management system .................................................................................................................... 9
3. Design a relational database system .......................................................................................................... 12
References ............................................................................................................................................................ 16
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

P1 Design a relational database using appropriate tools and techniques, containing at least four interrelated
tables, with clear statements of user and system requirement.
1. Data and database definition
a) Data
Data is any sort of information which is stored in computer memory. This information can later be used
for a website, an application or any other client to store for future purpose. The most common
information is User information in the form of user personal, address and banking information. Let’s
consider Facebook, it stores our personal data, images, posts, comments and many more things.
Banking application also stores user data, their transactions details, funds summary etc. All this
information is data, but when it put together and store in a structural way, it becomes informational
data.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

Data, even in a database, is rarely useful in its raw form. For example, in a banking application, data is
the whole collection of bank account numbers; bank customers’ names, addresses, and ages; bank
transactions and so on. Being presented with this mass of numbers will simply overwhelm the average
human -- an individual simply cannot process it all. However, when data is arranged relationally, it then
becomes information, which is much more useful to users. For example, if the mass of numbers stored
in the banking database above is used to extract the names and addresses of the top 100 clients by
size of deposit, then the data has been used to provide useful information.
b) Database
A database is a collection of information that is organized so that it can be easily accessed, managed
and updated. Computer databases typically contain aggregations of data records or files, containing
information about sales transactions or interactions with specific customers.
Database (DB) are organized, they have a structure, and all the data they store it fits into that structure.
More specifically, a database is an electronic system that allows data to be stored, easily accessed,
manipulated and updated.
Databases are quite similar to spreadsheets as they are mostly made up of tables which contain rows
and columns like a spreadsheet. A database needs to be hosted or created on some special database
platform, some of the famous Database platforms are:
o PostgreSQL
o MySQL
o Microsoft Access
o SQLite
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

A database is stored as a file or a set of files. The information in these files may be broken down into
records, each of which consists of one or more fields. Fields are the basic units of data storage, and
each field typically contains information pertaining to one aspect or attribute of the entity described by
the database. Records are also organized into tables that include information about relationships
between its various fields. Although database is applied loosely to any collection of information in
computer files, a database in the strict sense provides cross-referencing capabilities. Using keywords
and various sorting commands, users can rapidly search, rearrange, group, and select the fields in
many records to retrieve or create reports on particular aggregates of data.
Facts about Database:
• Databases have evolved dramatically since their inception in the early 1960s.
• Some Navigational databases such as the Hierarchical database and the Network database
were the original systems used to store and manipulate data. Although these early systems
were actually inflexible
• In the early 1980s, Relational databases became very popular, which was followed by object-
oriented databases later on.
• More recently, NoSQL databases came up as a response to the growth of the internet and the
need for faster speed and processing of unstructured data.
• Today, we have cloud databases and self-driving databases that are creating a new ground
when it comes to how data is collected, stored, managed, and utilized.
SQL Database:
A database in SQL Server is made up of a collection of tables that stores a specific set of structured
data. A table contains a collection of rows, also referred to as records or tuples, and columns, also
referred to as attributes. Each column in the table is designed to store a certain type of information, for
example, dates, names, dollar amounts, and numbers.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

2. Database management system
A database management system is a software used to perform different operations, like addition,
access, updating, and deletion of the data, like adding your name in the database for an online retail
store as a customer. A database management system acts as the backbone of a database and makes
using a database a cakewalk as it makes access and management of data a lot easier.
Database Management System or DBMS in short refers to the technology of storing and retrieving
usersí data with utmost efficiency along with appropriate security measures. This tutorial explains the
basics of DBMS such as its architecture, data models, data schemas, data independence, E-R model,
relation model, relational database design, and storage and file structure and much more.
Database is a collection of related data and data is a collection of facts and figures that can be
processed to produce information.
Mostly data represents recordable facts. Data aids in producing information, which is based on facts.
For example, if we have data about marks obtained by all students, we can then conclude about
toppers and average marks.
A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information. Following are the important characteristics and applications of
DBMS.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

• ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation, and
Durability (normally shortened as ACID). These concepts are applied on transactions, which
manipulate data in a database. ACID properties help the database stay healthy in multi-
transactional environments and in case of failure.
• Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to
access and manipulate data in parallel. Though there are restrictions on transactions when
users attempt to handle the same data item, but users are always unaware of them.
• Multiple views − DBMS offers multiple views for different users. A user who is in the Sales
department will have a different view of database than a person working in the Production
department. This feature enables the users to have a concentrate view of the database
according to their requirements.
• Security − Features like multiple views offer security to some extent where users are unable to
access data of other users and departments. DBMS offers methods to impose constraints while
entering data into the database and retrieving the same at a later stage. DBMS offers many
different levels of security features, which enables multiple users to have different views with
different features. For example, a user in the Sales department cannot see the data that
belongs to the Purchase department. Additionally, it can also be managed how much data of
the Sales department should be displayed to the user. Since a DBMS is not saved on the disk
as traditional file systems, it is very hard for miscreants to break the code.
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

3. Design a relational database system
Name: Coffee shop management system
Purpose: Manage coffee shop
User: Coffee shop’s owner
This system is designed by Microsoft SQL Server 2019.
User requirements:
A coffee shop, customer basic information, staff basic information.
Hardware requirement:
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

System ERD:
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

This was designed by www.draw.io
This system should be able to handle basic requirements of a small coffeeshop since the design is relatively basic.
This is the first version of the system. Therefore, it could still be updated to meet new requirements.
This system could only be use for one coffee shop only (*constraint).
System SQL ERD:
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])

References
• Sharma, L. (2018). What is Data, Database and Database Management System? [online] TOOLSQA.
Available at: https://www.toolsqa.com/sql/data-database-and-database-management-system/ [Accessed 4
Feb. 2021].
• Tutorialspoint.com. (2019). Database Management System Tutorial - Tutorialspoint. [online] Available at:
https://www.tutorialspoint.com/dbms/index.htm [Accessed 4 Feb. 2021].
• computerhindinotes.com. (n.d.). Introduction to DBMS 2013 | Computer Hindi Notes. [online] Available at:
https://computerhindinotes.com/introduction-to-dbms-2013/ [Accessed 4 Feb. 2021].
• MikeRayMSFT (n.d.). SQL Server 2019: Hardware & software requirements - SQL Server. [online]
docs.microsoft.com. Available at: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-
software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15 [Accessed 4 Feb. 2021].
• SearchSQLServer. (2019). What is database (DB)? - Definition from WhatIs.com. [online] Available at:
https://searchsqlserver.techtarget.com/definition/database [Accessed 4 Feb. 2021].
• Sugar, S. (2015). Brewing Up The Best Coffee Shop Location. [online] ShopKeep. Available at:
https://www.shopkeep.com/blog/what-to-consider-when-choosing-a-coffee-shop-location [Accessed 4
Feb. 2021].
• The Editors of Encyclopedia Britannica (2018). database | Definition, Types, & Facts. In: Encyclopædia
Britannica. [online] Available at: https://www.britannica.com/technology/database [Accessed 4 Feb. 2021].
• Techopedia.com. (n.d.). What is Data in the Context of Databases? - Definition from Techopedia. [online]
Available at: https://www.techopedia.com/definition/15/data-databases [Accessed 4 Feb. 2021].
• stevestein (n.d.). Databases - SQL Server. [online] docs.microsoft.com. Available at:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/databases?view=sql-server-
ver15#:~:text=A%20database%20in%20SQL%20Server%20is%20made%20up [Accessed 4 Feb. 2021].
• Freebiesupply.com. (2021). [online] Available at: https://cdn.freebiesupply.com/logos/large/2x/microsoft-
sql-server-logo-png-transparent.png [Accessed 4 Feb. 2021].
Document shared on www.docsity.com
Downloaded by: quang-huy-5 ([e-mail])