8/7/2019 15320_1chap22 and chap 24[1]

1/56

Chapter 22

Organizing Data and Information

1

8/7/2019 15320_1chap22 and chap 24[1]

2/56

What is a database

A database is any organized collection ofdata. Some examples of databases youmay encounter in your daily life are:

a telephone book

T.V. Guide

airline reservation system

motor vehicle registration records papers in your filing cabinet

files on your computer hard drive.

8/7/2019 15320_1chap22 and chap 24[1]

3/56

Why do we need a database?

Keep records of our:

Clients

Staff

Volunteers

To keep a record of activities and

interventions;

Keep sales records;

Develop reports;

Perform research

8/7/2019 15320_1chap22 and chap 24[1]

4/56

What is the ultimate purpose of a

database management system?

Data Information Knowledge Action

Is to transformIs to transform

8/7/2019 15320_1chap22 and chap 24[1]

5/56

DATAData

A necessity for almost any enterprise tocarry out its business. Consists of raw facts,and when organized may be transformed

into informationDatabase

A collection of data organized to meet usersneeds

Database management system (DBMS) A group of programs that manipulate the

database and provide an interface betweenthe database and the user of the database orother application programs

5

8/7/2019 15320_1chap22 and chap 24[1]

6/56

Data vs. information:

What is the difference? What is data?

Data can be defined in many

ways. Information science

defines data as unprocessedinformation.

What is information? Information is data that have

been organized and

communicated in a coherent

and meaningful manner.

Data is converted into

information, and information

is converted into knowledge.

Knowledge; information

evaluated and organized sothat it can be used

purposefully.

8/7/2019 15320_1chap22 and chap 24[1]

7/56

DBMSA collection of programs that enables you to store,

modify, extract information from a database. There aremany different type of DBMSs, ranging from small

systems that run on personal computers to huge systems

that run on mainframes. The following are examples ofdatabase applications:

computerized library systems

automated teller machines

flight reservation systems

computerized parts inventory systems

7

8/7/2019 15320_1chap22 and chap 24[1]

8/56

TerminologyDatabase

A collection of integrated and related files

File A collection of related records

Record A collection of related fields

Field A group of characters

Character Basic building block of information, represented

by a byte

8

8/7/2019 15320_1chap22 and chap 24[1]

9/56

Data Entities, Attributes, andKeysEntity

A generalized class of people, places, or things(objects) for which data are collected, stored, andmaintained E.g., Customer, Employee

Attribute A characteristic of an entity; something the entity

is identified by E.g., Customer name, Employeename

Keys A field or set of fields in a record that is used to

identify the record E.g, A field or set of fieldsthat uniquely identifies the record

9

8/7/2019 15320_1chap22 and chap 24[1]

10/56

The Traditional Approach

The traditional approach

Separate files are created and stored for each

application program

10

8/7/2019 15320_1chap22 and chap 24[1]

11/56

Drawbacks

Data redundancy

Duplication of data in separate files

Lack of data integrity The degree to which the data in any one file is

accurate

Program-data dependence

A situation in which program and data organized forone application are incompatible with programs and

data organized differently for another application

11

8/7/2019 15320_1chap22 and chap 24[1]

12/56

Database Approach

The database approach

A pool of related data is shared by multiple

application programs Rather than having separate data files, each

application uses a collection of data that is

either joined or related in the database

12

8/7/2019 15320_1chap22 and chap 24[1]

13/56

Advantages

Improved strategic use of corporate data

Reduced data redundancy

Improved data integrity

Easier modification and updating Data and program independence

Better access to data and information

Standardization of data access

A framework for program development Better overall protection of the data

Shared data and information resources

13

8/7/2019 15320_1chap22 and chap 24[1]

14/56

Disadvantages

Relatively high cost of purchasing and

operating a DBMS in a mainframe operating

environment

Increased cost of specialized staff

14

8/7/2019 15320_1chap22 and chap 24[1]

15/56

Data Modeling and

Database Models (2) Data model

A map or diagram of entities and their relationships

Enterprise data modeling Data modeling done at the level of the entire

organization

Entity-relationship (ER) diagrams

A data model that uses basic graphical symbols toshow the organization of and relationships between

data

15

8/7/2019 15320_1chap22 and chap 24[1]

16/56

Hierarchical Database Model

Hierarchical database model

A data model in which data are organized in a

top-down, or inverted tree structure

16

8/7/2019 15320_1chap22 and chap 24[1]

17/56

Department C

Employee

1

Employee

2

Employee

3

Employee

4

Employee

5

Employee

6

Department B

Project 1

Department A

8/7/2019 15320_1chap22 and chap 24[1]

18/56

Network Data Model

Network data model

An expansion of the hierarchical database model

with an owner-member relationship in which a

member may have many owners

Project 1 Project 2

Department A Department B Department C

8/7/2019 15320_1chap22 and chap 24[1]

19/56

Relational Data Model

Relational data model

All data elements are placed in two-

dimensional tables, called relations, that arethe logical equivalent of files

8/7/2019 15320_1chap22 and chap 24[1]

20/56

The Relational Model

Views entities as two-dimensional tables

Records are rows

Attributes (fields) are columns Tables can be linked

Supports one-to-many, many-to-many, and

one-to-one relationships

8/7/2019 15320_1chap22 and chap 24[1]

21/56

Project

NumberDescription

Dept.

Number

155 Payroll 257

498 Widgets 632

226Sales

manager

598

Dept. Number Dept. Name Manager SSN

257 Accounting 421-55-99993

632 Manufacturing 765-00-3192

598 Marketing 098-40-1370

SSN Last Name First Name Hire Date Dept. Number

005-10-6321 Johns Francine 10-7-65 257

549-77-1001 Buckley Bill 2-17-79 650

098-40-1370 Fiske Steven 1-5-85 598

Data Table 1: Project Table Data Table 2: Department Table

Data Table 3: Manager Table

8/7/2019 15320_1chap22 and chap 24[1]

22/56

Schemas and Subschemas

Schema

A description of the entire database

Subschema A file that contains a description of a subset of

the database and identifies which users can

perform modifications on the data items in

that subset

8/7/2019 15320_1chap22 and chap 24[1]

23/56

DBMS

Schema

Subschema

B

Subschema

A

Subschema

C

User

1

User

2

User

3

User

4

User

5

8/7/2019 15320_1chap22 and chap 24[1]

24/56

Emp_Id First_Name Last_Name Department

001234 Ignacio Fleta Accounting

002000 Christian Martin Computer Support002122 Orville Gibson Human Resources

003400 Ben Smith Accounting

003780 Allison Chong Computer Support

Database Table

Row

(Record)

Column Field

Each table has a primary key Uniquely identifies that row of the table Emp_Id is the primary key in this example Serves as an index to quickly retrieve the record

Columns are also calledfields orattributes Each column has a particular data type

8/7/2019 15320_1chap22 and chap 24[1]

25/56

SQL

Structured Query Language, abbreviated

SQL

Usually pronounced sequel but also ess-cue-ell) The common language of client/server database

management systems.

Standardized you can use a common set of SQL

statements with all SQL-compliant systems.

Defined by E.F. Codd at IBM research in 1970.

Based on relational algebra and predicate logic

8/7/2019 15320_1chap22 and chap 24[1]

26/56

Queries

Queries are the information retrieval

requests you make to the database

Your queries are all about the informationyou are trying to gather

8/7/2019 15320_1chap22 and chap 24[1]

27/56

Reports

If the query is a question...

...then the report is its answer

Reports can be tailored to the needs of thedata-user, making the information they

extract much more useful

8/7/2019 15320_1chap22 and chap 24[1]

28/56

Data Dictionary

Data Dictionary

A detailed description of all data used in the

database

8/7/2019 15320_1chap22 and chap 24[1]

29/56

Data Dictionary Features

Provide a standard definition of terms anddata elements

Assist programmers in designing and writing

programs Simplify database modification

Reduce data redundancy

Increase data reliability

Faster program development

Easier modification of data and information

8/7/2019 15320_1chap22 and chap 24[1]

30/56

Data Definition Language

Data Definition Language (DDL)

A collection of instructions and commands

used to define and describe data and datarelationships in a specific database

8/7/2019 15320_1chap22 and chap 24[1]

31/56

31

Database Transactions

1. DML

2. DDL

3. DCL

Transaction starts with an execution of SQLstatements and ends with one of the following:

COMMIT or ROLLBACK

DDL or DCL (automatic commit)

user exits or quits

system crashes

8/7/2019 15320_1chap22 and chap 24[1]

32/56

A cell is also called a FIELD.

A number of such field placed in

horizontal plane is called a RECORD or arow.

To create a cell in which a user can store

and maintain data,the DBA requires aminimum of three parameters by user.

Cell name, Cell length, Cell data type.

8/7/2019 15320_1chap22 and chap 24[1]

33/56

These parameters are passed to the DBAvia its natural language,SQL.

Various Datatypes: Char(max len 255)

Varchar(max len 2000)

Number

Date(dd/mm/yy)

Long(len upto 65,535)

8/7/2019 15320_1chap22 and chap 24[1]

34/56

Choosing Column Names

Define a column for each piece of data Allow plenty of space for text fields

Avoid using spaces in column names

For the members of an organization:Column Name Type Remarks

Member_ID int Primary key

First_Name varchar(40)

Last_Name varchar(40)

Phone varchar(30)Email varchar(50)

Date_Joined smalldatetime Date only, no time values

Meetings_Attended smallint

Officer Yes/No True/False values

8/7/2019 15320_1chap22 and chap 24[1]

35/56

22.4 SQL (Structured Query

Language)SQL keyword DescriptionSELECT Selects (retrieves) columns from one or more tables.FROM Specifies tables from which to get columns or delete

rows. Required in every SELECT and DELETE

statement.WHERE

Specifies criteria that determine the rows to beretrieved.INNERJOIN Joins rows from multiple tables to produce a single

set of rows.GROUPBY Specifies criteria for grouping rows.ORDERBY Specifies criteria for ordering rows.INSERT Inserts data into a specified table.UPDATE

Updates data in a specified table.DELETE Deletes data from a specified table.CREATE Creates a new table.DROP Deletes an existing table.COUNT Returns the number of records that satisfy given

search criteria.

Fig. 22.12 SQL keywords.

8/7/2019 15320_1chap22 and chap 24[1]

36/56

22.4.1 Basic SELECT Query

SELECT * FROM tableName

SELECT * FROM Authors

SELECT authorID, lastName FROMAuthors

8/7/2019 15320_1chap22 and chap 24[1]

37/56

22.4.1 Basic SELECT Query

author

last ame author

last ame

1 eitel 7 adhu

2 eitel 8 c hie

3 Nieto 9 aeger

4 teinbuhler 10 Zlatkina

5 antry 11 Wiedermann

6 in 12 iperiFi . . 3 author

last ame fr t

uthors t bl .

8/7/2019 15320_1chap22 and chap 24[1]

38/56

The create table command:

CREATE TABLE tablename(columnname datatype(size),

columnname datatype(size));

Eg:CREATE TABLE client(client_no

varchar2(6),name varchar2(20),add

varchar2(30),pincode number(6),bal_due

number(10,2));

8/7/2019 15320_1chap22 and chap 24[1]

39/56

insertion

INSERT INTO tablename

[(columnname,columnname)]

Values(expression,expression); INSERT INTO client

(client_no,name,add,Pincode)

VALUES(c123,lovely,phagwara,144444);

NOTE:character expression in single quotes

8/7/2019 15320_1chap22 and chap 24[1]

40/56

Updation

UPDATE tablename SET

columnname=expression,

columnname=expression WHEREcolumnname=expression;

UPDATE client SET

name=university,add=jalandharWhere client_no=c123;

8/7/2019 15320_1chap22 and chap 24[1]

41/56

Deletion

DELETE FROM tablename; DELETE FROM client;

DELETE FROM client WHERE

client_no=c123;

8/7/2019 15320_1chap22 and chap 24[1]

42/56

42

Controlling Transactions

An automatic commit occurs under the

following circumstances:

DDL statement is issued DCL statement is issued

normal exit from SQL, without explicitly

issuing COMMIT or ROLLBACK

Abnormal Termination

8/7/2019 15320_1chap22 and chap 24[1]

43/56

43

COMMIT & ROLLBACK

INSERT, DELETE, & UPDATE data

from a database can be reversed

(ROLLBACK) or committed (COMMIT) Show AUTOCOMMIT

ROLLBACK

COMMIT SET AUTOCOMMIT ON

8/7/2019 15320_1chap22 and chap 24[1]

44/56

44

State of Data Before COMMIT

or ROLLBACK The previous state of the data can be recovered

The current user can review the result of the

DML operation by using SELECT Other user cannot view the result of the DML

statements by the current user

The affected rows are locked (other users can not

change the data within the affected rows)

8/7/2019 15320_1chap22 and chap 24[1]

45/56

45

State of the Data after COMMIT

Data changes are made permanent in DB

The previous state of the data is

permanently lost All users can view the results

Locks are released

All saved points are erased

8/7/2019 15320_1chap22 and chap 24[1]

46/56

46

Committing Data

UPDATE student

SET major = COSC

WHERE id = 111;

COMMIT;

8/7/2019 15320_1chap22 and chap 24[1]

47/56

47

State of the Data after ROLLBACK

Data changes are undone

Previous state of the data is restored

Locks on the rows are released

DELETE FROM student;

10,000 records are deleted

ROLLBACK;

8/7/2019 15320_1chap22 and chap 24[1]

48/56

48

Controlling Transactions

INSERT UPDATE INSERT DELETE

Rollback

Rollback

Rollback

Rollback

Point BPoint ACOMMIT

Transaction

8/7/2019 15320_1chap22 and chap 24[1]

49/56

49

Roll Back Changes to a Marker

SAVEPOINT PointA;

..

SAVEPOINT PointB;..

ROLLBACKTO PointB;

ROLLBACK;

8/7/2019 15320_1chap22 and chap 24[1]

50/56

Distributed Databases

Distributed database

A database in which the actual data may be

spread across several smaller databases

connected via telecommunications devices

8/7/2019 15320_1chap22 and chap 24[1]

51/56

8/7/2019 15320_1chap22 and chap 24[1]

52/56

Data Warehouse

Data warehouse

A relational database management system designed

specifically to support management decision making

Current evolution of Decision Support Systems(DSSs)

Data mart

A subset of a data warehouse for small and medium-

size businesses or departments within largercompanies

Schematic

8/7/2019 15320_1chap22 and chap 24[1]

53/56

Relational

databases

Hierarchicaldatabases

Network

databases

Flat files

Spreadsheets

Data

extractionprocess

Query and

analysis

tools

Data

wharehouse

Data

cleanup

process

End user access

8/7/2019 15320_1chap22 and chap 24[1]

54/56

Data Mining Applications

Data mining

The automated discovery of patterns and relationships

in a data warehouse

Data mining applications Market segmentation

Customer queries

Fraud detection

Direct marketing Market basket analysis

Trend analysis

8/7/2019 15320_1chap22 and chap 24[1]

55/56

Object-Relational Database

Management Systems (ORDBMS)

Object-relational database management system (ORDBMS) A DBMS capable of manipulating audio, video, and graphical data.

Hypertext

Users can search and manipulate alphanumeric data in an unstructuredway

Hypermedia

Allows businesses to search and manipulate multimedia forms of data

Spatial data technology

Use of an object-relational database to store and access data accordingto the location it describes and to permit spatial queries and analysis

8/7/2019 15320_1chap22 and chap 24[1]

56/56

56

THATS ALL