Database management systems

1
Database management systems

• The Entity Relationship Model

2
The Steps of Database Design

• Requirements Analysis
• Conceptual Design
• Logical Design
• Physical Design

3
Class Hierarchies

• Class Hierarchies
• Entities can be placed in subclasses
• Inheritance by attributes of the subclass
  subclass ISA class
• Queries on a class must consider the subclass
  entities as well
• There can be multiple ISA nodes

4
Class Hierarchies

• Can be viewed in 2 ways
• Class specialized into subclass
• Subclasses generalized by class
• Constraints on ISA hierarchies
• Overlap
• Covering
• Why have them?
• Specific descriptive attributes
• To easily identify all participants

5
Aggregation

• Differs from relationships, which associate
  entities
• Useful when a relationship set participates in
  another relationship set

6
Entity Relationship Diagrams

• Fan traps ambiguous ternary relationships
• Who works where?
• Chasm traps missing binary relationships among
  ternary relationships
• The lost project

Staff
Division
Department
lt Employees
Operates gt
Department
Staff
Project
Employees gt
Works on gt
7
ER conversion

• ER diagrams communicate through entities and
  relationships, but are not designed for data
  storage and retrieval
• Converting to a relational model requires a
  step-wise process that considers the conversion
  of both into tables

8
ER Conversion

• Make tables easy to explain
• Identify and apply keys to
• eliminate unnecessary redundancies
• avoid spurious tuples on joins
• Example
• Minimize nulls
• Verify attributes

Steps pSP pPD SP?PD
9
ER Conversion

• Identify all entities, relationships and
  aggregations
• Identify and type all attributes
• Simple or Composite
• Single or Multi-valued
• Derived
• List and assign all attributes to an entity,
  watching for
• Multiple entities for an attribute
• Data as attributes

10
ER Conversion

• Strong Entity Types
• Create table, including all attributes
• Determine primary key
• Weak Entity Types
• Create table, including all attributes and
  primary key from parent
• Determine primary key from owner and weak entity
  attributes

11
ER Conversion

• Relationship types
• 1 relationship
• Copy primary key from table on 1 side to table on
  N side
• Make copied column foreign key
• relationship
• Create table that includes all attributes of
  relationship
• Copy primary keys from each table participating
  in relationship, making them foreign keys
• Create primary key from collection of foreign keys

12
ER Conversion

• 11 relationship
• Total participation on both sides
• Merge tables into one table, or
• Copy primary key from one table to the other and
  make copied column a foreign key
• Partial participation on one side
• Copy primary key of partial participant into
  total participant and make copied column a
  foreign key
• Partial participation on both sides
• Copy primary key from one participant into the
  other and make the copied column a foreign key

13
ER Conversion

• 11 recursive relationship
• Total participation on both sides
• Create one table with two copies of primary key,
  using one as foreign key
• Partial participation on one side
• Create one table with two copies of primary key
  using one as foreign key, or
• Create one table with primary key and one table
  with two copies of primary key, making total
  participant be primary key and partial
  participant be foreign key
• Partial participation on both sides
• Create one table with primary key and one table
  with two copies of primary key, , making one be
  primary key, one be foreign key

14
ER Conversion

• Multi-valued attributes
• Create table with an attribute for multi-values
  and with primary key from parent entity to serve
  as foreign key to parent table
• Make foreign key attributes and multi-value type
  attribute primary key

15
Validation
Division
has
1
N

• Validate with normalization
• Typically BCNF or 3NF
• Validate with data entry
• Check for
• locked loops constraints limit entry due to
  total participation requirements in two or more
  tables
• fan traps ambiguous results due to fan out of
  two or more 1N relationships
• chasm traps suggested relationship where one or
  more tables require partial participation

Staff
N
oversee
N
1
supervising
1
Managers
Division
has
1

Staff
1
owns

Branches
Division
has
1..1
1..
Staff
1..1
Sells
0..
Product Line