Question

Draw an ER diagram and write a database design outline for the following prompt:

You run a coaching service to help high school students prepare for the SAT exam. You have a staff of coaches, each of which has an employee ID, an hourly rate (such as $20 per hour), and personal information consisting of their first name, last name, middle name/initial, address information, phone number, mobile phone number, and e-mail address. For each high school student, you want to keep similar personal information and a student ID number, date of birth, and expected date of graduation from high school. Coaching takes place in sessions, to each of which you assign a unique ID number. Each session consists of one coach and one student. In addition to being able to identify the coach and student involved in each session, you want to store its start date/time and end date/time.

Answer 1

Base on the given scenario, the entities are:

• Coach
• Student
• Session

Key points:

• Attributes of Coach : employee ID, hourly rate , first name, last name, address, phone number, e-mail .
• Attributes of Student: student ID, date of birth, and expected date of graduation.
• Coaching takes place in sessions, to each of which you assign a unique ID number.
• Session attributes: Session_ID,start_time,end_time.
• Each session consists of one coach and one student. (Session entity consists of one coredinality with both coach and student).
• Coach may take many sessions.
• Student also can attend many sessions.

Now,the ERD is as follows:

ER diagram:
ER model is to represent the strucre of data by using entities and attributes and their reklations. ER diagram considers entities in real world and relating with them. It is a graphical view.

Relational diagram:
In Relational database model,the entities and relations are represented by using tables. Each table contains records and attribues.

• If we have multi-valued attribute, make that attribute as new entity.
• In 1:1 Relationships, we are not considering relation as seperate entity. We add less priority table primary key as foreign keyb of priority table.
• In 1:M relationships, we are not considering relation as seperate entity. We simply use primary key of one table as forign key of another table.
• In M:M relationship, We need to create a relationship as seperate entity.

Now let us convert ER diagram to Relational tables:

1) Relationship between Coach and Session: As it contains 1:N relationship,we are adding primary key of 1 cordinal table as foreign key of N cordinal table.

Coach(Coach_ID,FirstName,Last_Name,Address,Phone,HourlyRate,Email)
Session(Session_ID,Start_Time,End_Time,Coach_ID)

2) Relationship between Student and Session: As it contains 1:N relationship,we are adding primary key of 1 cordinal table as foreign key of N cordinal table.

Student(Student_ID,FirstName,LastName,Address,Phone,DOB,ExpectedPassedOutDate)
Session(Session_ID,Start_Time,End_Time,Coach_ID,Student_ID)

So the final Tables are:

Coach(Coach_ID,FirstName,Last_Name,Address,Phone,HourlyRate,Email)
Student(Student_ID,FirstName,LastName,Address,Phone,DOB,ExpectedPassedOutDate)
Session(Session_ID,Start_Time,End_Time,Coach_ID,Student_ID)