Stuck on a Data Management Project
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 – 40 points
- 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)
-
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 |
Data Type |
Field |
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
- 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.
- 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.
- Review the queries to see if you have fields which are appropriate to perform each query.
- 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.
Grading Rubric for Design Phase – 40 points
Criteria |
Excellent |
Satisfactory |
Poor |
Tables: |
All four tables appropriate to complete the design are included. |
Most of the tables are included. |
Most of the tables are not included. |
Fields: |
All appropriate fields are included in each table. |
Most of the fields included in each table are included. |
Most of the fields are not included. |
Table listing: |
Table listing includes all tables, fields, nulls, data types, field sizes and appropriate domain constraints. |
Table listing shows most of the information required, but some of it is missing or incorrect. |
Table listing does not show most of the information required. |
ER Diagram: |
ER diagram is drawn correctly, including relationships, tables and fields. |
ER diagram is mostly correct but some relationships, tables or fields are not correct. |
ER diagram is not correct. |
DBDL: |
DBDL listing is correct. |
DBDL listing is mostly correct. |
DBDL listing is not correct. |
Primary and foreign keys: |
Primary and foreign key fields are appropriate for every table. |
Primary key field is appropriate for most tables. |
Primary key field is not appropriate for most tables. |
Development – 20 points
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.
Grading Rubric for Development Phase – 20 points
Criteria |
Excellent |
Satisfactory |
Poor |
Tables and fields: 10 points |
All tables and field names are correct. |
All tables are included but some fields are missing. |
Some tables are not included and/or some fields are missing. |
Data types: 5 points |
All data types for each field are correct. |
Most data types are correct but some fields that should be text are numeric and vice versa. |
Most data types are not correct. |
Data included: 5 points |
All data for each table is included and correct. |
Most of the data is included and correct. |
None of the data is included. |
Queries – 20 points
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 2013 event meet.
2 List the last name, age and team for all swimmers in the Girls 100 Fly, 2013 event, sorted by last name, ascending order.
-
List the last name and team for all swimmers in both the Boys 100 Fly and Boys 100 Back events, for the year 2013.
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 3. 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.
Grading Rubric for Queries – 20 points
Criteria |
Excellent |
Satisfactory |
Poor |
Query field names: 10 points |
Queries display only field names listed in the instructions criteria (no more and no less). |
Queries display most of the field names listed in the instructions criteria but includes either additional fields or does not include all fields. |
Queries do not display field names listed in instructions criteria. |
Query results: 10 points |
Queries display correct results with no duplication. |
Queries display correct results but duplicates are listed. |
Queries do not display the correct results. |
Administration – 20 points
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.
Grading Rubric for Administrative Phase Paper- 20 points
Criteria |
Excellent |
Satisfactory |
Poor |
All three topics covered 5 points |
Paper includes written policies for security, backup and recovery and shared update policies. |
Paper includes written policies which cover only two of the listed topics. |
Paper includes written policies which cover one or none of the listed topics. |
Proper sentence structure 3 points |
All sentences are written using proper English, including correct spelling, grammar and sentence structure. |
Most sentences are written using proper English. There are a few spelling and/or grammatical errors. |
Most sentences are NOT written using proper English and sentence structure and/or there are multiple spelling and grammatical errors. |
Length of paper 2 points |
Should be approximately one page, double-spaced. |
Paper is less than one page (or longer than two pages). |
Paper is only a few sentences in length (much less than one page). |
Content 10 points |
Paper addresses appropriate policies for each topic. |
Paper addresses appropriate polices for 2 of the three listed topics. |
Paper addresses appropriate policies for less than two of the three topics listed. |
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: 2013 Aquatics Developmental Meet
Meet ID: KY 2013
Meet Year: 2013
Event Number: 1
Event Type: Girls 100 Fly
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 1999 WA 1 min
1061 Owen, Kristy 2000 WA 1 min
1074 Allen, Kirsten 1999 WA 2 min
1155 Hall, Amanda 2000 LYD 2 min
1181 Spittler, Katie 1999 LYD 3 min
1172 Newcomb, Danie 2000 LYD 4 min
1258 Littrell, Ashley 1999 LYD 5 min
Event Number: 2
Event Type: Boys 100 Fly
Swim ID Name Birth year Team Finals
1038 Dougherty, David 1999 WA 1 min
1115 Buncher, Stanley 2000 WA 1 min
1164 Lovell, Brandon 1999 LYD 2 min
1071 Jacobs, Clay 2000 WA 3 min
1050 Leer, Courtland 1999 WA 4 min
1018 Huster, Bradley 2000 TNT 5 min
1053 Burchett, Philip 1999 WA 6 min
Event Number: 3
Event Type: Girls 100 Back
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 1999 WA 1 min
1061 Owen, Kristy 2000 WA 1 min
1074 Allen, Kirsten 1999 WA 2 min
1155 Hall, Amanda 2000 LYD 3 min
1181 Spittler, Katie 1999 LYD 4 min
1172 Newcomb, Danie 2000 LYD 4 min
1258 Littrell, Ashley 1999 LYD 5 min
Event Number: 4
Event Type: Boys 100 Back
Swim ID Name Birth year Team Finals
1038 Dougherty, David 1999 WA 1 min
1115 Buncher, Stanley 2000 WA 1 min
1164 Lovell, Brandon 1999 LYD 2 min
1071 Jacobs, Clay 2000 WA 3 min
1050 Leer, Courtland 1999 WA 3 min
1018 Huster, Bradley 2000 TNT 4 min
1053 Burchett, Philip 1999 WA 5 min
Meet Title: 2014 Aquatics Developmental Meet
Meet ID: KY 2014
Meet Year: 2014
Event Number: 1
Event Type: Girls 100 Fly
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 1999 WA 2 min
1061 Owen, Kristy 2000 WA 3 min
1074 Allen, Kirsten 1999 WA 3 min
1155 Hall, Amanda 2000 LYD 3 min
1181 Spittler, Katie 1999 LYD 4 min
1172 Newcomb, Danie 2000 LYD 4 min
1258 Littrell, Ashley 1999 LYD 5 min
Event Number: 2
Event Type: Boys 100 Fly
Swim ID Name Birth year Team Finals
1038 Dougherty, David 1999 WA 1 min
1115 Buncher, Stanley 2000 WA 1 min
1164 Lovell, Brandon 1999 LYD 3 min
1071 Jacobs, Clay 2000 WA 4 min
1050 Leer, Courtland 1999 WA 5 min
1018 Huster, Bradley 2000 TNT 6 min
1053 Burchett, Philip 1999 WA 6 min
Event Number: 3
Event Type: Girls 100 Back
Swim ID Name Birth year Team Finals
1078 Viney, Barbie 1999 WA 1 min
1061 Owen, Kristy 2000 WA 1 min
1074 Allen, Kirsten 1999 WA 2 min
1155 Hall, Amanda 2000 LYD 2 min
1181 Spittler, Katie 1999 LYD 3 min
1172 Newcomb, Danie 2000 LYD 4 min
1258 Littrell, Ashley 1999 LYD 5 min
Event Number: 4
Event Type: Boys 100 Back
Swim ID Name Birth year Team Finals
1038 Dougherty, David 1999 WA 1 min
1115 Buncher, Stanley 2000 WA 1 min
1164 Lovell, Brandon 1999 LYD 2 min
1071 Jacobs, Clay 2000 WA 2 min
1050 Leer, Courtland 1999 WA 2 min
1018 Huster, Bradley 2000 TNT 3 min
1053 Burchett, Philip 1999 WA 4 min
"Looking for a Similar Assignment? Order now and Get 10% Discount! Use Code "GET10" in your order"
![](https://timelyhomework.com/wp-content/uploads/2020/09/order_now-2.png)
![](https://timelyhomework.com/wp-content/uploads/2021/01/order-process.jpeg)