Database Systems

1
Database Systems
2
Major Content Grade

• Introduction
• The Relational Model
• SQL
• Transaction Management
• Database Design (E-R)
• Database Design (Normalization)

3
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

4
About SQL

• SQL is of the ability to implementing database in
  a computerized environment.
• The SQL is an non-procedural language.
• Power of SQL
• Data definition (DDL)
• ( Include Other database object definition )
• Interactive Data manipulation (DML)
• Embedded SQL and dynamic SQL(???????)
• Integrity
• Authorization (Security)
• Transaction Control

5
History of SQL

• IBM Sequel (Specifying QUeries As Relational
  Expression, 1972) language developed as part of
  System R project at the IBM San Jose Research Lab
• SEQUEL (Structured English QUEry Language,1974)
  Renamed SQL (Structured Query Language, 1976)
• ANSI and ISO standard SQL
• SQL-86, SQL-89 (SQL1)
• SQL-92 (SQL2)
• SQL1999 (SQL3)
• SQL2003
• Commercial systems offer most, if not all, SQL-92
  features, plus varying feature sets from later
  standards and special proprietary features.

6
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

7
Data Definition Language

• Data Definition Language (DDL) provide the
  abilities to setting up a database.
• DDL allows the specification of not only a set of
  relations but also information about each
  relation, including

• The schema for each relation.
• The domain of values associated with each
  attribute.
• Integrity constraints
• The set of indices to be maintained for each
  relations.
• Security and authorization information for each
  relation.
• The physical storage structure of each relation
  on disk.

8
Domain Types in SQL

• char(n) Fixed length character string, with
  user-specified length n.
• varchar(n) Variable length character strings,
  with user-specified maximum length n.
• int Integer (a finite subset of the integers
  that is machine-dependent).
• smallint Small integer (a machine-dependent
  subset of the integer domain type).
• numeric(p,d) Fixed point number, with
  user-specified precision of p digits, with n
  digits to the right of decimal point.

9
Domain Types in SQL

• real, double precision Floating point and
  double-precision floating point numbers, with
  machine-dependent precision.
• float(n) Floating point number, with
  user-specified precision of at least n digits.
• date Dates, containing a (4 digit) year, month
  and date
• Example date 2005-7-27
• time Time of day, in hours, minutes and
  seconds.
• Example time 090030 time
  090030.75
• timestamp date plus time of day
• Example timestamp 2005-7-27 090030.75

10
Domain Types in SQL

• blob binary large object -- object is a large
  collection of uninterpreted binary data (whose
  interpretation is left to an application outside
  of the database system).
• clob character large object -- object is a
  large collection of character data.
• User-defined domain
• Example create domain money numeric(12, 2)

11
Creating DataBase

• Creating the Banking database
• Database Schema
• branch (branch_name, branch_city, assets)
• customer (customer_name, customer_street,
  customer_city)
• depositor (customer_name, account_number)
• account (account_number, branch_name, balance)
• borrower (customer_name, loan_number)
• loan (loan_number, branch_name, amount)
• Creating database Banking steps
• 1) CREATE DATABASE Banking (syntax lie on DBMS)
• 2) Creating referenced tables(?????)
• 3) Creating referencing tables
• 4) Creating other object of database

12
Creating Tables

• Syntax

• An SQL relation is defined using the create
  table command
• CREATE TABLE lttable-namegt (
• ltcolumn-name1 data_typegt ltcolumn_constra
  intgt ,
• ...n
• lttable_constraintgt ,
• )

13
Creating Tables

• Creating Tables in the Banking database

• //Creating table customer in SQL
• CREATE TABLE customer (
• customer_name char(20),
• customer_street char(30) NOT NULL,
• customer_city char(30)
• )

14
Integrity Constraints in Tables

• Integrity constraints ENSURE that changes made to
  the database by authorized DO NOT result in a
  loss of data consistency.
• PRIMARY KEY Constrants (Entity integrity)
• FROEIGE KEY Constrants (Referential integrity)
• NOT NULL Constrants
• UNIQUE Constrants
• DEFAULT Constrants
• CHECK Constrants
• Assertion Constrants
• Syntax
• CONSTRAINT ltconstraint_namegt ltconstraintgt

15
Integrity Constraints in Tables

• Create table with constraints

CREATE TABLE account ( account_number
char(10), branch_name char(30) NOT
NULL, balance numeric(12.2), PRIMARY KEY
(account_number), FOREIGN KEY (branch_name)
REFERENCES branch(branch_name), CONSTRAINT
chk_balance CHECK (balance gt 0 ) )

• The referenced table must be an existing
  relation!
• Integrity constraints can be added to an existing
  relation, if the relation does not satisfies the
  constraint, reject!
• Constraint name make it easy to drop.

16
Drop and Alter Table

• The drop table command deletes all information
  about the dropped relation from the database.
• Syntax DROP TABLE lttable_namegt
• Example DROP TABLE customer
• The alter table command is used to add attributes
  or constraints to an existing relation.
• Syntax
• ALTER TABLE lt table_name gt ADDDROPALTER
• Examples
• ALTER TABLE customer ADD customer_id
  CHAR(10)
• ALTER TABLE customer DROP customer_city
• ALTER TABLE account ALTER balance numeric(10.2)

17
More about create tables

• After create table statement executed, the
  defination will been stored in Data Dictionary as
  metadata.
• A foreign key specification is accepted only if
  it references an existing table.
• There are more complexed integrity Constrants
  will be introduced later.
• Only after tables been created, data can be
  entered into the table in database.
• Integrity Constrants are important part of table
  to avoid invalid data into database.

18
????

• 3.1, 3.2
• 4.1, 4.2

19
Database File (ch11)

• The database is stored as a collection of files.
  Each file is a sequence of records
• A database file is partitioned into fixed-length
  storage units called blocks. Blocks are units of
  both storage allocation and data transfer.
• Database system seeks to minimize the number of
  block transfers between the disk and memory. We
  can reduce the number of disk accesses by keeping
  as many blocks as possible in main memory.
• Buffer portion of main memory available to
  store copies of disk blocks.

20
Database File

• A block is composed by block header, records and
  free space
• A page header contains
• number of record entries
• end of free space in the block
• location and size of each record
• Records can be moved around within a page to keep
  them contiguous with no empty space between them
• Blocks are linked together as a file

21
Index of Table (ch12)

• Records may be stored in the Sequential File
  Organization(????) ordered by a search-key.
• Example the account relation storage ordered by
  branch_name

• To find the records
• By a given branch_name value?
• -- binary search(????)
• By a given balance value?
• --Linear search(????)
• NOT a good idea!

22
Index of Table

• An index file consists of records (called index
  entries) of the form

• Search Key - attribute or set of attributes used
  to look up records in a file. Search Key is
  ordered or hashed.
• Indexing mechanisms used to speed up access to
  desired data.

23
Index of Table

• Types of index
• Primary index in a sequentially ordered file,
  the index whose search key specifies the
  sequential order of the file.
• Also called clustering index
• The search key of a primary index is usually but
  not necessarily the primary key.
• Secondary index an index whose search key
  specifies an order different from the sequential
  order of the file. Also called non-clustering
  index.
• Unique index an index was the accepted way in
  some database systems to guarantee a uniqueness
  constraint for a candidate key.

24
Index of Table

• SQL DDL about index
• CREATE UNIQUE CLUSTER INDEX ltindex_namegt ON
  lt table_name gt ( ltcolumn_name1gt ASC DESC
  ltcolumn_name2 gt ASC DESC )
• E.g. CREATE INDEX b-index ON
  branch(branch_name)
• DROP INDEX lt index_name gt

25
Database Systems
26
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

27
Basic Query Structure

• SQL is based on set and relational operations
  with certain modifications and enhancements
• A typical SQL query has the form

SELECT A1, A2, ..., AnFROM r1, r2, ...,
rmWHERE P

• Ai represents an attribute
• Ri represents a relation
• P is a predicate

• This query is equivalent to the relational
  algebra expression

• The result of an SQL query is a relation.

28
The SELECT Clause

• The SELECT clause list the attributes desired in
  the result of a query
• corresponds to the projection operation of the
  relational algebra
• Example find the names of all branches in the
  loan relation
• SELECT branch_name FROM loan
• In the relational algebra, the query would be
  ?branch_name (loan)
• NOTE SQL names are case insensitive (i.e., you
  may use upper- or lower-case letters.)

29
The SELECT Clause

• SQL allows duplicates in relations as well as in
  query results.
• To force the elimination of duplicates, insert
  the keyword DISTINCT after select.
• Example Find the names of all branches in the
  loan relations, and remove duplicates
• SELECT DISTINCT branch_name FROM loan
• The keyword all specifies that duplicates not be
  removed.
• SELECT ALL branch_name FROM loan

30
The SELECT Clause

• An asterisk() in the select clause denotes all
  attributes
• SELECT FROM loan
• The SELECT clause can contain arithmetic
  expressions involving the operation, , , ?, and
  /, and operating on constants or attributes of
  tuples.
• The query SELECT loan_number,
  branch_name, amount ? 100 FROM loan
• would return a relation that is the same as the
  loan relation, except that the value of the
  attribute amount is multiplied by 100.

31
The WHERE Clause

• The WHERE clause specifies conditions that the
  result must satisfy.
• Corresponds to the selection predicate of the
  relational algebra.
• Example Find all loan number at the Perryridge
  branch with loan amounts greater than 1200.
  SELECT loan_number FROM loan WHERE
  branch_name Perryridge AND amountgt1200
• Comparison results can be combined using the
  logical connectives AND, OR, and NOT.

32
The WHERE Clause

• SQL includes a BETWEEN comparison operator
• Example Find the loan number of those loans
  with loan amounts between 90,000 and 100,000
  (that is, ? 90,000 and ? 100,000)
• SELECT loan_number FROM loan WHERE amount
  BETWEEN 90000 AND 100000

33
The FROM Clause

• The FROM clause lists the relations involved in
  the query
• Corresponds to the Cartesian product operation of
  the relational algebra.
• Example Find the Cartesian product borrower
  loan
• SELECT ? FROM borrower, loan
• Example Find the name, loan number and loan
  amount of all customers having a loan at the
  Perryridge branch
• SELECT customer_name, borrower.loan_number,
  amountFROM borrower, loanWHERE
  borrower.loan_number loan.loan_number AND
  branch_name Perryridge

34
The RENAME Operation

• The SQL allows renaming relations and attributes
  using the AS clause
• old-name AS new-name
• Example Find the name, loan number and loan
  amount of all customers rename the column name
  loan_number as loan_id.
• SELECT customer_name, borrower.loan_number
  AS loan_id, amountFROM borrower, loanWHERE
  borrower.loan_number loan.loan_number

35
The RENAME Operation

• Relation variables are defined in the FROM clause
  via the use of the as clause.
• Example Find the customer names, loan number and
  loan amount for all customers having a loan from
  the bank.
• SELECT customer_name, B.loan_number, amount
  FROM borrower AS B, loan AS L WHERE
  B.loan_number L.loan_number

36
The RENAME Operation
Branch_ name Branch_ city assets
Brighton Brooklyn 3100000
Downtown Brooklyn 9000000
Mianus Horseneck 400000
North Town Rye 3700000

branch branch branch

• Example Find the names of all branches that have
  assets greater than at least one (some) branch
  located in Brooklyn.

B1.bn B1.bc B1.a B2.bn B2.bc B2.a
Brighton Brooklyn 3100000 Brighton Brooklyn 3100000
Brighton Brooklyn 3100000 Downtown Brooklyn 9000000
Downtown Brooklyn 9000000 Brighton Brooklyn 3100000
Downtown Brooklyn 9000000 Downtown Brooklyn 9000000
Mianus Horseneck 400000 Brighton Brooklyn 3100000
Mianus Horseneck 400000 Downtown Brooklyn 9000000
North Town Rye 3700000 Brighton Brooklyn 3100000
North Town Rye 3700000 Downtown Brooklyn 9000000
.
pB1.branch_name(?B1.assetsgtB2.assets ( ?B1)(
branch) ?B2.branch_cithBrooklyn(?B2(
branch)) ) )
37
The RENAME Operation
Branch_ name Branch_ city assets
Brighton Brooklyn 3100000
Downtown Brooklyn 9000000
Mianus Horseneck 400000
North Town Rye 3700000

branch branch branch

• Example Find the names of all branches that have
  assets greater than at least one (some) branch
  located in Brooklyn.

SELECT DISTINCT B1.branch_name FROM branch
AS B1, branch AS B2 WHERE B1.assets gt
B2.assets AND B2.branch_city Brooklyn
pB1.branch_name(?B1.assetsgtB2.assets
?B2.branch_cithBrooklyn( ?B1)( branch)
?B2( branch) ) )
38
String Operations

• SQL includes a string-matching operator for
  comparisons on character strings. The operator
  LIKE uses patterns that are described using two
  special characters
• percent (). The character matches any
  substring.
• underscore (_). The _ character matches any
  character.
• Example Find the names of all customers whose
  street includes the substring Main.
• SELECT customer_name FROM customer WHERE
  customer_street LIKE Main
• Example Match the name Main
• LIKE Main\ escape \

39
The ORDER BY Clause

• The ORDER BY Clause Ordering the Display of
  Tuples
• Example List in alphabetic order the names of
  all customers having a loan in Perryridge branch
• SELECT DISTINCT customer_name FROM borrower,
  loan WHERE borrower loan_number
  loan.loan_number AND branch_name
  Perryridge
• ORDER BY customer_name
• We may specify DESC for descending order or ASC
  for ascending order, for each attribute
  ascending order is the default.
• Example ORDER BY customer_name DESC

40
Set Operations

• The set operations union, intersect, and except
  operate on relations and correspond to the
  relational algebra operations ????????
• Each of the above operations automatically
  eliminates duplicates to retain all duplicates
  use the corresponding multiset versions union
  all, intersect all and except all.
• Set operations may NOT been implemented in some
  DBMS.

41
Set Operations

• Examples
• Find all customers who have a loan, an account,
  or both
• (SELECT customer_name FROM depositor)UNION(SELE
  CT customer_name FROM borrower)
• Find all customers who have both a loan and an
  account.
• (SELECT customer_name FROM depositor)INTERSECT(
  SELECT customer_name FROM borrower)
• Find all customers who have an account but no
  loan.
• (SELECT customer_name FROM depositor)EXCEPT(SEL
  ECT customer_name FROM borrower)

42
Database Systems
43
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

44
Aggregate Functions(???)

• These functions operate on the multiset of values
  of a column of a relation, and return a value

• avg(DISTINCT ALL ltcolumn_namegt) average
  value
• min(DISTINCT ALL ltcolumn_namegt) minimum
  value
• max(DISTINCT ALL ltcolumn_namegt) maximum value
• sum(DISTINCT ALL ltcolumn_namegt) sum of
  values
• count(DISTINCT ALL ltcolumn_namegt)count(DISTI
  NCT ALL ) number of values

45
Aggregate Functions

• Examples
• Find the average account balance at the
  Perryridge branch.
• SELECT avg (balance) FROM account WHERE
  branch_name Perryridge
• Find the number of tuples in the customer
  relation.
• SELECT count () FROM customer
• Find the number of depositors in the bank.
• SELECT count (DISTINCT customer_name) FROM
  depositor

46
The GROUP BY Clause

• The GROUP BY Clause division records in the
  middle result into different part(group)
  according attribute or attributes given by the
  GROUP BY Clause.
• Tuples with the same value on all attributes in
  the GROUP BY Clause are placed in one group.
• After the tuples was grouped, Aggregate Functions
  will act on the group, NOT the whole tuples.

47
The GROUP BY Clause

• Example Find the number of depositors for each
  branch.
• SELECT branch_name, count (customer_name) FROM
  depositor, account WHERE depositor.account_numbe
  r account.account_number GROUP BY
  branch_name

(DISTINCT customer_name)
Note Attributes in SELECT clause outside of
aggregate functions must appear in GROUP BY list.
48
The HAVING Clause

• The HAVING Clause is used to choose the specific
  groups according the given predicate following
  the HAVING Clause.
• The HAVING Clause is usually after the GROUP BY
  Clause.
• Example Find the names of all branches where the
  average account balance is more than 1,200.
• SELECT branch_name, avg (balance) FROM
  account GROUP BY branch_name
• HAVING avg (balance) gt 1200

49
Executing Orders of Query Clauses
?

• SELECT ALL DISTINCT
  ltobject_exp1gt, ltobject_exp1gt
• FROM lttable_name1gt, lttable_name2 gt
• WHERE ltcondition_expgt
• GROUP BY ltcolumn_namesgt
• HAVING ltcondition_expgt
• ORDER BY ltcolumn_name1gt ASC DESC
  ltcolumn_name2gt ASC DESC

?
?
?
?
50
Null Values

• It is possible for tuples to have a null value,
  denoted by null, for some of their attributes
• null signifies an unknown value or that a value
  does not exist.
• The predicate IS NULL can be used to check for
  null values.
• Example Find all loan number which appear in the
  loan relation with null values for amount.
• SELECT loan_number FROM loan WHERE amount
  IS NULL
• The result of any arithmetic expression with null
  involving null is null
• Example 5 null returns null
• Any comparison with null returns unknown
• Example 5 lt null or null ltgt null or
  null null

51
Null Values

• Three-valued logic using the truth value unknown
• OR (unknown OR true) true, (unknown OR
  false) unknown (unknown OR unknown)
  unknown
• AND (true AND unknown) unknown,
  (false AND unknown) false, (unknown
  AND unknown) unknown
• NOT (NOT unknown) unknown
• P is unknown evaluates to true if predicate P
  evaluates to unknown
• Result of WHERE clause predicate is treated as
  false if it evaluates to unknown

52
Null Values

• Aggregate functions simply ignore nulls
• Total all loan amounts
• SELECT sum (amount ) FROM loan
• Above statement ignores null amounts
• Result is null if there is no non-null amount
• All aggregate operations except count() ignore
  tuples with null values on the aggregated
  attributes.

53
Nested Subqueries

• SQL provides a mechanism for the nesting of
  subqueries.
• A subquery is a SELECT-FROM-WHERE expression that
  is nested within another query.
• Nested Query indicates that SQL is a structured
  language.

SELECT ALL DISTINCT ltobject_exp1gt,
ltobject_exp1gt FROM lttable_name1gt,
lttable_name2 gt WHERE ltcondition_expgt
GROUP BY ltcolumn_namesgt HAVING
ltcondition_expgt ORDER BY ltcolumn_name1gt
ASC DESC ltcolumn_name2gt ASC DESC

54
The IN predicate

• Find all customers who have both an account and a
  loan at the bank.
• SELECT DISTINCT customer_nameFROM
  borrowerWHERE customer_name IN (
• SELECT customer_name FROM depositor )
• Find all customers who have a loan at the bank
  but do not have an account at the bank
• SELECT DISTINCT customer_name FROM borrower
  WHERE customer_name NOT IN ( SELECT
  customer_name FROM
  depositor )

55
The IN predicate

• Find all customers who have both an account and a
  loan at the Perryridge branch.

A1 (IN predicate)
SELECT DISTINCT customer_nameFROM borrower,
loanWHERE borrower.loan_number
loan.loan_number AND branch_name
Perryridge AND (branch_name,
customer_name ) IN ( SELECT branch_name,
customer_name FROM depositor,
account WHERE depositor.account_number
account.account_n
umber )
56
The IN predicate

• Find all customers who have both an account and a
  loan at the Perryridge branch.

account_ number branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400
A-305 Round Hill 350
A-201 Brighton 900
A-222 Redwood 700
A-217 Brighton 750
account account account
loan_ number branch_ name amount
L-11 Round Hill 900
L-14 Downtown 1500
L-15 Perryridge 1500
L-16 Perryridge 1300
L-17 Downtown 1000
L-23 Redwood 2000
L-93 Mianus 500
loan loan loan
57
The IN predicate

• Find all customers who have both an account and a
  loan at the Perryridge branch.

A2 (Algebra expression)
SELECT DISTINCT depositor.customer_name FROM
depositor , account, borrower, loan WHERE
depositor.account_number account.account_number
AND borrower.loan_number loan.loan_number
AND depositor.customer_name
borrower.customer_name AND account.branch_name
Perryridge AND loan.branch_name
Perryridge
pdepositor.customer_name( ? depositor.customer_nam
e borrower.customer_name ?
account.branch_name Perryridge ?
loan.branch_name Perryridge (

)
58
Set Comparison

• gtsome ( gt any ), including gt, lt, lt,
• gtall, including gt, lt, lt,
• Example Find all branches that have greater
  assets than some branch located in Brooklyn.

Branch_ name Branch_ city assets
Brighton Brooklyn 3100000
Downtown Brooklyn 9000000
Mianus Horseneck 400000
North Town Rye 3700000

branch branch branch
SELECT branch_nameFROM branchWHERE assets gt
some ( SELECT assets FROM branch WHERE
branch_city Brooklyn)
59
Set Comparison

• Find the names of all branches that have greater
  assets than all branches located in Brooklyn.

Branch_ name Branch_ city assets
Brighton Brooklyn 3100000
Downtown Brooklyn 9000000
Mianus Horseneck 400000
North Town Rye 3700000

branch branch branch
SELECT branch_nameFROM branchWHERE assets gt
all ( SELECT assets FROM branch WHERE
branch_city Brooklyn)
60
Set Comparison

• Find the branch that has the highest average
  balance.

account_ number branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400
A-305 Round Hill 350
A-201 Brighton 900
A-222 Redwood 700
A-217 Brighton 750
account account account
SELECT branch_nameFROM account GROUP BY
branch_name HAVING avg(balance)
max (
SELECT avg(balance) FROM account
GROUP BY branch_name)
??????????????!
61
Set Comparison

• Find the branch that has the highest average
  balance.

account_ number branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400
A-305 Round Hill 350
A-201 Brighton 900
A-222 Redwood 700
A-217 Brighton 750
account account account
SELECT branch_nameFROM account GROUP BY
branch_name HAVING avg(balance)
(
SELECT avg(balance) FROM account
GROUP BY branch_name)
max(avg(balance))
?????????!
62
Set Comparison

• Find the branch that has the highest average
  balance.

account_ number branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400
A-305 Round Hill 350
A-201 Brighton 900
A-222 Redwood 700
A-217 Brighton 750
account account account
SELECT branch_nameFROM account GROUP BY
branch_name HAVING avg(balance)
gt ALL (
SELECT avg(balance) FROM account
GROUP BY branch_name)
63
Test for Empty Relations

• The exists construct returns the value true if
  the argument subquery is nonempty.
• Example Find the customers name who have at
  least one deposit of a balance greater than 700.

t ?u( depositor(u) ? ?v( account(v) ?
uaccount_number vaccount_number ?
vbalance gt 700 ) ? tcustomer_name
ucustomer_name )
SELECT DISTINCT customer_nameFROM
depositorWHERE
EXISTS ( SELECT FROM account WHERE
depositor.account account.account AND balance
gt 700 )
64
Test for Empty Relations

• Find all customers who have an account at all
  branches located in Brooklyn.

t ?u ( depositor(u) ? ?v (branch(v) ?
vbranch_city Brooklyn? ?w( account(w) ?
waccount_number uaccount_number ?
wbranch_name vbranch_name) ) ?
tcustomer_name ucustomer_name )
t ?u ( depositor(u) ? ? ? v (branch(v) ?
vbranch_city Brooklyn ? ? ?w( account(w) ?
waccount_number uaccount_number ?
wbranch_name vbranch_name) ) ?
tcustomer_name u customer_name )
65
Test for Empty Relations
SELECT DISTINCT customer_nameFROM depositor AS
DWHERE

• NOT EXISTS (
• SELECT FROM branch AS BWHERE branch_city
  Brooklyn AND
• NOT EXISTS (
• SELECT FROM account
• WHERE account_number D.account_number AND
  branch_name B. branch_name))

t ?u ( depositor(u) ? ? ? v (branch(v) ?
vbranch_city Brooklyn ? ? ?w( account(w) ?
waccount_number uaccount_number ?
wbranch_name vbranch_name) ) ?
tcustomer_name u customer_name )
66
Queries
account_ number Branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400

account account account

• Find the largest account balance.

A1 (Algebra expression)
pbalance(account)
pA1.balance (?A1.balance lt A2.balance
(?A1(account) ?A2(account) ) )
SELECT DISTINCT balanceFROM account EXCEPT SELEC
T A1.balanceFROM account AS A1, account AS A2
WHERE A1.balance lt A2.balance
67
Queries
account_ number Branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400

account account account

• Find the largest account balance.

A2 (Aggregate Functions)
SELECT DISTINCT balanceFROM accountWHERE
balance max( balance )
(
SELECT max( balance )FROM account )
68
Queries
account_ number Branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400

account account account

• Find the largest account balance.

A3 (Set Comparison)
SELECT DISTINCT balanceFROM accountWHERE
balance gt ALL ( SELECT balance FROM
account )
69
Queries
account_ number Branch_ name balance
A-101 Downtown 500
A-215 Mianus 700
A-102 Perryridge 400

account account account

• Find the largest account balance.

A4 (EXISTS predicate)
t ?u ( account(u) ? ? ? v( account(v) ?
ubalance ? vbalance) ? tbalance
ubalance )
SELECT DISTINCT A1.balanceFROM account A1 WHERE
NOT EXISTS ( SELECT FROM account
A2 WHERE A1.balance lt A2.balance)
70
Test for Absence of Duplicate Tuples

• The unique construct tests whether a subquery has
  any duplicate tuples in its result.
• Example Find all customers who have at most one
  account at the Perryridge branch.
• SELECT DISTINCT D1.customer_name FROM
  depositor AS D1
• WHERE UNIQUE (
• SELECT D2.customer_name FROM account AS
  A, depositor AS D2 WHERE A.account_number
  D2.account_number AND D1.customer_name
  D2.customer_name AND A.branch_name
  Perryridge )

71
Test for Absence of Duplicate Tuples

• Find all customers who have at least two accounts
  at the Perryridge branch.

A1 (using UNIQUE)
SELECT DISTINCT D1.customer_name FROM
depositor AS D1 WHERE NOT UNIQUE (
SELECT D2.customer_name FROM account AS A,
depositor AS D2 WHERE A.account_number
D2.account_number AND D1.customer_name
D2.customer_name AND A.branch_name
Perryridge )
72
Test for Absence of Duplicate Tuples

• Find all customers who have at least two accounts
  at the Perryridge branch.

A2 (using GROUP BY)
SELECT customer_name FROM depositor AS D,
account AS A WHERE D.account_number
A.account_number AND A.branch_name
Perryridge GROUP BY customer_name
HAVING count() gt 2
73
Derived Relations

• SQL allows a subquery expression to be used in
  the FROM clause
• Example Find the average account balance of
  those branches where the average account balance
  is greater than 1200.

SELECT branch_name, avg_balanceFROM (SELECT
branch_name, avg (balance) FROM account
GROUP BY branch_name ) AS branch_avg (
branch_name, avg_balance )WHERE avg_balance gt
1200
74
With Clause

• The with clause provides a way of defining a
  temporary view whose definition is available only
  to the query in which the with clause occurs.
• Find all accounts with the maximum balance
  WITH max_balance (value) as SELECT max
  (balance) FROM account SELECT
  account_number FROM account, max_balance
  WHERE account.balance max_balance.value

75
With Clause

• Find all branches where the total account deposit
  is greater than the average of the total account
  deposits at all branches.

WITH branch_total (branch_name, value) AS
SELECT branch_name, sum (balance) FROM
account GROUP BY branch_name WITH
branch_total_avg (value) AS SELECT avg
(value) FROM branch_total SELECT
branch_name FROM branch_total,
branch_total_avg WHERE branch_total.value gt
branch_total_avg.value
76
Homework 3

• 3.2
• 3.9
• ?????????????!

77
Database Systems
78
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

79
Views

• In some cases, it is not desirable for all users
  to see the entire logical model (that is, all the
  actual relations stored in the database.)
• Consider a person who needs to know a customers
  loan number but has no need to see the loan
  amount. This person should see a relation
  described, in SQL, by
• (select customer_name, loan_number
  from borrower, loan
  where borrower.loan_number loan.loan_number )
• A view provides a mechanism to hide certain data
  from the view of certain users.
• Any relation that is not of the conceptual model
  but is made visible to a user as a virtual
  relation is called a view.

80
Create Views

• A view is defined using the create view statement
  which has the form
• CREATE VIEW v AS lt query expressiongt
• where ltquery expressiongt is any legal SQL
  expression. The view name is represented by v.

81
Create Views

• Once a view is defined, the view name can be used
  to refer to the virtual relation that the view
  generates.
• When a view is created, the definition of the
  view is placed in the database, but no data is
  retrieved or stored.
• A view is a window on the data of the base
  tables, thus queries on views are immediately
  responsive to changes in the underlying base
  table data.

82
Create Views

• A view consisting of branches and their customers

branch (branch_name, branch_city,
assets) customer (customer_name, customer_street,
customer_city) depositor (customer_name,
account_number) account (account_number,
branch_name, balance) borrower (customer_name,
loan_number) loan (loan_number, branch_name,
amount)
83
Create Views

• A view consisting of branches and their customers

CREATE VIEW all_customer AS (SELECT
branch_name, customer_name FROM depositor,
account WHERE depositor.account_number
account.account_number )
UNION (SELECT branch_name, customer_name
FROM borrower, loan WHERE borrower.loan_numbe
r loan.loan_number )

• Find all customers of the Perryridge branch.

SELECT customer_nameFROM all_customerWHERE
branch_name Perryridge
84
Create Views

• If the optional column name list is not
  specified, then the columns of the new view table
  will inherit(??) names of single columns in the
  target list of the Subquery statements. However,
  names must be provided when any view columns
  represent expressions in the target list.
• Example create a view for each branch the sum of
  the amounts of all the loans at the branch.
• CREATE VIEW branch_total_loan(branch_name,
  total_loan)AS SELECT branch_name,
  sum(amount)FROM loanGROUP BY branch_name

85
Create Views

• One view may be used in the expression defining
  another view
• A view relation v1 is said to depend directly on
  a view relation v2 , if v2 is used in the
  expression defining v1
• A view relation v1 is said to depend on view
  relation v2 , if either v1 depends directly to v2
  or there is a path of dependencies from v1 to v2
  
• A view relation v is said to be recursive if it
  depends on itself.(?????)

86
Drop Views

• SyntaxDROP VIEW viewname CASCADERESTRICT

87
Views ANSI/SPARC architecture

• View is the external level of the DBMS
  architecture.

88
ANSI/SPARC architecture
??? ??? ???
??xxxxx ??xxx ??x
???? ???? 75
????? ????? 81

• ???(External Schema,??????????)
• ???????????????????????

• ??(Logical Schema, Schema,??????)
• ????????????????(?)???

Sno Sname Ssex Sage Sdept
Cno Cname Cpno Ccredit
Sno Cno Grade

• ???(Physical Schema,??????)
• ??????????????

89
ANSI/SPARC architecture
CREATE VIEW scores AS (SELECT sno,
sname,ssex,cname,grade FROM S, C, SC
WHERE S.sno SC.sno AND SC.cno C.cno
??? ??? ???
??xxxxx ??xxx ??x
???? ???? 75
????? ????? 81

NOTE A view create the External Schema and also
the map from External Schema to Logical Schema.
S
C
Sno Sname Ssex Sage Sdept
Cno Cname Cpno Ccredit
SC
Sno Cno Grade








90
ANSI/SPARC architecture
CREATE VIEW scores AS (SELECT sno,
sname,ssex,cname,grade FROM S, C, SC
WHERE S.sno SC.sno AND SC.cno C.cno
??? ??? ???
??xxxxx ??xxx ??x
???? ???? 75
????? ????? 81

External/Conceptual mapping
Sno Sname Ssex Sage Sdept
Cno Cname Cpno Ccredit
Sno Cno Grade








Conceptual/Internal mapping

• MAPPING

91
Data Independence

• Two type data independence
• Logical data independence
• the immunity of the external schemas to changes
  in the conceptual schema.
• related to External/Conceptual mapping
• Physical data independence
• the immunity of the conceptual schema to changes
  in the internal schema.
• related to Conceptual/Internal mapping

92
The value of views

• Views provide a way to make complex, commonly
  issued queries easier to compose.(????)
• Views allow obsolete tables, and programs that
  reference them, to survive reorganization.
  (?????)
• Views add a security aspect to allow different
  users to see the same data in different ways.
  (??????????)

93
Unit 3 SQL

• 3.1 Introduction
• 3.2 Setting Up the Database
• 3.3 Queries
• 3.4 View
• 3.5 Data manipulation
• 3.6 Security

94
Insertion

• To insert ONE tuple or a query result into a
  table.
• Syntax

INSERT INTO table_name (col_name1 ,
col_name2) VALUES (expr1 NULL ,
expr2NULL) Subquery

• Add a new tuple to account .
• INSERT INTO account VALUES ( A-9732,
  Perryridge,1200 ) or INSERT INTO account
  (branch_name, balance, account_number)
  VALUES ( Perryridge, 1200, A-9732)

95
Insertion

• Present a new 200 savings account as a gift to
  all loan customers of the Perryridge branch, for
  each loan they have. Let the loan number serve
  as the account number for the new savings account.

branch (branch_name, branch_city,
assets) customer (customer_name, customer_street,
customer_city) depositor (customer_name,
account_number) account (account_number,
branch_name, balance) borrower (customer_name,
loan_number) loan (loan_number, branch_name,
amount)
96
Insertion

• Present a new 200 savings account as a gift to
  all loan customers of the Perryridge branch, for
  each loan they have. Let the loan number serve
  as the account number for the new savings account.

• INSERT INTO account SELECT loan_number,
  branch_name, 200 FROM loan WHERE branch_name
  Perryridge
• INSERT INTO depositor SELECT customer_name,
  loan_number FROM loan, borrower WHERE
  branch_name Perryridge AND loan.
  loan_number borrower. loan_number
• The two SQL statements must be ONE transaction.

97
Updates

• To change a value in a tuple in current table
  without changing all values in the tuple.
• Syntax

UPDATE table_nameSET col_name1 expr NULL
(Subquery1) , col_name2
exprNULL (Subquery2) WHERE
search_condition
98
Updates

• Increase all accounts with balances over 10,000
  by 6, all other accounts receive 5.
• Write two update statements
• UPDATE account SET balance balance ?
  1.06 WHERE balance gt 10000
• UPDATE account SET balance balance ?
  1.05 WHERE balance lt 10000
• The order is important
• Also should be a transaction

99
Updates

• Same query as before Increase all accounts with
  balances over 10,000 by 6, all other accounts
  receive 5.
• UPDATE account SET balance CASE
  WHEN balance lt 10000
  THEN balance 1.05 ELSE
  balance 1.06 END

100
Deletion

• To delete the tuple(s) in the current table.
• Syntax
• Delete all account tuples at the Perryridge
  branch.
• DELETE FROM account WHERE branch_name
  Perryridge
• Delete all accounts at every branch located in
  the city Needham.
• DELETE FROM account WHERE branch_name IN (
• SELECT branch_name FROM branch
  WHERE branch_city Needham )

• DELETE FROM table_name
• WHERE search_conditon

101
Deletion

• Delete the record of all accounts with balances
  below the average at the bank.
• DELETE FROM account WHERE balance lt (SELECT avg
  (balance )
  FROM account )
• Problem as we delete tuples from deposit, the
  average balance changes
• Solution used in SQL
• 1. First, compute avg balance and find all
  tuples to delete
• 2. Next, delete all tuples found above (without
  recomputing avg or retesting the tuples)

102
Update of a view

• A modification to a view must be translated to a
  modification to the actual relations in the
  logical model of the database.
• Example Create a view of all loan data in the
  loan relation, hiding the amount attribute
• CREATE VIEW branch_loan AS SELECT
  branch_name, loan_number FROM loan
• Add a new tuple to branch_loan INSERT INTO
  branch_loan VALUES (Perryridge, L-307)
• This insertion must be represented by the
  insertion of the tuple (L-307, Perryridge,
  null )into the loan relation

103
Update of a view

• Suppose a view downtown_account is defined as
  follow
• CREATE VIEW downtown_account AS SELECT
  account_number, branch_name, balance FROM
  account WHERE branch_name Downtown
• Then the tuple (A-999, Perryridge, 100) can
  be inserted into the downtown_account view, in
  fact, the tuple is inserted into the TABLE
  account, but this is not what we want.
• This problem can be solved at the defination of
  view CREATE VIEW v AS lt query expressiongt
  WITH CHECK OPTIONThe clause WITH CHECK
  OPTION add the views WHERE conditions to the
  modifications of view.

104
Update of a view

• Redefine the view downtown_account as follow
• CREATE VIEW downtown_account AS SELECT
  account_number, branch_name, balance FROM
  account WHERE branch_name Downtown WITH
  CHECK OPTION
• INSERT INTO downtown_account(account_number,
  balance) VALUES (A-999, 100)
• will insert the tuple (A-999, downtown, 100)
  into TABLE account, and insert the tuple
  (A-999, Perryridge, 100) to the view
  downtown_account will be rejected by the DBMS.
• Updates and Deletes are similarly rejected if the
  new value does not satisfy the WHERE clause
  condition.

105
Update of a view

• A view is either updatable or read-only. Insert,
  Update, and Delete operations are permitted for
  updatable view and not permitted for read-only
  views.
• To be an updatable view, the following conditions
  must be all satisfied
• The FROM clause has only one database relation.
• The SELECT clause contains only attribute names
  of the relation, and does not have any
  expressions, aggregates, or DISTINCT
  specification.
• Any attribute not listed in the SELECT clause can
  be set to NULL.
• The query does not have a GROUP BY or HAVING
  clause.

106
Joined Relations

• Join operations take two relations and return as
  a result another relation.
• These additional operations are typically used as
  subquery expressions in the from clause
• Join condition defines which tuples in the two
  relations match, and what attributes are present
  in the result of the join.
• Join type defines how tuples in each relation
  that do not match any tuple in the other relation
  (based on the join condition) are treated.

107
Joined Relations
loan_number branch_name amount
L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700
loan loan loan
customer_name loan_number
Jones L-170
Smith L-230
Hayes L-155
borrower borrower

• loan INNER JOIN borrower ONloan.loan_number
  borrower.loan_number

• loan NATURAL INNER JOIN borrower

loan_number branch_Name amount customer_name
L-170 Downtown 3000 Jones
L-230 Redwood 4000 Smith
108
Joined Relations
loan_number branch_name amount
L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700
loan loan loan
customer_name loan_number
Jones L-170
Smith L-230
Hayes L-155
borrower borrower

• loan LEFT OUTER JOIN borrower onloan.loan_number
  borrower.loan_number

109
Joined Relations
loan_number branch_name amount
L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700
loan loan loan
customer_name loan_number
Jones L-170
Smith L-230
Hayes L-155
borrower borrower

• loan natural right outer join borrower

110
Joined Relations
loan_number branch_name amount
L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700
loan loan loan
customer_name loan_number
Jones L-170
Smith L-230
Hayes L-155
borrower borrower

• loan full outer join borrower using (loan_number)

111
Joined Relations

• Find all customers who have either an account or
  a loan (but not both) at the bank.
• SELECT customer_nameFROM (depositor natural
  full outer join borrower )WHERE account_number
  IS NULL OR loan_number IS NULL

112
Homework 4

• 3.15
• 3.19