Chapter 2 Database Design

1
Chapter 2Database Design
Database Programming SAK 2406
2
Highlight

• Step in Designing System
• Database Schema
• E-R model

3
Database System Design
User views of data.
Conceptual data model.
Implementation (relational) data model.
Physical data storage.
Class diagram that shows business entities,
relationships, and rules.
Indexes and storage methods to improve
performance.
List of nicely-behaved tables. Use data
normalization to derive the list.
4
The Need for Design

• Goal To produce an information system that adds
  value for the user
• Reduce costs
• Increase sales/revenue
• Provide competitive advantage
• Objective To understand the system
• To improve it
• To communicate with users and IT staff
• Methodology Build models of the system

5
Designing Systems

• Designs are a model of existing proposed
  systems
• They provide a picture or representation of
  reality
• They are a simplification
• Someone should be able to read your design
  (model) and describe the features of the actual
  system.
• You build models by talking with the users
• Identify processes
• Identify objects
• Determine current problems and future needs
• Collect user documents (views)
• Break complex systems into pieces and levels

6
Design Stages

• Initiation
• Scope
• Feasibility study
• Cost Time estimates
• Requirements Analysis
• User Views Needs
• Forms
• Reports
• Processes Events
• Entity Attributes
• Conceptual Design
• Models
• Data flow diagram
• Entity Relationships
• Objects
• User feedback

• Physical Design
• Table definitions
• Application development
• Queries
• Forms
• Reports
• Application integration
• Data storage
• Security
• Procedures
• Implementation
• Training
• Purchases
• Data conversion
• Installation
• Evaluation Review

7
Initial Steps of Design

• 1. Identify the exact goals of the system.
• 2. Talk with the users to identify the basic
  forms and reports.
• 3. Identify the data items to be stored.
• 4. Design the classes (tables) and relationships.
• 5. Identify any business constraints.
• 6. Verify the design matches the business rules.

8
Database Schema

• Physical Schema
• Specifications for how data from conceptual
  schema are stored.
• Conceptual Schema
• overall structure of a database, ER-diagram
• External Schema
• User Views
• Subsets of Conceptual Schema
• Can be determined from business-function/data
  entity matrices
• DBA determines schema for different users
• This is part of people-management in databases

9
Figure 2.1 Three-schema database architecture
10
(No Transcript)
11
Figure 2.2 Three-tiered client/server database
architecture
12
The E-R Model

• E-R model a logical representation of the data
  for an organization or for a business area
• E-R diagram a graphical representation of an
  entity-relationship model

13
E-R Model Constructs

• Entity - person, place, object, event, concept
• Attribute - property or characteristic of an
  entity type (often corresponds to a field in a
  table)
• Relationship link between entities (corresponds
  to primary key-foreign key equivalencies in
  related tables)

14
Figure 2.3 Sample E-R Diagram
15
Figure 2.4 Basic E-R Notation
A special entity that is also a relationship
Entity symbols
Attribute symbols
Relationship symbols
16
Entity

• Entity Type vs. Entity Instance
• Entity Type - collection of entities (often
  corresponds to a table)
• Entity instance - a single occurrence of an
  entity type (often corresponds to a row in a
  table)
• Strong vs. Weak Entity Types
• Strong Entity Type entity that exists
  independently of other entity types
• Weak Entity Type entity type whose existence
  depends on some other entity type

17
Attributes

• Simple vs. Composite Attribute
• Simple attribute cannot broken into smaller
  components
• Composite attribute can broken into component
  parts
• Single-Valued vs. Multivalued Attribute
• Single-Valued each of the attributes has one
  value
• Multivalued attribute more than one value
• Stored vs. Derived Attributes
• Stored attribute data input or set
• Derived Attribute attribute whose values can
  be calculated from related attribute values.
• Identifier Attributes

18
Figure 2.5 Composite attribute
An attribute broken into component parts
19
Figure 2.6 Entity with a multivalued attribute
(Skill) and derived attribute (Years_Employed)
20
Identifiers (Keys)

• Identifier (Primary Key) - An attribute (or
  combination of attributes) that uniquely
  identifies individual instances of an entity type
• Composite Identifier an identifier that
  consists of a composite attribute.
• Candidate Key an attribute that could be a
  keysatisfies the requirements for being a key

21
Characteristics of Identifiers

• Will not change in value
• Will not be null
• No intelligent identifiers (e.g. containing
  locations or people that might change)
• Substitute new, simple keys for long, composite
  keys

22
Figure 2.5 (a) Simple key attribute
23
Figure 2.5 (b) Composite key attribute
24
Relationships

• Relationship Types vs. Relationship Instances
• The relationship type is modeled as the diamond
  and lines between entity typesthe instance is
  between specific entity instances
• Relationships can have attributes
• These describe features pertaining to the
  association between the entities in the
  relationship
• Two entities can have more than one type of
  relationship between them (multiple
  relationships)
• Associative Entity combination of relationship
  and entity

25
Figure 2.6 (a) Relationship type
Figure 2.6 (b) Entity and Relationship instances
26
Degree of Relationships

• Degree of a Relationship is the number of entity
  types that participate in it
• Unary Relationship
• Binary Relationship
• Ternary Relationship

27
Figure 2.7 Degree of relationships
28
Cardinality of Relationships

• One to One
• Each entity in the relationship will have exactly
  one related entity
• One to Many
• An entity on one side of the relationship can
  have many related entities, but an entity on the
  other side will have a maximum of one related
  entity
• Many to Many
• Entities on both sides of the relationship can
  have many related entities on the other side

29
Figure 2.8 Degree of relationships and Cardinality
(a) Unary relationships
30
(b) Binary relationships
31
(c) Ternary relationships
Note a relationship can have attributes of its
own
32
Cardinality Constraints

• Cardinality Constraints - the number of instances
  of one entity that can or must be associated with
  each instance of another entity.
• Minimum Cardinality
• If zero, then optional
• If one or more, then mandatory
• Maximum Cardinality
• The maximum number

33
Figure 2.8 Cardinality Mandatory and Optional
34
Figure 2.9 Cardinality Constraints
(a) Basic relationship with only maximum
cardinalities showing
(b) Mandatory minimum cardinalities
35
Figure 2.10 Examples of multiple relationships
Employees and departments entities can be
related to one another in more than one way
36
Strong vs. Weak Entities, andIdentifying
Relationships

• Strong entities
• exist independently of other types of entities
• has its own unique identifier
• represented with single-line rectangle
• Weak entity
• dependent on a strong entitycannot exist on its
  own
• Does not have a unique identifier
• represented with double-line rectangle
• Identifying relationship
• links strong entities to weak entities
• represented with double line diamond

37
Figure 2.10 Strong and weak entities
Identifying relationship
Strong entity
Weak entity
38
Associative Entities

• Its an entity it has attributes
• AND its a relationship it links entities
  together
• When should a relationship with attributes
  instead be an associative entity?
• All relationships for the associative entity
  should be many
• The associative entity could have meaning
  independent of the other entities
• The associative entity preferably has a unique
  identifier, and should also have other attributes
• The associative may be participating in other
  relationships other than the entities of the
  associated relationship
• Ternary relationships should be converted to
  associative entities

39
Figure 2.11 An associative entity
(a) An associative entity (CERTIFICATE)
Associative entity involves a rectangle with a
diamond inside. Note that the many-to-many
cardinality symbols face toward the associative
entity and not toward the other entities
40
(b)Ternary relationship as an associative entity