lecture 01 introduction week01

8/6/2019 Lecture 01 Introduction Week01

1/68

Introduction To Databases

(week 1)

Remedios de Dios Bulos


2/68

How were data records maintained

in the pre-computer period?


3/68

Manual Filing System prepared by:RdDB

Collection of

related data

Projects

Products

Equipment

Clients

Employees

Sales


4/68


5/68


Clients

Employees

Sales

What does an employee

record contain?

Employee id

Name

Address

Position

Structure

organization


6/68


Projects

Products

Equipment

Clients

Employees

Sales

Add a record

Update a record

Delete a record

Query a record

Generate reports

using a file

Generate reports

using 2 or more

files

What do you do with

records?


7/68


8/68

Goals of the Course

1. Logical design of a database

2. Implement the logical design

a) Database Schema creationb) Manipulation of database

i. Add

ii. Delete

iii. Update

iv. Query


9/68

Application

programs Students

Courses

Faculty

How can a user access the database?

DBMS

SQL


10/68

Exercise

Given: IDNo, SName, Degree, GPA

Required:

List of students sorted alphabetically and their

correspondingGPAs

List of students and their GPAs sorted in

descending order (from highest to lowest)

Implementation: use any programmimg

language you know


11/68

P

resentation ofEx

ercise Solution


12/68

Limitations of File-Based Systems


13/68

File-based systemprepared by:RdDB

a collection of applicationprograms that perform

services for the end users

such as the production of

reports.

EAFenrollment

FileRegistrar

StudentCourses

Faculty

Data entry of

coursesData entry of

enrollmentGeneration of

EAF


14/68

File-based systemprepared by:RdDB

Each program in the

system defines and

manages its own data.

[CBS98]

EAFenrollment

FileRegistrar

Student

Courses

Faculty

Data entry of

coursesData entry of

enrollment

struct course

{ char code[5];

char desc[20];

int units [3];

}


15/68

File-based

systemsprepared by:RdDB

EAF

enrollment

FileRegistrar

Student

Courses

Faculty

OR

payment of fees

FileAccountingStudent

Fees

course

cards

Processing of

grades

FileDepartment StudentCourses

Grades

Faculty

Each user (with the assistance of DP

staff) defines and implements (including

storage and control) the files needed for

a specific application. [CBS98,EN

94]


16/68

Student System (File-based)prepared by:RdDB

EAFenrollment

FileRegistrar

Student

Courses

Faculty

ORpayment of fees


Fees

course

cards

Processing of

grades


Grades

Faculty

Data redundancyWhat can be observed?


17/68


EAFenrollment

FileRegistrar

Student

Courses

Faculty

ORpayment of fees


Fees

course

cards

Processing of

grades


Grades

Faculty

Separation and

isolation of data

What can be observed?


18/68


EAFenrollment

FileRegistrar

Student

Courses

Faculty

ORpayment of fees


Fees

course

cards

Processing of

grades


Grades

Faculty

Program-data dependenceWhat can be observed?

struct person

{ char first[20];

char middle[3];

char last[30];

} employees,

managers;


19/68


EAFenrollment

FileRegistrar

Student

Courses

Faculty

ORpayment of fees


Fees

course

cards

Processing of

grades


Grades

Faculty

Incompatibility of filesWhat can be observed?

COBOL

C


20/68


EAFenrollment

FileRegistrar

Student

Courses

Faculty

ORpayment of fees


Fees

course

cards

Processing of

grades


Grades

Faculty

Fixed queries;

proliferation of application

programs

What can be observed?


21/68

Limitations of File-Based Systems

Separation and isolation of data

Duplication of data

Program-data dependence

Incompatibility of files (e.g C vs. COBOL)

Fixed queries / proliferation of application

programs

prepared by:RdDB


22/68

Factors that limit File-Based System

The definition of data is embedded in the

application programs, rather than being

stored separately and independently.[CBS98]

There is no control over the access and

manipulation of data beyond that imposed

by the application programs. [CBS98]

prepared by:RdDB


23/68

DatabaseA

pproach


24/68

Projects

Products

Equipment

Clients

Employees

Sales

Projects

Products

Equipment

Clients

Employees

Sales


25/68

What is a database?

It is a shared collection of logically coherent

data with some inherent meaning;

It is designed , built and populated with data

for specific purpose such as meeting the

information needs of an organization;

It represents some aspect of the real-world.

[EN94]

Ex: student database, employee database,

library database, air flights database,

hospital database, etc.

prepared by:RdDB

Departments

Employees

Projects


26/68

Departments

Employees

Projects

Can a userdirectly access

a database?


27/68

A

pplicationprogram

Departments

Employees

Projects

Can a user access

a database through

an application

program alone?


28/68

Application

programsDBMS

Departments

Employees

Projects

How can a user access the

database?


29/68

What is a DBMS?

Database Management System It is a software system that enables users to :

define, create and maintain the database

DDL

DML

provide controlled access to this database

Security

Integrity

Concurrency control

Recovery control

User-accessible catalogue (description of data)


30/68

Solution to Exercise:Database Approach Using Access


31/68

Projects

Products

Equipment

Clients

Employees

Sales

Projects

Products

Equipment

Clients

Employees

Sales

Clients

Projects

Products

Equipment

Clients

Employees

Sales

How can we create an

electronic/computerized database?


32/68

Steps in

developing an

DB

Information

System


33/68

1st

Step: Database / Application Planning 2nd Step: System Definition

3rd Step: Requirements Gathering and Analysis

4th Step:A

pplication and Database Design 5th Step: DBMS Selection

6th Step: Prototyping

7th Step: Implementation

8th Step: Data Conversion and Loading

9th Step: Testing

10th Step: Operational Maintenance

Steps in developing a DB Information System


34/68

Components of the DBMS Environment[CBS98]

Hardware

- PC- mainframe

- network

Software

- DBMS

- OS- network

software

- application

programs

Procedure- log on

- start/stop

- backup

- handle

failures

- change

structure

People- data admin

- database

admin- DB designer

- application

programmers

- end users

Machine Human

Bridge

Data

- stored data

- meta-data

- schema

prepared by:RdDB


35/68

A Simple Database System Environment

Application

Programs/

Queries

DATABASE

SYSTEMData

Definition

Database

DBMS Software

Software to

process

programs/queries

Software to

access storeddata

prepared by:RdDB


36/68

Can you give some examples of

database systems?


37/68

Supermarket Credit card

Travel Agent

Library

Insurance

Hospital

Bank University


38/68

Student Database System

enrollment

payment

of fees

processingof grades

DBMS

EAF

officialreceipt

coursecards

DB

prepared by:RdDB


39/68

Supermarket Database System

official

receipt

DB

milkoil

inventory &

price check

DBMSsales update

credit

check DBMS DBcard

prepared by:RdDB


40/68

END . Thank You!


41/68

Application programming with connection to a

database

http://www.configure-all.com/java_access_mysql.php

http://alperguc.blogspot.com/2008/12/c-ms-access-

connection-string-ole-db.html

http://www.easysoft.com/developer/languages/c/odbc_tutorial.html


42/68

Hardware [CBS96] Ranges of computer hardware:

a single personal computer

a single mainframe

a network of computers

Multi-User DBMS Architectures

Teleprocessing : one computer with a single CPU and a

number of terminals

File-Server: LAN where the file-server acts as a shared

hard disk drive for the database, and the applications

and DBMS run on each workstation

Client-Server: the DBMS resides in the server

Prepared by: RdDB


43/68

Teleprocessing topology

DBMS

Application Programs Database


44/68

LAN

File-Server

Database

Request for data Files returned

File-server

Workstation 2

DBMS

Workstation 1DBMS

Workstation 3

DBMS


45/68

Client-Server

LAN

Server withDBMS

Request for data Selected data returned

Client 1

Client 2 Client 3

Database


46/68

Software

DBMS with fourth-generation tools

Application programs

Operating System

Network Software


47/68

Data

Operational data

Meta-data

Schema (structure)


48/68

ProceduresThese refer to the instructions and rules that govern the use

and design of the database:

log on to the DBMS

use a particular DBMS facility or application program

start and stop the DBMS

make a backup copies of the database

handle hardware or software failures

change the structure of a table, reorganize the database,

improve performance, archive data to secondary storage

Prepared by : RdDB


49/68

Workers Behind the Scene

DBMS Designers and Implementers Data Administrator

Database Administrator

Database Designer

Logical database designer Physical database designer

Application Programmers

Tool Developers (e.g. performance monitoring,graphical interfaces, etc.)

Operators and Maintenance Personnel

End Users


50/68

Data Administrator manages the data resource including:

database planning,

development and maintenance of standards, policies

and procedures and

logical database design.

Database Administrator is responsible for the physical

realization of the database including

physical design and implementation,

security and integrity control,

maintenance of the operational system and

ensuring satisfactory performance for the applications

and users.

prepared by RdDB


51/68

Database Designers identify the data to store and

choose the proper structures.

System Analysts and Application Programmers

deal with development of applications for end

users. End Users actually access the database contents.

prepared by RdDB


52/68

End Users

Casual end users occasionally access the database, using a

query language.

Naive or parametric end users use standard queries, due

to consistent needs from the database, using cannedtransactions.

Sophisticated end users are engineers, scientists, business

analysts and those who have complex requirements.

Stand-alone users maintain personal databases, usingready-made program packages (e.g. tax package).


53/68

Strengths of Database Systems

Control of data redundancy

Promote data consistency

Sharing of data

Improved data integrity

Improved security

prepared by:RdDB


54/68


55/68

Weaknesses of Database Systems [CBS98]

Complexity

Size

Cost of DBMS

Additional hardware costs

Cost of conversion

Performance Higher impact of a failure

prepared by:RdDB


56/68

Steps in

developing a

DB

Information

System


57/68

1st Step: Database / Application Planning

Defining:

Purpose of the database project

Objectives that the target application needs toaccomplish and attain

Development ofstandards for the target

application as well as the whole process of

developing the application


58/68

2nd Step: System Definition

Defining:

Scope and

boundaries of

database application

The different users of

the system and their

perspective over the

target application

The connectionbetween these

perspectives


59/68


The most exciting part of the process

Process ofcollecting and analyzing information

about the part of organization to be supported bythe database application

Identify users requirements for the DB system

Identify expected information to be generated

and what sets of data are needed to generate it

One of the most chaotic if not done properly


60/68



61/68



62/68

4th Step: Application and Database Design

For Databases, it goes through 3-stages

Conceptual Design

Logical Design (done together with the DBMS

Selection)

Physical Design

For Applications, it considers:

Transactions that will need to be supported

User interface


63/68

5th Step: DBMS Selection

Selection of an appropriate DBMS to

support the database application


64/68

6th Step: Prototyping

Building a working model of a databaseapplication

Purpose:

to identify features of a system that work well,or are inadequate;

to suggest improvements or even newfeatures;

to clarify the users requirements;

to evaluate feasibility of a particular systemdesign


65/68

7th Step: Implementation

Physical realization of the database and

application designs

Use DDL to create database schemas and

empty database files

Use DDL to create any specified user views

Use 3GL or 4GL to create the application

programs. This will include the database

transactions implemented using the DML,

possibly embedded in a host programming

language


66/68

8th Step:

Data Conversion and Loading

Transferring any existing data into new

database and converting any existing

applications to run on new database


67/68

9th Step: Testing

Process of executing application programs

with intent of finding errors

Use carefully planned test strategies andrealistic data

Testing cannot show absence of faults; it can

show only that software faults are present

Demonstrates that database and application

programs appear to be working according to

requirements


68/68

10th Step: Operational Maintenance

Process of monitoring and maintaining

system following installation

lecture 01 introduction week01

Documents