Pearson

Higher Nationals in
Computing
Database Design & Development
MR. Siyab Aakil
 Higher National Diploma in Computing
Course Tittle Pearson BTEC Level 5 Higher National Diploma in Computing

Unit Number and Title: Unit 4 - Database Design & Development

Assignment Title Centralized Database for MadZoo Digital

Name of the Learner MR. Siyab Aakil

Ref. No. of the Learner Pearson Regd. No.

Assignment Number Batch No & Semester

Issue Date Submission Date

Re-submission Date Date Received 1st submission

Unit Assessor: Mr. Sashi Kumar Academic Year 2023 - 2024

2|Page Database Siyab Aakil

Acknowledgment
I want to start by expressing how grateful I am that this assignment was completed successfully.
I sincerely appreciate the advice and assistance I got while working on this project. I would
like to express my gratitude to Mr. Sashi Kumar, my Database Design & Development
instructor, for his wise counsel and suggestions, which were extremely helpful in overcoming
obstacles and succeeding. I sincerely appreciate the additional information and insights he
provided me with during this process. I also want to express my gratitude to my parents for
their continuous help and support, which was essential in completing this assignment in the
allotted time.

3|Page Database Siyab Aakil

Contents
Acknowledgment .................................................................................................................................... 3
INTRODUCTION ....................................................................................................................................... 5
DATABASE MODELLING .......................................................................................................................... 5
What is a Database? ........................................................................................................................... 5
Tables of Database .............................................................................................................................. 6
Relationship of Database .................................................................................................................... 7
Normalization of database.................................................................................................................. 8
Keys of Database ................................................................................................................................. 9
ER Diagram of Database.................................................................................................................... 11
MEDZOO DIGITAL ER DIAGRAM............................................................................................................ 12
Bibliography .......................................................................................................................................... 13
List of Figures

Figure 1: Difference between Graph Database and Relational Database ............................................. 5

Figure 2: Normal form............................................................................................................................. 9
Figure 3: ER Diagram for MedZoo Digital.............................................................................................. 12
Figure 4: Schema of MedZoo Digital ..................................................................................................... 12

4|Page Database Siyab Aakil

INTRODUCTION
The MedZoo Digital School is planning football matches, requiring to the creation of a
corresponding database system for the school to transform its entire school with data processing
methods to meet its development and flexibility demands. Based registration management
system.

This report seeks to justify, explain, and clarify the specifics and semantics of the relevant
database created at their request.

DATABASE MODELLING
What is a Database?
A database is an organized collection of data created for simple administration, updating, and
access. Data records or files holding several forms of information, such as sales transactions,
client information, financial information, and product specifications, are frequently kept in
computers.

Databases are used to store, manage, and provide access to many types of data. They effectively
gather data on people, places, or things, concentrating it in one spot for observation and
analysis. In their most basic form, databases are catalogs of data. (Lutkevich, 2023)

Use of Database:

• Improve business processes.

• Keep track of customers.
• Secure personal health information.
• Store personal data

Figure 1: Difference between Graph Database and Relational Database

5|Page Database Siyab Aakil

Tables of Database
A table is a basic and crucial part of a database that contains a collection of linked data arranged
in rows and columns. Data is organized and stored in a grid-like layout. Each column in the
table represents a particular property or field that characterizes the data, while each row in the
table corresponds to a single record or entry.

Consider a straightforward database for managing the personnel of a business. Columns in the
table could include "Employee ID," "First Name," "Last Name," "Department," and "Salary."
The information in the table will then be organized so that each row represents a single
employee, with each column containing information about that person.

Tables are named to uniquely identify them inside the database and are created to contain
particular sorts of data. Databases are an effective approach to handling and dealing with huge
volumes of information because they enable efficient data organization, retrieval, and
modification. Keys may be used to define relationships between tables to build more
complicated databases that link and store data from many sources. (Indeed Editorial Team,
2022)

Based on the given scenario, I expect the table of the database to be as follows:

Teams: ID, Name, Main Stadium, city

Players: ID, Name, DOB, Start Year, Shirt number, TeamsID

Matches: ID, Date, Final_Resulst, TeamsPlaysMatchesID

Teams_Plays_Matches: ID, Host_Team, Guest_Team, Stadium

Players_Participated_Matches: ID, MatcheID, PlayersID, Goals_He_Stored, Get yellow

card, Get red card

Subsitute_Playes: ID, MatchesID, PlayerID, Subsitute_Player_ID, Time, Place

Referee: ID, Name, DOB, Years of Experience

Refrees_Matches: ID, MatchesID, Main_Refree, Assistent_Refree1, Assistent_Refree2

6|Page Database Siyab Aakil

Relationship of Database
A relationship in a database is an association or link between two or more tables that is based
on shared data items. It is a key idea in relational database management systems (RDBMS) and
establishes the relationships between the data in various tables.

There are three main types of relationships in a database:

1. One-to-One (1:1) Relationship: One entry in Table A is associated to just one

matching record in Table B in a one-to-one (1:1) connection, and vice versa. It is often
used when it is necessary to divide vast or sensitive data into distinct tables while yet
maintaining a direct link between the relevant information. It is comparatively less
popular.
Example: Think about the two tables "Employees" and "EmployeeContacts." Each
entry in the "EmployeeContacts" database can only be linked to one employee, and
each employee can only have one related record there.
2. One-to-Many (1:N) Relationship: A record in Table A may be connected to several
records in Table B, but each record in Table B is only related to one record in Table A
in a one-to-many connection (1:N). In databases, this kind of interaction is the most
prevalent.
Example: Using the preceding example as a guide, a "Employee" can have numerous
records in the "Tasks" table, each of which represents a different job that has been given
to the employee. The "Tasks" table, however, only links one unique person to each
assignment.
3. N:M (Many-to-Many) connection: When many records in Table A may be connected
to multiple records in Table B, a many-to-many connection is present. An intermediate
table, also known as a junction or connecting table, is utilized to describe this
relationship in a relational database.
Example: Consider that there are two tables: "Students" and "Courses." A third table
named "Enrollments" can be made to link the two tables as a student may enroll in
several courses and a course may have multiple students. The "Enrollments" table links
the two databases by including a mix of student and course IDs in each entry.

When working with complex and interrelated data, establishing and preserving connections in
a database protects data integrity, decreases redundancy, and enables effective information
searching and retrieval. (Indeed Editorial Team, 2022)

7|Page Database Siyab Aakil

Normalization of database
Database normalization is a technique used in database architecture to efficiently arrange data,
lessen data redundancy, and keep data integrity. The fundamental objective of normalization is
to organize a database to reduce information duplication and get rid of anomalies that might
cause data inconsistencies or mistakes.

A huge table with duplicated or related data is divided into smaller, more manageable tables as
part of the normalizing process. Then, associations based on shared data items are used to
connect these tables. Each of the usual forms, or rules, that govern the process addresses a
certain sort of data reliance and redundancy.

The most used normal forms are:

1. 1NF: A table's columns must only contain atomic values (indivisible data), and each
record must be individually identified, according to the First Normal Form (1NF). It
removes the possibility of having recurring groups of characteristics or storing multiple
values in a single cell.
Rules:
• Each table cell should contain a single value.
• Each record needs to be unique.
2. 2NF: Second Normal Form (2NF): 2NF eliminates partial dependencies in addition to
satisfying the conditions of 1NF. It implies that the full primary key, not just a portion
of it, must be a dependency for all non-key properties in a database.
Rules:
• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key that does not functionally dependant on
any subset of candidate key relation
3. 3NF: Building on the Second Normal Form, the Third Normal Form (3NF) eliminates
transitive dependencies. It makes sure non-key attributes in the same table don't rely on
other non-key attributes.
Rules:
• Rule 1- Be in 2NF
• Rule 2- Has no transitive functional dependencies

8|Page Database Siyab Aakil

Higher normal forms, such as the Boyce-Codd Normal Form (BCNF) and the 4NF, 5NF, and
5NF, cope with more intricate data dependence scenarios and further cut down on data
redundancy. The utmost level of normalization is not necessary for every database, though;
doing so might result in additional complexity and perhaps worse query speed.

Data redundancy is decreased by normalizing a database, which not only frees up storage but
also aids in maintaining data correctness and consistency. Since the data is organized among
tables, updates, insertions, and removals are less likely to cause errors. The trade-off is that
data retrieval from numerous connected tables could need more intricate queries. Based on the
unique needs of the application and the size of the database, database designers must balance
normalization and convenience of data retrieval. (Peterson, 2023)

Figure 2: Normal form

Keys of Database
In a relational database, keys are traits or set of properties that are essential for creating links
between tables and uniquely identifying records inside a table. They are utilized to promote the
development of linkages between tables, guarantee data integrity, and make data retrieval
easier. In a database, the most common key types are:

Primary Key:

• Each record in a table has a primary key that serves as a unique identifier. It guarantees
that every row in the table can be distinguished from every other row and can be
uniquely recognized.
• Duplicate or empty values are not permitted in the main key.
• Every table in a relational database has a primary key, which needs to be specified when
the table is created.
• Forging associations between other tables uses it as a reference (foreign key).\

Foreign Key:

• An attribute, or set of attributes, in one table that relates to the primary key of another
table is known as a foreign key. The two tables are connected or related as a result.

9|Page Database Siyab Aakil

 • A parent-child connection between the tables is established when the foreign key in one
table is used to refer to the main key in another table.
• It guarantees referential integrity, which means that the information in the associated
tables stays accurate and consistent.
• It's used to make sure that only legitimate values are placed in the reference column,
for example, and to apply other restrictions.

Unique Key:

• A unique key guarantees uniqueness inside a table, just like a primary key does. A table,
however, can have more than one unique key, unlike the main key.
• When you want to guarantee that a certain characteristic or group of attributes is unique
but do not want to utilize it as the primary key, you use unique keys.

Candidate Key:

• A basic collection of characteristics that may definitively identify each record in a

database is known as a candidate key.
• Each unique key in a table with numerous unique keys is a candidate key.

Composite Key:

• To uniquely identify a record in a database, a composite key is a key made up of two

or more properties.
• When a mixture of characteristics is necessary for uniqueness, it serves as an alternative
to utilizing a single attribute as a primary key.

Super Key:

• A group of one or more qualities known as a super key may be used to specifically
identify records in a database.
• It is a superset of candidate keys since it may have more qualities than the bare
minimum needed for uniqueness.

To efficiently construct links between tables and ensure data integrity, it is crucial to
comprehend and define keys in a database correctly. They make ensuring that the database
continues to manage data consistently and effectively. (JavaTPoint, 2022)

10 | P a g e Database Siyab Aakil

ER Diagram of Database
A visual depiction of the logical structure of a database used in Database Management Systems
(DBMS) is called an Entity-Relationship (ER) diagram. It displays the database's entities (also
known as objects or things) and their connections. In order to represent the database's structure
before it is actually implemented, ER diagrams are a crucial component of the database design
process.

Key components of an ER diagram:

1. Entity:
• A real-world item, idea, or thing that requires data to be recorded in the database is
represented by an entity.
• Entities are represented in the diagram as rectangles with the name of the entity
written within.
2. Attributes:
• The traits or possessions of an entity that define it and store information are called
its attributes.
• Each entity has one or more characteristics, which are shown as linked ovals or
ellipses next to the appropriate entity.
3. Relationship:
• A relationship shows how two or more entities are associated or connected.
• It displays the relationships between things in the database.
• The linked entities are connected by diamond-shaped relationships, and the type
of relationship (e.g., one-to-one, one-to-many, many-to-many) is shown next to the
diamond.
4. Cardinality:
• The number of instances of one entity that are connected to the number of instances
of another entity by a connection is referred to as their cardinality.
• Near the ends of the relationship lines, common notations for cardinality include
"1" (one), "0..1" (zero or one), "0.." (zero or many), and "1.." (one or many).

These entities, attributes, and connections will be graphically represented in the ER diagram,
along with their cardinality, to give a clear and succinct outline of the logical structure of the
database. During the implementation phase, the physical database schema is built on top of this
design. (JavaTPoint, 2022)

11 | P a g e Database Siyab Aakil

MEDZOO DIGITAL ER DIAGRAM.

Figure 3: ER Diagram for MedZoo Digital

The organizational Entity Relationship diagram (ERD) was used as a modeling technique to
model the database. During the modeling phase, 5 different Entity and their characteristics were
identified according to the visual and stated needs of the football matches. On closer inspection,
it was found that these companies have relationships between them. After a thorough study,
the relationships were determined and properly mapped.

Figure 4: Schema of MedZoo Digital

12 | P a g e Database Siyab Aakil

Bibliography
Indeed Editorial Team, 2022. What Is A Relationship In Database? (Definition And Types). [Online]
Available at: https://in.indeed.com/career-advice/career-development/what-is-relationship-in-
database#:~:text=To%20answer%20%22What%20is%20a,key%20of%20the%20other%20tables.
[Accessed 01 July 2023].

Indeed Editorial Team, 2022. What Is a Table in a Database? (Including Its Structure). [Online]
Available at: https://ca.indeed.com/career-advice/career-development/what-is-a-table-in-a-
database
[Accessed 01 July 2023].

JavaTPoint, 2022. ER (Entity Relationship) Diagram in DBMS. [Online]

Available at: https://www.javatpoint.com/dbms-er-model-concept
[Accessed 01 July 2023].

JavaTPoint, 2022. Keys. [Online]

Available at: https://www.javatpoint.com/dbms-keys
[Accessed 01 July 2023].

Lutkevich, B., 2023. database (DB). [Online]

Available at: https://www.techtarget.com/searchdatamanagement/definition/database
[Accessed 01 July 2023].

Peterson, R., 2023. What is Normalization in DBMS (SQL)? 1NF, 2NF, 3NF Example. [Online]
Available at: https://www.guru99.com/database-normalization.html
[Accessed 01 July 2023].

13 | P a g e Database Siyab Aakil