sql and database management n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL and Database Management PowerPoint Presentation
Download Presentation
SQL and Database Management

Loading in 2 Seconds...

play fullscreen
1 / 16

SQL and Database Management - PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on

SQL and Database Management. MIS 171 – Web Technologies and Innovation Paul F. Clay, Ph.D. 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. Database System.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL and Database Management' - halia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql and database management
SQL and Database Management

MIS 171 – Web Technologies and Innovation

Paul F. Clay, Ph.D.

definitions
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
database system
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
database system database
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
database system hardware
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
database system software
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
slide7

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

slide8

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

slide9

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

slide10

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

slide11

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

slide12

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
slide13

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
slide14

SQL- query

  • 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 GPA>3.5

SELECT <columns>

FROM <table>

[WHERE <condition>]

[GROUP BY <columns>]

[HAVING <condition>]

[ORDER BY <column>];

14

slide15

SELECT F_Name, L_Name

FROM Student

WHERE GPA>3.5

15