Storing Organizational Information Databases

1
Storing Organizational Information - Databases

• Dr. Alisha Malloy
• MIS 200 Spring 2007
• February 13, 2007

2
Learning Outcomes

• Define the fundamental concepts of the relational
  database model
• Evaluate the advantages of the relational
  database model
• Compare relational integrity constraints and
  business-critical integrity constraints
• Describe the role and purpose of a database
  management system
• List and describe the four components of a
  database management system
• Describe the two primary methods for integrating
  information across multiple databases

3
Relational Database Fundamentals

• Information is everywhere in an organization
• Information is stored in databases
• Database maintains information about various
  types of
• objects (inventory),
• events (transactions),
• people (employees), and
• places (warehouses)

4
Relational Database Fundamentals

• Database models include
• Hierarchical database model information is
  organized into a tree-like structure (using
  parent/child relationships) in such a way that it
  cannot have too many relationships
• Network database model a flexible way of
  representing objects and their relationships
• Relational database model stores information in
  the form of logically related two-dimensional
  tables

5
Entities

• Entity a person, place, thing, transaction, or
  event about which information is stored
• The rows in each table contain the entities
• CUSTOMER Table includes Daves Sub Shop, Pizza
  Palace, and Ts Fun Zone entities

6
Entities

• Entity class (table) a collection of similar
  entities
• CUSTOMER, ORDER, ORDER LINE, DISTRIBUTOR, and
  PRODUCT are entity classes

7
Attributes

• Attributes (fields, columns) characteristics or
  properties of an entity class
• The columns in each table contain the attributes
• Attributes for CUSTOMER include
• Customer ID
• Customer Name
• Contact Name
• Phone

8
Keys and Relationships

• Primary keys and foreign keys identify the
  various entity classes (tables) in the database
• Primary key (PK) a field (or group of fields)
  that uniquely identifies a given entity in a
  table
• Foreign key a primary key of one table that
  appears an attribute in another table and acts to
  provide a logical relationship among the two
  tables

FK
PK
PK
9
Potential relational database for Coca-Cola
10
Relational Database Advantages

• Database advantages from a business perspective
  include
• Increased flexibility
• Increased scalability and performance
• Reduced information redundancy
• Increased information integrity (quality)
• Increased information security

11
Increased Flexibility

• A well-designed database should
• Handle changes quickly and easily
• Provide users with different views
• Have only one physical view
• Physical view deals with the physical storage
  of information on a storage device
• Have multiple logical views
• Logical view focuses on how users logically
  access information

12
Increased Scalability and Performance

• A database must scale to meet increased demand,
  while maintaining acceptable performance levels
• Scalability refers to how well a system can
  adapt to increased demands
• Performance measures how quickly a system
  performs a certain process or transaction

13
Reduced Information Redundancy

• Databases reduce information redundancy
• Redundancy the duplication of information or
  storing the same information in multiple places
• Inconsistency is one of the primary problems with
  redundant information

14
Increase Information Integrity (Quality)

• Information integrity measures the quality of
  information
• Integrity constraint rules that help ensure the
  quality of information
• Relational integrity constraint rule that
  enforces basic and fundamental information-based
  constraints
• Business-critical integrity constraint rule
  that enforce business rules vital to an
  organizations success and often require more
  insight and knowledge than relational integrity
  constraints

15
Increased Information Security

• Information is an organizational asset and must
  be protected
• Databases offer several security features
  including
• Password provides authentication of the user
• Access level determines who has access to the
  different types of information
• Access control determines types of user access,
  such as read-only access

16
Database Management Systems

• Database management systems (DBMS) software
  through which users and application programs
  interact with a database

17
Database Management Systems

• Four components of a DBMS

18
Data Definition Component

• Data definition component creates and maintains
  the data dictionary and the structure of the
  database
• The data definition component includes the data
  dictionary
• Data dictionary a file that stores definitions
  of information types, identifies the primary and
  foreign keys, and maintains the relationships
  among the tables

19
Data Definition Component

• Data dictionary essentially defines the logical
  properties of the information that the database
  contains

20
Data Manipulation Component

• Data manipulation component allows users to
  create, read, update, and delete information in a
  database
• A DBMS contains several data manipulation tools
• View allows users to see, change, sort, and
  query the database content
• Report generator users can define report
  formats
• Query-by-example (QBE) users can graphically
  design the answers to specific questions
• Structured query language (SQL) query language

21
Data Manipulation Component

• Sample report using Microsoft Access Report
  Generator

22
Data Manipulation Component

• Sample report using Access Query-By-Example (QBE)
  tool

23
Data Manipulation Component

• Results from the QBE query

24
Data Manipulation Component

• SQL version of the QBE Query

25
Application Generation and Data Administration
Components

• Application generation component includes tools
  for creating visually appealing and easy-to-use
  applications
• Data administration component provides tools
  for managing the overall database environment by
  providing faculties for backup, recovery,
  security, and performance
• IT specialists primarily use these components

26
Integrating Information among Multiple Databases

• Integration allows separate systems to
  communicate directly with each other
• Forward integration takes information entered
  into a given system and sends it automatically to
  all downstream systems and processes
• Backward integration takes information entered
  into a given system and sends it automatically to
  all upstream systems and processes

27
Integrating Information among Multiple Databases

• Forward integration

28
Integrating Information among Multiple Databases

• Backward integration

29
Integrating Information among Multiple Databases

• Building a central repository specifically for
  integrated information

30
OPENING CASE STUDY QUESTIONSIt Takes A Village
to Write an Encyclopedia

• Identify the different types of entity classes
  that might be stored in Wikipedias database
• Explain why database technology is so important
  to Wikipedias business model
• Explain the difference between logical and
  physical views and why logical views are
  important to Wikipedias customers

31
Hotcourses Increases Revenues by 60 Percent

• Hotcourses is one of the hottest new e-businesses
  in London
• Offers a comprehensive online educational
  marketplace
• After two years of operation the company
  estimates revenues for 2003 between 100 and 500
  million

32
Chapter Seven Case Questions

• Identify the different types of entity classes
  and attributes potentially maintained in the
  Hotcourses database
• Describe the two different ways that employees at
  Hotcourses might access the information in their
  databases
• Create two questions that a manager at Hotcourses
  could turn into queries and run against a
  database to discover business intelligence
• List several useful reports management would like
  to see based on data in the database
• Would different entity types be required for
  different countries?