Chapter 1 overview of database concepts
This presentation is the property of its rightful owner.
Sponsored Links
1 / 88

Chapter 1 Overview of Database Concepts PowerPoint PPT Presentation


  • 57 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 1 Overview of Database Concepts. Jason C.H. Chen , Ph.D. Professor of MIS School of Business, Gonzaga University Spokane, WA 99258 USA [email protected] Objectives. Define database terms Identify the purpose of a database management system (DBMS)

Download Presentation

Chapter 1 Overview of Database Concepts

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 1 overview of database concepts

Chapter 1Overview of Database Concepts

Jason C.H. Chen, Ph.D.

Professor of MIS

School of Business, Gonzaga University

Spokane, WA 99258 USA

[email protected]


Objectives

Objectives

  • Define database terms

  • Identify the purpose of a database management system (DBMS)

  • Explain database design using entity-relationship models and normalization

  • Explain the purpose of a Structured Query Language (SQL)

  • Understand how this textbook’s topics are sequenced and how the two sample databases are used


What is information

DATA

What is Information ?

INFORMATION

Information is refined data.


Data vs information

Data vs. Information

  • Users really want is

    • Information

  • What users can learn from the data

    • how to satisfy their best customers

    • how to allocate their resources most efficiently,

    • how to minimize losses


Objectives of the mis

Objectives of the MIS

who has whatinformation about whom and when,where, and how will all be decided in the process of building an information system.

Deliver the right information

to the right people,

at the right time,

with the right form.

Ultimately, MIS should improve the workers’ productivity.


Chapter 1 overview of database concepts

A Relationship Between the Organization and the DataBase

Organizational

IS Resources:

1. Hardware

2. Software

3. Data

4. Procedures

5. People

Picture

Or

Model

Old

State

Activities

Events

New

State

DataBase

Management

Systems

Transactions

(Add, modify, delete, …)

D.B.

Correspond with the processing of the events

Information must be:

1. Time: Timely,

2. Content: Accurate, relevant, and verifiable.

3. Form: Presented in a useable form.

User

TM 0-6


Questions

Questions

  • Is MS/Access or Oracle (SQL/Server or IBM DB2) a data base?

  • Why don’t we just create a “huge” data file containing all fields (columns)?

    • Have you ever created a database (with many applications) with only one “huge” data file containing all fields (columns)?


Traditional file processing approach

Traditional File Processing Approach

  • Traditional File Processing

    • An application uses one specialized file. For example, purchase order processing application uses data about supplier and products, while an order-taking application uses data about customer, products and orders.

  • Problems:

    • Data dependence: data structures are tightly coupled with applications. In other words, we prefer data independence.

    • Data redundancy: same data are repeatedly saved for different applications.

    • Other problems


Types of data processing

Types of Data Processing

  • Two types of data processing

    • File-based (traditional) data processing

      • applications developed by Java

    • Data-based data processing

      • applications developed by Oracle or MS/Access)


Chapter 1 overview of database concepts

Duplicate Data

Three file processing systems at a Furniture Company

What is the main problem in the company’s processing systems?


Disadvantages of file processing

Disadvantages of File Processing

  • Program-Data Dependence

    • All programs maintain metadata for each file they use

  • Data Redundancy (Duplication of data)

    • Different systems/programs have separate copies of the same data

  • Limited Data Sharing

    • No centralized control of data

  • Lengthy Development Times

    • Programmers must design their own file formats

  • Excessive Program Maintenance

    • 80% of of information systems budget


Database management system

Application

#1

Application

#2

Application

#3

Database

containing

centralized

shared data

Database Management System

DBMS

DBMS manages data resources like an operating system manages hardware resources

What are the advantages of employing Data-based Data Processing?


Traditional file processing

Traditional File Processing


Database management systems

Database Management Systems


Database terminology

Database Terminology

  • Database – an organized collection of logically related data files

  • Database management system (DBMS) – software used to create and interact with the database


Database components

Database Components

  • Character

    • Basic unit of data

    • Can be a letter, number, or special symbol

  • Field

    • A group of related characters

    • Represents an attribute or characteristic of an entity

    • Corresponds to a column in the physical database

  • Record

    • A collection of fields for one specific entity

    • Corresponds to a row in the physical database

  • File

    • A group of records about the same type of entity


Relational databases

Relational Databases

  • Data is organized in tables

    • Columns (fields) represent different data categories

    • Rows (records) contain actual data values


Database example

Database Example


Components example

Components Example


Relational database terms

Relational Database Terms

  • Entity:an object about which you want to store data

  • Table, column, row

    • Flat file, attribute (or field), record

  • Relationships:links that show how different records are related

  • Key Fields: establish relationships among records in different tables

  • Five main types of key fields:

    • primary keys

    • candidate keys

    • surrogate keys

    • foreign keys

    • composite keys


Primary keys vs candidate keys

Primary Keys vs. Candidate Keys

  • Primary key

    • Value must be unique for each record

    • Serves to identify the record

    • Present in every record

    • Can’t be NULL

    • Should be numeric

  • Candidate key

    • Any field that could be used as the primary key

    • Should be a unique, unchanging numeric field


Surrogate keys

Surrogate Keys

  • Surrogate key: created to be the record’s primary key identifier when no suitable primary key exists

  • Surrogate key has no real relationship to the record to which it is assigned, other than to identify the record uniquely

  • Developers configure the database to generate surrogate key values automatically

  • In an Oracle database, you can automatically generate surrogate key values using a sequence

  • Surrogate keys are always numerical fields, because the database generates surrogate key values automatically by incrementing the previous value by one


Foreign keys

Foreign Keys

  • Foreign key:a field in a table that is a primary key in another table

  • Foreign key creates a relationship between the two tables

  • Foreign key value must exist in the table where it is a primary key


Composite key

Composite Key

  • Composite key: a unique key that you create by combining two or more fields

  • Usually comprised of fields that are primary keys in other tables

Composite

Key


Database management system1

Database Management System

  • Data storage: manage the physical structure of the database

  • Security: control user access and privileges

  • Multiuser access: manage concurrent data access

  • Backup: enable recovery options for database failures

  • Data access language: provide a language that allows database access

  • Data integrity: enable constraints or checks on data

  • Data dictionary: maintain information about database structure


Client server database management systems

Client/Server Database Management Systems

  • Client/server database

    • Takes advantage of distributed processing and networked computers by distributing processing across multiple computers

    • DBMS server process runs on one workstation, and the database applications run on separate client workstations across the network

    • Preferred for database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records because they minimize network traffic and improve response times

  • Organizations generally use a client/server database if the database will have more than 10 simultaneous users and if the database is mission critical


The oracle11g client server database

The Oracle11g Client/Server Database

  • Oracle11gis the latest release of Oracle Corporation’s relational database management system

  • All Oracle server- and client-side programs use Oracle Net, a utility that enables the network communication between the client and the server


Chapter 1 overview of database concepts

DATABASE TRENDS

Linking Internal Databases to the Web

N


Web based client server database architecture

2. Request for

data-based Web page

1. Request for

data-based Web page

3. Data query

8. Data-based

Web page

6. Retrieved data

7. Data-based

Web page

Web Browser

Web server

4. Data

query

5. Retrieved

data

Legend

Communications

Between Web browser

And Web server

Communications

Between Web server

And database server

Database Server

Web-Based Client/Server Database Architecture

Network


System response time depends on

System Response TimeDepends on ...

  • the speed of the network

  • the size of the database

  • the way the database is used

  • a personal database running on a server might handle 10 users making database transactions at the same time before becoming overloaded.


Advantages of client server database management systems

Advantages of Client/Server Database Management Systems

  • Handling server and client failures

  • Processing transactions

  • Handling high data volumes

  • Providing security

  • Servicing multiple simultaneous users


Sql sql plus and pl sql

Language or Tool

Description

SQL

A command language for communication with the

Oracle 10/11Server from any tool or application. Oracle

SQL contains many extensions.

SQL*Plus

An Oracle tool that recognizes and submits SQL and

PL/SQL statements to the Server for execution and

contains its own command language.

PL/SQL

An Oracle Procedural Language for writing application

logic and manipulating data outside the database.

SQL, SQL*Plus, and PL/SQL

Terminal

Server

SQL*Plus

Buffer

SQL and

PL/SQL

Scripts


Design principles

Design Principles

  • To avoid creating tables that contain redundant data, group related items that describe a single entity together in a common table

  • Do not create tables that duplicate values many times in different rows

  • When creating a database and inserting data values, you must specify the data type for each column

  • Recall that primary key fields should use a number data type to avoid typographical, punctuation, and case variation errors


Database design

Database Design

  • Systems Development Life Cycle (SDLC)

  • Entity-relationship model (E-R model)

  • Normalization


Systems development life cycle

Systems Development Life Cycle

Systems Investigation

(Definition) Product:

Feasibility Study

Understand the

Business

Problem or

Opportunity

Systems Analysis

Product:

Functional Requirements

Develop an

Information

System

Solution

Systems Design

Product:

System Specifications

Systems Implementation

Product:

Operational System

Implement

the Information

System

Solution

Systems Maintenance

Product:

Improved System


Systems development life cycle sdlc

Systems Development Life Cycle (SDLC)

  • Systems investigation – understanding the problem

  • Systems analysis – understanding the solution

  • Systems design – creating the logical and physical components

  • Systems implementation – placing completed system into operation

    • including integration, testing and deployment

  • Systems maintenance and review – evaluating the implemented system


Data models

CUSTOMER

ORDER

Data Models

  • A data modelis a collection of concepts for describing data.

  • Three database models

    • Hierarchical

    • Network

    • Relational


Entity relationship model e r model

Entity-Relationship Model (E-R Model)

  • Used to depict the relationship that exists among entities

  • The following relationships can be included in an E-R model:

    • One-to-one

    • One-to-many

    • Many-to-many


E r model notation examples

E-R Model Notation Examples

Figure 1-3 E-R Model notation examples

Please note that the name of entity should be singular even it contains multiple instances


Chapter 1 overview of database concepts

Figure 1-5 JustLee Books’ table structures after normalization


One to one relationship

One-to-One Relationship

  • Each occurrence of data in one entity is represented by only one occurrence of data in the other entity

  • Example: Each order has just one invoice and each invoice is assigned to just one order

ORDER

INVOICE


One to many relationship

One-to-Many Relationship

  • Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity

  • Example: A class has only one instructor, but each instructor can teach many classes

Class

Instructor


Many to many relationship

Many-to-Many Relationship

  • Data can have multiple occurrences in both entities

  • Example: A student can take many classes, and each class is composed of many students

  • Can not be included in the physical database

Class

Student


Justlee example e r model

JustLee Example E-R Model


Installing personal oracle11g

Installing Personal Oracle11g

  • I created a “Discussion Questions” on the Bb for you to share (or post question) your experience on installation of Oracle 11g. Please participate and it will be also your part of class performance.

  • Please note that if your computer with 64-bit operating system such as Windows 7, you can’t use the DVD to install Oracle since it is for 32-bit OS. Rather you need to download 64-bit Oracle 11g from the Oracle site (www.oracle.com) or type in keyword search of “Oracle 11g download”.


Chapter 1 overview of database concepts

Q: Can we create/enter the data base on the following table immediately?

BOOKS table


Database normalization

Database Normalization

  • Purposes

    • design a reliable and stable data bases

    • increase data integrity (reduce or control data redundancy)

  • Processes

    • determines required tables and columns for each table

    • multistep process


Chapter 1 overview of database concepts

???

Problem Solving for Modeling a Database Project

Study and Analyze

w/Team

Business Problem

IMPLEMENTATION


Chapter 1 overview of database concepts

User interview &

Integrated Model

ER or other Model

Normalization

Normalization

(3NF)

IMPLEMENTATION

Problem Solving for Modeling a Database Project

Study and Analyze

w/Team

Business Problem


Well structured relations

Well-Structured Relations

  • A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

  • Goal is to avoid (minimize) anomalies

    • Insertion Anomaly– adding new rows forces user to create duplicate data

    • Deletion Anomaly– deleting rows may cause a loss of data that would be needed for other future rows

    • Modification Anomaly– changing data in a row forces changes to other rows because of duplication

General rule of thumb: a table should not pertain to more than one entity type


Functional dependencies and keys

Functional Dependencies and Keys

  • Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute.

  • Candidate Key

    • A unique identifier. One of the candidate keys will become the primary key

      • E.g. perhaps there is both credit card number and SS# in a table…in this case both are candidate keys

    • Each non-key field is functionally dependent on every candidate key

Figure 5-9


Database normalization continued

Database Normalization (continued)

  • Data redundancy – refers to having the same data in different places within a database

  • Data anomalies – refers to data inconsistencies

    • Insertion anomaly

    • Deletion anomaly

    • Modification anomaly


Unnormalized data

Unnormalized Data

  • Contains repeating groups in the Author column in the BOOKS table


Chapter 1 overview of database concepts

Remove …

Remove …

Figure: 5-22 Steps in normalization

Table with Repeating Group attributes

Remove Repeating Group Attributes

First normal

form (1NF)

Second normal

form(2NF)

Third normal

form (3NF)

Remove remaining anomalies resulting from multiple candidate keys

Boyce-Codd normal

form (BC-NF)

Remove Multivalued Dependencies

Fourth normal

Form (4NF)

Remove Remaining Anomalies

Fifth normal

form (5NF)


First normal form 1nf

First-Normal Form (1NF)

  • Primary key (pk) is identified

  • Repeating groups are eliminated

  • Every attribute value is atomic (singled-value)


First normal form 1nf continued

First-Normal Form (1NF) (continued)

  • ISBN and Author columns together create a composite primary key


Chapter 1 overview of database concepts

1NF: ISBN and Author fields together create a composite primary key

Q: What fields are dependent on ISBN alone ?


Composite primary key

Composite Primary Key

  • More than one field (column) is required to uniquely identify a record (row).

  • Can lead to partial dependency – a field is only dependent on a portion of the primary key


Second normal form 2nf

Second-Normal Form (2NF)

  • 1NF and every non-key attribute is fully functionally dependent on the primary key.

  • Partial dependency must be eliminated

    • Break the composite primary key into two parts, each part representing a separate table

    • Every non-key attribute must be defined by the entire key (either a single PK or a CK), not by only part of the key


Second normal form 2nf continued

Second-Normal Form (2NF) (continued)

  • BOOKS table in 2NF


Chapter 1 overview of database concepts

1NF: ISBN and Author fields together create a composite primary key

Q: What fields are dependent on ISBN alone ?


Chapter 1 overview of database concepts

Fig: A Process from 1NF to 2NF

Dependency on entire primary key (ISBN & Author)

ISBN

Title

Publication Date

Cost

Retail

Category

Publisher

Contact

Author

Dependency on partial primary key (ISBN)


Chapter 1 overview of database concepts

Fig: A Process from1NF to 2NF

Dependency on entire primary key (ISBN & Author)

ISBN

Title

Publication Date

Cost

Retail

Category

Publisher

Contact

Author

Dependency on partial primary key (ISBN)

ISBN & Author  Title, Publication Date, Cost …

ISBN  Title, Publication Date, Cost …

Therefore, NOT in 2nd Normal Form!!


Chapter 1 overview of database concepts

Fig: A Process from 1NF to 2NF

Dependency on entire primary key (ISBN & Author)

ISBN

ISBN

Title

Title

Publication Date

Publication Date

Cost

Cost

Retail

Retail

Category

Category

Publisher

Publisher

Contact

Contact

Author

Dependency on partial primary key (ISBN)

ISBN

Author


Chapter 1 overview of database concepts

Fig: A Process from 1NF to 2NF


Chapter 1 overview of database concepts

Figure 1-5 JustLee Books’ table structures after normalization


Chapter 1 overview of database concepts

Remove Partial Dependencies

Remove Transitive Dependencies

Figure: 5-22 Steps in normalization

Table with Repeating Group attributes

Remove Repeating Group Attributes

First normal

form (1NF)

Second normal

form(2NF)

Third normal

form (3NF)

Remove remaining anomalies resulting from multiple candidate keys

Boyce-Codd normal

form (BC-NF)

Remove Multivalued Dependencies

Fourth normal

Form (4NF)

Remove Remaining Anomalies

Fifth normal

form (5NF)


Third normal form 3nf

Third Normal Form (3NF)

  • Transitive dependency must be eliminated

    • 2NF and transitive dependencies (functional dependency between non-key attributes) must be eliminated


Chapter 1 overview of database concepts

Fig: A Process from 1NF to 2NF

Dependency on entire primary key (ISBN & Author)

ISBN

ISBN

Title

Title

Publication Date

Publication Date

Cost

Cost

Retail

Retail

Category

Category

Publisher

Publisher

Contact

Contact

Author

Dependency on partial primary key (ISBN)

Is it in the 3NF?

ISBN

Author


Chapter 1 overview of database concepts

BUT

ISBN Category  Contact

implies

Fig: A Process from 2NF to 3NF

All non-key fields are dependent on pk (ISBN)

ISBN

Title

Publication Date

Cost

Retail

Category

Publisher

Contact

a non-key field is dep. on another non-key field

ISBN  Title

ISBN  Publication Date

ISBN  Category

ISBN  Publisher

ISBN  Contact

and

ISBN  Contact

Transitive dependency

(not in 3rd NF)

Category  Contact

All this is OK

(2nd NF)


Chapter 1 overview of database concepts

Fig: A Process from 2NF to 3NF

ISBN

ISBN

Title

Title

Publication Date

Publication Date

Cost

Cost

Retail

Retail

Category

Category

Publisher

Publisher

Contact

Category

Contact


Third normal form 3nf1

Third-Normal Form (3NF)

  • Publisher contact name has been removed

TABLE 1-5 The BOOKS Table in 3NF

We might further create a look up table for “Category” (see Table 1-6, p.12)


Chapter 1 overview of database concepts

Fig: A Process from 2NF to 3NF


Chapter 1 overview of database concepts

Processes from 1NF  2NF  3NF

Dependency on entire primary key (ISBN & Author)

ISBN

ISBN

ISBN

Title

Title

Title

Publication Date

Publication Date

Publication Date

Cost

Cost

Cost

Retail

Retail

Retail

Category

Category

Category

Publisher

Publisher

Publisher

Contact

Contact

Author

Dependency on partial primary key (ISBN)

(remove partial dependency)

ISBN

Author

(remove transitive dependency)

Category

Contact

Q: Finally, how many tables on the 3NF?


Chapter 1 overview of database concepts

Q: How many tables on the 3NF?

ISBN

ISBN

Title

Title

Publication Date

Publication Date

Cost

Cost

Retail

Retail

Category

Category

Publisher

Publisher

Contact

Author

Answer: Three tables. What are they?

BOOK_AUTHOR

BOOKS

CATEGORY_CONTACT

ISBN

Author

Note that You can’t do any implementation until you transform a table into 3NF (with more tables produced)

Category

Contact


Chapter 1 overview of database concepts

Processes from 1NF  2NF  3NF

fk(foreign key)

We will learn all detailed processes in the main text (ch.4)


Chapter 1 overview of database concepts

Remove Partial Dependencies

Remove Transitive Dependencies

Figure: 5-22 Steps in normalization

Table with Repeating Group attributes

Remove Repeating Group Attributes

First normal

form (1NF)

Second normal

form(2NF)

Third normal

form (3NF)

Remove remaining anomalies resulting from multiple candidate keys

Boyce-Codd normal

form (BC-NF)

Remove Multivalued Dependencies

Fourth normal

Form (4NF)

Remove Remaining Anomalies

Fifth normal

form (5NF)


Summary of normalization steps

Summary of Normalization Steps

  • 1NF: eliminate repeating groups, identify the primary key

  • 2NF: table is in 1NF, and partial dependencies are eliminated

  • 3NF: table is in 2NF, and transitive dependencies are eliminated


Chapter 1 overview of database concepts

Remove Partial Dependencies

Remove Transitive Dependencies

Figure: 5-22 Steps in normalization

Table with Repeating Group attributes

Remove Repeating Group Attributes

First normal

form (1NF)

Second normal

form(2NF)

Third normal

form (3NF)

Remove remaining anomalies resulting from multiple candidate keys

Boyce-Codd normal

form (BC-NF)

Remove Multivalued Dependencies

Fourth normal

Form (4NF)

Remove Remaining Anomalies

Fifth normal

form (5NF)


Relating tables within the database

Relating Tables within the Database

  • Once tables are normalized, make certain tables are linked

  • Tables are linked through a common field

  • A common field is usually a primary key in one table and a foreign key in the other table


Chapter 1 overview of database concepts

Category_Contact

Category

Contact


Lookup table

Lookup Table

fk(foreign key)

Common reference for descriptive data tables referenced in a foreign key


Structured query language sql

Structured Query Language (SQL)

  • Data sublanguage

  • Used to:

    • Create or modify tables

    • Add data to tables

    • Edit data in tables

    • Retrieve data from tables

  • ANSI and ISO standards


Databases used in this textbook justlee books database

Databases Used in this Textbook – JustLee Books Database

  • Assumptions

    • No back orders or partial shipments

    • Only U.S. addresses

    • Completed orders are transferred to the annual SALES table at the end of each month to enable faster processing on the ORDERS table


Topic sequence

Topic Sequence

  • The first half of the text will focus on creating a database

  • The second half of the text will focus on querying or retrieving data from a database


Summary

Summary

  • A DBMS is used to create and maintain a database

  • A database is composed of a group of interrelated tables

  • A file is a group of related records; a file is also called a table in the physical database

  • A record is a group of related fields regarding one specific entity; a record is also called a row

  • A common field is used to join data contained in different tables

  • A primary key is used to uniquely identify each record

  • A foreign key is a common field that exists between two tables but is also a primary key in one of the tables

  • A lookup table is a common term for a table referenced in a foreign key

  • A Structured Query Language (SQL) is a data sublanguage that navigates the data stored within a database’s tables


Summary continued

Summary (continued)

  • A record is considered unnormalized if it contains repeating groups

  • A record is in first-normal form (1NF) if no repeating groups exist and it has a primary key

  • Second-normal form (2NF) is achieved if the record is in 1NF and has no partial dependencies

  • After a record is in 2NF and all transitive dependencies have been removed, then it is in third-normal form (3NF), which is generally sufficient for most databases


Chapter 1 overview of database concepts

Homework: Transform it into 3NF

SALES relation with simple data

SALES


  • Login