lOMoARcPSD|52310946

DDD unit 4 - Data Base Assigment

HND in Computing (ESOFT Metro Campus)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)
 lOMoARcPSD|52310946

Higher Nationals
Internal verification of assessment decisions – BTEC (RQF)

INTERNAL VERIFICATION – ASSESSMENT DECISIONS

Programme title BTEC HND in Computing

Assessor Internal
Verifier
Unit 04: Database Design & Development
Unit(s)
Database Solution for Quiet Attic Films
Assignment title
Mohamed Nizam Abdul Aneem
Student’s name
List which assessment Pass Merit Distinction
criteria the Assessor
has awarded.
INTERNAL VERIFIER CHECKLIST
Do the assessment criteria
awarded match those shown in the Y/N
assignment brief?
Is the Pass/Merit/Distinction grade
awarded justified by the assessor’s Y/N
comments on the student work?
Has the work been assessed
Y/N
accurately?
Is the feedback to the student:
Give details:
• Constructive? Y/N
• Linked to relevant assessment Y/N
criteria? Y/N
• Identifying opportunities for
improved performance? Y/N
• Agreeing actions?
Does the assessment decision
Y/N
need amending?

Assessor signature Dat

e

Internal Verifier signature Dat

ANEEM | COL00158885 DATABASE DESIGN AND DEVELOPMENT –

1
ASSIGMENT 01

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

e
Programme Leader signature (if
required) Dat
e
Confirm action completed
Remedial action
taken
Give details:

Assessor signature Dat

e
Internal
Verifier Dat
signature e
Programme
Leader signature Dat
(if required) e

ANEEM | COL00158885 DATABASE DESIGN AND DEVELOPMENT –

2
ASSIGMENT 01

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Higher Nationals - Summative Assignment Feedback Form

Student Name/ID M.N.M. Aneem

Unit Title Unit 04: Database Design & Development

Assignment 1 Assessor
Number
Date
Submission Date Received
1st
submission
Date Received
Re-submission 2nd submission
Date
Assessor Feedback:
LO1 Use an appropriate design tool to design a relational database system for a
substantial problem
Pass, Merit & P1 M1 D1
Distinction Descripts

LO2 Develop a fully functional relational database system, based on an existing system
design
Pass, Merit & P2 P3 M2 M3 D2
Distinction Descripts

LO3 Test the system against user and system requirements.

Pass, Merit & P4 M4 D2
Distinction Descripts

LO4 Produce technical and user documentation.

Pass, Merit & P5 M5 D3
Distinction Descripts

Grade: Assessor Signature: Date:

Resubmission Feedback:

Grade: Assessor Signature: Date:

Internal Verifier’s Comments:

Signature & Date:

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

* Please note that grade decisions are provisional. They are only confirmed once internal
and external moderation has taken place and grades decisions have been agreed at the
assessment board.

Assignment Feedback
Formative Feedback: Assessor to Student

Action Plan

Summative feedback

Feedback: Student to Assessor

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Assessor Date
signature

Student Date
signature

Pearson Higher Nationals in

Computing

Unit 04: Database Design & Development

Assignment 01

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

General Guidelines

1. A Cover page or title page – You should always attach a title page to your assignment.
Use previous page as your cover sheet and make sure all the details are accurately
filled.

2. Attach this brief as the first section of your assignment.

3. All the assignments should be prepared using a word processing software.

4. All the assignments should be printed on A4 sized papers. Use single side printing.

5. Allow 1” for top, bottom , right margins and 1.25” for the left margin of each page.

Word Processing Rules

1. The font size should be 12 point, and should be in the style of Time New Roman.

2. Use 1.5 line spacing. Left justify all paragraphs.

3. Ensure that all the headings are consistent in terms of the font size and font style.

4. Use footer function in the word processor to insert Your Name, Subject,
Assignment No, and Page Number on each page. This is useful if individual sheets
become detached for any reason.

5. Use word processing application spell check and grammar check function to help
editing your assignment.

Important Points:

1. It is strictly prohibited to use textboxes to add texts in the assignments, except for
the compulsory information. eg: Figures, tables of comparison etc. Adding text boxes
in the body except for the before mentioned compulsory information will result in
rejection of your work.

2. Carefully check the hand in date and the instructions given in the assignment. Late
submissions will not be accepted.

3. Ensure that you give yourself enough time to complete the assignment by the due
date.

4. Excuses of any nature will not be accepted for failure to hand in the work on time.

5. You must take responsibility for managing your own time effectively.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

6. If you are unable to hand in your assignment on time and have valid reasons such as
illness, you may apply (in writing) for an extension.

7. Failure to achieve at least PASS criteria will result in a REFERRAL grade .

8. Non-submission of work without valid reasons will lead to an automatic RE FERRAL.

You will then be asked to complete an alternative assignment.

9. If you use other people’s work or ideas in your assignment, reference them properly
using HARVARD referencing system to avoid plagiarism. You have to provide both in-
text citation and a reference list.

10. If you are proven to be guilty of plagiarism or any academic misconduct, your grade
could be reduced to A REFERRAL or at worst you could be expelled from the course

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Student Declaration

I hereby, declare that I know what plagiarism entails, namely to use another’s work and to
present it as my own without attributing the sources in the correct form. I further
understand what it means to copy another’s work.

1. I know that plagiarism is a punishable offence because it constitutes theft.

2. I understand the plagiarism and copying policy of Edexcel UK.
3. I know what the consequences will be if I plagiarise or copy another’s work in any of
the assignments for this program.
4. I declare therefore that all work presented by me for every aspect of my program,
will be my own, and where I have made use of another’s work, I will attribute the
source in the correct way.
5. I acknowledge that the attachment of this document signed or not, constitutes a
binding agreement between myself and Pearson, UK.
6. I understand that my assignment will not be considered as submitted if this
document is not attached to the assignment.

Student’s Signature: E183892@esoft.academy Date:

(Provide E-mail ID) (Provide Submission Date)

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Higher National Diploma in Computing

Assignment Brief
Student Name /ID Number M.N.A. Aneem / COL00158885
Unit Number and Title Unit 4: Database Design & Development
Academic Year 2022/23
Unit Tutor
Assignment Title Data base system for Quiet Attic Films
Issue Date
Submission Date
IV Name & Date

Submission format

Part 1: The submission should be in the form of an individual written report written in a
concise, formal business style using single spacing and font size 12. You are required to make
use of headings, paragraphs and subsections as appropriate, and all work must be supported
with research and referenced using Harvard referencing system. Please also provide in-text
citation and bibliography using Harvard referencing system. The recommended word limit is
3,000–3,500 words, although you will not be penalised for exceeding the total word limit.
Part 2: The submission should be in the form of a fully functional relational database system
demonstrated to the Tutor; and an individual written report (please see details in Part 1
above).
Part 3: The submission should be in the form of a witness statement of the testing completed
by the Tutor; technical documentation; and a written report (please see details in Part 1
above).
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:

Assignment brief

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Quiet Attic Films is a film production company based in London, England who specialize in
making short information films and advertisements for television. They want you to design
and implement a database that meets the requirements for their data. These requirements
are specified in this scenario and the examples of paper documents kept by the company
shown below.
Quiet Attic Films organize their data around the concept of a ‘production’. A production is
specified as being for a particular client; but note that a client might have more than one
production at any time. A production will take place at one or more locations. A production
will also use a number of, what are called, properties, which might be anything from an
actual property like a building, to costumes or small items of any sort. It is important to keep
a record of which properties are required at which location.
There should also be a record kept of the staff types that are assigned to productions

Activity 1
Identify the user and system requirements to design a database for the above scenario and
design a relational database system using conceptual design (ER Model) by including
identifiers (primary Key) of entities and cardinalities, participations of relationships.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Convert the ER Model into logical database design using relational database model
including primary keys foreign keys and referential Integrities.
It should contain at least five interrelated tables. Check whether the provided logical
design is normalised. If not, normalize the database by removing the anomalies.
(Note:-It is allowed to have your own assumptions and related attributes within the
scope of the case study given)
Design set of simple interfaces to input and output for the above scenario using Wireframe
or any interface-designing tool. Evaluate the effectiveness of the given design (ERD and
Logical design) in terms of the identified user and system requirements.

Activity 2
Develop a relational database system according to the ER diagram you have created (Use
SQL DDL statements). Provide evidence of the use of a suitable IDE to create a simple
interface to insert, update and delete data in the database. Implement proper security
mechanisms in the developed database and evaluate the database solution developed in
terms of its effectiveness with relevance to the user and system requirements identified,
system security mechanisms (EX: -User groups, access permissions) and the maintenance
of the database. Suggest improvements for any identified problems.

Assess the usage of the below SQL statements with the examples from the developed
database to prove that the data extracted through them are meaningful and relevant to
the given scenario.
Select/ Where / Update / Between / In / Group by / Order by / Having

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Activity 3

3.1 Provide a suitable test plan to test the system against user and system requirements.
provide relevant test cases for the database you have implemented. Assess how the
selected test data can be used to improve the effectiveness of testing.
Note:- Learner needs to give expected results in a tabular format and screenshots of the
actual results with the conclusion

3.2 Get independent feedback on your database solution from the non-technical users and
some developers (use surveys, questioners, interviews or any other feedback collecting
method) and make a separate conclusion from the feedbacks.

Activity 4

Produce technical and user documentation for a fully functional system, including data
flow diagrams showing movement of data through the system, and flowcharts describing
how the system works. Evaluate the developed database by suggesting future
enhancements to ensure the effectiveness of the system.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Database system for Quiet

Attic Films

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

ID : COL00158885
Batch : HND-COM
( Sep /Oct Weekday 2022 )

Activity 01 (Use an appropriate design tool to design a relational database system for a
substantial problem)

What is Database?
“A database is a systematic collection of data. They support electronic storage and
manipulation of data. Databases make data management easy.

Let us discuss a database example: An online telephone directory uses a database to store
data of people, phone numbers, and other contact details. Your electricity service provider
uses a database to manage billing, client-related issues, handle fault data, etc.

Let us also consider Facebook. It needs to store, manipulate, and present data related to
members, their friends, member activities, messages, advertisements, and a lot more. We
can provide a countless number of examples for the usage of databases.” (Peterson, 2023)

Types of Databases

“There are many types of Databases

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

 Distributed databases:
A distributed database is a type of database that has contributions from the common
database and information captured by local computers. In this type of database system, the
data is not in one place and is distributed at various organizations.

 Relational databases:
This type of database defines database relationships in the form of tables. It is also called
Relational DBMS, which is the most popular DBMS type in the market. Database example
of the RDBMS system include MySQL, Oracle, and Microsoft SQL Server database.

 Object-oriented databases:
This type of computers database supports the storage of all data types. The data is stored in
the form of objects. The objects to be held in the database have attributes and methods that
define what to do with the data. PostgreSQL is an example of an object-oriented relational
DBMS.

 Centralized database:
It is a centralized location, and users from different backgrounds can access this data. This
type of computers databases store application procedures that help users access the data
even from a remote location.

 Open-source databases:
This kind of database stored information related to operations. It is mainly used in the field
of marketing, employee relations, customer service, of databases.

 Cloud databases:
A cloud database is a database which is optimized or built for such a virtualized
environment. There are so many advantages of a cloud database, some of which can pay for
storage capacity and bandwidth. It also offers scalability on-demand, along with high
availability.

 Data warehouses:
Data Warehouse is to facilitate a single version of truth for a company for decision making

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

and forecasting. A Data warehouse is an information system that contains historical and
commutative data from single or multiple sources. Data Warehouse concept simplifies the
reporting and analysis process of the organization.

 NoSQL databases:
NoSQL database is used for large sets of distributed data. There are a few big data
performance problems that are effectively handled by relational databases. This type of
computers database is very efficient in analyzing large-size unstructured data.

 Graph databases:
A graph-oriented database uses graph theory to store, map, and query relationships. These
kinds of computers databases are mostly used for analyzing interconnections. For example,
an organization can use a graph database to mine data about customers from social media.

 OLTP databases:
OLTP another database type which able to perform fast query processing and maintaining
data integrity in multi-access environments.

 Personal database:
A personal database is used to store data stored on personal computers that are smaller and
easily manageable. The data is mostly used by the same department of the company and is
accessed by a small group of people.

 Multimodal database:
The multimodal database is a type of data processing platform that supports multiple data
models that define how the certain knowledge and information in a database should be
organized and arranged.

 Document/JSON database:
In a document-oriented database, the data is kept in document collections, usually using the
XML, JSON, BSON formats. One record can store as much data as you want, in any data
type (or types) you prefer.

 Hierarchical:

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

This type of DBMS employs the “parent-child” relationship of storing data. Its structure is
like a tree with nodes representing records and branches representing fields. The windows
registry used in Windows XP is a hierarchical database example.

 Network DBMS:
This type of DBMS supports many-to-many relations. It usually results in complex database
structures. RDM Server is an example of database management system that implements the
network model.” (Peterson, 2023)

Database Components

Database Components
“There are five main components of a database:
 Hardware:
The hardware consists of physical, electronic devices like computers, I/O devices, storage
devices, etc. This offers the interface between computers and real-world systems.
 Software:
This is a set of programs used to manage and control the overall database. This includes the
database software itself, the Operating System, the network software used to share the data
among users, and the application programs for accessing data in the database.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

 Data:
Data is a raw and unorganized fact that is required to be processed to make it meaningful.
Data can be simple at the same time unorganized unless it is organized. Generally, data
comprises facts, observations, perceptions, numbers, characters, symbols, images, etc.
 Procedure:
Procedure are a set of instructions and rules that help you to use the DBMS. It is designing
and running the database using documented methods, which allows you to guide the users
who operate and manage it.
 Database Access Language:
Database Access language is used to access the data to and from the database, enter new data,
update already existing data, or retrieve required data from DBMS. The user writes some
specific commands in a database access language and submits these to the database.”
(Peterson, 2023)

“What is a Database Management System (DBMS)?

Database Management System (DBMS) is a collection of programs that enable its users to
access databases, manipulate data, report, and represent data. It also helps to control access to
the database. Database Management Systems are not a new concept and, as such, had been
first implemented in the 1960s.
Charles Bachman’s Integrated Data Store (IDS) is said to be the first DBMS in history. With
time database, technologies evolved a lot, while usage and expected functionalities of
databases increased immensely.

History of Database Management System

Here, are the important landmarks from the history:

 1960 – Charles Bachman designed first DBMS system.
 1970 – Codd introduced IBM’S Information Management System (IMS).
 1976 – Peter Chen coined and defined the Entity-relationship model also know as the
ER model.
 1980 – Relational model becomes a widely accepted database component.
 1985 – Object-oriented DBMS develops.
 1990 – Incorporation of object-orientation in relational DBMS.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

 1991 – Microsoft ships MS access, a personal DBMS and that displaces all other
personal DBMS products.
 1995 – First Internet database applications.
 1997 – XML applied to database processing. Many vendors begin to integrate XML
into DBMS products.

Advantages of DBMS

 DBMS offers a variety of techniques to store & retrieve data.

 DBMS serves as an efficient handler to balance the needs of multiple applications
using the same data.
 Uniform administration procedures for data.
 Application programmers never exposed to details of data representation and storage.
 A DBMS uses various powerful functions to store and retrieve data efficiently.
 Offers Data Integrity and Security.
 The DBMS implies integrity constraints to get a high level of protection against
prohibited access to data.
 A DBMS schedules concurrent access to the data in such a manner that only one user
can access the same data at a time.
 Reduced Application Development Time.


Disadvantage of DBMS

DBMS may offer plenty of advantages but, it has certain flaws-

 Cost of Hardware and Software of a DBMS is quite high which increases the budget
of your organization.
 Most database management systems are often complex systems, so the training for
users to use the DBMS is required.
 In some organizations, all data is integrated into a single database which can be
damaged because of electric failure or database is corrupted on the storage media.
 Use of the same program at a time by many users sometimes lead to the loss of some
data.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

 DBMS can’t perform sophisticated calculations.

Summary
 Definition of Database or Database meaning: A database is a systematic collection of
data. They support electronic storage and manipulation of data. Databases make data
management easy.
 DBMS stands for Database Management System
 We have four major types of DBMSs namely Hierarchical, Network, Relational,
Object-Oriented
 The most widely used DBMS is the relational model that saves data in table formats.
It uses SQL as the standard query language” (Peterson, 2023)

“Difference between File System and DBMS

File System :
The file system is basically a way of arranging the files in a storage medium like a
hard disk. The file system organizes the files and helps in the retrieval of files when
they are required. File systems consist of different files which are grouped into
directories. The directories further contain other folders and files. The file system
performs basic operations like management, file naming, giving access rules, etc.

Example: NTFS(New Technology File System), EXT(Extended File System).

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

DBMS(Database Management System) :

Database Management System is basically software that manages the collection of related
data. It is used for storing data and retrieving the data effectively when it is needed. It also
provides proper security measures for protecting the data from unauthorized access. In
Database Management System the data can be fetched by SQL queries and relational
algebra. It also provides mechanisms for data recovery and data backup.

Example:
Oracle, MySQL, MS SQL server.

DBMS” (GeeksforGeeks, 2023)

“Difference between File System and DBMS:

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Basis File System DBMS

The file system is software that

DBMS is software for
manages and organizes the files in a
managing the database.
Structure storage medium within a computer.

Data Redundant data can be present in a In DBMS there is no

Redundancy file system. redundant data.

It provides backup and

It doesn’t provide backup and
Backup and recovery of data even if it is
recovery of data if it is lost.
Recovery lost.

Query There is no efficient query Efficient query processing

processing processing in the file system. is there in DBMS.

There is more data

There is less data consistency in the
consistency because of the
file system.
Consistency process of normalization.

It has more complexity in

It is less complex as compared to
handling as compared to the
DBMS.
Complexity file system.

DBMS has more security

File systems provide less security in
Security mechanisms as compared to
comparison to DBMS.
Constraints file systems.

It has a comparatively
It is less expensive than DBMS. higher cost than a file
Cost system.

Data In DBMS data

There is no data independence.
Independence independence exists.

User Access Only one user can access data at a Multiple users can access

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Basis File System DBMS

time. data at a time.

The user has to write procedures for The user not required to
Meaning managing databases write procedures.

Data is distributed in many files. So, Due to centralized nature

Sharing not easy to share data sharing is easy

Data It give details of storage and It hides the internal details

Abstraction representation of data of Database

Integrity Integrity Constraints are difficult to Integrity constraints are

Constraints implement easy to implement

Example Cobol, C++ Oracle, SQL Server

(GeeksforGeeks, 2023)
“The main difference between a file system and a DBMS (Database Management
System) is the way they organize and manage data.
1. File systems are used to manage files and directories, and provide basic operations for
creating, deleting, renaming, and accessing files. They typically store data in a
hierarchical structure, where files are organized in directories and subdirectories. File
systems are simple and efficient, but they lack the ability to manage complex data
relationships and ensure data consistency.
2. On the other hand, DBMS is a software system designed to manage large amounts of
structured data, and provide advanced operations for storing, retrieving, and
manipulating data. DBMS provides a centralized and organized way of storing data,
which can be accessed and modified by multiple users or applications. DBMS offers
advanced features like data validation, indexing, transactions, concurrency control,
and backup and recovery mechanisms. DBMS ensures data consistency, accuracy, and
integrity by enforcing data constraints, such as primary keys, foreign keys, and data
types.
In summary, file systems are suitable for managing small amounts of unstructured data,
while DBMS is designed for managing large amounts of structured data, and offers more

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

advanced features for ensuring data integrity, security, and performance.” (GeeksforGeeks,
2023)
User and System requirements for designing a database

User Requirements (Functional Requirements)

 Record Productions: Users should be able to create records of productions,

including production details such as production name, client information, start and
end dates, production type, and the number of days.
 Manage Locations: Users should be able to store and manage information about
locations where productions take place, including location name, address, city, and
country.
 Track Properties: Users should be able to keep track of properties required for each
production, including property name, description, and property type.
 Associate Properties with Locations: Users should be able to link properties to
specific locations where they are required for a production.
 Manage Staff: Users should be able to define staff types and their associated fees
per production day.
 Assign Staff to Productions: Users should be able to assign staff members to
productions based on their staff types.
 Retrieve Information: Users should be able to retrieve information about
productions, clients, locations, properties, and staff for reporting and analysis
purposes

System Requirements (Non-Functional Requirements)

 Database Management System: The system should support a relational database

management system (RDBMS) to store and manage the data.
 Data Security: The system should provide appropriate measures to ensure the
security and privacy of the stored data, including user authentication and access
control.
 Data Integrity: The system should enforce data integrity constraints to maintain the
accuracy and consistency of the data.
 Data Retrieval: The system should provide efficient and flexible mechanisms to

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

retrieve information from the database, such as SQL queries or a user-friendly

interface.
 Scalability: The database design should allow for scalability to handle a growing
number of productions, clients, locations, properties, and staff members.
 Data Backup and Recovery: The system should have provisions for regular data
backups and the ability to recover data in case of system failures or data loss.

“ER (Entity Relationship) Diagram in DBMS

 ER model stands for an Entity-Relationship model. It is a high-level data model.
This model is used to define the data elements and relationship for a specified
system.
 It develops a conceptual design for the database. It also develops a very simple and
easy to design view of data.
 In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.

For example, Suppose we design a school database. In this database, the student will be an
entity with attributes like address, name, id, age, etc. The address can be another entity with
attributes like city, street name, pin code, etc and there will be a relationship between them.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Component of ER Diagram

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

” (ER (Entity Relationship) Diagram Model in DBMS Examples - Javatpoint, n.d.-b)

“1. Entity:

 An entity may be any object, class, person or place. In the ER diagram, an entity can
be represented as rectangles.
 Consider an organization as an example- manager, product, employee, department
etc. can be taken as an entity.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

a. Weak Entity
 An entity that depends on another entity called a weak entity. The weak entity
doesn't contain any key attribute of its own. The weak entity is represented by a
double rectangle.

” (ER (Entity Relationship) Diagram Model in DBMS Examples - Javatpoint, n.d.-b)

“2. Attribute

 The attribute is used to describe the property of an entity. Eclipse is used to represent
an attribute.
For example, id, age, contact number, name, etc. can be attributes of a student.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

a. Key Attribute

 The key attribute is used to represent the main characteristics of an entity. It

represents a primary key. The key attribute is represented by an ellipse with the text
underlined.

b. Composite Attribute

 An attribute that composed of many other attributes is known as a composite

attribute. The composite attribute is represented by an ellipse, and those ellipses are
connected with an ellipse.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

c. Multivalued Attribute

 An attribute can have more than one value. These attributes are known as a
multivalued attribute. The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.

d. Derived Attribute
 An attribute that can be derived from other attribute is known as a derived attribute.
It can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived from another attribute
like Date of birth.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

” (ER (Entity Relationship) Diagram Model in DBMS Examples - Javatpoint, n.d.-b)

“3. Relationship

 A relationship is used to describe the relation between entities. Diamond or rhombus

is used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship
 When only one instance of an entity is associated with the relationship, then it is
known as one to one relationship.
For example, A female can marry to one male, and a male can marry to one female.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

b. One-to-many relationship
 When only one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then this is known as a one-to-
many relationship.
For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.

c. Many-to-one relationship
 When more than one instance of the entity on the left, and only one instance of an
entity on the right associates with the relationship then it is known as a many-to-one
relationship.
For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship
 When more than one instance of the entity on the left, and more than one instance of
an entity on the right associates with the relationship then it is known as a many-to-
many relationship.
For example, Employee can assign by many projects and project can have many

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

employees.

” (ER (Entity Relationship) Diagram Model in DBMS Examples - Javatpoint, n.d.-b)

ER Diagram For Quiet Attic Flims

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Logical Schema For Quiet Attic Flims

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

“Data Normalization

 Data normalization is the process of structuring information in a database to cut

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

down on redundancy and make that database more efficient. Think of normalization
as a way to make sure that every field and table in your database is organized
logically, so that you can avoid data anomalies when inserting, updating, or deleting
records. This process is carried out according to specific rules that dictate how tables
should be organized.

 Normalization is one part of the larger data cleaning and standardization process,
which also involves confirming that your data is accurate, complete, and doesn’t
contain duplicate records, as well as ensuring that you’ve selected the appropriate
data types for your fields. If you’re starting with denormalized tables, the
normalization process will involve creating additional, smaller tables that can be
joined to one another by a foreign key. Maybe you’ve become frustrated with having
to update the same information in multiple places across your database after a single
value changes, or are finding that you’re losing valuable data when a record gets
deleted. Normalizing your tables will help in both of these cases.

 The principles we’ll cover in this lesson apply to relational database management
systems (RDBMS). If you’re using a NoSQL or document-based database like
MongoDB, the information below won’t apply. (Data Normalization, n.d.)”

“Simplification and reducing storage: benefits of normalized data

Normalization is all about making your data more efficient, so that your team can find and
use the information they need. These benefits and rules may seem like common sense once
you have some familiarity with how databases work, but it pays to know the explicit
purpose of each table and field within your database. Benefits of normalized data include:

 Simplifying transactional queries. With normalized data, a query for customer

addresses only needs to look in the single field that stores those addresses. If you
store customer addresses multiple times in different locations across your database
or even keep multiple addresses within the same field, that query will take longer to
execute.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

 Decreasing the size of your database. If you repeat customer data in several
locations across your database, that means you’ve made space to store that
information several times. This may not be a major concern if your database only
contains a few tables, but if you’re working on a larger scale, disk space can be at a
premium. Reducing duplicate information means cutting storage costs, whether
you’re running a local server or relying on a cloud-hosted database.

 Make database maintenance easier. Think of that same customer data stored
several times in your database. Each time a customer changes their address, it will
need updated in every instance of a Customer Address field, which leaves a lot of
room for error. If your data is normalized, you’ll only have one Customer Address
field, which joins to other relevant tables like Orders. (Data Normalization, n.d.)”

“Data anomalies

Data anomalies are inconsistencies in how information is stored within a database. These
flaws with how a database is structured become apparent whenever something goes wrong
when a record is updated, added, or deleted. Fortunately, adhering to the rules of
normalization can prevent these anomalies from happening in the first place.

Update anomaly

Update anomalies stem from data redundancy. For example, let’s say your database stores
customer address information in fields across several tables. A customer changing their
address may result in only one of those fields updating to include the new information,
leaving you with inconsistent data.

Insertion anomaly

An insertion anomaly occurs when a record can’t be created without certain fields
containing data — data that may not exist yet. For example, a denormalized database may
be structured so that a customer account can’t be created unless that customer has place an

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

order. Normalizing that database would solve this problem, through the creation of separate
Orders and Customers tables, with no rule prohibiting null values.

Deletion anomaly

Unintentional information loss is the result of a deletion anomaly. Let’s say a table in your
database includes information about university courses and the students that take those
courses. If one course was cancelled due to low enrollment, you may inadvertently lose
valuable student information by removing that course record. Like with insertion anomalies,
breaking your data out into multiple, specific tables would prevent this issue. (Data
Normalization, n.d.)”

“Rules of normalization

The rules for normalizing data were first introduced in the early 1970s. These rules are
grouped in tiers called normal forms. Each tier builds upon the last — you can only apply
the second tier of rules if your data already meets the first tier of rules, and so on. While
there are several more normal forms beyond the three listed below, these first three are
sufficient for most use cases.

As we covered in our introduction to databases, tables within a database should contain a

entity key, also known as a primary key. This field distinguishes each row within a table
according to a unique ID, and is helpful when joining tables. Before we can even get into
first normal form, your table needs to have an entity key field.

1. First normal form (1NF)

The first normal form (1NF) dictates that each field within a table should only store one
value, and that your table shouldn’t contain multiple fields that store similar information,
like columns titled Address1 and Address2.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Here’s an example of a table that we’ll normalize according to first normal form. This table
includes information about college courses and who teaches them.
Professor table

Professor Professor
Course name
ID name

P001 Gene Intro to

Watson Philosop
hy;
Ethics

P002 Melissa Quantum

King Mechanics

P003 Errol Macroeconomics

Tyson

P004 Mary Graphic Novels

Jacobson

We notice that while our fields are distinct, one professor (Gene Watson, in the first row) is
teaching two courses, and that information is currently stored within a single cell. If we
normalize this table according to 1NF, we’ll need to break our data out into multiple tables:

Normalized professor table

Professor ID Professor name

P001 Gene Watson

P002 Melissa King

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Professor ID Professor name

P003 Errol Tyson

P004 Mary Jacobson

Normalized course table

Course Professor
Course name
ID ID

C001 Intro to P001

Philosophy

C002 Ethics P001

C003 Quantum P002

Mechanics

C004 Macroeconomics P003

C005 Graphic Novels P004

Since one professor can teach more than one course, we’ve broken this data out into two
tables. Now, our Professor table has a one-to-many relationship with our Course table. This
new table structure meets first normal form, and joins the two tables via a foreign key,
the Professor ID field. (Data Normalization, n.d.)”

2. Second normal form (2NF)

Second normal form is about reducing redundancy and making sure that every field
describes something about what the entity key identifies. To meet 2NF, all fields in a table
that aren’t the entity key must be fully dependent on the table’s entity key (which may be a
composite key made up of two fields). Let’s look at a new example — a table that includes
information about your employees’ birthdays.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Employee birthday table

Employee Departmen
Birthday
ID t

E001 November Accounting

18

E002 March 29 Sales

E003 June 1 Marketing

E004 February 7 Accounting

This table meets 1NF, because each column is distinct and only holds one value within each
cell. However, this table has a composite key: Employee ID + Birthday combined make up
the table’s entity key. This table does not meet 2NF in its current state, because
the Department field only partially depends on the composite key, since an employee’s
department doesn’t depend on their birthday, only on their employee ID. To fix this, we’ll
break this information out into two tables:

Normalized employee birthday table

Employee ID Birthday

E001 November 18

E002 March 29

E003 June 1

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Employee ID Birthday

E004 February 7

Normalized employee department table

Employee ID Department

E001 Accounting

E002 Sales

E003 Marketing

E004 Accounting

3. Third normal form (3NF)

A table meets third normal form if (in addition to meeting 2NF) it doesn’t contain any
transitive dependency. Transitive dependency happens when Column A depends on Column
B, and Column B depends on the entity key. If you want to normalize according to 3NF,
you’ll need to remove Column A from the table, since it does not depend on the entity key
directly, and place it in a different table with its own entity key.
Orders table

Ord Custome
Order Custome
er r zip
date r ID
ID code

R00 01/17/202 C032 99702

1 1

R00 03/01/202 C004 39204

2 1

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Ord Custome
Order Custome
er r zip
date r ID
ID code

R00 06/30/202 C054 06505

3 1

R00 08/22/202 C010 84098

4 1

R00 09/27/202 C004 39204

5 1

This table isn’t in third normal form because the Customer zip code field is dependent
on Customer ID, which is not this table’s entity key (the entity key here is Order ID). Our
current structure could lead to unwanted information loss; if customer C032 returned their
order and we needed to delete this record, we’d unintentionally lose their zip code
information. If customer C004 ever moved and their zip code changed, we’d also have to
update it in two places, since they’ve placed multiple orders. To bring this table into 3NF —
you guessed it — we’re going to break it out into two tables.

Normalized orders table

Order ID Order date Customer ID

R001 01/17/2021 C032

R002 03/01/2021 C004

R003 06/30/2021 C054

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Order ID Order date Customer ID

R004 08/22/2021 C010

R005 09/27/2021 C004

Normalized customers table

Customer ID Customer zip code

C032 99702

C004 39204

C054 06505

C010 84098

Drawbacks to normalization: when to denormalize

Once you reach higher levels of normalization, your database may perform certain
analytical queries at a slower rate — particularly those that need to grab a lot of data. Since
normalized data demands that a database tap into several tables to perform a query, this can
take longer, especially as your database grows in complexity. The tradeoff is that your
normalized data takes up less space.(Data Normalization, n.d.)”

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Reference

 Peterson, R. (2023). What is a Database? Definition, Meaning, Types with Example.

Guru99. [online] Available at : URL(https://www.guru99.com/introduction-to-
database-sql.html )[Accessed on 07.05.2023].
 GeeksforGeeks. (2023). Difference between File System and DBMS. GeeksforGeeks.
[online] Available at:( https://www.geeksforgeeks.org/difference-between-file-system-
and-dbms/ )[Accessed on 07.05.2023]
 ER (Entity Relationship) Diagram Model in DBMS Examples - javatpoint. (n.d.-b).
www.javatpoint.com.[online] Available at: URL( https://www.javatpoint.com/dbms-
er-model-concept )[Accessed on 08.05.2023]
 Data normalization. (n.d.). Metabase | Business Intelligence, Dashboards, and Data
Visualization.[online] Available at:URL
(https://www.metabase.com/learn/databases/normalization )[Accessed on 08.05.2023]


Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

Grading Criteria Achieved Feedback

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 normalization.
D1 Evaluate the effectiveness of the design in relation to
user and system requirements.

LO2 Develop a fully-functional relational database system,

based on an existing system design

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

P2 Develop the database system with evidence of user

interface, output and data validations, and querying across
multiple tables.

P3 Implement a query language into the relational database

system
.
M2 Implement a fullyfunctional database system, which
includes system security and database maintenance.

M3 Assess whether meaningful data has been extracted

through the use of query tools to produce appropriate
management information
LO3 Test the systems against user and system
requirements
P4 Test the system against user and system requirements.

M4 Assess the effectiveness of the testing, including an

explanation of the choice of test data used.
D2 Evaluate the effectiveness of the database solution in
relation to user and system requirements and suggest
improvements.
LO4 Produce technical and user documentation

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)

 lOMoARcPSD|52310946

P5 Produce technical and user documentation.

M5 Produce technical and user documentation for a fully-
functional system, including data flow diagrams and

flowcharts, describing how the system works.

D3 Evaluate the database in terms of improvements needed
to ensure the continued effectiveness of the system.

Downloaded by Abdullah Azar (abdullahazar03@gmail.com)