Entity Relationship Model Part-1

image_pdfimage_print

Database Model:
Logical structure of a database and fundamental determines in which manner data can be stored, organized and manipulated.

1)Hierarchical Model:

  • Data is organized in tree like structure, implying a single parent for each record.
  • Allows to one to many relationship

2)Network Model:

  • Allows many to many relationship in a graph like structure that allows multiple parents.
  • Organise data using two fundamental concepts called records and sets.

3)Relational Data Model:
Collection of tables to represent data and the relationship among those data. Eg: Oracle, Sybase.

4)Object Oriented Data Model:
Data and their relationship are organized or contained in a single structure known as object.

Hierarchical ,Network and Relational data model is type of Record Based Model 

Image result for data models in dbms

ENTITY RELATIONSHIP MODEL DESIGN

1)Entity: It is “thing” or “object” in the real world that is distinguishable from all other objects. An entity has a set of properties and values for some set of properties that may uniquely identify an entity.

2)Entity Set:
Collection of entities all having same properties or attributes.

3)Attributes:
Each entity is described by set of attributes/properties. Attributes are descriptive properties possessed by each member of an entity set.
For each attributes, there is set of permitted values called domain or value set of the attributes.

Types of attributes:

1)Simple Attributes: Not divided into subpart eg: any unique number like 1234
2)Composite Attributes: Divided into subpart eg: Name is divided into first name, middle name and last name.
3)Single Value Attribute: Single value for a particular entity eg: order_id
4)Multivalued Attribute: More than one value for a particular entity eg: Phone No.
5)Derived Attribute: Attribute value is dependent on some other attribute.Eg: Age

Null Values: Entity doesn’t have value for the attribute.

Keys:
Key plays an important role in relational database; it is used for identifying unique rows from table. It also establishes relationship among tables.
Types of Key:
1)Primary Key
2)Composite Key
3)Super Key
4)Candidate Key
5)Secondary Key
6)Foreign key
1)Primary key:
A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
A relation may contain many candidate keys.When the designer select one of them to indentify a tuple in the relation,it becomes a primary key.It means that if there is only one candidate key ,it will automatically selected as primary key.
primary key of table
2)Composite key
Key that consist of two or more attributes that uniquely identify an entity occurrence is called Composite key. But any attribute that makes up the Composite key is not a simple key in its own.
composite key o table
3)Super Key
A super key is the most general type of key.A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.Super key is a superset of Candidate key.
4)Candidate key
A candidate key is simply the “shortest” super key. Candidate Key are individual columns in a table that qualifies for uniqueness of each row/tuple.Every table must have at least one candidate key but at the same time can have several.
5)Secondary key
Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.
6)Foreign key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.They act as a cross-reference between tables.
Image result for foreign key in dbms