Question

Jefferson Dance Club ERD Problem Follow the Simple Procedure for ERD diagramming to design an ERD using the following user provided information. Remember the ERD will eventually turn into the design for a database from a top down view. Make sure you resolve any many to many conflicts. Label an identifier or primary key and all pertinent attributes given. If an attribute is not readily apparent from the user information, then make one up that makes sense for the particular entity. Therefore, all entities should end up with at least an identifier and another attribute that makes sense. Please use a drawing tool of some type to show the ERD diagram (Word, PowerPoint, and Visio all work well). I want you to get in the habit of drawing these with a computer instead of by hand, as this will be important as we get to more difficult examples. You may use any of the approaches shown by the book, though the ER-Model and Crow’s foot are the most common. Make sure you show entities, relationships, cardinality, and optionality. Jefferson Dance Club

The Jefferson Dance Club teaches social dancing and offers both private and group lessons. Jefferson charges $45 dollars per hour for a private lesson and $6 dollars per hour for a group lesson. Group lessons are limited to 10 customers. Private lessons are offered during the day and group lessons at night. Jefferson employs two types of instructors, F/T salaried instructors and P/T instructors. Instructors may teach both private and group lessons. In addition to the lessons, Jefferson sponsors two weekly dances of which instructors must attend one of the two. The instructors are paid a flat rate of $25 dollars for dance attendance. Customers must pay $5 to attend a dance. Jefferson does not track which customers attend a dance.

Answer 1

Step 1: List entities:

i. LessonType: This is the entity which will store the type of lesson i.e. private or group along with timing, fees etc details

ii. Lesson: This is the actual dance lesson which will be stored on the basis of Lesson_Id

iii. Instructor: These are the instructors who gives the lessons

iv. WeeklyDance: The weekly dances that are sponsored by Jefferson

v. Week: This wntity will just hold the week details as each week will have 2 dances named as WeeklyDance.

Step2: Add attributes to entities:

**Primary key is bold

LessionType(LessonType_ID, Charges, Allowed_max_customers, day_time)

Lesson(Lesson_id, Date, Note, TotalAttendees)

Instructor(Instructor_id, type, Salary)

Week(Week_id, start_date, end_date)

WeeklyDance(Dance_id, Date)

Step 3: List relationships :

1. Lesson has exactly one LessonType, but a LessonType e.g. Private lesson will have many Lessons for it. It is M:N relationship with full participation from both side as there must be one record for both sides.

2. Lesson has one Instructor and an instructor gives many lessons (may be some has not given any lesson). It is partial participating from instructor and full participation from lesson

3. WeeklyDance is attended by many Instructors and an Instructor attends atleast one to many dances.It has full participation from both entities.

4. Week has two weeklydances and a weekly dance belongs to one Week. It has full participation from Weeklydance and a partial participation from week as at start of the week, it may have no weekly dance.

Step 4: ERD for the given system

Below is the Crow’s foot notation ERD created in online tool diagrams<dot>net

Step 5: Notations:

ERD shows PK for all primary keys.

Full participation is shown as:

i. : For exactly one cardinality

ii. : For atleast one to many cardinality

Partial participation is shown as:

i. : For zero or many cardinality