Olympic Preparation Committee Management System: Database Report

This report analyzes a database management system designed for the Olympic preparation committee. It begins with an introduction to database concepts and then addresses several key tasks. The report includes an assumption regarding the clarity of information and explores business constraints and rules within the context of the Olympic committee. An entity-relationship (ER) diagram is created to model the system, defining entities such as venues, events, judges, athletes, and teams, along with their attributes and relationships. The report also covers relational schema, normalization, and the creation of database tables, including booking and judge tables. Furthermore, it discusses relational database management systems (RDBMS), file systems, indexes, and hardware considerations. Transaction analysis within the database tables is also examined. The report delves into SQL syntax and queries, as well as security threats and measures to protect the database. It then explores the need for database optimization and briefly touches upon alternative database solutions like NoSQL. The report concludes with a personal reflection and references.

DATABASE

Table of Contents
INTRODUCTION...........................................................................................................................4
TASKS.............................................................................................................................................4
An Assumption that make sure that the information not being clear or detailed on the basis of
case study.....................................................................................................................................4
Business constraints, business rule choose to the business case study and common knowledge
about the enterprise sectors..........................................................................................................5
Question- 2.......................................................................................................................................5
Create entity relationship diagram on the basis of given case study and also included the
different information in proper manner. .....................................................................................5
Question- 3.......................................................................................................................................8
Relational schema and database tables........................................................................................8
Question- 4.....................................................................................................................................16
Design the decision of successful implementation of database on one centralised locations. . 16
Discuss the RDBMS, file system and indexes and underlying the hardware decisions. ..........16
Transaction analysis in the database tables ...............................................................................16
Question- 5.....................................................................................................................................17
Purpose of Queries- ...................................................................................................................17
SQL Syntax and queries.............................................................................................................17
Question- 6.....................................................................................................................................20
Evaluate the different types of security threats in the database solution. .................................20
Security measure in the database that protect the database and data.........................................21
The most important threats is to be centralised in the database system. ...................................22
Question- 7.....................................................................................................................................23
Need and requirement of optimisation in the database system. ................................................23
Question- 8.....................................................................................................................................23
Motivation is needed in the alternative database solution such as NoSQL ..............................23
Question- 9 ....................................................................................................................................25
Personal reflection-....................................................................................................................25
CONCLUSION..............................................................................................................................27

REFERENCES..............................................................................................................................28

INTRODUCTION
Database management system is basically the collection of information and data that
generally store or access in the computer system. Sometimes, Database management system will
help for developing the formal design in proper manner. This system will interact with the
software that connect with the user and applications to create an effective database system. This
report will discuss about the Olympic preparation committee management system that manage
the entire task and operations effectively and efficiently. This database system should be
maintain their management process. Furthermore, it will create database system and entity
relation diagram that represented all the entities and their particular attributes in this report. It
includes the different conditions in ER diagram such as all real world entities, sub type, primary
key, foreign key and also discuss about the description of relation between the database tables. In
another ways, this assignment will cover the relation schema and appropriate attribute where
each schema shows the primary and foreign key. At last, this report will create report to explain
the database by using the case study of business.
TASKS
Question- 1
An Assumption that make sure that the information not being clear or detailed on the basis of
case study.
An Assumption is the thing that accept the truth on the certain condition and situation. In
this way, it can be said that in Olympic preparation's system has some assumption. Sometimes,
The information and detailed cannot be clear in appropriate manner because it can be lost and
cannot manage in proper manner that's why, it is important for managing all the details in the
form of database management system. According to business case study, the Olympic
preparation is managed by the committee to perform different task to manage their operations
and function in proper manner. In this system, it is useful for determine the relationship between
the different entities that's why, it will create an effective database system in proper manner.
There are different assumptions that applicable in the Olympic preparation management system.
 In this Olympic, Team members are participated in the events that has to be follows and
book in the advance manner. It is the best ways to participate in the sports on the basis of
their interest.
 Athletes also get the training session to improve their sports in proper manner.

 Participants also part of event and take part in the global tracking initiatives that can
tracking with the help of devices.
Business constraints, business rule choose to the business case study and common knowledge
about the enterprise sectors.
Business constraints are anything that interfere with profitability of the company or business. To
improve business these constraints need to be minimized or removed like time constraint,
finance, management, business rules. Business rules are the type of constraint which cannot be
removed and need to be followed (Adetunji, Lamont and Schmidt, 2018). In the above Olympic
preparation case study there are few business constraints and business rules like
 Only one team at a time can train at training venue and also they need to pre-book the
venue with deposit amount.
 Teams can only book a venue maximum of four times not more than that.
 All the committee members can only take responsibility of three to six country accounts
maximum.
Question- 2
Create entity relationship diagram on the basis of given case study and also included the different
information in proper manner.
Entity relationship diagram is basically the model of different entities that interrelated to each
other to specific knowledge of domain. This ER model is composed of various entity types and
create specific relationship between the existing entity type. In the business case study, it can be
create the entity relationship diagram of Olympic preparation management system. This system
will contain the different types of entities and contain specific attributes in appropriate manner.
Entity relationship model is commonly formed to represent the business need in order to perform
different task in the Olympic preparation management system in effective manner. There are
different types of entities and attributes represented in the diagram that also considered the
primary key and foreign key in the database tables.
Entities: Attributes:
Venue  venue_id

 venue_name
 Venue_type
 venue_location
Event  Event_id
 Event_name
 Event_time
 Event_date
Judge  Judge_id
 judge_name
 judge_gender
 judge_number
training_session  training_id
 Coach_name
 training_date
 training_time
Athlete  athlete_id
 athlete_name
 athlete_country
 athlete_number
team  team_id
 team_name
 team_rank
committee  Member_id
 member_name
manager  manager_id
 manager_name
 mange/type_services
Sponsor  Sponsor_name

According to the business model of case study, it is important for identifying the specific
role and responsibilities in the Olympic preparation committee. In ER Model, there are different
entities and their specific attributes participate in the entity relationship diagram or connect to
each other for creating an effective relationship with the help of primary key and foreign key.
Entity relationship Diagram can be included all the criteria.
Primary key- Primary is basically the key that create relational database where each and
every record contain the unique values. It is generally unique identified for example- athlete_id ,
team_id, venue_id, event_id, manager_id etc. All the entities contain the unique value that
cannot be repeat in the data base system. In the relational database system, it is needed for one
primary key in the table and it appears in the columns form of relational database tables.
Foreign key- It is a collection of field where one or more table contain the primary key
that show the same primary key in another table as foreign key ( Atzeni, Cabibbo and Torlone,
2018). According to the ER diagram, venue table contain the event id because event id is
generally primary key in the venue table but it can show the foreign key in the venue table. In
this way, we can say that it will create relationship between two tables.
Aggregation- Sometimes, Entity relationship diagram cannot be representing the
relationship between the entity that's why, it required some scenario to understand and represent
in the proper manner. The relationship with the different entities is aggregated to the higher level
of entity. For Example- the athletes are participated in the event but may require the venue
location that ' why, in this way, it can be created aggregation in between the different entities.
Composition- It is the more restrictive where it can be composed between the two
objects and cannot be composed without having the objects.
Generalization- It is a process of identifying the common attribute of different entities in
proper manner. In this way, it easily determines that where three different entities represented in
the er model which is common attribute. For Example- in this relationship diagram, there are
three different entities such as training session, athletes and booking are interconnected with IS-
A relationship because athletes can be book the training session for the purpose of increase their
capabilities and performance.
All the key constraints are participated in the ER model that help for creating a relationship
between the different entities in the business case study in proper manner.

Question- 3
Relational schema and database tables
Normalization is type of database design which organizes tables in a manner that reduces
redundancy and dependence of data (Cumin and et.al., 2017). In this large tables are divided into
smaller tables and link then using relationships. There are many types of normal forms but main
are;
 First normal form: this rule says that all the attributes in a relation must have atomic
domains
 Second normal Form: Second normal form says that each non-prime attribute should be
functionally dependent on prime attribute.
 Third Normal form: for third normal form it is important that tables should be in second
normal form. Then no prime attributes should be dependent on prime key attributes.
 Boyce-Codd Normal Form: it is an extended version of third normal form as it says that
for any non trivial dependency, X-> A, X must be a super key.
Database tables-