DATABASE DESIGN

1
DATABASE DESIGN

• DATABASE DESIGN AS A MODELING PROCESS
• Models as vehicles for understanding reality
• DATABASE DESIGN AS AN INTUITIVE PROCESS
• Satisficing vs. Rationality
• DATABASE DESIGN AS AN ITERATIVE PROCESS
• Prototyping

2
THREE SCHEMA DATA MODEL

• Data Model
• A way of representing data that models some part
  or parts of an organization.
• Schema
• Simply, a representation of something, perhaps a
  blueprint or a flowchart.
• 3-Schema Model

Internal Schema (Physical)
External Schema
Conceptual Schema (Logical)
External Schema
Database
External Schema
3
THREE SCHEMA DATA MODEL (Contd)

• External Schema User views
• Axiom Different users have different needs and
  different views of the database
• Conceptual Schema Logical view of the DB
  containing all data and relationships
• Internal Schema Physical representation of the
  Conceptual Schema in a target DBMS

4
CONCEPTUAL SCHEMA / DATA MODEL METHODOLOGIES

• Entity-Relationship (E-R) developed by Chen
  (1976) still the most widely used technique
• IDEF1X National standard E-R model
• Unified Modeling Language (UML) object-oriented
  system development methodology object friendly
  but not DB friendly
• Semantic Object Modeling (SOM) developed by
  Kroenke uses objects to model the semantics of
  the data, i.e. what the data means nice
  crossover between E-R and UML
• Relational Model data expressed as tables not
  semantically expressive not as accessible to end
  users other methodologies are generally used for
  modeling, then transformed into relations at
  implementation time

5
EXTENDED E-R MODEL

• Basic E-R Model

6
ENTITY CLASS and ENTITY INSTANCE
Entities are simply things which can be
identified in the environment being modeled.
7
ATTRIBUTES

• Attributes describe an Entitys characteristics
• Attributes may be simple (e.g., Zip) or composite
  (e.g., Address)
• Attributes may be single-valued (e.g., Zip) or
  multi-valued (e.g., PhoneNumber)
• Each Attribute has a domain of possible values
  which it can assume for any Entity instance
  (e.g., State may be any of 50 two-character
  abbreviations PhoneNumber may be (nnn) nnn-nnnn
  where n is an integer between 0 and 9)

8
RELATIONSHIPS

• Relationships involve associations between two or
  more entity classes
• Relationships may involve more than 2 entities,
  however we focus upon binary relationships which
  involve just 2 entities.
• There are 3 types of binary relationships
• One-to-One (11)
• One-to-Many (1N)
• Many-to-Many (MN)

9
BINARY RELATIONSHIPS

• One-to-One (11)
• One-to-Many (1N)
• Many-to-Many (MN)

IS_ASSIGNED_TO
EMPLOYEE
AUTO
11
IS_ASSIGNED
RESIDES_IN
DORMITORY
STUDENT
1N
HOUSES
HAS_MEMBERS
CLUB
STUDENT
MN
IS_MEMBER_OF
10
RELATIONSHIP CARDINALITIES

• Relationships have maximum cardinality and
  minimum cardinality associated with them which
  are the maximum and minimum number of entity
  instances involved in the relationship
  respectively.
• The maximum cardinality numbers are those
  contained within the relationship diagram.
• The minimum cardinality numbers are either 0 or 1
  and are shown as ovals or hash marks on the
  relationship lines respectively.
• A DORMITORY HOUSES at least 1 STUDENT and can
  HOUSE many STUDENTs simultaneously, a STUDENT
  may or may not reside in a DORMITORY, but can
  RESIDE in, at most, one DORMITORY.

RESIDES_IN
DORMITORY
STUDENT
1N
HOUSES
11
WEAK ENTITIES

• WEAK ENTITY cannot exist unless another type of
  entity already exists.

EMPLOYEE
FAMILY DEPENDENT
1N
EMPLOYEE may exist without any FAMILY DEPENDENTs,
but not vice versa.
12
ID-DEPENDENT ENTITY

• A special case of WEAK ENTITY where the
  identifier of the dependent entity includes the
  identifier of the entity upon which it depends.

BUILDING
APARTMENT
1N
Identifier BldgName Identifier
BldgName, AptNum
13
(No Transcript)
14
SUBTYPE ENTITIES

• Previous binary relationships are HAS_A
  relationships
• Subtype entities are used to represent IS_A
  relationships (also referred to as
  specialization hierarchies or generalization
  hierarchies)
• E.g., a zebra is an ungulate is a mammal is an
  animal is a specialization hierarchy going down
  the tree and a generalization hierarchy going up
  the tree
• A Subtype entity is a special case of an already
  existing entity
• Each Subtype inherits all the attributes of its
  parent type.

15
SUBTYPE ENTITY (EXCLUSIVE)
An INDIVIDUAL CLIENT IS_A CLIENT. Each CLIENT
must be one (minimum cardinality 1), and only
one (maximum cardinality 1), of INDIVIDUAL,
PARTNERSHIP, or CORPORATE.
16
SUBTYPE ENTITY (NON-EXCLUSIVE)
A CLIENT USING PCs IS_A CLIENT. A CLIENT need
not be using PCs, MINIs, or MAINFRAMEs (minimum
cardinality 0), but can use any combination of
those computers (the m on the arc).
17
EXTENDED E-R EXAMPLE