1 / 73

Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems. Entity-Relationship Modeling. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview – Part 1. 3.1 Introduction 3.2 The Entity-Relationship Model 3.3 Entity

sheldone
Download Presentation

Web-Enabled Decision Support Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Web-Enabled Decision Support Systems Entity-Relationship Modeling Prof. Name name@email.com Position (123) 456-7890 University Name

  2. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  3. Overview – Part 2 • 3.14 Enhanced Entity-Relationship Modeling • 3.15 Superclass, Subclass, and Relationships • 3.16 Generalization and Specialization Process • 3.17 Participation and Disjoint Constraints • 3.18 Superclass/Subclass Hierarchy • 3.19 Case Study: Conceptual Design for University Database • 3.20 In-Class Assignment • 3.21 Summary

  4. Introduction • Adata modelis an integrated collection of concepts that represents real world objects, events, and their relationships • There are two types of data models: • Object-based data models • Relation-based data models • In database design, we develop an object-based model first • Entity-relationship model • Functional model • Object-oriented model • Then systematically convert the model into a relation-based model • More suitable for database implementation

  5. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  6. The Entity-Relationship Model • An entity-relationship modeldescribes data in terms of the following: • Entities • Relationship between entities • Attributes of entities • We graphically display an E-R model using an entity-relationship diagram (E-R diagram)

  7. Entity-Relationship Diagram Example of an E-R Diagram

  8. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  9. Entity • An entity is an object that exists and which is distinguishable from other objects • Can be a person, a place, an object, an event, or a concept • Examples: • Person: Student, Employee, Client • Object: Couch, Airplane, Machine • Place: City, Park, Room, Warehouse • Event: War, Marriage, Lease • Concept: Project, Account, Course Entity Representation in an E-R Diagram

  10. Entity (cont.) • An entity typedefines a collection of entities that have the same attributes • An entity instanceis a single item in this collection • An entity set is a set of entity instances • Example: • Entity type: STUDENT • Entity instance: Student with ID number 555-55-5555 • Entity set: Collection of all students

  11. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  12. Attributes • An attributeis a property or characteristic of an entity type • We represent an entity with a set of attributes • Examples: • STUDENT = {Student ID, SSN, Name, Address, Phone, Email, DOB} • ORDER = {Order ID, Date of Order, Amount of Order} • ACCOUNT = {Account Nr, Account type, Balance} • CITY = {Name, State, Population} • Attributes Types: • Simple, composite, single-valued, multi-valued, stored, and derived

  13. Attributes in E-R Diagram Attributes of the STUDENT Entity Type

  14. Simple and Composite Attributes • A simple oran atomic attribute cannot be further divided into smaller components • Examples: • City • State • A composite attribute, however, can be divided into smaller subparts where each subpart represents an independent attribute • Examples: • Name: First Name, Last Name • Address: Street, City, State, Zip

  15. Single-Valued and Multi-Valued Attributes • Single-valued attributes have a single value for an entity instance • Examples: • Major • Date Of Birth • Multi-valuedattributes, on the other hand, may have more than one value for an entity instance • Denoted with a double-lined ellipse • Example: • Languages: Stores the names of the languages that a student speaks

  16. Stored and Derived Attributes • The value of a derivedattribute can be determined by analyzing other attributes • Therefore, no need to store them in the database • Denoted with a dashed ellipse • Example: • Age: Can derived from the current date and the attribute DateOfBirth • An attribute whose value cannot be derived from the values of other attributes is called a storedattribute

  17. Key Attribute • A key attribute (or identifier) is a single attribute or a combination of attributes that uniquely identify an individual instance of an entity type • Underlined in an E-R diagram • Example: • Student: StudentID The Key Attribute

  18. Candidate Key Attribute • Sometimes no single attribute can uniquely identify an instance of an entity type • A composite key is a composite attribute that uniquely identifies each entity instance • Example: • City: {Name, State} The Composite Key Attribute

  19. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  20. Relationships • A relationship is an association among several entities • Examples: • STUDENT “takes” COURSES • CUSTOMER “has” ACCOUNT • Represented by diamond shaped box connected to relating entities Relationship between CUSTOMER and ACCOUNT Entities

  21. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  22. Degree of a Relationship • The number of entity sets that participate in a relationship is called the degree of relationship • Three common degrees in a database: • Unary (degree 1) • An association between two instances of the same entity type • R є E1 x E1 • Binary (degree 2) • An association between two instances of two different types • R є E1 x E2 • Ternary (degree 3) • An association between three instances of three different types • R є E1 x E2 x E3

  23. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  24. Cardinality of a Relationship • Maximum cardinalityrepresents the maximum number of instances of entity B that can be associated with any instance of entity A The Four Types of Relationships between Entity Types

  25. Maximum Cardinality • Relationship types by maximum cardinality: • One-to-One • One-to-Many (and vice-versa) • Many-to-Many Many One Relationship Types Based on Maximum Cardinality

  26. Minimum Cardinality • The minimum cardinality of a relationship is defined as the minimum number of instances of entity B that must be associated with each instance of entity A Optional Mandatory Relationship Types Based on Minimum Cardinality

  27. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  28. Unary Relationships - Examples One-to-One One-to-One Many-to-Many

  29. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  30. Binary Relationships - Examples One-to-One One-to-Many Many-to-Many

  31. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  32. Ternary Relationships - Examples Musical Performance Example Student “Uses” Equipment for a Project Example

  33. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  34. Attributes of Relationships • An attribute on a relationship stores information related to the relationship • Much like attributes on entity types Relationship Attribute Example of an Attribute of a Relationship

  35. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  36. Associative Entities • An associative entity is an entity type that connects the instances of one or more entity types and contains attributes particular to this association • Allows us to store data from relationship attributes more effectively • Relationship meets one of the following conditions: • It is a many-to-many binary relationship • It is a ternary relationship or a relationship of an even higher degree

  37. Associative Entities – Example 1 Many-to-Many Binary Relationship Many-to-Many Binary Relationship Converted to an Associated Entity

  38. Associative Entities – Example 2 Ternary Relationship Ternary Relationship Converted to an Associated Entity

  39. Overview – Part 1 • 3.1 Introduction • 3.2 The Entity-Relationship Model • 3.3 Entity • 3.4 Attributes • 3.5 Relationships • 3.6 Degree of a Relationship • 3.7 Cardinality of a Relationship • 3.8 Unary Relationship • 3.9 Binary Relationship • 3.10 Ternary Relationships • 3.11 Attributes of Relationships • 3.12 Associative Entities • 3.13 Weak Entity Types

  40. Weak Entity Types • A strong entity type exists independent of other entity types • A weak entity typedepends on another entity type • Entity type depends on the identifying owner Weak Entity Weak Entity in an E-R Diagram

  41. Overview – Part 2 • 3.14 Enhanced Entity-Relationship Modeling • 3.15 Superclass, Subclass, and Relationships • 3.16 Generalization and Specialization Process • 3.17 Participation and Disjoint Constraints • 3.18 Superclass/Subclass Hierarchy • 3.19 Case Study: Conceptual Design for University Database • 3.20 In-Class Assignment • 3.21 Summary

  42. Enhanced E-R Modeling • Enhanced Entity Relationships (EER)is the enhanced version of the original E-R model designed to cope-up with demand of complex database requirements by applications like Multimedia, GIS, and CAD • New modeling constructs incorporated into EER: • Superclass • Subclass • EER also introduces two processes: • Specialization • Generalization

  43. Overview – Part 2 • 3.14 Enhanced Entity-Relationship Modeling • 3.15 Superclass, Subclass, and Relationships • 3.16 Generalization and Specialization Process • 3.17 Participation and Disjoint Constraints • 3.18 Superclass/Subclass Hierarchy • 3.19 Case Study: Conceptual Design for University Database • 3.20 In-Class Assignment • 3.21 Summary

  44. Entity Attributes • All the instances of an entity type share the same set of attributes, but each single attribute may not be a required attribute for each instance • Example: • Class, GPA not required for PERSON instances that are faculty E-R Diagram for Entity Type PERSON

  45. Superclass and Subclass • A superclass is an entity type that has one or more distinct subgroups with unique attributes • A subclass isan entity type that shares common attributes or relationships distinct from other subclasses Notations for Superclass and Subclass Relationships

  46. Superclass and Subclass - Example Enhanced E-R Diagram for Entity Type PERSON

  47. Superclass and Subclass Relationships • The entity in a subclass is the same entity in the superclass except it has a distinct role • Relationship between superclass and subclass is one-to-one • Every instance in a subclass is a member of superclass and shares its attributes • Attribute inheritance is the property by which subclass entities inherit attributes of the superclass • Each subclass has attributes and relationships that make it unique

  48. Superclass and Subclass Relationships Example University Example of Superclass/Subclass Relationships

  49. Overview – Part 2 • 3.14 Enhanced Entity-Relationship Modeling • 3.15 Superclass, Subclass, and Relationships • 3.16 Generalization and Specialization Process • 3.17 Participation and Disjoint Constraints • 3.18 Superclass/Subclass Hierarchy • 3.19 Case Study: Conceptual Design for University Database • 3.20 In-Class Assignment • 3.21 Summary

  50. Generalization and Specialization • Specialization and generalization are two processes that help recognize possibilities enabled by superclass/subclass models in the real world • Serve as conceptual models for development of superclass/subclass relationships • Generalization is the process of defining general entity types from a set of specialized entity types by identifying their common characteristics • Specialization is the process of defining one or more subclasses of a superclass by identifying its distinguishing characteristics

More Related