Database Operations - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Database Operations - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity! Database Management Systems 1 Docsity.com Objectives • How does a DBMS store data for efficient retrieval? • How does a DBMS interact with the file system? • What are the common database operations? • What options does a DBMS have for storing tables? • How is one data row stored? • How can you improve performance by specifying where data is stored? • How does a DBA control file storage? • What performance issues might arise at Sally’s Pet Store? 2 Docsity.com Table Operations • Retrieve data – Read entire table. – Read next row/sequential. – Read arbitrary/random row. • Store data – Insert a row. – Delete a row. – Modify a row. • Reorganize/pack database – Remove deleted rows. – Recover unused space. 5 LastName FirstName Phone Adams Kimberly (406) 987-9338 Adkins Inga (706) 977-4337 Allbright Searoba (619) 281-2485 Anderson Charlotte (701) 384-5623 Baez Bessie (606) 661-2765 Baez Lou Ann (502) 029-3909 Bailey Gayle (360) 649-9754 Bell Luther (717) 244-3484 Carter Phillip (219) 263-2040 Cartwright Glen (502) 595-1052 Carver Bernice (804) 020-5842 Craig Melinda (502) 691-7565 Docsity.com Deleting Data • Deletes are flagged. • Space is reused if possible when new row is added. • If not exactly the same size, some blank holes develop. • Packing removes all deleted data and removes blanks. 6 LastName FirstName Phone Adams Kimberly (406) 987-9338 Adkins Inga (706) 977-4337 Allbright Searoba (619) 281-2485 Anderson Charlotte (701) 384-5623 Baez Bessie (606) 661-2765 XBaez Lou Ann (502) 029-3909 Bailey Gayle (360) 649-9754 Bell Luther (717) 244-3484 Carter Phillip (219) 263-2040 Cartwright Glen (502) 595-1052 Carver Bernice (804) 020-5842 Craig Melinda (502) 691-7565 Docsity.com Data Storage Methods • Sequential – Fast for reading entire table. – Slow for random search. • Indexed Sequential (ISAM) – Better for searches. – Slow to build indexes. • B+-Tree – Similar to ISAM. – Efficient at building indexes. • Direct / Hashed – Extremely fast searches. – Slow sequential lists. 7 Docsity.com Insert into Sequential Table • Insert Inez: – Find insert location. – Copy top to new file. – At insert location, add row. – Copy rest of file. 10 ID LastName FirstName DateHired 8 6 7 2 Carpenter Eaton Farris Gibson Carlos Anissa Dustin Bill 12/29/2001 8/23/2001 3/28/2001 3/31/2001 4 5 9 3 1 10 Hopkins James O’Connor Reasoner Reeves Shields Alan Leisha Jessica Katy Keith Howard 2/8/2001 1/6/2001 7/23/2001 2/17/2001 1/29/2001 7/13/2001 ID LastName FirstName DateHired 8 6 7 2 Carpenter Eaton Farris Gibson Carlos Anissa Dustin Bill 12/29/2001 8/23/2001 3/28/2001 3/31/2001 11 Inez Maria 1/15/2002 4 5 9 3 1 10 Hopkins James O’Connor Reasoner Reeves Shields Alan Leisha Jessica Katy Keith Howard 2/8/2001 1/6/2001 7/23/2001 2/17/2001 1/29/2001 7/13/2001 Docsity.com Pointers • When data is stored on drive (or RAM). – Operating System allocates space with a function call. – Provides location/address. • Physical address • Virtual address (VSAM) – Imaginary drive values mapped to physical locations. • Relative address – Distance from start of file. – Other reference point. 11 Data Address Key value Address / pointer Volume Track Cylinder/Sector Byte Offset Drive Head Docsity.com Pointers for Indexes 12 Data Address Key value Address pointer File Start Key value Address pointer Data Address Index Docsity.com Insert into a Linked List • Get space/location with address. – Data: Save row (A97). – Key: Save key and pointer to data (B14). • Find insert location. – Eccles would be after Eaton and before Farris. – From prior key (Eaton), put next address (B71) into new key, next pointer. – Put new address (B14) in prior key, next pointer. 15 Farris B71 B38 A63 Eaton B29 B71 A58 Eccles B14 B71 A97 NewData = new (. . .) NewKey = new (. . .) NewKey->Key = “Eccles” NewKey->Data = NewData FindInsertPoint(List, PriorKey, NewKey) NewKey->Next = PriorKey->Next PriorKey->Next = NewKey B14 Docsity.com Binary Search • Given a sorted list of names. • How do you find Jones. • Sequential search – Jones = 10 lookups – Average = 15/2 = 7.5 lookups – Min = 1, Max = 14 • Binary search – Find midpoint (14 / 2) = 7 – Jones > Goetz – Jones < Kalida – Jones > Inez – Jones = Jones (4 lookups) • Max = log2 (N) – N = 1000 Max = 10 – N = 1,000,000 Max = 20 16 Adams Brown Cadiz Dorfmann Eaton Farris 1 Goetz Hanson 3 Inez 4 Jones 2 Kalida Lomax Miranda Norman 14 entries Docsity.com B-Tree • Store key values • Utilize binary search (or better). • Trees – Nodes – Root – Leaf (node with no children) – Levels / depth – Degree (maximum number of children per node) 17 Hanson Dorfmann Kalida Brown Farriis Inez Miranda Adams Cadiz Eaton Goetz Jones Lomax Norman A C B D E F G H I J K L M N Inez Key Data < >= Docsity.com B+-Tree Insert • Insert 257 – Find location. – Easy with extra space. – Just add element. 20 315 < <= 231 < <= < 287 <= 458 < <= < 792 <= 315 < <= < 347 <= 458 < <= < 692 <= 156 < <= 792 < <= 287 < <= 231 < <= < 257 <= Docsity.com B+-Tree Insert • Insert 532 – Find location. – Cell is full. – Move up a level, cell is full. – Move up to top and split. – Eventually, add a level. 21 231 < <= < 287 <= 692 < <= < 792 <= 156 <= 231 < <= 287 < <= < 315 < <= < 692 <= 347 < <= < 458 <= 315 < <= 347 < <= 458 < <= 532 < <= 692 < <= 792 < <= 315 < <= 231 < <= < 287 <= 458 < <= < 792 <= 315 < <= < 347 <= 458 < <= < 692 <= 156 < <= 792 < <= 287 < <= 231 < <= < 257 <= Docsity.com B+-Tree Strengths • Designed to give good performance for any type of data and usage. – Lookup speed is based on degree/depth. Maximum is logm n. – Sequential usage is fast. – Insert, delete, modify are reasonable. • Many changes are easy. • Occasionally have to reorganize large sections. 22 Docsity.com Storing Data Columns • Different methods of storing data within each row. – Positional/Fixed • Simple/common. – Fixed with overflow • Memo/highly variable text. 25 A101: -Extra Large A321: an-Premium A532: r-Cat ID Price QOH Description 4 110.00 Dog Kennel-Extra Large 18 1.00 1874 Cat Food-Can-Premium 29 6.00 240 Flea Collar-Cat ID Price QOH Description 4 110.00 Dog Kennel A101 18 1.00 1874 Cat Food-C A321 29 6.00 240 Flea Colla A532 Docsity.com Storing Data Columns • Different methods of storing data within each row. – Indexed • Fast access to columns. – Delimited • File transfer. 26 4, 110, , “Dog Kennel-Extra Large” 18, 1, 1874, “Cat Food-Can-Premium” 29, 6, 240, “Flea Collar-Cat” 012345678901234567890123456 1 4 5 41106Dog Kennel-Extra Large 2 3 7 1811874Cat Food-Can-Premium 2 3 6 296240Flea Collar-Cat Docsity.com Data Clustering and Partitioning • Clustering • Grouping related data together to improve performance. – Close to each other on one disk. – Preferably within the same disk page or cylinder. – Minimize disk reads and seeks. – e.g. cluster each invoice with the matching order. • Partitioning • Splitting tables so that infrequently used data can be placed on slower drives. – Vertical partition • Move some columns. • e.g., move description and comments to optical drive. – Horizontal partition • Move some rows. • e.g., move orders beyond 2 years old to optical drive. 27 Docsity.com Vertical Partition • In one table, some columns are large and do not need to be accessed as often. – Store primary data on high speed disk. – Store other data on optical disk. – DBMS retrieves both automatically as needed. • Products table example. – Basic inventory data. – Detailed technical specifications and images. 30 High speed hard disk Low cost optical disk Item# Name QOH Description TechnicalSpecifications 875 Bolt 268 1/4” x 10 Hardened, meets standards ... 937 Injector 104 Fuel injector Designed 1995, specs . . . Docsity.com Disk Striping and RAID • Redundant Array of Independent Drives (RAID) – Instead of one massive drive, use many smaller drives. – Split table to store parts on different drives (striping). – Duplicate pieces on different drive for backup. – Drives can simultaneously retrieve portions of the data. 31 CustID Name Phone 115 Jones 555-555-1111 225 Inez 666-666-2222 333 Shigeta 777-777-1357 938 Smith 888-888-2225 Docsity.com