Database Design for Olympic Preparation Committee

This assignment involves designing a database system for the Olympic preparation committee. The goal is to create a system that can record and manage various information related to Olympic preparations, including athlete details, event schedules, and results. The database design should take into account business constraints and rules, as well as security considerations to ensure the integrity and privacy of sensitive data.

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-

Entity relationship Diagram-
Entity relationship Diagram is basically graphical representation of different entities that
interrelated to each other and also interest in the specific domain of knowledge. ER Model can
be composed the different types of entities and specific relationship exist in the instances of
entity type.
Relational schema of booking table-
According to the database design, it can be considered the booking table that identify their
specific data schemas.
For Example- In this table, booking is entity that consists of different types of attributes in the
columns.
Create Booking table ( booking_id, booking_date, booking time...., Athlete-id)
Where booking = schema name
booking_id = primary key
Athlete_id = Foreign Key.

Attribute
name
Data type Data size Primary key Foreign key Domain
(Allowable
value)
Derived
data
booking_id
booking_date
booking_time
Integer
date and
time format
16 Size Yes yes null null
Let assume the Judge table-
Attributes
name
Data type Data size Primary key Foreign key Derived data
judge_id
judge_name
judge_gender
judge_phone
no
judge_address
event_id
Integer
character
text
integer
Varchar
integer
16
5
6
10
12
6
Yes Yes address
Create judge table (Judge_id, judge_name, judge_gender, judge_phone no, Judge_address,
event_id)
Where judge_table= Data Schema
judge_id= primary key
Event_id = Foreign key.

Question- 4
Discuss the RDBMS, file system and indexes and underlying the hardware decisions.
Transaction analysis in the database tables
Relational database management system is based on the system that allow for user to
modify m construct and administer the relational database system in proper manner. This
database system will help for collecting all the data and information in the form of tables and
columns. One column is establishing the relationship between to another table. In this system,
stored data can be allowed to manipulate with the help of relational operators in the database
management system. In this system, it will use the SQL language to perform different task and
operation in the database system.
File system allows accessing the single file or table at time. In the file system Data can
be stored in the specific set of files in proper manner. Relational database management system is
maintained the update delete and modify in proper manner (Groomer and Murthy, 2018). In this
way, it also shares the database that involves for multiple user can easily access the database.
In the database system, Transaction analysis is a sequence of different operations
performed in the management system. This will help for maintain the reliable and consistent
state in proper manner. When the transaction fails then all the steps are reversed and also
maintain the data integrity. There are four important principle applicable in the database
transaction analysis.
 Atomic- it is the main series of database that perform different operations and also avoid
the partial update in the database management system in proper manner. It can be
describe the irreducible and indivisible.
 Consistent- it is the efficient idea where the transaction can be bring the database from
one state to another. It consists of cascades, constraints and triggers etc.
 Isolated- It is also described the changes that made by the transaction which hidden from
the another running transaction. It should be visible which based on the rules (Jansson
and Sandström, 2017).
 Durable- In the transaction that has to be committed towards the database where they
survive in permanently. It can be considered that committed happen only after copied the
transaction logs.

Question- 5
Purpose of Queries-
The purpose of queries language where the database can be stored in the form of specific
structured format and easily accessed with the help of query language. This language is basically
designed for the specifying the specific purpose to create queries to retrieve the information in
the database system.
SQL Syntax and queries

SQL Queries-
Inner Join:

Like -
Sub queries-

Group By-

Aggregate Functions

Order By
SUBSTR()

Date-
Operator =

Round-

Date and time-

UNION
Relational Algebra
1. Query - Select * from manager where manager_id= 113 and manager_name = 'oscar'
∏ (manager_id, manager_name) σ (manager_id= 113, manager_name= 'oscar') (Manager)
2. Select event_id
from event union select event_id, event_name from judge
∏ (judge ) σ (event_id)(R) σ(event_name)(R)∪
3. Select training_date, training_time
from training_session
∏(training_date, training_time)(training_session)

Question- 6
Evaluate the different types of security threats in the database solution.
Security threat is main problem in the database management system that can be designed
the database system to secure the confidential data because this database is secure the
information on the server where attacker cannot harm the entire personal information of Olympic
management system. There are different types of database security threats discuss in the
followings:
Excessive privileges- Sometimes, it is can be identified that when the employee are
granted to default the database privileges that can be exceed the need and requirement of
different function in DBMS (Lemahieuvanden, Broucke and Baesens, 2018). Many companies
can fail to update and give the more privilege to their employee. In this way, employee can be
change the setting of accounts and used to secure the information in proper manner.
Database injection attack- there are two important database injection attack the entire
SQL system and also targeting the tradition database management system. NoSQL injection is
used to target the large amount of data such as big data and another injection is SQL which
directly attack because they cannot have any type of SQL based technology. In this way, A
successful input injection provide the authority to attacker when it will be unrestricted the
database system.
Malware- This threats is basically used in the devices where the attacker can be stolen
the sensitive data and legitimate users. It is based on the program and file that harm the entire
hardware system. Malware also includes the worms, computer viruses, Horses and Spyware etc.
this programs is performed different functions to damage the entire data therefore, many hacker
and attacker monitor the user activities without any permissions.
Storage media exposure- In the hardware system, backup storage and media cannot be
completely protected from the attacker because they used various types of software and viruses
that affect the entire processing of system. There are numerous type of security breached
includes theft in the database backup tabs. In this way, administrator also failure in the monitors
and audit. It also low level access in the important and sensitive information that's why, it will
create risk in the database system.

Security measure in the database that protect the database and data.
To secure database collective measures need to be used to secure database from
malicious attacks and threats. Database security need to be done to secure all the aspects of
databases like data stored in database, database servers, database work flow applications and
complete database management system (Liu Duan and Xiong, 2017). Database security is
generally planned, maintained and implemented by database administrator or any other
information security professional. So following security measures can be taken to protect the
data and database like:
 Sensitive database should be isolated- An inventory should be maintained of all the
databases and all the sensitive database residing in those databases should be separated.
 Eliminate vulnerabilities: continuously identify, assess and remediate vulnerabilities that
exposes the database.
 The Least privileges should be enforced: Identify who the user is then enforce user
access controls and privileges to limit the access so that they can access minimum
required data for employees to do their jobs.
 Deviations should be monitored: Implement appropriate policies and monitor any
vulnerabilities that should be remediate for all the activities that are deviated from the
authorized activity.
 Suspicious behaviour should be responded: any abnormal or suspicious behaviour should
be responded and alerted in the real time to minimize risk of the threat or attack.
 Unauthorized access should be restricted to use the database by implementing multi
factor and strong limited access to data and management controls.
 Different types of database testing should be done like load testing, stress testing,
capacity testing so that it does not crash in user overload or distributed denial of service
attack.
 Server security and database backup equipments should be installed in case of any theft
or natural disaster.
 Road map should be implemented in case of unknown vulnerabilities it is needed to
migrate.
The most important threats is to be centralised in the database system.
There are many threats of centralized database but four main threats are:

 In a centralized database everything is stored at a single place within a database. So, if
any system failure happens then all the data stored can be destroyed or lost and will affect
the visibility of the company. There will always be a risk of complete loss of information
present when using a centralized database (Lorenzo and et.al., 2018).
 Centralized database backup is stored at a common place. So there is always a threat that
data or information can be stolen from company premises itself and as employees cannot
be trusted with their loyalty especially with the sensitive data stored in the database and
can sell it to any third party.
 Whenever database security testing is done many companies avoid SQL injections testing
which is a major threat in case of centralized database. Through SQL injections many
malicious data can be inserted into the database which will affect complete database.
 Centralized database provides limited access to everyone including administrator and
limited administration access provides poor data protection as access to users are limited
it becomes easier for hackers to have a control over database.

Question- 7
Need and requirement of optimisation in the database system.
Optimization of database refers to use of variety of strategies to reduce response time of
database. In simple terms it is simply managing load or speed of web pages. It is a technique
performed which deletes all the unwanted space and reclaim in database but doesn't reduce the
size of database. Database analyst perform all the operations in database to improve the speed
and reduce the time of any website.
 Optimization of data is required as it reduces the time to retrieve the data or information.
 It helps in faster query processing to achieve results. It is also helpful in increasing the
speed and high performance of the system.
 It helps the data analyst to use the database engine to its efficient use.
 As it reduces the unwanted space less memory is consumed which increases the speed.
 Optimization of database helps in reducing the data redundancy and updating errors.
 But the most important need of database optimization is that it improves and increases the
security of data in the database.
 Memory size of database is also used efficiently and is saved.
Monitoring is a simple task done by database administrator to ensure the health and
performance of database (Atzeni, Cabibbo and Torlone, 2018). It can be done by analysing the
workload running against the database so that if any issue is identified then it can easily be
resolved. To optimize the performance of database tuning of database is required. It normally
means maintenance of large database management system. In business complex databases is
used it is important to relay on its efficient running. Tuning generally means examining all parts
of database system including software, hardware, reduce bottleneck and keep query response
time as short as possible. After tuning of database system is done optimization of database
systems is focused to ensure the safety, performance, reduced response time etc.
Question- 8
Motivation is needed in the alternative database solution such as NoSQL
Big data is simple term used for complex and large data sets that are way too large for
data processing application software to deal with. They are used to analysing computational to
reveal patterns, associations, trends. Data with many rows and high complexity leads to false

results. Big data capture data, analyse it, store data, share, search, transfer, visualization, update
and perform many more operations. Big data is difficult to handle so it requires a computing
power to process variety and huge amount of data by a single server. So it can be said that big
data computing to process large amount of data which can be used to process work and operate
through a collaborative clustered architecture(Lorenzo and et.al., 2018). Nowadays cloud
computing is used to store, analyse and process data where a third party host the system where
they can store the data and with the help of scaling of data they can manage all the data and
changes made in it. Scaling of Big data has many advantages like as big data can store petabytes
of data scaling of big data is important weather to scale up or scale down as a scalable data can
accommodate all the quick changes either in growth of data or increasing volume of data. When
a company has platform for scalable data they can get prepared for all the rapid changes or
growth in their data needs. After scaling of big data clustering of data is to be done. Clustering
has become an essential part of companies for data mining and analysing big data as it one of the
most important for data mining where large data need to be grouped.
To improve service quality for companies with bid data NoSQL can be used. NoSQL is a
type of database which does storage and retrieval of data that are modelled in a means other than
in tabular format used in relational databases. Nowadays use of NoSQL is increasing rapidly for
big data and real time web applications. There are different kinds of NoSQL which can be used
such as:
 Key-value Store: It simply has big hashed table of values and keys. Example of key
value store is: riak, Amazon S3( Dynamo)
 Document based store: Documents made of tagged elements are stored in it. Examples:
CouchDB
 Column based store: Each storage block contains data from only one column. Example:
Hbase, Cassandra
 Graph based: Its is kind of network database that uses nodes and edges to represent and
store data. Example: Neo4J
Hadoop can be used as a possible solution for big data. Hadoop is a open source software used
for storing data and running applications on clusters of commodity hardware. It can be used to
store any kind of data as it has enormous processing power and can handle any task or jobs.

Hadoop file systems (HDFS) cab be used as it holds large amount of data and provides easier
access to the data. HDFS can be used to handle, store , process big data.
Question- 9
Personal reflection-
I have successfully completed to design the effective database system of Olympic committee
preparation to organize the event for managing the entire operations and function in proper
manner. In my opinion, it is the best approach to collect all the information and details in the
Olympic in the form of database management system. I can use the Microsoft SQL server that
help for me to create table by using the key constraints. This will really helping for me to
organize the information and I also modify, access, delete and retrieve the information in the
database system in effective manner(Lorenzo and et.al., 2018). I used the SQL server which
assist for optimise and calculate and generate the query in the best way that's why, I always used
the database tool to provide various type of facilities. Generally, I cannot be concerned towards
the SQL queries because I passed the table and database in the query optimizer to easily check
the execution of different queries. In my opinion, this concept is effective that able to calculate
the estimate of different rows and columns. I also generate the intimate rows and column by
using the effective strategies for the purpose of business process. In this tool, there are some
database engines that keep all the statistics about various distribution key values in the each and
every index of table. I used the tool that generated many problems during database creations and
development. If I can design the database therefore, I can do some mistakes in the database
development where I used the incorrect attribute for particular table that cannot create the best
relationship between the database system. There are various types of challenges faced in the
database system as followings:
 Poor design and planning
 Ignoring the normalization forms.
 Lack of documentation
 one table hold all type of domain values
 Using the identify guide and columns only the key
 Lack of testing
These are important problem that were faced by me during the creation of data base. These are
important problem that were faced by me during the creation of data base. During this whole

process I have learned that as the data storage increases security threats in a database also
increases, system becomes less responsive and management of database is also disturbed. It is
important to have a roadmap if any problem arises into the database so that no data is affected or
lost if database is attacked or any natural hazard occurs it is important to have a pre planned
solution of any problem. I have also learned that security of database is also dependent on how
many users have access to how many data as if many numbers of users will have access to larger
portion of data then data security is compromised and if data administrator has the least access
to the data then also security of data is compromised. I have also learned that big data cannot be
managed in relational databases rather it needs NoSQL database to be more precise HADOOP
database can be used to store, manage, analyse, and retrieve information.
I lave learned that to choose right database first and the foremost thing is to establish
right criteria and crate an objective of what is required and how it can be achieved. Then we need
to find the solution of the business goal and objective and how it can be achieved so that it has
complete protection of the data and how much faster it can be achieved (). Then testing and
analysis should be that that weather it can work efficiently and efficiently with your existing
technology of your company and testing should be done accordingly including load testing,
stress testing, SQL injection testing and many more ( Atzeni, Cabibbo and Torlone, 2018). At
last main things should be focused i.e. monitoring of database, tuning of database and at last
optimization of database as it increases the performance of the database, reduced unwanted
spaced in the database and releases the memory and reduced the response time of the database
queries and response time of the application or website. Backup and cyber security of the
database is also required which should be addressed and should not be compromised with as it
can affect the heath of the database. Database and the system whose data is to be stored should
be properly integrated and tested before use as it can create many hurdles and problems
afterwards. I have also learned that whenever a database is to be build all the business constraints
and business rules should be kept in mind as it will be a problem when a database is build
because it can be a problem in security of the company and ethical issues can be arise. I am
proud that I have tried to address all the questions required and have tried to implement it on the
practical work that is done.

CONCLUSION
As per discussion, it can be successfully designed the database system of Olympic
preparation committee. This is also record all the informations and details in the form of different
tables and columns. In this way, it is designed the relationship between the different tables and
columns to create relationship between them.

REFERENCES
Books and Journals:
Adetunji, M.O., Lamont, S.J. and Schmidt, C.J., 2018. TransAtlasDB: an integrated database
connecting expression data, metadata and variants. Database, 2018.
Atzeni, P., Cabibbo, L. and Torlone, R., 2018. Data Modeling Across the Evolution of Database
Technology. In A Comprehensive Guide Through the Italian Database Research Over the
Last 25 Years (pp. 221-234). Springer, Cham.
Cumin, J., Petit, J.M., Scuturici, V.M. and et.al., 2017, March. Data exploration with sql using
machine learning techniques. In International Conference on Extending Database
Technology-EDBT (pp. 96-107).
Groomer, S.M. and Murthy, U.S., 2018. Continuous auditing of database applications: An
embedded audit module approach. In Continuous Auditing: Theory and Application (pp.
105-124). Emerald Publishing Limited.
Jansson, S. and Sandström, T., 2017. Mobile Framework for Real-Time Database Management.
Lemahieu, W., vanden Broucke, S. and Baesens, B., 2018. Principles of Database Management:
The Practical Guide to Storing, Managing and Analyzing Big and Small Data. Cambridge
University Press.
Liu, F., Duan, H. and Xiong, L., 2017. Application research on tea quality traceability system of
whole industry chain based on database technology. Journal of Food Safety and
Quality, 8(7), pp.2825-2829.
Lorenzo, A.J., Rickard, M., Braga, L.H., Guo, Y. and et.al., 2018. Predictive Analytics and
Modeling Employing Machine Learning Technology: The Next Step in Data Sharing,
Analysis, and Individualized Counseling Explored With a Large, Prospective Prenatal
Hydronephrosis Database. Urology.