Agenda Relational Constraints Keys Relational Algebra Structured Query
Agenda • • Relational Constraints Keys Relational Algebra Structured Query Language (SQL)
Relational Constraints • Three Basic Integrity Constraints – Constraints that are built into the relational data model – Can be enforced by Data Definition Language (DDL) in most systems • Primary Key Constraint – Uniqueness & Minimality • Entity Constraint – The Primary Key of a relation cannot be null • Referential Integrity Constraint – The value of a Foreign Key, if not null, must exist in the original relation
Relational Constraints • Application-Dependent Constraints – Domain constraints (range, min/max, enumerated values) • Enforced by using the CHECK clause in SQL – Semantic constraints • Enforced by triggers, stored procedures, and programs
Keys • Primary Key (PK) – Uniqueness: uniquely identify each tuple (i. e. row) in a relation – Minimality: the least amount of attributes to uniquely identify each tuple • Foreign Key (FK) – An attribute which is a PK in another relation • Composite Key or Concatenated Key – A PK that consists of multiple attributes
Keys • Candidate Key (CK) – A set of attributes which can be used as a PK – Example • Doctor(SSN, Name, License#, Driver. L#, Specialty, Age, School) • CKs = {SSN, License#, Driver. L#} • Alternate Key – A candidate key that was not designated as the PK – Example • If SSN is the PK, then all of the other CKs are alternate keys
Keys • Secondary key – An attribute that is used for an index • Search key – An attribute that is used in a query
Relational Algebra • • A basis for implementation of any relational query language A procedural language Set-at-a-time language Unary Operations – SELECT • Extracts tuples (rows) that satisfy a condition • Notation • Example: condition(relation name) – Find all employees in the SALES department » dept=“SALES”(Work) – Find all employees that do not work for the TOY department and whose eno is greater than 2 » (eno>2) and (dept “TOY”)(Work) – Can use any of the comparison operators (=, , <, , >, ) and logical operators (AND, OR, NOT)
Relational Algebra • Unary Operations – PROJECT • Extracts attributes (columns) from a relation • After projection, duplicate rows are removed • Notation condition(relation name) • Example – Find all employee names from emp 1 – name(Emp 1)
SQL: Overview • What is SQL? Structured Query Language Developed at IBM’s San Jose research lab in early 1970 s First called “Sequel”, then later renamed “SQL” SQL is descriptive (what to get), not a procedural language (how to get) – It is a SET-based language – It is the real world implementation of Relational Algebra – –
SQL: Overview (continued) • What is SQL? – Currently SQL-92 is the most implemented standard by most vendors. – SQL-99, which includes Object-Oriented Data Management, and some procedural commands is being implemented in vendor’s own flavors
SQL: Language Subsets • SQL – DDL: Data Definition Language • Create, Alter, Drop – DCL: Data Control Language • Grant and Revoke – DML: Data Manipulation Language • Select, Insert, Update, Delete
SQL: DDL • SQL DDL is used for creating and destroying DB objects: – Domains, Tables, Views, Indexes… – Main SQL DDL statements are: CREATE TABLE/ALTER TABLE CREATE VIEW CREATE INDEX Example: CREATE TABLE Student ( sid first. Name last. Name login dob ); DROP TABLE DROP VIEW DROP INDEX CHAR(2), VARCHAR(15) , VARCHAR(20) , VARCHAR(7), DATE
SQL • Enforcement of basic constraints in SQL – Primary Key • PRIMARY KEY clause – Entity constraint • NOT NULL keywords – Referential integrity constraint • FOREIGN KEY…REFERENCES clause – Alternate key • UNIQUE clause • Application-dependent constraints – Domain constraint • CHECK clause
SQL • Another Example create table employee( employee. ID number(5), ssn char(9), f. Name varchar(15) l. Name varchar(15), dept. ID number not null, gender char(1), constraint employee_pk primary key(employee. ID), constraint employee_fk 1 foreign key (dept. ID) references department(dept. ID) on delete restrict on update cascade, constraint employee_uq 1 unique (ssn), constraint employee_ck 1 check (gender IN (‘M’, ‘m’, ‘F’, ‘f’)));
SQL: DDL • Examples for ALTER TABLE Student MODIFY ( last. Name VARCHAR(25)); ALTER TABLE Student ADD( gpa NUMBER(3, 2)); • Example for DROP TABLE Student;
SQL: DCL • Syntax GRANT {Privilege. List | ALL PRIVILEGES} ON Object. Name TO {Authorization. Id. List | PUBLIC} [WITH GRANT OPTION] • Examples a) Give Manager full privileges to Staff table. GRANT ALL PRIVILEGES ON Staff TO Manager WITH GRANT OPTION; b) Give Personnel and Director SELECT and UPDATE on column salary of Staff. GRANT SELECT, UPDATE (salary) ON Staff TO Personnel, Director; c) Permit SELECT and UPDATE access privileges to all users GRANT SELECT, UPDATE ON employee, project TO PUBLIC;
SQL: DML • SQL DML allows users to pose queries and to insert, delete and modify data – SELECT, INSERT, UPDATE and DELETE • SELECT – THE most important statement in the language, with maximum options/variations to it. – Implements 3 relational algebra operations • Selection, Projection, Join
SQL: DML SELECT • Parts of SELECT statement – SELECT clause : For Selection – FROM clause : Which ‘Relation/Relations’ to use – WHERE clause : For Join conditions, and Filtering (i. e. search condition) – ORDER BY : For sorting the output – GROUP BY : For creating ‘data buckets’ – HAVING : For ‘filtering’ the ‘data buckets’
SQL: DML SELECT • WHERE (used to specify search conditions) – Comparison • =, >, <, <>, NOT, AND, OR – Range Search • BETWEEN, NOT BETWEEN – Set Membership search • IN, NOT IN – Pattern match search condition • LIKE, NOT LIKE – NULL search conditions • IS NULL, IS NOT NULL
SQL: DML SELECT • • – – – AGGREGATE FUNCTIONS (i. e. COLUMN FUNCTIONS) COUNT SUM AVG MIN MAX Examples: a) SELECT AVG(e. age) FROM Employee e; b) SELECT pnumber, pname, count(*) FROM project, work_on WHERE project. pnumber=work_on. pno GROUP BY pnumber, pname HAVING count(*) >2 ORDER BY pname;
SQL: DML INSERT • • a) b) – – INSERT – 2 formats INSERT INTO tablename[(column_list)]…. . VALUES (value_list)…. . INSERT INTO tablename[(column_list)]…. . AS SELECT…. . Examples INSERT INTO Student(sid, name, login, age, gpa) VALUES (53688, ‘SMITH’, ‘SMITH@EE’, 18, 3. 2); INSERT INTO project VALUES(1234, ‘PERFECT PROJECT’, NULL, ‘JOHN’); TIP: Insert values in capital letters.
SQL: DML UPDATE • Syntax UPDATE tablename SET fieldname=VALUE [WHERE fieldname comparison. Operator VALUE]; – If WHERE clause is specified, then only those rows that satisfy the condition will be updated – If WHERE clause is NOT specified, then ALL rows will be updated • Examples: UPDATE student s SET s. age=s. age +1 WHERE sid=53688; UPDATE project SET budget=1. 1*budget WHERE projno>1000;
SQL: DML DELETE • Syntax DELETE FROM tablename [WHERE fieldname comparison. Operator VALUE]; – If WHERE clause is specified, then only those rows that satisfy the condition will be deleted – If WHERE clause is NOT specified, then ALL rows will be deleted • Examples: DELETE FROM project WHERE manager =‘JOHN’; DELETE FROM student;
SQL • The result of a SQL query is a relation, but… – Does not automatically eliminate redundant tuples – To remove redundant records from the result set, then use the keyword DISTINCT immediately after the keyword SELECT – Set operations (UNION, INTERSECTION and MINUS) do remove redundant tuples – Attributes are stored by the sequence in which they are listed in the CREATE command – Tuples are not ordered • SELECT in SQL is the same as PROJECT in the relational algebra
SQL • Convention for NULL values in SQL – Null value is allowed as the default – Use keywords NOT NULL, if you don’t want null values – SUM, MIN, MAX, AVG, COUNT DISTINCT do not include null values – COUNT includes null values – Null values do not participate in comparisons – Null values are not included in an index
Creating Script Files • It is best to write your SQL statements in a script file. A script file is a text file that has the file extension sql (e. g. test. sql). You can use any text editor (e. g. Note. Pad) to create the script file. We will use Textpad.
- Slides: 26