Chapter 2 TABLES AND INDEXES Bordoloi and Bock

Chapter 2 : TABLES AND INDEXES Bordoloi and Bock

Table Creation • • 1. 2. 3. 4. One of the first steps in creating a database is to create the tables that will store organization’s data. In order to create a table , four pieces of information must be determined: The table name The column (field) names Column data types and Column sizes Bordoloi and Bock

Naming Tables and Columns • • • Table and Column names should be meaningful and reflect the nature of the data that is to be stored. If the data stored is about the products that a firm sells , then the table should probably be named product! If products are identified by a string of eight characters, then the column that stores the product information data should be named product_number, or product_code. Bordoloi and Bock

Picking a Data type • • • The data type chosen for a column determines the nature of the data that can be stored in the column. This is termed the Domain of valid column values. Oracle provides 14 pre-determined data types as well as the ability to declare user defined data types. Bordoloi and Bock

Data Types. • • CHAR - Used to store fixed-length, character data. VARCHAR 2 - Used to store variable-length, character data that is up to 2000 characters per column entry. LOB – Used to store large object data. A LOB column can store either character or binary data that is up to four gigabytes in size. LONG – Used to store variable-length, character data that is up to two gigabytes in size. contd. Bordoloi and Bock

Data Types • • NUMBER – Used to store numeric data values of a specified precision and scale. DATE – Used to store valid dates. Bordoloi and Bock

CREATING A TABLE • • Creating a simple table that stores five items of information about employees for an organization. The table is named “employee” and stores information about each employee’s social security number, last name, first name, date hired, and annual salary. Contd. Bordoloi and Bock

Table Creation CREATE TABLE employee ( emp_ssn emp_last_name emp_first_name emp_date_of_birth emp_salary ); • • CHAR(9), VARCHAR 2(25), DATE, NUMBER(7, 2) The table name “employee”, is specified along with five data columns. Each column has a name that is unique within the table and is specified to store a specific type of data. Bordoloi and Bock

Data Integrity and Table Constraints • • • The term data integrity simply means that the data stored in the table is valid. There are different types of data integrity, often referred to as constraints. The specifications of different data types aids in maintaining certain aspects of the data stored for employees. Bordoloi and Bock

NOT NULL Constraint • • • A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL. A fairly standard practice is to assign each constraint a unique constraint name. If constraints are not named, then Oracle assigns meaningless system-generated names to each constraint. Bordoloi and Bock

Example emp_last_name VARCHAR 2(25) CONSTRAINT nn_emp_last_name NOT NULL, emp_first_name VARCHAR 2(25) CONSTRAINT nn_emp_first_name NOT NULL, PRIMARY KEY Constraint • Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table. This column or set of columns is referred to as the primary key. Bordoloi and Bock

PRIMARY KEY Constraint • If a table requires two or more columns in order to identify each row , the primary key is termed a composite primary key. emp_ssn CHAR(9) CONSTRAINT pk_employee PRIMARY KEY, Bordoloi and Bock

CHECK Constraint • • Sometimes the data values stored in a specific column must fall within some acceptable range of values. A CHECK constraint is used to enforce this data limit. emp_salary NUMBER(7, 2) CONSTRAINT ck_emp_salary CHECK (emp_salary <= 85000), Bordoloi and Bock

UNIQUE Constraint • • • Sometimes it is necessary to enforce uniqueness for a column value that is not primary key column. The UNIQUE constraint can be used to enforce this rule and Oracle rejects any rows that violate the constraint. Assume that each parking space for the organization is numbered and that no two employees can be assigned same parking space. emp_parking_space NUMBER(4) CONSTRAINT un_emp_parking_space UNIQUE, Bordoloi and Bock

Commands to Manage, Drop and Alter Tables Viewing a Table Description • • The SQL*PLUS DESCRIBE (DESC) command can display the column names and data types for any table. This command can be used when exact data types and column sizes for a table are unknown. Bordoloi and Bock

DESCRIBE Command DESC employee; Name Null? Type -----------EMP_SSN NOT NULL CHAR(9) EMP_LAST_NAME NOT NULL VARCHAR 2(25) EMP_FIRST_NAME NOT NULL VARCHAR 2(25) EMP_DATE_OF_BIRTH DATE EMP_SALARY NOT NULL NUMBER(7, 2) EMP_PARKING_SPACE NUMBER(4) • Note that while emp_ssn column was specified to have a PRIMARY KEY constraint, the Null? Column displayed in the table description indicates whether or not a column is constrained as NOT NULL. Bordoloi and Bock

Dropping a Table • • Employee table can be deleted with the DROP TABLE command. This command deletes both the table structure, its data, related constraints, and indexes. DROP TABLE employee; Bordoloi and Bock

Renaming a Table • • A table can be renamed with the RENAME command. This command does not affect table structure or data; it simply gives the current table a new name. RENAME employee TO worker; Bordoloi and Bock

Adding and Altering a column for an existing Table • • Modifying existing tables to either add new columns or alter existing columns can be accomplished with the ALTER TABLE MODIFY and ALTER TABLE ADD commands. The current data type of the emp_parking_space column is NUMBER(4). A very large organization may have in excess of 9, 999 employees. Bordoloi and Bock

ALTER TABLE Command • The ALTER TABLE command can be used to modify the emp_parking_space column to enable the allocation of upto 99, 999 parking spaces. ALTER TABLE employee MODIFY (emp_parking_space NUMBER(5)); • If a column modification will result in a column that is smaller than was originally specified, Oracle will return an error message if data rows exist such that their data will not fit into the new specified column size. Bordoloi and Bock

ALTER TABLE Command • • The ALTER TABLE command can be used to add a new column to the employee table. Suppose that an organization recognizes the need to track the gender of employees in order to meet a governmental reporting requirement, an emp_gender column can be added to the employee table. ALTER TABLE employee ADD (emp_gender CHAR(1)); Bordoloi and Bock

Relating Tables – Identifying Foreign Keys • • • Normally, data rows in one table are related to data rows in other tables. The department table will store information about departments within the organization. Each department has a unique, 2 -digit department number, department name, location, and primary telephone number for contacting the department manager. Contd. Bordoloi and Bock

Identifying Foreign Keys CREATE TABLE department ( dpt_no NUMBER(2) CONSTRAINT pk_department PRIMARY KEY, dpt_name VARCHAR 2(20) CONSTRAINT nn_dpt_name NOT NULL ); • • Employees are generally assigned to work in departments. In an organization, at a given time, an employee is assigned to a single department. In order to link rows in the employee table to rows in department table we need to introduce a new type of constraint, the FOREIGN KEY constraint. Bordoloi and Bock

Identifying Foreign Keys • • • Foreign keys (FKs) are columns in one table that reference primary key (PK) values in another or in the same table. Lets relate employee rows to the PK column named dpt_no in the department table. The value stored to the emp_dpt_number column for any given employee row must match a value stored in the dpt_no column in the department table. Bordoloi and Bock

Identifying Foreign Keys CREATE TABLE employee ( emp_ssn CHAR(9) CONSTRAINT pk_employee PRIMARY KEY, emp_last_name VARCHAR 2(25) CONSTRAINT nn_emp_last_name NOT NULL, emp_first_name VARCHAR 2(25) CONSTRAINT nn_emp_first_name NOT NULL, emp_date_of_birth DATE, emp_salary NUMBER(7, 2) CONSTRAINT ck_emp_salary CHECK (emp_salary <= 85000), emp_parking_space NUMBER(4) CONSTRAINT un_emp_parking_space UNIQUE, emp_gender CHAR(1), emp_dpt_number NUMBER(2), CONSTRAINT fk_emp_dpt FOREIGN KEY (emp_dpt_number) REFERENCES department ON DELETE SET NULL ); Bordoloi and Bock

MAINTAINING REFERRENTIAL INTEGRITY • • FOREIGN KEY constraints are also referred to as referential integrity constraints, and assist in maintaining database integrity. Referential integrity stipulates that values of a foreign key must correspond to values of a primary key in the table that it references. But what happens if the primary key values change or the row that is referenced is deleted? Bordoloi and Bock

MAINTAINING REFERRENTIAL INTEGRITY • • • Several methods exist to ensure that referential integrity is maintained. ON DELETE SET NULL clause can be used to specify that the value of emp_dpt_number should be set to null if the referenced department row is deleted. There additional referential integrity constraints that can be used to enforce database integrity. Bordoloi and Bock

MAINTAINING REFERRENTIAL INTEGRITY On Update (Delete) Restrict On Update (Delete) Cascade On Update (Delete) Set Null Bordoloi and Bock Any update/delete made to the department table that would delete or change a primary key value will be rejected unless no foreign key references that value in the employee table. This is the default constraint in Oracle. Any update/delete made to the department table should be cascaded through to the employee table. Any values that are updated/deleted in the department table cause affected columns in the employee table to be set to null.

THE INSERT Command • • The INSERT command is used to store data in tables. The INSERT command is often embedded in higher-level programming language applications as an embedded SQL command. There are two different forms of the INSERT command. The first form is used if a new row will have a value inserted into each column of the row. Bordoloi and Bock

THE INSERT Command • The general form of the INSERT command is INSERT INTO table VALUES (column 1 value, column 2 value, …); • The second form of the INSERT command is used to insert rows where some of the column data is unknown (NULL). Bordoloi and Bock

THE INSERT Command • This form of the INSERT command requires that you specify the names of the columns for which data are being stored. INSERT INTO employee (emp_ssn, emp_last_name, emp_first_name) VALUES ('999111111', 'Bock', 'Douglas'); Bordoloi and Bock

The DELETE Command • • • The DELETE command is perhaps the simplest of the SQL statements. It removes one or more rows from a table. Multiple table delete operations are not allowed in SQL. The syntax of the DELETE command is: DELETE FROM table_name [WHERE condition]; Bordoloi and Bock

The DELETE Command • • Since the WHERE clause is optional, you can easily delete all rows from a table by omitting a WHERE clause since the WHERE clause limits the scope of the DELETE operation. For example, the DELETE FROM command shown here removes all rows in the assignment table. DELETE FROM assignment; Bordoloi and Bock

The UPDATE Command • • Values stored in individual columns of selected rows can be modified (updated) with the UPDATE command. Updating columns is different from altering columns. The ALTER command changes the table structure, but leaves the table data unaffected. The UPDATE command changes data in the table, not the table structure. Bordoloi and Bock

The UPDATE Command • The general syntax of the UPDATE command is: UPDATE table SET column = expression [, column = expression]. . . [WHERE condition]; Bordoloi and Bock

The COMMIT Command • • INSERT, UPDATE, and DELETE commands are not committed to the database until the COMMIT statement is executed. COMMIT is a transaction managing command that confirms operations to the database on the server (closing Oracle also acts as a confirmation of the commands entered). Bordoloi and Bock

The ROLLBACK Command • • SQL command ROLLBACK (ROLL) can be issued immediately to cancel any database operations since the most recent COMMIT. Like COMMIT, ROLLBACK is also a transaction managing command; however, it cancels operations instead of confirming them. Bordoloi and Bock

INDEXES • • Indexes are optional structures associated with tables. There are different types of indexes including those used to enforce primary key constraints, unique indexes, non-unique indexes, concatenated indexes, and others. Bordoloi and Bock

PRIMARY KEY indexes • • • When a PRIMARY KEY constraint is specified, Oracle will automatically create a unique index to support rapid data retrieval for the specified table. Without an index, a command that retrieves data will cause Oracle to completely scan a table for rows that satisfy the retrieval condition. For example, consider the following SELECT statement that will display a list of employees assigned to a specific department. Bordoloi and Bock

PRIMARY KEY indexes SELECT emp_last_name, emp_first_name, emp_dpt_number FROM employee WHERE emp_dpt_number = 7; • If the employee table is not indexed on the emp_dpt_number column, Oracle will have to scan the entire table in order to satisfy the query. Bordoloi and Bock

Creating an Index • The general form of the CREATE INDEX command is: CREATE INDEX <index name> ON <table name> (column 1, column 2…); Bordoloi and Bock

Creating a UNIQUE Index • The general form of the CREATE UNIQUE INDEX command is: CREATE UNIQUE INDEX <index name> ON <table name> (column 1, column 2…); Bordoloi and Bock

DROPPING Indexes • • • An index should not be retained unless it improves system processing in some fashion. All indexes on a table must be updated whenever row data is changed that is referenced by an index. Useless indexes burden the system by adding unnecessary maintenance and by needlessly occupying disk space. These indexes should be dropped. Bordoloi and Bock

DROPPING Indexes • The syntax of the DROP INDEX command is simple: DROP INDEX index_name; Bordoloi and Bock