1 / 41

IT420: Database Management and Organization

This lecture provides an overview of the Entity-Relationship Model and the process of designing a database, including requirements analysis, conceptual design, logical design, and physical tuning.

rosalyna
Download Presentation

IT420: Database Management and Organization

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. IT420: Database Management and Organization Introduction to ER Model Adina Crăiniceanu www.cs.usna.edu/~adina

  2. Lab • Lab 1 due next Tuesday (January 17) before class • You do not have to enforce the rule Landing > Takeoff • Send created database to adina@usna.edu

  3. Last Time • Why Database Management Systems? • Transactions • High-level abstractions for data access, manipulation, and administration • Data integrity and security • Performance and scalability • Microsoft Access

  4. Goals of This Lecture • Understand the Entity-Relationship Model

  5. Database Design Process • Requirements analysis • Conceptual design  data model • Logical design • Schema refinement: Normalization • Physical tuning

  6. Conceptual Design Overview • ER Model • What are the entities and relationships in the enterprise? • What information about these entities and relationships should we store in the database? • What are the integrity constraints or business rules that hold?

  7. Data Model • A data model is a plan, or blueprint, for a database. • General • Abstract (no implementation suggested) • Easy to change

  8. ER Model • Entity-Relationship model: set of concepts and graphical symbols • Versions • Original E-R model— Peter Chen (1976) • Extended E-R model — Extensions to the Chen model • Information Engineering (IE)— James Martin (1990) “Crow’s foot” notation • IDEF1X— A national standard developed by the National Institute of Standards and Technology • Unified Modeling Language (UML)— The Object Management Group; it supports object-oriented methodology

  9. Entities • Something that can be identified and the users want to track • Entity class— a collection of entities of a given type • Entity instance— the occurrence of a particular entity • There are usually many instances of an entity in an entity class.

  10. CUSTOMER

  11. Attributes • Attributes: describe the characteristics of an entity • Entity instances: • Same attributes • Different values

  12. EMPLOYEE:Attributes in Ellipses

  13. EMPLOYEE:Attributes in Rectangle

  14. Identifiers • Identifiers =attributes that identify entity instances • Composite identifiers: Identifiers that consist of two or more attributes • Identifiers in data models become keys in database designs: • Entities have identifiers. • Tables (or relations) have keys.

  15. Entity Attributes Display in Data Models

  16. Relationships • Relationships: associations between entities • Relationship classes: associations among entity classes • Relationship instances: associations among entity instances • No attributes • Relationship degree: Number of entities in the relationship

  17. Binary Relationship

  18. Ternary Relationship

  19. Cardinality • Cardinality means “count,” and is expressed as a number • Maximum cardinality: maximum number of entity instances that can participate in a relationship • Minimum cardinality: minimum number of entity instances that must participate in a relationship

  20. Maximum Cardinality • Maximum cardinality: maximum number of entity instances that can participate in a relationship • One-to-One [1:1] • One-to-Many [1:N] • Many-to-Many [N:M]

  21. The Three Types ofMaximum Cardinality

  22. HAS-A Relationships • Previous relationships: HAS-A relationships: • Each entity instance has a relationship with another entity instance: • An EMPLOYEE has one BADGE • A BADGE has an assigned EMPLOYEE.

  23. Minimum Cardinality • Minimum cardinality: minimum number of entity instances that must participate in a relationship. • zero [0] participation is optional • one [1] participation is mandatory

  24. Indicating Minimum Cardinality • Minimum cardinality of zero [0]: oval next to the optional entity. • Minimum cardinality of one [1]: vertical hash mark next to the required entity.

  25. The Three Types ofMinimum Cardinality

  26. (b) Crow’s foot version (ERWin) Data Modeling Notation

  27. Data Modeling Notation:ERwin

  28. Data Modeling Notation:N:M and O-M Note that: (1) ERwin cannot indicate true minimum cardinalities on N:M relationships (2) Visio introduces the intersection table instead of using a true N:M model

  29. ID-Dependent Entities • ID-dependent entity: entity (child) whose identifier includes the identifier of another entity (parent) • Example: • BUILDING : APARTMENT • PAINTING : PRINT • Minimum cardinality from the ID-dependent entity to the parent is always one

  30. ID-Dependent Entities A solid line indicates an identifying relationship

  31. Weak Entities • A weak entity is an entity whose existence depends upon another entity. • All ID-Dependent entities are considered weak. • But there are also non-ID-dependent weak entities. • The identifier of the parent does not appear in the identifier of the weak child entity.

  32. Weak Entities (Continued) Weak entities must be indicated by an accompanying text box in Erwin – There is no specific notation for a nonidentifying but weak entity relationship A dashed line indicates a nonidentifying relationship

  33. ID-Dependent and Weak Entities • ID-Dependent entity: Identifier depends includes another identifier • Identifying relationship • Ex: BUILDING:APARTMENT • Weak entity: existence depends on another entity • Ex: MODEL:CAR • ID-Dependent  Weak • Weak does NOT imply ID-Dependent

  34. Subtype Entities • Subtype entity: special case of a supertype entity: • STUDENT : UNDERGRADUATE or GRADUATE • Supertype: • all common attributes • [discriminator attribute] • Subtypes: • specific attributes.

  35. Subtypes with a Discriminator

  36. Subtypes: Exclusive or Inclusive • If subtypes are exclusive, one supertype relates to at most one subtype. • If subtypes are inclusive, one supertype can relate to one or more subtypes.

  37. Subtypes: Exclusive or Inclusive

  38. Subtypes: IS-A relationships • IS-A relationships: a subtype IS A supertype. • Supertype and subtypes identifiers are identical • Use subtypes if • Have attributes that make sense only for subtypes • Want to specify a relationship only for subtype or supertype

  39. Class Exercise • Draw ER diagram for a database used to manage IT420 class (at least 3 entities) • Specify entities, attributes, identifiers • Specify relationships • Specify cardinalities for relationships

  40. ER Modeling: Case Study • Drugwarehouse.com has offered you a free life-time supply of prescription drugs (no questions asked) if you design its database schema. Given the rising cost of health care, you agree. Here is the information that you gathered: • Patients are identified by their SSN, and we also store their names and age • Doctors are identified by their SSN, and we also store their names and specialty • Each patient has one primary care physician • Each doctor has at least one patient

  41. For Next Time • Read Chapter 5

More Related