IT420: Database Management and Organization

1
IT420 Database Management and Organization

• Introduction to ER Model
• Adina Crainiceanu
• 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 gt
  Takeoff
• Send created database to adina_at_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) Crows 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
EMPLOYEEAttributes in Ellipses
13
EMPLOYEEAttributes 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 11
• One-to-Many 1N
• Many-to-Many NM

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
Data Modeling Notation
27
Data Modeling NotationERwin
28
Data Modeling NotationNM and O-M

• Note that
• (1) ERwin cannot indicate true minimum
  cardinalities on NM relationships
• (2) Visio introduces the intersection table
  instead of using a true NM 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 BUILDINGAPARTMENT
• Weak entity existence depends on another entity
• Ex MODELCAR
• 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