DBMS NOTES UNIT 3

 

                                                            UNIT 3 

ER (Entity Relationship)

An entity-relationship model is known as an ER model. This data model is at a high level. The data items and relationships for a given system are defined using this model.
It creates the database's conceptual design. Additionally, it creates a highly straightforward and user-friendly data view.
An entity-relationship diagram is a type of diagram used in entity-relationship modelling to represent the database structure.

Assume, for instance, that we create a database for a school. The student will be an entity in this database with attributes such as name, address, ID, age, and so forth. There will be a relationship between the address and other entities that include attributes like the city, street name, pin code, etc.

components of ER diagram are:

Entity: Weak entity, Strong entity 

Attributes: Key, composite, multivaluded, single, derived, singular etc.

Relationship: One to one, one to many, many to one, many to many.

Entity: An entity can be any kind of thing, person, place, or class. Rectangles can be used in the ER diagram to represent an entity.

Using an organisation as an example, one can treat a manager, a product, an employee, a department, etc. as an entity.

Weak entity,

A weak entity is an entity that is dependent on another entity. The weak entity lacks any essential characteristic of its own. A double rectangle serves as the weak entity's representation.Ex installment is depended on loan for value.

Attribute :

An entity's property is described by its attribute. Eclipse is a tool for attribute representation.

A student's identity, age, contact information, name, and so forth are examples of qualities.

 a. Key Attribute

An entity's primary attributes are represented by its key attribute. It stands for a principal key. The text is emphasized and an ellipse is used to symbolise the main attribute.

b. Composite Attribute

A composite characteristic is one that is made up of numerous other attributes. An ellipse is used to symbolise the composite attribute, and those ellipses are joined by another ellipse.c. Multidimensional Property

c. Multivalued 
Multiple values are possible for an attribute. We refer to these characteristics as multivalued attributes. A multivalued attribute is represented by a double oval.

A student may, for instance, possess many phone numbers.

d. Derived Attribute 

Originated Characteristic

A derived attribute is one that may be obtained from another attribute. It can be shown as an ellipse with a dash.

An individual's age, for instance, varies with time and might be inferred from another characteristic, such as their birthdate.

  
Relationship
The relationship between entities is expressed through a relationship. A rhombus or diamond is used to symbolise the partnership.

One to One

A relationship is referred to as one to one when there is just one instance of each entity involved.

As an illustration, a woman can marry one man, and a man can marry one woman.


One-to-many 

A one-to-many relationship exists when there is only one instance of the entity on the left and multiple instances of the entity on the right that are associated with the relationship.

For instance, a scientist is capable of creating numerous inventions, but only that one particular creation.

Many to one 

A relationship is said to as many-to-one when there are multiple instances of the left entity and only one instance of the right entity associated with it.

For instance, a student may enrol in just one course, yet there may be several students in one course.


many-to-many 

A relationship is referred to as many-to-many when multiple instances of the entity on the left and multiple instances of the entity on the right are associated with it.

For instance, a person may be assigned to numerous projects, and a project may employ numerous people.

 

Abstraction:

Abstraction is the process of simplifying complex systems by focusing on essential details while hiding unnecessary complexities. In the context of ER diagrams:

Entity Abstraction: Entities represent real-world objects or concepts, abstracted from their detailed properties. For example, in a university database, entities like Student, Course, and Instructor represent real-world entities without considering all their attributes.

Relationship Abstraction: Relationships represent associations between entities, abstracted from the specific attributes involved. For instance, a relationship between Student and Course might represent enrollment, without specifying details like enrollment date or grade.

Attribute Abstraction: Attributes define the properties of entities and relationships, abstracted from their detailed implementation. For example, a Student entity might have attributes like StudentID, Name, and Age, abstracting away details like the format of StudentID or the calculation of Age.

Abstraction allows ER diagrams to focus on the essential aspects of the database structure, making it easier to understand and communicate complex relationships.

Integration:

Integration involves combining different components or perspectives into a unified whole. In ER diagrams, integration occurs in several ways:

Entity Integration: Entities from different parts of the system are integrated into a single diagram, showing their relationships and interactions. For example, a university database might integrate entities like Student, Course, and Department into a single ER diagram.

Relationship Integration: Relationships between entities are integrated to show how different parts of the system interact with each other. For instance, in the university database, relationships between Student and Course, Instructor and Course, etc., are integrated to illustrate the overall structure of the academic system.

Attribute Integration: Attributes from various entities and relationships are integrated to depict the complete set of data elements in the system. This integration helps understand the data requirements and dependencies across different components.

Integration ensures that ER diagrams provide a comprehensive view of the database structure, capturing all relevant entities, relationships, and attributes necessary for system understanding and design.

In summary, abstraction and integration are fundamental principles in ER diagram creation and interpretation. They enable the representation of complex database structures in a clear and concise manner, facilitating effective communication and system design.

What is the Relational Model? 

The relational model represents how data is stored in Relational Databases. A relational database consists of a collection of tables, each of which is assigned a unique name. Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table. 

Table Student  ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18

 

Important Terminologies

Attribute: Attributes are the properties that define an entity. e.g.; ROLL_NO, NAME, ADDRESS

Relation Schema: A relation schema defines the structure of the relation and represents the name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema.

Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples, one of which is shown as:

1 RAM DELHI 9455123451 18

Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation instance. Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is an insertion, deletion, or update in the database.

Degree: The number of attributes in the relation is known as the degree of the relation. The STUDENT relation defined above has degree 5.

Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4.

Column: The column represents the set of values for a particular attribute. The column ROLL_NO is extracted from the relation STUDENT.

ROLL_NO

1

2

3

4

NULL Values: The value which is not known or unavailable is called a NULL value. It is represented by blank space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL. 

Relation Key: These are basically the keys that are used to identify the rows uniquely or also help in identifying tables. These are of the following types.

Primary Key

Candidate Key

Super Key

Foreign Key

Alternate Key

Composite Key

Constraints in Relational Model

While designing the Relational Model, we define some conditions which must hold for data present in the database are called Constraints. These constraints are checked before performing any operation (insertion, deletion, and updation ) in the database. If there is a violation of any of the constraints, the operation will fail.

Domain Constraints

These are attribute-level constraints. An attribute can only take values that lie inside the domain range. e.g.; If a constraint AGE>0 is applied to STUDENT relation, inserting a negative value of AGE will result in failure.

Key Integrity

Every relation in the database should have at least one set of attributes that defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO in STUDENT is key. No two students can have the same roll number. So a key has two properties: 

It should be unique for all tuples.

It can’t have NULL values.

Referential Integrity

When one attribute of a relation can only take values from another attribute of the same relation or any other relation, it is called referential integrity. Let us suppose we have 2 relations 

Table Student 

ROLL_NO NAME ADDRESS PHONE AGE BRANCH_CODE

1 RAM DELHI 9455123451 18 CS

2 RAMESH GURGAON 9652431543 18 CS

3 SUJIT ROHTAK 9156253131 20 ECE

4 SURESH DELHI 18 IT

Table Branch 

BRANCH_CODE BRANCH_NAME

CS COMPUTER SCIENCE

IT INFORMATION TECHNOLOGY

ECE ELECTRONICS AND COMMUNICATION ENGINEERING

CV CIVIL ENGINEERING

BRANCH_CODE of STUDENT can only take the values which are present in BRANCH_CODE of BRANCH which is called referential integrity constraint. The relation which is referencing another relation is called REFERENCING RELATION (STUDENT in this case) and the relation to which other relations refer is called REFERENCED RELATION (BRANCH in this case). 

Anomalies in the Relational Model

An anomaly is an irregularity or something which deviates from the expected or normal state. When designing databases, we identify three types of anomalies: Insert, Update, and Delete.

Insertion Anomaly in Referencing Relation

We can’t insert a row in REFERENCING RELATION if referencing attribute’s value is not present in the referenced attribute value. e.g.; Insertion of a student with BRANCH_CODE ‘ME’ in STUDENT relation will result in an error because ‘ME’ is not present in BRANCH_CODE of BRANCH. 

Deletion/ Updation Anomaly in Referenced Relation: 

We can’t delete or update a row from REFERENCED RELATION if the value of REFERENCED ATTRIBUTE is used in the value of REFERENCING ATTRIBUTE. e.g; if we try to delete a tuple from BRANCH having BRANCH_CODE ‘CS’, it will result in an error because ‘CS’ is referenced by BRANCH_CODE of STUDENT, but if we try to delete the row from BRANCH with BRANCH_CODE CV, it will be deleted as the value is not been used by referencing relation. It can be handled by the following method: 

On Delete Cascade

It will delete the tuples from REFERENCING RELATION if the value used by REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION. e.g.; For, if we delete a row from BRANCH with BRANCH_CODE ‘CS’, the rows in STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted. 

On Update Cascade

It will update the REFERENCING ATTRIBUTE in REFERENCING RELATION if the attribute value used by REFERENCING ATTRIBUTE is updated in REFERENCED RELATION. e.g;, if we update a row from BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the rows in STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be updated with BRANCH_CODE ‘CSE’.

Super Keys

Any set of attributes that allows us to identify unique rows (tuples) in a given relationship is known as super keys. Out of these super keys, we can always choose a proper subset among these that can be used as a primary key. Such keys are known as Candidate keys. If there is a combination of two or more attributes that are being used as the primary key then we call it a Composite key.

Codd Rules in Relational Model

Edgar F Codd proposed the relational database model where he stated rules. Now these are known as Codd’s Rules. For any database to be the perfect one, it has to follow the rules.

For more, refer to Codd Rules in Relational Model.

Advantages of the Relational Model

Simple model: Relational Model is simple and easy to use in comparison to other languages.

Flexible: Relational Model is more flexible than any other relational model present.

Secure: Relational Model is more secure than any other relational model.

Data Accuracy: Data is more accurate in the relational data model.

Data Integrity: The integrity of the data is maintained in the relational model.

Operations can be Applied Easily: It is better to perform operations in the relational model.

Disadvantages of the Relational Model

Relational Database Model is not very good for large databases.

Sometimes, it becomes difficult to find the relation between tables.

Because of the complex structure, the response time for queries is high.

Characteristics of the Relational Model

Data is represented in rows and columns called relations.

Data is stored in tables having relationships between them called the Relational model.

The relational model supports the operations like Data definition, Data manipulation, and Transaction management.

Each column has a distinct name and they are representing attributes.

Each row represents a single entity.

Properties of Relations

The relation's name is unique compared to all other relations.
There is exactly one atomic (single) value in every relation cell.
Every characteristic has a unique name.
The attribute domain is meaningless.
Tuple is free of duplicate values.
A tuple's order may differ in its sequence.