Database planning

1
The Database Application Lifecycle

• Database planning
• System definition
• Requirements collection and analysis
• Database design
• Application design
• Implementation
• Data conversion and loading
• Testing
• Maintenance

2
Database design The process of creating a
design for a database that will support the
enterprises operations and objectives.

• Conceptual database design
• Logical database design
• Physical database design

3
Criteria for Optimal Data Model

• Structural validity
• Simplicity
• Expressability
• Non-redundancy
• Shareability
• Extensibility
• Integrity
• Diagrammatic representation

4
Conceptual database design The process of
constructing a model of the information used in
an enterprise, independent of all physical
consideration.

• Identify entity types and relationship types
• Identify and associate attributes with entity or
  relationship types
• Determine attribute domains
• Determine candidate and primary key attributes

5
Problems with ER Models

• Fan trap where a model represents a relationship
  between entity types, but the pathway between
  certain entity occurrences is ambiguous.
• Chasm trap where a model suggests the existence
  of a relationship between entity types, but the
  pathway does not exist between certain entity
  occurrences.

6
Logical database design The 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.

• Map local conceptual data model to local logical
  data model
• Derive relations from local logical data model
• Validate model using normalization
• Validate model against user transactions
• Define integrity constraints

7
Refine the Conceptual Data Model

• Remove MN relationships
• Remove complex relationships
• Remove recursive relationships
• Remove multi-valued attributes
• Re-examine 11 relationships
• Remove redundant relationships

8
Normalization
A technique for producing a set of relations with
desirable properties, given the data requirements
of an enterprise.
9
Un-Normalized Form (UNF)
A table that contains one or more repeating
groups.
10
Potential Problems with Improper Relations

• Insertion Anomalies
• Deletion Anomalies
• Modification Anomalies

11
First Normal Form (1NF)
A relation in which the intersection of each row
and column contains one and only one value.
12
Functional Dependency
Let A and B are attributes of relation R. The
attribute B is functionally dependent on the
attribute A (A ? B) if each value of A is
associated with exactly one value of B.
13
Full Functional Dependency
B is fully functionally dependent on A if B is
functionally dependent on A but not on any proper
subset of A.
14
Second Normal Form
A relation that is in first normal form and every
non-primary-key attribute is fully functionally
dependent on the primary key.
15
Transitive Dependency
A condition where A, B, and C are attributes of a
relation such that if A ? B and B ? C, then C is
transitively dependent on A via B (provided that
A is not functionally dependent of B or C).
16
Third Normal Form (3NF)
A relation that is in first and second normal
form, and in which no non-primary-key attribute
is transitively dependent on the primary key.
17
Boycy-Codd Normal Form (BCNF)
A relation is in BCNF if and only if every
determinant is a candidate key.