Database Management

1
Database Management Fall 2003 SQL Chapter 10
2
SQL

• Structured Query Language
• Declaritive language vs. procedural
• Three types of SQL statements
• Data Manipulation Language (DML)
• SELECT, INSERT, UPDATE, DELETE
• Data Definition Language (DDL)
• CREATE/ALTER/DROP TABLE, VIEW, INDEX
• Data Control Language (DCL)
• GRANT, REVOKE

3
Data Manipulation Commands (DML)

• SELECT
• Retrieve data from the database
• INSERT
• Add new rows to the database
• UPDATE
• Modify data in the database
• DELETE
• Remove rows from the database

4
SELECT

• We are already familiar with this command
• SELECT col1, col2, FROM table1, table2,
  WHERE search_condition
• AND search_condition
• OR search_condition GROUP BY
  group_by_expression HAVING search_condition
  ORDER BY order_expression ASC DESC

5

• INSERT
• INSERT INTO table (col1, col2,) VALUES (val1,
  val2, )
• UPDATE
• UPDATE table SET col1 val1, col2 val2,
• WHERE condition
• DELETE
• DELETE FROM table WHERE condition

6
INSERT

• Enforces integrity constraints
• Two styles with or without column names
• Style 1 insert values in order of columns
• as defined by CREATE TABLE see object browser.
• All columns must be included or NULL
• INSERT INTO item VALUES (21, 'Camoflage Pants',
  'C', 'Khaki')
• Style 2 indicate columns explicitly.
• (Not all columns need appear, but must match up
  with values)
• INSERT INTO item (itemno, itemname, itemtype)
• VALUES (22, 'Hunting Vest', 'C')

7
INSERT-SELECT

• Special case inserts rows from another table
• CREATE TABLE new_item
• (itemno INT not null,
• itemname CHAR(30) null,
• itemtype CHAR(1) null,
• itemcolor CHAR(10) null)
• INSERT INTO new_item VALUES (23, 'Climbing Rope',
  NULL, 'White')
• INSERT INTO item SELECT FROM new_item
• INSERT INTO item (itemno, itemname, itemcolor)
• SELECT itemno, itemname, itemcolor FROM new_item
• WHERE itemcolor White

8
UPDATE

• Modify data in a table
• Enforces integrity, primary key constraints
• UPDATE locks rows during update
• Without a WHERE clause, updates ALL rows!
• Give all employees a 10 raise
• UPDATE emp SET empsalary 1.10(empsalary)
• Move all employees in Personnel to Marketing
• UPDATE emp SET deptname 'Marketing'
• WHERE deptname 'Personnel'

9
DELETE

• Remove entire rows from table
• Again, without WHERE clause, deletes ALL rows!
• Enforces integrity constraints
• DELETE FROM new_item
• DELETE FROM item WHERE itemno 23

10
Three options for deleting a table

• DELETE FROM emp
• Safest Logs the delete transactions in a log
  file for undo
• Does not delete the table
• Does not reclaim space
• TRUNCATE TABLE emp
• Faster than delete, does not log transactions
• Does not delete the table
• Reclaims space
• DROP TABLE emp
• Fast, does not log deletions
• Deletes the table as well as the rows
• Reclaims all space

11
Data Definition Language (DDL)

• CREATE
• TABLE
• VIEW
• INDEX
• TRIGGER, FUNCTION, PROCEDURE
• ALTER
• TABLE
• VIEW
• TRIGGER, FUNCTION, PROCEDURE
• DROP
• TABLE
• VIEW
• INDEX
• TRIGGER, FUNCTION, PROCEDURE

12
CREATE TABLE

• Creates a base table
• CREATE TABLE table_name
• (col1_name col1_type DEFAULT constraints,
• col2_name col2_type DEFAULT constraints,
• ,
• PRIMARY KEY (pk_col1, pk_col2, ),
• FOREIGN KEY (fk_col1, fk_col2, )
• REFERENCES ref_table (ref_col1, ref_col2,),
• CHECK (check conditions))

13
Creating tables example

• CREATE TABLE nation (
• natcode CHAR(3),
• natname VARCHAR(20),
• exchrate DECIMAL(9,5),
• PRIMARY KEY (natcode))
• CREATE TABLE stock (
• stkcode CHAR(3),
• stkfirm VARCHAR(20),
• stkprice DECIMAL(6,2),
• stkqty DECIMAL(8),
• stkdiv DECIMAL(5,2),
• stkpe DECIMAL(5),
• natcode CHAR(3),
• PRIMARY KEY(stkcode),
• CONSTRAINT fk_stock_nation FOREIGN KEY(natcode)
  
• REFERENCES nation)

14
CREATE TABLE Example 2

• CREATE TABLE stock
• (stkcode char(3) NOT NULL,
• stkfirm char(20),
• stkprice decimal(6,2) DEFAULT 0,
• stkqty decimal(8) CHECK (stkqty gt 0),
• stkdiv decimal(5,2),
• stkpe decimal(5),
• natcode char(3),
• PRIMARY KEY (stkcode),
• FOREIGN KEY (natcode)
• REFERENCES nation(natcode))

15
CREATE TABLE Example 3

• CREATE TABLE stock
• (stkcode char(3) PRIMARY KEY,
• stkfirm char(20),
• stkprice decimal(6,2),
• stkqty decimal(8) CHECK (stkqty gt 0),
• stkdiv decimal(5,2),
• stkpe decimal(5),
• natcode char(3) FOREIGN KEY REFERENCES
  nation(natcode))
• Constraints can appear in the column
• definitions

16
Constraints

• Primary key
• CONSTRAINT pk_stock PRIMARY KEY(stkcode)
• Foreign key
• CONSTRAINT fk_stock_nation
• FOREIGN KEY(natcode) REFERENCES nation
• Unique
• CONSTRAINT unq_stock_stkname UNIQUE(stkname)
• Check
• CONSTRAINT item_color_cons
• CHECK (itemcolor IN ('White', 'Brown', 'Khaki'))

17
CHECK Constraints

• The set of all possible (and meaningful!)
• values that an attribute can take on.
• Age gt
• State code one of AK, AL, WY
• ShipDate gt OrderDate

18
CHECK Constraints

• MS SQLServer
• A column can have any number of CHECK constraints
• The condition can include multiple logical
  expressions combined with AND and OR.
• Multiple CHECK constraints for a column are
  validated in the order created.
• The search condition must evaluate to a Boolean
  expression and cannot reference another table.

19
Creating a CHECK constraint

• CREATE TABLE item
• (itemno INT NOT NULL,
• itemname CHAR(30) NULL,
• itemtype CHAR(1) NULL,
• itemcolor CHAR(10) NULL
• CONSTRAINT chk_item_color
• CHECK (itemcolor IN ('White', 'Brown',
  'Khaki')))
• INSERT INTO item VALUES (24, 'Climbing Rope',
  'N', 'Yellow')
• Server Msg 547, Level 16, State 1, Line 1
• INSERT statement conflicted with COLUMN CHECK
  constraint 'chk_item_color'. The conflict
  occurred in database 'markf', table 'item',
  column 'itemcolor'.
• The statement has been terminated.

20
DROP TABLE

• DROP Deletes the table and all its rows
• Cannot be undone!
• DROP TABLE table_name

21
ALTER TABLE

• Add or drop a column or constraint
• Change a column type
• ALTER TABLE table_name
• ALTER COLUMN column_name
• DROP/ADD column or constraint
• ALTER TABLE stock_tmp
• ALTER COLUMN stkfirm char(30)
• ALTER TABLE stock_tmp
• ADD stkyield decimal(4,2)

22
Altering Columns some rules

• Cant alter a primary key or a column referenced
  by a foreign key or index.
• Data type changes
• have to be compatible
• cant be smaller
• cant change to NOT NULL if there are already
  values
• Cant drop a column if it is part of a constraint

23
Views - virtual tables

• An imaginary table constructed by the DBMS when
  required not a base table.
• Only the definition of the view is stored, not
  the result
• Usually cant be updated except in special
  circumstances
• CREATE VIEW view_name
• (col1, col2, )
• AS select statement

24
Create View example

• CREATE VIEW stkvalue
• (nation, firm, price, qty, exchrate, value)
• AS SELECT natname, stkfirm, stkprice, stkqty,
  exchrate,
• stkpricestkqtyexchrate
• FROM stock, nation
• WHERE stock.natcode nation.natcode

25
DROP/ALTER VIEW

• DROP VIEW deletes a view
• ALTER VIEW
• Only replaces the SELECT statement
• Usually DROP VIEW, then CREATE VIEW are used
  instead

26
CREATE INDEX

• What is an index?
• A database object associated with a table
• A sorted list for fast lookup of rows (remember,
  RDBMS rows are not ordered)
• Similar to the index in a textbook
• CREATE INDEX index_name (col1, col2,)
• ON table or view
• CREATE INDEX color_idx (itemcolor) ON item

27
Indexing

• An index has data for one or more columns in a
  table and pointers to the locations of the rows
  with those column values.
• Indexes reduce disk accesses and improve SELECT
  performance
• Indexes slow down INSERT and UPDATE due to
  maintenance overhead

28
UNIQUE INDEX

• Special index that enforces uniqueness on the
  column(s).
• The database automatically creates an unique
  index for every primary key.
• Unique constraint creates a unique index.
• CREATE UNIQUE INDEX index_name
• (col1, col2,)
• ON table or view

29
B-tree index

• Sequence set is a single level index with
  pointers to records
• Index set is a tree-structured index to the
  sequence set

30
DROP/ALTER INDEX

• DROP INDEX
• Can you guess?
• There is no ALTER INDEX command simply drop and
  recreate.

31
Security

• Data Control Language (DCL)
• Data is a valuable resource
• Access should be controlled
• SQL security procedures
• CREATE VIEW
• Authorization commands

32
Authorization

• Based on privilege concept
• You cannot execute an operation without the
  appropriate privilege
• DBA has all privileges

33
GRANT

• Defines a users privileges
• GRANT privileges ON object TO users
• WITH GRANT OPTION
• An object is a base table or view
• The keyword privilege can be ALL PRIVILEGES or
  chosen from
• SELECT
• UPDATE
• DELETE
• INSERT
• Privileges can be granted to everybody using the
  keyword PUBLIC or to selected users by specifying
  their user identifier

34
GRANT

• The UPDATE privilege can specify particular
  columns in a base table or view
• WITH GRANT OPTION
• Permits a user to pass privileges to another user

35
Using GRANT

• Give Alice all rights to the STOCK table.
• GRANT ALL PRIVILEGES ON stock TO alice
• Permit the accounting staff, Todd and Nancy, to
  update the price of a stock.
• GRANT UPDATE (stkprice) ON stock TO todd, nancy
• Give all staff the privilege to select rows from
  ITEM.
• GRANT SELECT ON item TO PUBLIC
• Give Alice all rights to view STK.
• GRANT SELECT, UPDATE, DELETE, INSERT ON stk
• TO alice

36
REVOKE

• Removes privileges
• Format
• REVOKE privileges ON object FROM users
• Cascading REVOKE
• Reverses use of the WITH GRANT OPTION
• When a users privileges are revoked, all users
  whose privileges were established using WITH
  GRANT OPTION are also revoked

37
Using REVOKE

• Remove Sophie's ability to select from ITEM.
• REVOKE SELECT ON item FROM sophie
• Nancy is no longer permitted to update stock
  prices.
• REVOKE UPDATE ON stock FROM nancy

38
The catalog

• A relational database containing definitions of
  base tables, view, etc.
• Also known as data dictionary or metadata
• Can be interrogated using SQL
• Called systems tables rather than base tables
• Key tables are
• sysobjects
• syscolumns
• systypes
• sysindexes
• sysusers

39
Interrogating the catalog

• Find how many columns belong to each table
• SELECT sysobjects.name 'Table',
• COUNT(syscolumns.name) 'Number of Columns'
• FROM sysobjects, syscolumns
• WHERE sysobjects.id syscolumns.id
• GROUP BY sysobjects.name
• What columns in what tables store dates?
• SELECT sysobjects.name 'Table',
• syscolumns.name 'Column'
• FROM sysobjects, syscolumns, systypes
• WHERE sysobjects.id syscolumns.id
• AND syscolumns.xtype systypes.xtype
• AND systypes.name 'datetime'

40
Data Control Language (DCL)

• GRANT and REVOKE DML privileges
• Control access to data
• GRANT privileges ON object TO user
• GRANT SELECT ON emp (deptname) TO markf
• GRANT ALL ON sale TO markf
• WITH GRANT OPTION grants superuser privileges for
  the object to the grantee
• GRANT SELECT ON emp (deptname) TO markf WITH
  GRANT OPTION

41
Data Control Language (DCL)

• REVOKE removes privileges from a user
• REVOKE privilege ON object FROM user
• Oracle
• Roles
• System privileges
• GRANT CREATE TABLE TO user