CIT 170 Database Project After reading through this entire document, please watch the video on Project Design Hints. The video has additional information to assist you with your design phase. 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 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. 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 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 3. The data type for the swimmers 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 swimmers ID as the primary key field for the swimmers 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 swimmers 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 swimmers 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. 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. 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 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 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 Girls 100 Fly) and event number (1-4). The same events occur each year. Following the event descriptions are the swimmers individual results. The swim ID uniquely identifies each swimmer. The swimmers name, birth year, team, and the time to complete the event are also listed. You can determine the swimmers gender by looking at the event they are swimming in (i.e. Girls 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