ENTITY RELATION SHIPS COMPLETE ANALYSIS UPDATED

ER RELATION SHIPS

1. Data base Design and ER model:-
The Data base Systems are designed to managed Large
blocks of information.
The Data base design can be divided into the six steps
1.Requirement Analysis:- To understand what data is to
be stored in the Data base, What applications must be built
on top. Analysis of data and users requirement to entity
and relationship sets.
2.Conceptual Database design:- ER model is used in this
Stage. It mainly used as what entities and relationships
are used.
3.Logical database design:- To convert ER schema into a
relational database schema.

2. 4. Schema Refinement:- Analyze the Collections of
Relations.
5. Physical Data base Design:- Data base Indexing
6. Application and security Design:- We Provide
the security Privileges to the database and access
control to the Various users.
Example:-
The Company is organized into departments. Each
department has a unique name, a unique number
and a particular employee who manages the
department. We keep track of the start date when
the employee began managing the department. A
department may have several locations.

4. ENTITY RELATIONSHIP MODEL
•ER model stands for an Entity-Relationship model.
It is a high-level data model.
•It develops a conceptual design for the database. It
also develops a very simple and easy to design view
of data.
•In ER modeling, the database structure is portrayed
as a diagram called an entity-relationship diagram.

5. Entity :- It is a real world thing with
Physical or Conceptual Existence.
Attribute:- It is a Property which
describes an entity.
Entity type:- It Defines a Collection of
entities that have same attributes.

6. Entity are represented with the Rectangle
Symbol.
Attributes are represented with the Oval
Symbol.

9. Weak Entity: An entity that depends on another
entity called a weak entity.
The weak entity is represented by a double
rectangle.

10. Key Attribute:- The attribute which is
uniquely identifies each entity in the entity
set is called key attribute.
Key attribute is represented by an Oval
which is underlined in the name.

11. Composite Attribute:-
An attribute that composed of many other
attributes is known as a composite attribute.
 
 

12. Single Valued and Multi Valued Attributes
Single Valued attributes are attributes with Single value .
Multivalued Attribute:-
An attribute can have more than one value. These attributes are known as
a multi valued attribute. The double oval is used to represent multi
valued attribute.

13. Stored and Derived Attributes:-
Stored attributes :- Attributes which are stored called as Stored
attributes.
Derived attributes:- Attributes which are derived from the values of the
Stored attributes are called as Derived Attributes.
These derived attributes are indicated as Dashed Oval symbol.

14. Example 1
Requirements Collection and Analysis
“COMPANY”
An entity type DEPARTMENT with attributes Name,
Number, Locations, Manager, and Manager_start_date.
Locations is the only multivalued attribute. We can specify
that both Name and Number are (separate) key attributes
because each was specified to be unique.

16. Example 02
An entity type PROJECT with attributes
Name, Number, Location, and
Controlling_department. Both Name and
Number are (separate) key attributes.

18. Relationships:-
Entities may have several relationships among
themselves. Whenever an attribute of one
entity refers to another entity there exists a
relationship between the two entities.
A relationship is used to describe the relation
between entities.
Diamond or rhombus is used to represent the
relationship.

19. Types of relationships:-
a.One-to-One Relationship
When only one instance of an entity is associated
with the relationship, then it is known as one to
one relationship.

20. Types of relationships:-
b. One-to-many relationship
When only one instance of the entity on the left, and more
than one instance of an entity on the right associates
with the relationship then this is known as a one-to-
many relationship.
For example, 
Scientist can invent many inventions,
but the invention is done by the only specific
scientist.

21. Types of relationships:-
c. Many-to-one relationship When more than one
instance of the entity on the left, and only one instance
of an entity on the right associates with the relationship
then it is known as a many-to-one relationship.
For example, Student enrolls for only one course,
but a course can have many students.

22. Types of relationships:-
d. Many-to-many relationship
When more than one instance of the entity on the left, and
more than one instance of an entity on the right
associates with the relationship then it is known as a
many-to-many relationship.
For example, 
Employee can assign by many projects
and project can have many employees.

23. Understanding Relationships
Role name signifies the role that a participating entity from the
entity type plays in each relationship instance, and helps to
explain what the relationship means.
Role
employee
worker
Role
department
employer

25. Degree of a Relationship Type-
The number of participating entity types is known as the
degree of relationship type.
•Binary Relationship –
 A relationship type of degree two is
called binary relationship.

26. Ternary Relationship-
 A relationship type of degree three is
a ternary relationship for example, in the below
figure
 
supply
 
relationship connects three entities
SUPPLIER, PART AND PROJECT.

27. Recursive
  Relationship
If the same entity type participate more than once in a
 
relationship type in different roles then such
  relationship
types are called recursive
  relationship. For example, in the
below figure REPORTS_TO is a recursive relationship as the
Employee entity type plays two roles – 1) Supervisor and 2)
Subordinate.

28. Roles
•Entity sets of a relationship need not be distinct
•The labels “manager” and “worker” are called roles; they specify
how employee entities interact via the works-for relationship set.
•Roles are indicated in E-R diagrams by labeling the lines that
connect diamonds to rectangles.
•Role labels are optional, and are used to clarify semantics of the
relationship.

29. Cardinality Constraints
•We express cardinality constraints by drawing either a
directed line (), signifying “one,” or an undirected line
(—), signifying “many,” between the relationship set and
the entity set.
•E.g.: One-to-one relationship:
–A customer is associated with at most one loan via the relationship
borrower
–A loan is associated with at most one customer via borrower

30. One-To-Many Relationship
In the one-to-many relationship a loan is associated with at
most one customer via borrower, a customer is associated
with several (including 0) loans via borrower

31. Many to One Relationship
In a many-to-one relationship a loan is associated with several
(including 0) customers via borrower, a customer is
associated with at most one loan via borrower

32. Many to Many Relationship
•A customer is associated with several (possibly 0) loans via
borrower
•A loan is associated with several (possibly 0) customers via
borrower

33. Notations of Different Types of Cardinality
 In ER Diagram
–

34. Participation of an Entity Set in a Relationship Set
•Total participation (indicated by double line):
every entity in the entity set participates in at least
one relationship in the relationship set
–E.g. participation of loan in borrower is total
• Every loan must have a customer associated to it via borrower.
•Partial participation: Some entities may not
participate in any relationship in the relationship set.
–E.g. participation of customer in borrower is partial

36. Class hierarchies
Class hierarchies are divided into two types
1.Specialization
2.Generalization
Specialization
It is a top down process.
A lower-level entity set inherits all the attributes and
relationship participation of the higher-level entity set
to which it is linked.

38. Generalization
 
A bottom-up design process
start with lots of distinct entities that share attributes
Combine a number of entity sets that share the same attributes
into a higher-level entity set.
Specialization and generalization are simple inversions of each other;
they are represented in an E-R diagram in the same way.

41. Aggregation
 
Consider the ternary relationship works-on
Suppose we want to record managers for tasks performed by an
employee at a branch.
works-on and manages represent overlapping information
Every manages relationship corresponds to a works-on
relationship
some works-on relationships may not correspond to any manages
relationships
we can’t discard the works-on relationship.

43. E-R Model: Analyze the problem carefully and
came up with the entities in it. Identity what
data has to be persisted in the database. This
contains the entities, attributes etc.
•Entities
• Passenger
–Reservation
–Ticket
–Depo
–Bus
–Cancellation

44. S.No Entity Attributes
A) 1) Passenger
S.No(Primary Key)
Name (Foreig Key)
Age
Phone No
Gender
Address
Passport Id
S.No Entity Attributes
B) 2) Bus
Bus No
Source
Destination
Departure Time
Bus Type
Arrival Time

45. S.No Entity Attributes
C) 3)Ticket
Ticket No (primary Key)
Ticket Type
Seat No
Ticket Cost
Bus No
Date
Time
Bus Type
No Of Tickets
Name of the Passenger
Distance

46. S.No Entity Attributes
D) Reservation
Reservation No(PK)
Seat Conform
Seat No
Date of Travel
Date if Reservation
Bus No
Time
Bus Type
No Of Tickets
Type of Ticket
Waiting List
S.No Entity Attributes
E) Depo
Depo No (PK)
No. Of Buses
Type Of Buses
No.Of Buses Arrivals
No. Of Departures
Bus No (FK)

47. S.No Entity Attributes
F) Cancellation
Reservation cancel
No.Of Tickets Cancel
Precancel
Personal Cancel
Online Cancel
Change for cancel

48. Passenger Entity

49. Bus Entity

50. Ticket Entity

52. Conversion of E-R model
to Relational model

53. Steps

54. Example conversion

55. Entity set with a composite attribute

56. Entity set with multi valued
attribute

57. Conversion of multi valued
attributes into a relation

58. Translation of relationship into a
relation