Entity -Relationships Models
Entity-Relationship models are useful for both database designers and business users. ER models help business users understand whether or not a database is designed according to the business requirements. ER models help us answering following questions:
- What are entities and attributes?
- What is a relation in an ER model?
- What is the degree of relation between two entities?
- What are the minimum and maximum cardinalities?
‘Three-Level Architecture’. A database is used to organise data. To store and secure the data efficiently, a 3-level architecture is used. The three-level architecture refers to the three different layers at which a DBMS can be viewed.
The three levels are:
- Internal level — The internal level involves the physical storage of data on storage devices.
- Logical level — the logical layer involves the schema design for database implementation.
- View level — The view level involves the applications required to access the database.
The implementation of all three levels is independent of each other. If the schema implementation is changed at the logical level, then there is no change at the physical-level implementation. Each view level displays only specific parts of the logical schema, i.e., only the view level that displays the changed part of the logical schema changes; the implementation of the other view levels remains the same.
Data Models
A data model means to structure data in which we define a particular schema according to which the data is stored and also to define the relationship between various data elements,
Data models are logical designs created on paper. These designs are then implemented physically.
The logical view of the database architecture contains data models. These data models do not define how the data is stored on physical storage devices.
Data models are necessary for business users to understand a database. Database designers build data models based on the business requirements defined by business users and business users can review the models.
There are different types of data models: Entity-Relation or E-R model, relational model, network model, hierarchical model, and dimensional model.
An E-R Model describes real-world objects as entities and their properties as attributes. An E-R Model identifies the relation between these entities. It also identifies the degree of relation and the participation of each entity in these relations.
A Relational Model stores each business concept in a table and describes it as a relation. Each relation or table has keys to uniquely identify the data in every record. Foreign keys are used to describe how two different relations or tables are related. The foreign keys are the implementation of the relations described in the E-R model.
Building an E-R Model
Entities are real-world objects. E.g. A student, an employee, a bank account. An entity is something about which a business wants to store information.
e.g. Marketing is an entity that has properties such as Number of Employees, Head.
An entity is a table in a relational model.
Attributes can be understood as the properties of the real-world objects. Product size, Product weight, product name, customer address, user Login ID. course name, number of employees or manager. An attribute is some information about an entity that a business wants to store. So we can say that Attributes in an E-R model represent the properties of a real-world object. For example, a particular product’s properties.
For Department as entity with attributes Manager , Number of employees e.g. Marketing which has Manager named Rahul and number of employees to be 100.
OR
Relation in an E-R Model
Important part in the relations in an E-R model is understanding the one-to-one, one-to-many, many-to many relations between entities and demonstrating how E-R diagrams are represented.
A relation defines how the entities in an E-R model are related to each other. It is generally a verb that connects entities such as An employee ‘works’ in a company, A department ‘has’ many employees, A team ‘manages’ a project.
A Unary relation or recursive relation relates one row of an entity to another. One employee may ‘report’ to another employee.
A Binary relation connects two different entities. For example, a customers ‘buys’ products.
A Ternary relation connects three different entities.
A relation is a table in a relational model.
A degree of relation defines how two entities participate in a relation.
When the maximum participation from each entity is one, the relation is one-to-one. A one-to-one relation defines each record of one entity is related to at most of one record in another entity.
When the maximum participation from one of the entities is N, the relation is one-to-many. A one-to-many relation between entity A and entity B defines that each record of entity A is related to many records of the entity B but each record of entity B can be related to at most one record of the entity A.
When the maximum participation from both entities is N, the relation is many-to-many. A many-to-many relation defines that one row of each table can be related to any number of rows in the other table.
Cardinality
Cardinality can be Minimum and Maximum.
Minimum Cardinality is again 2 types: Optional Cardinality, Mandatory Cardinality.
Optional Participation(CAN): As the name suggests, here the relationship between two entities is optional.
going back to the Team and Project example, If a Team can participate in relation with the Project entity. A team may or may not handle any project. The minimum cardinality of the Team is 0.
A Project can participate in relation with the Team entity. A project may or may not be handled by any team. The minimum cardinality of the project is 0.
Mandatory Participation(MUST): As the name suggests the relationship between two entities is mandatory.
for the same example of Team and Project, The Team must participate in relation with the Project entity. A team must handle at least one project. The minimum cardinality of the team is 1.
A Project must participate in relation with the Team entity. A project must be handled by at least one team. The minimum cardinality of the project is 1.
Maximum Cardinality: is the degree of relation between two entities.
If the relation between the team and project entities is many-to-many, then the maximum cardinality of the team is N and that of the project entity is N as well.
If the relation between the team and project entities is one-to-one, then the maximum cardinality of both entities is 1.
If the relation between the team and project entities is one-to-many, then the maximum cardinality of the team is 1, whereas that of the project entity is N.
Representation of Cardinality
In the above diagram, for the first relation, the first vertical line nearer to the team as well as the first one nearer to the project is maximum cardinality.
the vertical line away from the team as well as the project is minimum cardinality.
for the second relation, the Maximum Cardinality for the Team is 1 and the Minimum is 0. For the Project, the maximum and minimum are 1.
e.g of one-to-many
1st Relationship: For the Team, the maximum and minimum cardinality is 1, For Project, the maximum cardinality is N, minimum being 1.
2nd Relationship: For the Team, the maximum and minimum cardinality is 1, For Project, the maximum cardinality is 1, minimum being 0.
1st Relationship: For the Team, the maximum cardinality is N and the minimum cardinality is 0, For Project, the maximum cardinality is N, the minimum being 1.
2nd Relationship: For the Team, the maximum cardinality is N and the minimum cardinality is 0, For Project, the maximum cardinality is 1, the minimum being 0.
Reference: MSc Curriculum, LJM
