Basic building entity relationship model

Entity relationship model

1. Unit-3 Entity Relationship Model Basic buildings of ER-model The entity relationship model (ER) model has become widely accepted standard for data modelling. Peter Chen first introduced the ER model in 1976 ; it was graphical representation of entities and their relationships in a database structure. ER models are normally represented in an E ntity relationship D iagram (ERD ), which uses graphical representations to model database Basic Building blocks: The basic building blocks of all data model are E ntities A ttributes Relationships

2. Entity An entity is anything (a person , a place , a thing , or an event ) about which data are to be collected and stored. Some examples of each of these types of entities are Person : employee, student, patient Place : store, warehouse, state Concept : account, course Entities can be categorized into three types: Strong Entity Weak Entity Associative Entity

3. Attribute An attribute is a characteristic or property of an entity. For example : A customer entity would described by attributes such as customer last name, customer first name, customer phone, customer address. Attributes can be categorized into six types Simple attribute Key Attribute Composite Attribute Derived Attribute Single value Attribute Multi value attribute

4. Relationships A relationship describes an association among entities. For example, a relationship between customers and agents may be described as “An agent can serve many customers, and each customer may be served by one agent”. Data models use three types of relationships: one-to-one one-to-many many-to-many Degree Of Relationship : The relationships can be categorized based on the number of entities that are participating in the relation is called “Degree of Relation”. They are Unary relationship Binary Relationship 3. Ternary Relationship

5. ER-Diagrams Entity Relationship Diagram Symbols

6. Classification of Entityset   Entity sets can be categorized into three types: 1. Strong Entity Set 2. Weak Entity Set 3. Associative Entity Set Strong entity: I t is an entity that has its own existence and is independent . The entity relationship diagram represents a strong entity type with the help of a single rectangle.

7. 2. Weak Entities A weak entity is one that meets two conditions: The entity is existence–dependent; that is, it cannot exist without the entity with which it has a relationship. 2. The entity has a primary key that is partially or totally derived from the parent entity in the relationship.

8. 3. Associative (Composite) Entity The associative entity is used to implement M:M relationship between two or more entities . This associative entity (also known as composite or bridge entity) is composed of the primary keys of each of the entities to be connected. A composite entity in the Chen model is represented by a diamond shape with in a rectangle. In Crow’s Foot model composite entity is identified by the solid relationship line between the parent and child entities, there by indicating the presence of a strong relationship.

9. Types of attributes An attribute is a characteristic or property of an entity. For example, a customer entity would described by attributes such as customer last name, customer first name, customer phone, customer address. Attributes can be categorized into five types Simple attribute Key Attribute Composite Attribute Derived Attribute Single Value Attribute Multi value attribute Simple Attribute : A simple attribute is an attribute that cannot be subdivided. For example, age , sex , and marital status would be classified as simple attributes.

10. 2. Key Attribute: An attribute that uniquely identifies a particular entity. The name of a key attribute is underscored 3. Composite Attribute : A composite attribute, is an attribute that can be further subdivided to F ield additional attributes. For example, the attribute ADDRESS can be subdivided into street , city, state , and zip code.

11. 4. Single–Valued Attributes : A single–valued attribute is an attribute that can have only a single value. For example, a student can have only one Roll Number , a nd a student is in only one class . 5. Multi value attributes: Multi value attributes are attributes that can have many values. For instance, a student may have several mail-ids . Similarly, a student may have several phone numbers E x: Land line, cell number etc ..

12. 6. Derived Attribute: An attribute whose value is calculated (derived) from other attributes. The derived attribute may or may not be physically stored in the database. In the Chen notation, this attribute is represented by dashed oval.

13. Degree of Relationships:   A relationship degree indicates the number of entities or participants associated with a relationship. There are three types of Relationships Unary Relationships Binary Relationships Ternary Relationships Unary Relationships A unary relationship exists when one entity is associated in a relationship.

14. Binary Relationships A binary relationship exists when two entities are associated in relationship. Ternary Relationship: A ternary relationship implies an association among three different entities.

15. Types of Relationships   A relationship describes an association among entities. One-to-one : Each store be managed by single employee, and each employee manages a single store. Therefore, the relationship “EMPLOYEE manages STORE” is labeled as one-to-one (1:1). One-to-many : A painter paints many different paintings , but each of them is painted by only one painter. Thus , the painter (the “one”) is related to the paintings (the “many”). Therefore, database designer label the relationship “PAINTER paints PAINTINGS” as one-to-many (1:M) relationship Many-to-many : An employee may learn many job skills , and each job skill may be learned by many employees. Database designer label the relationship “EMPLOYEE learns SKILL” as many-to-many (M:N) relationship. (Note: Draw the above Diagram for each type of relation)

16. Generalization: Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common. In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further higher level entity. Generalization is more like subclass and super class system, but the only difference is the approach. Generalization uses the bottom-up approach. In generalization, entities are combined to form a more generalized entity, i.e., subclasses are combined to make a superclass. For example ,  Faculty and Student entities can be generalized and create a higher level entity Person.

17. Specialization: Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one higher level entity can be broken down into two lower level entities. Specialization is used to identify the subset of an entity set that shares some distinguishing characteristics. Normally , superclass is defined first, the subclass and its related attributes are defined next, and relationship set are then added. For example: In an Employee management system , EMPLOYEE entity can be specialized as TESTER or DEVELOPER based on what role they play in the company.

18.   Aggregation: In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity. Relationship sets work and uses could be combined into a single set. Transforming an E-R diagram with aggregation into tabular form is easy. We create a table for each entity and relationship set as before. The table for relationship set uses contains a column for each attribute in the primary key of machinery and work . For example: Center entity offers the Course entity act as a single entity in the relationship which is in a relationship with another entity visitor. In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only or just about the Center instead he will ask the enquiry about both.

19. Composition Composition is a form of aggregation that represents an association between entities, where there is a strong ownership between the ‘whole’ and the ‘part’. For example, a tree and a branch have a composition relationship. A branch is ‘part’ of a ‘whole’ tree - we cannot cut the branch and add it to another tree.

20. CODD’S RELATIONAL DATABASE RULES In 1985, Dr. E.F. Codd published a list of 12 rules to define a relational database system. Dr.Codd’s list, shown in below table, serves as a frame of reference for what a truly relational database should be. Rule Rule Name Description 1 Information All information in a relational database must be logically represented as column values in rows within tables. 2 G uaranteed Access Every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name. 3 Systematic Treatment of Nulls Nulls must be represented and treated in systematic way, independent of data type. 4 Dynamic On-Line Catalog Based on the Relational Model The metadata must be stored and managed as ordinary data, that is, in tables within the database. Such data must be available to authorized users using the standard database 5 Comprehensive Data Sublanguage The relational database may support many languages. However, it must support one well defined, declarative language with support for data definition, view definition, data manipulation, integrity constraints, authorization .

21. 6 View Updating Any view that is theoretically updatable must be updatable through the system. 7 High-Level Insert, Update and Delete The database must support set-level inserts, updates, and deletes 8 Physical Data Independence Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed. 9 Logical Data Independence A pplication programs and ad hoc facilities are logically unaffected when changes are made to the table structure that preserves the original table values. 10 Integrity Independence All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level 11 Distribution Independence The end users and application programs are unaware and unaffected by the data location (distributed vs. local ) 12 Nonsubversion If the system supports low-level access to the data, there must not be a way to bypass the integrity rules of the   Rule Zero All preceding rules are based on the notation that in order for a database to be considered relational, it must use its Re lational facilities exclusively to manage the database.

22. Advantages and Disadvantages of E-R Data Model A dvantages of an E-R Model : Straightforward relation representation : Having designed an E-R diagram for a database application, the relational representation of the database model becomes relatively straightforward. Easy conversion for E-R to other data model: Conversion from E-R diagram to a network or hierarchical data model can· easily be accomplished. Graphical representation for better understanding: An E-R model gives graphical and diagrammatical representation of various entities, its attributes and relationships between entities. This is turn helps in the clear understanding of the data structure and in minimizing redundancy and other problems. Disadvantages of E-R Data Model No industry standard for notation : There is no industry standard notation for developing an E-R diagram. Popular for high-level design : The E-R data model is especially popular for high level.

23. Relational Data Model Relational database model is a type of database that stores information in the form of logically related two-dimensional tables. The term relational stems from the fact that each table in the database contains information related to a single subject and only that subject. For example, a data set containing all the real estate transactions in a town can be grouped by the year the transaction occurred; or it can be grouped by the sale price of the transaction; or it can be grouped by the buyer‘s last name; and so on. Such a grouping uses the relational model (a technical term for this schema). Hence such a database is called a "relational database.“ Relational model stores data in the form of tables. This concept purposed by Dr. E.F. Codd , a researcher of IBM in the year 1960s. The relational model consists of three major components: 1. The set of relations and set of domains that defines the way data can be represented (data structure). 2. Integrity rules that define the procedure to protect the data (data integrity). 3. The operations that can be performed on data (data manipulation). A rational model database is defined as a database that allows you to group its data items into one or more independent tables that can be related to one another by using fields common to each related table.

24. Characteristics of Relational Database Relational database systems have the following characteristics: A table is a two-dimensional structure composed of rows and columns. Each table row (tuple) represents a single entity occurrence within the entity set. Each table column represents attribute, and each column has a distinct name. Each row/column intersection represents a single data value. All values in a column must conform to the same data format. Each column has a specific range of values known as the attribute domain. The whole data is conceptually represented as an orderly arrangement of data into rows and columns,called a relation or table. All values are scalar. That is, at any given row/column position in the relation there is one and only one value. All operations are performed on an entire relation and result is an entire relation, a concept known as closure. The order of columns and rows is immaterial to the DBMS.

25. Relational Integrity Relational integrity constraint is used to ensure accuracy and consistency of data in a relational database. Information Integrity is the trustworthiness and dependability of information. More specifically, it is the accuracy, consistency and reliability of the information content, processes and systems. Information integrity is also a prerequisite, because you need it for many other management decisions. If certain information cannot be trust and has a low level of integrity than a business has a low chance of success. You need information integrity to have a successful business. Integrity constraints are sets of rules that can help maintain the quality of information that is put up. Integrity constraints are mostly used when trying to promote accuracy and consistency of data that is found in a relational database. This is very important to companies because information can be considered as an asset to certain organizations and it must be protected. Therefore, relational Integrity constraints are rules which all instances of the relational Database must satisfy in order to correctly model the real world. A relational database schema (i.e. Database definition) consists of – relation schemas (table definitions) – integrity constraints Any operation that would violate a declared constraint will be disallowed. The relationship between Integrity and Database design is very important, as many real-world constraints are imposed by a combination of – good design of relations (such as via ER modeling), plus – maintenance of key (uniqueness) constraints