1 / 22

SQL DDL

SQL DDL. Language used to define objects such as tables, indexes, primary keys, foreign keys Used by most relational databases. SQL Syntax (1). Elmasri, p. 289. SQL Syntax (2). SQL2 Statements Defining COMPANY Schema (1). Elmasri, p. 245. SQL2 Statements Defining COMPANY Schema (2).

linda-reese
Download Presentation

SQL DDL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL DDL • Language used to define objects such as tables, indexes, primary keys, foreign keys • Used by most relational databases

  2. SQL Syntax (1) Elmasri, p. 289

  3. SQL Syntax (2)

  4. SQL2 Statements Defining COMPANY Schema (1) Elmasri, p. 245

  5. SQL2 Statements Defining COMPANY Schema (2)

  6. Specifying Referential Triggered Actions Elmasri, p. 248

  7. SQL DDL • Look in workbook at p. 22 and 27 • Translated ERD to DDL • Look at structure of scripts – spool, drops, creates, query catalog.

  8. SQL DDL • Space gets allocated on disks so we need to pay attention to disk structure • DBA may have to “place” objects (today)

  9. Why important for us? • When we use DDL and say CREATE TABLE or CREATE INDEX, what happens? • What options does the engine provide? • Different engines may provide ability to control data storage option or index option

  10. Characteristics of Disk Systems Stallings, p. 158

  11. Disks Single-sided disk Disk Pack Elmasri, p 468

  12. Multiple-Platter Disk Stallings, p. 160

  13. Disk Data Layout Stallings, p. 156

  14. Tracks divided into Blocks • Blocks is where database people get involved because • 1. It is our unit of I/O (could be multiblock) • 2. We use it as our sizing unit • Disks are where we place objects • Assume dbcourse2 has 2k blocks

  15. Winchester Disk Track Format (Seagate ST506) Stallings, p. 157

  16. Fixed and Moveable Head Disks Stallings, p. 159

  17. Group of Sectors That Subtend the Same Angle Elmasri, p. 469

  18. Interleaved vs. Simultaneous Concurrency Elmasri, p. 474

  19. Double buffering • Shown on next slide • cpu starts processing a block once transfer to main memory is completed • at same time - disk I/O processor can be reading and transferring the next block into a different buffer

  20. Use of Two Buffers for Reading from Disk Elmasri, p. 474

  21. Double buffering • “…Eliminates seek time and rotational delay for all but first block” • So…improves performance!

  22. DDL creates objects on disk • Go back to idea of DDL and what happens • Relate DDL to disk layout here • What happens on disk when you execute DDL?

More Related