In this example, all performers must be The entity relationship diagram above. The entity relationship (ER) data model has existed for over 35 years. For the rest of this chapter, we will use a sample database called the COMPANY Many to many relationships become associative tables with at least two foreign keys. CS Home Data Modeling ERD Entities Relationships Attributes Example A single entity instance in one entity class (parent) is related to multiple entity.
Consider the following requirements list: 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. 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.
Entity Relationship Modeling Examples - Learning MySQL [Book]
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.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
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.
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. The set of all possible values for an entity, such as all possible students, is the entity type. In an ER model, we diagram an entity type as a rectangle containing the type name, such as student see Figure 2. Definition An entity is a real-world item or concept that exists on its own.
The set of all possible values for an entity is the entity type. ER diagram notation for entity student Attribute Each entity has attributes, or particular properties that describe the entity.
For example, student Emanuel Vagas has properties of his own Student Identification number, name, and grade. A particular value of an attribute, such as 93 for the grade, is a value of the attribute. Most of the data in a database consists of values of attributes. The set of all possible values of an attribute, such as integers from 0 to for a grade, is the attribute domain.
In an ER model, an attribute name appears in an oval that has a line to the corresponding entity box, such as in Figure 3. Definition An attribute of an entity is a particular property that describes the entity.
The set of all possible values of an attribute is the attribute domain. Sometimes the value of an attribute is unknown or missing, and sometimes a value is not applicable. In such cases, the attribute can have the special value of null. For example, until the professor grades a laboratory assignment, the team grade is missing or null. Definition Null is the special attribute value that indicates an unknown or missing value. An attribute can be simple or composite.
A simple attribute, such as grade, is one component that is atomic. If we consider the name in two parts, last name and first name, then the name attribute is a composite.
A composite attribute, such as "Emanuel Vagas", has multiple components, such as "Emanuel" and "Vagas"; and each component is atomic or composite. We illustrate this composite nature in the ER model by branching off the component attributes, such as in Figure 4. Definition A simple attribute is one component that is atomic. A composite attribute has multiple components, each of which is atomic or composite. ER diagram notation for composite attribute domain, name Another way to classify attributes is either as single-valued or multi-valued.
For an entity an attribute, such as StudentGrade, usually holds exactly one value, such as 93, and thus is a single-valued attribute. However, two lab assistants might assist in a laboratory section. Consequently, the LabAssistant attribute for the entity LabSection is multi-valued.
A multi-valued attribute has more than one value for a particular entity. We illustrate this situation with a double oval around the lab assistant type, LabAssistant see Figure 5. Definition For a particular entity, an entity attribute that holds exactly one value is a single-valued attribute.
ER diagram notation for multi-valued attribute domain, LabAssistant A derived attribute can be obtained from other attributes or related entities. For example, the radius of a sphere can be determined from the circumference. We request the derived attribute with a dotted oval and line, such as in Figure 6. ER diagram notation for derived attribute, radius An attribute or set of attributes that uniquely identifies a particular entity is a key. However, to determine the class we need a composite key that consists of several attributes, such as catalogue number, section, semester, and year.
In the ER diagram of Figure 7we underline the composite key, class.