SQL: Structured Query Language

1
Lecture 20

• SQL Structured Query Language

2
MySQL File Insert

• HW 5
• LOAD DATA INFILE '/usr/people/faculty/cs/irahal/
  test.txt' INTO TABLE DEPTS_INFO
• FIELDS DELIMETED BY (default is tab \t)
• LINES DELIMTED BY (default is tab \n)
• IGNORE/REPLACE
• SET FOREIGN_KEY_CHECKS0 before Load
• SET FOREIGN_KEY_CHECKS0
• Insert into dept_locations values(1223,'assasaa')
  
• More info http//dev.mysql.com/doc/refman/5.0/en/
  load-data.html

3
DELETE

• Removes tuples from a relation
• Includes a WHERE-clause to select the tuples to
  be deleted
• Tuples are deleted from only one table at a time
  (unless CASCADE is specified on a referential
  integrity constraint)
• A missing WHERE-clause specifies that all tuples
  in the relation are to be deleted
• ? the table then becomes an empty table
• DELETE FROM EMPLOYEE
• DELETE FROM EMPLOYEEWHERE LNAME'Brown
• DELETE FROM EMPLOYEEWHERE DNO
  IN (SELECT DNUMBER FROM DEPARTMENT
  WHERE DNAME'Research')

4
UPDATE

• Used to modify attribute values of one or more
  selected tuples in a single table
• A WHERE-clause selects the tuples to be modified
• An additional SET-clause specifies the attributes
  to be modified and their new values
• Change the location and controlling department
  number of project number 10 to 'Bellaire' and 5,
  respectively
• UPDATE PROJECTSET PLOCATION 'Bellaire', DNUM
  5WHERE PNUMBER10

5
UPDATE

• Give all employees in the 'Research' department a
  10 raise in salary
• UPDATE EMPLOYEESET SALARY SALARY
  1.1WHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME'Research')
• In this request, the modified SALARY value
  depends on the original SALARY value in each
  tuple
• The reference to the SALARY attribute on the
  right of refers to the old SALARY value before
  modification
• The reference to the SALARY attribute on the left
  of refers to the new SALARY value after
  modification

6
Constraints as Assertions -

• General constraints constraints that do not fit
  in the basic SQL categories
• Domain-level
• Attribute-level
• Tuple-level
• Mechanism CREATE ASSERTION
• components include a constraint name, followed
  by CHECK, followed by a condition which must be
  true on every database state
• The name can be used later on to drop or alter
  the assertion

7
Assertions An Example -

• The salary of an employee must not be greater
  than the salary of the manager of the department
  that the employee works for
• CREAT ASSERTION SALARY_CONSTRAINTCHECK (NOT
  EXISTS(SELECT FROM EMPLOYEE E, EMPLOYEE M,
  DEPARTMENT DWHERE E.SALARY gt M.SALARY AND
  E.DNOD.NUMBER AND D.MGRSSNM.SSN ))
• Specify a query that violates the condition
  include inside a NOT EXISTS clause
• ? Query result must be empty for assertion to be
  valid
• if the query result is not empty, the assertion
  has been violated

8
Various CHECK Constraints -

• Most constraints are specified using the CHECK
  clause
• Domain-level CREATE DOMAIN
• Attribute-level On the same line where an
  attributes definition is provided within CREATE
  TABLE
• Involves only one attribute
• Tuple-level After defining the attributes in the
  CREATE TABLE
• Involves more than one attribute from the same
  table
• Table-level Through assertions
• Involves attributes from different tables

9
SQL Triggers

• Objective to monitor a database and take action
  when a condition occurs
• Triggers are expressed in a syntax similar to
  assertions and include the following
• timing (before/after)
• event (insert/update/delete operation)
• action (to be taken when the condition is
  satisfied)

10
MySQL Triggers

• CREATE TRIGGER trigger_name trigger_time
  trigger_event ON tbl_name FOR EACH ROW
  trigger_stmt
• trigger_time ? BEFORE or AFTER
• trigger_event ? INSERT, UPDATE, or DELETE
• NEW OLD
• NEW Insert, values after Update
• OLD Delete, values before Update

11
MySQL Triggers

• SHOW DEPTS_INFO
• drop trigger UpdateTotal
• CREATE TRIGGER UpdateTotal AFTER INSERT ON
  employee
• FOR EACH ROW UPDATE DEPTS_INFO SET NO_OF_EMPS
  NO_OF_EMPS 1,TOTAL_SAL
  TOTAL_SALNEW.salary where DEPT_NAME IN (Select
  dname from department where dnumberNEW.dno)
• Insert into employee(fname,lname,ssn,salary,dno)
  values ('Imad','Rahal','12344321',100000,6)
• SHOW DEPTS_INFO

12
Purpose of Data Warehousing

• Data Warehouses are mainly optimized for
  appropriate data access
• Traditional databases are transactional and are
  optimized for both access mechanisms and
  integrity assurance measures
• Data warehouse users need only read access but,
  need the access to be fast over a large volume of
  data
• Data warehouses emphasize more on historical data
  as their main purpose is to support time-series
  and trend analysis.
• The above functionality is achieved by Data
  Warehousing and Online analytical processing
  (OLAP)
• Compared with transactional databases, data
  warehouses are nonvolatile.

13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
Views in SQL

• Fridays Assignment 1 view 1 trigger
• Reflect the different external schema(s)
• A view is a virtual table that is derived from
  other tables referred to as the defining tables
  of the view
• Virtual VS base tables which are physically
  stored on disk
• Allows full query operations (SELECT operations)
• No difference between a table and a view
• Allows for a limited update operations (since
  most of the time, the view table is not
  physically stored)
• Visible differences between tables and views

17
Advantages of Views

• Simplify the specification of some frequently
  posed queries (more efficient)
• A view is similar in many ways to a subroutine in
  standard programming
• Can be used in multiple queries
• Access Control and security Give groups of users
  access to just the information they are allowed
  to see
• E.g. Registrar and Business office need student
  information but the former cares about grades and
  GPAs while the latter about balances owed
• View allows owner to provide SELECT access to a
  subset of columns
• Customization Users need not see full complexity
  of database
• View creates the illusion of a simpler database
  customized to the needs of a particular category
  of users
• JOINs

18
Specification of Views

• SQL command CREATE VIEW view_name AS
• a query to specify the table contents
• Specify a WORKS_ON1 view that shows, for every
  project, the full name of every employee along
  with the number of hours they work on the project
• CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME
  ,HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
  SSNESSN AND PNOPNUMBERORDER BY PNAME

19
SQL Views An Example

• Specify a view that shows, for every department,
  the name of the department and the total number
  of employees working in that department along
  with the sum of their salaries
• CREATE VIEW DEPT_INFO ASSELECT DNAME,COUNT()
  AS NO_OF_EMPS, SUM(SALARY)AS TOTAL_SALFROM
  EMPLOYEE,DEPARTMENTWHERE DNODNUMBER GROUP
  BY DNUMBER, DNAME

20
Using Views
CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME,
HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
SSNESSN AND PNOPNUMBERORDER BY PNAME

• Specify any SQL query on a newly created view
• SELECT FNAME, LNAME FROM WORKS_ON1
• WHERE PNAMESeena
• Much simpler than original query
• Limits information to given attributes only
  (security)
• When no longer needed, a view can be dropped
• DROP WORKS_ON1

21
View Implementation
CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME,
HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
SSNESSN AND PNOPNUMBERORDER BY PNAME

• Views must always be up-to-date two ways to
  achieve this
• (1) Query modification whenever a query is posed
  on a view, represent the view query in terms of a
  query defined on the defining base tables
• SELECT FNAME, LNAME FROM WORKS_ON1
• WHERE PNAMESeena
• ?SELECT FNAME, LNAMEFROM EMPLOYEE, PROJECT,
  WORKS_ONWHERE SSNESSN AND PNOPNUMBER AND
  PNAMESeena
• MySQL

22
View Implementation

• Steps in mapping the view query to a query on the
  defining tables
• SELECT clause Get the projection attribute list
  from the view and map each attribute into the
  original attribute in the defining tables
• FROM clause Get the tables form the FROM clause
  in the view definition and append to them all
  tables that appear in the FROM clause of the view
  query
• WHERE clause Get the conditions specified in the
  WHERE clause of the view definition and add to
  them (using ANDs) the conditions that appear in
  the FROM clause of the view query
• Disadvantage inefficient for views defined via
  complex queries especially if the view is
  accessed frequently

23
View Implementation

• (2) View materialization involves physically
  creating and keeping a temporary table containing
  the data of the view
• Assumption view is frequently accessed
• Disadvantages maintaining correspondence between
  the base table and the view when the base table
  is updated
• Must update the corresponding view table

24
View Updates

• Views are updateable if
• Defined on single tables
• Have no aggregate operations
• Contains all primary key of the base relation
  along with all NOT NULL attributes having no
  DEFAULTs
• E.g. View defined on the DEPARTMENT table that
  includes DNAME, DNUMBER

25
View Updates

• Views defined using groups and aggregate
  functions are not updateable
• Why?
• CREATE VIEW DEPT_INFO ASSELECT DNAME,COUNT() AS
  NO_OF_EMPS, SUM(SALARY)AS TOTAL_SALFROM
  EMPLOYEE,DEPARTMENTWHERE DNODNUMBER GROUP
  BY DNUMBER, DNAME
• UPDATE DEPT_INFO SET NO_OF_EMPS 500 WHERE
  DNAMERESEARCH
• What if we update DNAME?

26
View Updates

• Views defined on multiple tables using joins are
  generally not updateable
• Why?
• CREATE VIEW WORKS_ON1 ASSELECT FNAME,LNAME,PNAME,
  HOURSFROM EMPLOYEE,PROJECT,WORKS_ONWHERE
  SSNESSN AND PNOPNUMBERORDER BY PNAME
• UPDATE WORKS_ON1 SET HOURS 200
  WHERE PNAMEPROJECT X
• What if we update FNAME? LNAME? PNAME?

27
View Updates

• In SQL, WITH CHECK OPTION clause must be added to
  the end of the definition of a view if the view
  is to be updated
• to allow check for updatability and to plan for
  an execution strategy
• CREATE VIEW EMP_INFO ASSELECT SSN, FNAME,
  LNAME, BDATE, ADDRES, DNO FROM
  EMPLOYEEWITH CHECK OPTION