Database systems. B asic concepts of databases. Databases and database

systems.

in a structured format
for optimized information management
Database Management System

(DBMS)
is a software that enables easy creation, access, and modification of databases
for efficient and effective database management
Database System
is an integrated system of hardware, software, people, procedures, and data
that define and regulate the collection, storage, management, and use of data within a database environment

Coronel
- manages interaction between end users and database

Coronel
Hardware
Software - OS - DBMS

- Applications
People
Procedures
Data

Database Design
Defines the database’s expected use
different approach needed for different

types of databases
Avoid data redundancy & ensure data integrity
data is accurate and verifiable
Poorly designed database generates errors
leads to bad decisions
can lead to failure of organization
Functions of DBMS/Database System
Stores data and related data entry forms, report definitions, etc.
Hides the complexities of relational database model from the user
facilitates the construction/definition of data elements and their relationships
enables data transformation and presentation
Enforces data integrity
Implements data security management
access, privacy, backup & restoration

software, raw data, data processing procedures
Identify
Database needs
what database can do

to further the goal of the organization
User needs and characteristics
who the users are, what they want to do, how they envision doing it
Database system requirements
what the database system should do to satisfy the database and user needs
Design
From conceptual design to a detailed system specification
Implementation
Create the database
Maintenance
Troubleshoot, update, streamline the database

organization
based on policies, procedures, or principles within a specific organization
help

to create and enforce actions within that organization’s environment
apply to any organization that stores and uses data to generate information
Why
Enhance understanding & facilitate communication
Standardize company’s view of data
Constitute a communications tool between users and designers
Allow designer to understand business process as well as the nature, role, and scope of data
Promote creation of an accurate data model
How (sources)
Interviews
Company managers
Policy makers
Department managers
End users
Written documentation
Procedures, Standards, Operations manuals
Observation
Business operations

problem with the use of the system, the system is

the problem, not the user.
Compliance
The user has the right to a system that performs exactly as promised.
Instruction
The user has the right to easy-to-use instructions (user guides, online or contextual help, error messages) for understanding and utilizing a system to achieve desired goals and recover efficiently and gracefully from problem situations.
Usability
The user should be the master of software and hardware technology, not vice-versa. Products should be natural and intuitive to use.

simple (graphical) representations
Facilitate interaction among the designer, the applications programmer,

and the end user
Basic Building Blocks
Entity
thing about which data are to be collected and stored
Attribute
a characteristic of an entity
Relationship
describes an association among entities
Constraint
restrictions placed on the data

tagged file folders in a filing cabinet
organized according to expected

use
e.g. file per customer
easy to create, but hard to
locate data
aggregate/summarize data
Computerized File System
to accommodate the data growth and information need
manual file system structures were duplicated in the computer
Data Processing (DP) specialists wrote customized programs to
write, delete, update data (i.e. management)
extract and present data in various formats (i.e. report)

Coronel

Problems
Duplication
same data may be stored in multiple files
Inconsistency
same data may

be stored by different names in different format
Rigidity
requires customized programming to implement any changes
cannot do ad-hoc queries
Implications
Waste of space
Data inaccuracies
High overhead of data manipulation and maintenance

(20 char)
Carol T. Johnson
AGENT (20 char)
Carol J. Smith
- inconsistent field

name, field size - inconsistent data values - data duplication

Rob & Coronel

manufacturing projects
e.g., Information Management System (IMS)
IBM-Rockwell joint venture
clustered related data

together
hierarchically associated data clusters using pointers
Hierarchical Database Model
Assumes data relationships are hierarchical
One-to-Many (1:M) relationships
Each parent can have many children
Each child has only one parent
Logically represented by an upside down tree

Coronel

related to each other
related data could be viewed together
Centralization of

data
reduced redundancy and promoted consistency
Disadvantages
Limited representation of data relationships
did not allow Many-to-Many (M:N) relations
Complex implementation
required in-depth knowledge of physical data storage
Structural Dependence
data access requires physical storage path
Lack of Standards
limited portability

standard
Network Database Model
Similar to Hierarchical Model
Records linked by pointers
Composed of

sets
Each set consists of owner (parent) and member (child)
Many-to-Many (M:N) relationships representation
Each owner can have multiple members (1:M)
A member may have several owners

Coronel

flexible data access
“network” vs. “tree” path traversal
Conformance to standards
enhanced

database administration and portability
Disadvantages
System complexity
require familiarity with the internal structure for data access
Lack of structural independence
small structural changes require significant program changes

manipulation (programs) too dependent on physical file structure
Hard to manipulate

by end-users
No capacity for ad-hoc query (must rely on DB programmers).
Evolution in Data Organization
E. F. Codd’s Relational Model proposal
Separated the notion of physical representation (machine-view) from logical representation (human-view)
Considered ingenious but computationally impractical in 1970
Relational Database Model
Dominant database model of today
Eliminated pointers and used tables to represent data
Tables
flexible logical structure for data representation
a series of row/column intersections
related by sharing common entity characteristic(s)

and associations among groups of data (i.e., tables)

physical data storage/access
Easier database design, implementation, management, and use

Ad hoc query capability with Structured Query Language (SQL)
SQL translates user queries to codes
Disadvantages
Substantial hardware and system software overhead
more complex system
Poor design and implementation is made easy
ease-of-use allows careless use of RDBMS

Toward a Unified View of Data”
Graphical representation of entities and

their relationships
Entity Relationship (ER) Model
Based on Entity, Attributes & Relationships
Entity is a thing about which data are to be collected and stored
e.g. EMPLOYEE
Attributes are characteristics of the entity
e.g. SSN, last name, first name
Relationships describe an associations between entities
i.e. 1:M, M:N, 1:1
Complements the relational data model concepts
Helps to visualize structure and content of data groups
entity is mapped to a relational table
Tool for conceptual data modeling (higher level representation)
Represented in an Entity Relationship Diagram (ERD)
Formalizes a way to describe relationships between groups of data

in capital letters.
Relationships
represented by an active or passive verb inside

the diamond that connects the related entities.
Connectivities
i.e., types of relationship
written next to each entity box.

Database Systems: Design, Implementation, & Management: Rob & Coronel

name in capital letters.
Relationships
represented by an active or passive verb

that connects the related entities.
Connectivities
indicated by symbols next to entities.
2 vertical lines for 1
“crow’s foot” for M

Database Systems: Design, Implementation, & Management: Rob & Coronel

representation of database
facilitates database design and management
Integration with the relational

database model
enables better database design via conceptual modeling
Disadvantages
Incomplete model on its own
Limited representational power
cannot model data constraints not tied to entity relationships
e.g. attribute constraints
cannot represent relationships between attributes within entities
No data manipulation language (e.g. SQL)
Loss of information content
Hard to include attributes in ERD

in a single structure (object)
Developed by Hammer & McLeod in

1981
Object-oriented concepts became popular in 1990s
Modularity facilitated program reuse and construction of complex structures
Ability to handle complex data types (e.g. multimedia data)

Object-Oriented Database Model (OODBM)
Maintains the advantages of the ER model but adds more features
Object = entity + relationships (between & within entity)
consists of attributes & methods
attributes describe properties of an object
methods are all relevant operations that can be performed on an object
self-contained abstraction of real-world entity
Class = collection of similar objects with shared attributes and methods
e.g. EMPLOYEE class = (employ1 object, employ2 object, …)
organized in a class hierarchy
e.g. PERSON > EMPLOYEE, CUSTOMER
Incorporates the notion of inheritance
attributes and methods of a class are inherited by its descendent classes

Management: Rob & Coronel
OODBM: - can accommodate relationships within a

object - objects to be used as building blocks for autonomous structures

meaningful description of data via object
Modularity, reusability, inheritance
Ability to handle
complex

data
sophisticated information requirements
Disadvantages
Lack of standards
no standard data access method
Complex navigational data access
class hierarchy traversal
Steep learning curve
difficult to design and implement properly
More system-oriented than user-centered
High system overhead
slow transactions