ER Model Basic Concepts
The Entity Relationship Model At a basic level, databases store information In the ER diagram, an entity set is represented by a rectangle containing the entity name. We could use the name attribute to distinguish between customers, but this driver's license numbers, and library card numbers are examples of unique . Let us discuss the differences between both, the Strong entity and weak entity The relationship between a weak and a strong entity is denoted by Identifying which is denoted by double diamond in the ER diagram. . DBMS · Hardware · Internet · Networking · Operating System · Programming · Software. The first stage is database design stage (Planning the Database Structure) Zach Freeman, Instructor, Masters Program in Computer Science at University of Chicago Here's a basic description combining Entity Relationship Diagram ( ERD).
When one instance of an entity is associated with many instances of another type of entity, it is called one to many 1: In a faculty, there can be multiple students, but a single student is in one faculty. Therefore, it is a one to many 1: When many instances of an entity are associated with many instances of another type of entity, it is called many to many m: A student can participate in multiple courses, and a single course can have multiple students.
So, it is a many to many relationship m: Furthermore, when an entity is related to itself, it is called a recursive relationship. When there are three entities connected, that relationship is known as a ternary relationship.
In the above ER diagram, the Lecture conducts Course. A single Lecturer can conduct multiple Courses, but one Course is conducted by one Lecturer. So, it is a one to many 1: A Student can follow multiple Courses, and a single Course can have multiple Students. Therefore, it is a many to many m: The Course has an Exam. The Exam is a weak entity, and it depends on the Course.
So, that relationship is a weak relationship. The collection of similar weak entities is called Weak Entity Set. The relationship between a weak entity and a strong entity is always denoted with an Identifying Relationship i. We have Loan as our weak entity, and as I said above for each loan there must be at least one borrower. You can observe in the loan entity set, no customer has borrowed a car loan and hence, it has totally vanished from loan entity set.
For the presence of car loan in loan entity set, it must have been borrowed by a customer. In this way, the weak Loan entity is dependent on the strong Customer entity. The second thing, we know is a weak entity does not have a primary key. In the Loan entity set, we have two exactly same entities i.
So, it will be determined that one home loan is borrowed by Customer Jhon and other by Customer Ruby. This is how the composed primary key of weak entity identify each entity in weak entity set.
Key Differences Between Strong Entity and Weak Entity The basic difference between strong entity and a weak entity is that the strong entity has a primary key whereas, a weak entity has the partial key which acts as a discriminator between the entities of a weak entity set.
A strong entity is denoted with a single rectangle and a weak entity is denoted with a double rectangle. The relationship between two strong entities is denoted with single diamond whereas, a relationship between a weak and a strong entity is denoted with double diamond called Identifying Relationship.
Get unlimited access to videos, live online training, learning paths, books, tutorials, and more. No credit card required The Entity Relationship Model At a basic level, databases store information about distinct objects, or entities, and the associations, or relationships, between these entities. For example, a university database might store information about students, courses, and enrollment.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
A student and a course are entities, while an enrollment is a relationship between a student and a course. Similarly, an inventory and sales database might store information about products, customers, and sales.
A product and a customer are entities, while a sale is a relationship between a customer and a product. A popular approach to conceptual design uses the Entity Relationship ER model, which helps transform the requirements into a formal description of the entities and relationships that appear in the database. In the ER diagram, an entity set is represented by a rectangle containing the entity name.
An entity set is represented by a named rectangle We typically use the database to store certain characteristics, or attributes, of the entities.
In a sales database, we could store the name, email address, postal address, and telephone number for each customer. Attributes describe the entity they belong to. An attribute may be formed from smaller parts; for example, a postal address is composed of a street number, city, ZIP code, and country. Some attributes can have multiple values for a given entity. For example, a customer could provide several telephone numbers, so the telephone number attribute is multivalued.
Attributes help distinguish one entity from other entities of the same type.
The Entity Relationship Model - Learning MySQL [Book]
We could use the name attribute to distinguish between customers, but this could be an inadequate solution because several customers could have identical names. To be able to tell them apart, we need an attribute or a minimal combination of attributes guaranteed to be unique to each individual customer. The identifying attribute or attributes form a key.
In our example, we can assume that no two customers have the same email address, so the email address can be the key. However, we need to think carefully about the implications of our choices. For example, if we decide to identify customers by their email address, it would be hard to allow a customer to have multiple email addresses.
Any applications we build to use this database might treat each email address as a separate person, and it might be hard to adapt everything to allow people to have multiple email addresses. Clearly, there may be several possible keys that could be used to identify an entity; we choose one of the alternative, or candidate, keys to be our main, or primary, key. You usually make this choice based on how confident you are that the attribute will be non-empty and unique for each individual entity, and on how small the key is shorter keys are faster to maintain and use.
Attributes comprising the primary key are shown underlined. The parts of any composite attributes are drawn connected to the oval of the composite attribute, and multivalued attributes are shown as double-lined ovals.
Similarly, a product price could be a positive rational number. Attributes can be empty; for example, some customers may not provide their telephone numbers.
You should think carefully when classifying an attribute as multivalued: The sales database requirements may specify that a product has a name and a price. To distinguish between products, we can assign a unique product ID number to each item we stock; this would be the primary key.