Question

Data modeling using Entity Relationship Model : E.R. model concept, notation for ER diagrams mapping constraints, Keys, Concept of super key, candidate key, primary key generalizations, Aggregation, reducing ER diagrams to tables, extended ER model

Answer 1

Data modeling using Entity Relationship Model

ER Model

Entity-relationship model is a model used for design and representation of relationships between data.An Entity–relationship model (ER model) describes the structure of a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a design or blueprint of a database that can later be implemented as a database. The main components of E-R model are : 

1. Entity
2. Attribute
3. Relationships

Entity 

An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.

Example : we have two entities Student and College and these two entities have many to one relationship as many students study in a single college.

1. Weak Entity : An entity that cannot be uniquely identified by its own attributes and relies on the relationship with other entity is called weak entity. The weak entity is represented by a double rectangle. For Example a bank account cannot be uniquely identified without knowing the bank to which the account belongs, so bank account is a weak entity.

Attributes 

If a Student is an Entity, then student's roll no., student's name, student's age, student's gender etc will be its attributes.An attribute can be of many types, here are different types of attributes defined in ER database model:

• Simple attribute : Simple attributes are those attributes which can not be divided further. For example, student's age. 

• Composite attribute : A composite attribute is made up of more than one simple attribute. For example, student's address will contain, house no., street name, pincode etc.

        Here, the attributes “Name” and “Address” are composite attributes as they are composed of many other simple attributes.

• Derived attribute : These are the attributes which are not present in the whole database management system, but are derived using other attributes. For example, average age of students in a class.

    

• Single-valued attribute : As the name suggests, they have a single value. in the below example , all the attributes are single valued attributes as they can take only one specific value for each entity.

• Multi-valued attribute : they can have multiple values. in the given example below  the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one values for a given entity.

• Relationships : When an Entity is related to another Entity, they are said to have a relationship. For example, A Class Entity is related to Student entity, because students study in classes, hence this is a relationship.Depending upon the number of entities involved, a degree is assigned to relationships.

  For example, if 2 entities are involved, it is said to be Binary relationship, if 3 entities are involved, it is said to be Ternary relationship, and so on......To know more about types of Relationships Click here

ER Diagram Notations

• Rectangles: This symbol represent entity types
• Ellipses : Symbol represent attributes
• Diamonds: This symbol represents relationship types
• Lines: It links attributes to entity types and entity types with other relationship types
• Primary key: attributes are underlined
• Double Ellipses: Represent multi-valued attributes

Mapping Constraints 

• One to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.
• One to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.
• Many to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A.
• Many to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A. 

Keys

A DBMS key is an attribute or set of an attribute which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. 

• Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.

• Super Key – A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME) the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key.The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc.
• Candidate Key – A super key with no redundant attribute is known as candidate keyFor example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the attributes like SSN, Pan_No, and License_No, etc. are considered as a candidate key

• Alternate Key – Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.Example:In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key.

  StudID	Roll No	First Name	LastName	Email
  1	11	Tom	Price	[email protected]
  2	12	Nick	Wright	[email protected]
  3	13	Dana	Natan	[email protected]

• Composite Key – A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key.
• Foreign Key – Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables. For Example:

DeptCode	DeptName
001	Science
002	English
005	Computer

Teacher ID	Fname	Lname
B002	David	Warner
B017	Sara	Joseph
B009	Mike	Brunton

In this example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.

Teacher ID	DeptCode	Fname	Lname
B002	002	David	Warner
B017	002	Sara	Joseph
B009	001	Mike	Brunton

This concept is also known as Referential Integrity.

Primary Key vs Foreign Key

Primary Key	Foreign Key
Helps you to uniquely identify a record in the table.	It is a field in the table that is the primary key of another table.
Primary Key never accept null values.	A foreign key may accept multiple null values.
Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index.	A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
You can have the single Primary key in a table.	You can have multiple foreign keys in a table.

---

---

For ER Model part 2 Click Here

---

For more Delhi Technical University(DTU) CSE-IV Database Management System Lab Experiments Click Here