Week 7 October 10

1
Week 7October 10

• Database Planning and Design

2
Embedded SQL and Host Variable
Host variable
3
Value assigned to the host variable
4
Output
All sales revenue for store number 101
5
Host Variable, Lower Function, Wildcards,
Calculation
Calculation
LOWER function, concatenation and wildcards
Host variable
6
Dynamic SQL in Oracle Graphics
The value of table_name is assigned during the
execution of the program
Ampersand () specifies a lexical reference
select title, sum(sales_revenue) from table_name
where record_label_code label group by title
Colon () indicates a host variable
7
Dynamic SQLLexical Reference Variables
Text assigned at runtime
Query
product_code, sales_revenue sales_99 manufacturer_
code SON
select column_names from table_names where
condition
Equivalent to...
select product_code, sales_revenue from
sales_99 where manufacturer_code SON
8
Data Resource
9
Robert Anthony's Taxonomy of Managerial
Information Requirements
Aggregate
Infrequent
Information Requirements
Quite old
External
Future
Wide
Low
Strategic Planning
Management Control
Source
Scope
Time Horizon
Currency
Frequency of Use
Required Accuracy
Level of Aggregation
Operational Control
Data are organizational resources and must be
properly managed.
High
Internal
Detailed
Historical
Well defined
Very frequent
Highly current
10
Database Planning
Database Planning
Operational maintenance
Systems Definition
Testing
Requirements collection/analysis
Data conversion and loading
Management Activities
Database design
Implementation
DBMS selection
Prototyping (Application)
Application design
11
Database Planning

• Formulating an IS strategy
• Identify enterprise plans and goals with a
  subsequent determination of IS needs
• Evaluate current IS to determine and understand
  existing strength and weakness
• Appraise IT opportunities that might yield a
  competitive advantage

Enterprise Data Model
12
System Definition

• Identify boundaries (scope) of the new system
• Current users and application areas
• User views
• Ensures no major users forgotten from database
  requirements
• Defines data and transactions
• Future users and applications

13
Requirements Collection

• Information about each major user view
• Description of the data used or generated
• Details of how data is to be used or generated
• Any additional requirements
• Approaches to managing requirements for multiple
  user views
• Centralized gather all requirements to form a
  global data model
• View integration gather requirements of users
  to form local data models and combine local data
  models to form a global data model
• Combination centralized/view integration

14
Database Design

• Conceptual database design - process of
  constructing a model of the information used in
  an enterprise, independent of all physical
  considerations
• Logical database design - process of constructing
  a model of the information used in an enterprise
  based on a specific data model, but independent
  of a particular DBMS and other physical
  considerations
• Physical database design - process of producing a
  description of the implementation of the database
  on secondary storage it describes the base
  relations, file organizations, and indexes used
  to achieve efficient access to the data and any
  associated integrity constraints and security
  measures

15
DBMS Selection

• Define terms of reference of study State
  objectives and scope of study, and tasks
• Shortlist 2 to 3 products Depends on
  organization
• Evaluate products Create categories (groups)
  for evaluation based on features
• Scorecard approach
• Recommend selection and produce reports

16
Application Design

• Design of the user interface and the application
  programs that use and process the data
• Transaction Design
• Transaction is a event applied to the database
• Purpose is to define and document high-level
  characteristics of the transactions
• Data to be used
• Functional characteristics
• Output
• Importance to users
• Expected rate of usage

17
Application Design

• Transaction Design
• Three types of transactions
• Retrieval
• Update
• Mixed
• User Interface Design Guidelines
• User layout of form or report
• Guidelines
• Meaningful titles
• Comprehensible instructions
• Logical group and sequencing of fields

18
Application Design

• User Interface Design Guidelines
• Guidelines
• Visually appealing layout
• Familiar field labels
• Consistent terminology and abbreviations
• Consistent use of color
• Visible space and boundaries for data-entry
  fields
• Convenient cursor movement
• Error correction for individual characters and
  entire fields
• Error messages for unacceptable values

Follow form or report layout
Edit checks
19
Application Design

• User Interface Design Guidelines
• Guidelines
• Optional fields marked clearly
• Explanatory messages for fields
• Completion signal

20
Prototyping

• Working model less all required features and
  functionality
• Types
• Requirements (proof of concept) - Used to
  determine requirements and afterwards is
  discarded
• Evolutionary Used to determine requirements and
  evolves to become the actual application

21
Implementation andData Conversion and Loading

• Implementation
• Physical realization f the database and
  application designs
• Define database entities (i.e., users, tables,
  columns, views, etc.)
• Implement security and integrity controls
• Data conversion and loading
• Transfer existing data into new database
• Converting existing applications to run on the
  new database

22
Testing

• Testing
• Run database system with the intent of finding
  errors
• Methodology and test cases
• Usability criteria
• Learnability
• Performance
• Robustness (error tolerance)
• Recoverability
• Adaptability

23
Operational Maintenance

• Monitoring performance
• Maintaining and upgrading

24
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)

25
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
26
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

27
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
28
Fact-Finding Techniques

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

29
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
30
Data Modeling Entity Relationship Modeling
31
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

32
ER Modeling
Relationship type
Products Stock number Product description Retail
price Stock on hand Stock on order
Manufacturers Manufacturer code Manufacturer name
Attributes
Entity type
33
ER Modeling
Relationship type
Products Stock number (PK) Product
description Retail price Stock on hand Stock on
order
Manufacturers Manufacturer code Manufacturer name
Attributes
Entity type
34
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)
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
35
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
36
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
37
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
38
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
39
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
40
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
41
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
42
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
43
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)

44
Diagrammatic Representation
Customers Cust_account Cust_name First_name
Middle_name Last_name Cust_address
Street_number Zip_code (fk) Cust_phone Soc_sec_
num
Method to Follow
Key
Composite attribute
Composite attribute
Foreign key
45
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
46
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
47
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
48
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
49
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)
50
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
51
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.

52
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.
53
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.
54
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).

55
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.
56
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
57
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)
58
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)
59
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)
60
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
61
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
62
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
63
(No Transcript)