it 20303 data modeling for logical design section 04

IT 20303

• Data Modeling for Logical Design

• Section 04

Relational Database Theory

• Database Development Life Cycle– Requirements– Analysis/Design– Produce– Build/Test– Production Maintenance


• What is a Data Model?– A way to represent reality– A schematic of data items and relationships– A “blueprint” for the database


• Why do people use data models?– To better understand the world we are modeling– To “picture” the conceptual view of the database– To better communicate between people– To arrive at a common understanding of meaning of

terms


• When do we make a data model?– For a specific application, begin in the definition

phase– For multiple applications, in a long-range planning

activity• An information resource management activity• A data administration activity

– When in doubt - model


• Building a database is a learning process– At the beginning, we only know what things are

important– We identify the major things and call them Entities– We accumulate Attributes and associate them with

entities– We determine how to identify each occurrence of an

entity – PK


• Cont’d– We identify relationships between entities – FK– We accumulate rules for the entities and relationships

– Constraints– We put it all together in a model and check it out with

users– This is called top-down data modeling (This is good to

do)


• The Entity-Relationship Approach– Represents reality using well-defined graphics

and rules• Basic building blocks are “things” (entities) and

relationships

Member

Animal

M

1 Adopts


• Advantages– Theoretical foundation (Set Theory)– Good for communication– Build E-R Model, then translate to any type of

RDBMS

• Disadvantages– Different (yet another new thing to learn)– Must translate to the relational model


• Entity-Relationship Model: Basic Concepts– Entity

• Thing, Object, Concept of interest to the enterprise• Each occurrence can be uniquely identified


• Entity-Relationship Model: Basic Concepts– Attribute

• Property of an entity• Column


• Entity-Relationship Model: Basic Concepts– Relationship

• Association between two (or more) entities


• Entity-Relationship Model: Basic Concepts– Entity Identifier

• Attribute(s) whose value uniquely identifies an entity

• Primary Key


• What is an Entity?– Physical entity types

• Person• Building• Machine• Book• Usually Singular


• What is an Entity?– Conceptual entity types

• Contract• Account• Order• Course


• What is an Entity?– Event entity types

• Transaction• Shipment• Reservation• Phone Call• Seminar Offering

Relational Database Theory• Entity-Relationship Model: Diagrams

– Example:

– Soft Rectangle represents entities• Noun• Singular

– Connecting Line represents relationships• Verb

Member

Animal

Adopts

Relational Database Theory• Relationships have Characteristics

– A relationship has Cardinality (Degree)

One-to-One One-to-Many Many-to-Many

Relational Database Theory• Each entity’s participation is

Mandatory or Optional

• Cardinality & Optionality

are based on business rules

Mandatory

Optional

Relational Database Theory• One:One Relationship

– One Member adopts one animal– One Animal is adopted by one member

Member

Animal

Adopts

Relational Database Theory• One:Many relationship

– One member adopts one animal– One animal is adopted by many members

Member

Animal

Adopts

Relational Database Theory• Many:Many relationship

– One member adopts many animals– One animal is adopted by many members

Member

Animal

Adopts

Relational Database Theory• Optionality: Participation in a

Relationship

Zoo Employee

Animal

Cares for

Relational Database Theory• Mandatory

– Every instance of the entity MUST participate in the relationship

– Example:• Every animal is cared for by at least one

employee

Relational Database Theory• Optional

– An instance of the entity CAN participate in the relationship

– Example:• Some employees do not take care of animals

End 09-14-05

Relational Database Theory• Data Modeling Example: Hospital

– Sample Hospital Data• Ward Name: Liston• Ward Type: Orthopedic• # of Beds: 6• Senior Nurse: J. Bryan

Relational Database Theory• Data Modeling Example: Hospital

– Cont’d

– Date of Birth: dd/mm/yy

Patient No Patient Name Date of Birth

45812 Carter, D. 21/02/65

71384 Willis, R. 08/10/46

69355 Barnes, G. 17/06/41

Relational Database Theory• Requirements determines boundaries of

data modeling– Data model does contain data about

• Ward• Patients

– Data model does not contain data about• Color of walls in ward• Color of patients hair• Springiness of the beds in the ward

Relational Database Theory• Entity-Relationship Modeling: Step-

by-Step1. Identify Entities

PATIENT WARD


by-Step2. Associate Attributes with entities

PATIENTPatient number

NameDate of birth

WARDWard name

TypeNo bedsSr nurse


by-Step3. Select or create a Unique Identifier for

each entity


NameDate of birth

WARDWard name

TypeNo bedsSr nurse


by-Step4. Identify Relationship between entity


NameDate of birth

WARDWard name

TypeNo bedsSr nurse

Is assigned to

Has assigned to it


by-Step5. Determine Optionality

Patient Ward


by-Step6. Show Cardinality

Patient Ward


by-Step7. History – time affects cardinality

Patient Ward


by-Step8. Resolve Many:Many relationship


NameDate of birth

WARDWard name

TypeNo bedsSr nurse

STAYPatient number

Ward nameDate

Relational Database Theory• Meaning of an Entity

– What is a patient?• In-patient• Out-patient• Psychiatric patient• Baby born in hospital• Baby born outside hospital• Current patient• Former patient

Relational Database Theory• Meaning of an Entity

– Cont’d• Clarify exact meaning of an entity by:

– Identifying several attributes» List examples

– Documenting the meaning


• Document Precise definition of each entity (Should always do)

Entity Name Entity Description

20. Patient A person with a specific disease or condition who receives treatment within a county. An entity on the patient master index. This may be a person who uses a hospital bed in order to receive clinical care/treatment or someone attending a clinic, day-care facility, etc. It also includes people in the community receiving care under a “Nursing Care in the Community” or a hospice program.

This also includes patients on the elective admission list who are awaiting elective admission.

The local authority of residence should also be recorded for each patient. This can be derived from the postcode.

Relational Database Theory• Drawing an E-R Diagram

– Identify the entities– Write (make up) a few attributes for each

entity– Designate the unique identifier (PK)– Identify the relationship (FK goes on the

many side)– Show cardinality and optionality for each

relationship

Relational Database Theory• E-R Modeling Exercise:

– A Seminar Company• A seminar company offers more than 100 different

courses– Each course has a unique course number and a title

• The company schedules thousands of seminars annually

– Each seminar is the presentation of one specific course– A seminar is either public or on-site

Relational Database Theory• E-R Model:

– Entities• Course

– Course # (PK)– Course Title– Duration– Author


– Entities• Seminar

– Seminar # (PK)– Date– Place– Type

» Public» On-site

– Course # (FK)


COURSE

SEMINAR

Optional

Mandatory

1

M


– A Personnel Database• A company has four divisions• Each employee works for a department• Each department reports to one of the

divisions


– Entities• Division

– Division #– Division Name


– Entities• Employee

– Employee #– Employee Name– Dept # (FK)


– Entities• Department

– Dept #– Dept Name– Div # (FK)

End 09-16-05

Relational Database Theory• Optionality: Four Possibilities

• ADEPARTMENT

EMPLOYEE

Works for


• BDEPARTMENT

EMPLOYEE

Works for


• CDEPARTMENT

EMPLOYEE

Has working for it


• DDEPARTMENT

EMPLOYEE

Has working for it

Relational Database Theory• Optionality on the “many” side

– Can be declared as a constraint in the database

– Determines whether the foreign key must be not null

Relational Database Theory• Determining Optionality & Cardinality

– Optionality & Cardinality• Specify lower and upper bounds of each

entity’s participation in the relationship• Use one of the following templates

Relational Database Theory• Template 1 (B)

– One employee (may/must) work for (one and only one/one or more) department

• Template 2 (B)– One employee works for a minimum of

(0/1) and a maximum of (1/many) department

Relational Database Theory• Use either template

– Read each relationship twice• Left to Right• Right to Left


DIVISION DEPARTMENTReports to

EMPLOYEE

Works for

B


– A Supplier-Parts Database• A company purchases many parts from its suppliers

– Each part has only one supplier

• A supplier can supply zero, one, or many parts

– One _____(may/must) _____

(one and only one/one or more) ______


SUPPLIER

PART

Supplies

Relational Database Theory• E-R Modeling: Resolving a M:N

Relationship– Establish a “Linker” entity (associative entity)– Make two 1:M relationships with the linker entity– Make the linker entity identifier the composite of

the entity identifiers from the existing entities– Assign attributes to the linker entity that belong

to the relationship


– M:N SUPPLIER

PART


– 1:M – 1:M

SUPPLIERSupplier ID

PARTPart No

SUPPLIER/PARTSupplier ID Part No

suppliesis supplied by


– A Warehouse-Products Database• A company has six warehouses

– Each warehouse stocks at least 50 different products

• The company markets approximately 500 products in a year

– Approximately 300 products are in stock at any one time


WAREHOUSEWarehouse #

LocationINVENTORYWarehouse #

Product #

Stocked in

PRODUCTProduct #

Product Name

Stocks

Original N:M

Relational Database Theory• Guidelines to Develop an E-R Diagram

– Identify the Major Entities– Identify the Attributes for each entity– Determine the Unique Identifier(s)– Identify the Relationships– Assign Cardinality– Turn Multivalued attributes into another entity– Determine Optionality– Resolve M:N Relationships

End 09-19-05End 09-19-05

Relational Database Theory• Recursive Relationships: 1:M

– A recursive relationship is a relationship between multiple occurrences of the same entity

Relational Database Theory• Recursive Relationships: 1:M

– There is a binary (1:M) recursive relationship between employees

• One employee supervises other employees

– The supervisor is also an employee• Empno and Mgr have the same domain

(Employee)

Relational Database Theory• Recursive Relationships: 1:M Cont’d

EMPLOYEE

Empno Ename Dob Startdate Job Mgr Sal Deptno …

EMPLOYEEEmpnoEname

Mgr….

Supervises

Relational Database Theory• Example of a M:N Recursive

Relationship– “Bill of Materials” is a recursive

relationship between parts• One part can be an assembly, which contains

parts• One part can be a component, contained in

an assembly

– Resolve M:N recursive relationship• Into two 1:M relationships

Relational Database Theory• Cont’d

PARTPart #

….

Contains

ASSEMBLYAssembly Part #

Component Part #Qty…

PARTPart #

…

Contains Is contained in

Relational Database Theory• Entity Subtypes and Supertypes

– An entity supertype can have several subtypes

– Different subtypes usually have different attributes


EMPLOYEEEmpnoEname

Jobtype

SALARIED EMPLOYEEWage/month

HOURLY EMPLOYEEWage/hour

Shift

Is A Is A

SUPERTYPE

SUBTYPE SUBTYPE

Relational Database Theory• Mapping the E-R Diagram to the

Relational Database– Each entity becomes a Table– Each attribute becomes a Column– Unique Identifier becomes the PK– Each 1:M becomes a FK on the Many

Side


– Optionality determines if the FK can be Null

– Each M:N becomes a New Table• Primary key is a composite of PK’s on each

table• Attributes of the relationship become columns• Any unresolved M:N must be resolved at this

time

Relational Database Theory• Mapping Subtypes and Supertypes

– Implementation Choices• One Table for the Entity

– Includes all attributes of supertype + all attributes of each subtype


– One table for each subtype• Includes all attributes of supertype in each

table

– One table for each subtype plus one table for the supertype

Relational Database Theory• Variations in Data Modeling Graphics:

Entities & Relationships

• 1:1

1 1

1 1



• 1:M

1 M

1 M



• M:N

M N

M N

Relational Database Theory• A “GOOD” Data Model

– Accurately Models the Real World or Application Domain

• As defined by the requirements

– Aids in Communications– Identifies all the Important Entities and

Relationships

Relational Database Theory• Questions?

it 20303 data modeling for logical design section 04

Documents