6-1

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)

6-2

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

6-3

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

6-4

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

6-5

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

6-6

Reduced 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

6-7

Increased 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 organization’s success and often require more insight and knowledge than relational integrity constraints

6-8

Increased 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

6-9

• Entity – a person, place, thing, transaction, or event about which information is stored– The rows in each table contain the entities– In Figure 6.5 CUSTOMER includes Dave’s Sub Shop

and Pizza Palace entities

• Entity class (table) – a collection of similar entities– In Figure 6.5 CUSTOMER, ORDER, ORDER LINE,

DISTRIBUTOR, and PRODUCT entity classes

RELATIONAL DATABASE FUNDAMENTALS

6-10

• Attributes (fields, columns) – characteristics or properties of an entity class– The columns in each table contain the attributes– In Figure 6.5 attributes for CUSTOMER include:

• Customer ID• Customer Name• Contact Name• Phone

RELATIONAL DATABASE FUNDAMENTALS

6-11

• Primary keys and foreign keys identify the various entity classes (tables) in the database– Primary key – 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

RELATIONAL DATABASE FUNDAMENTALS

6-12

Potential relational database for Coca-Cola

6-13

DATABASE MANAGEMENT SYSTEMS

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

6-14

DATABASE MANAGEMENT SYSTEMS

• Four components of a DBMS

6-15

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

6-16

Data Definition Component

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

6-17

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

6-18

Data Manipulation Component

• Sample report using Microsoft Access Report Generator

6-19

Data Manipulation Component

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

6-20

Data Manipulation Component

• Results from the query in Figure 6.10

6-21

Data Manipulation Component

• SQL version of the QBE Query in Figure 6.10

6-22

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

6-23

INTEGRATING DATA 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

6-24

INTEGRATING DATAAMONG MULTIPLE DATABASES

• Forward and backward integration

6-25

INTEGRATING DATAAMONG MULTIPLE DATABASES

• Building a central repository specifically for integrated information