230 likes | 429 Views
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).
E N D
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
SQL2 Statements Defining COMPANY Schema (1) Elmasri, p. 245
Specifying Referential Triggered Actions Elmasri, p. 248
SQL DDL • Look in workbook at p. 22 and 27 • Translated ERD to DDL • Look at structure of scripts – spool, drops, creates, query catalog.
SQL DDL • Space gets allocated on disks so we need to pay attention to disk structure • DBA may have to “place” objects (today)
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
Characteristics of Disk Systems Stallings, p. 158
Disks Single-sided disk Disk Pack Elmasri, p 468
Multiple-Platter Disk Stallings, p. 160
Disk Data Layout Stallings, p. 156
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
Winchester Disk Track Format (Seagate ST506) Stallings, p. 157
Fixed and Moveable Head Disks Stallings, p. 159
Group of Sectors That Subtend the Same Angle Elmasri, p. 469
Interleaved vs. Simultaneous Concurrency Elmasri, p. 474
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
Use of Two Buffers for Reading from Disk Elmasri, p. 474
Double buffering • “…Eliminates seek time and rotational delay for all but first block” • So…improves performance!
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?