Business Database Management Systems

1
Business Database Management Systems

• Class 4
• DSC 544/444
• Fall 2006

2
Reminders

• Turn in Group Assignment 1
• Presentations start next week
• October 9 AutoTrader.com
• October 11 KelleyBlueBook.com

3
Agenda

• A Second In-Class Design Exercise
• Overview of Database Design
• Conceptual design for databases
• Entities, attributes, and relationships

4
Database design overview

• The design challenge is all about defining the
  information architecture
• What information needs to be stored?
• What database table structures can hold and
  properly relate the data so that we can pull the
  desired information back out of the database?
• Fortunately, methodologies exist to help us
  fashion good information architecture!

5
Database design overview

• What we dont need to be concerned about when
  designing our information architecture
• Who will enter the data, and when?
• What will the user interface look like (e.g.,
  will there be drop-down menus or)?
• Will the data be displayed in a web-browser or a
  different application?
• In what sequence is data entered, and what
  triggers data-entry to occur?
• These application-development issues do not
  impact the information architecture in any
  substantive way, so thankfully we can ignore
  these questions during our design phase.

6
Tables, yes easy, no.

• Whats the big deal Its only tables!, right?
• As you have seen, being an information-architect
  can be challenging
• Even small-scale organizations present
  complexities
• Consider SAP scale designs!

7
A Design-Hierarchy Framework

• Recommended sequencing of design efforts
• Conceptual
• Defining the information content and all
  relationships
• Logical
• Defining the database structures (tables,
  typically, or objects in an O-O database)
• Physical
• Implementation details regarding the precise
  computer platform (considering both hardware
  software)

8
A Design-Hierarchy Framework

• Where within this framework did your team fall,
  when you faced the ski-club and fundraising
  organizations challenges?
• Conceptual?
• Logical?
• Physical?
• I hope not!

9
What is Conceptual Design?

• An answer to a common corporate 911 call
• Conceptual design methods will provide the basis
  for a powerful visual approach for grappling with
  corporations complex information needs.

Where do we begin?! We are finding it hard to
define and structure all the information that our
department uses!
10
Conceptual Design Basics

• Focus on the real-world problem at hand
• About what (things) are you collecting
  information about?
• Such things will be called entities.
• What things potentially need to be associated
  with other things?
• Such associations will be called relationships.
• Do not focus on tables
• Tables will ultimately be the vehicle for
  implementing our conceptual design, but we dont
  want to worry about tables yet

11
Conceptual Design Importance

• Common misunderstanding
• Conceptual design seems so vague. I should write
  down my tables to be more precise.
• Wrong! Given a nicely defined conceptual design,
  it is an easy matter to convert the design to a
  logical design.
• Conceptual design is the essence of the design
  effort.
• We will also learn how to convert from a
  conceptual design to the logical design.

12
Before Conceptual Design Begins

• A critical phase that precedes the conceptual
  design effort is called Requirements Analysis
• Ask what should the desired system do?
• Review/critique current system (if any)
• Collect relevant paper forms
• Historically, the 1 source of information
• Discussions with intended users
• Result of the requirement analysis step should be
  a full accounting of all information the new
  system should handle
• Conceptual design then addresses the architecture
  of that information

13
Conceptual Design3 Primary Questions

• What are the entities?
• Each entity will become a table in the
  (relational) logical design.
• What are the attributes?
• Each attribute will become a column in the
  (relational) logical design.
• What are the relationships?
• Each relationship will become either a column or
  a new table in the logical design

14
Conceptual Design Entities

• What are entities?
• You create a database to keep track of
  information about certain things.
• We call each different category of thing an
  entity
• What are examples of entities in the University
  of Oregons database?
• ___________
• ___________
• ___________
• ___________

15
Conceptual Design Attributes

• What are attributes?
• You create a database to keep track of
  information about certain things.
• We call each piece of entity-specific information
  an attribute
• What are examples of attributes for students in
  the Univ. of Oregons database?
• ___________
• ___________
• ___________
• ___________

16
Conceptual Design Relationships

• What are relationships?
• You create a database to keep track of
  information about certain things.
• We call information that connects entity
  instances (either across entities, or within a
  single entity class) a relationship
• What are examples of relationships for a class in
  the Univ. of Oregons database?
• ___________
• ___________
• ___________
• ___________

17
Conceptual Design Relationships

• Understanding when a relationship exists
• Consider
• Is there a relationship?
• Knowing when a relationship exists requires that
  we understanding the business/problem context

Lockers
Gym members
18
Entities and Attributes

• In a conceptual model, an entity is defined by 3
  things
• Its name
• Its identifier
• Its attributes
• The name should be descriptive, but brief.
• The identifier must be some piece(s) of data (can
  be an arbitrary 1,2,3,) that will be unique
  for each instance of the entity.

19
An example entity

• Consider a grade entity in the Univ. database
• Official name StudentGrades
• What is a valid identifier for each entity
  instance (i.e., a particular grade in the univ.
  db)?
• _________________________________
• Or ____________
• What should its attribute(s) be?
• _________________________________

20
An example entity (cont.)

• What are the obvious relationships for a grade
  entity?
• Think of a particular grade of B floating
  around in the univ. database.
• To what other entity (instances) should we be
  able to relate any given grade to?
• At a minimum, we need to relate that grade to a
  particular _________, and
• to a particular ___________.
• And, indirectly, to what other entities?
  
• e.g., _____________________________

21
Entities for Ski Club

• ________________
• ________________
• ________________
• ________________
• ________________
• ________________

22
Entities for Fundraising organization

• ________________
• ________________
• ________________
• ________________
• ________________
• ________________

23
Listing Attributes for Each Entity

• A crucial output of the requirements analysis
  phase is your understanding of what pieces of
  information (i.e., attributes!) you wish to
  collect.
• We need to define, for each entity, the list of
  attributes.
• Let us now do so specifically for the member
  entity in our two club scenarios.

24
Brainstorm Attributes for Ski Club member

• ________________
• ________________
• ________________
• ________________
• ________________

• ________________
• ________________
• ________________
• ________________
• ________________

25
Brainstorm Attributes for Fundraising club
member

• ________________
• ________________
• ________________
• ________________
• ________________

• ________________
• ________________
• ________________
• ________________
• ________________

26
Relationships big-picture thinking

• Because relationships can (and typically do) span
  entities, we need to think outside of the box
• Let us draw each entity as a labeled box, and
  then use lines to denote relationships between
  those entities.
• We again consider our two club scenarios to
  highlight the meaning of the term relationships.

27
Relationships Ski Club(ignore rental entity for
now)
Ski Area
Member
Trip
Meeting
Equipment
28
Relationships Fundraising club(ignore donation
entity for now)
Donor
Event
Charity
Member
Committee
29
of tables we will have in the relational
database

• Each entity will become a table in the
  (relational) logical design.
• Each attribute will become a column in the
  (relational) logical design.
• Each relationship will become either a column or
  a new table in the logical design
• So, ( tables) ( entities)
• e.g., even if both the Ski Club and Fundraising
  organizations imply 6 entities, we might find the
  Ski Club implies more tables due to its different
  relationship structures.

30
Designing with Visualization

• Business-oriented database applications are
  highly sophisticated
• The business world the most complex database
  environment there is!
• Many external information sources
• Even more data generated within the firm
• Big picture visualization (as on prior 2
  slides) techniques help users/designers
  understand the problem
• We will need to be proficient at ER diagramming
  techniques (next time)

31
Summary

• 3 design levels conceptual, logical, physical
• We will first master conceptual design
• Defining entities, attributes, and relationships
• With these design elements in hand, we will then
  be ready to conquer the logical design phase
• We have defined the elements of a conceptual data
  model
• So, we now know the essentials of what a data
  model is, but we still need an approach for
  creating one
• Visualization via ER-diagramming will help us
  bring our conceptual models to life quickly, and
  with fewer errors