Description
All the instructions should be in the attached document. If you have any questions at all please let me know
Unformatted Attachment Preview
Homework Assignment #3
Entity Relationship Diagram (ERD)
Please submit your homework on the day it is due using the homework submission tool on the
course website. Please do not email the document to the instructor.
Your homework document should be a PDF or MS Word document. Your homework must be
your own work, in your own words. The use of material from other sources, even when it is properly
quoted and cited, should be limited. There is a penalty for late homework.
Please remember to put your name on your ERD Diagram. I know it seems foolish to
mention this but a few students always forgot to do it.
ERD ASSIGNMENT – PATIENT APPOINTMENT AND BILLING SYSTEM
In this question, we ask you to develop an ERD (using Lucidchart or another drawing program) for a very
simple patient appointment and billing system for a large dental office with a dozen dentists. Your ERD
should include the Entities, the most important attributes within each entity, the cardinalities and labels
between entities, and a description of the various components and relationships that make up your ERD.
This descriptive material can be part of the diagram itself, using the yellow post-it note style text boxes
that can be added to the ERD, or a separate part of the document. (We recommend that you use
Lucidcharts to create this ERD, but of course, it is possible to use PowerPoint or another drawing
program to create this diagram.)
For simplicity, you should not list more than six attributes per entity, and you can combine common
attributes. For example, instead of having two attributes to hold the patient’s name (First_Name and
Last_Name) you could have a single attribute called Patient_Name.
You should mark the Primary Key in an Entity by adding ‘**PK’ next to the attribute name on the ERD.
For example, an attribute called Patient_ID, or something similarly labeled, is clearly the primary key in
the Patient entity table, and it would be labeled as Patient_ID**PK. You should also mark any
necessary Foreign Keys you identify as ‘**FK’ next to the attribute’s name. Your ERD should have four
or five entities (tables) depending on your design. If you have more than five entities (tables) your
design is unnecessarily complex. Note that some attributes, such as an address or date of birth in a
Patient table, will not be identified as either a PK or a FK since they are not a key of either type.
Some of the business rules include:
– A dentist can be scheduled for many different appointments, but may not have any appointments
scheduled at all.
– A patient can be scheduled for many different appointments, but may not have any appointments
scheduled at all.
– Each individual appointment is scheduled between exactly one patient and one dentist.
– An appointment must generate exactly 1 bill, and a bill is generated by only 1 appointment.
– For the purpose of this ERD you do not need to track the payments or any outstanding balance on the
bills, but you need to be able to create the bill from the details in the ERD.
The ERD you design should be able to help answer important questions such as:
– Which patients have an appointment on a specific date?
– Which dentists have an appointment with a certain patient?
– How many appointments occurred on a certain day?
– Are there any dentists that have not seen any patients in the past week?
– Which patient has had more than five appointments in the past month?
– What bill got generated for a specific appointment?
Page 1 of 2
Note that we are not asking you to write the SQL statements that would handle these queries, since some
of them are quite complex, but your ERD should include the information needed to answer them.
You should create your ERD using Lucidchart (or another drawing program), and then create a JPG or
PNG of your diagram. (A PNG is another type of picture format.) You can then import this JPG or PNG
into a MS Word document for your assignment and then upload that document to the course website.
Remember, you need to include a description of the various components and the relationships of
the ERD, and the level of detail in your description should allow someone who has not read this
homework assignment to understand what your system is designed to do. Your descriptive material can
be part of the diagram itself (using post-it note style text boxes), or part of the document that goes along
with your diagram.
[10 points total for this assignment]
Page 2 of 2
Purchase answer to see full
attachment
