Data Definition Language DDL Database Management Systems 1

Data Definition Language (DDL) Database Management Systems 1

Objectives • Define terms • Define a database using SQL data definition language • Establish referential integrity using SQL • Use SQL commands to manage tables

SQL Database Definition • Data Definition Language (DDL) – CREATE – ALTER – DROP – RENAME – TRUNCATE – COMMENT • Table – Basic unit of storage; composed of rows

Naming Rules • Table names and column names: – Must begin with a letter – Must be 1– 30 characters long – Must contain only A–Z, a–z, 0– 9, _, $, and # – Must not duplicate the name of another object owned by the same user – Must not be an Oracle server–reserved word

Data Types Data Type Description VARCHAR 2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p, s) Variable-length numeric data DATE Date and time values LONG Variable-length character data (up to 2 GB) CLOB Character data (up to 4 GB) RAW and LONG RAW Raw binary data BLOB Binary data (up to 4 GB) BFILE Binary data stored in an external file (up to 4 GB) ROWID A base-64 number system representing the unique address of a row in its table

Steps in Table Creation 1. Identify data types for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique (candidate keys) 4. Identify primary key–foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table

Including Constraints • Constraints enforce rules at the table level. • Constraints prevent the deletion of a table if there are dependencies. • The following constraint types are valid: • • • NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

Constraint Guidelines • You can name a constraint, or the Oracle server generates a name by using the SYS_Cn format. • Create a constraint at either of the following times: • At the same time as the creation of the table • After the creation of the table • Define a constraint at the column or table level. • View a constraint in the data dictionary.

Defining Constraints • Syntax: CREATE TABLE [schema. ]table (column datatype [DEFAULT expr] [column_constraint], . . . [table_constraint][, . . . ]); • Column-level constraint syntax: column [CONSTRAINT constraint_name] constraint_type, • Table-level constraint syntax: column, . . . [CONSTRAINT constraint_name] constraint_type (column, . . . ),

The following slides create tables for this enterprise data model (from Lesson 1, Figure 1 -3)

Figure 6 -6 SQL database definition commands for Pine Valley Furniture Company (Oracle 11 g) Overall table definitions

Defining attributes and their data types 12

Non-nullable specification Primary keys can never have NULL values Identifying primary key 13

Non-nullable specifications Primary key Some primary keys are composite– composed of multiple attributes 14

Controlling the values in attributes Default value Domain constraint 15

Identifying foreign keys and establishing relationships Primary key of parent table Foreign key of dependent table 16

Data Integrity Controls • Referential integrity–constraint that ensures that foreign key values of a table must match primary key values of a related table in 1: M relationships • Restricting: – Deletes of primary records – Updates of primary records – Inserts of dependent records

FOREIGN KEY Constraint: Keywords • FOREIGN KEY: Defines the column in the child table at the table-constraint level • REFERENCES: Identifies the table and column in the parent table • ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted • ON DELETE SET NULL: Converts dependent foreign key values to null

Figure 6 -7 Ensuring data integrity through updates Relational integrity is enforced via the primary-key to foreign-key match 19

Changing Tables • ALTER TABLE statement allows you to change column specifications: • Table Actions: • Example (adding a new column with a default value):

Adding a Constraint Syntax • Use the ALTER TABLE statement to: – Add or drop a constraint, but not modify its structure ALTER TABLE <table_name> ADD [CONSTRAINT <constraint_name>] type (<column_name>); ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp 2(employee_id);

Renaming Column • RENAME COLUMN statement allows you to rename an existing column in an existing table in any schema. ALTER TABLE table-name RENAME COLUMN old-table-name to new-table-name; ALTER TABLE employee RENAME COLUMN manager to supervisor;

Read-Only Tables • You can use the ALTER TABLE syntax to: – Put a table into read-only mode, which prevents DDL or DML changes during table maintenance – Put the table back into read/write mode ALTER TABLE employees READ ONLY; -- perform table maintenance and then -- return table back to read/write mode ALTER TABLE employees READ WRITE;

Removing Tables • DROP TABLE statement allows you to remove tables from your schema. • Moves a table to the recycle bin • Removes the table and all its data entirely if the PURGE clause is specified DROP TABLE table_name [PURGE]; DROP TABLE CUSTOMER_T;

FLASHBACK TABLE Statement • Enables you to recover tables to a specified point in time with a single statement • Restores table data along with associated indexes, and constraints • Enables you to revert the table and its contents to a certain point in time or SCN

FLASHBACK TABLE Statement • Repair tool for accidental table modifications • Restores a table to an earlier point in time • Benefits: Ease of use, availability, and fast execution • Is performed in place • Syntax: FLASHBACK TABLE[schema. ]table[, [ schema. ]table ]. . . TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ];

Using the FLASHBACK TABLE Statement DROP TABLE emp 2; SELECT original_name, operation, droptime FROM recyclebin; … FLASHBACK TABLE emp 2 TO BEFORE DROP;

Renaming Table • RENAME TABLE allows you to rename an existing table in any schema (except the schema SYS). • To rename a table, you must either be the database owner or the table owner. RENAME TABLE table-name to newtable-name; RENAME TABLE employees to emp;

TRUNCATE Statement • Removes all rows from a table, leaving the table empty and the table structure intact • Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone • Syntax: TRUNCATE TABLE table_name; • Example: TRUNCATE TABLE copy_emp;

Adding Comments to a Table • You can add comments to a table or column by using the COMMENT statement: COMMENT ON TABLE employees IS 'Employee Information'; COMMENT ON COLUMN employees. first_name IS 'First name of the employee';

Summary • In this lesson, you should have learned the following: – Define a database using SQL data definition language – Establish referential integrity using SQL – Use SQL commands to manage tables

References • Hoffer, J. , Ramesh, V. , Topi, H. (2013). Modern Database Management 11 th Edition, Prentice Hall. • Singh, P. , Pottle, B. (2009). Oracle Database 11 g: SQL Fundamentals I, Oracle.