Enhanced Guide to Oracle8i

1
Enhanced Guide to Oracle8i
Chapter 2 Creating and Modifying Database
Objects
2
Database Objects

• An Oracle database consists of multiple user
  accounts
• Each user account owns database objects
• Tables
• Views
• Stored programs
• Etc.

3
Database Queries

• Query command to perform operation on database
  object
• Create
• Modify
• View
• Delete
• Structured Query Language (SQL)
• Standard query language for relational databases

4
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

5
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

6
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

7
User Accounts

• Each Oracle database user has a user schema
• Area in the database where the users database
  objects are stored
• Identified by a unique username and protected by
  a password
• Each user schema is granted specific privileges

8
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

9
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

10
Creating New User Accounts

• Done by DBA
• Syntax
• CREATE username IDENTIFIED BY password

11
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
12
Granting System Privileges

• Done by DBA
• Syntax
• GRANT privilege1, privilege2, TO username

13
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 roles privileges
• Used to easily assign groups of related
  privileges to users

14
1. Creating Roles

• Syntax
• CREATE ROLE role_name

15
2.Assigning Privileges to a Role

• Syntax
• GRANT privilege1, privilege2,
• TO role_name

16
3.Assigning a Role to a User

• Syntax
• GRANT role_name TO user_name

17
Revoking System Privileges

• Syntax
• REVOKE privilege1, privilege2,
• FROM username

18
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

19
Users Account
DBA_User (System/ocmt)
User_2
User_1
T
T
T
T
T
T
20
Defining Database Tables

• To create a table, you must specify
• Table name
• Field names
• Field data types
• Field sizes
• Constraints

21
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

22
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

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

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

25
Character Data Types

• NCHAR
• Supports 16-digit binary character codes
• Used for alternate alphabets

26
Number Data Type

• NUMBER
• stores values between 10-130 and 10126
• General declaration format
• variable_name NUMBER(precision, scale)

27
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

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

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

30
Floating Point Numbers

• Contain a variable number of decimal places
• Precision and scale are omitted
• Sample declaration
• s_GPA NUMBER

31
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 HHMISS AM
• example 05-JUN-03 120000 AM
• Sample declaration
• s_dob DATE

32
Specifying Date and Time Values

• If no time value is given when a new date is
  inserted, default value is 120000 AM
• If no date value is given when a new time is
  inserted, default date is first day of current
  month

33
Large Object (LOB) Data Types

• Binary Large Object (BLOB)
• Stores up to 4 GB of binary data
• Character Large Object (CLOB)
• Stores up to 4 GB of character data
• BFILE
• Stores a reference to a binary file maintained in
  the operating system
• NCLOB
• Character LOB that supports 16-bit character code

34
Declaring LOB Data Fields

• Item size is not specified
• Examples
• item_image BLOB
• item_image BFILE

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

36
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

37
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

38
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

39
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

40
Nationality_nat_no_Pk
41
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

42
Primary Key Constraints

• Can be defined when field is declared

43
Primary Key Constraints

• Can also be defined after all table field
  definitions are completed

44
Composite Primary Keys

• Syntax
• CONSTRAINT constraint_name
• PRIMARY KEY (field1, field2)
• Must be defined after fields that compose key are
  defined

45
H.W

• What is the default value for Number Data
  type??
• Means
• If the user did not specify the size of the
  field

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

47
Foreign Key Constraints

• Can be defined when field is declared

48
Foreign Key Constraints

• Can also be defined after all table field
  definitions are completed

49
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

50
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

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

52
SQLPlus

• Oracle SQL command line utility for issuing SQL
  commands
• Starting SQLPlus

53
Using SQLPlus

• 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

54
Exiting SQLPlus

• Type exit at SQLgt prompt
• or
• Click Close button on SQLPlus window

55
Oracle Help Resources

• Ora.hlp file
• Oracle Technology Network (OTN)
• http//otn.oracle.com

56
Viewing Table Information

• Viewing a tables structure
• DESCRIBE table_name

57
Oracle Data Dictionary

• Contains tables that describe the database
  structure
• Is in the SYSTEM user schema
• Is automatically updated as users create and
  modify tables
• Cannot be updated directly
• Contains views that allow users to retrieve
  information about the database structure

58
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

59
Querying the Data Dictionary Views

• Syntax
• SELECT field1, field2,
• FROM privilege_viewname

60
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
61
Modifying Tables

• Unrestricted actions
• Renaming tables
• Adding new columns
• Increasing column sizes
• Dropping columns
• Dropping constraints

62
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 columns 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

63
Altering Tables

• Adding a new field
• ALTER TABLE tablename
• ADD (fieldname field_specification)

64
Altering Tables

• Modifying an existing field
• ALTER TABLE tablename
• MODIFY (fieldname new_field_specification)

65
Altering Tables

• Deleting an existing field
• ALTER TABLE tablename
• DROP COLUMN fieldname

66
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