Transcript Introduction to Databases

Introduction to Databases
A line manager asks, “If data unorganized is like
matter unorganized and God created the heavens and
earth in six days, how come it takes six months for all
these consultants to build a database model for a
simple business process?”
Introduction to Databases





File System Limitations
Database Systems
History of Databases
Pros & Cons of Databases
Database Careers
File System Limitations
Section #1






Separating and Joining Data
Duplicating Data
Data Dependence
Incompatible File Formats
Fixed Queries
Security Limitations
Separating and Isolating Data

Files store related data about a subject


Joining files lets you answer questions


Files only contain information for their primary
purpose.
Programs resolve complex relationships and
formatting between different files.
Business reports read multiple files

Programmers write reports by joining data from
multiple files to create meaningful information.
Duplicatiing Data

Files systems duplicate data


Files maintain their copy of the data


Files become data islands with complex organization,
and store copies of secondary data from other files.
Files maintain their own copy of data and change the
data without advising the primary source file.
Duplicate data leads to inconsistent data

Over time, businesses don’t know which file contains
the most accurate data, and can make bad decisions.
Data Dependence

Physical storage in files


Delimiters, parsing and concatenating


Files store things in structures or lines of text and
they are called flat files.
Delimiters are non-text characters separating data
elements, and can be XML tags.
Position specific files

Files organized by physical position define what
character range contains a data element.
Incompatible File Formats

Programming languages differ


Programmers solve problems differently


Files written by C/C++ differ from files written by
COBOL or RPG.
Organization of files differs dependent on how the
programmer saw the problem.
Formats differ

Data structures differ between files and require
mapping their differences.
Fixed Queries

Fixed queries report or enter data


Fixed queries are written by programmers


These are programs dealing with one or more files
that are reports or data entry forms.
Programmers write reports and forms as requested
and budgeted by organizations.
Fixed queries don’t answer Ad Hoc questions

Impromptu end-user questions can’t be answered
without the assistance of an IT organization.
Security Limitations

File ownership limits


File change limits


Primary source files are controlled by one
organization in businesses.
Primary source files are changeable only with
permission of the owning organization.
File confidentiality

Primary source files are viewable as a whole and
cannot be restricted by data elements, resulting in
compromised confidentiality.
File System Limitations
Review Section #1






Separating and Joining Data
Duplicating Data
Data Dependence
Incompatible File Formats
Fixed Queries
Security Limitations
Database Systems
Section #2





Database Instance
Database Management System
Standard Language Interface
Security Privileges & Roles
Database Types
Database Instance




Data and the definition of data are stored
in an organized way.
Data is stored in tables that are 2D arrays,
like a spreadsheet.
Data definition is metadata, or data about
data, which is also stored in tables.
Data can also contain stored programs
that let you access data.
Database Management System




Programs that manage and access the
database instance.
Programs that let external programs
access the database instance.
Programs that control access to the
database instance.
Programs that backup and recover the
database instance.
Standard Language Interface

Structured Query Language (SQL)


Structured English QUEry Language (SEQUEL)
ANSI Standards





ANSI
ANSI
ANSI
ANSI
ANSI
SQL-86
SQL-89
SQL-92
SQL:1999
SQL:2003
Security Privileges & Roles

Security is tiered by ANSI-SPARC rules




Superuser grants privileges to users




Superuser – holds internal data
Administrator – holds conceptual data
User – holds external data
Grants individual privileges one at a time.
Creates roles as groups of privileges.
Grants grouped privileges as roles.
Data can be restricted at various levels
Database Types

Database types



Operational – dynamic, time-independent, and critical
business operational data.
Analytical – statistical, historic, and time-dependent
business data.
Database implementation types


Online Transactional Processing (OLTP) – business
applications, also known as operational databases.
Online Analytical Processing (OLAP) – data
warehouses, also known as analytical databases.
Database Systems
Revised Section #2





Database Instance
Database Management System
Standard Language Interface
Security Privileges & Roles
Database Types
History of Databases
Section #3





File Systems
Hierarchical File Systems
Networked File Systems
Relational Databases
Object Relational Databases


Also known as Extended Relational Databases
Object-Oriented Databases
File Systems





Files were the first way to organize data.
Files contain related data in structures.
File systems contain files, and they
duplicate and corrupt copies of data.
File systems are programming language
specific, restricting file sharing.
File systems have too many structures.
Hierarchical File Systems



Are defined by an inverted tree
The top most node is a parent, who can
have child nodes, and the bottom node is
a leaf node – by a parent-child pointer.
You navigate from the top most node to
all subordinate nodes, which becomes
expensive when data is spread out.
Networked File Systems



Are also defined by an inverted tree
The top most node (also a set structure)
links by a pointer or list of pointers to
other set structures or nodes.
Searches are made from node to node
through pointers that associate a node
with subordinate nodes or set structure.
Relational Databases





Are defined by tables.
Tables are defined by a list of columns, known
as a signature.
Metadata tables hold definitions of tables.
Links between tables are dynamic relationships
as opposed to stored pointer structures.
Relationships between tables are made by
comparing values in columns, and columns are
known as primary and foreign keys.
Object Relational Databases
(Extended Relational Databases)





Are defined by objects, which can be tables,
nested collections, or objects with methods.
Objects are defined by a list of columns, known
as a signature, which can contain methods.
Metadata tables hold definitions of objects.
Links between objects are dynamic relationships.
Relationships between objects are made by
comparing values in columns, and columns are
known as primary and foreign keys.
Object-Oriented Databases





Are defined by instantiable objects with
methods.
Objects are defined by a list of attributes with a
constructor and methods.
Metadata tables hold definitions of objects.
Links between objects are dynamic relationships.
Relationships between objects are made during
instantiation of object instances by dynamic
marshalling.
History of Databases
Review Section #3





File Systems
Hierarchical File Systems
Networked File Systems
Relational Databases
Object Relational Databases


Also known as Extended Relational Databases
Object-Oriented Databases
Pros & Cons of Databases
Section #4

Advantages


Data redundancy control, consistency,
sharing, integrity, security, access,
responsiveness, concurrency, independence,
and backup/recovery.
Disadvantages

Complexity, conversion costs, performance,
and all-or-nothing risk of catastrophic failure.
Database Careers
Section #5

Database Administrator (DBA)





Physical Database Administrator
Application Database Administrator
Database Analyst
Database Designer
Database Programmer
Database Administrator (DBA)

Physical Database Administrator




Physical file administration and ownership
Startup and shutdown authority
Backup and recovery responsibility
Application Database Administrator



Privilege and role administration
User/schema administration
Operations and tuning administration
Database Analyst

Database physical model implementation




Translation of logical design to physical model
Analysis of query performance
Analysis of index performance
Database data integrity


Analysis of insertion, update and deletion
anomalies
Analysis of primary and foreign key validation
Database Designer

Define a data model for business process






Meet with users and define requirements
Understand the purpose of the business
Design a logical model to manage data
Define primary and foreign key relationships
Render model and generate build scripts
Secure business acceptance of model
Database Programmer

Define a business processes






Meet with users and define requirements
Understand the purpose of the business
Design a program architecture for process
Write programs to support process
Coordinate unit and integration testing
Document and release code to production
Database Careers
Review Section #5

Database Administrator (DBA)





Physical Database Administrator
Application Database Administrator
Database Analyst
Database Designer
Database Programmer
Summary





File System Limitations
Database Systems
History of Databases
Pros & Cons of Databases
Database Careers