Transcript ETEC 100 Information Technology

ETEC 100 Information
Technology
Lecture
Introduction to database
Learning Objectives
 Define
and understand the following
terms:
data, information, database, database
management system (DBMS), database
system, metadata
 Compare and contrast conventional file
systems and database systems. Identify
advantages of database approach over
traditional file systems
What is database?

Database: An organized collection of logically related data

Data: Known facts that could be recorded and stored on computer
media


Facts could be in any format: text, graphics, images…
Information: Processed data that could be used to increase the
knowledge of the user



Closely related to data, but different
View data as “raw material” and information as “intermediate product”
The final product is “knowledge”
Data, Information and Knowledge
Database
Queries
Data
A. Berger
M. Jones
T. Martin
J. Smith
Shoes
Scarves
Jewelry
Groceries
50,000
46,800
29,200
75,500
MoU
Qty
Income
Education
Information
?
A. Berger is most
likely to buy new
product
T. Martin is profitable
customer and is likely
to switch carriers
Knowledge &
Customer Insight
Some of the images above come from a presentation given by:
®
Databases



Interact with DBs many times in a day
Database processing integral part of many
businesses
Keep track of things (what sort of things?)
History of data collection
methods
 Manual
System
 File System
 Database System
Manual Filling Systems

Before computer: Manual Filing Systems




Still used in many everyday activities
Usually organize data in a logical way
Ex;bills……..
Problems with Manual Filling Systems


Small –scale
Hard to analyze: ‘ How many students enrolled in
MIS ?
Traditional File-Based
processing
 Application
programs were written
specifically for the data file structure
that was to be used, so:
A
change in the data file structure
required change in an application
program(s) that needed that data
File Processing Systems: An example
Data Entry
File Management
Programs
Data Files
File Report
Programs
End Users
 Separated systems, separated files, separated data
 Both file management programs and file report
programs have describe WHAT data to write / read
and HOW to do that
File Processing Systems: Problems
Isolation of Data/ Limited Data Sharing
 Isolation between departments file systems
 Isolation in a department file system:
Duplication of Data
 Duplicated data waste storage space
 Duplicated data result in loss of data integrity
Lack of Data Independence
If we change the data type of address from 40 characters to 41
characters, all related programs have to change accordingly
Database Systems: Basic Terms

Database:

Database Management Systems (DBMS): A software package
to facilitate the creation and maintenance of a database.

Database system: a computerized record-keeping system,
containing database and DBMS as well as some other
components.

An organized collection of logically related data
Four components: Users, Software, Hardware, Data
Database Systems: Basic Terms
Software
interact
Application Programs
develop
End users
Application
Programmers
“What” to get
DBMS
Data
maintain
Database
Administrators
“How” to get
Database
design
Database
Designers
Users
Database Systems: Advantages

Data are stored in a central repository, managed by
DBMS




Data sharing is easy
Reduce duplication to minimum: one table for each entity
Inconsistency can be avoided to some extent by enforcement
of integrity constraints
Metadata offers data abstraction


Data independence: If format needs to be changed, simply
update the metadata. Application programs are not affected
Structural independence: similar to the above
Data Elements in a Database




User data (files)
relations in a table format (e.g. Relational Database)
Metadata (systems tables)
data about data, data dictionary
meaning of terms, description of structure etc.
Indexes (overhead data)
improves performance and accessibility
Application Metadata
structure & format of forms, reports, & queries etc.
Database Development Process
Requirement Analysis
Conceptual Design
Logical Design
Physical Design
Implementation
Maintenance
Database Development Process

Requirements Analysis


Conceptual Design



Define the database and write application programs.
Implementation


Translate the conceptual model into the logical model
Normalization.
Physical Design


Design a conceptual model, e.g., ER model.
Logical Design


Collect and Analyze the requirements of the users.
Write, test, and install programs that process the database
MaintenanceUse
maintain, and “tune” the database
Relational Database
 A relational
database is one in which the
data consists of a collection of tables
related to each other through common
values.
 The two most prominent characteristics of
a relational database are

data stored in tables

relationships between tables
Table
Relation



Two-dimensional table
Rows are tuples
Columns are attributes
Equivalent Terms:
Characteristics of a
relation







a relation has a unique name within same DB
each cell contains one single value
each attribute within a given relation has a distinct
name
values of an attribute are from the same domain
order of attributes has no significance
order of rows has no significance
no duplicate rows
Key
“a
group of one
or more
attributes
(columns) that
uniquely
identifies a
tuple (row)”
Relational Database Keys

Candidate key
 Primary key: candidate key selected to
uniquely
identify all table rows
 Foreign key: attributes(s) in one table whose
values must match primary key in
another table
Relationships
Referential Integrity Rule


If a foreign key exists in a relation,
either
the foreign key value must match a candidate key
value of some tuple in its home relation
or
the foreign key value must be wholly null
Therefore:-
if B references A
then A must exist
Schema
 A schema
is an abstract representation of
database entities and objects. A schema
helps us design a database
diagrammatically before we start creating
it.
Schema (cont.)
Query

Requests for information from a database are
made in the form of a query.
 The set of rules for constructing queries is
known as a query language.
 query language called SQL (structured query
language)
 For example, the query -requests all
recordsfrom customer table in which the NAME
field is SMITH and the AGE field is greater than
35.
SQL (structured query language)
 For
example, the query -requests all
recordsfrom customer table in which the
Name field is SMITH and the Age field is
greater than 35.
SELECT ALL
FROM Customer
WHERE Name = "SMITH" and age > 35