DBMS, Schemas

1
DBMS, Schemas SQL

• 332351
• Programming Methodology II

2
Database Management System (DBMS)

• Collection of interrelated data
• Set of programs to access the data
• DBMS contains information about a particular
  enterprise
• DBMS provides an environment that is both
  convenient and efficient to use.
• Database Applications
• Banking all transactions
• Airlines reservations, schedules
• Universities registration, grades
• Sales customers, products, purchases

3
Purpose of Database System

• In the early days, database applications were
  built on top of file systems
• Drawbacks of using file systems to store data
• Data redundancy and inconsistency
• Multiple file formats, duplication of information
  in different files
• Difficulty in accessing data
• Need to write a new program to carry out each new
  task
• Data isolation multiple files and formats
• Integrity problems
• Integrity constraints (e.g. account balance gt 0)
  become part of program code
• Hard to add new constraints or change existing
  ones

4
Purpose of Database Systems (Cont.)

• Drawbacks of using file systems (cont.)
• Atomicity of updates
• Failures may leave database in an inconsistent
  state with partial updates carried out
• E.g. transfer of funds from one account to
  another should either complete or not happen at
  all
• Concurrent access by multiple users
• Concurrent accessed needed for performance
• Uncontrolled concurrent accesses can lead to
  inconsistencies
• E.g. two people reading a balance and updating it
  at the same time
• Security problems
• Database systems offer solutions to all the above
  problems

5
Levels of Abstraction

• Physical level describes how a record (e.g.,
  customer) is stored tracks, cylinders, indices
  etc. on the random access disk system
• Logical level describes data stored in database,
  and the relationships among the data.
• type customer record name
  string street string city
  integer end
• View level application programs hide details of
  data types. Views can also hide information
  (e.g., salary) for security purposes.

6
View of Data
An architecture for a database system
7
Instances and Schemas

• Similar to types and variables in programming
  languages
• Schema the logical structure of the database
• e.g., the database consists of information about
  a set of customers and accounts and the
  relationship between them)
• Analogous to type information of a variable in a
  program
• Physical schema database design at the physical
  level
• Logical schema database design at the logical
  level
• Instance the actual content of the database at
  a particular point in time
• Analogous to the value of a variable
• Physical Data Independence the ability to
  modify the physical schema without changing the
  logical schema
• Applications depend on the logical schema
• In general, the interfaces between the various
  levels and components should be well defined so
  that changes in some parts do not seriously
  influence others.

8
Relational Schema Example of a Relation
9
Basic Structure

• Formally, given sets D1, D2, . Dn a relation r
  is a subset of D1 x D2 x x DnThus a
  relation is a set of n-tuples/rows (a1, a2, ,
  an) where each ai ? Di
• Example if
• customer-name Jones, Smith, Curry,
  Lindsay customer-street Main, North,
  Park customer-city Harrison, Rye,
  PittsfieldThen r (Jones, Main, Harrison),
  (Smith, North, Rye),
  (Curry, North, Rye),
  (Lindsay, Park, Pittsfield) is a relation over
  customer-name x customer-street x customer-city

10
Attribute Types

• Each attribute (column) of a relation has a name
• The set of allowed values for each attribute is
  called the domain of the attribute
• Attribute values are (normally) required to be
  atomic, that is, indivisible
• E.g. multivalued attribute values are not atomic
• E.g. composite attribute values are not atomic
• The special value null is a member of every
  domain

11
Relation Schema

• A1, A2, , An are attributes
• R (A1, A2, , An ) is a relation schema
• E.g. Customer-schema
  (customer-name, customer-street, customer-city)
• r(R) is a relation on the relation schema R
• E.g. customer (Customer-schema)

12
Relation Instance

• The current values (relation instance) of a
  relation are specified by a table
• An element t of r is a tuple, represented by a
  row in a table

13
Relations are Unordered

• Order of tuples is irrelevant (tuples may be
  stored in an arbitrary order)
• E.g. account relation with unordered tuples

14
Database

• A database consists of multiple relations
• Information about an enterprise is broken up into
  parts, with each relation storing one part of the
  information E.g. account stores
  information about accounts
  depositor stores information about which
  customer owns which account
  customer stores information about customers
• Storing all information as a single relation such
  as bank(account-number, balance,
  customer-name, ..)results in
• repetition of information (e.g. two customers own
  an account)
• the need for null values (e.g. represent a
  customer without an account)

15
What is SQL?

• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database

16
Basic Structure

• SQL is based on set and relational operations
  with certain modifications and enhancements
• A typical SQL query has the form select A1, A2,
  ..., An from r1, r2, ..., rm where P
• Ais represent attributes
• ris represent relations
• P is a predicate.
• The result of an SQL query is a relation.

17
SQL Database Tables

• A database most often contains one or more
  tables. Each table is identified by a name (e.g.
  "Customers" or "Orders"). Tables contain records
  (rows) with data.
• Below is an example of a table called "Persons"
• The table above contains three records (one for
  each person) and four columns (LastName,
  FirstName, Address, and City).

18
SQL Queries

• With SQL, we can query a database and have a
  result set returned.
• A query like this
• Gives a result set like this

19
SQL Data Manipulation Language (DML)

• SQL (Structured Query Language) is a syntax for
  executing queries. But the SQL language also
  includes a syntax to update, insert, and delete
  records.
• These query and update commands together form the
  Data Manipulation Language (DML) part of SQL
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database
  table

20
SQL Data Definition Language (DDL)

• The Data Definition Language (DDL) part of SQL
  permits database tables to be created or deleted.
  We can also define indexes (keys), specify links
  between tables, and impose constraints between
  database tables.
• The most important DDL statements in SQL are 
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

21
The SELECT Statement

• The SELECT statement is used to select data from
  a table. The tabular result is stored in a result
  table (called the result-set).
• Syntax

22
Select Some Columns

• To select the columns named "LastName" and
  "FirstName", use a SELECT statement like this

"Persons" table
Results
23
Select All Columns

• Results

24
The SELECT DISTINCT Statement

• The DISTINCT keyword is used to return only
  distinct (different) values.
• The SELECT statement returns information from
  table columns. But what if we only want to select
  distinct elements?
• With SQL, all we need to do is to add a DISTINCT
  keyword to the SELECT statement

25
Using the DISTINCT keyword

• To select ALL values from the column named
  "Company" we use a SELECT statement like this

Results
"Orders" table
26
The WHERE Clause

• To conditionally select data from a table, a
  WHERE clause can be added to the SELECT
  statement.
• With the WHERE clause, the following operators
  can be used

27
Using the WHERE Clause

"Persons" table
Result
28
The INSERT INTO Statement

• The INSERT INTO statement is used to insert new
  rows into a table.
• You can also specify the columns for which you
  want to insert data

29
Using INSERT INTO
Persons table
Result
30
Modification of Database - Updates
Update one Column in a Row
Result
31
The Delete Statement
Result
32
Joins Keys

• Sometimes we have to select data from two or more
  tables to make our result complete. We have to
  perform a join.
• Tables in a database can be related to each other
  with keys. A primary key is a column with a
  unique value for each row. The purpose is to bind
  data together, across tables, without repeating
  all of the data in every table.

33
Joins Keys (contd.)

• When you look at the example tables notice that 
  
• The "Employee_ID" column is the primary key of
  the "Employees" table, meaning that no two rows
  can have the same Employee_ID
• The "Prod_ID" column is the primary key of the
  "Orders" table
• The "Employee_ID" column in the "Orders" table is
  used to refer to the persons in the "Employees"
  table without using their names

34
Using Joins Keys

Employees table
Orders table
35
Using Joins Keys (contd.)

• Who has ordered a product, and what did they
  order?

Results
36
References

• Database System Concepts, Fourth Edition -
  Abraham Silberschatz Henry F. Korth S. Sudarshan
• SLIDES
• http//cs-www.cs.yale.edu/homes/avi/db-book/slide-
  dir/
• Chapters 1,3 and 4
• SQL Tutorial
• http//www.w3schools.com/sql/default.asp