BSIM3017 Database Systems Individual Assignment: Data Requirements

This report presents a solution to a database systems assignment focused on designing a database for a school's athletic meets. It begins with an Entity Relationship Diagram (ERD) to model the data objects, their attributes, and relationships. The report then details the creation of a data schema, normalized to the third normal form (3NF), ensuring data integrity and eliminating redundancy. It also explores de-normalization techniques to improve query performance and includes the addition of indexes. Finally, the report provides example SQL queries to retrieve specific data from the database, such as finding contestants who won gold medals in track events and counting the number of participants in field events. The report is structured to provide a comprehensive understanding of database design principles and practical implementation using SQL.

Running head: DATABASE MANAGEMENT SYSTEM
DATABASE MANAGEMENT SYSTEM
Name of the Student
Name of the University
Author Note

Table of Contents
Entity Relationship Diagram...........................................................................................................2
Data Schema (3nf)...........................................................................................................................3
De-normalization effort or addition of indexes...............................................................................4
Queries.............................................................................................................................................5
References........................................................................................................................................6

Entity Relationship Diagram
An entity relationship diagram (ERD) represents the sets of the data objects with entities,
attributes and relationship between them. The following diagram has been mapped to support the
computer application of the athletic meets for the school. The relation has been represented using
Chen’s notation of one to one, one to many and many to many (Łacheciński, 2013). Based on
this ERD model the further schema can be developed and implemented in the application
development.
Figure 1: Entity Relationship Model of Athletic meets 2018

Source: created by author
Data Schema (3nf)
Data Schema is the logical view of the physical database which can be implemented in
the application development (Connolly & Begg, 2015). Along with the Entities, attributes and
relationship, the primary and foreign keys are highlighted after normalizing the schema to the
third normal form. The normalization of the schema can be described below:
a. First Normal form: According to the first normal form, the tables should have only
atomic values that means each row should have one value. If there is multiple values for a
row it should be removed. In this schema, all the values are atomic hence the schema is
already in first normal form.
b. Second Normal form: It states that, the schema should be in first normal form and all the
non-key attributes in the table should be fully functional dependent on the key attributes
of that table. All the primary key and foreign key has been created for the tables. Hence
the schema is in second normal form.
c. Third Normal form: The schema should be in second normal form and there should not
be any transitive functional dependencies on between the non-key attributes (Form,
2014). In this scenario the Results table removes the transitive functional dependencies
between the each participant’s name, event and their points gained on that event. Many-
to-many relationship is also resolved in this normal form.
The below data schema is normalized into third normal form along with the all key and non-key
attributes.

Figure 2: Data Schema of Athletic meets 2018
Source: created by author
De-normalization effort or addition of indexes
De-normalization of is done for improving the performance of the database in terms of
queries. It removes the disadvantageous breakdowns of the tables and functional dependencies
to achieve data retrieval with less joining of the tables (Hasler, 2014). In this case, the house
table has been removed and included into the contestant table as direct value and dependent on
the contestant ID. Apart from that, an indexing has been added as column in events table to

recognize the events type whether it is track based, field based or court based games. It will help
in the queries to identify and sort the data based on the event type.
Figure 3: Data Schema of Athletic meets 2018 after de-normalization
Source: created by author
Queries
1. Select c.Name from Contestant c inner join Results r on r.ID=c.ID
Inner join Events e on e.eventno=r.eventno and e.event_type=’Track Events’ and
r.medal = ‘Gold’;
2. Select count(ec.id) from Results r inner join Events e on e.eventno=
r.eventno and e.event_type=’Field’;

References
Connolly, T., & Begg, C. (2015). Database systems. Pearson Education UK.
Form, T. N. (2014). Third Normal Form. Fourth Normal.
Hasler, T. (2014). Physical Database Design. In Expert Oracle SQL (pp. 403-424). Apress,
Berkeley, CA.
Łacheciński, S. (2013). Modeling notations in the design of relational databases. Studia
Informatica, 34(4), 5-21.