ER Model.pdf

Conceptual Database Design - Entity Relationship(ER) Modeling: Database Design Techniques 1. ER Modeling (Top down Approach) 2. Normalization (Bottom Up approach) What is ER Modeling? A graphical technique for...

Conceptual Database Design - Entity Relationship(ER) Modeling: Database Design Techniques 1. ER Modeling (Top down Approach) 2. Normalization (Bottom Up approach) What is ER Modeling? A graphical technique for understanding and organizing the data independent of the actual database implementation We need to be familiar with the following terms to go further. Entity Any thing that has an independent existence and about which we collect data. It is also known as entity type. In ER modeling, notation for entity is given below. Database System Concepts - 6 th 1.1 ©Silberschatz, Korth and  Entity Types and Entity Sets. A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribute. An entity type defines a collection (or set) of entities that have the same attributes. Each entity type in the database is described by its name and attributes. Figure 7.6 shows two entity types: EMPLOYEE and COMPANY, and a list of some of the attributes for Database System Concepts - 6 th 1.2 ©Silberschatz, Korth and Database System Concepts - 6 th 1.3 ©Silberschatz, Korth and  Entity instance Entity instance is a particular member of the entity type. Example for entity instance : A particular employee Regular Entity An entity which has its own key attribute is a regular entity. Example for regular entity : Employee. Weak entity An entity which depends on other entity for its existence and doesn't have any key attribute of its own is a weak entity. Example for a weak entity : In a parent/child relationship, a parent is considered as a strong entity and the child is a weak entity. In ER modeling, notation for weak entity is given below. Database System Concepts - 6 th 1.4 ©Silberschatz, Korth and  What is Attributes? Attributes are the properties that define the entity type. Each entity has attributes—the particular properties that describe it. For example, an EMPLOYEE entity may be described by the employee’s name, age, address, salary, and job. A particular entity will have a value for each of its attributes. The attribute values that describe each entity become a major part of the data stored in the database. Several types of attributes occur in the ER model: simple versus composite, single valued versus multivalued, and stored versus derived. Composite versus Simple (Atomic) Attributes. Single-Valued versus Multivalued Attributes Stored versus Derived Attributes. NULL attributes. Key attribute Complex Attributes. Database System Concepts - 6 th 1.5 ©Silberschatz, Korth and Database System Concepts - 6 th 1.6 ©Silberschatz, Korth and Value Sets (Domains) of Attributes. Each simple attribute of an entity type is associated with a value set (or domain of values), which specifies the set of values that may be assigned to that attribute for each individual entity. In Figure 7.6, if the range of ages allowed for employees is between 16 and 70, we can specify the value set of the Age attribute of EMPLOYEE to be the set of integer numbers between 16 and 70. Similarly, we can specify the value set for the Name attribute to be the set of strings of alphabetic characters separated by blank characters, and so on. Value sets are not displayed in ER diagrams, and are typically specified using the basic data types available in most programming languages, such as integer, string, Boolean, float, numerated type, subrange, and so on. Additional data types to represent common database types such as date, time, and other concepts are also employed. Database System Concepts - 6 th 1.7 ©Silberschatz, Korth and  Relationships Associations between entities are called relationships Example : An employee works for an organization. Here "works for" is a relation between the entities employee and organization. In ER modeling, notation for relationship is given below. Database System Concepts - 6 th 1.8 ©Silberschatz, Korth and Database System Concepts - 6 th 1.9 ©Silberschatz, Korth and Database System Concepts - 6 th 1.10 ©Silberschatz, Korth and  However in ER Modeling, To connect a weak Entity with others, you should use a weak relationship notation as given below Degree of a Relationship Degree of a relationship is the number of entity types involved. The n-ary relationship is the general form for degree n. Special cases are unary, binary, and ternary ,where the degree is 1, 2, and 3, respectively. Example for unary relationship : An employee is a manager of another employee Example for binary relationship : An employee works-for department. Example for ternary relationship : customer purchase item from a shop keeper Database System Concepts - 6 th 1.11 ©Silberschatz, Korth and Database System Concepts - 6 th 1.12 ©Silberschatz, Korth and Database System Concepts - 6 th 1.13 ©Silberschatz, Korth and Database System Concepts - 6 th 1.14 ©Silberschatz, Korth and Database System Concepts - 6 th 1.15 ©Silberschatz, Korth and  Cardinality of a Relationship Relationship cardinalities specify how many of each entity type is allowed. Relationships can have four possible connectivities as given below. 1. One to one (1:1) relationship 2. One to many (1:N) relationship 3. Many to one (M:1) relationship 4. Many to many (M:N) relationship The minimum and maximum values of this connectivity is called the cardinality of the relationship Example for Cardinality – One-to-One (1:1) Employee is assigned with a parking space. Database System Concepts - 6 th 1.16 ©Silberschatz, Korth and Database System Concepts - 6 th 1.17 ©Silberschatz, Korth and  Example for Cardinality – One-to-Many (1:N) Organization has employees Database System Concepts - 6 th 1.18 ©Silberschatz, Korth and Database System Concepts - 6 th 1.19 ©Silberschatz, Korth and  Example for Cardinality – Many-to-One (M :1) It is the reverse of the One to Many relationship. employee works in organization Database System Concepts - 6 th 1.20 ©Silberschatz, Korth and Database System Concepts - 6 th 1.21 ©Silberschatz, Korth and  Cardinality – Many-to-Many (M:N) Students enrolls for courses Database System Concepts - 6 th 1.22 ©Silberschatz, Korth and Database System Concepts - 6 th 1.23 ©Silberschatz, Korth and  Relationship Participation 1. Total In total participation, every entity instance will be connected through the relationship to another instance of the other participating entity types 2. Partial Example for relationship participation Consider the relationship - Employee is head of the department. Here all employees will not be the head of the department. Only one employee will be the head of the department. In other words, only few instances of employee entity participate in the above relationship. So employee entity's participation is partial in the said relationship. However each department will be headed by some employee. So department entity's participation is total in the said relationship. Database System Concepts - 6 th 1.24 ©Silberschatz, Korth and Database System Concepts - 6 th 1.25 ©Silberschatz, Korth and  Weak Entity Set in ER diagrams ▪ An entity type should have a key attribute which uniquely identifies each entity in the entity set, but there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type. ▪ The entity sets which do not have sufficient attributes to form a primary key are known as weak entity sets and the entity sets which have a primary key are known as strong entity sets. ▪ As the weak entities do not have any primary key, they cannot be identified on their own, so they depend on some other entity (known as owner entity). The weak entities have total participation constraint (existence dependency) in its identifying relationship with owner identity. Weak entity types have partial keys. Partial Keys are set of attributes with the help of which the tuples of the weak entities can be distinguished and identified. ▪ Note – Weak entity always has total participation but Strong entity may not have total participation. ▪ Weak entity is depend on strong entity to ensure the existence of weak entity. Like strong entity, weak entity does not have any primary key, It has partial discriminator key. Weak entity is represented by double rectangle. The relation between one strong and one weak entity is represented by double diamond. Database System Concepts - 6 th 1.26 ©Silberschatz, Korth and  Weak entities are represented with double rectangular box in the ER Diagram and the identifying relationships are represented with double diamond. Partial Key attributes are represented with dotted lines. Database System Concepts - 6 th 1.27 ©Silberschatz, Korth and Database System Concepts - 6 th 1.28 ©Silberschatz, Korth and Database System Concepts - 6 th 1.29 ©Silberschatz, Korth and  How to Draw ER Diagram? The very first step is Identifying all the Entities, and place them in a Rectangle, and labeling them accordingly. The next step is to identify the relationship between them and place them accordingly using the Diamond, and make sure that, Relationships are not connected to each other. Attach attributes to the entities properly. Remove redundant entities and relationships. Database System Concepts - 6 th 1.30 ©Silberschatz, Korth and Database System Concepts - 6 th 1.31 ©Silberschatz, Korth and  Database ▪ We can now define the entity types for the COMPANY database, ▪ 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. ▪ 2. An entity type PROJECT with attributes Name, Number, Location, and ▪ Controlling_department. Both Name and Number are (separate) key attributes. ▪ 3. An entity type EMPLOYEE with attributes Name, Ssn, Sex, Address, Salary, ▪ Birth_date, Department, and Supervisor. Both Name and Address may be composite attributes; however, this was not specified in the requirements. We must go back to the users to see if any of them will refer to the individual components of Name—First_name, Middle_initial, Last_name—or of Address. ▪ 4. An entity type DEPENDENT with attributes Employee, Database System Concepts - 6 th 1.32 ©Silberschatz, Korth and  Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students’ transcripts. This is similar but not identical to the database shown in Figure 1.2: a. The university keeps track of each student’s name, student number, Social Security number, current address and phone number, permanent address and phone number, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Some user applications need to refer to the city, state, and ZIP Code of the student’s permanent address and to the student’s last name. Both Social Security number and student number have unique values for each student. b. Each department is described by a name, department code, office number, office phone number, and college. Both name and code have unique values for each department. c. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of the course number is unique for each course. d. Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3,..., up to the number of sections taught during each semester. e. A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, or 4). Design an ER schema for this application, and1.33draw an ER diagram for ©Silberschatz, Database System Concepts - 6 th the schema. Korth and Database System Concepts - 6 th 1.34 ©Silberschatz, Korth and Database System Concepts - 6 th 1.35 ©Silberschatz, Korth and Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans. a. List the strong (nonweak) entity types in the ER diagram. b. Is there a weak entity type? If so, give its name, partial key, and identifying relationship. c. What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram? d. List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices. e. List concisely the user requirements that led to this ER schema design. f. Suppose that every customer must have at least one account but is restricted to at most two loans at a time, and that a bank branch cannot have more than 1,000 loans. How does this show up on the (min, max) constraints? Database System Concepts - 6 th 1.36 ©Silberschatz, Korth and Database System Concepts - 6 th 1.37 ©Silberschatz, Korth and Database System Concepts - 6 th 1.38 ©Silberschatz, Korth and  Extended Entity-Relationship (EE-R) Model ▪ EER is a high-level data model that incorporates the extensions to the original ER model. Enhanced ERD are high level models that represent the requirements and complexities of complex database. ▪ In addition to ER model concepts EE-R includes − ▪ Subclasses and Super classes. ▪ Specialization and Generalization. ▪ Category or union type. ▪ Aggregation. ▪ These concepts are used to create EE-R diagrams. Database System Concepts - 6 th 1.39 ©Silberschatz, Korth and  Subclasses and Super class Super class is an entity that can be divided into further subtype. For example − consider Shape super class. Database System Concepts - 6 th 1.40 ©Silberschatz, Korth and  Specialization and Generalization ▪ Generalization is a process of generalizing an entity which contains generalized attributes or properties of generalized entities. Database System Concepts - 6 th 1.41 ©Silberschatz, Korth and  ▪ It is a Bottom up process i.e. consider we have 3 sub entities Car, Truck and Motorcycle. Now these three entities can be generalized into one super class named as Vehicle. ▪ Specialization is a process of identifying subsets of an entity that share some different characteristic. It is a top down approach in which one entity is broken down into low level entity. Database System Concepts - 6 th 1.42 ©Silberschatz, Korth and  Modeling of UNION Types Using Categories All of the superclass/subclass relationships we have seen thus far have a single super-class. A shared subclass such as ENGINEERING_MANAGER in the lattice in Figure 8.6 is the subclass in three distinct superclass/subclass relationships, where each of the three relationships has a single superclass. However, it is sometimes necessary to represent a single superclass/subclass relationship with more than one superclass, where the superclasses represent different entity types. In this case, the subclass will represent a collection of objects that is a subset of the UNION of distinct entity types; we call such a subclass a union type or a category. Database System Concepts - 6 th 1.43 ©Silberschatz, Korth and  For example, suppose that we have three entity types: PERSON, BANK, and COMPANY. In a database for motor vehicle registration, an owner of a vehicle can be a person, a bank (holding a lien on a vehicle), or a company. We need to create a class (collection of entities) that includes entities of all three types to play the role of vehicle owner. A category (union type) OWNER that is a subclass of the UNION of the three entity sets of COMPANY, BANK, and PERSON can be created for this purpose. We display categories in an EER diagram as shown in Figure 8.8. The superclasses COMPANY, BANK, and PERSON are connected to the circle with the ∪ symbol, which stands for the set union operation. An arc with the subset symbol connects the circle to the (subclass) OWNER category. If a defining predicate is needed, it is dis-played next to the line from the superclass to which the predicate applies. In Figure 8.8 we have two categories: OWNER, which is a subclass of the union of PERSON, BANK, and COMPANY; and REGISTERED_VEHICLE, which is a subclass of the union of CAR and TRUCK. Database System Concepts - 6 th 1.44 ©Silberschatz, Korth and Database System Concepts - 6 th 1.45 ©Silberschatz, Korth and  Aggregation Database System Concepts - 6 th 1.46 ©Silberschatz, Korth and Database System Concepts - 6 th 1.47 ©Silberschatz, Korth and  ▪ Represents relationship between a whole object and its component. ▪ Consider a ternary relationship Works_On between Employee, Branch and Manager. Now the best way to model this situation is to use aggregation, So, the relationship-set, Works_On is a higher level entity-set. Such an entity-set is treated in the same manner as any other entity-set. We can create a binary relationship, Manager, between Works_On and Manager to represent who manages what tasks. Database System Concepts - 6 th 1.48 ©Silberschatz, Korth and Database System Concepts - 6 th 1.49 ©Silberschatz, Korth and Database System Concepts - 6 th 1.50 ©Silberschatz, Korth and  Transforming ER diagram into the tables. / ER & EER to Relational Mapping After designing an ER Diagram, ER diagram is converted into the tables in relational model. This is because relational models can be easily implemented by RDBMS like MySQL , Oracle etc. Following rules are used for converting an ER diagram into the tables- Rule-01: For Strong Entity Set With Only Simple Attributes- A strong entity set with only simple attributes will require only one table in relational model. Attributes of the table will be the attributes of the entity set. The primary key of the table will be the key attribute of the entity set. Database System Concepts - 6 th 1.51 ©Silberschatz, Korth and Database System Concepts - 6 th 1.52 ©Silberschatz, Korth and  Rule-02: For Strong Entity Set With Composite Attributes- Database System Concepts - 6 th 1.53 ©Silberschatz, Korth and  Rule-03: For Strong Entity Set With Multi Valued Attributes- Database System Concepts - 6 th 1.54 ©Silberschatz, Korth and  Rule-04: Translating Relationship Set into a Table- Database System Concepts - 6 th 1.55 ©Silberschatz, Korth and  NOTE- If we consider the overall ER diagram, three tables will be required in relational model- One table for the entity set “Employee” One table for the entity set “Department” One table for the relationship set “Works in” Database System Concepts - 6 th 1.56 ©Silberschatz, Korth and  Rule-05: For Binary Relationships With Cardinality Ratios- The following four cases are possible- ▪ Case-01: Binary relationship with cardinality ratio m:n ▪ Case-02: Binary relationship with cardinality ratio 1:n ▪ Case-03: Binary relationship with cardinality ratio m:1 ▪ Case-04: Binary relationship with cardinality ratio 1:1 Database System Concepts - 6 th 1.57 ©Silberschatz, Korth and Database System Concepts - 6 th 1.58 ©Silberschatz, Korth and Database System Concepts - 6 th 1.59 ©Silberschatz, Korth and Database System Concepts - 6 th 1.60 ©Silberschatz, Korth and Database System Concepts - 6 th 1.61 ©Silberschatz, Korth and  Thumb Rules to Remember While determining the minimum number of tables required for binary relationships with given cardinality ratios, following thumb rules must be kept in mind- For binary relationship with cardinality ration m : n , separate and individual tables will be drawn for each entity set and relationship. For binary relationship with cardinality ratio either m : 1 or 1 : n , always remember “many side will consume the relationship” i.e. a combined table will be drawn for many side entity set and relationship set. For binary relationship with cardinality ratio 1 : 1 , two tables will be required. You can combine the relationship set with any one of the entity sets. Database System Concepts - 6 th 1.62 ©Silberschatz, Korth and  Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation Constraints- Cardinality constraints will be implemented as discussed in Rule-05. Because of the total participation constraint, foreign key acquires NOT NULL constraint i.e. now foreign key can not be null. Database System Concepts - 6 th 1.63 ©Silberschatz, Korth and  Case-01: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From One Side- Database System Concepts - 6 th 1.64 ©Silberschatz, Korth and Database System Concepts - 6 th 1.65 ©Silberschatz, Korth and Database System Concepts - 6 th 1.66 ©Silberschatz, Korth and Database System Concepts - 6 th 1.67 ©Silberschatz, Korth and Database System Concepts - 6 th 1.68 ©Silberschatz, Korth and  Generalization Database System Concepts - 6 th 1.69 ©Silberschatz, Korth and Database System Concepts - 6 th 1.70 ©Silberschatz, Korth and Database System Concepts - 6 th 1.71 ©Silberschatz, Korth and