Last Time Why Database Management Systems? IT420: Database - PDF document

Last Time � Why Database Management Systems? IT420: Database Management and � High-level abstractions for data access, Organization manipulation, and administration � Data integrity and security � Performance and scalability Introduction to � Transactions Entity-Relationship Model (Chapter 5) Goals of This Lecture Database Design Process � Database design: Entity-Relationship � Requirements analysis Model � Conceptual design � data model � Logical design � Schema refinement: Normalization � Physical tuning 1

Problem: University Database The College Report � Divisions (Colleges) � Departments � Faculty � Students The Department Report The Department Major Report 2

The Student Acceptance Letter Conceptual Design Overview � Entity-Relationship (ER) Model � What are the entities and relationships for given problem? � What information about these entities and relationships should we store? � What are the integrity constraints or business rules that hold? Data Model ER Model � A data model is a plan, or blueprint, for a � Entity-Relationship model: set of database . concepts and graphical symbols � General � Versions Original E-R model — Peter Chen (1976). Extended E-R model — Extensions to the Chen model. � Abstract (no implementation suggested) � Original E-R model Information Engineering (IE) — James Martin (1990); it uses “crow’s foot” notation, is easier to understand and we will use it. � Extended E-R model � Easy to change IDEF1X — A national standard developed by the NIST Unified Modeling Language (UML) — The � Information Engineering (IE) Object Management Group; it supports object- oriented methodology � IDEF1X � Unified Modeling Language (UML) 3

Entities Attributes � Something that can � Attributes: describe be identified and the the characteristics of users want to track an entity � Entity class � Entity instances: � Entity instance � Same attributes � There are usually � Different values many instances of an entity in an entity class. Entity Attributes Display in Data Identifiers Models � Identifiers = attributes that identify entity instances � Composite identifiers : Identifiers that consist of two or more attributes 4

Relationships Cardinality � Cardinality means “count” - a number � Maximum cardinality � Minimum cardinality Maximum Cardinality Minimum Cardinality � Maximum � Minimum cardinality: cardinality: minimum number of maximum number of entity instances that entity instances that must participate in a can participate in a relationship. relationship � zero [0] � optional � One-to-One [1:1] � one [1] � mandatory � One-to-Many [1:N] � Many-to-Many [N:M] 5

HAS-A Relationships Data Modeling Notation � Previous relationships: HAS-A relationships : � Each entity instance has a relationship with another entity instance: � An EMPLOYEE has one BADGE (b) Crow’s foot version (ERWin) � A BADGE has an assigned EMPLOYEE. Data Modeling Notation: Class Exercise ERwin � Give examples of the following relationships: � Maximum cardinality: � One-to-One � One-to-Many � Many-to-Many � Minimum cardinality � Optional-Optional � Mandatory-Optional � Mandatory-Mandatory 6

ID-Dependent Entities ID-Dependent Entities � ID-dependent entity: entity (child) whose identifier includes the identifier of another entity (parent) A solid line indicates an � Example: identifying relationship � BUILDING : APARTMENT � Minimum cardinality from the ID- dependent entity to the parent is always one Weak Entities Weak Entities (Continued) � A weak entity is an entity whose existence Weak entities must be indicated by an depends upon another entity. accompanying text box in A dashed line Erwin – There is no � All ID-Dependent entities are considered indicates a specific notation for a nonidentifying nonidentifying but weak weak. relationship entity relationship � 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. 7

ID-Dependent and Weak Entities Subtype Entities � ID-Dependent entity: Identifier depends � Subtype entity: special case of a supertype (includes) another identifier entity : � Identifying relationship � STUDENT : � Ex: BUILDING:APARTMENT UNDERGRADUATE or GRADUATE � Weak entity: existence depends on � Supertype: another entity � all common attributes � Ex: MODEL:CAR � [ discriminator attribute] � ID-Dependent � Weak � Subtypes: � specific attributes � Weak does NOT imply ID-Dependent Subtypes: Exclusive or Inclusive 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. 8

Subtypes: IS-A relationships Class Exercise � Drugwarehouse.com has offered you a free life- � IS-A relationships : a subtype IS A time supply of prescription drugs (no questions supertype. asked) if you design its database schema. Given � Supertype and subtypes identifiers are the rising cost of health care, you agree. Here is the information that you gathered: identical � Patients are identified by their SSN, and we also � Use subtypes if store their names and age � Have attributes that make sense only for � Doctors are identified by their SSN, and we also subtypes store their names and specialty � Want to specify a relationship only for subtype � Each patient has one primary care physician � Each doctor has at least one patient or supertype ER Summary For Next Time � Entities, attributes, identifiers � Read Chapter 5 � HAS-A Relationships � Degree: binary, ternary � Maximum cardinality � Minimum cardinality � Weak entities � ID-dependent entities; identifying relationships � IS-A Relationships � Inclusive, Exclusive 9