Week 8 October 19

1
Week 8October 19

• Database Design
• Modeling with ERD

2
Administration

• Data Administrator (DA) management of the data
  resources, including the database planning,
  development, and maintenance of standards,
  policies and procedures, and conceptual and
  logical database design
• Database Administrator (DBA) management of the
  physical realization of a database system,
  including physical database design and
  implementation, setting security and integrity
  controls, monitoring system performance, and
  reorganizing the database (when necessary)

3
Database Design

• Data modeling
• Understanding the meaning of data
• Identify the users perspective of data
• Identify the data themselves
• Identify the applications supported by the data
• Communication information requirements
• Diagram with ERD (entity-relationship diagram)

Satisfying the information needs of the
organization
4
Optimal Logical Design Criteria

• Structural validity - reflects the enterprise
• Simplicity - ease of understanding
• Expressability - distinguishability of data
• Nonredundancy - exclusion of extraneous
  information
• Shareability - nonexclusive data
• Extensibility - support future information
  requirements
• Integrity - consistency with organizations
  information use and management
• Diagrammatic representation - ability to
  graphically model data

5
Logical vs. Physical Design

• Logical
• Defines the whats (e.g., what information needs
  to be present)
• Physical
• Defines the hows (e.g., how data will be stored)

How
What
Sequence
6
Fact-Finding Techniques

• Examining documents
• Interviewing
• Observing the enterprise in operation
• Research
• Questionnaires

7
Design Tools

• Relational database design
• Entity relationship diagram (ERD)
• Relations, relationships, constraints
• Data normalization
• Method for establishing relations

For relational model only
For relational database only
8
Data Modeling Entity Relationship Modeling
9
Entity Relationship (ER) Model(applies to
relational data model)

• High-level conceptual model
• Describes the structure of the database, and the
  associated retrieval and update transactions on
  the database
• Composed of
• Entity types
• Relationship types
• Attributes

10
ER Modeling
Relationship type
Products stock number product description retail
price stock on hand stock on order
Manufacturers manufacturer code manufacturer name
?
Have
0..
1..1
Attributes
Entity type
11
ER ModelingAlternatively
Relationship type
Products Stock number Product description Retail
price Stock on hand Stock on order
Manufacturers Manufacturer code Manufacturer name
Attributes
Entity type
12
ERD Notation
Primary key
Entity type
Relationship type
Relationship name
Music_categories
CDs
Classify?
music_category_code PK music_category_title
Attributes
stock_number PK CD_title artist music_category_c
ode record_label_code
1..1
0..
Multiplicity (constraint)
Degree of the Relationship Binary
13
ERD NotationAlternatively
Primary key (underscored)
Entity type
Relationship type
Relationship name
Music_categories
CDs
Music_category_code Music_category_title
Classify
Attributes
Stock_number CD_title Artist Music_category_code
Record_label_code
Zero (circle)
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
14
1. Entity Types

• Strong Entity Type
• Not existence-dependent on another entity type
• Weak Entity Type
• Existence-dependent on another entity type (i.e.,
  child, dependent, subordinate)

Entity type
Entity
Entity
Uniquely identifiable
Entity
15
Entity Types
Strong entity?
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
16
Definition of a Weak Entity Type

• An entity type that borrows all or part of its
  primary key. Identifying relationships indicate
  the entity types that supply components of the
  borrowed primary key.
• Mannino, 1999

Entity type 1 Key attributes...
Method to Follow
Have
Weak entity type
Entity type 2 Key Key attributes...
Composite key
17
Diagramming Weak Entity Types
An account cannot exist without an customer.
Strong entity type (parent, owner, dominant)
Customers attributes...
Minimum must be one
Customer_Accounts attributes...
Weak entity entity (child, dependent, subordinate)
A customer can have more than one account
Designates a weak entity type
18
2. Attributes

• Property of an entity or relationship type
• Attribute domain
• Set of values that may be assigned to a
  single-valued attribute

Customers
Cust_account Cust_name Cust_address
Cust_phone Soc_Sec_Num
Customer_Accounts
Cust_account Current_balance Credit_limit
Active_date Expire_date
19
Attributes of Attributes

• Simple (atomic attributes) - composed of a single
  component
• Composite - composed of multiple components
• Single valued - one value for an entity
• Multi-valued - one or more values for an entity
• Derived - value derived from a related attribute
  or set of attributes

Student_ID FName MName LName
Single-valued
Multi-valued
Student_ID Semester Course_ID
More than one semester, more than one course_id
20
Attribute Domain
Customers
Composite
Cust_account Cust_name Cust_address
Cust_phone Soc_Sec_Num

• On an ER model, should customer name be shown as
  a composite or simple attribute?
• What is the attribute domain of Cus_name?

Cust_first_name
Cust_last_name
John William Anita Homer
Brown Tell Breake Simpson
21
Derived Attributes

• Derived - value derived from a related attribute
  or set of attributes

Student_ID Semester Course_ID Units
Grade Grade_point
Student_ID Semester Course_ID Units
Grade Grade_point
Student_ID Semester Course_ID Units
Grade Grade_point
Units x Grade Grade point
22
Attributes as Keys
Uniquely identifies an entity
Candidate key

• Keys cannot change their values (good for the
  life of the entity)
• An efficient means for identifying an entity

Primary key

• Alternate key - candidate that can also be used
  to access an entity
• Composite key - composed of multiple attributes
  (components)

23
Diagrammatic Representation
Customers Cust_account PK Cust_name
First_name Middle_name Last_name Cust_addres
s Street_number Zip_code (fk) Cust_phone Soc
_sec_num
Key
Composite attribute
Composite attribute
Foreign key
24
3. Relationship Types

• A set of associations between two (or more)
  participating entity types
• Each is given a name that describes the function

Customers Customer_account
Own
Customers_accounts Customer_account
25
Entity Relationship Diagram

• Degree of a relationship - number of entities
  participating in a relationship (binary, ternary,
  quaternary, etc.)

Customers Customer_account
Strong
Relationship
Own

• Dog-ear lines indicate a relationship between a
  weak and strong entity

Customers_accounts Customer_account
Weak
26
Data Modeling
Music_categories Music_category_code Music_catego
ry_title
Strong Entity (parent)
All children (CDs) must have a parent (music
categories or record labels)
Relationship
Classify
Strong Entity (parent)
CDs Stock_number CD_title Artist Music_category_
code (fk) Record_label_code (fk)
Record_labels Record_label_code Record_label
Produce
Weak Entity (child)
Method to Follow
27
Degree of a Relationship
Customers
A customer purchases products and places them on
his/her account
Products
Buy
Relationship of degree three or ternary
Cust_Accounts
28
Degree of a Relationship
An employee is managed by only one manager (an
employee is related to a maximum and minimum of
one manager)
Manages
Employees
Employee_number Employee_name Classification Proje
ct_ID
Self-referencing relationship
A manager manages one to many employees (a
manager is related to a minimum of one and a
maximum of many employees)
29
Structural Constraints

• Cardinality
• Determines the number of possible relationships
  for each participating entity
• 11 - one to one
• 1M - one to many
• MN - many to many
• Participation
• Determines whether the existence of an entity
  depends upon its being related to another entity
  through the relationship

Defined by business rules
30
Cardinality

• 11 (one to one)
• Each entity in X is associated with at most one
  entity in Y and conversely each entity in Y is
  associated with at most one entity in X
• 1M (one to many)
• Each entity in X can be associated with many
  entities in Y but each entity in Y is associated
  with at most one entity in X.
• MN (many to many)
• Each entity in X can be associated with many
  entities in Y and each entity in Y can be
  associated with many entities in X.

31
Cardinality
11 Relationships
Strong entity type
Weak entity type
Customers
Accounts
Customer_ID Customer_name Customer_address Zip_cod
e
Account_number Customer_ID Account_type Current_ba
lance
Own
Mandatory participation
A customer owns a minimum and maximum of one
account
An account is owned by a minimum and maximum of
one customer
Note. This would be avoided in the logical
design, but could be implemented in the physical.
32
Cardinality
1M Relationships
Strong entity type
Weak entity type
Customers
Accounts
Customer_ID Customer_name Customer_address Zip_cod
e
Account_number Customer_ID Account_type Current_ba
lance
Own
Mandatory participation
A customer owns a minimum one and maximum of many
accounts
An account is own by a minimum and maximum of one
customer
Note. This would be avoided in the logical
design, but could be implemented in the physical.
33
Cardinality

• MN relationship if a customer can own more than
  one account (e.g., revolving, long-term), and one
  account can have more than one owner (e.g., joint
  account).

34
Cardinality
MN Relationships
Strong entity type
Weak entity type
Customers
Accounts
Customer_ID Customer_name Customer_address Zip_cod
e
Account_number Customer_ID Account_type Current_ba
lance
Own
Mandatory participation
A customer owns a minimum of one and a maximum of
many accounts
An account is owned by a minimum of one and a
maximum of many customers
Note. This would be avoided in the logical
design, but could be implemented in the physical.
35
Participation Constraints

• Determines whether the existence of an entity
  depends on it being related to another entity
  through the relationship
• Total (mandatory) - If the existence of one
  requires another
• Partial (optional) - If the existence of one does
  not require the other

Existence Dependency An entity that cannot
exist unless another related entity exists. A
mandatory relationship produces an existence
dependency. Mannino, 1999
36
ERD Notation
Primary key (underscored)
Entity type
Relationship type
Relationship name
Music_categories
CDs
Music_category_code Music_category_title
Classify
Attributes
Stock_number CD_title Artist Music_category_code
Record_label_code
Zero (circle)
A CD is related to a minimum and maximum of one
music category
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
37
ERD Notation
Primary key (underscored)
Entity type
Relationship type
Relationship name
Music_categories
CDs
Music_category_code Music_category_title
Classify
Attributes
Stock_number CD_title Artist Music_category_code
Record_label_code
Zero (circle)
A music category is related to a minimum of zero
and maximum of many CDs
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
38
ERD Notation
Minimum cardinality of one (a music category has
to have at least one CD)
Entity type
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Weak entity type (all four corners)
39
ERD Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
A record label is related to a minimum of zero
and maximum of many CDs
Produce
Record_labels
Record_label_code Record_label
40
ERD Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Produce
A CD is related to a minimum and maximum of one
record label
Record_labels
Record_label_code Record_label
41
ERD Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Produce
Quantity_produced
Attribute of a relationship
Record_labels
Record_label_code Record_label
42
(No Transcript)