ER Diagram Tutorial in DBMS (with Example)
This section explains the requirements for our three example databases— music , university, and flight —and shows you their Entity Relationship diagrams. Like the relational model, abstract data is converted to a logical data model when Each entity type can exist independently of another; for example, the entity. They mirror grammatical structure, with entities as nouns and relationships as verbs. ERD example. ER diagrams are related to data structure diagrams (DSDs) .
It can be identified uniquely by considering the primary key of another entity. For that, weak entity sets need to have participation. Let's learn more about a weak entity by comparing it with a Strong Entity Strong Entity Set Strong entity set always has a primary key. It does not have enough attributes to build a primary key. It is represented by a rectangle symbol.
It is represented by a double rectangle symbol.
It contains a Primary key represented by the underline symbol. It contains a Partial Key which is represented by a dashed underline symbol. The member of a strong entity set is called as dominant entity set. The member of a weak entity set called as a subordinate entity set. Primary Key is one of its attributes which helps to identify its member. In a weak entity set, it is a combination of primary key and partial key of the strong entity set. In the ER diagram the relationship between two strong entity set shown by using a diamond symbol.
The relationship between one strong and a weak entity set shown by using the double diamond symbol. The connecting line of the strong entity set with the relationship is single. The line connecting the weak entity set for identifying relationship is double. Attributes It is a single-valued property of either an entity-type or a relationship-type.
For example, a lecture might have attributes: An attribute is represented by an Ellipse Types of Attributes Description Simple attribute Simple attributes can't be divided any further. For example, a student's contact number. It is also called an atomic value.
Composite attribute It is possible to break down composite attribute. For example, a student's full name may be further divided into first name, second name, and last name. 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.
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. 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.
entity-relationship diagram (model)
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. 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.
Entity-Relationship (ER) Models — CSCI Database Systems 1 documentation
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. 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. For example, there is one IR flight on April 1, another on April 2, and so on. Different airplanes can operate on the same flight number over time; our model would need to be extended to support this.
The system also assumes that each leg of a multihop flight has a different FlightNumber. Our database also has limited ability to describe airports. If we were to model the airport as a separate entity, we could use the IATA-assigned airport code as the primary key. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.