Database Management & Information Retrieval at UW Information School, Study notes of Information Technology

Download Database Management & Information Retrieval at UW Information School and more Study notes Information Technology in PDF only on Docsity! 1 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n INFO-340: Database Management & Information Retrieval David Hendry Class L-05 INFO-340: Class 5 2 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Relational Model: Tables, SQL, domains, attributes, keys, relational integrity constraints, indexes, data independence, system catalog, … Problem domain: Entities, attributes, relationships, visualizations, user needs, client machines, … HISTORY PLACES Database Implementation: Access, MySQL, PostgresSQL, … Client Tools: Psql command shell, web browser, … Server Tools: Linux, HTML, JSP, JDBC, … Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n SQL INFO-340: Class 5 4 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Two Types of SQL Commands • Data Definition Commands – Create database structure • CREATE TABLE • ALTER TABLE • DROP TABLE • Data Manipulation – Populate and query tables • SELECT • INSERT • UPDATE • DELETE INFO-340: Class 5 5 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Database Design: Example Database: BoatClub Three tables: Sailor (sid: integer; sname: string; rating: integer; age: real) Boat (bid: integer, bname:string; color:string) Reserve (sid:integer; bid:integer; day:date) INFO-340: Class 5 6 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n SQL Versions • SQL is an international standard BUT vendors tend to add different features to the languages • The textbook contains the ISO syntax but PostgreSQL provides different features in addition to the ISO syntax – Check the documentation • (MySQL implements only a subset of SQL) 2 INFO-340: Class 5 7 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Data Tables Sailor (S1) 63.53Bob95 25.53Art85 35.09Horatio74 16.010Zorba71 35.07Horatio64 35.510Rusty58 25.58Andy32 55.58Lubber31 33.01Brutis29 45.07Dustin22 ageratingsname sid Boat (B1) redMarine104 greenClipper103 redInterlake102 blueInterlake101 colorbname bib Reserve (R1) 9/8/9810374 9/8/9810264 9/5/9810164 11/12/9810431 11/6/9810331 11/10/9810231 10/7/9810422 10/8/9810322 10/10/9810222 10/10/9810122 daybid sid INFO-340: Class 5 8 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Basic Form of SQL Query SELECT [DISTINCT] select-list FROM from-list WHERE qualification INFO-340: Class 5 9 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: List full details about the table sailor SELECT * FROM SAILOR; 63.53Bob95 25.53Art85 35.09Horatio74 16.010Zorba71 35.07Horatio64 35.510Rusty58 25.58Andy32 55.58Lubber31 33.01Brutis29 45.07Dustin22 INFO-340: Class 5 10 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the names and ages of all sailors SELECT s.name, s.age FROM Sailor s; 63.5Bob 25.5Art 35.0Horatio 16.0Zorba 35.0Horatio 35.5Rusty 25.5Andy 55.5Lubber 33.0Brutis 45.0Dustin INFO-340: Class 5 11 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the names and ages of all sailors SELECT DISTINCT s.name, s.age FROM Sailor s; 63.5Bob 25.5Art 16.0Zorba 35.0Horatio 35.5Rusty 25.5Andy 55.5Lubber 33.0Brutis 45.0Dustin INFO-340: Class 5 12 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find all sailors with a rating above 7 SELECT s.id, s.sname, s.rating FROM Sailor AS s WHERE s.rating > 7; 9Horatio74 10Zorba71 10Rusty58 8Andy32 8Lubber31 5 INFO-340: Class 5 25 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Find the names of Sailors who have reserved boat 101 select name PROJECTION from sailor s, reserve r X-PRODUCT where r.sid = s.id SELECTION and r.bib = 101 INFO-340: Class 5 26 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Joins in PostgreSQL T1 num | name ---------- 1 | a 2 | b T2 num | value 1 | xxx 3 | yyy 5 | xxx select * from t1 cross join t2 num | name | num | value ------------------------ 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz INFO-340: Class 5 27 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Joins in PostgreSQL T1 num | name ---------- 1 | a 2 | b 3 | c T2 num | value 1 | xxx 3 | yyy 5 | xxx select * from t1 inner join on t2 on t1.num = t2.num num | name | num | value ------------------------ 1 | a | 1 | xxx 3 | c | 3 | yyy INFO-340: Class 5 28 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Joins in PostgreSQL T1 num | name ---------- 1 | a 2 | b 3 | c T2 num | value 1 | xxx 3 | yyy 5 | xxx select * from t1 inner join on t2 using (num); num | name | value ------------------------ 1 | a | xxx 3 | c | yyy INFO-340: Class 5 29 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Joins in PostgreSQL T1 num | name ---------- 1 | a 2 | b 3 | c T2 num | value 1 | xxx 3 | yyy 5 | xxx select * from t1 natural inner join t2; num | name | value ------------------------ 1 | a | xxx 3 | c | yyy INFO-340: Class 5 30 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Joins in PostgreSQL T1 num | name ---------- 1 | a 2 | b 3 | c T2 num | value 1 | xxx 3 | yyy 5 | xxx select * from t1 left join t2 using (num); num | name | value ------------------------ 1 | a | xxx 2 | b | 3 | c | yyy 6 INFO-340: Class 5 31 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Aggregate Operators • COUNT ([DISTINCT] A) • SUM ([DISTINCT] A) • AVG ([DISTINCT] A) • MAX(A) • MIN(A) INFO-340: Class 5 32 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the average age of all sailors with a rating of 10 SELECT AVG(s.age) FROM Sailor s WHERE s.age > 10; INFO-340: Class 5 33 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: How many sailors are there? SELECT COUNT(*) FROM Sailor; INFO-340: Class 5 34 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n GROUP BY and HAVING clauses • Aggregate operators apply to all qualifying rows of a relation • Sometimes it is necessary to apply an aggregate operator to a number of groups of rows in a relation INFO-340: Class 5 35 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the age of the youngest sailor for each rating level SELECT MIN(s.age) FROM Sailor S WHERE s.rating = J (and J = 1,2, … 10) This is tedious – there is a better way! INFO-340: Class 5 36 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n GROUP BY and HAVING clauses SELECT [DISTINCT] select-list FROM from-list WHERE qualification GROUP BY grouping-list HAVING group qualification; 7 INFO-340: Class 5 37 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the age of the youngest sailor for each rating level SELECT s.rating, MIN(s.age) FROM Sailor s GROUP by s.rating; INFO-340: Class 5 38 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Q: Find the age of the youngest sailor who is eligible (> 18 years old) for each rating level with at least two such sailors SELECT s.rating, min(s.age) FROM Sailor s WHERE s.age > 18 GROUP BY s.rating HAVING COUNT(*) > 1; INFO-340: Class 5 39 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Subqueries • select statements can be embedded within select statements • Three types of subquery: – Scalar – Row subquery – Table subquery (See Text for details) INFO-340: Class 5 40 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Database Update Operations • These commands do what you might expect – INSERT – UPDATE – DELETE (Read about them in Chapter 5) INFO-340: Class 5 41 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Example • List all sailor ids who have reserved the blue boat. (NOTE: Assume that there is only one blue boat) select sid from reservations where bid = ( select bib from boat where color = “blue”) INFO-340: Class 5 42 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Summary • SQL is a language for updating and query relations • It is extremely powerful but can be tricky to use • Different vendor provide extensions and partial implementations – consult the documentation 2 INFO-340: Class 6 7 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Create Table • CREATE TABLE sailor ( sid SailorId NOT NULL, name SailorName, rating SailorRating, age integer, PRIMARY KEY (sid) ); INFO-340: Class 6 8 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Create Table • CREATE TABLE Reservation ( sid SailorId NOT NULL, bid BoatId NOT NULL, rdate DATE, PRIMARY KEY(sid,bid) FOREIGN KEY (sid) REFERENCES Sailor ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGH KEY (bid) REFERENCES Boat ON DELETE NO ACTION ON UPDATE CASCADE); Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Views INFO-340: Class 6 10 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Views Defined • A view is a virtual relation that does not necessarily exist in the database but is available when requested • A view is defined as a query against one or more base tables or views INFO-340: Class 6 11 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Example • CREATE VIEW simple AS SELECT t1.a, t2.b, t3.c FROM t1, t2, t3 …complex blah - - - SELECT a, b, c FROM simple WHERE a = “blah”; INFO-340: Class 6 12 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Two Implementation Approaches • View resolution – Run ‘view’ request against source tables then perform the request • View materialization – Store the view in a temporary table – Complication: Nee to preserve currency of view as underlying tables are updated 3 INFO-340: Class 6 13 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Why Use Views? • Data independence • Improved security • Reduce complexity** • Disadvantages – Update restrictions (best for querying) – Performance • (Nice summary on p. 183) Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Transactions INFO-340: Class 6 15 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Transaction defined • An action, or series of actions, carried out by a single user or application program, which reads or updates the contents of a database • Extremely important concept – Will examine transactions (a little) in lab INFO-340: Class 6 16 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Properties of Transactions • Atomicity – All or nothing. It works fully or nothing happens. • Consistency – The db remains in a consistent state • Isolation – Transactions are independent of each other • Durability – Transactions are permanent and won’t be lost because of a subsequent failure INFO-340: Class 6 17 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Key Idea • SQL provides operations for controlling transactions: – Commit – Rollback • Extremely important in systems with concurrent read/write/update operations • (Chapter 19 in text for more.) Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n File Organization and Storage Structures 4 INFO-340: Class 6 19 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Mapping Conceptual Abstractions to the Physical SQL … … … A2 ……Rn …R2 …R1 AnA1 CPU INFO-340: Class 6 20 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Memory Hierarchy • Data is stored on non-volatile media but to answer a query data must be brought into main memory • Time to access a data item – Main memory: ~ 50 nanoseconds (10 -9 sec.) – Disk: ~ 20 microseconds (10 -6 sec.) INFO-340: Class 6 21 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Goal: Minimize Disk Accesses Ratio = 20 * 10 -6 / 50 * 10 -9 = 400 ~ 100 – 1,000 times faster to access memory compared to disk Thus, it is important to reduce disk accesses INFO-340: Class 6 22 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n Buffer Pool DB Page Page Main Memory Disk SQL QUERIES Higher Level Code Page size= 4K INFO-340: Class 6 23 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n … … … A2 ……Rn …R2 …R1 AnA1 A FILE on disk A B C D E F GRow1 Row2 Row3 A TABLE INFO-340: Class 6 24 Th e In fo rm at io n Sc ho ol of th e U ni ve rs ity o f W as hi ng to n File Organization • The physical arrangement of data on records and pages on secondary storage (disk) • Types – Heap (unordered) – Sequential (ordered) – Hash files • Q: Which is the best?