Relational Query Languages (2) SQL and QBE Walid G. Aref
Query Languages For The Relational Model Relational Algebra Procedural Domain Relational Query Query By Languages Relational Example (QBE) Calculus Declarative Tuple Relational SQL Calculus Walid G. Aref
Query Languages For The Relational Model Relational Algebra Procedural Domain Relational Query Query By Languages Relational Example (QBE) Calculus Declarative Tuple Relational SQL Calculus Walid G. Aref
SQL: Structured Query Language • Standard since 1986 • Declarative: State what you want, not how you evaluate it • Builds on Tuple Relational Calculus: Variables refer to tuples • Where are the variables in SQL? • Select [Distinct] (Output Attributes List) From (Table List) Where (Qualification Predicates) • Example: Select DISTINCT S.name, S.gpa From Students S S is a Tuple Variable ß Where S.age < 21 • DISTINCT: Optional è Output table should not contain duplicates. • Default is that duplicates are not eliminated! • In contrast to Relational Algebra being based on sets (no duplicate tuples allowed, which is expensive), SQL is based on bags (duplicate tuples allowed) Walid G. Aref
Expressing Relational Algebra Operators Using SQL • Select : SELECT * FROM r WHERE r.id = 123 AND r.a =‘a’; • Project : Select r.a, r.b+100 FROM r • Union : (SELECT * FROM r) UNION (SELECT * FROM s); • Set Difference : (SELECT * FROM r) EXCEPT (SELECT * FROM s); • Cross Product : SELECT * FROM r, s; SELECT * FROM r CROSS JOIN s; • Rename : Select cid as CourseID into Table1 FROM Courses; • Intersect : (SELECT * FROM r) INTERSECT (SELECT * FROM s); • Join : Select * from r, s where r.a = s.b Walid G. Aref
SQL: Null Values and Three Valued Logic • Three-valued Logic: • Nulls mean missing value, unknown, a • OR: value does not exist or is not applicable. ( unknown or true ) = true • Handling Nulls complicates things in SQL ( unknown or false ) = unknown ( unknown or unknown) = unknown • In SQL, aggregate functions ignore null • AND: values (true and unknown) = unknown (false and unknown) = false • Could have returned null as result instead. (unknown and unknown) = unknown • What happens when Nulls are involved • NOT : in a predicate ( not unknown) = unknown • For SQL, the predicate: • Salary > 50K • P is unknown • But if salary is Null for some tuple. Returns • Evaluates to true if Predicate P True or False? Or Null? evaluates to unknown Walid G. Aref
Our Example Relational Database Schema • Students(sid: string, name: string, login: string, age: integer, gpa: real) • Courses(cid: string, cname: string, credits: integer) • Enrolled(sid: string, cid: string, grade: string) • Instructor(iid: string, iname: string, irank: string, isalary: real) • Teaches(iid: string, cid: string, year: integer, semester: string) Walid G. Aref
Creating Tables in SQL, Keys, and Foreign Keys • Students(sid: string, name: string, • CREATE TABLE Enrolled login: string, age: integer, gpa: real) (sid CHAR (20), • Enrolled(sid: string, cid: string, cid CHAR(20) , grade: string) grade CHAR (2), • CREATE TABLE Students PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students (sid: CHAR(20), ON DELETE CASCADE name: CHAR(20), ON UPDATE SET DEFAULT, login: CHAR(10), FOREIGN KEY (cid) REFERENCES Courses age: INTEGER, ON DELETE CASCADE gpa: REAL, ON UPDATE CASCADE) • Other options: PRIMARY KEY SID); • ON DELETE REJECT • UNIQUE (cid, grade) Walid G. Aref
Deleting and Altering Tables • To delete Table Students • DROP TABLE Students • Add an attribute to Table Students, and fill it with null • ALTER TABLE Students ADD COLUMN GraduationYear: integer • Can set another default value
Data Manipulation in SQL • Insert new tuple into table: • INSERT INTO Students (sid, name, login, age, gpa) VALUES (0111, ‘Bright, Mary’, ‘mb@purdue.edu’, 22, 4.0) • Can also perform bulk insert • Bulk Delete : Delete all the tuples that satisfy the delete predicate • DELETE FROM Students S WHERE S.name = ‘Bright, Mary’
Integrity Constraints • Must always be true for any instance of the database • Cannot detect if the data is true as long as it satisfies the integrity constraints. • But can detect if the data is wrong • Strength in SQL • Integrity constraints are associated with the data definition and not with the program logic • Integrity constraints are specified when schema is defined. • Does not depend on the programmers to enforce them. They are always verified by the system • ICs are checked every time the relation is modified or updated
Various Forms of Integrity Constraints • Domain Constraints : The possible data values permissible in a given attribute • Key Constraints : Unique combinations of attribute values over the entire table • Primary key constraint • Unique key constraints • Referential Integrity Constraints (Foreign Key Constraints): • Primary key value in the other relation must match with every foreign key value. • Avoid “dangling pointers”
Re Referential Integrity Constraints (Foreign Ke Key Con Constraints) • Foreign key values pointing to primary key values • Foreign Key à Primary Key • What should happen when a primary key value gets deleted or updated? • Integrity constraint enforced with the data itself not with the program logic • Domain experts specify what needs to be done Create Table … … Foreign key ... References … On delete reject On update cascade On delete set default
Supporting General Constraints • Can check general conditions, e.g., • CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL, PRIMARY KEY SID, check gpa >= 0.0 and gpa <=4.0); • Check can contain other Select statements, etc., e.g., gpa > select gpa from … • Can also have an ASSERTION that is not associated by any one table • E.g., maintaining some student-professor ratio • Create Assertion Student-InstructorRatio(Check ( (select count(*) from students) / (select count(*) from instructors) < 15) • Has performance implication as these need to be rechecked with every update
Views in SQL • View: A relation stored by storing the Select statement that generates it • CREATE VIEW BrightStudents (name, gpa) AS SELECT name, gpa FROM Students WHERE gpa > 3.6 ORDER BY gpa desc <<< to order the tuples by gpa in descending order • Can limit the number of tuples in the output, e.g., add LIMIT 3 to get the top 3 students • Use of Views for Security: Can hide some attributes in the base tables, e.g., hiding student’s grades or gpa when student is in the University health center. Also, hiding medical info of a student from the course instructor • SELECT name from BrightStudents where gpa = 4.0 • Replace BrightStudents with its Select statement è Nested query • May not be very efficient during query execution Walid G. Aref
Materialized Views • Optimization: Materializing a view • A view can be materialized into disk by storing its result on disk • Previous query accesses the materialized view like a regular table • CREATE MATERIALIZED VIEW … • Is one form of redundancy as data is partly replicated in the base tables and the materialized view tables • What happens when the base tables get updated? • Eager update of materialized views: • Whenever base tables get updated, will need to update the materialized views (overhead) è Eager maintenance of materialized views (overhead) • Lazy update of materialized views: • When a base table is updated, accumulate all updates in delta tables, but do not update materialized view tables eagerly. • When a query references a view, only then, propagate all delta tables related to this view into the materialized view, then evaluate the query
Updating Data in the Materialized View • Can we update the data in the materialized view? • Updating a view must translate to an update of the actual (base) relations in the database. • When Keys of the base tables are part of the view definition • Thus, a tuple in the view will have a one-to-one mapping to the base tables. • Thus, an update will be possible, and will reflect uniquely and unambiguously to tuples in the base tables. Walid G. Aref
SQL: Scalar and Table Functions • User-defined functions are defined in SQL using: • CREATE FUNCTION .. RETURNS TABLE… • CREATE FUNCTION .. RETURNS INTEGER … • Accept parameters as input • Perform calculations • Return the result. • The return value can either be • A scalar (single) value è Scalar Function • A table è Table Function (Function that returns a table) • Where do we use a table function? In the FROM clause (or in any location where a table is expected) Walid G. Aref
Nesting Queries in SQL • Find the names of the instructors who taught CS541 • Instructor(iid: string, iname: string, irank: string, isalary: real) • Teaches(iid: string, cid: string, year: integer, semester: string) • Select i.iname From instructors i where i.iid in (select t.iid from teaches t where t.cid = “CS541”); • Can plug in an SQL query anywhere in the Select statement where we are expecting a table, e.g., in the From clause or as shown above. • Other locations are after: IN, EXISTS and UNIQUE, NOT IN, NOT EXISTS and NOT UNIQUE, op ANY, op ALL, where op is any of: >, <, =, ≤, ≥, ≠ Walid G. Aref
Recommend
More recommend
