Complete the following steps to prepare for creating the DB in MySQL

Step 1: Determine the Aim of Your Relational Database

Define why you need a relational database. Describe how you will use it and what information it should contain.

Example: Suppose you are an event organizer for a track and field competition. You would a relational database to manage running events, results, and event history. With this database, you can easily retrieve and print running results, which will then be published in any sports magazine or website. As the event organizer you want to store all of the important information relating to the events, including the following:

Athlete First Name

Athlete Surname

Athlete Gender

Athlete Age

Athlete Identification number

Event name

Date of Event

Venue

Official Gun Time

Race director

Event identification number

Race number

Chip Time

Running category identification number

Running distance in kilometers

Registration fee

Total number of participants

Step 2: Define tables, fields and primary/foreign key index

A table should contain only a single category of specific information about a certain subject. A database contains different, specific subjects, which should each have their own table assignment. For example, you cannot mix information about the Athlete with information about the Events, because you will find it hard to manage and filter your needed information later on. Each should have its own table (e.g. one for Athlete and for Events).

A field is what makes the table complete and able to hold as much accurate information as possible. If you are having a problem figuring out what fields to add, just look at some paper forms or documents. You can also look at what information you ask for from your clients/employees, etc., and what information you normally deem important to keep.

A primary key index is what makes each entry unique in the table, thus preventing duplicates. A foreign key index will connect or relate the information from one table to another. Details of this will be discussed in step 3.

In accordance with the purpose of the database and the guidelines for assigning tables, the following are the tables and fields for the event organizer database:

Athlete a table containing information about the athlete. This table contains the following fields and their corresponding data types:

AthleteFirstName (Text)

AthleteSurname (Text)

AthleteGender (Text)

AthleteAge (Number)

AthleteID (Autonumber)

The primary key index should be AthleteID, since every ID generated for each Athlete is unique, preventing duplicate entries.

Events a table containing all information about the organized event. These are the fields and the data types:

EventName (Text)

EventDate (Date/Time)

Venue (Text)

GunTime (Date/Time)

RaceDirector (Text)

EventID (Autonumber)

The primary key index in this case should be EventID. 3. RunningCategory since an event can have different running categories (5K, 10K run, etc.), a specific table is needed to contain all of the information pertaining to that specific running category. These are the fields and the data types:

Length_Km (Number)

RegistrationFee (Currency)

NumberOfParticipants (Number)

RaceMapURL (Hyperlink)

CategoryID (Autonumber)

EventID (Number)

CategoryID is the primary key index for this table. EventID is the foreign key index. More details on this will be covered later.

Results this table contains all of the information about the race results of different running categories coming from different running events:

RaceNumber (Number)

ChipTime (Number)

CategoryID (Number)

AthleteID (Number)

This table does not have a primary key, although CategoryID and AthleteID are foreign key index numbers. When you enter those fields in MS Access you need to define the Field properties. For the sake of simplicity, we wont use validation rulin this database, although in a real implementation you should include one for the purpose of maintaining data integrity.

Step 3: Determine the relationships between tables

The running organizer database is a relational database. The following information shows how the tables are interrelated:

First, in the RunningCategory table, the EventID is the foreign key index. This is because any running category is a subset of a running event (e.g. a single running event can have different categories, such as a 5 km run, 10 km run, etc.). Therefore, this foreign key binds the relationship between the RunningCategory table and the Event table. This type of relationship is also known as a one-to-many relationship, because a single event can have many running categories.

Second, in the Results table, the AthleteID is used as the foreign key, because the running results race numbers and chip time details are pieces of information tied to a specific athlete participating in a specific running category. Also, the CategoryID is a foreign key, since the running results belong to a specific running category. This establishes the relationships between the Results and RunningCategory Tables.