Database Management Systems Assignment: DML & DDL Queries On Olympic Games

Question

Task: Scenario
The Olympic Games is a global event, held every four years, where individuals and teams from hundreds of countries around the world would participate. Since 1960, the Paralympics have also been held in the same year as the Olympics. The 2020 Olympics recently concluded. Assume that you or your friends are interested in Olympic Games (either Olympics or Paralympics) and need to know about the details of games, participants, winners, and all interesting things about this event.

Detailed description of database management systems assignment
Considering the given scenario, you are expected to do the following tasks in this assignment:
1. Design a database and the relational schema based on real world scenario:
a. You are expected to identify entities and attributes, relationships, multiplicity and participation constraints and design the ER diagram. The ER diagram should be drawn following the Chen’s notation used in the lectures OR IE notation shown in the lecture slides.
b. Then, you need to decide on suitable data types and any attribute-level constraints (such as NOT NULL). You have to define your attributes in a suitable tabular format with at least the name of the attribute, selected data type, description of the attribute and any constraints on attribute values.
c. Define the relational schema:
You may do this iteratively, starting with first identifying basic tables and attributes, then refining it to convert more complex relationships in the design. All your tables should be in at least first normal form, but if you have done the design and mapping correctly, your tables would be in third normal form. You have to think about the constraints such as primary and foreign keys. You also may add any other constraints deemed required. Then you may improve the preliminary work by adding referential integrity constraints.

2. Implementing the database:
a. Looking at the relational schema and the data description resulting from part 1 above, you will implement the database with suitable tables and constraints. First, create a sample database with the name _, and implement all tables there.
b. Insert sample values to the database and demonstrate that the integrity constraints are met when entering data. NOTE: Web links to obtain sample data relating to the given scenario will be mentioned in the assignment page. You have to enter a reasonable amount of data only to the database so that the query results can be obtained in next sections. 3. Designing and implementing queries:
When your database is up and running, it is time to retrieve data to answer some reasonable queries. First, think through the real-world scenario and derive some meaningful questions (around 5-10 questions) regarding the data of your database, which can be converted to SQL queries to get answers. Make sure you think about using a single table, several tables, obtaining data based on conditions, string manipulation etc. (there are many other aspects you can think of). Then, for each of your questions, design and implement an appropriate SQL query to produce the required answer in an effective manner.

You should demonstrate that you can:
Level 1: Use basic SQL SELECT statements, with use of numeric data, date-time functions, string comparison and manipulation, and other related basic methods with suitable WHERE clauses.
Level 2: Use joins and sub-queries, with GROUP BY, ORDER BY, aggregate functions and related clauses.

4. Increase the database functionality with advanced concepts:
You can use stored procedures, triggers, views and indexes to improve the capabilities of your database. Design and implement at least two categories of the above advanced features. For example, you can have several stored procedures and a few triggers.

5. Connect to the database using a suitable programming language and show sample query results:
In this section of the assignment, you are expected to demonstrate your abilities to connect to a MySQL database with Python3 and use it in Python3 environment. Connect your database to Python3 and call your already defined queries via the Python programming constructs to show that you can do some useful database activities. You can re-use the SQL queries and approaches from the lectures and practicals, but as the scenario is different, you cannot use them directly. Remember to cite and self-cite your sources, if any. If you submit work that you have already submitted for a previous assessment (in this unit or any other) you have to specifically state this, either in the code and/or in the report.

Answer

Introduction:
The database management systems assignmentis based on the database system with the ER modeling. The database system is a very efficient tool to keep the records effectively with some constraints. These constraints help to create the table with proper integrity. In the below report, DDL and DML queries are performed to fulfill the need for the assignment. At last, SQL statements are merged with the programming language that is python and shows the output as per the queries. Every task is performed step by step in the below section.

Design of database:
i. Explanation:
Database system consists of tables or entities. After that, the entities are consisting of various attributes that can define the characteristics of an entity (Coronel and Morris, 2016).

The selected entitiesfromOlympic Game are-
Teams
Medals
EntriesGender
Coaches
Athletes
The attributes and their data type’s are-
CREATE TABLE Athletes (
NAME varchar(30) NOT NULL,
NOC varchar(30) NOT NULL,
DISCIPLINE varchar(30) NOT NULL)
CREATE TABLE Coaches (
NAME varchar(30) NOT NULL,
NOC varchar(30) NOT NULL,
DISCIPLINE varchar(30) NOT NULL,
Event varchar(30) NOT NULL);

CREATE TABLE EntriesGender (
DISCIPLINE varchar(30) NOT NULL,
Female int not null,
Male int not null,
Total int not null);

CREATE TABLE Medals (
M_ID int not null,
Team_NOC varchar(30) NOT NULL,
Gold int not null,
Silver int not null,
Bronze int not null,
Total int not null,
RankByTotal int not null);

CREATE TABLE Teams (
NAME varchar(30) NOT NULL,
DISCIPLINE varchar(30) NOT NULL,
NOC varchar(30) NOT NULL,
Event varchar(30) NOT NULL);

ii. ER Diagram and Relational Schema:
ER Diagram is a high-leveled data model applied in data elements or relationship identification with respect to a particular system (Irmayanti, 2020).

ER-Diagram-in-database-management-systems-assignment

Figure-1: ER Diagram
Source: (Self-Created)

iii. An assumption I have made during the database design:
I have designed the database System, as per the process of the Olympic Games. At the time of developing the database, I have created Teams, Medals, EntriesGender, Coaches, and Athletes tables. After that, I have inserted the data in the table(Garcia-Molina et al., 2000).

Implementing the database:
This segment of the assignment is associated with the implementing database and tables (DuBois, 2008).

Database Creation in database management systems assignment

Figure No-2: Database Creation
(Source: Developed by the learner)

Table-Creation-Athletes-in-database-management-systems-assignment

Figure No-3: Table Creation Athletes
(Source: Developed by the learner)

Table-Creation-Coaches-in-database-management-systems-assignment

Figure No-4: Table Creation Coaches
(Source: Developed by the learner)

Table-Creation-Teams-in-database-management-systems-assignment

Figure No-5: Table Creation Teams
(Source: Developed by the learner)

Designing of queries and use of the database:

i. Design and implementation of queries with sample outputs:

SELECT Query from Athletes in database management systems assignment

Figure No-6: SELECT Query from Athletes
(Source: Developed by the learner)

SELECT Query from Coaches in database management systems assignment

Figure No-7: SELECT Query from Coaches
(Source: Developed by the learner)

SELECT-Query-from-EntriesGender-in-database-management-systems-assignment

Figure No-8: SELECT Query from EntriesGender
(Source: Developed by the learner)

SELECT-Query-from-Medals-in-database-management-systems-assignment

Figure No-9: SELECT Query from Medals
(Source: Developed by the learner)

SELECT-Query-from-Teams-in-database-management systems assignment

Figure No-10: SELECT Query from Teams
(Source: Developed by the learner)

SELECT-Query-in-database-management-systems-assignment.png assignment

Figure No-11: SELECT Query
(Source: Developed by the learner)

Developed-by-the-learner-in-database-management-systems-assignment

Figure No-12: SELECT Query
(Source: Developed by the learner)

GROUP-BY-Query-in-database-management-systems-assignment

Figure No-13: GROUP BY Query
(Source: Developed by the learner)

Query-Developed-by-the-learner-in-database-management-systems-assignment

Figure No-14: Query
(Source: Developed by the learner)

Query-Developed-by-the-learner-in-database-management-systems-assignment

Figure No-15: Query
(Source: Developed by the learner)

Query-in-database-management-systems-assignment-16