Chapter 2 :TABLES AND INDEXES

1
Chapter 2 TABLES AND INDEXES
2
Table Creation

• One of the first steps in creating a database is
  to create the tables that will store
  organizations 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

3
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.

4

• 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.

5

• Data Types.
• CHAR - Used to store fixed-length, character
  data.
• VARCHAR2 - 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.

6

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

7

• 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 employees social security
  number, last name, first name, date hired, and
  annual salary.
• Contd.

8

• Table Creation
• CREATE TABLE employee (
• emp_ssn CHAR(9),
• emp_last_name VARCHAR2(25),
• emp_first_name VARCHAR2(25),
• emp_date_of_birth DATE,
• emp_salary 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.

9

• 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.

10

• 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.

11

• Example
• emp_last_name VARCHAR2(25)
• CONSTRAINT nn_emp_last_name NOT NULL,
• emp_first_name VARCHAR2(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.

12

• 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,

13

• 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 lt 85000),
•  

14

• 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,

15

• Commands to Manage, Drop and Alter Tables
• Viewing a Table Description
• The SQLPLUS 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.

16

• DESCRIBE Command
• DESC employee
•  
• Name Null? Type
• ------------------- -------- ------------
• EMP_SSN NOT NULL CHAR(9)
• EMP_LAST_NAME NOT NULL VARCHAR2(25)
• EMP_FIRST_NAME NOT NULL VARCHAR2(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.

17

• 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

18

• 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

19

• 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.

20

• 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.

21

• 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))

22

• 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.

23

• Identifying Foreign Keys
• CREATE TABLE department (
• dpt_no NUMBER(2)
• CONSTRAINT pk_department PRIMARY KEY,
• dpt_name VARCHAR2(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.

24

• 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.

25

• Identifying Foreign Keys
• CREATE TABLE employee (
• emp_ssn CHAR(9)
• CONSTRAINT pk_employee PRIMARY KEY,
• emp_last_name VARCHAR2(25)
• CONSTRAINT nn_emp_last_name NOT NULL,
• emp_first_name VARCHAR2(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 lt 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

26

• 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?

27

• 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 are additional referential integrity
  constraints that can be used to enforce database
  integrity.

28

• MAINTAINING REFERRENTIAL INTEGRITY

29

• 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.

30

• THE INSERT Command
• The general form of the INSERT command is
• INSERT INTO table
• VALUES (column1 value, column2 value, )
• The second form of the INSERT command is used to
  insert rows where some of the column data is
  unknown (NULL).

31

• 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')
•  

32

• 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

33

• 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
•  

34

• 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.

35

• The UPDATE Command
• The general syntax of the UPDATE command is
• UPDATE table
• SET column expression ,column
  expression...
• WHERE condition

36

• 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).

37

• 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.

38

• 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.

39

• 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.

40

• 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.

41

• Creating an Index
• The general form of the CREATE INDEX command is
• CREATE INDEX ltindex namegt
• ON lttable namegt (column1, column2)

42

• Creating a UNIQUE Index
• The general form of the CREATE UNIQUE INDEX
  command is
• CREATE UNIQUE INDEX ltindex namegt
• ON lttable namegt (column1, column2)

43

• 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.

44

• DROPPING Indexes
• The syntax of the DROP INDEX command is simple
• DROP INDEX index_name