CIS 3650 Database Management Systems 2. Entity relationship diagram that documents the database design. The diagram should use the Fall 2022 Group Project appropriate crow's foot notation including minimum and maximum cardinalities, and identifiers for each relation. (See Part 2) 3. Data dictionary that includes these items: a- Description of tables, columns, their data type and other related information. b- Description of relationships (parent, child, cardinalities and minimum Group project information cardinality enforcement). (See Part 2 ) 1. For this project you are randomly assigned to a group. To access your group and communicate with 4. MS SQL Server database (Part 3). The deliverable for this will be your SQL statements used to your group members, use the "My Group" link on the main course menu. create the database, tables inside the database and populate tables with data, based on your work in 2. Since this is an online course, the expected form of communication among team members is virtual 1,2 , and 3 above. Name this file as your "[GroupNumber]CreateStatements.sql". communication/collaboration. You can meet in person if you can or you decide so as a group, but it is very important that all group members agree or are accommodated. Many students in this course are online only students who will not be able to meet in person on campus. 5. SQL queries for the set of queries given in Part 4 of this project. Name this file as "[GroupNumber]SelectStatements.sql". (Keep \#4 and \#5 as separate SQL files. Do not combine 3. Your submission will consist of one document and two script files. Except for the script files, all other answers must be submitted in a single document, Word or pdf. Answers to the questions should be typed and diagrams should be inserted in the document. 4. Do NOT submit hand-drawn diagrams. You can use any diagramming/design software or you can draw these diagrams in MS Word. Make sure you use notations discussed in this course. 5. One submission per group: designate one member to submit the deliverables on behalf of the group. 6. Peer evaluation: There will be a peer evaluation after the project is submitted. This will be taken them. into account when grading the project. Part 1 (25 Points) a. Is the data given to you a relation? If it is not a relation, please transform them into relations. b. Identify and list all functional dependencies/determinants (as well as partial dependencies and transitive dependencies) and provide your justifications or assumptions for the dependencies identified. c. What are the anomalies in the relation(s) created in step ' a '? List each of these anomalies by type and give specific examples from data for each type of anomaly. Project Details d. Examine the data provided. Identify and list any multivalued dependencies that you may find. How Please examine the data for a dentist's office given at the end of this document. many multivalued dependencies are present? Provide your justifications or assumptions for the dependencies identified. e. Normalize the data and bring all the tables you come up with into at least 3NF, and to BCNF and/or A dentist's office needs to store data about patients, their visits, procedures that were performed during 4NF where applicable. Identify what normal form is each table in for the tables you came up with in the visits, charges, and supplies used for the procedures. There are several dentists in this office. Each dentist sees several patients. A patient is always seen by the same dentist. There are several hundred patients (only a subset of that data is given to you here). Patients make many visits. your normalization solution. f. Identify and list all primary keys and foreign keys for all tables you have identified in your normalization solution. Your database should store information about the procedures performed during each visit and the charges for each of the procedures. Each procedure requires different supplies and the same type of supply is used for several procedures. At this time, the dentist does not require you to keep track of the patient payments. Part 2 (25 Points) A table with patient data is given. This is the data that the dentist office has, and your task is to create a database for the dentist office. Your task is to normalize the data. Then, model the data using an entity relationship diagram, a data dictionary for the database and create the database in Microsoft SQL a. Based on your normalization in Part 1, draw the entity relationship diagram using the Crow's foot notation. Other notations will NOT be accepted. b. Identify and include any cardinalities (both maximum and minimum) based on the information provided. You can make assumptions regarding the minimum or maximum cardinalities if that Server. Once you have created the database, answer the SQL queries specified in Part 4 . The project information is not included in this document. Make sure to include these assumptions in your submission. deliverables are: c. Specify primary keys and foreign keys. Depending on the tool you use or size of the diagram you may also include the data types documented in step 'e' below. d. Document relationships between each pair of the entities in your ERD, with their cardinalities and 1. Description of normalization process and final relations, also shown as normalized tables, including minimum cardinality enforcement using the tabular format we have used in exercises and the data in the new tables, with primary and foreign keys identified and assigned for each table assignments. (answers to questions in Part 1 below). e. Create a data dictionary based on your ERD and normalization solution. Provide the SQL server data types for each of the columns in every table you have identified. Identify the primary and foreign keys in each table. CIS 3650 Database Management Systems 2. Entity relationship diagram that documents the database design. The diagram should use the Fall 2022 Group Project appropriate crow's foot notation including minimum and maximum cardinalities, and identifiers for each relation. (See Part 2) 3. Data dictionary that includes these items: a- Description of tables, columns, their data type and other related information. b- Description of relationships (parent, child, cardinalities and minimum Group project information cardinality enforcement). (See Part 2 ) 1. For this project you are randomly assigned to a group. To access your group and communicate with 4. MS SQL Server database (Part 3). The deliverable for this will be your SQL statements used to your group members, use the "My Group" link on the main course menu. create the database, tables inside the database and populate tables with data, based on your work in 2. Since this is an online course, the expected form of communication among team members is virtual 1,2 , and 3 above. Name this file as your "[GroupNumber]CreateStatements.sql". communication/collaboration. You can meet in person if you can or you decide so as a group, but it is very important that all group members agree or are accommodated. Many students in this course are online only students who will not be able to meet in person on campus. 5. SQL queries for the set of queries given in Part 4 of this project. Name this file as "[GroupNumber]SelectStatements.sql". (Keep \#4 and \#5 as separate SQL files. Do not combine 3. Your submission will consist of one document and two script files. Except for the script files, all other answers must be submitted in a single document, Word or pdf. Answers to the questions should be typed and diagrams should be inserted in the document. 4. Do NOT submit hand-drawn diagrams. You can use any diagramming/design software or you can draw these diagrams in MS Word. Make sure you use notations discussed in this course. 5. One submission per group: designate one member to submit the deliverables on behalf of the group. 6. Peer evaluation: There will be a peer evaluation after the project is submitted. This will be taken them. into account when grading the project. Part 1 (25 Points) a. Is the data given to you a relation? If it is not a relation, please transform them into relations. b. Identify and list all functional dependencies/determinants (as well as partial dependencies and transitive dependencies) and provide your justifications or assumptions for the dependencies identified. c. What are the anomalies in the relation(s) created in step ' a '? List each of these anomalies by type and give specific examples from data for each type of anomaly. Project Details d. Examine the data provided. Identify and list any multivalued dependencies that you may find. How Please examine the data for a dentist's office given at the end of this document. many multivalued dependencies are present? Provide your justifications or assumptions for the dependencies identified. e. Normalize the data and bring all the tables you come up with into at least 3NF, and to BCNF and/or A dentist's office needs to store data about patients, their visits, procedures that were performed during 4NF where applicable. Identify what normal form is each table in for the tables you came up with in the visits, charges, and supplies used for the procedures. There are several dentists in this office. Each dentist sees several patients. A patient is always seen by the same dentist. There are several hundred patients (only a subset of that data is given to you here). Patients make many visits. your normalization solution. f. Identify and list all primary keys and foreign keys for all tables you have identified in your normalization solution. Your database should store information about the procedures performed during each visit and the charges for each of the procedures. Each procedure requires different supplies and the same type of supply is used for several procedures. At this time, the dentist does not require you to keep track of the patient payments. Part 2 (25 Points) A table with patient data is given. This is the data that the dentist office has, and your task is to create a database for the dentist office. Your task is to normalize the data. Then, model the data using an entity relationship diagram, a data dictionary for the database and create the database in Microsoft SQL a. Based on your normalization in Part 1, draw the entity relationship diagram using the Crow's foot notation. Other notations will NOT be accepted. b. Identify and include any cardinalities (both maximum and minimum) based on the information provided. You can make assumptions regarding the minimum or maximum cardinalities if that Server. Once you have created the database, answer the SQL queries specified in Part 4 . The project information is not included in this document. Make sure to include these assumptions in your submission. deliverables are: c. Specify primary keys and foreign keys. Depending on the tool you use or size of the diagram you may also include the data types documented in step 'e' below. d. Document relationships between each pair of the entities in your ERD, with their cardinalities and 1. Description of normalization process and final relations, also shown as normalized tables, including minimum cardinality enforcement using the tabular format we have used in exercises and the data in the new tables, with primary and foreign keys identified and assigned for each table assignments. (answers to questions in Part 1 below). e. Create a data dictionary based on your ERD and normalization solution. Provide the SQL server data types for each of the columns in every table you have identified. Identify the primary and foreign keys in each table