SQL: Part 2 (DDL) Lecture 4 SQL: Part 2 (DDL) 1.29.2016 1 - - PowerPoint PPT Presentation

▶
sql part 2 ddl
SMART_READER_LITE
LIVE PREVIEW

SQL: Part 2 (DDL) Lecture 4 SQL: Part 2 (DDL) 1.29.2016 1 - - PowerPoint PPT Presentation

Nov 10, 2023 408 likes •621 views

Wentworth Institute of Technology COMP2670 Databases | Spring 2016 | Derbinsky SQL: Part 2 (DDL) Lecture 4 SQL: Part 2 (DDL) 1.29.2016 1 Wentworth Institute of Technology COMP2670 Databases | Spring 2016 | Derbinsky Outline 1.

slide-1
SLIDE 1

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

SQL: Part 2 (DDL)

Lecture 4

1.29.2016 SQL: Part 2 (DDL) 1

slide-2
SLIDE 2

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Outline

  • 1. Transactions: BEGIN, COMMIT/ROLLBACK
  • 2. Schemas: CREATE/DROP/ALTER, USE
  • 3. Authorization: GRANT/REVOKE

1.29.2016 SQL: Part 2 (DDL) 2

slide-3
SLIDE 3

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Caution

Note that the specific syntax/functionality of all commands in this lecture are highly dependent upon the chosen DBMS (and possibly even the version) These slides should be taken as an

  • verview of common options; for actual

implementation you should reference DBMS documentation

1.29.2016 SQL: Part 2 (DDL) 3

slide-4
SLIDE 4

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Transactions

  • Review: ACID
  • In most DBMSs, each individual query, by

default, is a transaction

  • To group multiple operations:

– Start: BEGIN – End: COMMIT (default) or ROLLBACK

1.29.2016 SQL: Part 2 (DDL) 4

slide-5
SLIDE 5

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Schema Specification

SQL is used to create/edit/delete a …

– Database – Table – Column – Data type/domain – Primary/foreign/unique key(s) – Other (more later)

  • Index, view
  • Trigger, assertion
  • User, role, privilege

Schema description is stored in the catalog (sometimes represented/accessible as tables)

1.29.2016 SQL: Part 2 (DDL) 5

slide-6
SLIDE 6

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Database

CREATE { DATABASE | SCHEMA } [IF NOT EXISTS] database_name; DROP { DATABASE | SCHEMA } [IF EXISTS] database_name; After, common to need a USE database_name

  • r similar statement to indicate active database

context (in multi-database DBMSs)

1.29.2016 SQL: Part 2 (DDL) 6

slide-7
SLIDE 7

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Table

CREATE TABLE [IF NOT EXISTS] table_name ( column_name1 TYPE [OPTIONS], column_name2 TYPE [OPTIONS], {constraint}, … ); High-level notes

  • If an option applies to a single column, it can go with the column;

else separate entry, or sometimes separate command

  • Separate elements may/not have name (for later manipulation)
  • Change: ALTER TABLE table_name ADD/ALTER/DROP …;

1.29.2016 SQL: Part 2 (DDL) 7

slide-8
SLIDE 8

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Table: Common Data Types

  • BIT
  • INT (capacity, length, signed)
  • REAL/DOUBLE/FLOAT (size, digits)
  • DATE/TIME/DATETIME/TIMESTAMP
  • CHAR (length)
  • VARCHAR (length)
  • TEXT/CLOB
  • BINARY/BLOB

1.29.2016 SQL: Part 2 (DDL) 8

slide-9
SLIDE 9

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Table: Custom Data Types

  • CREATE DOMAIN

– Name, base type, constraint(s) via CHECK

  • CREATE TYPE

1.29.2016 SQL: Part 2 (DDL) 9

slide-10
SLIDE 10

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Table: Common Column Options

  • [NOT] NULL
  • DEFAULT <value>
  • UNIQUE
  • PRIMARY KEY
  • CHECK <expr>
  • AUTOINCREMENT

– DBMS-specific

1.29.2016 SQL: Part 2 (DDL) 10

slide-11
SLIDE 11

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Table: Keys

Separate line required if multi-column. Optional: CONSTRAINT constraint_name

PRIMARY KEY (c_name1, c_name2, …) FOREIGN KEY (l_c_name1, l_c_name2, …) REFERENCES table_name(f_c_name1, …) [ON <DELETE/UPDATE> <CASCADE/SET NULL>]

1.29.2016 SQL: Part 2 (DDL) 11

slide-12
SLIDE 12

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Index (1)

  • Supplementary data structure used to

make some operations faster

  • Defined on a sequence of field(s) of a

single table

– May optionally enforce uniqueness

  • More detail in physical tuning

– When to use, types, tradeoffs

1.29.2016 SQL: Part 2 (DDL) 12

slide-13
SLIDE 13

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Index (2)

CREATE [UNIQUE] INDEX index_name ON table_name (c_name1, …) [OPTIONS]; Notes

  • Ordering of columns is VERY important
  • Options often refer to the type of index

being used (e.g. btree, hash, spatial – VERY important)

1.29.2016 SQL: Part 2 (DDL) 13

slide-14
SLIDE 14

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

View

A “virtual” table defined via a SELECT query over base table(s) and/or other views CREATE VIEW view_name AS SELECT …; Common uses

– Convenience/code re-use: if multiple queries rely upon a common data transformation – Security: users only see the data they “need” to see (e.g. calculation/join/aggregation over base data) – Performance: a view may optionally be materialized (sometimes indexed), meaning the DBMS actually stores its contents on disk – can reduce query time via caching complex

  • perations/aggregations (more in physical tuning)

1.29.2016 SQL: Part 2 (DDL) 14

slide-15
SLIDE 15

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Assertion

Declarative constraint that is outside the scope of implicit/explicit constraints Typically cross-table

– Else CHECK

CREATE ASSERTION assertion_name CHECK (multi-table expr);

1.29.2016 SQL: Part 2 (DDL) 15

slide-16
SLIDE 16

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Trigger

Part of an active database – specifies actions that automatically occur as a result of database events Typically composed of three components

1. Database update event(s) 2. Before/after the event(s) occur, the condition that determines if the rule action applies 3. The action to be taken, typically a set of SQL statements

CREATE TRIGGER trigger_name <BEFORE/AFTER> <INSERT/UPDATE/DELETE> ON table_name FOR EACH ROW {body};

1.29.2016 SQL: Part 2 (DDL) 16

slide-17
SLIDE 17

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Stored Procedure/Function

  • Some DBMSs support the ability to store

code modules within the database, for access via SQL or library API

– Reduces duplication – Decreases latency – More complex constraints than SQL

  • SQL/PSM (SQL/Persistent Stored Modules)

is a standard for such modules, but each DBMS varies widely

– CREATE FUNCTION/PROCEDURE …

1.29.2016 SQL: Part 2 (DDL) 17

slide-18
SLIDE 18

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Discretionary Access Control

  • Create/remove users

– CREATE USER … – DROP USER …

  • Grant/revoke privilege(s)

GRANT/REVOKE <privilege list> ON <database/table> TO/FROM user

  • WITH GRANT OPTION supports propagation of

grant privilege

1.29.2016 SQL: Part 2 (DDL) 18

slide-19
SLIDE 19

Wentworth Institute of Technology COMP2670 – Databases | Spring 2016 | Derbinsky

Summary

  • You have now been exposed to a selection of

SQL DDL components

– BEGIN, COMMIT/ROLLBACK – CREATE/DROP/ALTER, USE – GRANT/REVOKE

  • These commands are very DBMS-specific

and are used to create/modify/remove…

– Schema elements (e.g. table, column, data types) – Physical implementation (e.g. indexes, views) – Constraints (e.g. keys, assertions) – Access (e.g. users, privileges)

1.29.2016 SQL: Part 2 (DDL) 19