F23AF1: DATABASE

1
F23AF1 DATABASE

• Miss Jenny Coady
• Heriot-Watt University
• Jenny_at_macs.hw.ac.uk
• Room EMG37, Ext 4178

2
Whats a Database?

• A Database is a collection of logically related
  data for a particular domain.
• Domain is often called the University of
  Discourse
• A Database Management System (DMBS) is a software
  System designed for the creation and management
  of Databases.
• e.g. Oracel, DB2, Access, MySQL, SQLServer
• A Database Application is a program that
  interacts with a database during its execution.
• A Database System is the Database together with
  the DBMS and associated applications

3
Real World Examples

• Databases are everywhere in the real-world, even
  though you do not often interact with them
  directly.
• Examples
• Retailers manage their products and sales using
  a Database.
• Online websites, i.e. Amazon and eBay track
  orders and shipments using a DB.
• The University maintains all registration
  information and marks in a database that is
  partially accessible through ISIS.
• Can you think of other examples?

4
Whats a DBMS?

• A Database is a very large collection of
  integrated data.
• A DBMS is a software package designed to store
  and manage databases. It provides efficient,
  convenient and safe multi-user storage and access
  to massive amounts of data.
• It Models real-world organization (e.g. business,
  University, ) through Entities and Relationships
• Entities (e.g. Students, modules)
• Relationships ( Student No 0000001 is taking
  F23AF1)

5
Why Use a DBMS?

• Without a DBMS, your application must rely on
  files to store its data persistently. A
  file-based system is a set of applications that
  use files to store their data.
• Each application in a file-based system contains
  its own code for accessing and manipulating files
  this causes problems such as
• Code duplication Difficulty in handling data
  changes
• Highly redundant data High Maintenance Costs
• Hard to support Multi-user access
• A DBMS provides generic functionality that
  otherwise would have to be implemented over and
  over again
• Data Independence and efficient access Uniform
  Data Administration
• Data Integrity and Security Concurrent Access
• All leads to a reduced application development
  time!

6
Data Models

• A Data Model is a collection of concepts for
  describing data. E.g.,
• Relations, attributes, tuples, (relational
  Model)
• Classes, subclasses, attributes, objects, . (OO
  Models)
• Entities, relationships, attributes (ER Models)
• A Schema is a description of a particular
  collection of data using a given data model.
• The Relational Model of data is the most widely
  used model today.
• Main concept relation, basically a table with
  rows and columns.
• Every relation has a schema, which describes the
  columns, or fields.

7
Schema

• A database designer uses a Data Definition
  Language (DDL) to define a schema for the
  database. The schema is maintained and stored in
  the system catalogue. The schema is one type of
  meta-data.
• A schema is a description of the structure of the
  database.
• A schema contains structures, names, and types of
  data stored.
• For example, the data model for Access is a
  relational model. A relational model contains
  tables and fields as its model constructs. The
  following DDL creates a product table
• CREATE TABLE product(
• prodID as VARCHAR(10) NOT NULL,
• prodName as VARCHAR(40),
• ProdDesc as VARCHAR(50),
• inventory as INTEGER,
• PRIMARY KEY (prodID)
• )

8
Data Definition Language

• The database is described to the DBMS using a
  Data Definition Language (DDL). The DDL allows
  the users to create data structures in the data
  model used by the database.
• The DDL defines the external view of the database
• A Data Model is a collection of concepts that can
  be used to describe the structure of a database.
• In the relational model, data is represented as
  tables and fields
• Examples relational model, XML, graphs, object
  oriented

9
Data Manipulation Language

• Once a database has been created in a DBMS using
  a DDL, the user accesses the data using a Data
  Manipulation Language (DML).
• The standard DML is SQL.
• The DML allows for the insertion, modification,
  retrieval, and deletion of data.
• Using a DML provides data abstraction as user
  queries are specified using the names of data
  elements and not their physical representation.
• For example, in a file system storing 3 fields,
  you would have to provide the exact location of
  the field in the file. In a database, you would
  only have to specify it by name.
• The DBMS contains all the code for accessing the
  data, so the applications do not have to worry
  about those details any more.

10
Three Levels of Abstraction

• ANSI-SPARC architecture for DBMSs
• Many Views,
• Single Conceptual (logical) schema
• And physical schema
• View describe how users see
• the data
• Conceptual schema defines
• logical structure
• Physical Schema describes the
• Files and indexes
• Schemas are defined using DDL
• Data is modified /queried using a DML.

11
ANSI/SPARC
12
Benefits of 3-Schema Architecture

• External Level
• Each user can access the data, but have their own
  view of the data independent of other users.
• Logical data independence - conceptual schema
  changes do not affect external views.
• Conceptual Level
• Single shared data representation for all
  applications and users which is independent of
  physical data storage.
• Users do not have to understand physical data
  representation details.
• The DBA can change the storage structures without
  affecting users or applications. Physical data
  independence - conceptual schema not affected by
  physical changes such as adding indexes or
  distributing data
• Physical Level
• Provides standard facilities for interacting with
  operating system for space allocation and file
  manipulation.

13
Example University Database

• Conceptual Schema
• Student (number string, name string, login
  string,
• birthday date, year integer)
• Module (id string, name string, credits
  integer)
• Enrolled (number string, id string, grade
  string)
• Physical Schema
• Relations stored as Unordered Files
• Index on first column of Students
• External Schema (View)
• Module_info (id string, enrolment integer)

14
Data Independence

• Means To Insulate applications from how data is
  structured and stored
• The major problem with developing applications
  based on files is that the
• application is dependent on the file structure.
  i.e. if the data file changes, the
• code that accesses the file must then be changed
  in the application.
• Applications need to be protected from changes in
  the
• Logical structure of the data logical data
  independence
• Physical structure of the data physical data
  independence
• One of the major benefits of DBs is they provide
  data abstraction. This
• Allows the internal definition of an object to
  change without affecting
• programs that use the object through external
  definition.

15
Concurrency Control

• Concurrent Execution of user programs
• is essential for good DBMS performance.
• Reason Disk accesses are frequent and slow
• keep the CPU busy by working on several user
  programs.
• Interleaving actions of different user programs
• can lead to inconsistencies
• Example Money is transferred while account
  balance is being computed
• A DBMS ensures such problems dont arise
• Users can pretend they are using a single-user
  system

16
Database people

• Users of query language interface
• DB application programmers (e.g.webmasters)
• Database designers
• Tool developers
• DBMS designers and implementors
• Database administrator (DBA)
• Designs logical/ physical schemas
• Handles security and authorisation
• Data availability, crash recovery
• Database tuning as needs evolve
• These must all understand how a DBMS works!

17
Components of a DBMS

• A DBMS is a complicated software system
  containing many components
• Query processor - translates user/application
  queries into low level data manipulation actions.
• Sub-components query parser, query optimizer
• Storage manager - maintains storage information
  including memory allocation, buffer management,
  and file storage.
• Sub-components buffer manager, file manager
• Transaction manager - performs scheduling of
  operations and implements concurrency control
  algorithms.

18
Structure of a DBMS

• A typical DBMS has
• a layered architecture
• Concurrency control
• and recovery components
• are not shown
• One of several possible
• architectures
• each system has its
• own variations.

19
Advantages of a DBMS

• Data access transparency (data independence)
• ??Program is not tied to physical data storage
  characteristics.
• Data consistency and integrity
• ??Data can be validated using rules and
  constraints.
• Data sharing for multiple users (concurrency)
• Integrated systems for backup, recovery, and
  security
• Provides different views, or perspectives, of the
  data

20
Disadvantages of DBMS

• Disadvantages of a DBMS ??
• High initial investment (software, hardware,
  training)
• Cost of conversion from file-based approaches
• Overheads (security, concurrency, recovery)
• High impact of failure (if DBMS fails, we have
  problems...)
• Reasons not to use a DBMS
• If data structures are simple, well defined, and
  not expected to change.
• If there are stringent real-time requirements.
• If only single user access is needed.

21
Summary

• A DB is a collection of logically related data
  stored and managed by a DBMS.
• Benefits include
• Quick application development
• Data integrity and security
• Concurrent access
• Recovery from system crashes
• Levels of abstraction give data independence (3
  schema architecture)
• A DBMS typically has a layered architecture.
• Do handout in tutorial ?