Database Design Process Requirements analysis Entity-Relationship - PDF document

Database Design Process � Requirements analysis Entity-Relationship Model � Conceptual design � data model � Logical design � Schema refinement: Normalization From Chapter 5, Kroenke book � Physical tuning Problem: University Database The College Report � Divisions (Colleges) � Departments � Faculty � Students 1

The Department Report The Department Major Report 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? 2

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. Identifiers Relationships � Identifiers = attributes that identify entity � Relationships: associations between instances entities � Composite identifiers : Identifiers that � No attributes consist of two or more attributes � Relationship degree 3

Cardinality Maximum Cardinality � Maximum � Cardinality means “count” - a number cardinality: � Maximum cardinality maximum number of � Minimum cardinality entity instances that can participate in a relationship � One-to-One [1:1] � One-to-Many [1:N] � Many-to-Many [N:M] Minimum Cardinality HAS-A Relationships � Minimum cardinality: � Previous relationships: HAS-A minimum number of relationships : entity instances that � Each entity instance has a relationship with must participate in a another entity instance: relationship. � An EMPLOYEE has one BADGE � zero [0] � optional � A BADGE has an assigned EMPLOYEE. � one [1] � mandatory 4

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 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 5

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. 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 6

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. Subtypes: IS-A relationships ER Summary � IS-A relationships : a subtype IS A � Entities, attributes, identifiers supertype. � HAS-A Relationships � Supertype and subtypes identifiers are � Degree: binary, ternary identical � Maximum cardinality � Use subtypes if � Minimum cardinality � Have attributes that make sense only for � ID-dependent entities; identifying subtypes relationships � Want to specify a relationship only for subtype � IS-A Relationships or supertype � Inclusive, Exclusive 7

Class Exercise Class Exercise � Drugwarehouse.com has offered you a free life- � Draw ER diagram for a database used to time supply of prescription drugs (no questions manage IT360 class (at least 3 entities) asked) if you design its database schema. Given the rising cost of health care, you agree. Here is � Specify entities, attributes, identifiers the information that you gathered: � Specify relationships � Patients are identified by their SSN, and we also � Specify cardinalities for relationships 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 8