Transcript Document - Oman College of Management & Technology

Enhanced Guide to Oracle8i
Chapter 2:
Creating and Modifying
Database Objects
1
Database Objects


An Oracle database consists of multiple
user accounts
Each user account owns database
objects




Tables
Views
Stored programs
Etc.
2
Database Queries
 Query: command to perform
operation on database object
 Create
 Modify
 View
 Delete
 Structured Query Language (SQL)
 Standard query language for
relational databases
3
SQL Command Types

Data Definition Language (DDL)


Used to create and modify the structure of
database objects
Data Manipulation Language (DML)

Used to insert, update, delete, and view
database data
4
DDL Commands

Used to create and modify the structure
of database objects




CREATE
ALTER
DROP
DDL commands execute as soon as
they are issued, and do not need to be
explicitly saved
5
DML Commands

Used to insert, view, and modify database
data





INSERT
UPDATE
DELETE
SELECT
DML commands need to be explicitly saved or
rolled back


COMMIT
ROLLBACK
6
User Accounts

Each Oracle database user has a user
schema



Area in the database where the user’s
database objects are stored
Identified by a unique username and
protected by a password
Each user schema is granted specific
privileges
7
Types of Database Privileges

System Privileges

Control the operations that the user can perform
within the database


1.Connecting to the database, 2.creating new tables,
3.shutting down the database, etc.
Object Privileges



Granted on individual database objects
Controls operations that a user can perform on a
specific object (insert data, delete data, etc.)
When you create an object in your user schema, you
can then grant object privileges on that object to
other database users
8
Oracle Naming Standard

Oracle database objects must adhere to
the Oracle Naming Standard



1 to 30 characters long
Must begin with a character
Can contain characters, numbers, and the
symbols $, _, and #
9
Creating New User Accounts


Done by DBA
Syntax:
CREATE username IDENTIFIED BY password;
10
Example Oracle System Privileges
Privilege
Level
Purpose
CREATE SESSION
User
Connecting to database
CREATE TABLE
User
Creating tables in current user schema
DROP TABLE
User
Dropping tables in current user schema.
UNLIMITED TABLESPACE
User
Allows user to create schema objects
using as much space as needed
CREATE USER
DBA
Creating new users
GRANT ANY PRIVILEGE
DBA
Granting system privileges to users
CREATE ANY TABLE
DBA
Creating tables in any user schema
DROP ANY TABLE
DBA
Dropping tables in any user schema
11
Granting System Privileges


Done by DBA
Syntax:
GRANT privilege1, privilege2, … TO username;
12
Database Roles



Role is a database object that can be
assigned system privileges
Role is then assigned to a user, and the
user inherits the role’s privileges
Used to easily assign groups of related
privileges to users
13
1. Creating Roles
Syntax:
CREATE ROLE role_name;

14
2.Assigning Privileges to a Role

Syntax:
GRANT privilege1, privilege2, …
TO role_name;
15
3.Assigning a Role to a User
Syntax:
GRANT role_name TO user_name;

16
Revoking System Privileges

Syntax:
REVOKE privilege1, privilege2, …
FROM username;
17
Administering System Privileges

To be able to grant system privileges to
other users, a user account must have
been granted the privilege WITH
ADMIN OPTION
18
DBA_User (System/ocmt)
User_2
User_1
T
T
T
T
T
T
19
Defining Database Tables
 To create a table, you must
specify:
 Table name
 Field names
 Field data types
 Field sizes
 Constraints
20
Table and Field Names
 Must follow the Oracle Naming
Standard
 Each table in a user schema must
have a unique name within that user
schema
 Each field in a table must have a
unique name within that table
21
Oracle Data Types
 Data type: specifies type of data
stored in a field
 Date, character, number, etc.
 Uses
 Error checking
 Efficient use of storage space
22
Oracle Character Data Types
 VARCHAR2
 Variable-length character strings
 Maximum of 4,000 characters
 Must specify maximum width
allowed
 No trailing blank spaces are added
 Example declaration:
student_name VARCHAR2(30)
23
Character Data Types
 CHAR




Fixed-length character data
Maximum size 2000 characters
Must specify maximum width allowed
Adds trailing blank spaces to pad width
 Example declaration:
student_gender CHAR(1)
24
Character Data Types
 NCHAR
 Supports 16-digit binary character
codes
 Used for alternate alphabets
25
Number Data Type
 NUMBER
 stores values between 10-130 and 10126
 General declaration format:
variable_name NUMBER(precision, scale)
26
NUMBER Data Types
 Number type (integer, fixed point,
floating point) specified by
precision and scale
 Precision: total number of digits on
either side of the decimal point
 Scale: number of digits to right of
decimal point
27
Integer Numbers
 Whole number with no digits to
right of decimal point
 Precision is maximum width
 Scale is omitted
 Sample declaration:
s_age NUMBER (2)
28
Fixed Point Numbers
 Contain a specific number of
decimal places
 Precision is maximum width
 Scale is number of decimal places
 Sample declaration:
item_price NUMBER(5, 2)
29
Floating Point Numbers
 Contain a variable number of
decimal places
 Precision and scale are omitted
 Sample declaration:
s_GPA NUMBER
30
Date Date Type
 DATE
 Stores dates from 1/1/4712 BC to
12/31/4712 AD
 Stores both a date and time component
 Default date format:
DD-MON-YY HH:MI:SS AM
 example: 05-JUN-03 12:00:00 AM
 Sample declaration:
s_dob DATE
31
Specifying Date and Time
Values
 If no time value is given when a
new date is inserted, default value
is 12:00:00 AM
 If no date value is given when a
new time is inserted, default date
is first day of current month
32
Large Object (LOB) Data
Types

Binary Large Object (BLOB)


Character Large Object (CLOB)


Stores up to 4 GB of character data
BFILE


Stores up to 4 GB of binary data
Stores a reference to a binary file maintained in
the operating system
NCLOB

Character LOB that supports 16-bit character code
33
Declaring LOB Data Fields

Item size is not specified
Examples:
item_image BLOB
item_image BFILE

34
Creating a Database Table
 Syntax:
CREATE TABLE table_name
( fieldname1 datatype,
fieldname2 datatype, …);
 Example:
CREATE TABLE my_students
( s_id NUMBER(6),
s_name VARCHAR2(30),
s_dob DATE,
s_class CHAR(2));
35
Constraints


Rules that restrict the values that can
be inserted into a field
Types of constraints


Integrity: define primary and foreign keys
Value: specify values or ranges of values
that can be inserted
36
Constraint Levels

Table constraint



Restricts the value of a field with respect to
all other table records
Example: primary key value must be
unique for each record
Column constraint


Restricts values in a specific column
Example: values in an S_GENDER field
must be ‘M’ or ‘F’
37
Constraint Names
 It is an Internal name used by DBMS to
identify the constraint
 Each constraint name in a user schema
must be unique
 If you do not name a constraint, the system
will automatically generate an unintuitive
name
38
Constraint Names
 Constraint naming convention:
1.tablename_2.fieldname_3.constraintID
 Constraint ID values:





Primary key: pk
Foreign key: fk
Check condition: cc
Not NULL: nn
Unique: uk
 Example constraint name:
1.my_students_2.s_id_3.pk
39
Nationality_nat_no_Pk
40
Primary Key Constraints
 Table-level
 Defining a primary key:
CONSTRAINT constraint_name PRIMARY KEY
 Example:
s_id NUMBER(6)
CONSTRAINT student_s_id_pk PRIMARY KEY
41
Primary Key Constraints
 Can be defined when field is declared
42
Primary Key Constraints
 Can also be defined after all table field
definitions are completed
43
Composite Primary Keys
 Syntax:
CONSTRAINT constraint_name
PRIMARY KEY (field1, field2)
 Must be defined after fields that compose
key are defined
44
H.W:



What is the default value for “Number”
Data type??
Means:
“If the user did not specify the size of
the field”
45
Foreign Key Constraints
 Table-level
 Can only be defined after field is defined as a
primary key in another table
 Syntax:
CONSTRAINT constraint_name
REFERENCES primary_key_table_name
(field_name)
46
Foreign Key Constraints
 Can be defined when field is declared
47
Foreign Key Constraints
 Can also be defined after all table field
definitions are completed
48
Value Constraints
 Column-level
 Restricts data values that can be inserted
in a field
 In general, avoid value constraints
because they make the database very
inflexible
49
Types of Value Constraints
 Check condition: restricts to specific values
 Example: s_gender (M or F)
CONSTRAINT my_students_s_gender_cc
CHECK (s_gender = ‘M’) OR (s_gender = ‘F’)
 Not NULL: specifies that a field cannot be
NULL
 Example:
CONSTRAINT my_students_s_dob_nn
NOT NULL
50
Types of Value Constraints
 Default: specifies a default value that is inserted
automatically
 Example:
s_state CHAR(2) DEFAULT ‘WI’
 Unique
 Table constraint
 Specifies that a non-primary key field must have a
unique value
CONSTRAINT consultant_c_email_uk UNIQUE (c_email)
51
SQL*Plus
 Oracle SQL command line utility for
issuing SQL commands
 Starting SQL*Plus
52
Using SQL*Plus
 All commands must be terminated
with a semicolon
 Use a text editor and copy and paste
commands
 Character data is case sensitive and
must be in single quotes
‘M’
‘Sarah’
53
Exiting SQL*Plus
 Type exit at SQL> prompt
or
 Click Close button on SQL*Plus
window
54
Oracle Help Resources
 Ora.hlp file
 Oracle Technology Network (OTN)
 http://otn.oracle.com
55
Viewing Table Information
 Viewing a table’s structure
DESCRIBE table_name;
56
Oracle Data Dictionary

Contains tables that describe the database
structure


Is automatically updated as users create and
modify tables


Is in the SYSTEM user schema
Cannot be updated directly
Contains views that allow users to retrieve
information about the database structure
57
Data Dictionary Views

Views present data in different formats
depending on the privileges of the user



USER: shows all objects belonging to the
current user
ALL: shows all objects belonging to the
current user, as well as objects current
user has privileges to manipulate
DBA: allows users with DBA privileges to
view objects of all database users
58
Querying the Data Dictionary
Views

Syntax:
SELECT field1, field2, …
FROM privilege_viewname;
59
Summary of Oracle
Data Dictionary Views
OBJECTS
All database objects
TABLES
Database tables
INDEXES
Table indexes created to improve query
performance
VIEWS
Database views
SEQUENCES
Sequences created to automatically
generate surrogate key values
USERS
Database users
CONSTRAINTS
Table constraints
CONS_CONSTRAINTS
Table columns that have constraints
IND_COLUMNS
Indexed columns
TAB_COLUMNS
All table columns
60
Modifying Tables
 Unrestricted actions





Renaming tables
Adding new columns
Increasing column sizes
Dropping columns
Dropping constraints
61
Modifying Tables
 Restricted actions
 Dropping tables
 Only allowed if table does not contain any fields that
are referenced as foreign keys, or if foreign key
constraints are dropped
 Changing a column’s data specification
 Only allowed if existing data is compatible with new
data specification
 Decreasing column sizes
 Only allowed if column does not contain any data
 Adding constraints
 Only allowed if existing data meets requirements of
new constraint
62
Altering Tables

Adding a new field:
ALTER TABLE tablename
ADD (fieldname field_specification);
63
Altering Tables

Modifying an existing field:
ALTER TABLE tablename
MODIFY (fieldname new_field_specification);
64
Altering Tables

Deleting an existing field:
ALTER TABLE tablename
DROP COLUMN fieldname;
65
Deleting Tables

Syntax to delete table if no table fields
are referenced as foreign keys:
DROP TABLE tablename;
 Syntax to delete table and constraints if
table contains fields that are referenced
as foreign keys:
DROP TABLE tablename CASCADE CONSTRAINTS;
66