Universidad Ricardo Palma

Facultad de Ingeniería
Escuela de Ingeniería Informática

Esquemas

Base de Datos II
Managing Schema Objects
 Objectives
•After completing this lesson, you should be able to:
– Define schema objects and data types
– Create and modify tables
– Define constraints
– View the columns and contents of a table
– Create indexes
– Create views
– Create sequences
– Explain the use of temporary tables
 > Schema

What Is a Schema? Constraints

Indexes
Views
Sequences
Temp Tables
Data Dict

owns

HR schema
HR user
Accessing Schema Objects
 Naming Database Objects
– The length of names must be from 1 to 30 bytes, with
these exceptions:
• Names of databases are limited to 8 bytes.
• Names of database links can be as long as 128 bytes.
– Nonquoted names cannot be Oracle-reserved words.
– Nonquoted names must begin with an alphabetic
character from your database character set.
– Quoted names are not recommended.
 Specifying Data Types in Tables
•Common data types:
– CHAR(size [BYTE|CHAR]): Fixed-length
character data of size bytes or characters
– VARCHAR2(size [BYTE|CHAR]): Variable-
length character string having a maximum length of
size bytes or characters
– DATE: Valid date ranging from January 1, 4712
(B.C.), through December 31, 9999 (A.D.)
– NUMBER(p,s): Number with precision p and
scale s
Creating and Modifying Tables

Specify the table

name and schema.

Specify the column names,

data types, and lengths.
 Creating and Modifying Tables
CREATE TABLE shopowner.jobs (
Job_id NUMBER(5),
Job_title VARCHAR2(30),
min_salary NUMBER(6),
max_salary NUMBER(6)
)
TABLESPACE users;

ALTER TABLE shopowner.jobs ADD bonus NUMBER(6);

 Schema

Understanding Data Integrity > Constraints

Indexes
Views
Sequences
Temp Tables
Data Dict

JOB_HISTORY DEPARTMENTS LOCATIONS

EMPLOYEE_ID DEPARTMENT_ID (PK) LOCATION_ID (PK)
(PK,FK) EMPLOYEES DEPARTMENT_NAME STREET_ADDRESS
START_DATE (PK) EMPLOYEE_ID (PK) MANAGER_ID POSTAL_CODE
END_DATE FIRST_NAME LOCATION_ID (FK) CITY
JOB_ID (FK) LAST_NAME STATE_PROVINCE
DEPARTMENT_ID (FK) EMAIL COUNTRY_ID (FK)
PHONE_NUMBER
HIRE_DATE
JOB_ID (FK)
SALARY
COMMISION_PCT
MANAGER_ID (FK)
DEPARTMENT_ID (FK) COUNTRIES
COUNTRY_ID (PK)
JOBS COUNTRY_NAME
JOB_ID (PK) REGION_ID (FK)
JOB_TITLE
MIN_SALARY
MAX_SALARY

REGIONS
REGION_ID (PK)
REGION_NAME
Defining Constraints
 Constraint Violations
•Examples of how a constraint can be violated:
– Inserting a duplicate primary key value
– Deleting the parent of a child row in a referential
integrity constraint
– Updating a column to a value that is out of the bounds
of a check constraint
ID AGE
101 … X …
…
22
49
–30
102 …
101
103 … … 16
… 5
 Constraint States
DISABLE DISABLE ENABLE ENABLE
NOVALIDATE VALIDATE NOVALIDATE VALIDATE

No DML

New data

Existing data
 Constraint Checking
•Constraints are checked at the time of:
– Statement execution (for nondeferred constraints)
– COMMIT (for deferred constraints)
Case: DML statement followed by COMMIT

1 Nondeferred constraints
checked
2 COMMIT issued

3 Deferred constraints checked

4 COMMIT complete
 Creating Constraints with SQL:
Examples
ALTER TABLE countries
a
ADD (UNIQUE(country_name) ENABLE NOVALIDATE);

ALTER TABLE shopowner.jobs ADD CONSTRAINT job_pk PRIMARY

b
KEY (job_id);

CREATE TABLE emp (emp_no NUMBER PRIMARY KEY,Last_name

VARCHAR2(30), first_name VARCHAR2(30), dept_no NUMBER,
c Mgr_no NUMBER, hire_date date,salary NUMBER,
CONSTRAINT Mgr_FK FOREIGN KEY (mgr_no) REFERENCES
emp(emp_no),CONSTRAINT ck1 CHECK (salary > 0));
Viewing the Columns in a Table
Viewing the Contents of a Table
Actions with Tables
 Dropping a Table
•Dropping a table removes:
– Data
– Table structure DROP TABLE hr.employees PURGE;
– Database triggers
– Corresponding indexes
– Associated object privileges
•Optional clauses for the DROP TABLE statement:
– CASCADE CONSTRAINTS: Dependent referential
integrity constraints
– PURGE: No flashback possible
 Truncating a Table
– Truncating a table removes the data and releases used
space.
– Corresponding indexes are truncated.

TRUNCATE TABLE hr.employees;

 Schema

Indexes Constraints
> Indexes
Views
Sequences
Temp Tables
Data Dict
… WHERE key = 22

Row
Key pointer

22

22

Index Table
 Types of Indexes
•These are several types of index structures that
are available depending on your needs. Two of
the most common are:
– B-tree index
• Default index type; in the form of a balanced tree
– Bitmap index:
• Has a bitmap for each distinct value indexed
• Each bit position represents a row that may or may not
contain the indexed value.
• Best for low-cardinality columns
 B-Tree Index
Index entry

Root

Branch

Index entry header

Leaf Key column length
Key column value
ROWID
 Bitmap Indexes
Table File 3
Block 10

Block 11

Index
Block 12

Start End
Key ROWID ROWID Bitmap
<Blue, 10.0.3, 12.8.3, 1000100100010010100>
<Green, 10.0.3, 12.8.3, 0001010000100100000>
<Red, 10.0.3, 12.8.3, 0100000011000001001>
<Yellow, 10.0.3, 12.8.3, 0010001000001000010>
 Index Options
– Unique index: Ensures that every indexed value is
unique
– Reverse key index: Has its key value bytes stored in
reverse order
– Composite index: Is based on more than one column
– Function-based index: Is based on a function’s return
value
– Compressed index: Has repeated key values removed
– Order: An index can have its key values stored in
ascending or descending order.
 Creating Indexes

CREATE INDEX my_index ON

employees(last_name DESC, first_name);
 Schema

Views
Constraints
Indexes
> Views
…
LOCATION table

join
AU_BR_VIEW view COUNTRY table

CREATE VIEW au_br_view AS SELECT location_id, country_name

FROM locations l, countries c
WHERE l.country_id = c.country_id AND c.country_id in
('AU','BR');
Creating Views
 Schema

Sequences Constraints
Indexes
Views
> Sequences
Temp Tables
•A sequence is a mechanism for automatically Data Dict

generating integers that follow a pattern.

– A sequence has a name, which is 1
how it is referenced when the next 2
3
value is requested. 4
5
– A sequence is not associated with
any particular table or column.
– The progression can be ascending or
descending.
– The interval between numbers can be of any size.
– A sequence can cycle when a limit is reached.
Creating a Sequence
 Using a Sequence
SQL> CREATE TABLE orders
(id NUMBER,
ord_date DATE,
prod_id NUMBER,
prod_desc VARCHAR2(30)
);
Table created.

SQL> INSERT INTO orders VALUES ( abc_seq.NEXTVAL,

sysdate, 1245009, 'Gizmo X');

1 row created.
 Schema

Temporary Tables Constraints

Indexes
Views
Sequences
> Temp Tables
•A temporary table: Data Dict

– Provides storage of data that is automatically cleaned up

when the session or transaction ends
– Provides private storage of data for each session
– Is available for use to all sessions without affecting the
private data of each session
Temporary Tables: Considerations
– Use the GLOBAL TEMPORARY clause to create
temporary tables:
CREATE GLOBAL TEMPORARY TABLE employees_temp
ON COMMIT PRESERVE ROWS
AS SELECT * FROM employees;

– Use the TRUNCATE TABLE command to delete

the contents of the table.
– You can create the following on temporary tables:
• Indexes
• Views
• Triggers
 Summary
•In this lesson, you should have learned how
to:
– Define schema objects and data types
– Create and modify tables
– Define constraints
– View the columns and contents of a table
– Create indexes
– Create views
– Create sequences
– Explain the use of temporary tables