tailieunhanh - Lecture Database Systems - Lecture 11

Chapter 11 present the contents: Designing a set of relations, properties of relational decompositions, algorithms for relational database schema, multivalued dependencies and fourth normal form, join dependencies and fifth normal form, inclusion dependencies, other dependencies and normal forms. | CSC271 Database Systems Lecture # 11 Summary: Previous Lecture Objectives of SQL History of SQL Importance of SQL Writing SQL statements DML (Data Manipulation Language) SELECT statement DreamHome Case Study Consist of following tables: Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Instance of DreamHome Instance of DreamHome Instance of DreamHome Row Selection (WHERE) To restrict rows that are to be retrieved, use WHERE clause Keyword WHERE is followed by a search condition There are five basic search conditions (or predicates using the ISO terminology) available The WHERE clause is equivalent to the relational algebra Selection operation Search Conditions Comparison Compare the value of one expression to the value of another expression Range Test whether the value of an expression falls within a specified range of values Set membership Test whether the value of an expression equals one of a set of values Search Conditions Pattern match Test whether a string matches a specified pattern. Null Test whether a column has a null (unknown) value Example Comparison search condition List all staff with a salary greater than 10,000 SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000; Result: Example SQL Comparison Operators In SQL, the following simple comparison operators are available: = , (ISO standard) / != (allowed in some dialects) , >= Complex predicate using logical operators AND, OR, NOT Parenthesis to show the order of evaluation Expression Evaluation The rules for evaluating a conditional expression are: An expression is evaluated left to right Sub expressions in brackets are evaluated first NOTs . | CSC271 Database Systems Lecture # 11 Summary: Previous Lecture Objectives of SQL History of SQL Importance of SQL Writing SQL statements DML (Data Manipulation Language) SELECT statement DreamHome Case Study Consist of following tables: Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Instance of DreamHome Instance of DreamHome Instance of DreamHome Row Selection (WHERE) To restrict rows that are to be retrieved, use WHERE clause Keyword WHERE is followed by a search condition There are five basic search conditions (or predicates using the ISO terminology) available The WHERE clause is equivalent to the relational algebra Selection operation Search Conditions Comparison