[SOLVED] Dbs211 lab 08 – entity relationship diagrams (database design erds)

Objective:
• Students work with a simple scenario to find the entities and the relationship between
entities.
• Students learn to define the relationship cardinality and optionality.• Students learn to design a simple database using an ERD model.
• Students learn how to fix many-to-many relationships in an ERD.Submission:
Students should submit a Word document with ERD and student information
Using the draw.io website, save your lab file as a jpeg file. You need to submit a single Word file
for this lab containing the rules and ERD. Both names, studentID’s and the current date must be
in the output document (make a box on the page and put the info in it). Submission without
names and the date will receive a mark of zero (0).Preface:
During this lab, you will need to create a relationship diagram. There are many free tools available to
you for this task. One example would be http://draw.io.
DBS211
EXAMPLE/ Sample output for LAB-8Consider the following business rules for a patient appointment system:
• A doctor can be scheduled for many appointments, but may not have any
scheduled at all.
• Each appointment is scheduled with exactly 1 doctor.• A patient can schedule 1 or more appointments.
• One appointment is scheduled with exactly 1 patient.
• An appointment must generate exactly 1 bill, a bill is generated by only 1
appointment.• One payment is applied to exactly 1 bill, and 1 bill can be paid off over time by
several payments.
• A bill can be outstanding, having nothing yet paid on it at all.
• One patient can make many payments, but a single payment is made by only 1
patient.• Some patients are insured by an insurance company. If they are insured, they
can only carry insurance with one company. An insurance company can have
many patients carry their policies. For patients that carry insurance, the
insurance company will make payments, each single payment is made by exactly
1 insurance company.Given the above information, the following ERD can be drawn:
DBS211
EXAMPLE/ Sample output for ERDDraw the corresponding crow’s foot ERD for the following scenario. M:N relationships
should be broken into two 1:M relationships using a bridge entity. Label PK attributes
and FK when applicable.
Bridge/Junction Entity: The bridge/junction entity is used to eliminate the many-to-many relationships.This entity sits between the two entities with the many to many relationships and this composite entity shares the
primary keys from both tables.
Show all PK’s and FK’s with your solution.First you need to find all entities. Next, list the attributes for each entity. Next, you need
to find the relationship between each two entities if there is any relationship. Fix all
many-to-many relationships. Finally, make sure all PKs and FKs are properly defined.For all entities, list their attributes. For example, for the students, we need to store
student ID, student name and last name, email address, address, and phone number.
You do not need to define all possible attributes. Just determine some important
attributes.For each entity, you need to define a primary key. If there is a relationship between two
entities make sure you define the corresponding foreign key in the child table.
• Seneca College contains many departments.
• Each department has many programs, but every program belongs
to only one department.
• Every department has many professors.
• A professor can work for only one department.
• A program has many courses.
• A course can be a requirement of many programs.
• A professor can teach many courses. A course can also be taught
with many professors, via sections.
• Each section is only taught by one professor (ignore the summer
term)
• A program has many students studying in that program.
• A student can study multiple programs. However, a student has to
register in least in one program.
• A student may take many courses.
• A student, however, can be off from school and do not take any
courses.
• A course can be taken by many students.
• A new course may not be available yet so the course may not be
taken by any student.
• A student may have a program advisor. Having an advisor is
optional for students so some students may not have any advisor.
• An advisor can have one or more students assigned to them.