Notes for Comp 468 - Database Design Lecture 1 - 25 August 2003 JOINs Here's a table of book information: ISBN TITLE AuthID AuthorName AuPhone PubID PubName PubPhone Price 111111 Database MS 22 Ramakr&Gehrke 9003241 13 McGraw 8001234567 102.25 222222 Access DB 3 Steve Roman 9001234 11 OReilly 9001234567 24.95 333333 Fund DB 14 Elmarsi/Navathe 9002345 14 AddWesl 8002345678 89.95 Is this ok? What if we add a new entry 444444 Oracle 3 Roman 8003579 14 AddWesl 8002459987 59.95 There is a problem here: Roman (and AddWesl) each have new phone numbers, but the *old* numbers in other entries aren't necessarily updated! There is a serious CONSISTENCY problem. Database consistency is a very real problem. Loyola does not have a centralized DB, and so there are lots of offices that have files that include my address. Even though I moved four years ago, I am pretty sure some of these files are still wrong! "Flatfile" databases just deal with this kind of redundancy/consistency problem as best they can, and as a result some inconsistencies inevitably creep in. These inconsistencies were once a justification for not adopting centralized databases. The relational approach is to break down the above table into subtables, together with identification of "keys", so that inconsistencies (two records containing different values for something that should be the same such as AddWesl's phone number) can't occur. That's not to say that data can't be *wrong*, due to incorrect entry, but at least you won't have to track down multiple places to update the same piece of information. A *relation* is just a multiple-field datatype, eg record/class/struct. In the database programming world, a relation is often called a *table*. A "flatfile" database has just a single table; here is a way of breaking up the book example into multiple tables. Note that we now have multiple *types*, in the programming-language sense, of records. BOOKS: key: ISBN AUTHORS: key: AuthID PUBS: key: PubID The rule is that each table can have at most one entry with a given key value. With these three tables we've guaranteed that if we update an AuPhone or PubPhone once, we've done it "everywhere". And we might save space; if the PUBS field includes 100 bytes for PubPhone and PubAddress, and there are 100,000 books by one publisher, having the data included in every one of those records would take ~10MB. Of course, the drawback is that PubID now occurs in two tables, ditto AuthID, so some space is used there, and there's also the need to *search* for the matching information. This process of recreating the original table by finding an entry in BOOKS and then looking up the AuthID and PubID in the AUTHORS and PUBS tables respectively is called JOINING the tables; the JOIN operation is fundamental. How about records with *lists* as fields? Well, we have that: books can have multiple authors. At first glance it might seem that the AuthID field in BOOKS should be an AuthIDList field. There are practical problems with list fields, though, notably that every DB operation has to take them into account when searching or updating. A better approach is again to use joins: replace BOOKS with BOOKS2: key: ISBN BOOKAUTH: key: Now our book would be represented by the following records: BOOKS2:< 111111, Database MS, 13, 102.25> BOOKAUTH:<111111, 22> BOOKAUTH:<111111, 23> New AuthID entry for Gehrke AUTHORS:<22, Ramakrishnan, ...> AUTHORS:<23, Gehrke, 903241> Note that you need to know both the ISBN *and* AuthID fields to uniquely determine a BOOKAUTH record. (This is, of course, all the fields there are). Why not BOOKTITLE: , BOOKPRICE:, etc? Because there is no advantage in this; each book has one publisher and one title and one price. History: late 1960's: IBM IMS 1970: Codd paper on relational db ~1980: practical relational dbs arise ===================================================================== Lecture 2 - 27 August 2003 What is a database? A collection of data: text file username:password:userid:groupid:gecos:homedir:shell fields can be delimited by ":", other char, tab, or by column. excel spreadsheet name email exam1 exam2 final grade1 part_num description quantity_in_stock photo library: list of "photo" might be a file, but it also might be the photo bitstream itself medical records patient_name, followed by any of several thousand possible triples file cabinet electric: bills, fuse map, energy-efficiency stuff phone: bills, service agreement, receipts various categories banking Implicit here: the data is in the form of a collection of *records* Med records: *sparse*, in some sense, unless labs are strings. Here's another example. Suppose we want to store data like , eg List data elements are *possible* (strings are after all lists of chars), but it complicates accessing individual components. DBMSs optimize for treating fields as atomic data. And we can get the same effect without lists by allowing duplicates: Note that the name field is no longer a *key*, but that's *ok*. This kind of vector-elimination is always possible. With two lists: this could also be: --------- [While this kind of decomposition can always be done, in some cases it's more complicated than in the example above] Here's another example: and some hypothetical data (note that hwk# serves as another way for representing lists/vectors) <100123, peter, 1, 37> <100123, peter, 2, 49> <100123, peter, 3, 67> <100123, peter, 4, 89> getting better! <100123, peter, 5, 97> I'd give me an A, at this point! <100124, peter, 6, 98> There is no problem here; different peter! <100123, pete, 7, 95> DANGER! DANGER! THIS IS BAD! <100123, peter, 4, 86> This is also bad! Why? The problem is that there is again relational redundancy. Here's a better way: key: key: With this arrangement, including enforcement of keys (easy to automate), the two bad cases above CANNOT OCCUR! Note again the need to use *joins*. HEre's a summary of some problems with flatfile databases: Update anomalies: to update a publisher phone#, we need to find every record! =inconsistency problem Insertion anomalies: what if we want to insert a new publisher that doesn't have any books out yet? Deletion anomalies: what if we delete all the books by a given publisher, but didn't want to delete the publisher info itself? Some relational-DB problems: Avoiding Data loss: note that we *have* lost which author is listed first! Relational integrity: what if we do remove a publisher? Can't do that without deleting all that publisher's books! Creating "views" (virtual tables based on joins of the original tables) gets complicated. ======================= Some topics: Design SQL - Structured Query Language Normalization - dividing up into subrelations as above Oracle Some practical issues Relational DBs: oracle, access, informix, sybase Other DB types: hierarchical, object-oriented DB designers, administrators, users Redundancy elimination Security Systemization (encoding "business rules") User interfaces (web, etc) backup & recovery (tricky if the crash happens in the middle of an update!) Write-Ahead Log Entity-Relation view of design File Systems versus DBMS * what is meant by file system? Separate tables as files, usually * how do we support queries? * how do we ensure consistent concurrent access? * how do we manage security? * how do we deal with crashes? How a DBMS deals with these things Sometimes a DBMS is overkill Sometimes there are realtime constraints Section 1.5: Relational model (outlined above) schema: Students(sid: string, name: string, login: string, age: integer, gpa: real) add integrity constraint: _sid_ is a key field. schemas are defined with data definition languages (DDLs); SQL has this embedded. Students(sid: string, name: string, login: string, age: integer, gpa: real) Faculty(fid,fname, salary) Courses(cid, coursename, credits) Rooms (rno, building, capacity) Enrolled(studentid, courseid, grade) record for a single enrollment, an "entity" Teaches(faculty_id, course_id) Meets_in(courseid, room_num, time) 3 kinds of data models Conceptual (or logical) schema: actual relations. Fields are the *entities* of the DB; thus the notion of entity-relationship modeling. Note: publishers, books, authors are the entitites above. In the example immediately above, students are the entitities (not gpas!) Physical schema: "hints" on how actual storage might be arranged, to improve efficiency. One important part is specification of what fields have indexes. External Schema: "virtual" relations, formed via join & other computation entire book publisher table above courseinfo(courseid, instructorname, enrollment) join Teaches & Faculty on faculty_id field, then *count* the Enrolled records ======================== Lecture 3: Sept 3 DO EXERCISES 2.2, 2.3 FOR MONDAY SEPT 8 Data independence logical: on relation structure physical: on storage layout Some queries: 1. What is the name of the student with ID 123456? 1.5. What students are named John Smith? 2. What is the average salary of professors who teach CS564? 2.5. What is the *median* salary ... 3. How many students are enrolled in CS564? 4. What percentage of them got a B or better? 5. What students with gpa < 3.0 are enrolled in CS564? SQL is a language for formulating these. DML: data manipulation language, a counterpart to DDL. Transactions serialization serialization example: Updating a record in which salary s=100 process1 process2 goal: add 10 goal: add 20 fetch s=100 compute s=110 fetch s=100 write s=110 compute s=115 write s=115 Note that the final value of salary is 115! The process1 update got overwritten, and lost! locks central DBMS server blocking, etc =========== E-R modeling (Entity-Relational) This is very similar to object modeling in software engineering: the goal is to identify the ENTITIES of the domain of discourse, that is the objects, like employees items_for_sale purchases payments departments locations and then figure out the RELATIONSHIPS between them. E-R modeling is not specific to relational DBs, or even necessarily suited best for them. But it is still a widely used technique with RDBs. E-R relationships basically are the relationships of the final DB. Entities are a little more vague: they can end up as single attributes (column names in relationships), or sets of attributes (eg ), or even a Relationship (A table of Employee info could be seen as representing employees!). Typical outcome: The ENTITIES each become a table of attributes of the entity in question, with a clear-cut key. For example, for employees the key might be SSN, with attributes SSN, badge_no, name, address, start_date, parking_lot The RELATIONSHIPS are typically binary, eg works_in: (Employee, Dept), and typically many-many. The reason for the last part is that 1-1 relationships, or even 1-many, are easy to implement as an extra attribute to one of the ENTITIES. (For example, if every employee works in exactly one dept, then we can just make DEPT an attribute of EMPLOYEE.) Relationships involve ENTITIES and also other ATTRIBUTES. It is often not clear when an ATTRIBUTE gets promoted to an ENTITY. Most "real" examples of relationships are binary, or can be decomposed into binary relationships. There are cases where a ternary relationship is required, though. Six design stages (pp 26-28); ER applies to first three: 1 Requirements Analysis 2 Conceptual DB design (high-level semantic model for data) 3 Logical DB design: choose a specific DBMS 4 Schema refinement: basically NORMALIZATION, to be addressed later 5 Physical DB design 6 Application design, Security design Entities: Students Faculty Courses Rooms *maybe* Enrollments?? Or should that be a relationship? Note in the example on p 13 these are the first 4-5 tables; the subsequent tables represent "relationships". But they are a special form: each begins with an id that is a key, followed by *attributes*. What attributes depends on what we need. Are addresses entities or attributes? They're the latter, if all we care about is their values as strings. But suppose we want to search for all employees in CHICAGO, or in zip codes 60626, 60660, and 60645: then we need to access subfields of the address, and we probably want addresses to be entities themselves. An ENTITY SET is the set of values, eg the set of all students. Entity sets have KEYs. Maybe one or more. Relations: Enrolled_in Teaches Meets_in Entities: rectangles Attributes: ovals Relationships: diamonds 1. Figure 2.1 shows Employees entity. Note name is a single attribute; we could also use fname,lname or even create a NAME entity. We could also add address info, either atomic or not. 2. Departments entity, in figure 2.2 3. Works_In relationship, in figure 2.2: note attribute, and m:n nature. Probably still use for n:1 relationship (n employees : 1 dept, or the employee is the key in the Works_In relationship: each employee has ONE dept). Note re attributes: the rule is that keys must consist of entities only! An INSTANCE of a relationship: figure 2.3 TERNARY relationship Works_in2 in figure 2.4 You can work in Math at Lakeshore and CS at Watertower Note we can't make this binary! Figure 2.5: Reports_To relationship; needs "role indicators" Key constraints: Works_In: neither employee nor Dept is a key Manages: Dept is key; Employee is not (can manage >1 dept) Arrow Arrow added to Figure 2.5: Reports_To Figure 2.7: good for Manages, not Works_in (key, participation violations) participation constraints: every employee has at least one dept, every dept has at least one employee, every dept has (only) one manager, *not* every employee manages some dept! Manages: 1:N, or one-to-many. or the key is the entity opposite the 1. 1-1 Manages Figure 2.9: a ternary relationship with Employee as key. Note this can be decomposed into two binary relationships (Employee, Location) and (Employee, Department). Participation constraints: total or partial: does every dept have a manager? Does every employee manage something? Does every employee have a dept? Figure 2.9: Manages and Works_In, side-by-side sketch of weak entities Week 3 Lecture 4: Monday, Sept 8 Entities and Relations are both implemented as relationships. However, entities are "natural" objects. In practice there are lots of grey areas. Weak Entities: dependents-insurance example. Need IDENTIFYING OWNER. Note that in a table of dependents, we would include a field Employee-owner containing the SSN (or other key) for the owning employee. Figure 2.11 Do we need weak entities? Alternative is to let entities have other entities as "attributes", eg add EMPLOYEE as a field of DEPENDENT. review of keys: for entities: underline the attributes forming a minimal key set may be several attributes; eg (state, license_plate) there may be more than one key; only the "primary" one is underlined, eg (state, license_plate), VIN for relationships: again, there may be more than one, but all we can display in an ER diagram is a single key (by making the line an arrow). multi-entity keys: can make relationship out of them, and use aggregation to make that a (key) entity in the new one. Class hierarchies and the IS-A relationship (inheritance). Blurring of divisions between ENTITIES and RELATIONSHIPS Aggregation: sort of viewing relationships as entities, for specific needs. Sponsors example, p 40 (not done sept 8) Section 2.5 entity v attribute example: address example: make (from,to) an entity so it can be part of a key Figure 2.14: another fundamentally ternary example entity v relationship if there is a per-manager budget, maybe the Manages relationship should become a Manager entity. binary v ternary Figure 2.17 is basic example. Now we add the constraints on p 43. Leads to splitting into two binary relationships: purchaser and beneficiary ====================================================================== Lecture 5, Sept 10 N:M relationship as a 1:N relationship and N:1 relationship, with a new ENTITY Example: Students, Courses, and Enrolls Make a Registration entity instead, and new relations TAKES and COURSE -------- Works_in2: ternary, no key constraints What exactly does this mean? (employee, dept) is not a key (employee, loc) is not a key (dept, loc) is not a key same employee can work in different depts at same or different locations same employee can work in different locations in same or different depts -------- aggregation v ternary Sponsors / Monitors relationships: original on p 40, Fig 2.13 There is no key or participation constraint for Monitors. Easy to add, though. Every project has a sponsor. Figure 2.19: alternative view of Sponsors. BUT we can't say that each project has a unique monitor! Or that each sponsorship has a monitor [?] Chapter 3 did TABLE/INSERT/DELETE/UPDATE integrity constraints (key, participation) also "inherent" integrity due to normalization relational schema: essentially a record-type declaration relation: set of *instances*; implicitly of distinct tuples. due to weak entities, sometimes the uniqueness is not required domain constraints: fundamental! degree: number of fields (ie columns) cardinality: number of rows SQL DDL statement CREATE TABLE. No punctuation between field name and type; commas between successive fields. INSERT INTO VALUES (...) DELETE FROM table rowname WHERE ...rowname... UPDATE table rowname SET ...rowname... WHERE boolean-involving-rowname 3.2 Integrity Constraints domain constraints key constraints candidate keys superkeys primary key SQL example Students, p 66: UNIQUE (name, age) declares 2ary key PRIMARY KEY (sid) declares primary key CONSTRAINT StudentKey: names a constraint Full declaration: CREATE TABLE Students( sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, gpa REAL, UNIQUE (name, age), ;; declares (name,age to be 2ndary key CONSTRAINT StudentsKey PRIMARY KEY (sid)) 3.2.2: foreign-key constraint: involves two or more relations We use this to declare that a given field matches the primary-key field of some other relation. (Maybe same relation). The IC part is that we cannot insert a studid into Enrolled (below) that doesn't match an sid of table Students. Consider Enrolled (sid: string, courseid: string, grade: string) We need to be sure that every record in Enrolled has a valid sid, the studentid, taken from the Students table. CREATE TABLE Enrolled ( studid CHAR(20), courseid CHAR(10), grade CHAR(10), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students) The last line means every studid value must match a PRIMARY KEY (sid) value in Students. Could refer to same entity; eg Students relation could have a field for lab_partner. Can be null initially. Key field can never be null, though! --- More general constraints (3.2.3) We can implement integrity constraints like: age >=16 age >= 19 requires gpa >= 3.0 (ie age < 19 or gpa >= 3.0) We will get to these in chapter 5, under table constraints and assertions Example: CREATE TABLE Students( sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, gpa REAL, UNIQUE (name, age), CONSTRAINT StudentsKey PRIMARY KEY (sid), CHECK (age <20 OR gpa >= 3.0) ) Integrity constraints need to be checked as new records are inserted, and after key field changes: UPDATE Students S SET S.sid = 50000 WHERE S.sid = 60000 Problems can occur with deletions of Students, or insertions of Enrolled. 3.3: Enforcing ICs, p 69 Two INSERT problems; these would be rejected due to primary key constraints Foreign keys are more complicated. Example 1: insertion of an Enrolled record with no corresponding sid. Inserts are easier. 3 questions, p 70: (a) what about illegal Enrolled insertion? Disallow it! (b) what about a Students deletion, if there are Enrolled records referring to that student? delete corresponding Enrolled? disallow the deletion? set the studid columns to a default value, if any match? set studid column to null, if any match? For DELETE (and UPDATE), sql allows a choice of these four options. delete corresponding Enrolled: ON DELETE CASCADE disallow: ON DELETE NO ACTION (default!) ON DELETE SET DEFAULT ON DELETE SET NULL These are all actions to be taken ON DELETion of the Students entry referred to by the FOREIGN KEY. 3.3.1: p 72 Example of a DEFERRED constraint, checked after a batch of updates. This is (a) often helpful, and (b) sometimes essential. The example on that page has a circular reference! ====================================================================== Lecture 6, Monday Sept 15 3.4 Querying Select * FROM Students S WHERE S.age < 18 Alternative: SELECT S.name, S.login Finally SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid = E.studid AND E.grade = 'A' Note role of FOREIGN KEY ------------- 3.5: Converting ER diagrams to relations 3.5.1: ENTITIES: relatively straightforward use PRIMARY KEY entries 3.5.2: relationships without constraints FOREIGN KEY (ssn) REFERENCES Employees(ssn): p 76 note 3-ary key Reports_To example: two FOREIGN KEY declarations Note: we're assuming employees can have multiple supervisors here. If not, then we'd have instead PRIMARY KEY (subordinate_ssn) CREATE TABLE Reports_To ( supervisor_ssn CHAR(11), subordinate_ssn CHAR(11), PRIMARY KEY (supervisor_ssn, subordinate_ssn), FOREIGN KEY (supervisor_ssn) REFERENCES Employees(ssn), FOREIGN KEY (subordinate_ssn) REFERENCES Employees(ssn), ) 3.5.3: Relationships with Key Constraints Basically, we declare PRIMARY KEY 2 approaches: 1. Freestanding MANAGES relationship, with key constraint 2. DEPT+MGR entity, moving manager into a field of Dept. Assumes each Dept has at most 1 manager. 3.5.4: Participation Constraints These can be HARD. Easy case: Dept+Mgr * make mgr_ssn NOT NULL mgr_ssn CHAR(11) NOT NULL * ON DELETE NO ACTION FOREIGN KEY (mgr_ssn) REFERENCES Employees ON DELETE NO ACTION So we can't delete the manager's Employee record. Hard case: participation constraints of Works_In1 (in which employees can work in multiple depts). Why doesn't making the ssn field NOT NULL work? Problem with Works_In: This relationship has two foreign keys, ssn and did. We want to guarantee every ssn and every did appears in some Works_In tuple. Could we make did in Departments be a FOREIGN KEY referring to Works_In? No; did isn't a key at all in Works_In. 3.5.5: Weak Entity Sets Two things: * use owner key as FOREIGN KEY for the weak entity * use ON DELETE CASCADE 3.5.6: Class Hierarchies Brief discussion, IS_A relationship Like OO, except there is no notion of class *methods* On the other hand, we do have "coverage" and "overlap" issues coverage: must every employee be in at least one of the subclasses? overlap: must every employee be in at most one of the subclasses? Employee, Hourly, Contract Person, Employee, Student, ... Two implementations: OO style Hourly: includes hourly attributes, and a key for the Employee superclass. Independent style: have separate Hourly and Contract entities, each containing all the basic "employee" fields. end of class Sept 15 Sept 17 3.5.7: ER with aggregation SKIM AS NEEDED Relatively straightforward option: just drop Sponsors Lecture 7, Sept 17 3.6: Views CREATE VIEW B-Students ... p 86 A virtual table; a *join*. Other views can be restrictions of a single table; eg all columns but the ssn and creditcard fields. UPdate restriction: can update a view only if it is based on a single physical table. Problems with updates: maybe the new/updated record isn't part of the view any more! More serious problem: if all we know is that we want to delete/update a record in the view, there may be several ways to achieve this in terms of modifications to the original tables. Club example, pp 90-91 Note that names are not unique! We have no way to know whether smith@ee or smith@math is the real member of Hiking. Chapter 4: relational algebra Note: field names are fundamentally irrelevant, but sometimes *very* convenient. tables: Entities: Sailors, Boats Relationship: Reserves Goal: compose relational expressions to get new relations (eg VIEWS). May then do some traditional computation on the result. Selection (sigma) Selects rows of a relation that satisfy a specified boolean expression projection (pi) extracts specified columns from a relationship; throws others away. Uniqueness issue! Union, Intersection, Difference Cross-Product: RxS = all tuples of length m+n, where m=degree(R) & n=degree(S), and 1st m items form a tuple in R and last n items form a tuple in S. Note that the cross-product may introduce name duplicates, so we allow a: Renaming operator, rho JOINS General join: cross product followed by selection. Example of S1.sid < R1.sid Equijoin Natural Join (implies field names have meaning) 4.2.6: several examples of relational-algebra queries Q1: example of query optimization Q2: more query optimization Week 5, Sept 22 Q4: note name is not a key! Q5, Q6: note asymmetry regarding OR and AND. Q7: At least *two* boats Q8: NOT example Q9: division example Some TRC Chapter 5: SELECT...FROM...WHERE... DISTINCT basic SQL examples Sept 24: NATURAL JOIN operator: list all sailor/boat reservations Do Q6 without INTERSECT Do Q6 without worry that sname is not a key more on such queries 5.4.1: nested queries, mostly as a natural way of handling these examples 5.4.2: sailors who have reserved boat 103: somewhat strange 5.4.3: set-comparison operators ALL, SOME problem with ANY 5.4.4: more red/green 5.5: aggregate operators: count, sum, avg, max, min ============================================================= Week 6, Sept 29 oracle demo ic - foreign key constraint some nested queries intersect 5.5.1: group by each group appears *once* in output: pathological examples detailed analysis of q32 more aggregate queries: 5.5.2 null values; 3 uses of null (2 kinds of unknown); true/false/unknown JOIN, NATURAL JOIN, NATURAL LEFT OUTER JOIN CHECK constraints in CREATE TABLE, for a single table - pp 165 doesn't work in MySQL CREATE ASSERTION Triggers: Chapter 19: Problems of Redundancy - 19.1 SNLRWH table, but W (wages) is determined by R (rating). IC as a _functional dependency_ redundant storage update anomalies * insertion anomalies deletion anomalies =================== redundancy issues Example: hourly_emps(ssn, name, lot, rating, hourly_wages, hours_worked) (s, n, l, r, w, h) rating=>hourly_wages is a functional dependency update/insertion/deletion anomalies null can be used to allow insertion of employees where hourly_wage isn't known yet. normal forms lossless-join dependency-preservation functional dependencies Let X and Y be attributes of a relation R. We have a functional dependency X->Y if t1.X=t2.X => t1.Y = t2.Y for all t1, t2 Examples: [name, address, order#, date, item, quantity] October 1: closure of a set of FDs; Armstrong's Axioms F+, the closure of a set F of FDs. derive union, decomposition rules CSJDPQV example on page 613 C Contract S Supplier J ProJect D Dept P Part Q Quantity V Value 1. C -> CSJDPQV 2. JP -> C (project+part determines contract) 3. SD -> P (supplier + dept determines part) 1NF: attributes are atomic Boyce-Codd NF: no nontrivial FDs at all 3NF: definition: no partial , no transitive dependencies (define) SBDC, with S->C, C->S Allow FDs of the form X->A where A is a *subset* of a key for R. partial dependency: X-> where X is a proper subset of some key K. SBDC from before: SBD is the key, and S->C, a partial dependency. transitive dependency: X is *not* a proper subset of any key K. Then there is a K so K->X->A. Example: SNLRWH: S is the only key, but we have R->W, hence S->R->W. SBDC example woth S->C: This is not BCNF, or 3NF as written. But if we add C->S too, then CBD is a key, hence S->C is allowed by 3NF. 2NF: basically disallows partial dependencies, allows transitive. -------------- lossless-join rule: can get original relation back by joining the subtables. Pretty important! Theorem 3, pl 620 F+ must contain either R1 intersect R2 -> R1 or R1 intersect R2 -> R2. SNLRWH example -------------- dependency-preserving decomposition. if we decompose CSJDPQV into CSJDQV and SDP to address the SD->P dependency, we have a problem enforcing JP->C. Dependency preserving: Let FX = dependencies in F+ that involve only attributes in X. dependency preserving if: (FX U FY)+ = F+ Closure: consider ABC, where A->B, B->C, and C->A, and decomposition into AB and BC. ============================ BCNF algorithm (may not be dependency preserving), p 623 Start with a relation R and a set of dependencies. If R is not BCNF, find a dependency X->A violating BCNF where A is a single attribute. Factor R into relations R-A and XA. Continue if either of these is not BCNF. Tree version, applied to CSJDPQV. Alternative tree, bottom of page 624 ------------ no good BCNF decomposition: SBD, with SB -> D and D->B: not BCNF because of D->B. BUT decomposing into SD and DB means we lose SB->D. ------------ ======================= Minimal Cover Algorithm, p 625: minimal cover for a set F of FDs is a set G of FDs so 1. every dependency in G is of form X->A for single attribute A 2. F+ = G+ 3. G is minimal in size and in attributes sets ABCDEFG example, with A->B, ABCD->E, EF->G, EF->H, ACDF->EG, p 625 Note that we have an algorithmic way of telling if one FD is implied by (is a consequence of) a set of other FDs; this amounts to asking if one FD is in the closure of the set. This may be done by reasoning via Armstrong's axioms, or with the attribute closure algorithm of page 614. Now we start the Minimal Cover Algorithm itself. Step 1: put all FDs into standard form, that is, with a single attribute on the righthand side. Step 2: Minimize the lefthand side of each FD. That is, if we have AB->C and A->B, then replace AB->C with A->C (because A->B, so A->AB->C). To do this, we remove attributes one at a time from the LHS and test if the result is still implied by the original set. That is, if the FD ABC->D is given, check if BC->D, AC->D, and AB->D are implied by ABC->D together with the other original FDs. If so, replace the ABC->D with the simpler, and continue. Step 3: Delete redundant FDs. Again, this means checking each FD in turn to see if it is implied by the others. Note that Step 3 must be done *after* step 2. =============================== 3NF algorithm, p 627 First apply the BCNF decomposition of page 623: * Let X->A be an FD that causes violation of BCNF. Decompose R into R-A and XA. * Continue for all dependencies in a minimal cover. Then make it dependency-preserving: * identify dependencies X->A not preserved, A atomic * add XA table NOTE THAT WE NOW HAVE CASES WHERE LOSSLESS-JOIN IS PRESERVED, BUT NOT *PAIRWISE*. CSJDPQV example again How do we know JP->C is not preserved? end of class Oct 1, review again Oct 6 ABCDEFG example: AC->E, E->D, A->B, AC->D Tables: AB, ED, ACE, ACFG =============================== Synthesis Algorithm, p 628 * now we patch up lossy-join! Given a set of FDs in minimal-cover form, create a relation XY for each FD X->Y. If none of the XYs contain a key for the relation, also add a relation K consisting of key fields. This last step (that I forgot at first in class) is what makes the join lossless. ABC example with A->B, C->B Get AB, BC, AC not *pairwise* lossless! Note that without the key, AC, the join is not lossless. ER Design issues: 19.7.1: can fix by creating Wage_Table, *but* we otherwise can't express the R->W dependency! 19.7.2 example: CQPSD, with DS->P (C is key). Easy to split into CQSD and SDP, but what kind of entity is CQSD? 19.7.3: attributes v entities Works_in, and now add dependency did->lot. Lots are really part of depts, not employees, now! ER can and does miss this! 19.7.4: identifying entity sets: Reserves, with credit cards, and S->C (every sailor has one credit card, max) But we wouldn't otherwise model credit cards as entities! 19.8: multivalued dependencies: overview, and comment re keys 19.9: DBDudes example: problem with Orders Exam Oct 20, not Oct 15 Exercises Chapter 5 2, 3.1-3.6, 3.9 Chapter 19 2, 4.1, 6, 7, 8.2 Chapter 8 Wed, Oct 8 Storage and indexing nature of external disk space: pages are very expensive! records each have a record id, or rid (sometimes just a seq#) heap files: just a bunch of records sorted files: an alternative, very expensive! external indexes: what they are search keys (not necessarily table keys!) data entry: entries in index file, denoted k* where k is the key. three options: 1. data entry k* is the actual record, ie the index *is* the file 2. data entry k* is 3. data entry k* is clustered index: index ordering matches file ordering means similar records (in the sense of the ordering) are likely to be on the same page. clustered file: index entries *are* table entries; index is the table! primary and secondary indexes: vague terms indexes for multiple attributes attributes don't have to be candidate keys! Week 8, October 13 1. Oracle is now available. Use account system/oracular Please respect the fact that all mods are persistent!! 2. Remark re homework! hash indexing: basic ideas. Can only be used for equality searches. When don't we need < searches? SSNs, maybe. Also equijoins. Double-hashing example on p 280; note hash function. Note the file is organized by hash of age (a good compromise between heap and sorted files) salary example; age/salary indexes tree indexing fat trees: basics B-trees: insert/delete linked lists; why links are a problem generally and when they are ok cost models: scan, search=, search< heap file, sorted file, CLUSTERED FILE, heap file with unclustered tree index 8.5: clustered index organization (8.5.2, actually) not cheap! no clustered file using hashing (not useful) index-only evaluation Age>40 example: 90% hits: may as well use a scan 10% hits: scan might still be better if records are scattered "hobby" index lots of stamp collectors: retrieval (in random order) via unclustered index can be $$$. Few collectors: much better approach. end of class Oct 13 October 15: discuss homework 2, mention anomalies (no 2NF, no lossless-join algorithm) solutions online late Friday discuss exam 1 a little: ch 2: ER model ch 3: keys, key constraints, ER-to-tables ch 4: relational algebra Section 4.3, relational *calculus*, will *not* be included!!! ch 5: SQL basic sql union, intersect, except 5.4: nested queries, in, not in, exists, every, some/any 5.5: aggregation (GROUP BY) 5.6: NULL 5.7: complex ICs (5.8, on triggers, will not be included) Chapter 19 19.1: overview 19.2: FDs 19.3: working with FDs: closure, Armstrong's axioms, **Attribute Closure** 19.4: normal forms: 3NF, BCNF, kinds of dependencies 19.5: decomposition, lossless-join theorem 3 19.6: actual decomposition algorithms. Note that minimizing FD lefthand-sides, and finding keys, both use the Attribute Closure algorithm. 19.7: what normalization has to do with ER diagrams Chapter 8: dno index: example on p 295 is an index-only query composite keys: matches rule: hash: equality only. tree: range/equality on a prefix figure 8.5: sal, age, and sal+age indexes two queries: 20<=age<=30 AND 3000<=sal<=5000 age=25 AND 3000<=sal<=5000: index is ok, but is not index-only example, p 297 bottom, example on p 298 SQL: CREATE INDEX IndAgeRating ON Students WITH STRUCTURE = BTREE KEY = (age, gpa) October 22 Ch 9: memory hierarchy disks disk geometry and reading; elevator algorithm system crashes raid 0: striping raid 1: mirroring raid 5: parity. reading a block recovering from a bad disk writing a block; bottleneck; raid 4 v raid 5 OS filesystem locks, buffering, permissions buffer manager request/release semantics: barely applicable to conventional filesystems! pin_count, dirty compare page buffering policies with virtual memory page-replacement policies! LRU: problem: keeping track of usage. pin_count does help there, enormously clock algorithm: add a _referenced_ bit to each page, set to 1 when pin_count==0. Scan in a circular fashion for a page with _referenced_ == 0; any pages passed over in the scan with _referenced_==1 have it set == 0. OS v DBMS: OS doesn't have any knowledge of page-usage patterns specific to the DBMS. sequential scans (OS *can* guess here; cf unix algorithm) relational operator analysis: can sometimes predict prefetch! Need to force physical writes File implementations * linked list * directory Records fixed length: rid = typically. Deletion, insertion variable-length: more complex. per-page directories blobs: big fields (often stored separately) max record size CPU architecture: DBMSs and SMP ------------------------------- Tree indexes fat trees ISAM trees overflow pages locking issues: ISAM tree pages don't need to be locked even during updates October 27 B+ trees (Bayer); aka VSAM d = degree means every node except top has m entries, d<=m<=2d. search insert: 10.5 insert 8* leaf split: fig 10.11 copying up 5 parent is also full! So split parent! Note tree stays of uniform height! sibling rotation variant: last paragraph on p 350 note I/O costs! Hashing -- chapter 11 basic strategy of primary buckets and "overflow" pages Hashing statistical analysis Extendible hashing: directories, splitting some buckets. Relies on variable # of bits in hash function output Linear hashing: again, h_n() is a hash function returning n bits. ============================================== Chapter 12: query evaluation catalogue selections in CNF; index matching access path selectivity uniform-distribution hypothesis selection 12.3.1 scan if there is no index that matches sometimes scan even if there is an index scan v unclustered index 5% rule projection main cost is duplicate-elimination October 29: send Maulin Patel all assignments, reading notes Selection (chapter 14) Here are some situations we might face: No index, unsorted data: need full scan No index, sorted data: need binary search for starting point, scan from then on as needed B+Tree index: clustered: ~1 page unclustered: maybe lots of pages; trick: sort by page# rname < 'C%' example, p 443: expect 100 pages, or 1E4 tuples Hash index: 100 reservations by "Joe"s: 5 pages? 100 pages? CNF again term: expr relop expr expr: fieldname or expression best case: term1 and term2 and term3 ... general case: (term11 or term12 or term 13) and (term21 or term22) and ... MATCHING only applies to the "best case" (which is common...) Projections sorting hashing spread tuples into N buckets via hashing do duplicate-elimination on each bucket, often with 2nd hash function h2 then we're done join: (p 402) outer (first) relation reserves 100/page, 1000 pages sailors 50/page, 500 pages basic nested-loops join: p 454 tuple-at-a-time join: 5x10^7 (10e5 reserves x 500 pages of sailors) page-at-a-time join: 501,000 (block nested loops join, p 455) foreach *page* of reserves, scan all sailor pages 1000 * 500 index nexted loops join: 221,000 p 457 and p 403 (10e5 reserves, one sailor for each, found with hash, hash cost 1.2 pages (B+Tree ~3 pages), data cost 1.0 pages) 221,000: scan reserves, look up each sailor (1 + 1.2)*100,000 Reserves.sid index: scan sailors, retrieve matching Reserves tuples. 500 scan of REserves 40000*1.2 lookups 40000 clustered index (all reservations on one page) 100000 unclustered index (2.5 reservations per sailor) sort-merge join, p 403 & later sort both, then join in linear time. 4000 + 2000 + 1000 + 500 = 7500, first two terms are sort times non-incremental! hash join Partition R into k buckets Partition S into k buckets for each i5" selection, since then sailor index can't be used join: 1200 I/O's (1000 tuples * 1.2 avg hash page-I/O cost), plus 10 more for other stuff 12.6: optimizers pushing selections ahead of joins left join brief note on multi-joins: typically each join is on a different field optimizers typically consider only left-deep plans * too many alternatives otherwise * need inner tables to be "materialized"; outer tables can be pipelined Nov 10 optimizers need inner tables to be "materialized"; outer tables can be pipelined 14.2.2: Selection without disjunction c1 = foo and c2 = bar and c3 < baz single index (on one of the columns); then filter the results multiple index: look up in indexes for c1, c2, and c3 get set of rids for each lookup intersect these then filter the usual way (if necessary) we probably want to sort with disjunctions: if even one disjunct requires a file scan, then we might as well scan for everything; Example 1: index on rname and on sid, and selection condition is rname = 'Joe' OR date < 8/19/2003 scan is the most selective path! Example 2: but the following is different (rname = 'Joe' OR date < 8/19/2003) and sid=1009 In this case we can use index on sid, and scan the output of that. Example 3: if we have an index on date, the first example can use indexes followed by taking a union Chapter 15: query optimization decomposing queries into blocks selection-projection-product inner part: p 481 Strategy: evaluate sel-proj-x part, keeping eye on the order for GROUP BY, etc. Estimation of plan costs simple selection: estimate reduction factor for each term; assume independent so total reduction is product. reduction for "column = value": 1/NKeys if there is a key, 1/10 heuristically if not. NKeys = total # of index keys; assume all keys are equally likely. other reductions on page 484: col1 = col2 col > value col IN list histogram recordkeeping (attached to tables) equiwidth, equidepth 15.3: relational algebra equivalences selections are commutative and sigma(_c1 and _c2) is the same as sigma_c1 composed with sigma_c2. Other examples are in the text Plan enumeration: list plans, estimate the cost of each, keep the cheapest. single-relation queries (no joins). need to consider whether GROUP BY, etc will later require data be sorted 0. no-index-use: do a scan. Not a lot of options. example query: select s.rating, count(*) from sailors s where s.rating > 5 and s.age = 20 groupby s.rating having count distinct (s.sname) > 2 indexes: B_ tree on rating hash on age B+ tree on 1. single-index-use: choose index that is most selective (expected to retrieve the fewest pages) Example: use hash index on age 2. multi-index-use: apply multiple indexes, then intersect the ridsets. Example: get rids of s.age=20 using age index, rids of rating>5 using rating index, then intersect 3. Sorted index access: used for GROUP BY: if grouping attributes is a prefix of a tree index, that index can be used to retrieve tuples in the order required by GROUP BY. Example: use rating index counts can be done on-the-fly wrt the GROUP BY clause 4. index-only cases Example: use the index to retrieve entries with rating>5 (can't do age=20... why?) select entries with age=20, on the fly count snames, get answer without going to the table itself. ================================================================== Nov 12 15.4.2: multiple-relation queries, p 496 SELECT ... FROM R1, R2, .. Rn WHERE term1 and term2 and term3 and ... Pass 1: enumerate all single-relation plans in effect we are considering alternatives for where to *start*. When considering relation R, we look at WHERE terms that ask only about R attributes; in particular we don't look at join terms. We keep the cheapest plan for each defined output ordering of tuples! Pass 2: 2-relation plans outer relation: output of Pass 1 inner relation: any of the other Ri, call it B identify: B-only WHERE terms; these are selections we can push ahead of the join join terms selections that can be applied later Note use of relational algebra equivalences outer relation is either pipelined in, or else we must take into account the cost of materialization. Consider: each A from Pass 1 each B from the remaining relations each join method on our designated list Example: p 499ff. Indexes: B+ tree on Sailors.rating hash index on Sailors.sid B+ index on Reserves.bid Pass 1: Sailors: 3 access methods, selection rating>5. Keep , Pass 2: outer=reserves: Need Sailors tuples satisfying rating>5 AND sid=value Can use B+ index on rating>5 or hash index on sid=value We choose the latter for index-nested-join. sort-merge join: no access methods sort by sid, so we would need to allow for that in the cost model. sort-merge join produces output ordered by sid, but we don't need this so we don't retain it unless it's cheapest. outer=sailors: Need Reserves tuples satisfying bid=100 AND sid=value. 3-way example, p 502 Note possibility of full-cross-product intermediate joins. Oops! 15.5 Nested subqueries Example 1: Select s.sname from sailors s where s.rating = (select max (s2.rating) from sailors s2) Inner query is a SINGLE NUMBER Example 2: Select s.sname from sailors s where s.sid in (select r.sid from reserves r where r.bid = 103) Inner query is a SINGLE TABLE, T. However, it's an on-the-fly table, with no evident index. NOte that having T be the outer table in the join with S might be good, but query optimizers generally do not discover this. Now consider Example 3: select s.sname from sailors s where exists (select * from reserves r where r.bid=103 and s.sid=r.sid) Same query, different version. But now we have a different inner table T[sid] for each different sid value in s. Problems with nested queries generally, and optimization in particular: Same sid value might come up multilple times, causing us to evaluate the same T[sid]. sort-merge join and hash join are not possible Example 2 issue: maybe we should do index join using Reserves as inner relation, with index on reserves.sid. But we can't, since we filter first on reserves.bid. Moral: consider finding the following equivalent version yourself: Example 4 Select s.sname from sailors s, reserves r where s.sid = r.sid and r.bid = 103 =================================================================== Nov 17 TRANSACTIONS - Chapter 16++ Sets of updates, executed as a unit ACID: Atomic Consistency of DB is preserved Isolation: transactions are isolated from one another; execution is *as if* transactions were serialized Durability: result should be permanent, even if system crashes Consistency example: transferring money between accounts Consistency: basically refers to preservation of *invariants* Isolation: note that may leave DB in different state than Sometimes a transaction may be aborted, in which case the atomicity requirement means we must undo whatever parts had been done. Atomicity+durability together imply crash recovery Abort/Commit: every transaction ends in one of these. Basic assumptions about transactions: they don't interact directly with each other, and the DB consists of a fixed collection of objects (ie no insertions). First isn't serious; second is tricky but we can relax it later. SCHEDULEs: list of read/write actions, eg T1 T2 R(a) W(a) R(b) W(b) R(c) W(c) complete schedules: end in commit or abort serial schedule: one where transactions don't overlap serializable schedule: one equiv to a serial schedule Why concurrency? overlap cpu with I/O Different I/O takes different lengths of time Long transactions shouldn't lock out short ones Serializable example on page 525: equiv to . Fig 16.3: serializable to Anomalies: WR, RW, WW WR example on page 527: one transaction transfers money, another increments by 6% RW example: read is unrepeatable Example: read # of items in stock, make decisions based on that: T1 T2 R(a) R(a) W(a) W(a) This is equiv to ; T2 gets lost! WW example: uses blind writes Aborted transactions A serializable schedule is one whose effect on any consistent DB is guaranteed to be equiv to some complete serial schedule of the *committed* transactions. Aborting the example on p 529 Note that there is a major problem with T2 having committed. We could have aborted T2 also, earlier, but it's too late now. If transactions read only changes by committed transactions, life is muuuuch better! LOCKING Strict 2PL shared locks on read objects; exclusive locks on write objects. blocking shared locks can be upgraded to exclusive later. Locks are not released until the end of the transaction. examples on page 532 Deadlocks timeout detection fancy algorithms requesting locks in order good case: we time out while waiting for locks, but haven't done anything yet bad case: we do some stuff, decide to do more, request lock, and then have more to unroll. Example: employee processing, with extra for managers performance of locking aborts: can be expensive, but hopefully are rare (although sometimes designing for bottleneck reduction is important) blocking is a bigger performance hit. Thrashing: point when more transactions leads to *less* throughput. (Different from OS thrashing.) Creating and terminating transactions COMMIT/ROLLBACK Note ROLLBACK goes back to previous SAVEPOINT. COMMIT AND CHAIN / ROLLBACK AND CHAIN 16.6.2: what should we lock? T1: select min(s.age) from sailors s where s.rating = 8; What do we lock? The entire table of Sailors? What if transaction T2 modifies the age of a sailor with rating 8? What if T2 increments the rating of sailors? What if T2 adds new sailors with rating 8? This is why we originally made the "no new objects" assumption. Too much locking hurts concurrency! Avoid table locks; we want higher *granularity*. Phantom problem: new objects being added. Nov 19: (just started the following) ==================================== SQL access modes: READ ONLY READ WRITE SQL isolation level modes: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE SERIALIZABLE is safest: Strict 2PL locking is used, including locks on potential new entries. Avoids dirty reads, unrepeatable reads, and phantoms. REPEATABLE READ: T reads only changes by committed transactions. T is serialized wrt other transactions on existing entries. However, there is exposure to the phantom problem. Locks everything SERIALIZABLE locks except for index locking. CRASH RECOVERY basic goals: making sure we can pick up where we left off, with a consistent DB, after OS kernel crash/panic Disk failure (within some limits) Atomic-write assumption Stealing/forcing: "stealing" is the writing to disk of a buffer page *before* the transaction commits! What this means / why we would do this Forcing is the immediate writing to disk of all buffer pages *immediately* upon a transaction's COMMIT. Safest: no-steal, force no-steal: transaction changes aren't written to disk until we commit force: transaction changes *are* written to disk as soon as we commit Drawbacks of no-steal: need lots of buffer pages Drawbacks of forcing: lots of I/O time And note that no-steal+force *still* requires a recovery mechanism. Real world: steal + no-force Basic WAL property: we have access to stable storage "guaranteed" to survive panic/crash. Chapter 17 Conflict-equivalent: Conflict-serializable what it is implies serializability, but not vice-versa Fig 17.1 example: Note that it is also equiv to T2,T1,T3, though not conflict-equiv to this either due to R(A) Precedence graphs; precedence graph for fig 17.1 Theorems: A schedule S is conflict serializable iff its precedence graph is acyclic. (Get topological sort of graph, execute in that order) Variant of Strict 2PL: nonstrict 2PL: a transactions can release locks before the end, BUT cannot request more locks once it releases *any* lock. Nonstrict 2PL => serializable. Transaction serial order is that in which transactions get all their locks. Strict schedule: values written by each transaction T are not read or overwritten until T either aborts or commits. Strict schedules are good. Strict 2PL implies each allowed schedule is strict! Nonstrict 2PL allows nonstrict schedules; ie allows situations where we need cascading aborts. Actually, nonstrict 2PL allows nonrecoverable schedules: T1 writes A, T2 reads A and commits, T1 aborts. View serializability: fig 17.1 is VS to view serializability + NOT conflict serializability => blind writes strict2PL + downgrades: reduces deadlocks, same semantics as strict2PL. (compare to lock upgrades, which can lead to deadlock) sometimes it helps to introduce UPDATE LOCKS. Compatible with shared locks, but only one UPDATE lock can be held. 17.2, 17.3: SKIP 17.4: DEADLOCKS Waits-for graph (Waits-for graphs do *not* work for deadlock detection in general OS case where there can be multiple instances of resources.) Deadlock prevention: one way is to not let "higher priority" transactions ever wait for lower-priority: wound-wait says abort lower-priority if this happens (wait-die kills lower-priority waiters) Conservative 2PL: get all locks at outset Problem: not knowing what they are!!! Specialized locking: INDEX LOCKING: if we lock the index page for rating=8, we lock all new records (or updated records) having rating=8. B+ trees and locks: When we lock a non-full child node, release lock on parent. Because child is non-full, we will *not* need parent even if we find later we do an insert. Optimistic concurrency control: Do the reads make updates to private space VALIDATE: see if these conflict with anyone else. Write out the changes, if validation passed ARIES: recovery algorithm compatible with steal: some uncommitted pages may be written to disk no-force: some committed pages may *not* be written to disk On post-crash DB startup: Analysis, Redo, Undo Analysis: identify which pages need updates, and which transactions were active at the time of crash. Redo: repeat all actions that were not on disk (both committed but not written, and also uncommitted-and-in-progress). Undo: abort transactions that were in progress but not committed. Fig 18.1: update/commit/end records. Note T1 and T3 are "active". Aries does a lot in the Redo phase that will later be Undone. Some find this inefficient. Note the Redo phase needs to restore some locks! The Log every log record has a Log Sequence Number, LSN. log tail is periodically forced to disk every page in the DB contains the LSN of the most recent log record describing a change to that page. THis pageLSN is stored in the page. Log records (p 583): UPdating a page. 1. write to the page, in the buffer pool (ie not forced) Page stays pinned throughout all this. 2. Add an UPDATE record to the log tail. Do not force. 3. Update the pageLSN Commit: add a commit record, and FORCE-WRITE THE ENTIRE LOG TAIL UP TO THIS POINT. This is the only force-written log record (though see buffer-write note below re UPDATE records) Aborting a transaction: write an abort long record, with transaction id End: After the commit, there is still some modest cleanup to do (eg deleting the transaction table entry) Undoing an update As part of the Undo process, we undo each Update record for a transaction, and write corresponding "Compensation log records", or CLRs, to indicate the update was undone. CLRs are never themselves undone (ie there is no "redo" operation) Records contain fields prevLSN: previous LSN about *this transaction* transID: transaction ID type: which of above types Update record on page 584: what fields are what Table on page 586 buffer writes: before writing any *page* to disk, we force-write every UPDATE log record that describes a change to the page. We do this by force-writing the entire log up to (and including) the point with LSN = pageLSN. (Maybe it's already written!) These two log-write rules are crucial. Note committed now means "transaction whose COMMIT record is written to disk" Force approach: write modified pages, rather than the log up to the commit record, to commit. Typically this is much larger! every dirty page has an associated recLSN, = LSN of 1st log record causing page to become dirty. The recLSNs stored in the buffer pool are lost along with everything else when the system crashes; we recover them in REDO. ================================== November 24 force-write log on: COMMIT, physical page writes ARIES peculiarity: we redo all the "losers" (transactions that never committed), only to eventually undo them! One justification: B tree updates. If we need to undo a "push-up" operation, it may now be moved to a new page by other, committed, inserts! Note dependence on strict 2PL locking! Recovery ANALYSIS Start at beginning, or at most recent CHECKPOINT (more likely), and scan to end of log. Find: * list that includes all dirty pages in buffer pool (perhaps others) * point at which to start REDO phase (nominally the point of the oldest recLSN that was in buffer pool before crash) * All transactions that need to be undone Start at dirty page/transaction checkpoint record, or beginning. Transactions are added if a record for them other than END is found, they're removed if END is found. Note is made if COMMIT is found. If a record about page P is found (other than non-redo), record P as maybe dirty. Keep lists of potential changes to P. New entry recLSN is *oldest* LSN refering to this page. End of analysis: Transaction table is good Dirty Page table may be too big; ok End-write log entries would clear up ambiguities, but isn't important. Example of fig 18.3 again, with two new records: T2000 commits, T1000 modifies page 700 Final UPDATE record is lost, but so is page modification!!! Some pages may have been written, but that's ok. end_write records would help minimize new dirty-page table; checkpoints help more! REDO reapply all updates, from point min(recLSN in reconstructed dirty-page table) Without checkpoints, this will be first UPDATE record in entire Log! For each update/CLR from that point on, REDO checks if any of the following hold: * affected page is not dirty * affected page *is* dirty, but (recLSN for the page) > current_LSN * pageLSN on the page >= current_LSN If none of these hold, we redo the operation & update pageLSN. No new log entries (but note physically writing the page from the buffer pool leads to an entry) UNDO: scan back from end of log. Identify LOSERS (transactions not COMMITted) in log. UNDO in reverse order Backing up a DB: a few issues Chapter 20: Physical DB design and tuning The first step is a workload description: a list of the top queries and updates, with frequencies, and ideally with performance goals. The workload *evolves* with time. It may also be hard to predict. Consider a reservations system; some people may find the right connections quickly; others may need to make lots of queries. Primary tuning tweak: INDEXES * choice of what indexes to create * clustered v unclustered * hash v B tree Other tweaks: * alternative normal forms * denormalization * adding views * transaction tuning (eg lock management) * rewriting queries (eg to avoid inner queries) Index selection: 4% rule: indexes should be at least 4% selective. Probably better. Indexes may (or may not!) *add* to cost of updates Sometimes only exact-match selections make sense (eg on SSNs, partnumbers, customer_ids, etc); hash is best for these. Hash is also good for joins. Consider indexes for joins and for selection-only. Influence of join indices can be gauged by looking at *changes* in query-optimizer strategies Multi-attribute indexes aren't really helpful for joins; they are useful when there is an important SELECTION that the index matches, or if the index allows an index-only evaluation of a query. Clustering: * range queries tend to benefit most, simply because there are more records likely to be retrieved in a given selection clause * indexes for index-only evaluation do *not* benefit from clustering Examples 20.3: Example 1: list employees & their mgr for the toy dept Select E.ename, D.mgr From Employees E, Departments D Where D.dname='Toy' and E.dno = D.dno D.dname='Toy' is likely to be very selective. A hash index on D.dname would be appropriate. We envision the number of hits would be small (only 1 Toy dept!) so clustering would *not* be helpful. An index on D.dno would *not* help (why?) Instead, we create a hash index on E.dno for the joins. This time, clustering *would* help: we'd get all the toy-dept employees on the same page! What happens if we add the clause "E.age = 25"? With and without an index on E.age? Example 2: Select E.ename, D.dname From Employees E, Departments D Where E.sal BETWEEN 1000 AND 2000 AND E.hobby='Stamps' and E.dno = D.dno We will *probably* want to do the selection on E first. E is thus likely to be the outer relation; we would need an index on D.dno to be useful. For E, a B Tree on E.sal would be useful, especially if clustered (why?) Hash index on hobby would be ok. A clustered index would be of use if lots of employees had stamps as a hobby. How about Example 3: Select E.ename, D.mgr From Employees E, Departments D Where E.hobby='Stamps' and E.dno = D.dno Compare to Example 1. In E1, we guessed D.dname='Toy' was likely to be selective. Now let us guess that E.hobby='Stamps' is *not* selective (ie philately is popular). We would need evidence to make this kind of guess, of course. At this point, we would need an index on D.dno for efficient index joining. Absent that, we might go with block or sort-merge joins. A B tree index on D.dno would help with sort-merge joins (and with index joins, of course). Cost model for clustering, page 660 Co-clustering: omit Example: parts, assembly list subassembly records right after each part# 20.5: index-only plans Example 1: Select D.mgr From Departments D, Employees E Where D.dno = E.dno Any Employee.dno index can work! Don't need clustering. Index-only on Employee. Now change SELECT statement to Select D.mgr, E.eid Now suppose we have an E.dno index. We still need to retrieve E.eid; clustering of E.dno would benefit. OTOH, suppose we have a B Tree index for E on (in that order). THis is a *range* selection: find all index entries matching . We can again do index-only evaluation. 20.6: Automatic index wizards: relatively common. Omit 20.7: skim if desired 20.8: query decomposition CSJDPQV, with C->CSJDPQV, SD->P, JP->C Not in BCNF Is in 3NF: SD->P, but P is part of the key JP BCNF: CJP, SDP, CSJDQV (SDP+CSJDQV is BCNF, but not DP) However, this decomposition may not help, particularly if we ask a query about Q, P, and C. Need a full join! Outright denormalization: Add a dept budget field B to Contracts relation. We have D->B, so we lose 3NF. But efficiency might still make this useful. 2.10: query tuning * revise queries with nested subqueries: see p 504 (15.5) * avoid use of OR * watch use of null, etc * reconsider use of DISTINCT * reconsider GROUP BY, as it implies a final sort. * break large transactions up into smaller ones (at a cost of atomicity) Course summary: See the summary from the midterm. Of the things covered on the midterm, the only things that will be on the final are SQL and *some* of chapter 19, eg 19.4 and 19.6. You will need to be familiar with the concepts of 3NF and BCNF, and the simpler algorithms necessary for decomposition into these. The final may also cover: Chapter 8: all Chapter 9: we did some of this, but it will *not* be on the exam! Chapter 10, B Trees: 10.1, 10.3-10.5 only Chapter 11: 11.1, 11.2, skim 11.3 Chapter 12: all (intro to optimization) Chapter 13: omit Chapter 14: 14.1-14.4 Chapter 15: 15.1-15.5 Chapter 16: all Chapter 17: 17.1, 17.4, 17.5.1, skim 17.5.2 Chapter 18: 18.1-18.6 Chapter 20: 20.1-20.3, 20.5, 20.8, 20.9 Chapter 21: misc topics