[M6-MAIN]_data definition language (DDL) (1).pdf

INFORMATION MANAGEMENT MODULE 6: Data Definition Language (DDL)  MODULE 6 DATA DEFINITION LANGUAGE (DDL)  MODULE 6 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Define a database using SQL d...

INFORMATION MANAGEMENT MODULE 6: Data Definition Language (DDL)  MODULE 6 DATA DEFINITION LANGUAGE (DDL)  MODULE 6 OBJECTIVES At the end of the chapter, the learner should be able to: Define terms Define a database using SQL data definition language Establish referential integrity using SQL Use SQL commands to manage tables Data Definition Language (DDL) CREATE ALTER DROP RENAME TRUNCATE COMMENT Table Basic unit of storage; composed of rows Database Systems 1 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 Database Systems 1 Data Type Description VARCHAR2(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 binary data RAW 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 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 Database Systems 1  Constraints enforce rules at the table level. Constraints prevent the deletion of a table if there are dependencies. The following constraint types are valid: o NOT NULL o UNIQUE o PRIMARY KEY o FOREIGN KEY o CHECK Database Systems 1  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. Database Systems 1  CREATE TABLE [schema.]table Syntax: (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,...), Database Systems 1 Database Systems 1 Figure 6-6 SQL database definition commands for Pine Valley Furniture Company (Oracle 11g) Overall table definitions  Non-nullable specification Identifying primary key  Non-nullable specifications Primary key Some primary keys are composite– composed of multiple attributes  Controlling the values in attributes Default value Domain constraint Identifying foreign keys and establishing relationships Primary key of parent table Foreign key of dependent table 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 Database Systems 1  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 Database Systems 1 Figure 6-7 Ensuring data integrity through updates Relational integrity is enforced via the primary-key to foreign-key match ALTER TABLE statement allows you to change column specifications: Table Actions: Example (adding a new column with a default value): Database Systems 1 Use the ALTER TABLE statement to: Add or drop a constraint, but not modify its structure ALTER TABLE ADD [CONSTRAINT ] type (); ALTER TABLE emp2 ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(manager_id) REFERENCES emp2(employee_id); Database Systems 1 RENAME COLUMN statement allows you to rename an existing column in an existing table in any schema. ALTER TABLE table-name RENAME COLUMN old-column-name to new- column-name; ALTER TABLE employee RENAME COLUMN manager to supervisor; Database Systems 1 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; Database Systems 1  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; Database Systems 1  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 SCN Database Systems 1  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 ]; Database Systems 1 DROP TABLE emp2; SELECT original_name, operation, droptime FROM recyclebin; … FLASHBACK TABLE emp2 TO BEFORE DROP;  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-name to new- table-name; RENAME employees to emp; Database Systems 1  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; Database Systems 1  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'; Database Systems 1 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 END OF MODULE 6  Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies. Harrington, J. (2016). Relational Database Design and Implementation (4th Edition). Morgan Kaufmann Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to Databases and Data Warehouses. Prospect Press Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson. Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison- Wesley. Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th Edition, Prentice Hall.