Unit 2: Entity Relationship Model (ER-Model)
Data Model
It is the collection of conceptual tools for describing data, data relationships, data semantics and consistency constraints. Data Model gives us an idea that how the final system will look like after its complete implementation. Data Models are fundamental entities to introduce abstraction in a DBMS.
Categories of data model are:
Record based data model
Record based data model is the data model that describes the overall logical structure of database in some fixed format of records. It has a fixed number of fields or attributes in each record type and each field is usually of a fixed length.
Further, it is classified into three types-
1. Hierarchical data model:
Hierarchical data model is the record based data model that uses tree like structure to represent data and their relationships.
Hierarchical Model was the first DBMS model. This model organizes the data in the hierarchical tree structure. The hierarchy starts from the root which has root node and then it expands in the form of a tree adding child node to the parent node. In this model, each child node has a parent node but a parent node can have more than one child node. If a parent node is deleted then the child node is automatically deleted. This model easily represents some of the real-world relationships like food recipes, sitemap of a website etc.
Advantages:
· The hierarchical data model allows to organize records using an ordered tree which is natural method of implementation of one to many relationship.
· It provides faster and easier retrieval of records.
· It maintains data independently i.e. if the data is altered in one table, it doesn’t affect other table or location.
· It provides database security.
Disadvantages:
· It doesn’t support many to many relationship.
· It is an old and outdated model.
· Increase redundancy because some data is to be written in different place.
· It is not flexible.
2. Network data model
The network data model is the record based data model that uses graphical data structure to represent data and relationship among them.
This model is an extension of the hierarchical model. It was the most popular model before the relational model. This model is the same as the hierarchical model, the only difference is that a record can have more than one parent. It replaces the hierarchical tree with a graph. Example: In the example above we can see that nodes teacher and courses have two parents i.e. civil engineering and computer engineering. This was earlier not possible in the hierarchical model. As it supports many to many relationship, there can be more than one path to the same record. This makes data access fast and simple.
Advantages:
· It reduces data redundancy because data shouldn’t be repeated at different places.
· The data can be accessed faster as compared to the hierarchical model. This is because the data is more related in the network model and there can be more than one path to reach a particular node. So the data can be accessed in many ways.
· It accepts many to many relationship. So it is more flexible.
· It is conceptually simple and easy to design, maintain, implement and use.
Disadvantages:
· Its database structure is very complex (difficult) because all the records in it are maintained using pointers.
· Less secure because data is open to all.
· Any change like updation, deletion, insertion is very complex.
· Need long program to handle relationship.
3. Relational Data Model
Relational data model is the record based data model that uses table (relation) to represent the data and relationships among them.
Relational Model is the most widely used model. In this model, the data is maintained in the form of a two-dimensional table. The basic structure of a relational model is tables. So, the tables are also called relations in the relational model. The tables are interrelated to each other. Each table has multiple columns, each column is identified by a unique name and each row contains a unique record.
Advantages:
· It is conceptually simple and easy to design, implement, maintain and use.
· Less data redundancy.
· Structural Independence
· Integrity rule can easily be implemented.
· It has powerful query processing capability.
Disadvantages:
· It needs more powerful hardware computers and data storage device.
· It is more complex than other model.
· Database would slow down if the database grows.
· The underlaying cost involved in this model is quite expensive.
Object based data model
It is the data model that describes the database in the concept of objects (entities) and relationship among them. It divides the data into multiple objects each having some specific characteristics (attributes).
Entity Relationship (ER) data model
The ER model is the object based data model that divides the data into multiple entities, their characteristics into attributes and their interconnections into relationships. Basically it(ER-Diagram/ER-Model) is a graphical representation of real world entities with their attributes and relationships among them. ER model represents logical structure of database graphically so that even the person doesn’t having technical knowledge can understand it. It also plays a significant role to systematically analyze the data requirements to produce a well-designed database. We use the ER diagram as a visual tool to represent an ER Model.
ER diagram has the following three components:
1. Entity: An entity is a thing or object in real world that is different from another. It can be a person, place, or even a concept. Example: Teachers, Students, Course, Building, Department, etc. are some of the entities of a School Management System. It is represented by rectangle. The set of entities having some common feature is called entity set.
2. Attribute: Attributes are the properties or characteristics of an entity. Attributes are represented by the means of ellipse. Every ellipse represents attribute and it is directly connected to its entity. Each entity has a value for its attributes. Example: The entity teacher has the property like teacher id, salary, age, etc.
The values for each attribute are defined in terms of properties such as data type, domain and default value. Data type of an attribute defines what type of data can be stored in that attribute such as integer, text etc. Domain for each attribute is a set of permitted values such as mark of subject cannot have values greater than the full marks. Default value is the value that will be recorded if not specified by the user such as 0 can be the default mark in case of not specified.
Types of attribute:
· Simple and composite attribute: − Simple attributes contain atomic values, which cannot be divided further. For example, a student's roll number. Composite attribute is an attribute that can be subdivided into other simple attributes with meanings of their own. Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first name and last name.
· Single valued and multivalued attribute: An attribute which can take only one value and doesn’t change with time is called single valued attribute. For e.g. DOB. The attribute which can take more than one value is called multivalued attribute. For example, a person can have more than one phone number, email, address, etc.
· Derived attribute: Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average salary in a department should not be saved directly in the database, instead it can be derived. For another example, age can be derived from data of birth.
3. Relationship: A relationship is an association among several entities. It represents a meaningful dependencies among them. It is represented by diamond. Example: Teacher works for a department. The set of relationships of same type is called relationship set. Example:
In the above diagram, the entities are Teacher and Department. The attributes of Teacher entity are Teacher_Name, Teacher_id, Age, Salary, Mobile_Number. The attributes of entity Department entity are Dept_id, Dept_name. The two entities are connected using the relationship. Here, each teacher works for a department.
Symbols used in ER diagram:
Note: See copy for the ER-diagram of school, library and hospital management system.
Entity set and Entity:
Entity | Entity Type | Entity Set |
Any particular row (a record) in a relation(table) is known as an entity. | The name of a relation (table) in RDBMS is an entity type | All rows of a relation (table) in RDBMS is entity set |
Keys:
A key in DBMS is an attribute or set of attributes that help to uniquely identify a tuple or a row in a relation or a table. Keys are also used to establish a relationships between the different tables.
Types of Keys:
Super Key: Super key is an attribute or set of attributes which can uniquely identify a tuple. Super key is a superset of a candidate key. There can be more than one super key in a relation. A super key may have some extra attribute which isn't necessary for uniquely identifying the rows in the table. Example: In the given Student Table we can have the following keys as the super key.
· {Roll_no}
· {Registration_no}
· {Roll_no, Registration_no},
· {Roll_no, Name}
· {Name, Registration_no}
· {Roll_no, Name, Registration_no}
All the above keys are able to uniquely identify each row. So, each of these keys is super key. Here you can see that by using Roll_no only, we can uniquely identify the rows but if you are making a super key, then you will try to find all the possible cases of keys that can be used to identify data uniquely.
Candidate Key: A candidate key is a super key with no redundant[Nt3] attribute. It is also called a minimal super key because we select a candidate key from a set of super key such that selected candidate key has the minimum attribute required to uniquely identify the table. It is selected from the set of the super key which means that all candidate keys are super key. Candidate Keys are not allowed to have NULL values. Example: In the above example, we had 6 super keys but all of them cannot become a candidate key. Only those super keys would become a candidate key which have no redundant attributes.
· {Roll_no}: This key doesn't have any redundant or repeating attribute. So, it can be considered as a candidate key.
· {Registration_no}: This key also doesn't have any repeating attribute. So, it can be considered as a candidate key.
Primary key: It is the candidate key (chosen by database designer) that is most appropriate to become the main key of the table. Example: In the above example, we saw that we have two candidate keys i.e (Roll_no) and (Registration_no). From this set, we can select any key as the primary key for our table. It depends upon our requirement. Here, if we are talking about class then selecting ‘Roll_no’ as the primary key is more logical instead of ‘Registrartion_no’.
Alternate Key: All the candidate key which are not a primary key are called an alternate key. Example: In the above example, since we have made ‘Roll_no’ as the Primary Key our Alternate Key would be ‘Registration_no’.
Foreign Key: A foreign key is an attribute in a table that acts as the primary key in another table. Hence, the foreign key is useful in establishing the relationship among tables. Data should be entered in the foreign key column with great care, as wrongly entered data can invalidate the relationship between the two tables. Example: If we have two tables of Student and Course then we can establish a relationship between these two tables using a foreign key. The ‘Course_id’ in the Student table is the foreign key as it establishes the link between the Student and Course Table. So, if we need to find the information about any course opted by any student then we can go the Course table using the foreign key.
One thing that is to be noted here is that the foreign key of one table may or may not be the primary key. But it should be the primary key of another table. In the above example, Course_id is not a primary key in the Student table but it is a primary key in the Course table.
Degree of Relationship
The degree of a relationship is the number of entity types that participate (associate) in a relationship. By seeing an E-R diagram, we can simply tell the degree of a relationship. Based on the number of entity types that are connected we have the following degree of relationships:
1. Unary Relationship: If only single entity type is involved in a relationship, then it is a unary relationship. For example, an employee (manager) supervises employee.
2. Binary Relationship: If two entity types are involved in a relationship, then it is a binary relationship. It is the most common relationship degree. For e.g. a student enrolled in course
3. Ternary Relationship: If three entity types are involved in a relationship, then it is a ternary relationship.
4. N-ary Relationship: If N entity types are involved in a relationship, then it is N-ary relationship.
Mapping cardinalities
A mapping constraint is a data constraint that expresses the number of entities to which another entity can be related/associated via a relationship set. It is most useful in defining binary relationship. Types of cardinalities:
1. One to one (Cardinality ratio 1: 1): In one to one relationship, one entity from entity set A can be associated with at most one entity of entity set B and vice versa. Example:
A College |
A Principle |
Has |
2. One to many (1:M): In one to many relationship, an entity from entity set A can be associated with any number of entities from entity set B and an entity from entity set B can be associated with at most one entity of entity set A. For e.g. :
Describe one to many relationship (SEE 2074)
3. Many to one (M:1): In many to one relationship, an entity from entity set A can be associated with at most one entity from entity set B and an entity from entity set B can be associated with any number of entities form entity set A. For e.g. :
4. Many to many (M:N): In many to many relationship, an entity from entity set A can be associated with any number of entities from entity set B and vice versa. For e.g.
0 Comments