Outline: SQL DDL - creating schemas - contraints - modifying schemas DML - in separate lectures

1. Outline: SQL • DDL • - creating schemas • - contraints • - modifying schemas • DML • - in separate lectures

2. Why SQL? • SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++. • declarative or non-procedural • SQL89, SQL92, SQL1999

3. Case insensitive or sensitive • SQL commands themselves are case insensitive. • Values in ‘’ are case sensitive though.

4. Two fundamental groups of commands of Structured Query Language • DDL for data definition • Create, drop, alter …affecting schemas • DML for query, update, view, affecting data instances only • Manipulation (insert, delete, update) • Retrieval (select) • What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.

5. Other commands fall into groups for security, integrity constraints, transactions, embedding into other 3GLs such as C\c++, java, perl etc, • Security: Grant and Revoke are used to specify user privileges • Grant select, update on Employee to Diane; • Revoke update, delete on Works_on from Jim; • Embedded SQL: SQL can be placed within 3GL programs • Transactions: SQL systems implement the ACID properties

6. Defining Oracle8i Database Tables • To create a new table specify: • Table name • Name of each data field • Data type and size of each data field • Constraints: restrictions on the data values that a field can store

7. Create a Table CREATE TABLE table_name( column_name1 data_type, column_name2 data_type, ....... )

8. Oracle naming standard • Series of rules Oracle Corporation established for naming all database objects. i.e. rules for names (table names, field names, or any names, etc.) • Objects must be from 1 to 30 characters long • Can contain letters, numbers, and the special symbols $, _, and # • Must begin with a character

9. Oracle8i Data Types • Data type:specifies the kind of data that a field stores, i.e., what type of data the column (attributes of a table) can hold. • Assigning a data type provides a means for error checking • Data types enable the DBMS to use storage space more efficiently by internally storing different types of data in different ways

10. Character Data Types • VARCHAR2 • Stores variable-length character data up to a maximum of 4,000 characters • Values in different records can have a different number of characters • Actually two parts, one is fixed length pointer, the other part is the real string part pointed by that pointer. • fieldname VARCHAR2(maximum_size)

11. Character Data Types • CHAR • Fixed-length character data up to a maximum size of 2,000 characters • Data values for different records all have the same number of characters • DBMS adds trailing blank spaces to the end of the entry to make the entry fill the maximum_sizevalue • Data longer than maximum_size causes an error • fieldname CHAR[(maximum_size)]

12. Character Data Types • NVARCHAR2 and NCHAR • Analogous to VARCHAR2 and CHAR but use Unicode rather than ASCII • Used to hold character data in languages other than English

13. The most commonly used generic type is NUMBER. • Columns of type NUMBER can hold either an integer or a real number.

14. Number Data Types • Stores negative, positive, fixed, and floating point numbers between 10–130 and 10125, with precision up to 38 decimal places • General Syntax: fieldname NUMBER [([precision,] [scale])] • Integer: fieldname NUMBER(precision) • Fixed point: fieldname NUMBER[([precision],[scale])] • Floating point: fieldname NUMBER

15. Date and Time Data Types • DATE • Dates from December 31, 4712 BC to December 31, 4712 AD • Default format DD-MON-YY • Default time format HH:MI:SS A.M. • fieldname DATE • TIMESTAMP • Similar to DATE but stores fractional seconds • fieldname TIMESTAMP (fractional_seconds_precision)

16. Date and Time Data Types • INTERVAL YEAR TO MONTH • Time interval, expressed in years and months • +02-11 specifies a positive time interval of 2 years and 11 months • fieldname INTERVAL YEAR[(year_precision)] TO MONTH • INTERVAL DAY TO SECOND • Time interval, expressed in days, hours, minutes, and seconds • -04 03:20:32.00: 4 days, 3 hours, 20 minutes, and 32 seconds • fieldname INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)] • Just need to know them, won’t be tested.

17. Example • This example demonstrates how you can create a table named "Person", with four columns, i.e. four attributes. The column names will be "LastName", "FirstName", "Address", and "Age": CREATE TABLE Person  ( LastName varchar2(10), FirstName varchar2(10), Address varchar2(20), Age int )

18. More detailed discussion on Oracle data types.

19. Definition of Data Integrity a. Whether the data separated in different tables reflects and only reflects the mini-world facts, adhere to the facts, especially when data are pieced together from the separated tables, or all data in a table treated as a whole.

20. Types of Data Integrity Different types of data integrity. Nulls It is about whether a single column allows or disallows inserts or updates of rows containing a null for the column. Unique Column Values About whether a column (or set of columns) allows only the insert or update of a row containing a unique value for the column (or set of columns). Primary Key Values Whether a column (or set of columns) so that each row in the table can be uniquely identified by the values in the column (or set of columns). Referential Integrity Whether a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity include: Restrict: A referential integrity rule that disallows the update or deletion of referenced data. Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL. Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value. Cascade: When referenced data is updated, all associated dependent data is correspondingly updated; when a referenced row is deleted, all associated dependent rows are deleted. Complex Integrity Checking A column (or set of columns) allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns). Larger, less in a range etc.

21. How Oracle Enforces Data Integrity Oracle allows you to define and enforce each type of the data integrity (defined in the previous section.)using rules (constraints) To auto the checking of data integrity, Oracle provides a set of rules for us to define on objects. Each rule takes care of a type of data integrity concern. Rules are there to use But to use them usually is subject to the database administrator or application developer, limited by their technical background.

22. Constraints • Rules that restrict the data values that you can enter into a field in a database table • Integrity constraints: define primary and foreign keys • Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL • Table constraint: restricts the data value with respect to all other values in the table • Field constraint: limits the value that can be placed in a specific field, irrespective of values that exist in other table records

23. Integrity Constraints • Define primary key fields • Specify foreign keys and their corresponding table and column references • Specify composite keys

24. Value Constraints • Check conditions: field value must be a specific value or fall within a range of values • NOT NULL constraints: specify whether a field value can be NULL • Default constraints: specify that a field has a default value that the DBMS automatically inserts for every record, unless the user specifies an alternate value • Unique constraints: specify that a field must have a unique value for every table record

25. Things the create table statement does! • When you create a table, you need to specify • Table name • Field name and data type for each field • Constraints as complete and reasonable as possible

26. DDL - creating schemas • Create tabletablename … • constraints: • primary keys • foreign keys • on delete set null|cascade|set default • on update set null|cascade|set default • on insert set null|cascade|set default • uniqueness for secondary keys

27. On delete cascade foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete. A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement. CREATE TABLE table_name(column1 datatype null/not null,column2 datatype null/not null,...CONSTRAINT fk_column  FOREIGN KEY (column1, column2, ... column_n)  REFERENCES parent_table (column1, column2, ... column_n)  ON DELETE CASCADE);

28. CREATE TABLE products ( product_id numeric(10) not null, supplier_id numeric(10) not null, CONSTRAINT fk_supplier   FOREIGN KEY (supplier_id)  REFERENCES supplier(supplier_id)   ON DELETE CASCADE ); Because of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplier_id value. This is one way to maintain data integrity.

29. We could also create a foreign key (with a cascade delete) with more than one field as in the example below. CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) ); CREATE TABLE products ( product_id numeric(10) not null, supplier_id numeric(10) not null, supplier_name varchar2(50) not null, CONSTRAINT fk_supplier_comp   FOREIGN KEY (supplier_id, supplier_name)  REFERENCES supplier(supplier_id, supplier_name)  ON DELETE CASCADE ); In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

30. Set null on delete for foreign key • What is a foreign key? • A foreign key means that values in one table must also appear in (come from) another table. • The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table. • A foreign key with a "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted. • A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

31. DDL - Examples: • Create table: • Create table EMPLOYEENEW • (FNAME VARCHAR(15) NOT NULL, • MINIT CHAR(1), • LNAME VARCHAR(15) NOT NULL, • SSN CHAR(9) NOT NULL, • BDATE DATE, • ADDRESS VARCHAR(30), • SEX CHAR(1), • SALARY number(10, 2), • SUPERSSN CHAR(9), • DNO INT NOT NULL, • PRIMARY KEY(SSN), • /*constraint pk_en primary key(ssn), */ • /*This allows you specify multiple column key words*/ • FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEENEW(SSN), • FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER));

32. DDL - Examples: • Specifying constraints: • Create table EMPLOYEE • (…, • DNO INT NOT NULL DEFAULT 1, • CONSTRAINT EMPPK • PRIMARYKEY(SSN), • CONSTRAINT EMPSUPERFK • FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) • ON DELETE SET NULL ON UPDATE CASCADE, • CONSTRAINT EMPDEPTFK • FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) • ON DELETE SET DEFAULT ON UPDATE CASCADE);

33. Employee delete delete ... ... ... ... ssn ssn supervisor supervisor 234589710 234589710 123456789 123456789 ... ... ... ... null null 234589710 234589710 set null or cascade: strategies to maintain data consistency Employee not reasonable delete cascade

34. Employee delete delete ... ... ssn supervisor 234589710 123456789 ... ... null 234589710 set null or cascade: strategies to maintain data consistency Employee ... ... ssn supervisor reasonable null 123456789 set null ... ... null 234589710

35. set default: strategy to maintain data consistency Employee ... ... ssn DNO 4 123456789 ... ... change this value to the default value 1. … … 234589710 Department ... ... DNUMBER … … … … 1 ... ... … … 4 delete

36. Table Creation Sequence • When creating tables with foreign key references, create referenced tables first • Always create tables without foreign keys before those with foreign keys

37. drop table tablename …

38. Deleting Existing Table Records • TRUNCATE • Removes all of the table data without saving any rollback information • Must disable foreign key constraints before truncating table • TRUNCATE TABLE tablename;

39. Using an ALTER TABLE statement after the table has been created • Some records could have been there • The syntax for creating a primary key in an ALTER TABLE statement is: • ALTER TABLE table_name add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

40. DDL - Examples: • drop constraints • ALTER TABLE EMPLOYEE • DROPCONSTRAINT EMPSUPERFK CASCADE; • ALTER TABLE EMPLOYEE • ADDCONSTRAINT (EMPSUPERFK FOREIGN • KEY(SUPERSSN) REFERENCE EMPLOYEE(SSN));

41. DDL - modifying schemas • alter tabletablenameadd|drop attributenamecascade|restrict • alter table • ALTER TABLE EMPLOYEE • ADD JOB VARCHAR(12); • ALTER TABLE EMPLOYEE • DROP ADDRESS CASCADE; • ALTER TABLE DEPARTMENT • ALTER MGRSSN DROP DEFAULT; • ALTER TABLE DEPARTMENT • ALTER MGRSSN SET DEFAULT “33344555”;

42. Modifying and Deleting Database Tables • Modify existing database tables by: • Changing the name of a table • Adding new columns • Deleting columns that are no longer needed • Changing the data type or maximum size of an existing column • Unrestricted action: some specifications can always be modified • Restricted action: specifications modified only in certain situations

43. Unrestricted Action

44. Restricted Actions

45. Deleting and Renaming Tables • To delete: • Drop table [tablename] • Use with caution • To delete foreign key constraints, add “cascade constraints” • To rename: • Rename old_tablename to new_tablename • DBMS automatically transfers to the new table integrity constraints, indexes, and privileges that referenced the old table.

46. Adding Fields to Existing Tables • To add a field: • ALTER TABLE tablename ADD(fieldname data_declaration constraints);

47. Modifying Existing Field Data Definitions • Can only change datatype to compatible data type (i.e. varchar2 to char) • ALTER tablename MODIFY(fieldname new_data_declaration);

48. Deleting a Field • ALTER TABLE tablename DROP COLUMN fieldname; • Can be used to rename a field – first drop, then add

49. Adding and Deleting Constraints • Add a constraint: ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_definition; • Remove a constraint: ALTER TABLE tablename DROP CONSTRAINT constraint_name;

50. DML • DML • Insert • Update • Delete • Select • Discussed in a separate lecture