FACULTAD DE CIENCIAS DE LA COMPUTACIÓNdpinto/bd/matdidbd.pdf · Diapositiva 1 Development of...
35
BENEMÉRITA UNIVERSIDAD AUTÓNOMA DE PUEBLA FACULTAD DE CIENCIAS DE LA COMPUTACIÓN Material Didáctico Bases de Datos Otoño 2002 MC David Eduardo Pinto Avendaño
Transcript of FACULTAD DE CIENCIAS DE LA COMPUTACIÓNdpinto/bd/matdidbd.pdf · Diapositiva 1 Development of...
BENEMÉRITA UNIVERSIDAD AUTÓNOMA DE PUEBLA
FACULTAD DE CIENCIAS DE LA COMPUTACIÓN
Material Didáctico
Bases de Datos
Otoño 2002
MC David Eduardo Pinto Avendaño
Diapositiva 1
Development of DataBase Schemas
Basic Knowledge
MC. David Eduardo Pinto Avendaño
Benemérita Universidad Autónoma de Puebla
Facultad de Ciencias de la Computación
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 2 Agenda
1. Introduction2. Overview of database development3. Modeling basic entities4. Modeling relationships5. Adding detail to the diagram6. Unique Identifiers7. Resolving many to many relationships
Day 1
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 3 Agenda
8. Modeling hierarchies, networks and roles 9. Modeling complex structures10 . Conceptual modeling review11. Initial database design12. Mapping exclusive relationships and entities to tables13. Normalization
Day 2
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 4 Introductions
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 5 Development Approach
METHODS TOOLS
TECHNIQUES
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 6 CDM Development Approach
Business Requirement DefinitionExisting System Examination
Technical ArchitectureDatabase Design and Build
Module Design and BuildData Conversion
DocumentationTesting
TrainingTransition
Post System Support
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 7 Database Development Process
Function Modeling
Application Design
Application Build
ER model,entity definition
Business Requirements
ConceptualData Modeling
Database Design
Database Build
Table , index,view
Function hierarchy,function definition
Module (screen ,report, menu , SQL)
ApplicationDatabase
Information Process
Operational System
Cross-checking
Cross-checking
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 8 Database Development Process
CONCEPTUAL
LOGICAL
PHYSICAL
Table Definitions
ERM
Database
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 9 Terminology
LOGICALCONCEPTUAL
DESIGN
ENTITY
RELATIONSHIP
ATTRIBUTE
TABLE
FOREIGN KEY
COLUMN
(Business view) (Systems view )
ANALYSIS
UNIQUE IDENTIFIER
PRIMARY KEY
UNIQUE KEY
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 10 Business Information Requirements
“I manage theHuman Resources Department for a large company.We need to keep information about each of our company’s employees.We need to track each employee’s first name, last name, job or position,hire date and salary. For any employees on commission, we also need to track their potential commission. Each employee is assigned a unique employee number.
Our company is divided into departments. Each employee is assigned toa department , for example, accounting, sales or development. We need to know the department responsible for each employee and the department’s location. Each department has a unique number, for example, accounting is 10 and sales is 30.
Some of the employees are managers . We need to knoweach employee’s manager and the employees that each manager manages”.
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 11 Entity Definitions
An object of interest to the businessA class or category of thingA named thingA nounA thing of significance about which the business needs information
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 12 Attribute Definitions
Nouns used to describe entities Specific pieces of information which need tobeknownAn entity should have attributes
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 13 Diagramming Entities
COMPANY(CLIENT)
EMPLOYEE
DEPARTMENT
MEMBERSHIP
name
date of birth
Soft box Singular, unique name in uppercaseOptional synonym nameAttribute names in lower case
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 14 Entity Instances
EMPLOYEE DEPARTMENT
Personnel Finance Sales
Head office
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 15 Identifying a Unique Instance
EMPLOYEE
badge numbernamedate of birthsalary
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 16 Identify and Model Entities
Identify a noun– Is it significant?– Is there information about it that the business needs to keep?– Is it a group or an instance?
Name the entityWrite a description of itIdentify a few attributesDraw a soft box for it
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 17 In Class Exercise - Solution
“I am the manager of a training company that providesinstructor- led COURSEs in management techniques. We teach courses, each of which has a code, a name, and a fee.Introduction to UNIX and C Programming aretwo of our more popular courses.
Courses vary in length from one to four days. Paul Rogers and Maria Gonzales aretwo of our best teachers. We need each INSTRUCTOR’s name and phone number. The STUDENTs can take several courses over time, and manydo this. Jamie Brown from AT&T took every course we offer !
We like to have each student’s name and phone number.”
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 18 In Class Exercise - Solution
COURSE STUDENTINSTRUCTOR
codenamefeelength
namephone no.
namephone no
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 19 Relationship Definitions
The way one entity relates to anotherThe business rules that link together business information needs What one thing has to do with another A named association between entities
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 20 Bi-directional Relationships
DMDD COURSE SMT COURSESMT COURSE
INSTRUCTOR COURSE
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 21 Diagramming Conventions
A line between two entitiesLower case relationship nameOptionality (Minimum cardinality)
Optional - may beMandatory - must be
One or more
One and only one
Degree (Maximum cardinality)
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 22 Diagramming Conventions
COPY TITLE
many(crow’s foot)
mandatory
optional
one
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 23 Relationship Syntax
Each
Subjectentity
must b eo rmay be
relationshipname
one or moreo rone and only one
entity 1 entity 2
DegreeObjectentityOptionality Name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 24
EMPLOYEE DEPARTMENTassigned to
Each EMPLOYEE must be assigned to one and only one DEPARTMENT
EMPLOYEE DEPARTMENT
Each DEPARTMENT may be responsible for one or more EMPLOYEES
responsible for
Validation - in class solution
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 25 Relationship Types
Many -to-One
One-to-One
Many-to-Many
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 26 Many -to-One Relationships
CUSTOMER SALES REPRESENTATIVE
assigned to
visited by
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 27 Many-to-Many Relationships
PATIENT HEALTH CARE WORKER
assigned to
attended by
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 28 One-to-One Relationships
BICYCLE CYCLIST
the rider of
is ridden by
Represents a snapshot in time
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 29 Analyzing and Modeling
Relationships1 Determine the existence of a relationship2 Name each direction of the relationship3 Determine the degree of each direction of the
relationship4 Determine the optionality of each direction of the
relationship5 Read the relationship aloud to validate it
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 30 Attributes
Badge Number - Identifies an employee
Name - Qualifies an employee
Payroll category (weekly or salaried) -Classifies an employee
Date of birth - Quantifies an employee
Employment status (active, leave, terminated) -Expresses the status of an employee
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 31 Finding Attributes
Is this attribute really needed ?
Beware of obsolete requirements from previous systems
Beware of derived data
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 32 Attribute Diagramming Conventions
EMPLOYEE
badge numfirst namelast name
payroll num
date of birthemployment status
Inside the entity's softbox
Singular
Lowercase
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 33 Meaningful Components
PERSON
name
PERSON
last namefirst name
ITEM
code
ITEM
typevendornum
Break down aggregate attributes
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 34 Verify for Single Value
RENTAL
transaction date
total amount paiditem
Yes, more than one item may berented at a time. An entity is missing.
RENTAL
transaction date
total amount paid
RENTAL ITEM
item num
Can an attribute have more than one value for an instance of the entity?
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 35 Attributes Which have Attributes
Does information need to be stored about any of the attributes?
Yes, review details. An entity is missing.
TITLE
REVIEW
authorcommentdate recorded
product codetitledescriptionreview details
product codetitledescription
TITLE
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 36 Finding Common or Derived Data
Count TotalMaximum, Minimum, AverageCalculation
Derived attributes are redundant andcan lead to inconsistent values
12 08 30 22----72----
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 37 Attribute Optionality
A value mustbe stored for each entity instance
Tagged with *
Mandatory Attributes
Optional Attributes A value may be stored for each entity instance
Tagged with o
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 38 Attribute Optionality
EMPLOYEE
badge num
first name
last name
title
***o weighto
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 39 Attribute Details and Volumes
Attribute - * Engine Size
Format Type NumberMaximum length 4Average length 4Decimal place 1Unit of measure ccAllowable values 900,1000,1500,1800,2000
Volume Initial 100%
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 40 Unique Identifier Definition
Each entity instance must be able to be uniquely identified
A combination of attributes or relationships that serve to identify a specific instance of an entity.
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 41 Simple Unique Identifier
CUSTOMER
# * customer num
876342
Single attribute
Tag the UID with #
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 42 Compound UID - Attributes
MEMBERSHIP
# * num
# ostart date
567498
Multiple attributes
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 43 Compound UID - Composite
ACCOUNT
* num
BANK
# *num
What would you need to know to identifya specific instance of ACCOUNT?
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 44 Compound UID - Composite
ACCOUNT
# * num
BANK
# *num
Use a UID bar to indicate that a relationship is part of the entity’s UID
Use # to indicate that the attribute is part of the entity’s UID
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 45 Compound UID - Relationships
RENTAL ITEM
* rental periodreturn dateo
RENTAL# * transaction num
* transaction date
COPY
# * inventory num* purchase cost
What do you need to know to identify a specific instance of RENTAL ITEM?
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 46 Compound UID - Relationships
RENTAL ITEM
* rental periodreturn dateo
RENTAL# * transaction num
* transaction date
COPY
# * inventory num* purchase cost
Rental item requires the rental transaction numand the inventory num
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 47 Multi-Level Relationship UIDs
What would you need to know to identify a specific instance of TICKET?
VENUEPLAY
# *title
PERFORMANCE# * date# * time # *name
CUSTOMER
# *name
TICKET
* seat number
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 48 Multiple UIDs
badge num
payroll num
first and last name
EMPLOYEE
badge num
payroll num
last name
first name
# (1)*
# (3) *
#(2) o
#(2) o
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 49 Identifying the Problem
TITLE SUPPLIER
supplied by
supplier of
In which entity would you store the attribute purchase price?
From this diagram , can you tell which supplier instance provides “Casablanca”?
# *prod code* name
# * supplier no* name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 50 Intersection Entities
CATALOG ITEM
availableas
SUPPLIER
supplier of
TITLETITLE
# *prod code* name # * supplier no
* name
* purchase price
forfor
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 51 Unique Identifiers
available as
CATALOG ITEM
SUPPLIER
supplier of
TITLE
TITLE
# * prod code* name
# * supplier no* name
* purchase price
forfor
CATALOG ITEM
available as
SUPPLIER
supplier of
TITLE
TITLE
# * prod code* name
# * supplier no* name
# * item num* purchase price
forforOR
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 52 Modeling Recursive Relationships
...and mine
...he’s mymanager
... but I’m HISmanager!
EMPLOYEE
manager of
managed by
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 53 Modeling Hierarchical Data
Company
Division
Team
Department
TEAM# name
DEPARTMENT# *name
DIVISION
# * name
COMPANY# * name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 54 Hierarchies as Recursive Relationships
ORGANIZATIONELEMENT
made up of
within# *name
* type
TEAM# name
DEPARTMENT# *name
DIVISION
# * name
COMPANY# *name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 55 Network Structures
COMPONENT
# * identifier
a part of
made upof
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 56 Network Structures
COMPONENT
ASSEMBLYRULE
a part ofmade upof
for for
o quantity
# * identifier
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 57 Exclusive Entities
* category* durationO audio
TITLE
#
**
product codetitledescription
*
GAMEMOVIE* category* medium* minimum memory
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 58 Exclusive Entities
COMPANY
idnametelephone numsupplier numsales contact
#*
o
*
*
o
COPYacquired from
the source of
the holderof
held byMEMBERSHIP
* num* start date* expiry date
termination
#
* inventory numo condition
o
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 59 Splitting Entities
COMPANY
#
**
acquired from
the source of
the holderof
held by
idnametelephone num
supplier numsales contact
**
OTHER
SUPPLIER
COPY
MEMBERSHIP
* num* start date* expiry date
termination
#
* inventory numo condition
o
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 60 Nesting Entities
EMPLOYEE
SALES
REP
TELESALES
CLERICAL
HUMANRESOURCES
CAR
driven by
authorizedto drive
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 61 Recursive Subtypes
ORGANIZATIONELEMENTTYPE
ORGANIZATION ELEMENT
made up of
COMPANY(ORGANIZATION)
DEPARTMENT(SUBDIVISION)
within
of
the classificationfor
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 62 Modeling Exclusive Relationships
o
oMEMBERSHIP
COMPANY
CUSTOMER
the holder of
held by
held by
the holder of
* num* startdate* expiry dateo termination
* name* postal area0 contact name
* num* first name* last name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 63 Modeling Exclusivity
“We offer membership to individual customers and companies”
MEMBERSHIP
CUSTOMER COMPANY
MEMBERSHIP
CUSTOMER COMPANY
MEMBERSHIP
CUSTOMER COMPANY
MEMBER
INDIVIDUAL ORGANIZATION
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 64 Modeling Data over Time
APARTMENT
# * code* address
PERSON
# * id* last name* first name
rented by
the renter of
What if you need to hold an apartment’s rental history?
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 65 Modeling Data over Time
APARTMENT
# * code* address
PERSON
# * id* last name* first name
rented by
RENTAL HISTORY
for
the renter of
for
# * from dateto dateo
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 66 Modeling Data over Time
the employer
of
employedby COMPANY
# * code* name
# * idlast name*
* first name
MEMBER
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 67 Modeling Data over Time
COMPANY
# * code* name
# * idlast name*
* first name
MEMBER
# * from dateto dateo
EMPLOYMENTHISTORY ENTRY
for for
employedby
the employer
of
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 68 Fan Traps
PERSON POSITION
COMPANY
# * id* last name* first name
# * job titlejob descriptiono
# * code* name
the holder of
heldby
included in
the employer of
the employer of
employed by
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 69 Fan Traps
PERSON POSITION
COMPANY
# * id* last name* first name
# * job titlejob descriptiono
# *code* name
employed as
held bythe subject of
the employer for
the employer for
employed at
POSITION HISTORY
ORGANIZATIONHISTORY
COMPANY HISTORY
for
for
for
forfor
for
* start dateend dateo
* startdateend dateo
* start dateend dateo
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 70 Transferable Relationships
DEPARTMENT
# *code
PERSON
# * id* last name* first name
works in
employs
Personnel Finance Sales
Head office
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapos itiva 71 Non-Transferable Relationships
COMPANY
#
**acquired from
the source of
idnametelephone num
supplier numsales contact
**
SUPPLIERCOPY
* inventory numo condition
OTHER
*
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 72 Design of Database schema
LOGICALCONCEPTUAL
ANALYSIS DESIGN
ENTITY
RELATIONSHIP
ATTRIBUTE
UNIQUE IDENTIFIER
TABLE
FOREIGN KEY
COLUMN
PRIMARY KEY
UNIQUE KEY
(Business view ) (Systems view )
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 73 Creating the Outline Design
1 Map simple entities to tables2 Map attributes to columns, and document
sample data3 Map unique identifiers to primary keys4 Map relationships to foreign keys
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 74 Mapping Simple Entities
Column name
BADGE_NUM
PAYROLL_NUM
FIRST_NAME
LAST_NAME
Key type
Nulls NN NN
Sampledata
Table Name: EMPLOYEES
POSITION
NN
EMPLOYEE
*badge numpayroll numfirst namelast nameposition
###
#
*oo
*
(3)
(3)
(2)
(1)
PK UK1 UK2 UK2
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 75 Mapping Relationships - M:1
*DEPARTMENT
numname
# **
EMPLOYEE
*badge numpayroll numfirst namelast nameposition
###
#
*oo
*
(3)
(3)
(2)
(1)
Column name
BADGE_NUM
PAYROLL_NUM
FIRST_NAME
LAST_NAME
Key type
Nulls NN NN
Sampledata
Table Name: EMPLOYEES
POSITION
NN
PK UK1 UK2 UK2
DEP_NUM
FK
NN
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 76 Mapping Relationships - Mandatory
1:1Column name
ID TYPE
Key type PK
Nulls NN NN NN
Sampledata
BICYCLE
#
*
**
idtype
CYCLIST
numname
# **
FK, UK
CYCLIST_ NUM
BICYCLES
Table Name:
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 77 Mapping Relationships - Optional 1:1
BICYCLE
#
*
**
idtype
CYCLIST
numname
# **
Column name
ID TYPE
Key type PK
Nulls NN NN
Sampledata
FK, UK
CYCLIST_NUM
Column name
NUMBER
Key type
Nulls
Sampledata
BICYCLE_ID
NAME
OR
BICYCLES
Table Name :
CYCLISTS
Table Name:
PK
NN NN
FK, UK
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 78 Mapping Relationships - Recursive
1:Mthe manager of
managedby
EMPLOYEE
*badge numpayroll numfirst namelast nameposition
###
#
*oo
*
(3)
(3)
(2)
(1)
Column name
BADGE_NUM
PAYROLL_NUM
FIRST_NAME
LAST_NAME
Key type
Nulls NN NN
Sampledata
Table Name: EMPLOYEES
POSITION
NN
PK UK1 UK2 UK2
EMP_MAN_NUM
FK
NN
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 79 Mapping Relationships - Recursive
1:1
Column name
ID NAME
Key type PK
Nulls NN NN
Sampledata
PERSON
#**
idname
FK,UK
PERSON_SPOUSE_ID
the spouse of
married to
2345
2345
ANN
RAY6785
6785
PERSONSTable Name:
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 80 Mapping Arcs
OFFICE SUITE o
o
o#
#
#
#
#**
*
*
*
building i d
suite no
INDIVIDUAL
id
PARTNERSHIP
code
COMPANY
num
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 81 Mapping Subtypes
TITLE
MOVIE
GAME
# * product code
* category
* medium
Single table designMultiple table designArc implementation
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 82 Explicit Arc Design
Column nameBUILDING_ID
SUITE_NO
IND_ID PART_CODE
Key typeNulls NN NN
Sampledata
1024 101 30045
512 210 A4431
977 144 54532
3041 510 10844
COMP_NUM
PK PK FK1 FK2 FK3
OFFICE_SUITES
Table Name:
OFFICE SUITE o
o
o#
#**
buildingidsuite no
# *INDIVIDUAL
id
# *PARTNERSHIP
code
#*COMPANY
num
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 83 Generic Arc Design
Column name BUILDING_ID
SUITE_NO RENTER_ID
Key typeNulls NN NN
Sampledata
1024 101 30045
512 210 A4431
977 144 54532
3041 510 10844
RENTER_TYPE
PK PK FK
OFFICE_SUITES
Table Name:
NN NN
I
PI
C
OFFICESUITE o
o
o#
#**
building id
suite no
#*INDIVIDUAL
id
# *PARTNERSHIP
code
#*COMPANY
number
!
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 84 Single Table Design
TITLE
MOVIE GAME
# *product code
* category * medium
Column name PRODUCT_CODE
MOVIE_CATEGORY
GAME_MEDIUM
Key typeNulls NN
Sampledata
453 HORR
516 CD
677 DRAMA
444 CASS
PK
Table Name :
TITLES
TYPE
M
G
G
M
NN
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 85 Multiple Table Design
TITLE
MOVIE GAME
# *product code
* category * medium
Column name PRODUCT_CODE
MOVIE_CATEGORY
Key typeNulls NN
Sampledata
453 HORR677 DRAMA
PK
Table Name:
GAMES
Column namePRODUCT_CODE
GAME_MEDIUM
Key typeNulls NN
Sampledata
516 CD
444 CASS
PK
Table Name:
MOVIES
NN NN
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 86 Specify Referential Integrity
EMPLOYEEDEPARTMENT
RULE
DEPT 40
FRED
JOE
BILL
RESTRICT
CASCADEFRED
JOE
BILLDEPT 40
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 87 Specify Referential Integrity
EMPLOYEEDEPARTMENT
RULE
FRED
JOE
BILL
FRED
JOE
BILL
DEFAULT
NULLIFY
DEPT 40
DEPT 99999
DEPT 40
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 88 Outline Index Design
KING
BLAKEJAMES
MILLERTURNER
ADAMSALLEN
BLAKECLARKFORD
JAMESJONES
KINGMARTIN
MILLERSCOTTSMITH
TURNERWARD
SMITHALLENWARDJONES
MARTINBLAKECLARKSCOTTKING
TURNERADAMSJAMESFORDMILLER
INDEX BLOCKS
DATA BLOCKS
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 89 Establish Views
ORD-NUM
ORD-DTE
ORD_CUS_NO
CUS_NO
CUS_LNME
CUS_FNME
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 90 Consider Derived Data
Order 1234
item 19.76
item 23.24
item 34.12
item 40.75
item 522.09
total39.96
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 91 Artificial Keys
CUSTOMER
o first name* last name* address
CUSTOMER
# * code* first name* last name* address
#
#
#
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 92 Planning Physical Storage
Estimate the amount of disk space neededDecide on placementDefine storage allocation
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 93 ER Diagrams and Normalization
Top down approachFastExamine requirements Business knowledge
Bottom up approachVery slowExamine existing dataMathematically based
NormalizationER Diagramming
• Top down create - bottom up checking• Accuracy• Greater understanding of the data
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 94 Rules of Normalization
Collect and list the raw data 0NFRemove repeating groups 1NFRemove part key dependencies 2NFRemove inter -data dependencies 3NFRemove inter -key dependencies BC NFTest and identify transitive dependenciesOptimizeRetestDraw and use the model
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 95 Collect the Raw Data
0NF1NF2NF3NFBC NFTestOptimizeRetest
customer name
John Doeorder 12345
John Doe
order 12349
product A453
desc. Bowls
quantity 6
address anytown
any place
date 03/04/96
0NF
# customer nameorder numproduct numproduct descriptionquantity orderedcustomer addressdateordered
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 96 Remove Repeating Groups
0NF1NF2NF3NFBC NFTestOptimizeRetest
# customer name# order num
product numproduct descriptionquantity ordereddateordered
FK
# customer namecustomer address
1NF
0NF
# customer nameorder numproduct numproduct descriptionquantity orderedcustomer addressdate ordered
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 97 Remove Part Key Dependencies
0NF1NF2NF3NFBC NFTestOptimizeRetest
# order numproduct numproduct descriptionquantity ordereddate ordered
# customer name# order num
# customer namecustomer address
2NF
FKFK
#customer name#order num
product numproduct descriptionquantity ordereddateordered
FK
#customer namecustomer address
1NF
NO CHANGE
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 98 Remove Inter-Data Dependencies
0NF1NF2NF3NFBC NFTestOptimizeRetest
# order numproduct numquantity ordereddate ordered
# customer name# order num
# customer namecustomer address
3NF
FKFK
FK
# product numproduct description
# order numproduct numproduct descriptionquantity ordereddate ordered
# customer name# order num
# customer namecustomer address
2NF
FKFK
NO CHANGE
NO CHANGE
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 99 Remove Inter-Key Dependencies
0NF1NF2NF3NFBC NFTestOptimizeRetest
#order numproduct numquantity ordereddate ordered
#order num customer name
#customer namecustomer address
BCNF
FK
FK
#product numproduct description
# order numproduct numquantity ordereddate ordered
#customer name#order num
# customer namecustomer address
3NF
FKFK
FK
# product numproduct description
NO CHANGE
NO CHANGE
NO CHANGE
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 100 Optimize
0NF1NF2NF3NFBC NFTestOptimizeRetest
# customer numcustomer namecustomer address
# order numproduct num FKcustomer num FKquantity ordereddate ordered
# order numproduct numquantity ordereddate ordered
# order num customer name
# customer namecustomer address
BCNF
FK
FK
OPTIMIZED DATA GROUPS
Transitive
BCNF
# customer numcustomer name
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 101 Entities and Normalization
Does any attribute havemore than one value ?Does any attribute need just part of the UID?Is any attribute dependent on another attribute and not theUID?Does any part of theUIDdepend on another part of the UID?
EMPLOYEE
* badge num* payroll num
* first name* last name
* payroll category
* date of birth* employmentstatus* previous departments
##
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
Diapositiva 102 Summary
CONCEPTUAL
LOGICAL
PHYSICAL
Table Definitions
ERM
Database
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
___________________________________
![UMLto[No]SQL: Mapping Conceptual Schemas to Heterogeneous ...€¦ · Index Terms—Database Design, UML, OCL, NoSQL, RDBMS, constraint, SQL, model partitioning I. INTRODUCTION The](https://static.fdocuments.co/doc/165x107/605c311db378d858b31acca7/umltonosql-mapping-conceptual-schemas-to-heterogeneous-index-termsadatabase.jpg)
