PART 2 A. Entity Relationship Diagram (using crows foot notation) B. Document Relationships between entities using...

Transcribed Image Text:

PART 2 A. Entity Relationship Diagram (using crows foot notation) B. Document Relationships between entities using format below Relationship Parent Child Type Cardinality Maximum Cardinality Minimum Cardinality (1:1, 1:N or N:M) | (O-M, 0-0, M-M or M-O) C. Document Actions to enforce minimum cardinality using format below Actions for required parent in [Table 1] - [Table 2] [Table 1] is required parent of [Table 2] Action on [Table 1] Action on [Table 2] Actions Insert Modify key or Foreign key Delete D. Create a Data Dictionary based on ERD and Normalization solution. Provide SQL server data types for each of the columns in every table you have identified. Identify the primary and foreign keys in each table: Name of the Entity Attributes Null/Not Null or other SQL Server Data Type PK/FK/AltK constraints Patient Number 1736 First སྐ Harriet Maness Address 2 嚣 9368 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 10/7/2020 Braces $3,500.00 Impression Felicia 150000 Hillcrest Street Material Hunter 1736 Harriet Maness 1736 Harriet Maness 1736 Harriet Maness 2649 Jess Fraga 9368 Hillcrest Street 9368 Hillcrest Street 9368 Hillcrest Street 30 Polygon St. Bucklin MO 64631 4177716403 237763124 6/16/1954 F 10/7/2020 Braces $3,500.00 Impression Felicia Trays Hunter 150000 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 8/25/2022 Cleaning $200.00 Fluoride Felicia Hunter 150000 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 8/25/2022 Cleaning $200.00 Cleaners Felicia Hunter 150000 Oronogo MO 64855 8168921621 448299828 10/11/2003 9/15/2021 Cleaning $200.00 Fluoride Felicia 150000 Hunter 2649 Jess Fraga 30 Oronogo MO 64855 8168921621 Polygon St. 448299828 10/11/2003 F 9/15/2021 Cleaning $200.00 Cleaners Felicia 150000 Hunter 2649 Jess Fraga 30 Oronogo MO 64855 8168921621 448299828 10/11/2003 F 3/9/2021 Polygon St. Restoration with local anesthesia $210.00 Numbing Agent Felicia 150000 Hunter 2649 Jess Fraga 30 Polygon St. Oronogo MO 64855 8168921621 448299828 10/11/2003 F 3/9/2021 Restoration with local anesthesia $210.00 Amalgam Felicia Hunter 150000 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 7/23/2020 Braces $3,500.00 Impression Keith Material Reynolds 160000 Hollow Dr. 2657 Arcelia Ibarra 35 Scotts MO 65756 8168322641 546754766 12/6/1991 F 7/23/2020 Braces Smoky City $3,500.00 Impression Keith Trays Reynolds 160000 Hollow Dr. Gender Visit Date Procedure or Service Done Charge Materials** Dentist Name Dentist Salary $ 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 1/31/2019 Cleaning $200.00 Fluoride Keith Reynolds 160000 Hollow Dr. 2657 Arcelia Ibarra 35 Scotts MO 65756 8168322641 546754766 12/6/1991 F 1/31/2019 Cleaning $200.00 Cleaners Smoky Hollow City Keith Reynolds 160000 Dr. 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 9/9/2018 Crowns and Caps $1,200.00 Crowns Keith 160000 and Caps Reynolds Hollow Dr. 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 9/9/2018 Crowns and Caps $1,200.00 Numbing Keith Agent Reynolds 160000 Hollow Dr. 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 3/1/2020 Cleaning $200.00 Fluoride Rita Jensen 155000 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 3/1/2020 Cleaning $200.00 Cleaners Rita Jensen 155000 2834 Kelli Holiman 128 Golden Saginaw MO 64864 3142802397 390395751 5/4/1979 F 5/22/2021 Cleaning $200.00 Fluoride Rita Jensen 155000 Star Ave. 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 5/22/2021 Cleaning $200.00 Cleaners Rita Jensen 155000 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 11/24/2019 Dentures $950.00 Impression Material Rita Jensen 155000 2834 Kelli Holiman 2834 Kelli Holiman 128 Golden Star Ave. 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 11/24/2019 Dentures $950.00 Impression Trays Rita Jensen 155000 Saginaw MO 64864 3142802397 390395751 5/4/1979 F 6/10/2022 Restoration $185.00 Amalgam Rita Jensen 155000 3271 lona Piatt 8991 Galvin St. lonia MO 65335 8169433490 271523900 8/24/2004 F 5/18/2016 Cleaning $200.00 Fluoride Bethany 175000 Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 5/18/2016 Cleaning $200.00 Galvin Cleaners Bethany 175000 Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/18/2018 Galvin Crowns and $1,200.00 Caps Crowns and Caps Bethany 175000 Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/18/2018 Galvin Crowns and Caps $1,200.00 Numbing Bethany 175000 Agent Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 10/13/2019 Extraction Galvin St. $263.00 Numbing Bethany 175000 Agent Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/22/2020 Galvin St. Restoration with local anesthesia $210.00 Numbing Bethany 175000 Agent Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/22/2020 Galvin St. Restoration with local anesthesia $210.00 Amalgam Bethany 175000 Tucker 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3695 Loni Asmussen 3695 Loni Asmussen 18 Autumn Street 18 Autumn Street 18 Autumn Street 18 Autumn Street 18 Autumn Street 6 Swanson St. 6 Swanson St. Bernie MO 63822 5735989702 229412809 12/28/1993 M 2/9/2018 Cleaning $200.00 Fluoride Bethany 175000 Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 2/9/2018 Cleaning $200.00 Cleaners Bethany 175000 Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 11/27/2019 Dentures $950.00 Impression Bethany 175000 Material Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 11/27/2019 Dentures $950.00 Impression Bethany 175000 Trays Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 8/29/2021 Restoration $185.00 Amalgam Bethany 175000 Tucker Eminence MO 65466 8168322641 807762312 12/6/1991 F 4/27/2021 Restoration with local anesthesia $210.00 Numbing Felicia Agent Hunter 165000 Eminence MO 65466 8168322641 807762312 12/6/1991 F 4/27/2021 Restoration with local anesthesia $210.00 Amalgam Felicia Hunter 165000 PART 2 A. Entity Relationship Diagram (using crows foot notation) B. Document Relationships between entities using format below Relationship Parent Child Type Cardinality Maximum Cardinality Minimum Cardinality (1:1, 1:N or N:M) | (O-M, 0-0, M-M or M-O) C. Document Actions to enforce minimum cardinality using format below Actions for required parent in [Table 1] - [Table 2] [Table 1] is required parent of [Table 2] Action on [Table 1] Action on [Table 2] Actions Insert Modify key or Foreign key Delete D. Create a Data Dictionary based on ERD and Normalization solution. Provide SQL server data types for each of the columns in every table you have identified. Identify the primary and foreign keys in each table: Name of the Entity Attributes Null/Not Null or other SQL Server Data Type PK/FK/AltK constraints Patient Number 1736 First སྐ Harriet Maness Address 2 嚣 9368 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 10/7/2020 Braces $3,500.00 Impression Felicia 150000 Hillcrest Street Material Hunter 1736 Harriet Maness 1736 Harriet Maness 1736 Harriet Maness 2649 Jess Fraga 9368 Hillcrest Street 9368 Hillcrest Street 9368 Hillcrest Street 30 Polygon St. Bucklin MO 64631 4177716403 237763124 6/16/1954 F 10/7/2020 Braces $3,500.00 Impression Felicia Trays Hunter 150000 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 8/25/2022 Cleaning $200.00 Fluoride Felicia Hunter 150000 Bucklin MO 64631 4177716403 237763124 6/16/1954 F 8/25/2022 Cleaning $200.00 Cleaners Felicia Hunter 150000 Oronogo MO 64855 8168921621 448299828 10/11/2003 9/15/2021 Cleaning $200.00 Fluoride Felicia 150000 Hunter 2649 Jess Fraga 30 Oronogo MO 64855 8168921621 Polygon St. 448299828 10/11/2003 F 9/15/2021 Cleaning $200.00 Cleaners Felicia 150000 Hunter 2649 Jess Fraga 30 Oronogo MO 64855 8168921621 448299828 10/11/2003 F 3/9/2021 Polygon St. Restoration with local anesthesia $210.00 Numbing Agent Felicia 150000 Hunter 2649 Jess Fraga 30 Polygon St. Oronogo MO 64855 8168921621 448299828 10/11/2003 F 3/9/2021 Restoration with local anesthesia $210.00 Amalgam Felicia Hunter 150000 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 7/23/2020 Braces $3,500.00 Impression Keith Material Reynolds 160000 Hollow Dr. 2657 Arcelia Ibarra 35 Scotts MO 65756 8168322641 546754766 12/6/1991 F 7/23/2020 Braces Smoky City $3,500.00 Impression Keith Trays Reynolds 160000 Hollow Dr. Gender Visit Date Procedure or Service Done Charge Materials** Dentist Name Dentist Salary $ 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 1/31/2019 Cleaning $200.00 Fluoride Keith Reynolds 160000 Hollow Dr. 2657 Arcelia Ibarra 35 Scotts MO 65756 8168322641 546754766 12/6/1991 F 1/31/2019 Cleaning $200.00 Cleaners Smoky Hollow City Keith Reynolds 160000 Dr. 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 9/9/2018 Crowns and Caps $1,200.00 Crowns Keith 160000 and Caps Reynolds Hollow Dr. 2657 Arcelia Ibarra 35 Smoky Scotts City MO 65756 8168322641 546754766 12/6/1991 F 9/9/2018 Crowns and Caps $1,200.00 Numbing Keith Agent Reynolds 160000 Hollow Dr. 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 3/1/2020 Cleaning $200.00 Fluoride Rita Jensen 155000 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 3/1/2020 Cleaning $200.00 Cleaners Rita Jensen 155000 2834 Kelli Holiman 128 Golden Saginaw MO 64864 3142802397 390395751 5/4/1979 F 5/22/2021 Cleaning $200.00 Fluoride Rita Jensen 155000 Star Ave. 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 5/22/2021 Cleaning $200.00 Cleaners Rita Jensen 155000 2834 Kelli Holiman 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 11/24/2019 Dentures $950.00 Impression Material Rita Jensen 155000 2834 Kelli Holiman 2834 Kelli Holiman 128 Golden Star Ave. 128 Golden Star Ave. Saginaw MO 64864 3142802397 390395751 5/4/1979 F 11/24/2019 Dentures $950.00 Impression Trays Rita Jensen 155000 Saginaw MO 64864 3142802397 390395751 5/4/1979 F 6/10/2022 Restoration $185.00 Amalgam Rita Jensen 155000 3271 lona Piatt 8991 Galvin St. lonia MO 65335 8169433490 271523900 8/24/2004 F 5/18/2016 Cleaning $200.00 Fluoride Bethany 175000 Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 5/18/2016 Cleaning $200.00 Galvin Cleaners Bethany 175000 Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/18/2018 Galvin Crowns and $1,200.00 Caps Crowns and Caps Bethany 175000 Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/18/2018 Galvin Crowns and Caps $1,200.00 Numbing Bethany 175000 Agent Tucker St. 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 10/13/2019 Extraction Galvin St. $263.00 Numbing Bethany 175000 Agent Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/22/2020 Galvin St. Restoration with local anesthesia $210.00 Numbing Bethany 175000 Agent Tucker 3271 lona Piatt 8991 lonia MO 65335 8169433490 271523900 8/24/2004 F 7/22/2020 Galvin St. Restoration with local anesthesia $210.00 Amalgam Bethany 175000 Tucker 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3682 Bert Llewellyn 3695 Loni Asmussen 3695 Loni Asmussen 18 Autumn Street 18 Autumn Street 18 Autumn Street 18 Autumn Street 18 Autumn Street 6 Swanson St. 6 Swanson St. Bernie MO 63822 5735989702 229412809 12/28/1993 M 2/9/2018 Cleaning $200.00 Fluoride Bethany 175000 Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 2/9/2018 Cleaning $200.00 Cleaners Bethany 175000 Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 11/27/2019 Dentures $950.00 Impression Bethany 175000 Material Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 11/27/2019 Dentures $950.00 Impression Bethany 175000 Trays Tucker Bernie MO 63822 5735989702 229412809 12/28/1993 M 8/29/2021 Restoration $185.00 Amalgam Bethany 175000 Tucker Eminence MO 65466 8168322641 807762312 12/6/1991 F 4/27/2021 Restoration with local anesthesia $210.00 Numbing Felicia Agent Hunter 165000 Eminence MO 65466 8168322641 807762312 12/6/1991 F 4/27/2021 Restoration with local anesthesia $210.00 Amalgam Felicia Hunter 165000

Answer rating: 100% (QA)

First using the data that has been provided lets design the Entity Relationship Diagram ERD Lets now record the connections between the entities Conne... View the full answer