Ch3_ Data Modeling Using the ER

Document Sample
Ch3_ Data Modeling Using the ER Powered By Docstoc
					        Chapter 7
Data Modeling Using the ERD
reza@aero.und.edu




                              1
 Overview of Database Design
 Role of conceptual data models in database design
 Application of ER models concepts
 ER notations
 Using UML to Design DB




                                                      2
1.   Requirement collection and analysis
2.   Conceptual Database Design
3.   Logical Database Design (data model mapping)
4.   Schema Refinement
5.   Physical Database design
6.   Security Design




                                                    3
4
 Revolves around discovering and analyzing
  organizational and user data requirements
 The important activities are to identify
     Entities
     Attributes
     Relationships
     Constraints
     ER diagrams




                                              5
 Formalizing organizational data requirements
   Helps users and system developers identify data
    requirements (abstract model)
   Helps in understanding how existing systems can be
    modified/maintained




                                                         6
 The COMPANY database keeps track of a company’s
 employees, departments, and projects
   The company is organized into department
   A department control a number of projects
   Each project has a unique name, ID, and a single
    location
   Employee may have dependent, so need to keep track of
    dependent




                                                            7
 Specification of dept. entity
   a unique name and number,
   start_date,
   a set of of locations
   a manager
 Business rule:
   Each dept. controls a number of projects




                                               8
 Each Project has
   a unique name
   a number
   a single location
 Rule:
   Each project can be done by one or more department
   Each project has a controlling department




                                                         9
 Specification of Employee
   SSN, address,
   salary, sex, and Birthdates
 Business rules :
   an employee is assigned to only one dept.
   an employee may work on several projects (not
    necessarily controlled by the same dept.)
   an employee works certain hrs per week
   an employee has a direct supervisor
   an employee may have dependent (s )


                                                    10
 Each dependent has
   name
   Sex
   birthrate,
   relationship to the employee




                                   11
12
 Entity
    the basic object represented by ER model
    “thing” or object in the real world
    E.g.,
       Car, Course, Faculty, Book, Office
 Attributes
    properties used to describe entity
    E.g.,
       name, age, address
 An entity will have a value for each of its attributes




                                                           13
14
 simple v.s. composite
   E.g., firstname vs. address
 single-valued v.s multi-valued
   E.g. firstname vs. degree
 stored v.s. derived
   E.g. Birthdate vs. age




                                   15
 attributes that can be divided into smaller subparts or
  more basic attributes
 e.g.,
   Address attributes of the employee
      “2311 Main street, Fargo, ND 58102 “
   City, State, and Zip-code with values : “2311 Main”,
    “Fargo”, “ND”, and “58102”




                                                            16
 attributes that can not be decomposed any further
   e.g.,
      street,
      apartment #




                                                      17
 attributes having single value for a particular entity
   e.g.,
      Age,
      SSN




                                                           18
 attributes having a set of values for the same entity
   e.g.,
      A Color attribute for a car,
      A College Degree for a person
      A house

 may have lower and upper bounds on the number of
  values for an individual entity
   e.g.,
      the Colors attributes of a car may have [1-5] values




                                                              19
 Derived
   attributes that can be extracted from some other attributes
   e.g.,
      the value of Age attributes from Birthday and current day

 Stored
       Birthdates is called a Stored attribute

 null value
    not applicable
    unknown
    missing



                                                                   20
 An entity type
   defines a set of entities that have same attributes
 Intention (type) v.s. extension (state)
 Key attributes
   single attribute (or the combination of some attributes) used to
    identify each entity uniquely
 Value sets (or domains of Attributes)
    A set of all possible values that can be assigned to an attributes




                                                                          21
22
23
 Relationship type
   Defines a set of possible associations among a set of
    entity types E1,E2,…,En.
   R ={r1,…, rn} is a set of relationship instance ri,
      where each ri associates n entities




                                                            24
25
26
 The degree of a relationship type is the number of
 participating entity types
   Binary relationships
   Ternary relationships
   n-nary relationships




                                                       27
 The restrictions on the possible links among entities
  (objects)
 Come from the requirements
   E.g. of Constraint
      An employee cannot work for more than one department




                                                              28
 Cardinality
  The maximum number of records in
   one file linked to a single record in
   another file and vice versa
  Driven from requirements




                                           29
 Cardinality ratio
   1:1
       e.g., MANGES relationship
   1:N
       e.g., WORKS_FOR relationship
   M:N
       e.g., WORKS_ON relationship,




                                       30
 Participating Constraints
   specifies whether the existence of an entity depends on
    its being related to anther entity u
   Total vs. partial
      e.g., WORKS_FOR (total)
      e.g., MANAGES (partial)

 Structural constraints
   cardinality ratio + participation constraints




                                                              31
 A relationship type with the same participating entity
 type in distinct roles
   Example:
      The SUPERVISION relationship
      EMPLOYEE participates twice in two distinct roles:
         supervisor (or manager) role
         supervisee (or subordinate) role

 Each relationship instance relates two distinct
 EMPLOYEE entities:
   One employee in supervisor role
   One employee in supervisee role


                                                            32
 Recursive Relationships
   When an entity participates in a relationship with itself




 EMPLOYEE               MARRIED




                                                                33
34
35
36
 Key concepts
   Primary Key (PK)
   Optional-max conversion
   Foreign key (FK)
   Intersection table




                              37
 Three basic rules to convert ER into tables:
   For 1:1 cardinality relationships,
      Merge all the attributes into a single table (bad)
      Or Use Foreign key/primary key approach (good)
   For 1:N cardinality relationship,
      Post the PK from the “one” side as an attribute to the “many”
       side.
   For N:M cardinality relationships,
      Create a new table (intersection)
      Post the primary keys from each entity as attributes in the new
       table



                                                                         38
 The upper bound is used to determine what relational
  tables will be created
 The lower bound is used to determine if the upper
  bound should be treated as a “many”
   E.g., when the lower cardinality bounds are optional (0),
    and upper cardinality bounds are one (1)




                                                                39
 Construct an ER diagram, and label the upper bounds
    All multi-valued attributes should be represented as an entity
 If the lower bound = 0 and the upper bound = 1, then
  label the upper bound as N.
 Use entity name for each entity as the table name
 select an PK for each table
 Take all the attributes that describe an entity and post
  them as column names in the relational table.




                                                                      40
 For 1:1, merge all the attributes into a single table or use
  PK /FK approach
 For 1:N, take the ID of the “one” side of the 1:N and post
  it as an attributes to the “many” side.
 For N:M, create a new table (intersection) and use the
  relationship name as the name for the table
 Take any additional attributes that describe entities or
  relationships and post these as column names in the
  appropriate relational table.
    If there are any composite attributes in the
      Relational table definition, decompose them into
      simple or atomic attributes.

                                                                 41
 Draw ER diagram for the mini University problem with
 following business rules
   Each faculty member can teach between 0 - 4 courses
   Each course can be taught by at most one (i.e., o-1)
    faculty
   Each student enrolls in 0 to 7 courses
   Each course can be taken by many students




                                                           42
 A student has one and only one home address
 A home address assigned to one and only one student
 A student has one and only one major
 A major can be taken by many students
 A student assigned to only one locker
 A locker assigned to only one student




                                                        43
44
45
46
47
48
49
 Suppose tables have the following attributes:
   FACULTY:
      FID, FNAME, LNAME
   COURSE
      CID,CNAME,CREDIT
   STUDENT
      SID,FNAME,LNAME
   MAJOR
      MAJID,DESCRIPTIO
   LOCKER
      LID, BUILDING
   HOME-ADDRESS
      HSTREET,HCITY,HSTATE




                                                  50
 One to one
   One table
   E.g.
       Student (SID, F_NAME, …, HSTREET, HCITY, HSTATE)
 One to many
   Two tables:
       STUDENT (SID, MAJID, F_NAME,…),
       MAJOR (MAJID, …)


 Many to many
   Create intersection table
       SUDENT (SID…),
       ENROLL (CID, SID, GRADE),
       COURSE (CID, CNAME, CREDIT)




                                                           51
 The degree of a relationship type is the number of
  participating entity types
    e.g., binary and ternary
 In general
    A ternary R represents more information than 3-binary
     relationship types




                                                             52
           A Ternary Relationship

  SID                                  JID
                          Quantity


supplier                             project
                      supply




                       part


                       PID
                                               53
             3 Binary Relationships

  SID                                            JID
                              Quantity


supplier             M                N     project
                         supplies
 M
                                             M
 Can_suppy
                                          Uses
                 N                    N
                          part


                           PID
                                                       54
FN              LN

                           0:1

     EMPLOYEE                    MARRIED-TO
                     0:1




      SS


                                              55
 EMPLOYEE( SS, FN, LN)
 MARRIED-TO (WF-SS,HS-SS)




                             56
FN              LN

                           1:1

     EMPLOYEE                    SUPERVISES
                     0:N




      SS


                                              57
 EMPLOYEE( ESSN, SSSN, FN, LN)




                      Posted as FK




                                     58
PN                                       QT-RQ
             QT-HD

                           0:N

      PART                       Is-part-of
                     0:M




     PD


                                                 59
 PART(PN, PD, QT-HD)
 STRUCTURE(MJPN, MRPN, QT-RQ)




                                 60
PName PD     QT-HD MJPN   MRPN QT-RQ
A    Bike  100     A      C    1
     Frame
B    Trike 180     A      G    1
     Frame
C    Wheel 90      A      E    1
D    Spokes 200    B      C    1
E    Chain   889   C      D    90
F    Links   999   G      D    80
62
63
64
 ER Model Concepts: Entities, attributes, relationships
 Constraints in the ER model
 Using ER in step-by-step conceptual schema design for
  the COMPANY database
 ER Diagrams – Notation




                                                           65
 System development is a game that demands team
 work
   Demands close collaboration of
      Analysis
      Software development
      Database teams

 UML
   Standard language used for modeling business and
    software application
   Can be used by both application development and
    database designer to communicate

                                                       66
 There are many types of UML diagrams to help DB
  designers
 Diagrams can be used for
   Analysis
   Design
 Examples of diagrams
   Use case
   Class
   Component
   Etc.


                                                    67
 Use case
   used to model the system’s intended functions and its
    environments
   The model can be used as a contract between the
    customer and the development




                                                            68
 The diagram contains
   Actors
      Anyone or anything that may use the system
   Use case
      Defines a series of actions , initiated by an actor, that the
       system performs




                                                                       69
                             Physician               Patient
Nurses



                                Provide
                             Clinical Care

                                                     Insurance Company
Legal Agent




              Accounting   Auditor           External service provides
                                                                         70
               Provide
            Clinical Care




              Accounts
              Receivable

                              Insurance Company
Corporate
Auditor


               Comply with
               Regulations

                             Auditor
                                             71
 Use case Name: Access Clinical Records
 Use Case Purpose: The purpose of this use case is to allow
    the clinical record information to be accessed by the
    authorized actors (i.e., users)
   Precondition: user is authorized
   Post conditions: Clinical records will remain locked until
    the clinical records user completes access and “releases” the
    clinical records.
   Constraints: Only the same user to whom the records were
    released can change or return the records
   Assumptions: None
                                                                    72
 Basic Steps:
    1.   The user identifies him/herself to EMR
    2.   EMR verifies the user’s security access, permission,
         and so on
    3.   The user specifies the request to get the records for a
         patient
    4.   EMR provides the records to the user
    5.   If user wants to access additional records, go to step
         3



                                                                   73
 Use case Name: close clinical records
 Use Case Purpose: The purpose of this use case is to , allow
    no further updates. This places the specific records out of
    daily use
   Precondition: the records must be archived
   Post conditions: the records removal schedule has been
    completed
   Constraints: patient is deceased or left
   Assumptions: the archive system must maintain the
    patient record for 7 years


                                                                  74
 Basic Steps:
    1. All clinical records are examined for each patient to
       determine if the patient is deceased
    2. For all deceased patients, the records are closed
    3. For those who left, the records are added to records
       close schedule
    4. All clinical records for closure are moved to the
       archive




                                                               75
 What are the cardinality relationships for the following
 rules:
   Each department can have only one manager
   Each manger can work in only one department
   Each department can have many employee
   Each employee can work in only one department




                                                             76

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:4/13/2012
language:English
pages:76