Database Technologies - PowerPoint PPT Presentation

1 / 34
About This Presentation
Title:

Database Technologies

Description:

What is a Database. A system that stores data 'persistent' Exists beyond the immediate use ... insulate applications from data storage details. ... – PowerPoint PPT presentation

Number of Views:36
Avg rating:3.0/5.0
Slides: 35
Provided by: csU59
Category:

less

Transcript and Presenter's Notes

Title: Database Technologies


1
Database Technologies
Yelena Yesha
2
Contents
  • Database Overview
  • DBMS
  • Relational Data Model
  • SQL
  • Web-Database Connectivity

3
What is a Database
  • A system that stores data
  • persistent Exists beyond the immediate use
  • Centralized storage
  • Single or multiple users

4
Advantages
  • Reduces redundancy
  • Reduces inconsistency
  • Shared
  • Data representation standards can be enforced
  • Enables security restrictions
  • Integrity maintained
  • Valid cross references between records
  • Allows data-independent applications
  • Applications ignorant of how data is stored

5
DBMS
  • Database Management System
  • Examples
  • Oracle
  • DB2
  • Microsoft SQL Server
  • Sybase
  • DBMS Users
  • End users
  • Programmers
  • Database administrations

6
DBMS Features
  • Optimize Queries
  • Manage memory
  • Control concurrent data access

7
Advanced DBMS Features
  • Integrity maintenance
  • Entity Integrity ( foreign key references)
  • Referential Integrity (foreign key references)
  • Cascading deletes
  • Set Null or default
  • No action (command is rejected)
  • Access Control
  • Grant Revoke
  • Access to tables
  • Capability to perform operations (Insert, update,
    delete, etc.)

8
Data Independence and Access Efficiency
  • DBMS allows to avoid rewriting all access
    routines every time the data format changes or
    data is added/modified/deleted
  • insulate applications from data storage details.
  • Logical independence protection from changes in
    logical structure of data
  • Physical independence protection from changes
    in physical structure of data
  • DBMS maintains data structures and implements
    algorithms allowing to avoid linear search
  • indexing search in O(log n)
  • fast access even on complex data queries

9
Concurrency Control and Data Integrity
  • DBMS insures semantically correct access to the
    same data by concurrent applications
  • two programs accessing the same data at the same
    time can result in an inconsistent update
  • implement sharing in a controlled manner
  • Data semantics may require certain constraints to
    be satisfied
  • DBMS guarantees that application programs comply
    with the constraints when adding/modifying the
    data

10
Reliability and Security
  • DBMS provides techniques for recovery from
    software and hardware failures
  • guarantee survival of the data across
    catastrophes.
  • DBMS prevents unauthorized users from
    accessing/modifying data or denying service to
    other users.

11
Data Distribution and Heterogeneity
  • Centralization is the enemy of scalability
  • a vast number of modern applications are
    distributed
  • Data sharing in a distributed environment is a
    challenge
  • Heterogeneity applies to networks, hardware,
    operating systems, programming languages, data
    formats, etc.
  • Distributed applications must mask the
    differences
  • Need distributed data management

12
Categories of Data Models
  • High-level or conceptual
  • entities, attributes, relationships
  • Representational or implementation or logical
  • relational, network hierarchical,
    object-oriented, object-relational
  • Physical or low-level
  • data storage

13
3-schema Architecture
  • Physical level description of a database
  • how things are stored on disk
  • files, record structures,
  • indices,
  • data structures for disk blocks,
  • methodology for dealing with too long records,
    etc.
  • Conceptual level description of a database
  • The description of application data (its schema)
    using one of the traditional data models.

14
3-Schema Architecture (cont'd)
  • View-level description of a database
  • What users of a particular application see
  • their own customized schema, e.g., for payroll,
    for the ticket agent, for a simulation program.
  • Multiple levels
  • helps with data independence
  • helps with maintenance.
  • Many views, single logical and physical schema.
  • Levels of abstraction give data independence.

15
The Entity-Relational Model
  • Entity a distinguishable object.
  • Entity set a set of entities all of the same
    type.
  • Attribute a single property of an entity
  • simple vs composite
  • single-valued vs multi-valued
  • stored vs derived
  • null values.
  • Domain set of values permitted for that
    attribute.

16
The E-R Model (contd)
  • Relationship an association between two or more
    entities.
  • Relationship set a set of relationships all of
    the same type
  • There is no correct schema for a batch of data.
    Which schema is best depends on the application.
  • Many basic data modelling choices depend on an
    understanding of the application.

17
Data Model
  • Data model notation for describing data, plus a
    set of operations used to manipulate that data.
  • a set of primitives for defining the structure of
    a DB
  • a set of operations for specifying the retrievals
    and updates on a DB
  • relational, hierarchical, network,
    object-oriented.

18
The Relational Model (Codd 1970)
  • The relational data model is the most important
    data model currently existing.
  • Value-oriented, i.e., allows operations on
    relations whose results are relations, thus
    enables to combine operations.
  • As opposed to object-oriented models, in which
  • Operations cannot be applied to the result of
    other operations
  • The result of an operation may be a new data
    type, and operations may not be available for
    this type.

19
Domain and Relation
  • A domain is a set of atomic values.
  • A relation is a finite subset of the Cartesian
    product of a finite list of domains
  • relation is a set of tuples
  • order of tuples is irrelevant and
  • no relation has 2 identical tuples
  • each tuple value is atomic
  • no composite attributes
  • no multi-valued attributes.

20
Relational Model (contd)
  • Everything is represented by relations
  • Formally Given sets D1, D2, ....Dn (not
    necessarily distinct), a relation R ? D1 X D2 X
    ...X Dn
  • Di 's are the domains and n is the arity
    (degree) of R
  • elements of R are called tuples
  • number of tuples in R is the cardinality of R
  • relational data model helps to view a relation as
    a table
  • Observe the following properties


21
Relational Model (contd)
  • Everything is represented by relations
  • Given sets D1, D2, ....Dn (not necessarily
    distinct), a relation R ? D1 X D2 X ...X Dn
  • Di 's are the domains and n is the arity (degree)
    of R
  • elements of R are called tuples
  • number of tuples in R is the cardinality of R.
  • Relational data model helps to view a relation as
    a table
  • each row represents a tuple (record)
  • each column represents an attribute (field).
  • Properties
  • no two rows are identical
  • the ordering of tuples is unimportant
  • the ordering of columns is important.

22
E-R to Relations (i.e., defining relations)
  • Done using DDL (Data Definition Language)
  • Name whole database schema
  • Declare domains for attributes
  • Define relations
  • name
  • attribute names and domains
  • primary and other keys
  • foreign keys

23
Translating from E-R
  • Represent entity set E by a relation whose
    attributes are all the E-R attributes of E. Then
    each tuple represents one entity of E.
  • To represent relation R between entity sets E1,
    , Ek, create relation R with key attributes of
    E1, , key attributes of Ek, as attributes
    (rename duplicates). Each tuple of the relation
    represents one combination of entities that are
    related to one another.
  • You might have some redundant relations, which
    you can delete.

24
Schema Normalization
  • Formal theory of database design
  • based on grouping attributes in a particular way
    using attribute dependencies to achieve good
    schemas
  • 1NF, 2NF, 3NF, BCNF, 4NF,
  • Goal
  • dont store redundant information
  • can represent everything (otherwise, the schema
    is useless!)

25
Query and Update Languages
  • DDL data definition language
  • used by DBA
  • to define schemas, create views, create indices
  • DML data manipulation language
  • used by sophisticated casual user
  • to query data or
  • update data

26
Relational Query Languages
  • Query languages allow manipulation and retrieval
    of data from a database.
  • Relational model supports simple, powerful query
    languages
  • strong formal foundation based on logic
  • allows for optimization.
  • Two mathematical languages form the basis for
    relational languages (e.g., SQL) and for
    implementation
  • Relational Algebra More operational, useful for
    representing execution plans
  • Relational Calculus Lets users describe what
    they want, rather than how to compute it
    (non-operational, declarative).
  • Basic operations
  • selection, projection, cross-product,
    set-difference, union, intersection, join,
    division

27
SQL
  • Originally Structured Query Language
  • Common language for interacting with relational
    databases
  • Syntax
  • SELECT column_name
  • FROM table_name
  • WHERE where_clause
  • SQL is now the query language for IBM's DB2 and
    the de-facto standard on most commercial RDBMS.
  • SQL is a comprehensive language providing
    statements for data definition, query and update.

28
Using SQL
  • Interactively
  • Via a command line program
  • Embedded SQL
  • Embedded complete SQL statement in code
  • Precompiler modifies the source code
  • Dynamic SQL
  • Embedded in scripts or other programs
  • Dynamically interpreted
  • Variables bound at run time

29
SQL Schema
  • EMP(Name,SSN,DNO,BirthPlace)
  • DEPT(DName,DNO,MGRSSN)
  • PROJECT(PName,PNO,PLocation,DNum)
  • WORKSON(ESSN,PNO,Hours)
  • CREATE SCHEMA 'COMPANY'
  • CREATE TABLE EMP
  • (
  • EName name_dom NOT NULL,
  • SSN CHAR(9) NOT NULL,
  • DNO INTEGER NOT NULL,
  • BirthPlace city_dom,
  • PRIMARY KEY(SSN),
  • FOREIGN KEY (DNO) REFERENCES DEPT (DNO)
  • )

30
Data Definition Language
  • DDL is used to define the (schema of) database
  • to create a database schema
  • to create a domain
  • to create, drop. alter a table
  • to create, remove an index defunct in SQL2
  • to create or drop a view
  • to define integrity constraints
  • to define access privileges to users (Oracle
    CONNECT, RESOURCE, DBA)
  • to GRANT or REVOKE privileges ON/TO object/user
  • SQL2 supports multiple schemas
  • CREATE SCHEMA name AUTHORIZATION user
  • CREATE SCHEMA EMPLOYEE AUTHORIZATION yesha

31
Web Server to Database Connectivity
  • The CGI-Based Approach

Database Server
HTTP
DB Protocol
Web Server
Web Browser
CGI
  • Easy to implement
  • HTTP server dependent
  • - Lack of user access control
  • - Lack of presentation graphics

32
Web Server to Database Connectivity (contd)
  • The Java-Based Approach

Gateway Server
DB protocol
TCP/IP
Database Server
Applet
HTTP
Web Server
Web Browser
  • - Higher development complexity
  • - True session based database access
  • - User access control through database server
  • - Presentation graphics

33
Web-Database Connectivity Middleware
  • Overview of the Web database connectivity, and
    focus on the middleware of the Web database
  • Trinity Three tires of Web Database use to
    build a Web database
  • Web Server
  • Web application server (middleware)
  • Database
  • (see picture)

34
How a user interacts with a Web Database
  • In a Web browser, a user submits a request to the
    Web server.
  • The Web server passes it onto the middleware
  • The middleware writes the request in SQL queries
    and sends it to a back-end database.
  • The data retrieved are handed back to the
    middleware
  • The middleware generates a Web page for the data
  • The Web server sends the Web page to the browser
  • The browser displays the Web page in front of the
    user
Write a Comment
User Comments (0)
About PowerShow.com