Download Data Modeling with Entity-Relationship (ER) Model: A Comprehensive Guide and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
Subject: DATABASE MANAGEMENT SYSTEM
Subject Code: 22AM 502
UNIT- 2
Data Modeling using the Entity-Relationship (ER) Model: Using High-Level Conceptual Data Models for Database Design, An Example Database Application, Entity Types, Entity Sets, Attributes and Keys, Relationship types, Relationship Sets, Roles and Structural Constraints, Weak Entity Types, Refining the ER Design, ER Diagrams, Naming Conventions and Design Issues, Example of other notation, Relationship types of degree higher than two, Another example. (Text 1: 3.1 to 3.10) Chapter 3 Data Modeling Using the Entity Relationship (ER) Model
- Modeling concepts of the entity–relationship (ER) model , which is a popular high-level conceptual data model.
- This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts.
- the diagrammatic notation associated with the ER model, known as ER diagrams.
- Unified Modeling Language (UML) are becoming increasingly popular in both database and software design. 3.1 Using High-Level Conceptual Data Models for Database Design: The first step shown is requirements collection and analysis.
- During this step, the database designers interview prospective database users to understand and document their data requirements.
- In parallel with specifying the data requirements, it is useful to specify the known functional requirements of the application.
- These consist of the user defined operations (or transactions) that will be applied to the database, including both retrievals and updates.
- In software design , it is common to use data flow diagrams, sequence diagrams, scenarios, and other techniques to specify functional requirements. the next step is Conceptual design.
- To create a conceptual schema for the database, using a high-level conceptual data model. This step is called conceptual design.
- These concepts do not include implementation details, they are usually easier to understand and can be used to communicate with nontechnical users.
- This also serves to confirm that the conceptual schema meets all the identified Functional requirements.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017, The next step in database design is the actual implementation of the database , using a commercial DBMS.
- the conceptual schema is transformed from the high-level data model into the
implementation data model. This step is called logical design or data model
mapping. The last step is the physical design phase
- during which the internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files are specified.
3.2 A Sample Database Application
Figure 3.2 shows how the schema for this database application can be displayed by means of the graphical notation known as ER diagrams.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
- Most attributes have a single value for a particular entity; such attributes are called single- valued. For example , Age is a single-valued attribute of a person.
- In some cases, an attribute can have a set of values for the same entity Such attributes are called multivalued EX: a Colours attribute for a car, or a College degrees attribute for a person. 3) Stored versus Derived Attributes.
- In some cases, two (or more) attribute values are related— Example : the Age and Birth-date attributes of a person.
- For a particular person entity, the value of Age can be determined from the current (today’s) date and the value of that person’s Birth_date.
- The Age attribute is hence called a derived attribute and is said to be derivable from the Birth_date attribute, which is called a stored attribute.
- Some attribute values can be derived from related entities; Example , an attribute Number_of_employees of a DEPARTMENT entity can be derived by counting the number of employees related to (working for) that department. 4) NULL Values
- In some cases, a particular entity may not have an applicable value for an attribute.
- Example: the Apartment_number attribute of an address applies only to addresses that are in apartment buildings and not to other types of residences, such as single-family homes.
- NULL can also be used if we do not know the value of an attribute for a particular entity—for example, if we do not know the home phone number of ‘John Smith’ in Figure 3.3. 5) Complex Attributes. Example, if a person can have more than one residence and each residence can have a single address and multiple phones, an attribute Address phone for a person can be specified as shown in Figure 3.5.4 Both Phone and Address are themselves composite attributes.
3.3.2 Entity Types, Entity Sets, Keys, and Value Sets
- 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 3.6 shows two entity types: EMPLOYEE and COMPANY, and a list of some of the attributes for each.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
- The collection of all entities of a particular entity type in the database at any point in time is called an entity set or entity collection.
- An entity type is represented in ER diagrams5 (see Figure 3.2) as a rectangular box enclosing the entity type name.
- An entity type describes the schema or intension for a set of entities that share the same structure.
- The collection of entities of a particular entity type is grouped into an entity set, which is also called the extension of the entity type.
Key Attributes of an Entity Type.
- An entity type usually has one or more attributes whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute , and its values can be used to identify each entity uniquely.
- For example , the Name attribute is a key of the COMPANY entity type in Figure 3. because no two companies are allowed to have the same name. For the PERSON entity type, a typical key attribute is Ssn (Social Security number).
- In ER diagrammatic notation, each key attribute has its name underlined inside the oval , as illustrated in Figure 3.7(a).
- Some entity types have more than one key attribute. For example, each of the Vehicle_id and Registration attributes of the entity type CAR (Figure 3.7) is a key in its own right.
- The Registration attribute is an example of a composite key formed from two simple component attributes, State and Number, neither of which is a key on its own.
- An entity type may also have no key , in which case it is called a weak entity type (see Section 3.5).
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
- 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.
- In our example , Name is modeled as a composite attribute, whereas Address is not, presumably after consultation with the users.
- An entity type DEPENDENT with attributes Employee, Dependent_name, Sex, Birth_date, and Relationship (to the employee).
3.4 Relationship Types, Relationship Sets, Roles, and Structural
Constraints
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017, 3.4.2 Relationship Degree, Role Names, and Recursive Relationships Degree of a Relationship Type. The degree of a relationship type is the number of participating entity types. Hence, the WORKS_FOR relationship is of degree two. A relationship type of degree two is called binary, and one of degree three is called ternary. An example of a ternary relationship is SUPPLY, shown in Figure 3.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017, An example of a 1:1 binary relationship is MANAGES (Figure 3.12), which relates a department entity to the employee who manages that department. This represents the miniworld constraints that—at any point in time—an employee can manage at most one department and a department can have at most one manager. The relationship type WORKS_ON (Figure 3.13) is of cardinality ratio M:N, because the miniworld rule is that an employee can work on several projects and a project can have several employees. Participation Constraints and Existence Dependencies.
- The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type.
- This constraint specifies the minimum number of relationship instances that each entity can participate in and is some-times called the minimum cardinality constraint.
- There are two types of participation constraints — total and partial
- Total participation is also called existence dependency 3.4.4 Attributes of Relationship Types
- Notice that attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types.
- For a 1:N relationship type, a relationship attribute can be migrated only to the entity type on the N-side of the relationship.
- For M:N (many-to-many) relationship types, some attributes may be determined by the combination of participating entities in a relationship instance, not by any single entity. Such attributes must be specified as relationship attributes.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
3.5 Weak Entity Types
- Entity types that do not have key attributes of their own are called weak entity types.
- In contrast, regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types.
- Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in com bination with one of their attribute values.
- We call this other entity type the identifying or owner entity type ,10 and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type.
3.6 Refining the ER Design for the COMPANY Database
In our example, we specify the following relationship types: ■ MANAGES, which is a 1:1(one-to-one) relationship type between EMPLOYEE and DEPARTMENT. EMPLOYEE participation is partial. DEPARTMENT participation is not clear from the requirements. We question the users, who say that a department must have a manager at all times, which implies total participation.13 The attribute Start_date is assigned to this relationship type. ■WORKS_FOR, a 1:N (one-to-many) relationship type between DEPARTMENT and EMPLOYEE. Both participations are total. ■ CONTROLS, a 1:N relationship type between DEPARTMENT and PROJECT. The participation of PROJECT is total, whereas that of DEPARTMENT is determined to be partial, after consultation with the users indicates that some departments may control no projects. ■ SUPERVISION, a 1:N relationship type between EMPLOYEE (in the supervi sor role) and EMPLOYEE (in the supervisee role). Both participations are determined to be partial, after the users indicate that not every employee is a supervisor and not every employee has a supervisor. ■WORKS_ON, determined to be an M:N (many-to-many) relationship type with attribute Hours, after the users indicate that a project can have several employees working on it. Both participations are determined to be total. ■ DEPENDENTS_OF, a 1:N relationship type between EMPLOYEE and DEPENDENT, which is also the identifying relationship for the weak entity type DEPENDENT. The participation of EMPLOYEE is partial, whereas that of DEPENDENT is total.
3.7 ER Diagrams, Naming Conventions, and Design Issues
3.7.1 Summary of Notation for ER Diagrams
- Weak entity types are distinguished by being placed in double rectangles and by having their identifying relationship placed in double diamonds, as illustrated by the DEPENDENT entity type and the DEPENDENTS_OF identifying relationship type. 3.7.2 Proper Naming of Schema Constructs
- We choose to use singular names for entity types, rather than plural ones, because the entity type name applies to each individual entity belonging to that entity type.
- Another naming consideration involves choosing binary relationship names to make the ER diagram of the schema readable from left to right and from top to bot tom.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017, 3.7.4 Alternative Notations for ER Diagrams There are many alternative diagrammatic notations for displaying ER diagrams. Appendix A gives some of the more popular notations. In Section 3.8, we introduce the Unified Modeling Language (UML) notation for class diagrams, which has been proposed as a standard for conceptual object Modeling.
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017,
3.8 Example of Other Notation: UML Class Diagrams
- The top section gives the class name (similar to entity type name);
- the middle section includes the attributes ;
- the last section includes operations that can be applied to individual objects (similar to individual entities in an entity set) of the class.
- in Figure 3.16. A composite attribute is modeled as a structured domain ,
- A multivalued attribute will generally be modeled as a separate class, as illustrated by the LOCATION class in Figure 3.16.
- Relationship types are called associations in UML terminology, and relationship instances are called links.
- A binary association (binary relationship type) is represented as a line connecting the participating classes (entity types), and may option ally have a name.
- A relationship attribute, called a link attribute , is placed in a box that is connected to the association’s line by a dashed line.
- The (min, max) notation described in Section 3.7.4 is used to specify relationship constraints, which are called multiplicities in UML terminology.
- A recursive relationship type (see Section 3.4.2) is called a reflexive association in UML
- Weak entities can be modeled using the UML construct called qualified association (or qualified aggregation)
3.9 Relationship Types of Degree Higher than Two
3.9.1 Choosing between Binary and Ternary (or Higher-Degree) Relationships
Asst. Prof. KAVYA R AI&ML Department SSIT-TUMKUR 1 Fundamentals of Database Systems Elmasri and Navathe 7th Edition, Pearson Education, 2017, 3.9.2 Constraints on Ternary (or Higher-Degree) Relationships
3.10 Another Example: A UNIVERSITY Database
