Database Management Systems

1
Database Management Systems

• SQL

2
History of the SQL language

• 1974-75 IBM SEQUEL (Structured English QUEry
  Languge)
• ANSI Standard since 1987
• Standard, supported by several DBMS manufacturer
• Non- procedural language
• The fundamental question is what is the
  interesting data but not how it is possible to
  find the answer.

3
Part of the SQL language

• Data Definition Language
• Creation/ Deletion/ Modification of the database
  schema
• Executing irrespectively of the transactions
• Data Manipulation Language
• Insert Creating new records
• Update Modifying existing records
• Delete Removing existing records
• Select Querying
• Data Control Language
• Defining user access rights to the database
  schema elements

4
Defining a Database Schema

• A database schema comprises declarations for the
  relations (tables) of the database.
• Many other kinds of elements may also appear in
  the database schema, including views, indexes,
  and triggers, which we will introduce later.

5
Data types

• Oracle
• Number(p,s)
• Varchar(n)
• Varchar2(n)
• Char(n)
• Date
• BLOB
• LONG
• ROWID

• SQL Server
• Int
• float
• Char
• Varchar
• Money
• Datetime
• Binary

6
Declaring a Relation

• Simplest form is
• CREATE TABLE ltnamegt (
• ltlist of elementsgt
• )
• And you may remove a relation from the database
  schema by
• DROP TABLE ltnamegt

7
Example Create Table

• CREATE TABLE Sells (
• bar CHAR(20),
• beer VARCHAR(20),
• price REAL
• )

8
Declaring Keys

• An attribute or list of attributes may be
  declared PRIMARY KEY or UNIQUE.
• These each say the attribute(s) so declared
  functionally determine all the attributes of the
  relation schema.

9
Declaring Single-Attribute Keys

• Place PRIMARY KEY or UNIQUE after the type in the
  declaration of the attribute.
• Example
• CREATE TABLE Beers (
• name CHAR(20) UNIQUE,
• manf CHAR(20)
• )

10
Declaring Multiattribute Keys

• A key declaration can also be another element in
  the list of elements of a CREATE TABLE statement.
• This form is essential if the key consists of
  more than one attribute.
• May be used even for one-attribute keys.

11
Example Multiattribute Key

• The bar and beer together are the key for Sells
• CREATE TABLE Sells (
• bar CHAR(20),
• beer VARCHAR(20),
• price REAL,
• PRIMARY KEY (bar, beer)
• )

12
PRIMARY KEY Versus UNIQUE

• The SQL standard allows DBMS implementers to make
  their own distinctions between PRIMARY KEY and
  UNIQUE.
• Example some DBMS might automatically create an
  index (data structure to speed search) in
  response to PRIMARY KEY, but not UNIQUE.

13
Required Distinctions

• However, standard SQL requires these
  distinctions
• There can be only one PRIMARY KEY for a relation,
  but several UNIQUE attributes.
• No attribute of a PRIMARY KEY can ever be NULL in
  any records. But attributes declared UNIQUE may
  have NULLs, and there may be several records
  with NULL.

14
Other Declarations for Attributes

• Two other declarations we can make for an
  attribute are
• NOT NULL means that the value for this attribute
  may never be NULL.
• DEFAULT ltvaluegt says that if there is no specific
  value known for this attributes component in
  some records, use the stated ltvaluegt.

15
Example Default Values

• CREATE TABLE Drinkers (
• name CHAR(30) PRIMARY KEY,
• addr CHAR(50)
• DEFAULT 123 Sesame St.,
• phone CHAR(16)
• )

16
Effect of Defaults 1

• Suppose we insert the fact that Sally is a
  drinker, but we know neither her address nor her
  phone.
• An INSERT with a partial list of attributes makes
  the insertion possible
• INSERT INTO Drinkers(name)
• VALUES(Sally)

17
Effect of Defaults 2

• But what record appears in Drinkers?
• name addr phone
• Sally 123 Sesame St NULL
• If we had declared phone NOT NULL, this insertion
  would have been rejected.

18
Constraints

• Primary key
• CONSTRAINT name PRIMARY KEY (columns)
• Unique key
• CONSTRAINT name UNIQUE (columns)
• Check
• CONSTRAINT name CHECK (columns)
• Foreign key
• CONSTRAINT name FOREIGN KEY (columns) REFERENCES
  TableName ON (columns) ON DELETE CASCADE

19
General schema of create table

• Create Table name(
• ColumnName type DEFAULT default value
• NULLNOT NULL column_constraints,
• ,
• TableConstraints
• )

20
Adding Attributes

• We may change a relation schema by adding a new
  attribute (column) by
• ALTER TABLE ltnamegt ADD
• ltattribute declarationgt
• Example
• ALTER TABLE Bars ADD
• phone CHAR(16)DEFAULT unlisted

21
Deleting Attributes

• Remove an attribute from a relation schema by
• ALTER TABLE ltnamegt
• DROP ltattributegt
• Example we dont really need the license
  attribute for bars
• ALTER TABLE Bars DROP license

22
Deleting schema object

• Drop table TableName
• Remove table from the schema
• Truncate table TableName
• Oracle specific
• Remove all data from the table
• DDL expression !!!

23
Data Manipulation Language

• Insert
• Update
• Delete
• Select

24
Insert

• Insert into TableName (c1,)
• Values(v1,)
• Insert into TableName(c1,)
• Select

25
Insert

• INSERT INTO dept      
• VALUES (50, 'PRODUCTION', 'SAN FRANCISCO')
• INSERT INTO emp (empno, ename, job, sal, comm,
  deptno)    
• VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40)
• INSERT INTO bonus    
• SELECT ename, job, sal, comm    
• FROM emp WHERE job'PRESIDENT'

26
Delete

• DELETE
• FROM TableName
• WHERE Conditions

27
Delete

• DELETE FROM emp
• DELETE FROM emp    
• WHERE JOB 'SALESMAN'

28
Update

• UPDATE TableName
• SET c1v1,
• c2v2,
• WHERE Condition

29
Update

• UPDATE emp SET deptno 1356
• UPDATE emp
• SET deptno 1500   
• WHERE name 'JONES'
• UPDATE emp     
• SET job 'MANAGER',
• sal sal 1000,
• deptno 20     
• WHERE ename 'JONES'

30
Simple query

• SELECT c1 AS alias1,
• FROM t1 alias1,t2 alias2
• WHERE condition

31
Select-From-Where Statements

• The principal form of a query is
• SELECT desired attributes
• FROM one or more tables
• WHERE condition about records of
• the tables

32
Our Running Example

• All our SQL queries will be based on the
  following database schema.
• Underline indicates key attributes.
• Beers(name, manf)
• Bars(name, addr, license)
• Drinkers(name, addr, phone)
• Likes(drinker, beer)
• Sells(bar, beer, price)
• Frequents(drinker, bar)

33
Example

• Using Beers(name, manf), what beers are made by
  Anheuser-Busch?
• SELECT name
• FROM Beers
• WHERE manf Anheuser-Busch

34
Result of Query

• name
• Bud
• Bud Lite
• Michelob

The answer is a relation with a single
attribute, name, and records with the name of
each beer by Anheuser-Busch, such as Bud.
35
Meaning of Single-Relation Query

• Begin with the relation in the FROM clause.
• Apply the selection indicated by the WHERE
  clause.
• Apply the extended projection indicated by the
  SELECT clause.

36
In SELECT clauses

• When there is one relation in the FROM clause,
  in the SELECT clause stands for all attributes
  of this relation.
• Example using Beers(name, manf)
• SELECT
• FROM Beers
• WHERE manf Anheuser-Busch

37
Result of Query

• name manf
• Bud Anheuser-Busch
• Bud Lite Anheuser-Busch
• Michelob Anheuser-Busch

Now, the result has each of the attributes of
Beers.
38
Renaming Attributes

• If you want the result to have different
  attribute names, use AS ltnew namegt to rename an
  attribute.
• Example based on Beers(name, manf)
• SELECT name AS beer, manf
• FROM Beers
• WHERE manf Anheuser-Busch

39
Result of Query

• beer manf
• Bud Anheuser-Busch
• Bud Lite Anheuser-Busch
• Michelob Anheuser-Busch

40
Expressions in SELECT Clauses

• Any expression that makes sense can appear as an
  element of a SELECT clause.
• Example from Sells(bar, beer, price)
• SELECT bar, beer,
• price 120 AS priceInYen
• FROM Sells

41
Result of Query

• bar beer priceInYen
• Joes Bud 300
• Sues Miller 360

42
Another Example Constant Expressions

• From Likes(drinker, beer)
• SELECT drinker,
• likes Bud AS whoLikesBud
• FROM Likes
• WHERE beer Bud

43
Result of Query

• drinker whoLikesBud
• Sally likes Bud
• Fred likes Bud

44
Complex Conditions in WHERE Clause

• From Sells(bar, beer, price), find the price
  Joes Bar charges for Bud
• SELECT price
• FROM Sells
• WHERE bar Joes Bar AND
• beer Bud

45
Important Points

• Two single quotes inside a string represent the
  single-quote (apostrophe).
• Conditions in the WHERE clause can use AND, OR,
  NOT, and parentheses in the usual way boolean
  conditions are built.
• SQL is case-insensitive. In general, upper and
  lower case characters are the same, except inside
  quoted strings.

46
Patterns

• WHERE clauses can have conditions in which a
  string is compared with a pattern, to see if it
  matches.
• General form ltAttributegt
  LIKE ltpatterngt or ltAttributegt NOT LIKE ltpatterngt
• Pattern is a quoted string with any string
  _ any character.

47
Example

• From Drinkers(name, addr, phone) find the
  drinkers with name Steve
• SELECT name
• FROM Drinkers
• WHERE phone LIKE Steve

48
NULL Values

• Records in SQL relations can have NULL as a value
  for one or more components.
• Meaning depends on context. Two common cases
• Missing value e.g., we know Joes Bar has some
  address, but we dont know what it is.
• Inapplicable e.g., the value of attribute
  spouse for an unmarried person.

49
Comparing NULLs to Values

• The logic of conditions in SQL is really 3-valued
  logic TRUE, FALSE, UNKNOWN.
• When any value is compared with NULL, the truth
  value is UNKNOWN.
• But a query only produces a record in the answer
  if its truth value for the WHERE clause is TRUE
  (not FALSE or UNKNOWN).
• Using Is NULL / Is Not NULL

50
Logical AND Expression
51
Logical OR Expression
52
Multirelation Queries

• Interesting queries often combine data from more
  than one relation.
• We can address several relations in one query by
  listing them all in the FROM clause.
• Distinguish attributes of the same name by
  ltrelationgt.ltattributegt

53
Example

• Using relations Likes(drinker, beer) and
  Frequents(drinker, bar), find the beers liked by
  at least one person who frequents Joes Bar.
• SELECT beer
• FROM Likes, Frequents
• WHERE bar Joes Bar AND
• Frequents.drinker Likes.drinker

54
Formal Semantics

• Almost the same as for single-relation queries
• Start with the product of all the relations in
  the FROM clause.
• Apply the selection condition from the WHERE
  clause.
• Project onto the list of attributes and
  expressions in the SELECT clause.

55
Operational Semantics

• Imagine one record-variable for each relation in
  the FROM clause.
• These record-variables visit each combination of
  records, one from each relation.
• If the record-variables are pointing to records
  that satisfy the WHERE clause, send these records
  to the SELECT clause.

56
Example
drinker bar drinker
beer tv1 tv2 Sally Bud Sally
Joes Likes Frequents
57
Join operation

• Natural join
• Use equivalence operator
• Outer Join
• Use equivalence operator
• Use () where the null value is acceptable in the
  join expression

58
Outer Join

• List each of the drinkers and its favourite bar
• Select From drinkers, frequents Where
  namedrinker
• Select From drinkers, frequents Where
  namedrinker()

59
Using Inner Join SQL Server
USE joindb SELECT buyer_name, sales.buyer_id,
qty FROM buyers INNER JOIN sales ON
buyers.buyer_id sales.buyer_id
60
Using Outer Join SQL Server
61
Using Cross Join SQL Server
USE joindb SELECT buyer_name, qty FROM buyers
CROSS JOIN sales
62
Multiple table joinSQL Server
63
Explicit Record-Variables

• Sometimes, a query needs to use two copies of the
  same relation.
• Distinguish copies by following the relation name
  by the name of a record-variable, in the FROM
  clause.
• Its always an option to rename relations this
  way, even when not essential.

64
Example

• From Beers(name, manf), find all pairs of beers
  by the same manufacturer.
• Do not produce pairs like (Bud, Bud).
• Produce pairs in alphabetic order, e.g. (Bud,
  Miller), not (Miller, Bud).
• SELECT b1.name, b2.name
• FROM Beers b1, Beers b2
• WHERE b1.manf b2.manf AND
• b1.name lt b2.name

65
Join a table to itselfSQL Server
USE joindb SELECT a.buyer_id AS buyer1,
a.prod_id, b.buyer_id AS buyer2 FROM sales
a JOIN sales b ON a.prod_id b.prod_id WHERE
a.buyer_id gt b.buyer_id
66
Subqueries

• A parenthesized SELECT-FROM-WHERE statement
  (subquery) can be used as a value in a number of
  places, including FROM and WHERE clauses.
• Example in place of a relation in the FROM
  clause, we can place another query, and then
  query its result.
• Better use a record-variable to name records of
  the result.

67
Subqueries That Return One record

• If a subquery is guaranteed to produce one
  record, then the subquery can be used as a value.
• Usually, the record has one component.
• Also typically, a single record is guaranteed by
  keyness of attributes.
• A run-time error occurs if there is no record or
  more than one record.

68
Example

• From Sells(bar, beer, price), find the bars that
  serve Miller for the same price Joe charges for
  Bud.
• Two queries would surely work
• Find the price Joe charges for Bud.
• Find the bars that serve Miller at that price.

69
Query Subquery Solution

• SELECT bar
• FROM Sells
• WHERE beer Miller AND
• price (SELECT price
• FROM Sells
• WHERE bar Joes Bar
• AND beer Bud)

70
The IN Operator

• ltrecordgt IN ltrelationgt is true if and only if the
  record is a member of the relation.
• ltrecordgt NOT IN ltrelationgt means the opposite.
• IN-expressions can appear in WHERE clauses.
• The ltrelationgt is often a subquery.

71
Example

• From Beers(name, manf) and Likes(drinker, beer),
  find the name and manufacturer of each beer that
  Fred likes.
• SELECT
• FROM Beers
• WHERE name IN (SELECT beer
• FROM Likes
• WHERE drinker Fred)

72
The Exists Operator

• EXISTS( ltrelationgt ) is true if and only if the
  ltrelationgt is not empty.
• Being a boolean-valued operator, EXISTS can
  appear in WHERE clauses.
• Example From Beers(name, manf), find those beers
  that are the unique beer by their manufacturer.

73
Example Query with EXISTS

• SELECT name
• FROM Beers b1
• WHERE NOT EXISTS(
• SELECT
• FROM Beers
• WHERE manf b1.manf AND
• name ltgt b1.name)

74
The Operator ANY

• x ANY( ltrelationgt ) is a boolean condition
  meaning that x equals at least one record in the
  relation.
• Similarly, can be replaced by any of the
  comparison operators.
• Example x gt ANY( ltrelationgt ) means x is not
  smaller than all records in the relation.
• Note records must have one component only.

75
The Operator ALL

• Similarly, x ltgt ALL( ltrelationgt ) is true if and
  only if for every record t in the relation, x is
  not equal to t.
• That is, x is not a member of the relation.
• The ltgt can be replaced by any comparison
  operator.
• Example x gt ALL( ltrelationgt ) means there is no
  record larger than x in the relation.

76
Example

• From Sells(bar, beer, price), find the beer(s)
  sold for the highest price.
• SELECT beer
• FROM Sells
• WHERE price gt ALL(
• SELECT price
• FROM Sells)

77
Filtering duplicate records

• SELECT DISTINCT city, state
• FROM authors
• SELECT city, state
• FROM authors
• GROUP BY city, state

78
Ordering the result

• SELECT city, state
• FROM authors
• ORDER BY state, city DESC
• SELECT city, state
• FROM authors
• ORDER BY 1, 2 DESC

79
Set manipulation

• Union compatible queries
• Same number of columns
• Equivalent data types in the corresponding
  columns
• Possible Expressions
• UNION
• UNION ALL
• MINUS
• INTERSECT

80
Set manipulation

• SELECT firstname ' lastname, city,
  postalcode
• FROM employees
• UNION
• SELECT companyname, city, postalcode
• FROM customers

81
Aggregate functions

• They can be applied to a column in a SELECT
  clause to produce that aggregation on the column.
• AVG
• COUNT, DISTINCT COUNT
• MIN
• MAX
• SUM
• Handling of the NULL
• During the calculation they skip the records if
  the corresponding column is null
• COUNT() expression count the records that
  contains NULL value

82
Aggregate functions

• Select avg(price)
• From sells

83
Using GROUP BY
84
Using GROUP BY and HAVING
SELECT bar, AVG(Price) AS Average FROM
sells GROUP BY bar
SELECT FROM sells
bar
beer
price
bar
Average
Joes
Bud
5
Joes
7.5
Joes
Kaiser
10
Johns
17.5
Johns
Bud
10
Franks
22.5
Johns
Kaiser
25
bar
Average
Franks
Bud
15
Joes
7.5
Franks
Kaiser
30
SELECT bar, AVG(Price) AS Average FROM
sells GROUP BY bar Having AVG(Price)lt10
Calculate aggregatefunctions then apply having