Driving School – Entity Relationship Diagram (ERD)

learn-2-driveLearn2Drive is a driving school that consists of 5 driving instructors. They would like a new IT system that will be used to store information about their instructors, learners, lessons and vehicles used by the instructors.

When creating such a database driven system, one of the key tasks is to identify what data will need to be stored in the database to then design an ERD: Entity Relationship Diagram.

To do so, a business analyst will first collect lots of information about how the business works and about their requirements. This information will be used to:

  1. Identify the main entities for which the system will store data,
  2. For each entity, identify the pieces of data (fields) that will need to be stored on the system,
  3. Identify whether there are relationships between the different entities,
  4. Use primary keys, foreign keys and link tables when necessary to implement these relationships.

You can find out more about these key relational database concepts on this page.

road-signs-banner

Scenario

Here is the main information that should help you design the first draft of the ERD for the Learn2Drive new IT system:

  • L2D is a driving school consisting of 5 instructors. Each instructor gives driving lessons in their own car which is insured by the school.
  • Students can book lessons with any of the 5 instructors.
  • Most students will need around 30 lessons before passing their driving license.

Use our online ERD tool to design the Entity Relationship Diagram for this driving school. Note that you have not been given any information about the pieces of information to store about learners, instructors, cars and lessons so you will have to decide of the most relevant fields to include on your ERD.

Design Your ERD Online

Solution

You can view/edit our ERD for this scenario:
View Solution!

Extension Task

The driving school would like to use their system to record the attendance of learners to theory lessons. These theory lessons will take place every Monday and Wednesday at the driving school’s main office which can accommodate up to 20 students per lesson. Each lesson will be delivered to the group of students by one instructor.

Your task is to update your ERD to take into consideration these new requirements.

unlock-access

Solution...

The solution for this challenge is available to full members!
Find out how to become a member:
➤ Members' Area

Did you like this challenge?

Click on a star to rate it!

Average rating 4.1 / 5. Vote count: 36

No votes so far! Be the first to rate this post.

As you found this challenge interesting...

Follow us on social media!

Tagged with: , ,