SQL and Database Management

1
SQL and Database Management

• MIS 171 Web Technologies and Innovation
• Paul F. Clay, Ph.D.

2
Definitions

• Data
• Facts, images, sound and video
• Information
• Data that has been processed so that it can be
  interpreted and used
• Metadata
• Data about data

3
Database System

• The database system consists of four major
  components
• Database
• Collection of integrated and shared data
• Hardware
• Processor CPU
• Memory primary, secondary and tertiary
• Software
• Database Management System (DBMS)
• Application programs
• People
• Administrators, Designers, Developers, End Users

4
Database System Database

• An organized collection of logically-related
  persistent data
• A shared self-describing collection of
  inter-related data
• Implicit Properties
• Represents some aspect of the real world called
  the mini world or the universe of discourse
• A logically related collection of data
• Developed for a specific purpose
• Has metadata, i.e., repository of definitions

• Database
• Hardware
• Software
• People

5
Database System Hardware

• Processor to support the execution of the
  database system software.
• Storage
• Primary main memory/ random access memory (RAM)
  and cache
• Secondary Magnetic Disks
• Tertiary Tapes

• Database
• Hardware
• Software
• People

6
Database System Software

• DBMS Software used to define, create, maintain,
  and provide controlled access to the database
• Define Specify data types, structures and
  constraints for the data to be stored
• Populate Process of entering/ storing the data
• Manipulate includes such functions as querying
  to retrieve specific data, updating the database
  to reflect changes, and generating reports from
  the data
• Control grant / restrict permissions, security
• Examples of DBMS
• Oracle 10g, IBM DB2, MS-SQL Server, MS-Access, My
  SQL

• Database
• Hardware
• Software
• People

7
Database System People

• Database Designer
• Design database structure, i.e., they are
  database architects
• Database Administrators
• Manage the DBMS use and ensure that the database
  is functioning properly
• Application programmers
• Responsible for writing application programs that
  use the database typically in a language like C,
  Java etc.
• End users
• Interact with the system from online workstations
  or terminals

• Database
• Hardware
• Software
• People

7
8
Database Example Table CORP
metadata
SYMBOL NAME --------------------
---------------------------- INTC
INTEL CORP CSCO CISCO
SYSTEMS MSFT MICROSOFT CP ORCL
ORACLE CORP AMAT
APPLIED MATL JDSU JDS
UNIPHASE SUNW SUN MICROSYS BRCD
BROCADE COMMS SIRI
SIRIUS SAT RADI MCDTA MCDATA CL
A DELL DELL INC
data
8
9
Benefits of Database Approach

• The data can be shared
• Sharing data is straightforward.
• FS Write program. Hard to share old data.
• DB Existing data can easily be shared by many
  applications.
• Redundancy can be reduced
• Facts are recorded in one place in database.
• FS Write program. Hard to control.
• DB Redundancy can be controlled.
• Inconsistency can be avoided (to some extent)
• Make sure Symbol of each company is valid.
• Make sure each company name is not stored more
  than once.
• FS Write program.
• DB DBMS can enforce some types of integrity.

9
10
Benefits of Database Approach 2

• Transaction support can be provided
• Allow multiple users to run the program at the
  same time.
• FS Write programs, maybe using locks.
• DB DBMS supports transaction management.
• Security can be enforced
• Authorize each user controlled access (e.g.,
  insert, update).
• FS Write programs, maybe using passwords.
• DB DBMS enforces security.
• Standards can be enforced
• Standardize the data representation across
  systems?
• FS All programs must agree on the data
  representation.
• DB The DBA can ensure that all applicable
  standards are observed in the representation of
  data.

10
11
Evolution of Database Systems

• Pre-1960s
• 1945 Magnetic tape developed
• 1959 IBM introduced the RAMAC (Random Access
  Method of Accounting And Control) system
• 1960s
• Information Management System (IMS) developed by
  IBM
• SABRE developed by IBM and American Airlines
• 1970s
• Relational model developed by Ted Codd
• ER diagram introduced by Chen
• 1980s
• DBMS developed for personal computers (DBASE,
  PARADOX)
• Preliminary SQL standard published
• 1990s
• Parallel processors
• OODB
• Standards for data query and exchange (SQL2)

• 50 disks, 2
• 5 MB
• 10k/MB

11
12
Overview

• SQL
• Structured Query Language
• High level declarative language for relational
  database
• De facto standard in the database world
• Is now an official standard too, accepted by
• American National Standards Institute (ANSI)
• International Organization for Standardization
  (ISO)
• The first standard (1986) SQL/86
• SQL2 (SQL/92) is the most popularly in use today

13
SQL

• DDL
• Define the database
• CREATE, ALTER and DROP a TABLE, VIEW or INDEX
• DML
• Manipulate the data in an existing database
• INSERT, UPDATE, DELETE
• SELECT, i.e., query
• DCL
• Control user access to an existing database
• GRANT and REVOKE

14
SQL- query

• SELECT ltcolumnsgt
• FROM lttablegt
• WHERE ltconditiongt
• GROUP BY ltcolumnsgt
• HAVING ltconditiongt
• ORDER BY ltcolumngt

• What is the first and last name of every student
  who has a GPA greater than 3.5?
• SELECT F_Name, L_Name
• FROM Student
• WHERE GPAgt3.5

14
15

• SELECT F_Name, L_Name
• FROM Student
• WHERE GPAgt3.5

15
16
Decentralized Computing

• Questions?