Design Part, the Development, the Queries, and the Administration.
You are to design, develop and implement a database for The Aquatics Swim Club based on the concepts you have learned in CIT 170. All documents must be produced on a computer. You should carefully read this entire document prior to beginning work on this project. This project is worth a total of 100 points. The specific breakdown for each phase is noted below.
Your overall design will include choosing the tables, fields, keys, and relationships, and making sure that all tables are in either 3rd or 4th normal form. You will also develop policies for security, backup and recovery, and shared update. The specifics for each phase are as follows:
Design
- Define all entities, attributes, data types, and whether null values will be accepted.
- Specify any candidate keys for each table.
- Select the primary key for each entity.
- Describe all foreign keys and their related tables.
- Define all relationships, including type (one-to-one, one-to-many, or many-to-many).
- Describe any domain constraints (legal values/check constraints).
- Produce an Entity-Relationship diagram, which will visually describe the database design.
- Produce the DBDL for each table.
Turn in for the Design Phase:
- An Entity-Relationship diagram describing each table. This should look like the ER diagrams in Chapter 6.
- DBDL describing each table. This should look like the DBDL examples in Chapter 6. Your DBDL will show any candidate or alternate keys, primary key(s), and foreign keys.
- A listing for each table which includes each of the following:
- a. Field names
- b. Data types
- c. Whether nulls will be accepted
- d. Domain constraints (legal values/check constraints)
e. Field size for character data types
Please use a copy of the listing below to complete #3 for each individual table:
Table name:
Field Name NULLS Allowed Data Type Field Size Domain constraints
Note: The three items listed above are due before you implement/c your database in Access so that I can provide input on your design. Each of the Design Phase documents must be produced in Word, Paint or Visio, including the E-R diagram. There are numerous E-R diagram and DBDL examples in your text in Chapter 6. Please review your Chapter 6 assignment, as well as my comments on your Chapter 6 assignment, in the grade book.
Prior to submitting your Design Phase documents, please carefully read the Design Phase Hints below. Additionally, you can create the tables and add a small subset of the data. This would help you determine if you are able to include the data to the tables, with your selected primary keys without unnecessary data duplication and if your tables are in 3rd normal form (i.e. that you do not have redundant data). You should watch the Final Project Hints video (in the assignment link) to assist you in your design.
Design Hints
- You will require some duplication, but only for purposes of joining your tables (primary key to foreign key). If you have numerous tables with the same fields (non-primary key fields), this is considered unnecessary duplication and should be avoided. Think about the tables and keys in the TAL database, specifically the Customer and Rep tables. Recall that the RepNum field is the primary key in the Rep table and the RepNum field is a foreign key in the Customer table. This is duplication, but considered necessary duplication for purposes of joining the Rep and Customer tables.
- To determine the extent of data duplication, look at the fields in all of your tables to determine how many times would be required to enter the following data:
Swimmer names - separate into first and last name fields
Swimmer ID
Swimmer final times
Event number or name
Meet ID, year or title
Birth year
Gender
Team
3. The data type for the swimmer's final time field should be a numeric value because Access does not properly handle a time data type for our purposes.
4. To determine if the primary key field(s) you selected is/are appropriate, think ahead to when you are entering your data values to determine if you will have repeating groups. If you see repeating groups, then you will need to incorporate a second or third field as the primary key field(s).For instance, if you have selected swimmer's ID as the primary key field for the swimmer's final time table, when you enter the data for the same swimmer in a second event, the DBMS, Access program, will prevent you from entering the same swimmer's ID a second time because this would violate the unique property of the primary key field. This means that you would need to include a multiple-field primary key for the swimmer's final time table.
5. Review the queries to see if you have fields which are appropriate to perform each query.
6. Your design should consist of four tables. These tables will be the "objects" or "nouns" to describe the various entities involved. Think about the tables for the two databases that you are already familiar with - the Colonial Adventure Tours and TAL Distributors databases. This should help you determine the tables for the Aquatics Swim Club database.
Development
After carefully reviewing my feedback on your Design Phase in the grade book, you will implement / create your database in Access. Remember, in Design View, if you have a multiple field primary key, you will select the first field, hold the CTRL key and select the next primary key field. After selecting all fields, click the Primary Key icon.
After creating each table, you must add/load the data, as indicated in the Aquatics Swim Club information below into your tables. Turn in for the Development Phase: A copy of your Access database.
Queries
1. List the first and last names of all swimmers who competed in the Boys 100 Back event in less than 2 minutes in the 2017 event meet.
2 List the last name, age and team for all swimmers in the Girls 100 Fly, 2017 event, sorted by last name, ascending order. Hint: to determine age, you should use the swim meet year minus the birth year.
3. List the last name and team for all swimmers in the Boys 100 Fly event for the year 2017.
You may create these queries using either the QBE grid or by writing the SQL statements. QBE queries were covered in chapter 2 and SQL queries were covered in chapter three. You can also review the videos for these two chapters in the Assignment area.
Turn in for the Queries phase: Word document with a copy of the result table from each query. You may use either the QBE grid or SQL statements to create these queries.
Administration
You are to determine appropriate administrative policies and how they will be implemented for each of the following:
- Security, including password policies and views.
- Backup and recovery policies.
- Concurrent update policy in an environment based on many users in one physical location.
Turn in for the Administrative Phase:
- The policies you determine are appropriate for:
- a. Security
- b. Backup and recovery
- c. Shared update
You should describe the general concept for each administration area listed above, followed by how you would implement each policy in your database. This document must be typed in Word and should be around 1 page, double-spaced.
Database Specifics:
The following information includes all data for the Aquatics Swim Club.
The first section shows the meet title, meet ID and year. The next section lists the event name (for example Girl's 100 Fly) and event number (1-4). The same events occur each year.
Following the event descriptions are the swimmer's individual results. The swim ID uniquely identifies each swimmer. The swimmer's name, birth year, team, and the time to complete the event are also listed. You can determine the swimmer's gender by looking at the event they are swimming in (i.e. "Girl's 100 Fly).
Aquatics Swim Club Results
Meet Title: 2017 Aquatics Developmental Meet
Meet ID: KY 2017
Meet Year: 2017
Event Number: 1
Event Type: Girls 100 Fly
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 2002 WA 1 min
1061 Owen, Kristy 2003 WA 1 min
1074 Allen, Kirsten 2002 WA 2 min
1155 Hall, Amanda 2003 LYD 2 min
1181 Spittler, Katie 2002 LYD 3 min
1172 Newcomb, Danie 2003 LYD 4 min
1258 Littrell, Ashley 2002 LYD 5 min
Event Number: 2
Event Type: Boys 100 Fly
Swim ID Name Birth year Team Finals
1038 Dougherty, David 2002 WA 1 min
1115 Buncher, Stanley 2003 WA 1 min
1164 Lovell, Brandon 2002 LYD 2 min
1071 Jacobs, Clay 2003 WA 3 min
1050 Leer, Courtland 2002 WA 4 min
1018 Huster, Bradley 2003 TNT 5 min
1053 Burchett, Philip 2002 WA 6 min
Event Number: 3
Event Type: Girls 100 Back
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 2002 WA 1 min
1061 Owen, Kristy 2003 WA 1 min
1074 Allen, Kirsten 2002 WA 2 min
1155 Hall, Amanda 2003 LYD 3 min
1181 Spittler, Katie 2002 LYD 4 min
1172 Newcomb, Danie 2003 LYD 4 min
1258 Littrell, Ashley 2002 LYD 5 min
Event Number: 4
Event Type: Boys 100 Back
Swim ID Name Birth year Team Finals
1038 Dougherty, David 2002 WA 1 min
1115 Buncher, Stanley 2003 WA 1 min
1164 Lovell, Brandon 2002 LYD 2 min
1071 Jacobs, Clay 2003 WA 3 min
1050 Leer, Courtland 2002 WA 3 min
1018 Huster, Bradley 2003 TNT 4 min
1053 Burchett, Philip 2002 WA 5 min
Meet Title: 2016 Aquatics Developmental Meet
Meet ID: KY 2016
Meet Year: 2016
Event Number: 1
Event Type: Girls 100 Fly
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 2002 WA 2 min
1061 Owen, Kristy 2003 WA 3 min
1074 Allen, Kirsten 2002 WA 3 min
1155 Hall, Amanda 2003 LYD 3 min
1181 Spittler, Katie 2002 LYD 4 min
1172 Newcomb, Danie 2003 LYD 4 min
1258 Littrell, Ashley 2002 LYD 5 min
Event Number: 2
Event Type: Boys 100 Fly
Swim ID Name Birth year Team Finals
1038 Dougherty, David 2002 WA 1 min
1115 Buncher, Stanley 2003 WA 1 min
1164 Lovell, Brandon 2002 LYD 3 min
1071 Jacobs, Clay 2003 WA 4 min
1050 Leer, Courtland 2002 WA 5 min
1018 Huster, Bradley 2003 TNT 6 min
1053 Burchett, Philip 2002 WA 6 min
Event Number: 3
Event Type: Girls 100 Back
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 2002 WA 1 min
1061 Owen, Kristy 2003 WA 1 min
1074 Allen, Kirsten 2002 WA 2 min
1155 Hall, Amanda 2003 LYD 2 min
1181 Spittler, Katie 2002 LYD 3 min
1172 Newcomb, Danie 2003 LYD 4 min
1258 Littrell, Ashley 2002 LYD 5 min
Event Number: 4
Event Type: Boys 100 Back
Swim ID Name Birth year Team Finals
1038 Dougherty, David 2002 WA 1 min
1115 Buncher, Stanley 2003 WA 1 min
1164 Lovell, Brandon 2002 LYD 2 min
1071 Jacobs, Clay 2003 WA 2 min
1050 Leer, Courtland 2002 WA 2 min
1018 Huster, Bradley 2003 TNT 3 min
1053 Burchett, Philip 2002 WA 4 min