1 of 50

OLYMPICS�DATABASE MANAGEMENT SYSTEM

THIRD SEMESTER PROJECT

2 of 50

SUBMITTED TO :

We as a team are very thankful to our mentor DR. DEBANJAN SADHYA for giving us wonderful opportunity to work on this project and solve real life problem.

While doing this project we have gained lot of experience in Database Management System

DR.DEBANJAN SADHYA

3 of 50

TEAM MEMBERS

HARSH DALWADI

HARSHVARDHAN�RAJ

APOORV �JAIN

01

04

AYUSH RAKESH

02

03

2021-IMT-026

2021-IMT-042

2021-IMT-015

2021-IMT-018

05

06

DEEPAK

KRIPLANI

�YASH

NIGAM

2021-IMT-115

2021-IMT-027

4 of 50

01. OVERVIEW OF PROJECT

The modern Olympic Games or Olympics are the leading international sporting events featuring summer and winter sports competitions in which thousands of athletes from around the world participate in a variety of competitions. The Olympic Games are considered the world's foremost sports competition with more than 200 teams, representing sovereign states and territories, participatingThis is an Olympics Database project which consists of developing of entities and their respective attributes. This approach also includes writing and applying

queries as required . After forming initial Entities with their attributes then we proceed with developing ER diagram for given entities in further phases.Details of the atheletes, countries, International Olympics Committee, Olympic Games are covered under this project . This database can handle different requests and provides an output based on above entities. The results from this database is used by olympic authorities for better decision making.

5 of 50

02.�ER DIAGRAM

6 of 50

7 of 50

ENTITY SETS

Athlete

This entity represents the athletes taking part in at least one sport in any Olympic. It has attributes name, dob, gender, contact, athlete_id

Country

This entity represents the countries taking part in at least one Olympics in any year. It has attributes such as abbreviation, name, anthem

Olympic

This entity represents the Olympics held in different years. It has attributes year, sponsors and host country

8 of 50

Olympic_game

This entity represents the game which is played in different Olympics in different years. It has attributes such sport, subsport and type.

Time_stamp

This entity has the attributes commencement time and date which gives information about which Olympic game is played at what date and time

Official

This entity represents the officers that forms an IOC(International Organization Council) in any Olympics. It has attributes such as dob, name, contact , official_id, etc.

9 of 50

AGGREGATION

An ER diagram is not capable of representing relationship between an entity and a relationship which may be required in some scenarios. In those cases, a relationship with its corresponding entities is aggregated into a higher level entity. Aggregation is an abstraction through which we can represent relationships as higher level entity sets.

An athlete can participate in various sports in multiple years. So played relationship is needed between played_at relationship and athlete entity. Using aggregation, played_at relationship with its entities Olympic_game and Olympics is aggregated into a single entity and the relationship played is created between aggregate entity and athlete.

10 of 50

03. ASSUMPTIONS

A sport event can last for multiple days.
In a particular year, a particular Olympic game is organized in the same place even if they are played on multiple days.
In a single year, a single Olympics will be organized.
The data of only those countries is taken which has participated in at least one Olympics.
All the contacts of players and officials will be unique.

11 of 50

04. RELATIONSHIP SETS AND CARDINALITIES

Belongs_to

It is a one to many relationship between country and athlete as multiple athletes can participate from single country and an athlete can belong to one and only one country. Country and Athlete both are showing total participation

On

It is an identifying relationship for the time_stamp which is a weak entity set and the aggregate entity is the identifying strong entity set. It is many to many relationship as single sport event can go on for multiple days and a many sport events can start at the same timestamp. It has total participation on both the sides.

12 of 50

IOC (International Olympic Committee)

it is a many to many relationship between Olympic entity and Officials as many officials can be part of an organizing IOC and a single official can be a part of multiple IOCs. Both the entities show total participation. It has a descriptive attribute post which identities which official has which post in a particular IOC.

Played

It is a many to many relationship between athlete and an aggregate entity which includes Olympic_game and Olympic entities. Both the entities are showing total participation. It has a composite descriptive attribute of result which shows which medal was won by an athlete in a particular Olympic game.

13 of 50

Played_at

It is a many to many relationship between Olympic_game and Olympic as an Olympic can have many Olympic games and a single Olympic game can be part of many olympics. Both entites are showing total participation. It has a composite descriptive attribute site_address which shows where was an Olympic game organized in a particular year.

Participated

It is a many to many relationship between country and Olympic as many countries can participate in single Olympic as well as a single country can participate in multiple Olympics. It has total participation from both the sides.

14 of 50

ER DIAGRAM TO RELATIONAL SCHEMA

05.

15 of 50

1) In athlete entity, contacts is a multi-valued attribute. That means, an athlete may have more than one contact number.

To reduce a multi-valued attribute into a relational schema, we have to create a separate table for each multi-valued attribute. Also, we need to include the primary key of strong entity set as a foreign key attribute to establish link.

Therefore, the athlete entity set will get divided into :

Athlete(athlete_id, name, dob, gender)

Athlete_contact(athlete_id, contact)

By the same reason, the officials entity will get divided into :

Official(official_id, dob, name, gender)

Official_contact(official_id, contact)

16 of 50

By the same logic, the olympic entity will get divided into :�Olympic(year, host_country)�Olympic_sponsor(year, sponsor)��2) Belongs_to(athlete_id, abbrev)�Since belongs_to relationship is one to many relationship between country and athlete and there is total participation in the athlete side, therefore instead of creating a schema for belongs_to relationship, we will be adding abbrev (primary key of country) in the athlete schema as a foreign key.�So the new schema for athlete entity :�Athlete(athlete_id, name, gender, abbrev, dob)�

17 of 50

WEAK ENTITY SET

Timestamp(commencement, date)

In the ER Diagram, Timestamp is an entity having commencement and date as the two attributes.It is a weak entity because it does not have any presence of its own and it depends upon the strong aggregate entity.

Therefore, we add the primary keys of the aggregate entity into the Timestamp entity as foreign keys. Since it is a weak entity set, the identifying relationship will be redundant and we have to add primary key of identifying strong entity set(aaggregate entity) and the resultant schema will be as follows :

Timestamp(sport, subsport, year, commencement, date)

18 of 50

The schemas (before normalization) will be : �

athlete(athlete_id, name, dob, gender, abbrev )
athlete_contact(athlete_id, contact)
Country(abbrev, name, anthem)
Participated(abbrev, year)
Played(result, sport, subsport, year)
Olympic(year, host_country)
Olympic_sponsors(year, sponsor)
Officials(official_id, name, dob, gender)
official_contact(official_id, contact)
IOC(year, official_id, post)
Timestamp(sport, subsport, year, commencement, date)
Olympic_game(sport, subsport, type)
Played_at(sport, subsport, year, pincode, city, state)

19 of 50

FUNCTIONAL DEPENDENCIES

06.

20 of 50

Schema	Functional Dependencies
athelete	athelete_id 🡪 dob, gender, name, abbrev
athelete_contact	contact 🡪athelete_id
country	abbrev 🡪 name, anthem
	name 🡪 anthem, abbrev
	anthem 🡪 abbrev, name
IOC	year, official_id 🡪 post
officials	official_id🡪name, dob, gender
Official_contact	Contact 🡪 official_id
olympic	year 🡪 host_country
played	athelete_id, year, sport, subsport🡪result
played_at	sport, subsport, year 🡪 pincode, city, state
	city 🡪 state
	pincode 🡪 city, state
olympic_game	Sport,subsport🡪 type
	sport 🡪 type

21 of 50

DECOMPOSITION USING NORMALIZATION

07.

22 of 50

1NF : Since all the attributes are in atomic form, the table is in 1NF

2NF : Since there is no partial dependency, the table is in 2NF

3NF : There is a transitive dependency,

Subsport, sport, year 🡪 pincode

pincode 🡪 city, state

which includes non-prime attributes. To convert the table into 3NF we split the table into two tables E1 and E2 :

E1(sport, subsport, year, pincode)

E2(pincode , city, state)

Played_at
PK	sport
PK	subsport
PK	year
	state
	city
	pincode

Here, E2 is still not in 3NF, therefore we split E2 into two table E3 and E4 :

E3(pincode, city)

E4(city, state). Therefore, played_at table gets decomposed into E1, E3, E4 and all are in 3NF , and all are in BCNF as well.

23 of 50

1NF : As all the attributes are in atomic form, the table is in 1NF form

2NF : As there is a dependency sport 🡪 type, we split the table into 2 tables E1 and E2

E1(sport, subsport)

E2(sport, type)

Both E1 and E2 are now in 2NF.

3NF : As there is no transitive dependencies in E1 and E2, both the tables are in 3NF and all are in BCNF as well.

Olympic_game
PK	sport
PK	subsport
	type

24 of 50

Athelete
PK	athelete_id
	dob
	name
	gender
FK	abbrev

athelete_contact
FK	athlete_id
PK	contact

1NF – All attributes are atomic in nature, therefore table is in 1NF

2NF – There is no partial dependencies, therefore table is in 2NF

3NF –There is no transitive dependenvcies, therefore table is in 3NF

BCNF – All dependencies have superkey or candidate key on left side

1NF – All attributes are atomic in nature, therefore table is in 1NF

2NF – There is no partial dependencies, therefore table is in 2NF

3NF –There is no transitive dependenvcies, therefore table is in 3NF

BCNF – All dependencies have superkey or candidate key on left side

25 of 50

Country
PK	abbrev
	name
	anthem

IOC
PK	year
PK	official_id
	post