DBMS

1
DBMS

• Relational algebra
• DDL

2
Relational Operations

• Commutative
• R1 n R2 ? R2 n R1
• Associative
• (R1 n R2) n R3 ? R1 n (R2 n R3)
• Distributive
• R1 U (R2 n R3) ? (R1 U R2) n (R1 U R3)
• R1 n (R2 U R3) ? (R1 n R2) U (R1 n R3)
• Can be applied to Union, Intersection,
  Cross-Product, Joins
• Not applicable to Set Difference or Division
• Select commutes with Project only if Select
  attributes are in Project relation

3
Relational Operations

• Transformations
• Double negation
• (s(S)) ? s(S)
• Nesting cascade
• sc2(sc1(S)) ? sc1(S) ? sc2(S)
• More equivalencies
• pa(sc (S)) ? sc(pa (S)) if c ? a
• R ?c S ? sc(R X S)
• sc(R X S) ? sc(R) X S if c attributes ? R
• sc(R ? S) ? sc(R) ? S if c attributes ? R
• sc1(sc2(sc3(R X S))) ? sc1(sc2(R) X sc3(S))

4
Structured Query Language (SQL)

• 1986 ANSIs first attempt
• 1989 slightly modified
• 1992 still the standard
• 1999 becoming the standard

5
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

6
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

7
Integrity controls

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

8
DDL

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

9
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

10
ALTER

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

11
DROP

• DROP TABLE TableName

12
INDEX

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

13
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