Database Design

1
Database Design

• Sections 4 5Subtype, Supertype, Mutually
  exclusive, non-transferability, transferable,
  11, 1M, MM, Redundant, Intersection entity,
  Barred relationship, CRUD analysis

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 next slide

• Identify business rules
• Construct ERD
• Class activity

5
Video Store example

• Read the given business scenario and walk through
  the steps below.
• 1. Examine the nouns. Are they things of
  significance?
• 2. Name each entity.
• 3. Is there information of interest about the
  entity that the business needs to hold?
• 4. Is each instance of the entity uniquely
  identifiable? Which attribute or attributes could
  serve as its UID?
• 5. Write a description of the entity.
• 6. Diagram each entity and a few of its
  attributes.  Remember, entity names are always
  singular.
• 7. Identify the relationships and draw them on
  the ERD.

6
Video ERD

featured in
7
Use Source Documents

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

8
Subtype

• A subtype
• inherits all attributes of the supertype
• inherits all relationships of the supertype
• usually has its own attributes or relationships
• is drawn within the supertype
• never exists alone
• may have subtypes of its own
• is also known as a "subentity

9
Subtype Supertype

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

JOB manager clerk sales support
10
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

11
Example

12
Global Fast Food Employees

• STAFF is an entity with supertypes
• What are the attributes of STAFF?
• id
• first name
• last name
• DOB
• salary
• What subtypes exist in STAFF?
• ORDER TAKER
• overtime
• COOK
• training
• OTHER
• Are there any attributes in any of the subtypes?

13
Example an incorrect subtypes in the
illustration.

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

14
Subtype Needs

• Exhaustive
• Every instance of the supertype is also an
  instance of one of the subtypes.
• Treating all parts or aspects without omissions.
• 2 or more items
• Mutually exclusive
• Every instance of the supertype is of one and
  only one subtype.
• A relationship that presents choices which are
  unable to be true at the same time.

15
Correctly identify subtypes

• When considering supertypes and subtypes, you can
  use three questions to see if the subtype is
  correctly identified
• Is this subtype a kind of supertype?
• Have I covered all possible cases? (exhaustive)
• Does the example fit into one and only one
  subtype? (mutually exclusive)

16
Nested subtypes
17
Documenting Business Rules

• Source documents assist in identiying
• entities
• attributes
• relationships
• Some business rules are implemented by
  programming

18
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

19
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

20
Structural Business Rule examples
21
Procedural Business Rule example
22
Discussion

• Students must have studied algebra and geometry
  in order to sign up for trigonometry. Could you
  represent this in the ERD?
• How would you implement this with programming?
• If the student had taken the subjects, can you
  think of an additional business rule that a
  school may want in this scenario?

23
Each entity has

• Name
• Optionality
• Cardinality
• Transferability
• Example on next slide.

24
Relationship Transferability
25
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

26
Non-transferable relationship

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

27
Example 5.1 exercise 1 a

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

28
Relationship types

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

29
One to One relationship
30
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

31
One to One relationship
32
One to Many relationship
33
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

34
One to Many example

35
Many to Many relationship
36
Many to Many

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

37
Redundant relationship

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

38
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

39
Example MM with Intersection Entity

40
Resolving MM with Intersection

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

41
Example

42
CRUD analysis

• Create ER models that reflect all business rules
  gathered during the interview process
• Identify the create, retrieve, update, and delete
  (CRUD) requirements of the business
• Validate your ER model by performing a CRUD
  analysis

43
CRUD requirements

• CRUD analysis will help you
• Translate between the consultant and the client.
• Provide a guideline for the DBA who will
  eventually build the database.
• Check that you havent missed any entities or
  relationships in your data model.
• Check to make sure that you havent modeled
  anything that the business does not require.

44
What is CRUD?

• CRUD is an acronym for create, retrieve, update,
  delete.
• Check for completeness and accuracy.

45
Create keywords

• Look for these words when you interview the
  client.
• INPUT
• ENTER
• LOAD
• IMPORT
• RECORD
• CREATE

46
Retrieve keywords

• Look for
• VIEW
• REPORT
• BRING UP
• PRINT
• FIND
• READ
• LOOK UP

47
Update keywords

• Look for
• CHANGE
• MODIFY
• ALTER
• UPDATE

48
Delete keywords

• Look for
• DISCARD
• REMOVE
• TRASH
• PURGE
• DELETE

49
CRUD analysis

• If you have a business function that has no
  entity to CRUD against, then your data model may
  be incomplete. Similarly, if you have entities in
  your ERD that are not touched by any CRUD (there
  is no business function that creates, retrieves,
  updates, or deletes from it), then you may not
  need that entity in your model.