Database management systems

1
Database management systems

• Data Definition Language
• Relational Calculus
• Query By Example

2
ISO SQL Identifiers

• Object identifier restrictions
• ISO default character set (A..Z, a..z, 0..9, _)
  is common
• 128 character maximum
• Must start with a letter
• Contains no spaces
• Applies to tables, views and columns

3
ISO SQL Data Types

• String, Numeric and Large Object
• Boolean True, False, Unknown (NULL)
• Character ASCII, EBCDIC, Unicode, etc. can be
  fixed or varying
• Bit 0 or 1
• Exact numeric Digits, an optional decimal
  point, and and optional sign has both precision
  and scale
• Approximate numeric real numbers
• Datetime the most difficult to implement and
  port typically uses universal coordinated time
  (UCT)
• Interval periods of time typically either
  year-month or day-time
• Large objects (BLOB, TLOB) post SQL92
• Scalar operators and functions to create an
  expression

4
Integrity controls

• Required data - NOT NULL
• Domain constraints CHECK()
• Entity integrity Primary key
• Referential integrity Foreign key
• Enterprise constraints CREATE ASSERTION

5
DDL

• CREATE
• SCHEMA
• DOMAIN
• TABLE
• VIEW
• INDEX
• ALTER
• DOMAIN
• TABLE
• DROP
• SCHEMA
• DOMAIN
• TABLE
• VIEW
• INDEX

6
CREATE

• CREATE TABLE TableName (
• ColumnName1 DataType NOT NULL,
• ColumnName2 DataType,
• ColumnName3 DataType DEFAULT Yes,
• ColumnName4 DataType CHECK (VALUE IN (SELECT
  col003 FROM table2)),
• PRIMARY KEY (ColumnName1,ColumnName2),
• FOREIGN KEY (ColumnName2) REFERENCES Table5
  (col006) ON DELETE NO ACTION ON UPDATE CASCADE

7
ALTER

• ALTER TABLE TableName ADD COLUMN (ColumnName
  DataType)
• ALTER TABLE TableName ALTER COLUMN (ColumnName
  DataType)
• ALTER TABLE TableName DROP COLUMN ColumnName

8
DROP

• DROP TABLE TableName

9
INDEX

• CREATE UNIQUE INDEX IndexName ON TableName
  (ColumnName1, ColumnName2) DESC
• DROP INDEX IndexName

10
Views

• CREATE VIEW ViewName AS
• SELECT col001, col005 FROM TableName WHERE
  col007 value
• DROP VIEW ViewName
• Views are updatable only if
• DISTINCT, GROUP BY, multiple tables and nested
  tables referenced in the FROM clause are not
  included
• Every attribute is the sole pointer to only a
  single column in the base table
• The integrity constraints of the base table are
  not violated
• View materialization may improve query speed, but
  delays updates

11
Relational Calculus

• High-level
• Declarative non-procedural
• From a branch of symbolic logic known as
  predicate calculus
• A predicate is a truth-valued function with
  arguments
• Replace arguments with values to obtain a
  proposition
• Determine if proposition is true or false
• Declares what is to be retrieved, not how to
  retrieve it
• Requires a well-formed formula
• Identical expressive power to relational algebra
  and vice versa

12
Relational Calculus

• T Cond(T)
• Cond is the predicate multiple predicates are
  connected using AND (/\), OR(\/) and NOT( or )
• T is the set of values such that Cond is true
• Comes in two forms
• Tuple Relational Calculus - variables range over
  tuples in a relation (tuple variables)
• Domain Relational Calculus variables range over
  domain elements

13
Relational Calculus

• Tuple Relational Calculus ltx1,x2,,xngtp(ltx1,x2,
  xmgt) m gt n
• Generalization
• Find the set of all tuples S such that F(S) is
  true SF(S)
• F is a wff (well-formed formula)
• Can be thought of as
• What is retrieved predicate to be satisfied
• With free variables on the left (within the
  domain of the expression) and bound variables on
  the right
• Example
• Single relation List the names of all managers
  who earn more than 25,000.
• S.Name Staff(S) /\ S.position manager /\
  Salary gt 25000
• Multiple relations List names of staff who
  manage properties for rent in Glasgow
• S.Name Staff(S) /\ ?(P)(PropertyForRent(P) /\
  (P.staffno S.staffno) /\ P.city Glasgow)
• Compare with relational algebra
• List all cities where there is a branch office
  but no properties for rent
• TRC B.city Branch(B) /\ ((?P)(PropertyForRen
  t(P) /\ B.city P.city))
• RA ?city (Branch) - ?city (PropertyForRent)

14
Relational Calculus

• Domain relational calculus d1,d2,dn
  F(d1,d2,dm) mgtn
• Variables take their values from domains of
  attributes
• Predicate requires finding a value in each
  domain that satisfies the proposition
• Example Find the names of managers who earn
  more than 25000
• N (?N,pos,sal)(Staff(N,pos,sal) /\
  posmanager /\ sal gt 25000
• Unlike TRC, DRC allows a formula for the UNION
  operation
• Example
• List all cities where there is either a branch
  office or a property for rent
• DRC city(Branch(bN,st,city,pc)\/(PropertyForRe
  nt(pN,st1,city,pc1,rms))
• RA ?city (Branch) ? ?city (PropertyForRent)

15
Relational Calculus

• Find the names and ages of all students with a
  gpa above 3
• TRC
• P ?S?Students(S.gpagt3?P.nameS.name ?
  P.ageS.age)
• DRC
• ltN,AgtltI,N,T,Agt ?Students ? T gt 3
• Binding variables using quantifiers
• ? - there is at least one value
• ? - all values

16
Relational Completeness
BranchID LoanNumber Loan Value 1 1 1000 1
2 3000 2 1 1200 3 1 1500

• Consider the following
• Bank Branches give loans
• Each loan has a loan number
• Each loan has a value
• Show the branches and loan numbers with a loan
  value greater than 1200
• DRCltl,b,agt ltl,b,agt ? loan ? a gt 1200
• TRCt t ? loan ? a gt 1200
• RA sagt1200Loans
• Show the loan number for any loans values greater
  than 1200
• DRCltlgt ?b,a (ltl,b,agt ? loan ? a gt 1200
• TRCt ?s ? loan(t.l s.l ? s.a gt 1200
• RA pl(sagt1200Loans)

17
Relational Calculus

• The basis for Query By Example (QBE)
• A graphical interface where the user enters
  values for what is wanted
• Originally develop by IBM in the 1970s
• Now in almost all DBMSs
• Example
• Show the name of all customers less than 18
  years old who reserved a tape on 10/02/02
• Can use 2 tables