DOC PREVIEW
Berkeley COMPSCI 186 - Midterm Review

This preview shows page 1-2-3-19-20-38-39-40 out of 40 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Spring 2007 Midterm 1 ReviewAdministriviaReview OutlineReview: DBMS componentsReview: ACID propertiesReview: Relational Data ModelReview: Bank of Middle EarthReview: Query LanguagesReview: Basic DDLRelational Algebra ReviewSlide 11Slide 12Relational Calculus ReviewSlide 14Slide 15Basic SQL QuerySet Operators in SQLSQL Review: Nested queryAggregate OperatorsSailors who have reserved all boatsReview: StorageDisks and FilesThe Storage HierarchyComponents of a DiskDisks are slow. Why?Disk Space ManagerBuffer Management in a DBMSBuffer ManagementBuffer Management – ReplacementReplacement PoliciesWhat is in Database Pages?How are records organized?How are pages organized?How are files organized?Several possible file organizationsIndexesClustered vs. Unclustered IndexB-Trees: a common, flexible indexAny Questions?See you here on Thursday…Spring 2007 Midterm 1 ReviewLectures 2-10Cow book Chapters 1,3,4,5,8,9,10,11Administrivia•Midterm 1 – in class this Thursday!–Closed book examination–You will be allowed one 8.5” x 11” sheet of notes (double sided). •Sample questions on class web siteReview Outline•Relational Data Model, Algebra, Calculus and SQL•Storage, Buffer Management and IndexesReview: DBMS componentsQuery Optimizationand ExecutionRelational OperatorsAccess MethodsBuffer ManagementDisk Space ManagementDB•Makes efficient use of disk space-> Think 300,000,000 accounts!•Makes efficient use of RAM-> Think 1,000,000 simultaneous requests!•Provides generic ways to combine data-> Do you want a list of customers and accounts or the total account balance of all customers?•Figures out the best way to answer a question-> There is always more than 1 way to skin a cat…!•Provides efficient ways to extract data-> Do you need 1 record or a bunch?Database application•Talks to DBMS to manage data for a specific task-> e.g. app to withdraw/deposit money or provide a history of the accountReview: ACID properties•A DBMS ensures a database has ACID properties:•Atomicity – nothing is ever half baked; database changes either happen or they don’t.•Consistency – you can’t peek at the data til it is baked; database changes aren’t visible until they are committed•Isolation – concurrent operations have an explainable outcome; multiple users can operate on a database without conflicting•Durability – what’s done is done; once a database operation completes, it remains even if the database crashesReview: Relational Data Model•Most widely used data model.•Relation: made up of 2 parts:–Schema : specifies name of relation, plus name and type of each column. •e.g. Students(sid: string, name: string, login: string, age: integer, gpa: real) –Instance : a table, with rows and columns described by the schema•Introduced data independence–Data layout on disk can change without affecting applications using the data•Keys contribute to data independence–Relationships are determined by field value, not physical pointers!Review: Bank of Middle EarthCustomerID Name Address AccountID314159 Frodo BagginsBagEnd 112358271828 Sam GamgeeBagShot Row13212442 Bilbo BagginsRivendell 112358Account IDBalance112358 4500.00132124 2000.00Give me an example of…•A super key for Accounts•Good primary key choices for both•A foreign key •A possible check constraintALTER TABLE ACCOUNTSADD CONSTRAINT CHECK_BALCHECK (BALANCE>= 0)Review: Query Languages•Query languages provide 2 key advantages:–Less work for user asking query–More opportunities for optimization•Algebra and safe calculus are simple and powerful models for query languages for relational model –Have same expressive power–Algebra is more operational; calculus is more declarative•SQL can express every query that is expressible in relational algebra/calculus. (and more)•Two sublanguages:–DDL – Data Definition Language•Define and modify schema (at all 3 levels)–DML – Data Manipulation Language•Queries and IUD (insert update delete)Review: Basic DDLCustomerIDName Address AccountID314159 Frodo BagginsBagEnd 112358271828 Sam GamgeeBagShot Row13212442 Bilbo BagginsRivendell 112358Account IDBalance112358 4500.00132124 2000.00CREATE TABLE CUSTOMERS (CustomerID INTEGER NOT NULL, Name VARCHAR(128), Address VARCHAR(256), AccountID INTEGER, PRIMARY KEY(CustomerID), FOREIGN KEY(AccountId) REFERENCES ACCOUNTS);CREATE TABLE ACCOUNTS (AccountID INTEGER NOT NULL, Balance Double, PRIMARY KEY (AccountID));•Why do we need NOT NULL?•What would happen if I executed these commands in this order?Additional operations:•Intersection ()•Join ( ) •Division ( / )Relational Algebra Reviewbid bnamecolor101InterlakeBlue102InterlakeRed103ClipperGreen104MarineRedsidbidday2210110/10/965810311/12/96Reserves Sailors BoatsBasic operations:•Selection ( σ ) •Projection ( π ) •Cross-product (  ) •Set-difference ( — ) •Union (  ) :tuples that appear in both relations.:like  but only keep tuples where common fields are equal.:tuples from relation 1 with matches in relation 2: gives a subset of rows.: deletes unwanted columns.: combine two relations.: tuples in relation 1, but not 2 : tuples in relation 1 appended with tuples in relation 2.Relational Algebra Reviewbid bnamecolor101InterlakeBlue102InterlakeRed103ClipperGreen104MarineRedsidbidday2210110/10/965810311/12/96Reserves Sailors BoatsFind names of sailors who’ve reserved a green boatσ ( color=‘Green’Boats) ( Sailors)π( sname ) ( Reserves)π( bid )π( sid )πsname ( ) sid sname rating age1 Frodo 7 222 Bilbo 2 393 Sam 8 27Relational Algebra ReviewFind names of sailors who’ve reserved all boats•First use division and renaming to find sids of sailors who reserved all boats•Then join result with sailors and project to get their namesρ (Tempsids, ) ( Tempsids Sailors)( sid,bid Reserves)π ( bid Boats)πTempsidssidbid day1 103 9/122 103 9/133 103 9/143 101 9/121 103 9/13SailorsReservesbid bname color101 Nina red103 Pinta blueBoatssidbid1 1032 1033 1013 103/bid101103=sid3Relational Calculus Review•VariablesTRC: Variables are bound to tuples.DRC: Variables are bound to domain elements (= column values)•Constants7, “Foo”, 3.14159, etc.•Comparison operators=, <>, <, >, etc.•Logical connectives -


View Full Document

Berkeley COMPSCI 186 - Midterm Review

Documents in this Course
Load more
Download Midterm Review
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Midterm Review and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Midterm Review 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?