1 / 22

Database Management Systems Lecture Summary: SQL Queries, Constraints, and Keys

Learn about SQL queries, database constraints, and keys for effective database management. Understand SELECT, FROM, DELETE, UPDATE operations, defaults, indices creation, and constraints in SQL. Enhance your knowledge of database schema modifications, keys, foreign key constraints, and assertion implementation in SQL. Explore tuple-level constraints and general assertions in database management systems.

ji
Download Presentation

Database Management Systems Lecture Summary: SQL Queries, Constraints, and Keys

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. C20.0046: Database Management SystemsLecture #15 M.P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  2. Summary: SQL queries • Only SELECT, FROM required • Can’t have HAVING without GROUP BY • Can have GROUP BY without HAVING • Any clauses used must appear in this order: SELECT LFROM Rs WHERE s GROUP BY L2 HAVING s2 ORDER BY L3 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  3. Deletions DELETE FROM Table WHERE condition • General form: • E.g.: INSERT INTO Knights VALUES('R. Giuliani', 'n', 'KBE'); INSERT INTO Knights VALUES('Bernard Kerik', 'n', 'CBE'); DELETE FROM Knights WHERE name = 'Bernard Kerik'; M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  4. Updates UPDATE Product SET field1 = value1, field2 = value2 WHERE condition • General form: • Example: UPDATE Product SET price = price/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date = DATE'Oct, 25, 1999') M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  5. Default Values • Specify defaults when creating table: • The default default: NULL CREATE TABLE People ( name VARCHAR(30), ssn CHAR(9), age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT 'New York', gender BIT(1), dob DATE DEFAULT DATE '1900-01-01' ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  6. Deleting and modifying schemata • Delete data, indices, schema: • Delete data and indices: • Either way, exercise extreme caution! • Add or delete attributes: DROP TABLE Person TRUNCATE TABLE Person ALTER TABLE Person ADD phone CHAR(12) Q: What’s put in the new fields? ALTER TABLE Person DROP age M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  7. Creating Indices • Syntax: • Here: • Searches are speed up logarithmically • If all New Yorkers, #comparisons: • 8000000  log2(8000000) ~= 23 • (i.e., 223 ~= 8000000) • In general: • Indices speed up queries a lot • They slow down modifications somewhat • They also speed up some modifications… CREATE INDEX index-name ON R(fields) CREATE INDEX nameIndex ON Person(name) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  8. Another BST example M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  9. Constraints in SQL simplest Constraints in SQL: • Keys • foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce Mostcomplex M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  10. Primary Keys & Unique Keys • There is at most one PRIMARY KEY; there can be many UNIQUE • Primary key v. candidate keys • Key fields get indices automatically (why?) CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (productID), UNIQUE (name, category) ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  11. Foreign Key Constraints • prodName is a foreign key to Product(name) • name should be a key in Product • Purchase ~ Product is many-one • NB: referenced field specified with (), not dot • Referenced and referring fields should/must be indexed (why?) Referentialintegrityin SQL CREATE TABLE Purchase ( prodName CHAR(30) REFERENCES Product(name), date DATETIME ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  12. Constraints on Attributes and Tuples • Constraints on attributes: • NOT NULL -- obvious meaning... • Can combine NOT NULL with foreign key constraints • CHECK condition -- any condition on row itself • Some DBMS support subqueries here, but many don’t • Constraints on tuples • CHECK condition M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  13. Tuple-level constraint How is this different from aForeign Key? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName IN SELECT Product.name FROM Product), date DATETIME NOT NULL ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  14. General Assertions • Defined in latest SQL standard, but doesn’t really exist • Implemented/approximated in MySQL and Oracle as stored procedures • PL/SQL in Oracle CREATE ASSERTION myAssert CHECK (NOT EXISTS( SELECT Product.name FROM Product, Purchase WHERE Product.name = Purchase.prodName GROUP BY Product.name HAVING count(*) > 200) ) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  15. Final Comments on Constraints • Can give them names, and alter later or drop • Still possible if unnamed, but more work to find • Can define constraints • On fields – NOT NULL, CHECK • On tuples • As triggers that look at multiple tables • On whole databases (in theory) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  16. Final Comments on Constraints • Each more powerful than last, but more expensive to enforce • Like indices • On the other hand: • The more mistakes you make impossible, the better • The more mistakes you make impossible on the DBMS side, the fewer you have to check on the client side • Think of data validation M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  17. Live examples • Q: produce a list of employees and their bosses • What if no boss? Or no subordinate? • Joins on emp, emp man: • Comma-based • Cross • Natural • Inner • Must use INNER JOIN in MySQL • If want non-managers, do outer join… • No FULL OUTER JOIN in MySQL yet M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  18. Live examples • Q: produce list of bosses and underling-counts, for bosses with >1 underling • Just add HAVING clause… M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  19. Finally: R.A./SQL has limitations • Can easily find Alice’s direct subordinates: • But: find all of King’s underlings • Cannot compute “transitive closure” • Cannot express in R.A./SQL! • SQL is not “Turing-Complete” M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  20. Midterm • Know all steps of project so far: • Draw E/R from dataset description • Convert E/R to relations • Normalize badly designed relations • Indices: • Why important • How BSTs work • Constraints • Why important • Basic concepts • SQL! • http://pages.stern.nyu.edu/~mjohnson/dbms/dbmsmtinfo.html M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  21. Midterm course evaluations • Please do midterm evals on Blackboard! • Worth 50 points on the midterm • Longish, but very helpful for me • Vote on interest levels for remaining possible topics • “We’re in touch, so you be in touch.” • Thanks! M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  22. Live examples • Examples from sqlzoo.net SELECT L FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) M.P. Johnson, DBMS, Stern/NYU, Spring 2005

More Related