Wednesday, 21 May 2014

Entity Relationship modeling

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel
Content, examples and diagrams are taken from that book.

UNIT-II Chapter –I Entity Relationship modeling
Q: What are E-R Model Components or modules?
Ans:Three components: Entities, Attributes, and Relationships.


Entity: Entity is anything about which data are to be collected and stored.
An entity may be concrete (a person or a book, for example) or abstract (like a holiday or a concept).
An entity is represented by a rectangle containing entity’s name.
The entity name , a noun, is usually written in all capital letters.


Attribute:
Attributes are characteristics of entities.
For ex: STUDENT entity has the attributes  STU_FNAME,STU_PHONE etc.
Attributes are represented by ovals and are connected to the entity rectangle with a line.
Each oval contains the name of the attribute  it represents.
Attributes may share a domain.
Primary keys are underlined. (here RollNo is the primary key.)


Relationship
A relationship is an association between entities.
Relationships are described as verbs.
Relationships are represented by diamond-shaped symbols


Q: What are the different Types Of Attributes:
Ans:
1. Required and Optional Attributes:
Required attribute is an attribute that must have a value, it cannot be left empty.
Ex: STU_FNAME, STU_FNAME


Optional attribute is an attribute that does not require a value, it can be left empty.
Ex: STU_PHONE…all students may or may not have a phone at home.


2.Composite and Simple attributes:
A simple attribute cannot be subdivided.
Examples: Age, Sex, and Marital status
A composite attribute can be further subdivided to yield additional attributes.
Examples:ADDRESS  into  Street, City, State, Zip
    PHONE NUMBER  into Area code, Exchange number
3. Single-Valued and Multivalued Attributes:
A single-valued attribute can have only a single value.
Examples: A manufactured part can have only one serial number.
A multivalued attribute can have many values.
Multivalued attributes are shown by a double line connecting to the entity
Examples: i) A person may have several college degrees.
    ii)A household may have several phones with different numbers
4. Derived Attribute and Stored Attribute
A derived attribute is not physically stored within the database; its value is computed from other attributes.
It is indicated using a dotted line connecting the attribute with the entity.
Example: AGE can be derived from DOB and current date.


What is Cardinality ?
Ans: Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity.
In the ERD, cardinality is indicated by placing appropriate numbers beside the entities, using the format (x,y).
The 1st  value represents the minimum number of associated entities,
while the 2nd value represents the maximum number of associated entities.
These implemented by the application software or by triggers.


Q:When can you say an entity is Existence dependent/ independent?
Ans: An entity is said to be existence dependent if it can exist in the database only when it is associated with another related entity occurrence.


Existence independence: if an entity can exist independently, then it is said to be existence dependent.

Q:What is relationship strength? Explain about strong and weak relationships.
Ans: Relationship Strength is based on how the primary key of a related entity is defined.
They are of 2 types.
Weak (Non-identifying) relationship: a weak relationship also known as non-identifying relationship, exists if the entity has a primary key that is not partially or totally derived from the parent entity in the relationship
Strong relationship also known as identifying relationship, exists if the entity has a primary key that is partially or totally derived from the parent entity in the relationship


What is Weak entity?
Ans: A weak entity is one that meets two conditions
  1. The entity is existence- dependent.
  2. The entity has a primary key that is partially or totally derived from the parent entity in the relationship i.e, Strong relationship.
A weak entity id identified by using a double-walled entity rectangle.
Ex: DEPENDENT is the weak entity in the relationship EMPLOYEE has DEPENDENT.


What is meant by relationship participation?
Ans: Participation in an entity relationship is either optional or mandatory.
Optional participation means that one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
For Ex: in the “COURSE generates CLASS” relationship, there are some courses that do not generate a class. Therefore, the CLASS entity is considered to be optional to the COURSE entity.
Mandatory participation means that one entity occurrence  require a corresponding entity occurrence in a particular relationship
If every COURSE must generate a CLASS then the CLASS entity is considered to be mandatory to the COURSE entity.


Types of Relationships
A relationship’s degree indicates the number of entities that participate in the relationship.
Different types of relationship degrees are :
1. Unary relationship : If an relationship is maintained within a single entity then  such relationship is called unary relationship.
Example: an employee within the EMPLOYEE entity is the manager for one or more employees within that entity.
when an entity has a relationship with itself then such relationship is called as recursive relationship.
2. Binary Relationship: Binary Relationship exists when two entities are associated in a relationship. Ex: the relationship “a PROFESSOR teaches one or more CLASSes


What is a recursive relationship?
Ans: when an entity has a relationship with itself then such relationship is called as recursive relationship.


What is an associative or composite or bridge entity?
Ans: When there is M:N relationship between two entities then we create a new entity called bridge/composite entity that contains the primary keys of both the entities participating in M:N relationship
Ex:


Explain database design challenges?
Ans:
  1. Design Standards: Standards guide one in developing logical structures that reduce data redundancies. Without design standards, it is not possible to design a proper design or evaluate an existing design.
  2. Processing Speed: high processing speed are top priority in database design as high processing speed  are necessary for many organizations
    for example: a perfect design must use a 1:1 relationship to avoid nulls, while a higher transaction –speed design might combine the two tables to avoid the use of an additional relationship, using dummy entries to avoid nulls.
    If the focus is on data-retrieval speed, one must include derived attributes in design.
  3. Information requirements: a design that meets all logical requirements is an important goal. The designer should consider end-user requirements such as performance, security, shared access. He must also verify that all update, retrieval and deletion options are available and also all query and reporting requirements.





No comments:

Post a Comment