Data Base Systems
Data Base Systems EEL 4852C
Popular in Course
Popular in Electrical Engineering
This 7 page Study Guide was uploaded by Mr. Chelsie Bergstrom on Wednesday September 23, 2015. The Study Guide belongs to EEL 4852C at University of South Florida taught by Yicheng Tu in Fall. Since its upload, it has received 65 views. For similar materials see /class/212708/eel-4852c-university-of-south-florida in Electrical Engineering at University of South Florida.
Reviews for Data Base Systems
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/23/15
EEL4852 Final review Fall 2007 Dr Yicheng Tu I Chapter I Introduction 1 Desirable characteristics of database systems advantages over file systems 2 Instances and schemas II Chapter 2 Relational Model 1 Relational algebraic operations focus on rulesassumptions Select b c d e f g h Project both regular and generalized nion Set differenceintersection Cartesian pro u Rename Natural join theta join Division i Aggregate 2 Database modification b c eetion Insertion Updating III Chapter 3 SQL 1 Basic structure of SQL queries a b c d e 39seect39 39from39 and 39where39 clauses Their algebraic equivalence TupIe variables in SQL Maintaineliminate duplicates TupIe ordering 2 SQL set operations a b c Union Intersection Exce t 3 Aggregate functions a b 4 IIes a What are the different types of aggregates 39group by39 and 39having39 clauses ted subqueries Set membership and comparison b Testing for empty relations and the absence of duplicate tupIes IV Chapter 5 Other Relational Languages Query by example QBE a Indication of a particular attribute s value being displayed b Negation operator 0 Queries involving more than one relation V Chapter 6 ER design 1 Model components 2 Constraints a a Entity sets b Relationship sets Cardinalitiesonemanytoonemany b Participation1total partial 3 ERdiagram VI Chapter 7 Relational DB design 1 Features of good relational DB design a Redundancy Lossless decomposition 2 Functional dependency losure of FD sets 3 Atomicity and 15 Normal Form 4 BoyceCodd Normal Form 5 3rd Normal Form VII Chapter 11 Storage and File Structure 1 Storage a The pyramid b Hard disk performance measurements access time seek time latency 2 RAID a Why do we need it Reliability and performance b Strategies redundancy and striping c RAID 0 1 5 3 Sequential file organization free lists deletioninsertion VIII Chapter 12 Indexing 1 Basics a Primarysecondary index b Densesparse index 2 BTree Index Files What s in a leaf node nonleaf node How many of them Fanout b Insertion c Deletion d Search a Btree 3 B Tree Index Files 4 Hash index a Hash function overflow buckets overflow chaining b uniformrandom hash functions 5 Static Hashing 6 Dynamic Hashing IX Chapter 13 Query Processing 1 Query cost measurements 2 Selection algorithms a File scan index scan A1A7 b Their costs 3 Join algorithms and their costs a NestedIoopjoin b Block nestedloop join c Indexed nestedloop join d Mergejoin e Hashjoin X Chapter 14 Query Optimization 1 Transformation of relational expressions a Equivalence rules 110 focus on first 8 b Proof of rule 11 2 Left deepjoin 3 Basics of statistics estimation a Histograms XI Chapter 15 Transactions 1 ACID properties 2 Transaction states active commit abort failed partially committed 3 Serializability Conflict serializability b Test for conflict View serializability 4 Recoverablecascadeless schedules Locking asi a Types of locks b Deadlock and starvation 2 2phase locking XII Chapter 16 Concurrency Control 1 b cs asic 2PL b Lock conversion c Strict 2PL d Rigorous 2PL 3 Multiple granularity locking 39 idea explicitimplicit locks b More lock types intention locks c Compatibility matrix Deadlock prevention oundwait waitdie 5 Deadlock detection a Waitfor graph Je Xlll Chapter 17 Recovery 1 Data access a Inputoutput readwrite b buffer block disk block work area 2 Logbased recovery Basic idea b DeferredImmediate DB modification Redoundo 3 Checkpointing In addition to the homework problems I strongly suggest you solve the following problems from the book without reading their solutions 71721181231241251271281331351411421561581510162163 Final exam will be held at 100pm on December 13 2007 Thursday in room CHE 303 same room for our regular class meeting It will be a 2hour closed booksnotesedevices exam Good luck Database Systems 6 Summary 091108 Author Eduard Seibel Covered Topics 1 Theta EQUI amp NATU RALJOIN 2 Complete Set of R A Operations 3 DIVISION Operation 4 Grouping with Aggregation 5 Query examples 1 Thetal EQUI amp NATURAL JOIN o The general case of the JOIN operation is called Theta JOIN r M as E09rxs Examples Relation r Relations HIE c o E III a 10 a III B 10 a 3 20 b y 10 B 1 rDltAcDlt15S c D E or 1 or 10 a 3 3 10 a 2 rgtlt maul Elli 3 rNAZCS A B c D E or 1 or 10 a 3 2 3 10 a 3 2 3 20 b o EQUI JOIN is a JOIN operation with equality comparsions only see exaple 3 above 0 NATURAL JOINjoins two realtions by using overlapping attributes Corresponding attributes have the same names in both relations It is a implizitJOIN without specifing explicitly the JOIN condition Example Relation r Relation s A B C D B D E d 1 d a a a B 2 V a 3 a 3 Y 4 l3 b 1 a v d 1 y a 2 b 5 5 2 3 b 3 b a r S E r N rBsBrDsD 5 A B C D E Theta JOIN d 1 d a d a 1 B a v EQUI JOIN d 1 y a d NATURAL d 1 y a y JOIN 5 2 3 b 5 2 Complete Set of R A Operations The set of operations including n Projection a Selection Difference p Rename U Union and x Cartesian Product is called a complete set because any other R A expression can be expressed by combination of these five operations Slide 644 Example Do we need Intersection No we can derive it from these five operations rnsrus r s s r 3 DIVISION Operation The devision operation is applied tot wo relations RZ SX where X is a subset of 2 Let Y Z Y and hence Z XU Y that is let Y be the set of attributes of R that are not attributes of S The Result result of DIVISION is a relation TY that includes a tuple t if tuples tR appear in R with tR Y t and with tR X t5 for every tuple ts in S slide 645 Example 1 Relation r Relation s r s E n u L Li 2 ii i L i ii ii ii i ii Example 2 Relation r Relation s r s an ABC a ma II Vav O Q lt QQQgt UUmUmUmmU l l l Wl l l l H I B a a a a a a a a For what it is useful For example to get all employees who are working in certain projects more than one projcet see slide 646 4 Grouping with Aggregation Used to send statistical queries that summarize information from the data base tupeles or collection of tuples grouping gaAir r R A1 A2 A3 An a MAX MIN AVG SUM CNT Examples Relation r gSUMcr gsuwc COUNTAr ISUMC ISUMCCOUNTA B a 27274 C 7 7 3 10 It is also possible to group the tuples by certain attributes first and then apply aggregate operations Examples we use the same realtion r see above A ESUMCF A gsumicr A COUNT C d 14 B 13 5 Query examples Query 1 Find all the names and addresses oft all the employees who work for the Research department nFname Minit Lname Address ODnamez39Research39 EM Dno Dnumber DEPARTMENT Query 2 Find all the names oft all employees who have no dependents nFnamel Minit Lname ngsnEMPLOYEE nEssnDependent EMPLOYEE or nFname Minit Lname 39 nFname Minit Lname M Ssn Essn DEPENDENT
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'