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 3^rd or 4^th 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

1. Define all entities, attributes, data types, and whether null values will be accepted.
2. Specify any candidate keys for each table.
3. Select the primary key for each entity.
4. Describe all foreign keys and their related tables.
5. Define all relationships, including type (one-to-one, one-to-many, or many-to-many).
6. Describe any domain constraints (legal values/check constraints).
7. Produce an Entity-Relationship diagram, which will visually describe the database design.
8. Produce the DBDL for each table. 

Turn in for the Design Phase:

1. An Entity-Relationship diagram describing each table.  This should look like the ER diagrams in Chapter 6. 
2. 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. 
3. 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)

1. 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 3^rd 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

1. 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.
2. 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

1. 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.
2. 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.

3. Review the queries to see if you have fields which are appropriate to perform each query.
4. 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: 8 points	All four tables appropriate to complete the design are included.	Most of the tables are included.	Most of the tables are not included.
Fields:  4 points.	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: 10 points	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: 8 points	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: 4 points.	DBDL listing is correct.	DBDL listing is mostly correct.	DBDL listing is not correct.
Primary and foreign keys: 6 points	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.

1. 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:

1. Security, including password policies and views.
2. Backup and recovery policies.
3. Concurrent update policy in an environment based on many users in one physical location.

Turn in for the Administrative Phase:

1. 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

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.