1 / 24

Lecture 11: Basic SQL, Integrity constraints

Lecture 11: Basic SQL, Integrity constraints. www.cl.cam.ac.uk/Teaching/current/Databases/. SQL – A language for relational databases. Originates from System R project at IBM So widespread it has been standardised SQL-86 (ANSI) SQL-89 (minor revisions)

colt-nelson
Download Presentation

Lecture 11: Basic SQL, Integrity constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lecture 11:Basic SQL,Integrity constraints www.cl.cam.ac.uk/Teaching/current/Databases/

  2. SQL – A language for relational databases • Originates from System R project at IBM • So widespread it has been standardised • SQL-86 (ANSI) • SQL-89 (minor revisions) • SQL-92 (major revision ANSI/ISO: current standard) • SQL:1999 (major extension - objects) • SQL:2003 (more features and XML support)

  3. SQL components • SQL/92 is a huge standard (600+ pages) • SQL is a comprehensive database language, containing • Data definition language (DDL): to define schemas • Data manipulation language (DML): to insert, delete, modify and retrieve data • Lots of other features: e.g. views, schema evolution, access control, triggers, assertions, …

  4. Relation instances Reserves Boats Sailors

  5. Simple queries • Contents of entire relation SELECT * FROM Sailors; • QualificationsSELECT * FROM Sailors WHERE age>22;

  6. Simple queries cont. • Projection of attributes SELECT rating,age FROM Sailors; • Note that SQL does not remove duplicates. We need to request this explicitly: SELECT DISTINCT rating,age FROM Sailors;

  7. Range variables Simple queries cont. • Can query across multiple relations:SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103; • SQL allows the range variables to be dropped, e.g. SELECT sname FROM Sailors,Reserves WHERE Sailors.sid=Reserves.sid AND Reserves.bid=103;

  8. Basic SQL query syntax SELECT [DISTINCT] target-list FROMrelation-listWHERE qualification • relation-list is a list of relation names (possibly with range variables) • target-list is a list of attributes of relations in relation-list • qualification is a Boolean expression

  9. Semantics of SQL queries • The semantics of SQL query evaluation can be conceptually defined as: • Compute the product of relation-list • Discard those tuples that fail the qualification • Delete attributes that are not in target-list • If DISTINCT is specified, eliminate duplicate rows • NOTE: This is probably the least efficient way to evaluate a query!

  10. Compiling to relational algebra • The querySELECT a1, …, anFROM R1, …, RmWHERE b • Can be compiled to the RA expression:a1,…,an(b(R1…Rm)) (Assuming no duplicates)

  11. Example Find sailors who’ve reserved at least one boat SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid • Would adding DISTINCT to this query make any difference?

  12. String matching • SQL provides powerful string matching facilities • These can be used in the WHERE clause • _ denotes any one character • % denotes zero or more characters • For example: SELECT * FROM Sailors WHERE sname LIKE ‘_ic%’;

  13. Arithmetic • SQL provides arithmetic operators, which can be used in the SELECT clause • Use AS to name columns SELECT sname, age*5 AS OldAge FROM Sailors; • Can also be used in the WHERE clause SELECT S1.sname FROM Sailors S1, Sailors S2 WHERE S1.rating=2*S2.rating –4;

  14. Nested Query! Set theoretic operations • SQL provides the familiar set theoretic operators: UNION, INTERSECT, IN (set membership) • SQL also allows a WHERE clause to contain another query, e.g. SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103);

  15. Example Find names of sailors who’ve reserved a red or a green boat SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid AND (B.colour=“red” OR B.colour=“green”);

  16. Example Find names of sailors who’ve reserved a red and a green boat SELECT S.sname FROM Sailors S, Reserves R1, Reserves R2, Boats B1, Boats B2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND B1.colour=“red” AND B2.colour=“green”;

  17. Integrity constraints • An integrity constraint (IC) is a condition that must be true for any instance of the database • E.g. domain constraints • ICs are specified when schema is defined • ICs are checked when relations are modified • A legal instance of a relation is one that satisfies all the specified ICs • We’d like the DBMS to check for ICs

  18. Primary key constraints • A set of fields is a (candidate) key for a relation if • No two distinct tuples can have same values in all the key fields; and • This is not true for any subset of the key • If part 2 is false, then it’s a superkey • If there’s more than one key for a relation, then one is chosen by the DBA to be the primary key

  19. Candidate key Primary and candidate keys in SQL • Can define possibly many candidate keys, one of which is chosen as the primary key CREATE TABLE Enrolled ( sid CHAR(20), name CHAR(20), age INTEGER, grade CHAR(2),PRIMARY KEY (sid),UNIQUE (name,age));

  20. Foreign key • A foreign key is a set of fields in one relation that is used to refer to a tuple in another relation (must correspond to a primary key of the second relation) • A bit like a pointer • For example, sid in Reserves is a foreign key referring to Sailors

  21. Foreign keys in SQL • Only sailors listed in the Sailors relation should be allowed to make reservations CREATE TABLE Reserves (sid INTEGER, bid INTEGER, date DATE, PRIMARY KEY (sid,bid,date),FOREIGN KEY (sid)REFERENCES Sailors);

  22. Referential integrity • If all foreign key constraints are enforced then referential integrity is achieved. • Similar to `no dangling pointers’ • Can you name a data model without referential integrity?

  23. Semantic integrity constraints • ICs come from the semantics of the real-world enterprise that is being modelled • Another class of ICs is semantic integrity constraints. These apply to the actual data values that can be stored, e.g. “Sailor rating is between 0 and 10”

  24. Summary You should now understand: • Simple SQL queries • SELECT-FROM-WHERE • Compilation to relational algebra • Nested queries • Integrity constraints • Primary and candidate keys

More Related