ERD Assignment:

Please somebody do the ERD tables using one of the various online media tools available for them. You can email me the file or files or PDFS or whatever is the end result at my google email picklickwick thank you.

The due date listed is incorrect, it was extended until Tuesday at 3pm.

Thank you

ERD Assignment 1 Due Date: Thursday, February 21, 2019, 11:59pm Draw an ERD for each of the following situations. (If you believe that you need to make additional assumptions, clearly state them for each situation.) Submit the assignment on canvas in PDF format or hand in paper copy in class. ERD assignments may be computer generated or hand written. Please list any collaborators who you may have worked with on this assignment. Your final submission must represent your own work and not simply copied from someone else 1. Create an ERD that can be implemented for a medical clinic using the following business rules: A patient can make many appcintments with one or more dactors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as "unscheduled. if kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment a. b. C. d. With each visit, the patient's records are updated to provide a medical history e. Each patient visit creates a bill Each patient visit is billed by one doctor, and each doctor can bill many patients f. Each bill must be paid. However, a bill may be paid in many installments, and a payment g. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to h. If the bil is paid by an insurance company, the deductible is submitted to the patient for may cover more than one bill. an insurance company payment 2. Wally Los Gatos, owner of Wally's Wonderful World of Wallcoverings, has hired you as a consultant to design a database management system for his chain of three stores that sell wallpaper and accessories. He would like to track sales, customers, and employees. After an initial meeting with Wally, you have developed a list af business rules and specifications to begin the design of an E-R model: a. Customers place orders through a branch. b. Wally would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language. c. A customer may place many orders d. A customer does not always have to order through the same branch all he time e. Customers may have one or more accounts, although they may aiso have no accounts t. The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount, and Type A branch mary have many customers The following information about each branch needs to be recorded: Branch Numbe Location (Address, City, State, Zip Codel, and Square Footage A branch may sell all items or may only sell certain items Orders are composed of one or more items The following information about each order needs to be recorded: Order Date and h. i. j. k. Credit Authorization Status Items may be sold by one or more branches. Type (item 20) may consist of wallpaper (item 22) and borders (item 23) L m. We wish to record the following about each nem: Description, Color, Size, Pattern, and n. An item can be composed of multiple items; for example, a dieing room wallcovering set o. Wally employs 56 employees. He would ike to track the following information about employees: Name, Address (Street, City, State, Zp Code), Telephone, Date of Hire, Title, Salary, Ski, and Age. dependent as well as the age and relationship Based upon this information, draw an E-R model. Piease indicate any assumptions that p Each employee works in one and only one branch o. Each empioyee may have one or more depeedents We wish to record the name of the r. Employees can have one or more skills, 1. Some Tiny College staff employees are information technology (IT) personnel Some IT provide technology support for academie programs, some provide technology nfrastructure support, and scene provide support for both. IT personnel are not professors; they are required to take periodie training to retain their techaical expertise. Tinmy College tracks all IT personnel training by date, type, and results (completed versus not completed) Given that information, create the complete ERD that contains all primary keys, foreign keys, and main attributes 4. An art museum owns a large volume of works of art. Each work of art is described by an nem size; size is further compesed of height, width, and weght.A code (identifierl, title, type, and size; size is further composed of h work of art is developed by an artist, but the artist for soeme works is unknown. An artist is described by as artist ID (dentfier), name, date of birth, and date of death (which is null for stl lving artits, Only data about artists for works currently owned by the museum are kept in the database. At any point in time, a work of art is ether on display at the museum, held in storage ay from the museum as part of a travelling show, ce on loan to another gallery. If on display at aw the museum, a work of art is also described by ts location within the museum. A traveling show s described by a show D (idenzifier), the city in which the show is currently appearing and the start and end dates of the show, Mamy of the museum works may be part of a given show, and only active shows with at least one museum work of art need be represented in the database Finally, another gallery is described by a gallery ID (identifier, name, and city. The museum wants to retain a complete history of loaning a work of art to other galleries, and each time a work is loaned, the museum wants to know the date the work was loaned and the date it was returned As you develop the ERD for this problem, folow good data naming guidelines 5. Our friend Wally los Gatos (see Problem 21, realing that his wallcovering business had a few wrinkles in it, decided to pursue a law degree at night. After graduating he has tea med up with database system based upon the following set of business rules. It is in your best interest to perform a thorough analysis, to avoid needless ingacion Pleave create an ERD based upon the folowing set of rules a. An ATTORNEY is retained by one or more CUENTS for each CASE b. Atributes of ATTORNEY ace Attorney ID, Name, Addrest, Clty, State, 2p Code, Specialty may be more than onel, and Bar Imay be mone than onel C A CUENT may have more than one ATTORNEY for each CASE d. Atributes of CLIENT are Client ID, Name, Asdress, Chy. State, 2p Code, Telephone, and Daze of Birth e. A CLIENT may have more than one f Actributes of CASt are Case ID, Case Description, and Case Type & An ATTORNEY may have more than one CASE h. Each CASE is assigned to one and only one COURT Attrlbuces of COURT are Court ID, Court Name, City, Stae, and Zip Code Each COURT has one or more JUDGES assigned oo it. Aterioutes of JucGt are Judige 1Q, Name, and Years is Practice L Each JUDGE is assigned to exactly one court Alo using the tool you hive been bold so use in your course. Please stace any assumptions that you have made. Also, draw a data model for this situation beis to use w ERD Assignment 1 Due Date: Thursday, February 21, 2019, 11:59pm Draw an ERD for each of the following situations. (If you believe that you need to make additional assumptions, clearly state them for each situation.) Submit the assignment on canvas in PDF format or hand in paper copy in class. ERD assignments may be computer generated or hand written. Please list any collaborators who you may have worked with on this assignment. Your final submission must represent your own work and not simply copied from someone else 1. Create an ERD that can be implemented for a medical clinic using the following business rules: A patient can make many appcintments with one or more dactors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as "unscheduled. if kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment a. b. C. d. With each visit, the patient's records are updated to provide a medical history e. Each patient visit creates a bill Each patient visit is billed by one doctor, and each doctor can bill many patients f. Each bill must be paid. However, a bill may be paid in many installments, and a payment g. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to h. If the bil is paid by an insurance company, the deductible is submitted to the patient for may cover more than one bill. an insurance company payment 2. Wally Los Gatos, owner of Wally's Wonderful World of Wallcoverings, has hired you as a consultant to design a database management system for his chain of three stores that sell wallpaper and accessories. He would like to track sales, customers, and employees. After an initial meeting with Wally, you have developed a list af business rules and specifications to begin the design of an E-R model: a. Customers place orders through a branch. b. Wally would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language. c. A customer may place many orders d. A customer does not always have to order through the same branch all he time e. Customers may have one or more accounts, although they may aiso have no accounts t. The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount, and Type A branch mary have many customers The following information about each branch needs to be recorded: Branch Numbe Location (Address, City, State, Zip Codel, and Square Footage A branch may sell all items or may only sell certain items Orders are composed of one or more items The following information about each order needs to be recorded: Order Date and h. i. j. k. Credit Authorization Status Items may be sold by one or more branches. Type (item 20) may consist of wallpaper (item 22) and borders (item 23) L m. We wish to record the following about each nem: Description, Color, Size, Pattern, and n. An item can be composed of multiple items; for example, a dieing room wallcovering set o. Wally employs 56 employees. He would ike to track the following information about employees: Name, Address (Street, City, State, Zp Code), Telephone, Date of Hire, Title, Salary, Ski, and Age. dependent as well as the age and relationship Based upon this information, draw an E-R model. Piease indicate any assumptions that p Each employee works in one and only one branch o. Each empioyee may have one or more depeedents We wish to record the name of the r. Employees can have one or more skills, 1. Some Tiny College staff employees are information technology (IT) personnel Some IT provide technology support for academie programs, some provide technology nfrastructure support, and scene provide support for both. IT personnel are not professors; they are required to take periodie training to retain their techaical expertise. Tinmy College tracks all IT personnel training by date, type, and results (completed versus not completed) Given that information, create the complete ERD that contains all primary keys, foreign keys, and main attributes 4. An art museum owns a large volume of works of art. Each work of art is described by an nem size; size is further compesed of height, width, and weght.A code (identifierl, title, type, and size; size is further composed of h work of art is developed by an artist, but the artist for soeme works is unknown. An artist is described by as artist ID (dentfier), name, date of birth, and date of death (which is null for stl lving artits, Only data about artists for works currently owned by the museum are kept in the database. At any point in time, a work of art is ether on display at the museum, held in storage ay from the museum as part of a travelling show, ce on loan to another gallery. If on display at aw the museum, a work of art is also described by ts location within the museum. A traveling show s described by a show D (idenzifier), the city in which the show is currently appearing and the start and end dates of the show, Mamy of the museum works may be part of a given show, and only active shows with at least one museum work of art need be represented in the database Finally, another gallery is described by a gallery ID (identifier, name, and city. The museum wants to retain a complete history of loaning a work of art to other galleries, and each time a work is loaned, the museum wants to know the date the work was loaned and the date it was returned As you develop the ERD for this problem, folow good data naming guidelines 5. Our friend Wally los Gatos (see Problem 21, realing that his wallcovering business had a few wrinkles in it, decided to pursue a law degree at night. After graduating he has tea med up with database system based upon the following set of business rules. It is in your best interest to perform a thorough analysis, to avoid needless ingacion Pleave create an ERD based upon the folowing set of rules a. An ATTORNEY is retained by one or more CUENTS for each CASE b. Atributes of ATTORNEY ace Attorney ID, Name, Addrest, Clty, State, 2p Code, Specialty may be more than onel, and Bar Imay be mone than onel C A CUENT may have more than one ATTORNEY for each CASE d. Atributes of CLIENT are Client ID, Name, Asdress, Chy. State, 2p Code, Telephone, and Daze of Birth e. A CLIENT may have more than one f Actributes of CASt are Case ID, Case Description, and Case Type & An ATTORNEY may have more than one CASE h. Each CASE is assigned to one and only one COURT Attrlbuces of COURT are Court ID, Court Name, City, Stae, and Zip Code Each COURT has one or more JUDGES assigned oo it. Aterioutes of JucGt are Judige 1Q, Name, and Years is Practice L Each JUDGE is assigned to exactly one court Alo using the tool you hive been bold so use in your course. Please stace any assumptions that you have made. Also, draw a data model for this situation beis to use w