Entity relationship model database definition and examples

Entity–relationship model - Wikipedia

entity relationship model database definition and examples

Learn about entity relationship diagram symbols. Read the ER Here's an example of a very basic database structure generated from data. Basic database . To fully utilize ER Diagram in database engineering guarantee you to produce high The ER diagram example below shows an entity with some attributes in it. An entity-relationship (ER) diagram is a graphical representation of entities and their For example, in a database of employees, each employee name (A) is.

Why use ER Model? Now you may think why use ER modeling when we can simply create the database and all of its objects without ER modeling? One of the challenges faced when designing database is the fact that designers, developers and end-users tend to view data and its usage differently.

If this situation is left unchecked, we can end up producing a database system that does not meet the requirements of the users. ER models are examples of such tools. ER diagrams also increase user productivity as they can be easily translated into relational tables.

entity relationship model database definition and examples

We will using this database for all hand-on in the remainder of this tutorials MyFlix is a business entity that rents out movies to its members.

MyFlix has been storing its records manually. The management now wants to move to a DBMS Let's look at the steps to develop EER diagram for this database- Identify the entities and determine the relationships that exist among them.

Entity–relationship model

Each entity, attribute and relationship, should have appropriate names that can be easily understood by the non-technical people as well. Relationships should not be connected directly to eachother. Relationships should connect entities.

  • Navigation menu
  • What is an ER diagram (ERD)?
  • When to draw ER Diagrams?

Each attribute in a given entity should have a unique name. Entities in the "MyFlix" library The entities to be included in our ER diagram are; Members - this entity will hold member information. Movies - this entity will hold information regarding movies Categories - this entity will hold information that places movies into different categories such as "Drama", "Action", and "Epic" etc.

Movie Rentals - this entity will hold information that about movies rented out to members.

What is Entity Relationship Diagram (ERD)?

Payments - this entity will hold information about the payments made by members. Defining the relationships among entities Members and movies The following holds true regarding the interactions between the two entities.

A member can rent a more than movie in a given period. A movie can be rented by more than one member in a given period. From the above scenario, we can see that the nature of the relationship is many-to-many.

Relational databases do not support many-to-many relationships. We need to introduce a junction entity. This is the role that the MovieRentals entity plays. It has a one-to-many relationship with the members table and another one-to-many relationship with movies table.

What is ER Modeling? Learn with Example

Movies and categories entities The following holds true about movies and categories. A movie can only belong to one category but a category can have more than one movie. The university offers one or more programs. A program is made up of one or more courses.

A student must enroll in a program. A student takes the courses that are part of her program. A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced.

A course has a name, a course identifier, a credit point value, and the year it commenced. Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled. When he finishes the course, a grade such as A or B and a mark such as 60 percent are recorded.

Each course in a program is sequenced into a year for example, year 1 and a semester for example, semester 1. Although it is compact, the diagram uses some advanced features, including relationships that have attributes and two many-to-many relationships.

What is ER Modeling? Learn with Example

The ER diagram of the university database In our design: Each student must be enrolled in a program, so the Student entity participates totally in the many-to-one EnrollsIn relationship with Program. A program can exist without having any enrolled students, so it participates partially in this relationship.

entity relationship model database definition and examples

As a weak entity, Course participates totally in the many-to-one identifying relationship with its owning Program. This relationship has Year and Semester attributes that identify its sequence position. Student and Course are related through the many-to-many Attempts relationships; a course can exist without a student, and a student can be enrolled without attempting any courses, so the participation is not total.

When a student attempts a course, there are attributes to capture the Year and Semester, and the Mark and Grade. For a real university, many more aspects would need to be captured by the database.

The airline has one or more airplanes. An airplane has a model number, a unique registration number, and the capacity to take one or more passengers.

An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time. Each flight is carried out by a single airplane.

entity relationship model database definition and examples

A passenger has given names, a surname, and a unique email address. A passenger can book a seat on a flight. The ER diagram of the flight database An Airplane is uniquely identified by its RegistrationNumber, so we use this as the primary key. A Flight is uniquely identified by its FlightNumber, so we use the flight number as the primary key. The departure and destination airports are captured in the From and To attributes, and we have separate attributes for the departure and arrival date and time.

Because no two passengers will share an email address, we can use the EmailAddress as the primary key for the Passenger entity. An airplane can be involved in any number of flights, while each flight uses exactly one airplane, so the Flies relationship between the Airplane and Flight relationships has cardinality 1: N; because a flight cannot exist without an airplane, the Flight entity participates totally in this relationship. A passenger can book any number of flights, while a flight can be booked by any number of passengers.

N Books relationship between the Passenger and Flight relationship, but considering the issue more carefully shows that there is a hidden entity here: We capture this by creating the intermediate entity Booking and 1: N relationships between it and the Passenger and Flight entities. Identifying such entities allows us to get a better picture of the requirements.

entity relationship model database definition and examples

There are no requirements to capture passenger details such as age, gender, or frequent-flier number. If, instead, we assumed that the capacity is determined by the model number, we would have created a new AirplaneModel entity with the attributes ModelNumber and Capacity. The Airplane entity would then not have a Capacity attribute. Airlines typically use a flight number to identify a given flight path and schedule, and they specify the date of the flight independently of the flight number.