Online Courses
Free Tutorials  Go to Your University  Placement Preparation 
0 like 0 dislike
988 views
in DTU B.Tech (CSE-IV) Database Management System Lab by Goeduhub's Expert (7.6k points)

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

Goeduhub's Top Online Courses @Udemy

For Indian Students- INR 360/- || For International Students- $9.99/-

S.No.

Course Name

 Coupon

1.

Tensorflow 2 & Keras:Deep Learning & Artificial Intelligence

Apply Coupon

2.

Natural Language Processing-NLP with Deep Learning in Python Apply Coupon

3.

Computer Vision OpenCV Python | YOLO| Deep Learning in Colab Apply Coupon
    More Courses

1 Answer

0 like 0 dislike
by Goeduhub's Expert (7.6k points)
edited by
 
Best answer

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.

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

output

  1. Weak EntityAn 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.

image

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. 

image 1

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

image 2

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

  • Derived attributeThese 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.

    image 3

  • 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.

image 4

  • 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.

multivalued

  • 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

notations

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. 

mapping constraints

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.

pkey

  • 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

ckey

  • 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.
    StudIDRoll NoFirst NameLastNameEmail
    111TomPriceabc@gmail.com
    212NickWrightxyz@gmail.com
    313DanaNatanmno@yahoo.com
  • 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:
DeptCodeDeptName
001Science
002English
005Computer

Teacher IDFnameLname
B002DavidWarner
B017SaraJoseph
B009MikeBrunton

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 IDDeptCodeFnameLname
B002002DavidWarner
B017002SaraJoseph
B009001MikeBrunton

This concept is also known as Referential Integrity.

Primary Key vs Foreign Key

Primary KeyForeign 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

3.3k questions

7.1k answers

394 comments

4.6k users

 Goeduhub:

About Us | Contact Us || Terms & Conditions | Privacy Policy || Youtube Channel || Telegram Channel © goeduhub.com Social::   |  | 
...