Database Design

1
Database Design

• Sections 4 5Subtype, Supertype, Mutually
  exclusive, non-transferability, transferable,
  11, 1M, MM, Redundant, Intersection entity,
  Barred relationship, First Normal Form (1NF)

2
DJs on Demand Partial ERD
3
Reserved Words

• A reserved work is one that has a specific
  meaning and function in the computer system,
  language, or database.
• Examples
• NULL
• DATE
• DISTINCT

4
Video Store example 4.1.7

• Identify business rules
• Construct ERD
• Class activity

5
Video Store Matrix
6
Video ERD

featured in
7
Use Source Documents

• Source document assist in identifying entities,
  attributes, and relationships

8
Global Fast Food

• Identify entities

9
Possible solution

STAFF id first name last name date of
birth salary
handle
handled by
ORDER number date time
FOOD ITEM number description price
list
listed on
10
Subtype Supertype

• Supertype (entity) can be subdivided into
  Subtypes
• Example

JOB manager clerk sales support
11
Attributes

• Supertype can have attributes at supertype level
• Attributes at supertype level are for all
  subtypes
• Subtypes can have own attributes for only that
  attribute

12
Example

13
Global Fast Food Employees

• STAFF is an entity and supertype
• What are the attributes of STAFF?
• What subtypes exist in STAFF?
• Are there any attributes in any of the subtypes?

14
Example 4.3.9

• AUTOMOBILE subtypes are not mutually exclusive
• AUTOMOBILE possible solution
• BUILDING subtypes have problem of not being
  exhaustive, should be minimum of 2 types

15
Business Rules

• Recognize if Business rule is implemented by the
  data model or by programming
• Example
• Only an employee with a title of manager can
  manage other employees
• The event planner must contact the customer
  before the DJ can be assigned
• Business rules can changed as society changes or
  business leaders feel change needed

16
Business rule types

• Structural
• Must be entered at time of initial data entry
• Example When ORDER entered a staff id must be
  entered
• Does not require code written to enforce
• Procedural
• Work flow related
• Programmed validation is procedural constraint
• Generally need to write code
• Example Commission can not be 25 of salary

17
Each entity has

• Name
• Optionality
• Cardinality
• Transferability

18
Relationship transferability

• Rules established for the purpose such as safety,
  efficiency, or increased profit
• Example Airline tickets
• can be transferable or non-transferable?
• Non-transferable means also not updatable
• symbol used to denote non-transferable
• blood type is non-transferable

19
Non-transferable relationship

• Orders are non-transferable
• Resolution would be to cancel order and create
  new order

20
Example 5.1.6 1

• Each town may be the birthplace of many people.
  Each person must be born in one and only one
  town.

21
Relationship types

• One to One (11)
• One to Many (1M)
• Many to Many (MM)

22
One to One relationship

• Usually few of these relationships
• Often mandatory at one side
• If mandatory at both ends be aware, it might be a
  error. Possible should be only one entity not
  two

23
One to Many

• Most common type are mandatory on one side and
  optional on the other
• Mandatory at both ends models entities that
  cannot exist without each other

24
One to Many example

25
Many to Many

• Often common in initial version of ERD
• Later stage of design converted to other type

26
Redundant relationship

• Redundant relationship is one that can be derived
  from another relationship in the model

27
Many to Many Relationship Resolution

• All MM relationships must be changed to a set of
  1M and M1 relationship
• Insert a transition entity between
• Occurs when you go from conceptual model to
  design model

28
Example MM with Intersection Entity

29
Resolving MM with Intersection

• Create an intersection entity
• If you cant find a good intersection entity
  create one by concatenate two entities together

30
Example

31
Normalization

• To make sure you dont store the same data twice
  in the model
• Also, that you store the data in the correct
  place
• We will cover the first 3 normal forms.

32
First Normal Form (1NF)

• First Normal Form requires that there be no
  multivalued attributes and no repeating groups.
• To check for 1NF, validate that each attribute
  has a single value for each instance of the
  entity.
• In other words One value per Attribute.

33
Example 1NF

• This has multiple values for the classroom.

SCHOOL BUILDING code name address classroom
34
Example 1NF

• Telephone is a multvalued attribute that could be
  an Entity. Ie. Home, work, mobile, fax etc.
• Not all Employees have multiple phones, so there
  would eliminate multiple NULL values.

EMPLOYEE id name address . . º telephone º
salary
35
Review 1NF examples

• Look at the 4 examples on 5.4.4