LECTURE 1
COB 3133 – DATABASE
SYSTEMS
Facilitator: Mr. Yamakili. (MSc. EEIT –UDSM)
At the end of this course, it is expected you
will be able to:
•Design Database systems
•Program Database using Database scripts
•Apply relational Database concepts
•Design and implement database system
technology and related fields.
Course assessment
• Semester examination = = 60 %
• Continuous assessment (CA)
Test 1 = = 15
Test 2 = = 20
(practical)
Quiz == 5
100%
DATABASE CONCEPTS
WHY STUDY DATABASES?
• Databases have incredible value to businesses.
• Very important technology for supporting operations.
• Vastly superior to file processing systems
• Businesses cannot survive without quality data
about their
• internal operations and
• external environment.
FOUNDATION DATA CONCEPTS
Abstract Concepts
• Entity – person, place, object or event
– stored as a record or a table row
• Attribute – characteristic of an entity
– stored as field or table column
FOUNDATION DATA CONCEPTS
Large
Database Concepts
• Database – a collection of related tables
• Tables – a collection of related records
– collection of related entities
• Record – collection of fields (table row)
–represents an entity
• Field – collection of characters (table column)
– represents an attribute
• Character – single alphabetic, numeric or other
symbol
Small
FIELDS
• A name with characters like Yamasoft form a
field
Last Name
Yamasoft
• A field is an attribute of an entity
RECORDS
• A bunch of fields form a record
First Name Last Name Sex Weight
Eric Breimer Yes 263
• A record is an entity
TABLES
• A bunch of records forms a table
First Name Last Name Sex Age
Eric Breimer M 30
Jeff Albert M 22
Jackie Pizzo F 21
• A table is a group of related entities
DATABASES
• A bunch of tables form a database
Customer Table Order Table
Product Table
• A database can represent a single business or an
entire market
DATABASES
• But, databases are not just a bunch of tables
Orders
OID CID PID Quantity
001 508 199 500,000
002 508 201 2
003 510 201 1
Customers Products
CID FName LName Address PID Description Cost
508 Eric Breimer ... 199 Viagra $45.99
509 Andrew Zych ... 200 Tooth Paste $2.58
• A database also includes relationships between
510 Greg Smith ... 201 Hair Gel $5.99
the different tables
RELATIONSHIPS &
QUERIES IN DATABASES
TYPES OF RELATIONSHIPS
ThingA ThingB
Relationship
One to One
Man Woman
• Examples?
Married
• Analysis Technique
• Consider ThingA and ThingB
• Can ThingA be related to more than one ThingB?
• Can ThingB be related to more than one ThingA?
• If the two answers are NO, then it is a one to
one relationship.
TYPES OF RELATIONSHIPS Student
Faculty Student
Student
One to Many
• Examples? Advises
• Analysis Technique Get Advisement
• Consider ThingA and ThingB
• Can ThingA be related to more than one ThingB?
• Can ThingB be related to more than one ThingA?
• If only one answer is yes, then you have a one to
many relationship
student takes course
TYPES OF RELATIONSHIPS Student
Course Student
Many to Many Course Student
• Examples? Student
• Analysis Technique course has a student
• Consider ThingA and ThingB
• Can ThingA be related to more than one ThingB?
• Can ThingB be related to more than one ThingA?
• If the answers are yes and yes, then the
relationship is many to many.
HOW TO MODEL
RELATIONSHIPS
Orders
OID CID PID Quantity
001 508 199 500,000
002 508 201 2
003 510 201 1
Customers Products
CID FName LName Address PID Description Cost
508 Eric Breimer ... 199 Viagra $45.99
509 Andrew Zych ... 200 Tooth Paste $2.58
510 Greg Smith ... 201 Hair Gel $5.99
Find the name and hire date of the manager working on the
sales manual project
TRADITIONAL FILE
PROCESSING SUCKS
File Processing:
• Data is organized, stored, and processed in
independent files of data records
PROBLEMS OF
FILE PROCESSING
• Data Redundancy –
• duplicate data requires
update to many files
• Lack of Integration –
• data stored in
separate files
hard to combine data
• Data Dependence –
• changing the file format requires changing the
program…
DATABASE MANAGEMENT
APPROACH
• Consolidates data records into one
CENTRAL database that can be
accessed by many different
application programs.
DATABASE MANAGEMENT
SOFTWARE (DBMS)
Definition:
• Software that controls the creation,
maintenance, and use of databases
DBMS SOFTWARE
COMPONENTS
• Database Definition
• Language and graphical tools to define entities,
relationships, integrity constraints, and
authorization rights
• Application Development
• Graphical tools to develop menus, data entry
forms, and reports
DBMS SOFTWARE
COMPONENTS
• Transaction Processing
• Controls to prevent interference from
simultaneous users and
• Controls to recover lost data after a failure
• Database Tuning
• Tools to monitor and improve database
performance
DATABASE INTERROGATION
Definition:
• Capability of a DBMS to report information from
the database in response to end users’ requests
• Query Language – allows easy, immediate
access to ad hoc data requests
• Report Generator - allows quick, easy
specification of a report format for information
users have requested
NATURAL LANGUAGE VS. SQL
QUERIES
SCHEMAS
Schema - A description
Subschema – describes
of the database
a subset of the database
and which users have
access to this subset
DATA DEFINITION LANGUAGE
• Language Used to
describe Schemas
and Subschemas
• Describes
relationships between
different data
• Provides a Logical
view of the data
DATA DICTIONARY ENTRY
• A more detailed description of the data in a
database
• Specifies data types
and ranges
• Assists programmers in
understanding the data
PHYSICAL VS. LOGICAL
• DBMS concentrate
on Physical access
to the underlying
tables
• Concurrency control
• Query’s
• Creating/deleting
tables
PHYSICAL VS. LOGICAL
• MIS systems are
(Logically) interface
with a DBMS
• monthly reports
• charts
• automated inquiries
APPLICATION DEVELOPMENT
• Today, even non-technical staff can use tools
to build little programs that use a database.
• Database Management Systems have all kinds of
tools to develop custom application programs and
interfaces.
• Example: The College’s MIS (Banner) is
actually an application built on top of a
Oracle Database.
DATABASE FOUNDATION
• Major Database Concepts
• Data and information
• Data - Raw facts
• Information - Processed data
• Data management
• Database
• Metadata
• Database management system (DBMS)
36
DATABASE FOUNDATION
• Importance of DBMS
• It helps make data management more efficient and
effective.
• Its query language allows quick answers to ad hoc queries.
• It provides end users better access to more and better-
managed data.
• It promotes an integrated view of organization’s operations
-- “big picture.”
• It reduces the probability of inconsistent data.
37
The DBMS Manages the Interaction
Between the End User and the Database
Figure 1.2 38
DATABASE FOUNDATION
• Why Database Design Is Important?
• A well-designed database facilitates data
management and becomes a valuable
information generator.
• A poorly designed database is a breeding
ground for uncontrolled data
redundancies.
• A poorly designed database generates
errors that lead to bad decisions.
39
THE FILE SYSTEM CONCEPTS
• Why Study File Systems?
• It provides historical perspective.
• It teaches lessons to avoid pitfalls of data
management.
• Its simple characteristics facilitate understanding
of the design complexity of a database.
• It provides useful knowledge for converting a file
system to a database system.
40
Contents of the CUSTOMER File
Figure 1.3 41
Table 1.1 Basic File Terminology
Data “Raw” facts that have little meaning unless they have been
organized in some logical manner. The smallest piece of data
that can be “recognized” by the computer is a single
character, such as the letter A, the number 5, or some
symbol such as; ‘ ? > * +. A single character requires one
byte of computer storage.
Field A character or group of characters (alphabetic or numeric)
that has a specific meaning. A field might define a telephone
numbers, a birth date, a customer name, a year-to-date
(YTD) sales value, and so on.
Record A logically connected set of one or more fields that describes
a person, place, or thing. For example, the fields that
comprise a record for a customer named J. D. Rudd might
consist of J. D. Rudd’s name, address, phone number, date
of birth, credit limit, unpaid balance, and so on.
File A collection of related records. For example, a file might
contain data about ROBCOR Company’s vendors; or, a file
might contain the records for the students currently42 enrolled
at Gigantic University.
Contents of the AGENT File
Figure 1.4
43
A Simple File System
44
Figure 1.5
FILE SYSTEM CRITIQUE
• File System Data Management
• File systems require extensive programming
in a third-generation language (3GL).
• As the number of files expands, system
administration becomes difficult.
• Making changes in existing file structures is
important and difficult.
• Security features to safeguard data are
difficult to program and usually omitted.
45
• Difficulty to pool data creates islands of
information.
FILE SYSTEM CRITIQUE
• Structural and Data Dependence
• Structural Dependence
A change in any file’s structure requires the modification of all
programs using that file.
• Data Dependence
A change in any file’s data characteristics requires changes in all
data access programs.
• Significance of data dependence is the difference between
the data logical format and the data physical format.
• Data dependence makes file systems extremely cumbersome
from a programming and data management point of view.
46
FILE SYSTEM CRITIQUE
• Field Definitions and Naming Conventions
• A good (flexible) record definition anticipates
reporting requirements by breaking up fields
into their components.
• Example:
• Customer Name Last Name, First Name, Initial
• Customer Address Street Address, City, State
FIELD CONTENTS
CUS_LNAME Customer last name
CUS_FNAME Customer first name
CUS_INITIAL Customer initial
CUS_AREACODE Customer area code
CUS_PHONE Customer phone
CUS_ADDRESS Customer street address or box number
47
CUS_CITY Customer city
CUS_STATE Customer state
FILE SYSTEM CRITIQUE
• Field Definitions and Naming Conventions
• Selecting proper field names is very
important.
• Names must be as descriptive as possible within
restrictions.
• Naming must reflect designer’s documentation
needs and user’s reporting and processing
requirements.
48
FILE SYSTEM CRITIQUE
• Data Redundancy:
Uncontrolled data redundancy sets the stage for
• Data Inconsistency (lack of data integrity)
• Data anomalies
• Modification anomalies
• Insertion anomalies
• Deletion anomalies
49
Figure 1.6
50
The Database System Environment
Figure 1.7 51
Figure 1.7
DATABASE SYSTEMS
• The Database System Components
• Hardware
• Computer
• Peripherals
• Software
• Operating systems software
• DBMS software
• Applications programs and utilities software
52
DATABASE SYSTEMS
• The Database System Components
• People
• Systems administrators
• Database administrators (DBAs)
• Database designers
• Systems analysts and programmers
• End users
• Procedures
• Instructions and rules that govern the design and use of the database system
• Data
• Collection of facts stored in the database
53
DATABASE SYSTEMS
• The Database System Components
• The complexity of database systems depends on
various organizational factors:
• Organization’s size
• Organization’s function
• Organization’s corporate culture
• Organizational activities and environment
• Database solutions must be cost effective AND
strategically effective.
54
DATABASE SYSTEMS
• Types of Database Systems
• Number of Users
• Single-user
• Desktop database
• Multiuser
• Workgroup database
• Enterprise database
• Scope
• Desktop
• Workgroup
• Enterprise
55
DATABASE SYSTEMS
• Types of Database Systems
• Location
• Centralized
• Distributed
• Use
• Transactional (Production)
• Decision support
• Data warehouse
56
DATABASE SYSTEMS
• DBMS Functions
1. Data Dictionary Management
2. Data Storage Management
3. Data Transformation and Presentation
4. Security Management
5. Multi-User Access Control
6. Backup and Recovery Management
7. Data Integrity Management
8. Database Access Languages (DDL and DML) and Application
Programming Interfaces
9. Database Communication Interfaces
57
DATABASE MODELS
• A database model is a collection of logical constructs used
to represent the data structure and the data relationships
found within the database.
• Two Categories of Database Models
• Conceptual models focus on the logical nature of the data
representation. They are concerned with what is represented
rather than how it is represented.
• Implementation models place the emphasis on how the data
are represented in the database or on how the data structures are
implemented.
58
DATABASE MODELS
• Three Types of Relationships
• One-to-many relationships (1:M)
• A painter paints many different paintings, but each one of them is painted by
only that painter.
• PAINTER (1) paints PAINTING (M)
• Many-to-many relationships (M:N)
• An employee might learn many job skills, and each job skill might be learned
by many employees.
• EMPLOYEE (M) learns SKILL (N)
• One-to-one relationships (1:1)
• Each store is managed by a single employee and each store manager
(employee) only manages a single store.
• EMPLOYEE (1) manages STORE (1)
59
DATABASE MODELS
• Three Types of Implementation Database
Models
• Hierarchical database model
• Network database model
• Relational database model
60
A Hierarchical Structure
Figure 1.8 61
DATABASE MODELS
• Hierarchical Database Model
• Basic Structure
• Collection of records logically organized to conform to the upside-
down tree (hierarchical) structure.
• The top layer is perceived as the parent of the segment directly
beneath it.
• The segments below other segments are the children of the
segment above them.
• A tree structure is represented as a hierarchical path on the
computer’s storage media.
62
DATABASE MODELS
• Hierarchical Database Model
• Advantages
• Conceptual simplicity
• Database security
• Data independence
• Database integrity
• Efficiency dealing with a large database
• Disadvantages
• Complex implementation
• Difficult to manage
• Lacks structural independence
• Applications programming and use complexity
• Implementation limitations
63
• Lack of standards
Child with Multiple Parents
64
Figure 1.9
DATABASE MODELS
• Network Database Model
• Basic Structure
• Set -- A relationship is called a set. Each set is
composed of at least two record types: an owner
(parent) record and a member (child) record.
• A set is represents a 1:M relationship between the
owner and the member.
65
A Network Database Model
66
Figure 1.10
DATABASE MODELS
• Network Database Model
• Advantages
• Conceptual simplicity
• Handles more relationship types
• Data access flexibility
• Promotes database integrity
• Data independence
• Conformance to standards
• Disadvantages
• System complexity
• Lack of structural independence
67
DATABASE MODELS
• Relational Database Model
• Basic Structure
• RDBMS allows operations in a human logical
environment.
• The relational database is perceived as a collection of
tables.
• Each table consists of a series of row/column
intersections.
• Tables (or relations) are related to each other by sharing
a common entity characteristic.
• The relationship type is often shown in a relational
schema.
• A table yields complete data and structural 68
independence.
Linking Relational Tables
69
Figure 1.11
DATABASE MODELS
• Relational Database Model
• Advantages
• Structural independence
• Improved conceptual simplicity
• Easier database design, implementation, management, and use
• Ad hoc query capability (SQL)
• Powerful database management system
• Disadvantages
• Substantial hardware and system software overhead
• Possibility of poor design and implementation
• Potential “islands of information” problems
70
A Relational Schema
71
Figure 1.12
DATABASE MODELS
• Entity-Relationship Data Model
• It is one of the most widely accepted graphical
data modeling tools.
• It graphically represents data as entities and
their relationships in a database structure.
• It complements the relational data model
concepts.
72
DATABASE MODELS
• Entity Relationship Data Model
• Basic Structure
• E-R models are normally represented in an
entity relationship diagram (ERD).
• An entity is represented by a rectangle.
• Each entity is described by a set of attributes. An
attribute describes a particular characteristics
of the entity.
• A relationship is represented by a diamond
connected to the related entities.
73
Figure 1.13 Relationship Depiction: The ERD
74
Figure 1.14 Relationship Depiction: The Crow’s Foot
75
DATABASE MODELS
• Entity-Relationship Data Model
• Advantages
• Exceptional conceptual simplicity
• Visual representation
• Effective communication tool
• Integrated with the relational database model
• Disadvantages
• Limited constraint representation
• Limited relationship representation
• No data manipulation language
• Loss of information content
76
DATABASE MODELS
• Object-Oriented Database Model
• Characteristics
• An object is described by its factual content.
• An object includes information about relationships
between the facts within the object, as well as with
other objects.
• An object is a self-contained building block for
autonomous structures.
77
DATABASE MODELS
• Object-Oriented Database Model
• Basic Structure
• Objects are abstractions of real-world entities or events.
• Attributes describe the properties of an object.
• Objects that share similar characteristics are grouped in classes.
• A class is a collection of similar objects with shared structure
(attributes) and behavior (methods).
• Classes are organized in a class hierarchy.
• An object can inherit the attributes and methods of the classes above it.
78
A Comparison: The OO Data Model and the ER Model
79
Figure 1.15
DATABASE MODELS
• Object-Oriented Database Model
• Advantages
• Add semantic content
• Visual presentation includes semantic content
• Database integrity
• Both structural and data independence
• Disadvantages
• Lack of OODM standards
• Complex navigational data access
• Steep learning curve
• High system overhead slows transactions
80
The Development of Data Models
81
Figure 1.16
WRAP-UP: THE EVOLUTION OF
DATA MODELS
• Common characteristics required for data
models:
• A data model must show some degree of
conceptual simplicity without compromising the
semantic completeness.
• A data model must represent the real world as
closely as possible.
• The representation of the real-world
82
transformations (behavior) must be in
compliance with the consistency and integrity
WRAP-UP: THE EVOLUTION OF
DATA MODELS
• Database Models and the Internet
The use of the Internet as a prime business tool is
shifting focus to database products that interface
efficiently and easily with the Internet.
• Successful “Internet age” databases are
characterized by:
• Flexible, efficient, and secure Internet access.
• Support for complex data types and
relationships.
• Seamless interfacing with multiple data sources
and structures.
• Simplicity of the conceptual database model.
83
• An abundance of available database tools.
• A powerful DBMS to help make the DBA’s job
easier.