Define a one-to-many relationship between the primary tblLevel table and the related tblAthlete table using the LevelID field. Choose the options to enforce referential integrity and to cascade update related fields.

PROJECT STEPS
⦁ Sandra Coates Skating Club (SCSC) is committed to the development of skaters of all ages. It manages athlete and coach data for skating athletes located at various skating clubs throughout Pennsylvania. Sandra Coates is the founder and general chairperson of SCSC, and she has just received approval from the board of directors to replace SCSC’s manual system of managing data about athletes, coaches, and locations with a database. She has asked you to help her create objects in the database that she and other staff members can use to manage data about athletes, coaches, and their locations.

Open the tblCoach table in Design View, and then make the following changes:
⦁ Move the LevelID field immediately after the CoachID field.
⦁ Delete the CertificationLevel field from the table. (Hint: If a warning message appears asking if you want to permanently delete the field and all the data it contains, click Yes.)
⦁ Save the table.

⦁ Switch to Datasheet View, enter the records shown in Table 1 below into the tblCoach table, and then close the table.

Table 1: New Records for the tblCoach Table

Coach ID Level ID Coach First Name Coach Last Name BGC Expiration Certification Expiration
901900 BEG2 Angela Pearson 12/31/2020 12/31/2020
901901 BEG1 Nora Keller 12/31/2020 12/31/2020

⦁ Create a new table in Datasheet View. Save the table as tblAthlete, and then make the following changes in Datasheet View:
⦁ Change the data type of the ID field to Short Text, and then change the field name to AthleteID.
⦁ Add the following fields to the table in the order listed, and choose the Short Text data type for each field: LocationID, FirstName, LastName, Address, City, State, Zip, LevelID, and Gender.
⦁ Save the table.
⦁ Switch to Design View for the tblAthlete table, and then set the field properties shown in Table 2 below.

Table 2: Field Properties for the tblAthlete Table

Field Name Data Type Description Field Size Other
AthleteID Short Text Primary key 5 Caption: Athlete ID
LocationID Short Text Foreign key 4 Caption: Location ID
FirstName Short Text 20 Caption: First Name
LastName Short Text 30 Caption: Last Name
Address Short Text 35
City Short Text 35
State Short Text 2 Default Value: PA
Zip Short Text 10
LevelID Short Text Foreign key 4 Caption: Level ID
Gender Short Text M, F 1

⦁ Add a new field to the tblAthlete table, immediately after the Zip field. Use the field name BirthDate, the Date/Time data type, the Short Date format, and the caption Birth Date.
⦁ Move the LevelID field so that it is located immediately after the AthleteID field. Save and close the table.
⦁ Much of the data that Sandra needs in the database is currently stored in other formats and locations, so you need to import it into the database. Use the Import Spreadsheet Wizard to add data to the tblAthlete table from an Excel spreadsheet as follows:
⦁ Specify the file Support_NP_AC16_CS1-4a_Athletes.xlsx, available for download from the SAM website, as the source of the data.
⦁ Select the option to append a copy of the records to the tblAthlete table.
⦁ In the Import Spreadsheet Wizard dialog boxes, choose the Athlete worksheet, and then import to the tblAthlete table. Do not save the import steps.
⦁ Import the structure of the tblLocation table in the database Support_NP_AC16_CS1-4a_SCSC.accdb, available for download from the SAM website, into the current database. Do not save the import steps.
⦁ Open the tblLocation table in Design View, and then update the field properties shown in Table 3 below. Set the LocationID field as the Primary Key, then save and close the tblLocation table.

Table 3: Field Properties for the tblLocation Table

Field Name Data Type Description Field Size Other
LocationID Short Text Primary key 4 Caption: Location ID
LocationName Short Text 50 Caption: Location Name

⦁ Sandra exported her existing location data to a text file, and she asks you to add this data to the tblLocation table. Import the data as instructed below:
⦁ Specify the file Support_NP_AC16_CS1-4a_Locations.txt, available for download from the SAM website, as the source of the data.
⦁ Select the option to append a copy of the records to the tblLocation table.
⦁ In the Import Text Wizard dialog boxes, choose the options to import delimited data, to use a comma delimiter, and to import the data into the tblLocation table. Do not save the import steps.
⦁ Open the tblLocation table in Datasheet View, and then resize the columns to best fit the data they contain.
⦁ Save and close the tblLocation table.
⦁ Create a new table in Design View, and then set the field properties shown in Table 4 on the following page. The LevelID field should be the primary key for the table. Save the table with the name tblLevel, and then close the table.

Table 4: Field Properties for the tblLevel Table

Field Name Data Type Description Field Size Other
LevelID Short Text Primary key 4 Caption: Level ID
LevelName Short Text 30 Caption: Level Name
MonthlyFee Currency Caption: Monthly Fee
Decimal Places: 0
RegistrationFee Currency Caption: Registration Fee
Decimal Places: 0

⦁ Sandra exported her levels data to a text file, and she asks you to add this data to the tblLevel table. Import the data as instructed below:
⦁ Specify the file Support_NP_AC16_CS1-4a_Levels.txt, available for download from the SAM website, as the source of the data.
⦁ Select the option to append a copy of the records to the tblLevel table.
⦁ In the Import Text Wizard dialog boxes, choose the options to import delimited data, to use a comma delimiter, and to import the data into the tblLevel table. Do not save the import steps.
⦁ Open the tblLevel table in Datasheet View, and then resize the columns to best fit the data they contain.
⦁ Save and close the tblLevel table.
⦁ Add the tblCoach, tblLevel, tblAthlete, and tblLocation tables, in that order, to the Relationships window. Resize the field list for the tblAthlete table so all fields are visible. Create the relationships in the database as instructed below:
⦁ Define a one-to-many relationship between the primary tblLevel table and the related tblCoach table using the LevelID field. Choose the options to enforce referential integrity and to cascade update related fields.
⦁ Define a one-to-many relationship between the primary tblLevel table and the related tblAthlete table using the LevelID field. Choose the options to enforce referential integrity and to cascade update related fields.

⦁ Define a one-to-many relationship between the primary tblLocation table and the related tblAthlete table using the LocationID field. Choose the options to enforce referential integrity and to cascade update related fields. Figure 1 below shows the completed Relationships window. Save and close the Relationships window.