Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Data Definition Language Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Context SQLPlus Toad • Client Tool • SQLPlus, Toad • Database Management System • SQL – Query an object – SELECT • DML – Change data in an object – INSERT, UPDATE, DELETE • DDL – Create objects - CREATE

3. Basic CREATE TABLE • Physical implementation of a Relation. • Syntax:CREATE TABLEtablename(column_a type NOT NULL,column_b type, ...); • Data Types: • CHAR(n) character data of fixed size up to 2000 characters. • VARCHAR2(n) variable length character data up to 4000 characters. • NUMBER integer data • NUMBER(n) integer data, length n • NUMBER(m,n) floating point data, m = total number of digits (not width), n = digits after decimal point • DATE date/time data • TIMESTAMP date/time/fractions of a second • NOT NULL null constraint, requires a value for row to exist

4. Other CREATE TABLE clauses • Appendix shows all CREATE TABLE clauses pp. 1109-1121 • Object tables • XML types • STORAGE clause – specifies how the data is stored in the database • VARRAYs and Nested Tables • LOB (Large OBject) parameters • External Tables • And many others

5. Constraints • Candidate Keys:Column(s) which uniquely identify rows in a table. Also called unique constraint. • Primary Keys:Column(s) used to specify and enforce uniqueness among rows in a table. create table employee (employee_idvarchar(8) not null, column x, ...., constraint emp_PK primary key (employee_id)); • Foreign Keys:Column(s) used to reference rows in another table. These values are actually the primary keys in the other table.alter table employee add constraint emp_dept_FK foreign key (dept_id) references department(dept_id); • Check Constraint:A constraint that forces a set of values (domain) for a column. NOT NULL columns automatically get a system generated check constraint.

6. Constraint Example SQL> desc customer Name Null? Type ----------------------- -------- ---- ID NUMBER NAME VARCHAR2(40) STATE CHAR(2) SQL> desc state2 Name Null? Type ----------------------- -------- ---- STATE_CODE NOT NULL CHAR(2) STATE_NAME VARCHAR2(50) SQL> alter table state2 2 add constraint PK_State2 3 primary key (state_code); Table altered. SQL> alter table customer 2 add constraint cust_another 3 foreign key (state) 4 references 5 state2(state_code); Table altered.

7. DROP vsTRUNCATE vs DELETE TABLE • DroppingTables: DROP TABLE tablename; • Dropping a table removes the table, • both the table’s data and definition are removed. • Truncating Tables:TRUNC TABLE tablename; • Truncating a table removes only the data, • Table lock required, cannot be rolled back, implicit commit, • the table’s definition is still available. • Deleting Tables:DELETE tablename WHERE condition; • Removes a set of data, • Table lock not required, read-consistent view, rollback.

8. Tables can be altered to: change column definitions, add new columns (without NOT NULL), rename columns enable or disable constraints, change storage parameters, drop columns as of 8i. ALTER TABLE empADD (thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);ALTER TABLE empMODIFY (thriftplan NUMBER(9,2));alter table emp drop column thriftplan; ALTER TABLE

9. Views • Special considerations about views: • You cannot insert if the underlying table has any NOT NULL columns that are not in the view. • You cannot insert/update if any one of the view’s columns referenced in the insert/update contains functions or calculations. • You cannot insert/update/delete if the view contains group by, distinct, or a reference to rownum. • INSTEAD OF triggers get around the above limitation. • View Stability: • dropping the underlying tables of a view prevents the view from working. • altering the underlying tables of a view are OK. • Order By in Views: • ORDER BY does not work in views before Oracle 8i. • GROUP BY still consolidates rows in views,a side affect of GROUP BY is ordering rows. • READ ONLY: • The read only clause prevents update/insert/delete of rows in a view.

10. Creating a Table from Another Table • Includes the data: create table RAIN as select City, Precipitation from TROUBLE; • Does not include the data, only the table definition: create table RAIN as select City, Precipitation from TROUBLE where 1=2;

11. Logging A critical design feature of database management systems is the safeguarding of data in the event of a system failure. All commercial databases achieve the high standard of recovery the database to the last committed transaction. Logging is the mechanism by which Oracle creates redo logs, two or more preallocated files that store all changes made to the database as they occur. It is common practice to place redo logs in a separate disk than the tablespaces. A separate concept archiving is use to copy redo logs for permanent storage. During recovery, both archive logs and redo logs are used to replay transactions from the last full backup to “roll forward” to the last committed transaction.

12. Logging and Mass Updates Logging is critical for enabling recovery during production updates. Some activities occur when the database is not operational, for example during software releases, initial loads, or mass updates of data. Typically, if a failure occurs when the system is not operational, no recovery is required. In this case, it is useful to create tables with NOLOGGING, load the initial data or make a mass update, and then ALTER TABLE to turn on LOGGING. Use of NOLOGGING is important when working with large tables. It is the difference between a job running days versus a few hours.

13. Partitioning • Partitioning address key issues with the management of very large tables. • Decompose tables into more manageable pieces called partitions. • Partitioning is declarative meaning that no SQL must be rewritten. • Data is managed at partition-level for loads, indexes creation, backup and recovery. • If care is taken to partition by expressions in WHERE clause, queries can access a single partition.

14. Partitioning Choices • List – values by partition • Range – range by partition • Hash – random (balanced) partition • Composite – Partitioning and subpartitioning

15. Partition Example create table BOOKSHELF (CategoryName VARCHAR2(20), …) partition by range (CategoryName) (partition PART1 values less than (‘B’) tablespace PART1_TS, partition PART2 values less than (MAXVALUE) tablespace PART2_TS);

16. Indexing • Like the index in the back of your textbook, indexes provide a list of sorted values and the location of where to find more information about the value. • Indexes require storage separate from the rows of a table. • Individual tables rows are accessed via ROWID. • Default index is B*-tree, depicted below, and discussed in detail in any data structures text.

17. Types of Indexes Primary Key – Unique and not null – PK_* Unique Key – Unique and allows null – UK_* Foreign Key – Nonunique and references PK in related table – FK_* Nonuniqies index – Nonunique allows null – IX_* Bitmap indexes – useful for WHERE clauses looking up combinations of values. While maintaining B* adds to the cost of DML statement, maintaining bitmap index during DML is prohibitive. Do not use bitmap indexes on tables whose values change regularly. Important: Indexes are an example of an important Computer Science concept – the space vs time tradeoff. Also typical of database tradeoff DML vs SQL.

18. Index Examples Unique:create unique index UK_BOOKSHELF_AUTHORon BOOKSHELF_AUTHOR(Title, AuthorName)tablespace INDEX_TS;Support queries WHERE Title=value AND AuthorName = value;or WHERE Title=value; - -Leading value Function-based nonunique indexcreate index IX_BOOKSHELF_2 on BOOKSHELF(Upper(Title));Supports queriesWHERE Upper(Title) = value; -- must match exactly

19. Sequences Sequence are declared counters used to generate sequences of unique values. Often use to generate unique id during INSERT and UPDATE. create sequence CustomerId increment by 1 start with 1000; insert into CUSTOMER_DEMO(Name, Contact, ID)values (‘COLE CONSTRUCTION’, ‘Veronica’, CustomerId.NextVal);

20. Acknowledgements • Loney, Oracle Database 10g The Complete Reference