Question

1 Approved Answer

Posted on Jul 15, 2024

4. Golf club GolfersUnite organizes golf tournaments for its members and their guests. They use the following TOURNAMENT RESULTS table to keep track of players,

4. Golf club GolfersUnite organizes golf tournaments for its members and their guests. They use the following TOURNAMENT RESULTS table to keep track of players, tournaments and results. If you are not familiar with golf, golfers play 18 holes. The number of strokes it takes a golfer to get the golf ball to the hole is their score for the tournament, the lower the better. The par of a golf course is 72 (18 holes times 4). This means that 72 strokes are expected, anything greater than 72 is above par, recorded with a plus sign, e.g. +8. Anything below par is recorded with a minus sign, e.g.-3. For every tournament, the player's score is recorded as below or above par. A player who shot 80 strokes will have a score of +8 (8 above par of 72). Every golf player has a handicap, which is the average number of the strokes above par that the player shoots over a year. TOURNAMENT RESULTS Field Name Sample Record 1 Sample Record 2 Sample Record 3 Sample Record 4 Tournament Code MEM19 MEM19 THX17 THX18 Tournament Name Memorial Memorial Thanksgiving Thanksgiving Tournament 2019 Tournament 2019 Tournament 2017 Tournament 2018 Tournament Date 5/27/2019 5/27/2019 11/25/2017 11/26/2018 Tournament Sponsors GolfBallsRUS; GolfBallsRUS; GolfBallsRUS; GolfBallsRUS; Nike; Nike; ABS Catering Dairy Queen; ABS Catering ABS Catering Tournament Golf Innisbrook Island Innisbrook Island Tampa Palms Innisbrook Island Course Golf Course Location Palm Harbor Palm Harbor Tampa Palm Harbor Player Number 1001 1002 1001 1002 Player Name Tom Galaxy Bill Panda Tom Galaxy Bill Panda Player Handicap +3 +3 Player Tournament +7 +4 +5 +6 Score Tournament Director Doc Gator Doc Gator Mama Bear Doc Gator Tournament Director (727) 555 5555 (727) 555 5555 (727) 999 9999 (727) 555 5555 Phone +5 +5 a) Examine the data in the above TOURNAMENT RESULTS table. In what normal form is this table? (3 points) b) List dependencies in the existing table structure. Show full functional, partial and/or transitive dependencies (if exist). (10 points) Full dependency: Partial Dependencies: 2. The following relational model shows four relations that form part of a database: PRODUCT (ProductNo, ProductName, ProductType, Product Description, Product Weight) ORDER (OrderNo, Order Date, OrderSubtotal, OrderTax, OrderCustomer) RECEIPT (OrderNo, ProductNo, Quantity) CUSTOMER (Customer No, CustomerFName, CustomerLName, Customer StreetAddress, CustomerCity, CustomerState, Customer Zip) Where PRODUCT contains product details and ProductlNo is the primary key; ORDER contains order details for each order and Order No is the primary key; RECEIPT contains details of order line items and Order No and Product No form the primary key; CUSTOMER contains customer details and CustomerNo is the primary key. a. Identify all foreign keys in this schema, identify the table they are in and the table they reference. (6 points) Answer in the following format: EXAMPLE Foreign Key In Table References table DepartmentID Faculty Department YOUR ANSWER based on the relational model above Foreign Key In Table References table a. Explain what is entity integrity? (4 points) b. Explain how entity integrity can be enforced in PRODUCT relation in the model above. (5 points) ITM408-Data and Information Management Fall 2020 Dr. Veltri d) Golfers Unite continued: Create the ERD to capture the information requirements shown in the TOURNAMENT RESULTS table. Use your normalized relations, and all appropriate data modeling techniques and guidelines. This can be done on paper or you can paste your Visio/LucidCharts diagram. (20 points) 4. Golf club GolfersUnite organizes golf tournaments for its members and their guests. They use the following TOURNAMENT RESULTS table to keep track of players, tournaments and results. If you are not familiar with golf, golfers play 18 holes. The number of strokes it takes a golfer to get the golf ball to the hole is their score for the tournament, the lower the better. The par of a golf course is 72 (18 holes times 4). This means that 72 strokes are expected, anything greater than 72 is above par, recorded with a plus sign, e.g. +8. Anything below par is recorded with a minus sign, e.g.-3. For every tournament, the player's score is recorded as below or above par. A player who shot 80 strokes will have a score of +8 (8 above par of 72). Every golf player has a handicap, which is the average number of the strokes above par that the player shoots over a year. TOURNAMENT RESULTS Field Name Sample Record 1 Sample Record 2 Sample Record 3 Sample Record 4 Tournament Code MEM19 MEM19 THX17 THX18 Tournament Name Memorial Memorial Thanksgiving Thanksgiving Tournament 2019 Tournament 2019 Tournament 2017 Tournament 2018 Tournament Date 5/27/2019 5/27/2019 11/25/2017 11/26/2018 Tournament Sponsors GolfBallsRUS; GolfBallsRUS; GolfBallsRUS; GolfBallsRUS; Nike; Nike; ABS Catering Dairy Queen; ABS Catering ABS Catering Tournament Golf Innisbrook Island Innisbrook Island Tampa Palms Innisbrook Island Course Golf Course Location Palm Harbor Palm Harbor Tampa Palm Harbor Player Number 1001 1002 1001 1002 Player Name Tom Galaxy Bill Panda Tom Galaxy Bill Panda Player Handicap +3 +3 Player Tournament +7 +4 +5 +6 Score Tournament Director Doc Gator Doc Gator Mama Bear Doc Gator Tournament Director (727) 555 5555 (727) 555 5555 (727) 999 9999 (727) 555 5555 Phone +5 +5 a) Examine the data in the above TOURNAMENT RESULTS table. In what normal form is this table? (3 points) b) List dependencies in the existing table structure. Show full functional, partial and/or transitive dependencies (if exist). (10 points) Full dependency: Partial Dependencies: 2. The following relational model shows four relations that form part of a database: PRODUCT (ProductNo, ProductName, ProductType, Product Description, Product Weight) ORDER (OrderNo, Order Date, OrderSubtotal, OrderTax, OrderCustomer) RECEIPT (OrderNo, ProductNo, Quantity) CUSTOMER (Customer No, CustomerFName, CustomerLName, Customer StreetAddress, CustomerCity, CustomerState, Customer Zip) Where PRODUCT contains product details and ProductlNo is the primary key; ORDER contains order details for each order and Order No is the primary key; RECEIPT contains details of order line items and Order No and Product No form the primary key; CUSTOMER contains customer details and CustomerNo is the primary key. a. Identify all foreign keys in this schema, identify the table they are in and the table they reference. (6 points) Answer in the following format: EXAMPLE Foreign Key In Table References table DepartmentID Faculty Department YOUR ANSWER based on the relational model above Foreign Key In Table References table a. Explain what is entity integrity? (4 points) b. Explain how entity integrity can be enforced in PRODUCT relation in the model above. (5 points) ITM408-Data and Information Management Fall 2020 Dr. Veltri d) Golfers Unite continued: Create the ERD to capture the information requirements shown in the TOURNAMENT RESULTS table. Use your normalized relations, and all appropriate data modeling techniques and guidelines. This can be done on paper or you can paste your Visio/LucidCharts diagram. (20 points)