Entity Relationship Modeling Examples - Learning MySQL [Book]
What is the ER Model? The ER or (Entity Relational Model) is a high-level conceptual data model diagram. Entity-Relation model is based on. ER Diagram Representation - Learn DBMS in simple and easy steps starting from Any object, for example, entities, attributes of an entity, relationship sets, and. Learn about entity relationship diagram symbols. Read the ER diagram tutorial. See ERD ER DIAGRAM TIPS; ENTITY RELATIONSHIP DIAGRAM EXAMPLES.
An album contains one or more tracks Artists, albums, and tracks each have a name. Each track is on exactly one album. Each track has a time length, measured in seconds. When a track is played, the date and time the playback began to the nearest second should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played. Conversely, each play is associated with one track, a track is on one album, and an album is by one artist.
The attributes are straightforward: The track entity has a time attribute to store the duration, and the played entity has a timestamp to store when the track was played.
If you wanted to use the music database in practice, then you might consider adding the following features: Support for compilations or various-artists albums, where each track may be by a different artist and may then have its own associated album-like details such as a recording date and time.
Under this model, the album would be a strong entity, with many-to-many relationships between artists and albums. Playlists, a user-controlled collection of tracks.
For example, you might create a playlist of your favorite tracks from an artist. Track ratings, to record your opinion on how good a track is. Source details, such as when you bought an album, what media it came on, how much you paid, and so on.
Album details, such as when and where it was recorded, the producer and label, the band members or sidemen who played on the album, and even its artwork. Smarter track management, such as modeling that allows the same track to appear on many albums.
The University Database The university database stores details about university students, courses, the semester a student took a particular course and his mark and grade if he completed itand what degree program each student is enrolled in. We explain the requirements next and discuss their shortcomings at the end of this section. 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.
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. 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.
ER Diagram Representation
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.
ER Diagram Tutorial in DBMS (with Example)
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. Derived attribute This type of attribute does not include in the physical database. However, their values are derived from other attributes present in the database.
Data Modeling and Entity Relationship Diagram (ERD)
For example, age should not be stored directly. Instead, it should be derived from the DOB of that employee. Multivalued attribute Multivalued attributes can have more than one values. For example, a student can have more than one mobile number, email address, etc.