Data Base Systems
Data Base Systems EEL 4852C
Popular in Course
verified elite notetaker
Popular in Electrical Engineering
This 6 page Class Notes was uploaded by Mr. Chelsie Bergstrom on Wednesday September 23, 2015. The Class Notes belongs to EEL 4852C at University of South Florida taught by Staff in Fall. Since its upload, it has received 6 views. For similar materials see /class/212715/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
ER Physical Design I Generally the Physical Design is invisible to normal developersDB users I The Physical Design is handled by the DBMS I Main goal of Physical Design is increased performance Review from Computer Architecture The following are ranked in order of retrieval speed to CPU each level is several orders of magnitude slower than the one above it and also much cheaper per unit of storage Main Memory Primary SSDFlash modern growing in popularity Hard Disk Secondary Tapes Tertiary SSDFlash is not only faster in IOPS and throughput than spinning mechanical disks but also is much more energy efficient Tapes are for archiving and not to be used for liveactive data Taps have a serious performance penalty as they are not Random Access devices Disk Hardware A Disk is a mechanical spinning patters on a single spindle Each Platter is dived into rotational tracks each track is divided into sectors usually 512 bytes each A floating head moves back and forth over the platters while the spindle rotates to read and write information to the disk These two mechanical movements create a significant performance penalty when compared to purely electronic storage in RAM or SSD I The latency incurred while waiting for the head to move is called Seek Time I The latency incurred while waiting for the spindle to rotate is called Rotational Latency A long strip of sequential data is best written to the disk on an direct sequence of sectors as it will be read off the disk with minimal mechanical delay DBMS Storage Layout The basic storage unit of a DBMS is called a block or a page the size of which is a fixed multiple of a disk sector Usually this is in the range of 4KB to 16KB this value is the smallest IO possible by the DBMS Some DBMS allow you to change this value Oracle does and others do not Tuples on Disk The tuple must be stored on disk inside these pages somehow There are a variety of methods to handle this 1 Fixed Length tuples An advantage of fixed length fields are that seeking the ith record does not require scanning the entire record A disadvantage is wasted storage space N Variable Sized tuples Special characters indicate when fields startstop in the record Also can be done using an array of field offsets at the beginning of the record which offers a efficient way to store NULL special do not know character Record FormIts Fixed Length F1 F2 F3 F4 Ease address Bl AddxessBL1IJ Information about field types same for all records in a le stored in system catalogs z Finding 139 th field does not require scan of recor Database Managemem Systems 32d R Ramaknarman and 1 Genre Record Foriimts Variable Length Two alternative formats elds is fixed 4 Field Fields Delimited by Special Symbols Count F1 F2 F3 F4 Array of Field Offsets E Second otters direct access to i th eld ef cient storage of nulls special dun t kmsz Value small directory overhead Database Management Svstems 3ea R Ramakrishna and I Gehrke z Tables on Disk Tuples must exist inside a page on disk as well Again we have a variety of methods to handle this 1 N Fixed Length tupe Pages The page is broken up into 39slots39 that each hold a single tupe They can be packed or unpacked where a bitmap inside the page indicates whether a slot is used 1 or unused 0 Variable Length tupe Pages Within the page keep a digest that holds pointers to each record During operation holes will appear in the data section of a variable length tupe page and we must 39compact39 it from time to time Page Formats Fixed Length Records numbex umbez PACKED oftecoxds UNPACKEDBH ILAP ofslots ZRecord id ltpage id slot gt Infirst alternative moviri g recordsforfree space managementchanges rid may not be acceptable Database Maxmgemenlsyslemsied R Ramak I Gaiaka 51mm and Page FOTIlI i S Variable Length Reco V LN 24 N 1 slots Fomtex to stall ofhee pace SLOT DIRECTOR 2 Can move records on page without Chan gin g rid so attractiveforfixed length records too Database Managementsl stems 32d R Ramaknaiman and 1 Gahxke 4 File Organization Tuple relations must be handled on disk as well to support DB operations such as searching deleting inserting etc We can do this using 1 A heap file unordered tuples Consider a linked list of pages search time 0n and insert time 01 A sequential file sorted tuples Within each page the tuples are sorted in a sequential 1 order The data is sorted so Binary Search can be used to perform DB actions search time 0lg n and insert time 0lg n U A hashed file tuple location decided by a hash function Performance depends on the hash function chosen but it can approach 0c constant c depending on hash function for insert and search Heap File Implemented as 17 List Pages with Flee Space The header page id and Heap file name must be stored someplace Each page contains 2 pointers plus data Database MmgemeniSyslemsSed R Ramsknslms and Gehrke a Heap File Usmg n Page Dzreeturi Header Pagel Page Data Dmcmm 1 The entry for a page can include the number of free bytes on the page The directory is a collection of pages linked list implementation is just one alternative Daiaiaaee Management 5V5tems led R Ramaknsiman and l Gehxke 5 Matt Wagner Database Notes 1021l2008 Functional Dependencies Functional Dependeng constraints on the relationship between attributes in the schema In RXlt RYlt R xev In all instances of R if two tuples have the same values in X then they should have the same values in Y A Key Relationship is a functional dependency FD 1 Determined on the schema level not relational instance eg Y depends on X or X determines Y 2 A generalization of the key concept 3 The initial set of FD s comes from the application customer boss requirements et al Ex SSN 9 determines Fhame Rules to Derive FD s Subset Property Axiom of Re exiVity If Y is a subset of X thenX gt Y Augmentation Axiom of Augmentation If X gt Y then XZ gt YZ Transitivity Axiom of TransitiVity If X gt Y and Y gt Z thenX gt Z These three constitute a complete set of rules though more rules may be derived from these three Ex R A B C G H I FA B A9C C69H 36 89H 1 A9H Yes via transitive A9 BgtH 2 A69 Yes via augmentation AG CG 26 transitive 3 C69H Yes CG lCG replace CG with H this C69H Closure of F all possible functional dependencies derived using 3 rules given initial set of F Database Normalization 2nd Normal Form 2NF if and only if given any candidate key and any attribute that is not a constituent of a candidate key the honkey attribute depends upon the whole of the candidate key rather than just a part of it 339 Norm Farm aw mm anw bath ufthequuwmg commons mm s The re ztmn R tame s m secund nurmz farm ZNF ufR m s 2 superkeyithzt sX 5 my 2 cznmdztekey are supersm thereuf W INF swezkerthzn ach mpamp Etc
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'