Transcript Database Concepts - Integrated Statistics

SQL Training
Database Concepts
Lesson Objectives
At the end of this lesson you will be able to:
• Explain how data is stored in a relational database
• Describe the purpose primary and foreign keys
• Explain the role of SQL
• Explain the role of referential integrity
• Explain the role of indexes in improving database performance
• Explain the purpose of the Oracle Catalog
Page 2
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Lesson Plan
What is a Relational Database
Introduction to Oracle
What is SQL
Creating Database Tables
Preserving Data Integrity
Improving Performance
Using the Database Catalog
Page 3
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
What is a Relational Database?
What is a Table?
A table is a set of related data that contains:
Product
Columns, Fields, Attributes
Rows, Records
productID
productTypeID
productName
1
1
Floor Measurement
2
1
Consumer Solutions
3
1
Astra Applications
4
1
Assortment Space
5
1
Customized Research
7
1
Newspaper
8
5
Television
9
5
Radio
10
5
Magazines
Rows, Records
Columns, Fields, Attributes
A Relational Database is a set of related Tables.
Page 5
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Relational Database
Each table must follow these rules:
•
Each column has a unique name.
•
Entries in columns are single-valued (not a list of values).
•
Entries in columns are of the same kind.
•
Each row is unique. No two rows can have the same primary key value.
SubscribedProduct
subscribedProductID productID deliveryTypeID
subscribedProductName
startEffectiveDate endEffectiveDate
42
2
3
Secret Shopper
4-Mar-04
109
5
3
Corporate| concept work
6-May-07
112
5
3
Corporate| BASES
12-Dec-04
111
5
3
Corporate| Magazine Index
20-Oct-07
110
5
3
Corporate| Order Track
2-May-04
50
2
3
Corporate Channel
15-May-04
Note: All rows are not shown.
Primary Key
Page 6
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Relational Database
Tables are related by common fields.
Page 7
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Relational Database
ProductType
productTypeID
productTypeName
1 Retail
2 Consumer
3 Mobile
4 Print
5 Media
Primary & Foreign Keys
Create Relationships
pk
Product
productID
1
2
3
4
5
7
8
9
10
productTypeID
1
1
1
1
1
1
5
5
5
pk
fk
productName
Measurement
Consumer Solutions
Astra Applications
Assortment Space
Customized Research
Newspaper
Television
Radio
Magazine
DeliveryType
deliveryTypeID
1
2
3
deliveryTypeDescription
Database
Report
Application
pk
SubscribedProduct
subscribedProductID
productID
deliveryTypeID
subscribedProductName
42
2
3
Secret Shopper
4-Mar-04
109
5
3
Corporate| concepts work
6-May-07
112
5
3
Corporate| BASES
12-Dec-04
111
5
3
Corporate| Magazine Index
20-Oct-07
110
5
3
Corporate| Order Track
2-May-04
50
2
3
CorporateChannel
15-May-04
pk
fk
fk
Page 8
startEffectiveDate
endEffectiveDate
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Normalization
Table Design - Normalization
• Normalization is a set of guidelines (steps) used to optimally design a database
to reduce redundant data and modification anomalies.
•
Advantages:
•
•
•
Data redundancy can be reduced.
•
Efficient disk space usage
•
Increase efficiency of database updates
•
Increase data integrity.
Minimize unwanted side effects of database updates, inadvertent deletions or
insertion errors.
Disadvantages:
•
Requires more complicated SQL coding.
•
The more tables you have to join to retrieve the data the slower your query runs.
•
Can increase I/O and CPU overhead. Reduced database performance.
10
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
First Normal Form
•
A table is said to be in First Normal Form if:
•
The Table does not contain any repeating groups.
•
Each column has a unique name.
•
The order of the columns doesn’t matter.
•
No two rows may be identical.
•
Each cell must contain a single value.
Vendor
Vendor
Contact Name
Phone
Component
Price
Vendor may supply many parts.
•Vendor Table Data
Vendor
Contact Name
Phone
Components Sold
Prices
Cable Specialists
Dennis Limanek
321-985-9658
Flux Capacitor, Fuel Cell
5.06, 987.65
Switched Components Inc.
Sharon Linnell
659-854-9658
Inverter, Steel Enclosure
5.02, 51.23
GenCo
Frank Barbuto
517-711-1547
Cell Housing, Base Slab
9.50,18.77
Inverter Supplies
Lisa Allega
123-145-9652
Breaker Board
32.20
Stabilized Switching Inc
Susan McMahon
652-451-8522
DC Switch, Transformer
12.00,13.25
Component Source
Paul Butot
470-256-9853
Conduit Tray
29.98
11
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
First Normal Form
• To put the table in 1NF we will break the data into two separate tables.
Vendor
Vendor
VendorComponent
Name
Contact Name
Phone
Component
Price
VendorID
VendorName
VendorFirstName
VendorLastName
Phone
VendorID
Component
Price
Vendor
VendorID
VendorName
VendorFirstName
VendorLastName
Phone
1
Cable Specialists
Dennis
Limanek
321-985-9658
2
Switched Components Inc.
Sharon
Linnell
659-854-9658
3
GenCo
Frank
Barbuto
517-711-1547
4
Inverter Supplies
Lisa
Allega
123-145-9652
5
Stabilized Switching Inc
Susan
McMahon
652-451-8522
6
Component Source
Paul
Butot
470-256-9853
VendorComponent
VendorID
Component
Price
1
Flux Capacitor
5.06
1
Fuel Cell
987.65
2
Inverter
5.02
2
Steel Enclosure
51.23
3
Cell Housing
9.50
3
Base Slab
18.77
4
Breaker Board
32.20
5
DC Switch
12.00
5
Transformer
13.25
6
Conduit Tray
29.98
12
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
First Normal Form
• We added primary and foreign key fields to the tables.
Primary Key
Vendor
VendorComponent
VendorID
VendorID
VendorName
VendorFirstName
VendorLastName
Phone
Component
Price
Foreign Key
Dependent/Child Table
Parent Table
•
Primary Key  Uniquely identifies a row, record in parent table.
•
Foreign Key  Matching row in another table, record in child table.
•
Primary – Foreign keys form the One to Many relationships between tables.
13
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Second Normal Form
• A Table is in Second Normal Form if it is in 1NF and every
• Non-Keyed column is fully dependent on the entire primary key.
Warehouse
WarehouseID
Name
Manager
Phone
Fax
Address
City
Province
Postal Code
Inventory
ComponentID
WarehouseID
Description
Weight
StockedQty
• Are these tables in Second Normal Form?
14
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Second Normal Form
• A Table is in Second Normal Form if it is in First Normal Form
• And every Non-Keyed column is fully dependent on the entire primary key.
Warehouse
NO
WarehouseID
Name
Manager
Phone
Fax
Address
City
Province
Postal Code
Inventory
ComponentID
WarehouseID
Description
Weight
StockedQty
• The Description and Weight fields are dependent only on the ComponentID
key. Neither have anything to do with WarehouseID.
• How do we fix it?
15
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Second Normal Form
• A Table is in Second Normal Form if it is in First Normal Form
• And every Non-Keyed column is fully dependent on the entire primary key.
Warehouse
Inventory
ComponentID
WarehouseID
Description
Weight
StockedQty
Component
Inventory
ComponentID
Description
Weight
ComponentID
WarehouseID
StockedQty
WarehouseID
Name
Manager
Phone
Fax
Address
City
Province
Postal Code
• To put the tables in 2NF we put the Description and Weight fields into a new
table named Component.
• Description and Weight are fully dependent on the primary key of the
Component table (ComponentID)
• StockedQty is fully dependant on the primary key of Inventory (ComponentID,
WarehouseID).
16
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Third Normal Form
• A Table is in Third Normal Form if it is in Second Normal Form and every NonKeyed Column is NOT dependent on any other Non-Keyed column.
• Look for calculated fields.
•
Look for non-key fields that serve as a primary key in another table.
•Product
•ProductID
•FuelSourceID
•ProductCode
•ProductDescription
•ProductPrice
•PowerRating
•Voltage
•Frequency
•ShippingCost
•Weight
• The key, the
whole key, and
nothing but the
key.
• Shipping Cost = (Weight * Carrier Mileage rate * Mileage) +
•
Carrier Fixed Rate + Delivery Method Charge
• Weight = Sum (Weight of all Components)
17
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Can a database be Too Normalized?
•
•
•
When designing a database you need to keep two important objectives in
mind:
•
1.
Keep your database free of modification anomalies
•
2.
Speed
Unfortunately, these are two competing objectives.
•
To make sure modification anomalies don’t corrupt your data, you
should normalize your Tables.
•
However, separating data into different Tables will slow operations.
You need to use good judgment and common sense to arrive at a
compromise to provide acceptable data integrity and performance.
18
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Structure of an ORACLE Database
Database Object Hierarchy
Database
Tablespace
Table
Index
View
Page 20
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Database
Users Tablespace
users01.dbf
System Tablespace
users02.dbf
system01.dbf
• Oracle uses the word database to mean the physical and logical structure that you use to
store information.
• In an oracle database you can create many different database objects (tables, views,
indexes, etc.) All objects are stored in this one database.
• Internally, the Oracle database is divided into a System Tablespace, and one or more User
Tablespaces.
• The System Tablespace holds the Oracle Catalog rows. The User Tablespace holds the
rows of all User Tables.
Page 21
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Tablespace
• Tables reside in tablespaces.
Tablespaces reside in databases.
• Oracle stores data logically in
tablespaces and physically in
datafiles.
• A tablespace may contain multiple
tables.
• All databases have at least one
tablespace, the System
Tablespace, which Oracle creates
automatically when you create the
database.
Page 22
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
What is Structured Query Language?
Structured Query Language
Data Control
(DCL)
• Grant
• Revoke
User Privileges
Data Definition
(DDL)
Data Manipulation
(DML)
•
•
•
•
• Create
• Alter
• Drop
Tables, Views,
Constraints
Select
Insert
Update
Delete
Retrieve and
Manipulate Data
• SQL is the language most commonly used to create and process data
in relational databases.
• SQL can be used with Access, DB2, MySQL, Oracle, MS SQL Server,
Sybase, or any other relational database.
Page 24
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
A Little Practice
1. To count the rows in a table, use the following command:
Select count(*) from table_name;
2. To view the rows in a table, use the following command:
Select * from table_name;
3. To view the definition of the table, use the following command:
Describe table_name;
Page 25
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Creating Database Tables
Creating Tables
•
A table contains many columns. Each column must include a Data Type, Value
Set (optional), and a Mandatory indicator.
•
Valid Oracle Data Types include Number, VarChar2, Char, and Date.
•
Value Sets can include itemized values and ranges.
•
Mandatory fields cannot be Null.
Product Table
Attribute
Data Type
Mandatory
ProductID
INTEGER
yes
ProductTypeID
INTEGER
yes
ProductName
VARCHAR2(100)
yes
Note: Use the “describe” SQL command to display the table definition in the Oracle catalog:
For example: describe Product;
Page 27
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Common Oracle Datatypes
Datatype
Parameters
Example
Description
VARCHAR2(n)
n = 1 to 4,000
VARCHAR2(25)
Text string with a variable length. Specify the
maximum length (n) when defining the column.
If your data is shorter than the maximum size,
Oracle adjusts the length of the column to the
size of the data, which is a great space-saver.
NUMBER(p,s)
p = 1 to 38,
s = -84 to 127
NUMBER(10,2)
Use this datatype only for numbers. Specify the
precision (p), which is the number of digits, and
the scale (s), which is the number of digits to
the right of the decimal place. Oracle truncates
data that doesn’t fit into the scale.
If you use INTEGER as the datatype, Oracle
will create the field with a datatype of
Number(38).
DATE
None
DATE
Use this datatype for Date, Time and
Timestamp
CHAR(n)
n = 1 to 2000
CHAR(14)
Text string with a fixed length.
Page 28
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Creating Tables
Oracle uses Structured Query Language (SQL) to create tables.
create table ProductType (
productTypeID
INTEGER
not null,
productTypeName
VARCHAR2(50)
not null,
constraint pkproductType primary key (productTypeID)
);
create table Product (
productID
INTEGER
not null,
productTypeID
INTEGER
not null,
productName
VARCHAR2(100)
not null,
constraint pkproduct primary key (productID),
constraint fkproduct_producttype foreign key (productTypeID)
references ProductType (productTypeID)
);
Page 29
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Create Table
As you create Oracle Tables and Indexes, you must provide a valid
name for the object. These names must adhere to the following rules:
1.
Names must be from 1 to 30 characters long.
2.
Names can be case sensitive depending on deployment. Our
Oracle server is not case sensitive.
3.
A name must begin with an alphabetic character.
4.
Names can only contain alphanumeric characters _, $, and #.
5.
A name cannot be an Oracle reserved word.
For a listing of Oracle reserved words:
http://download.oracle.com/docs/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
Page 30
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Preserving Data Integrity
Column Domain Constraints
You can constrain column values in a table to a particular range.
The column constraint will be validated when a column value is updated or inserted.
create table NielsenUser (
nielsenUserID
INTEGER
not null,
userFirstName
VARCHAR2(50)
not null,
userLastName
VARCHAR2(50)
not null,
userDepartment
VARCHAR2(50)
not null,
userLocation
VARCHAR2(50),
hourlyRate
NUMBER(7,2)
not null,
overtimeRate
NUMBER(7,2)
not null,
workPhone
VARCHAR2(25),
mobilePhone
VARCHAR2(25),
email
VARCHAR2(50),
constraint pkNielsenUser primary key (nielsenUserID),
constraint CHECK_hourlyRate Check (hourlyRate > 45));
Page 32
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Column Domain Constraints
If you try to enter an
hourly rate less
than 45, you will
receive an error.
Page 33
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Referential Integrity
Parent Table
Parent & Child Table
Child Table
Primary Key: ProductID
Foreign Key: ModalityID
Primary Key: CustomerTypeID
Primary Key: CustomerID
Foreign Key: CustomerTypeID
Primary Key: UserID
Foreign Key: CustomerID
Referential Integrity
“Thou shall not create orphans.”
Every child (foreign key) must have a matching parent (primary key).
You can not delete a parent if there is a matching child.
Referential Integrity
helps to maintain
data integrity.
You can not add a child record if you do not have a matching parent record.
Page 34
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Referential Integrity
customerID
1
2
3
4
5
6
7
customerTypeID
2
2
2
2
2
2
2
Parent Table: Customer
customerName
Avon Products Inc.
Church & Dwight Co. Inc.
Kimberly-Clark
Pepsico, Inc.
McDonald's Corp.
The Coca-Cola Company
Kellogg Co.
customerSymbol
AVP
CHD
KMB
PEP
MCD
KO
K
Child Table: Users
userID
22
39
148
227
75
100
171
139
190
69
customerID
1
1
2
2
3
3
4
5
5
6
street
11873 Springs Rd
440 W Laurel Ave
320 Beard Creek Rd
2003 Bluegrass Cir
718 Avenida de Independence
300 Highway 10 E
3800 Eastside Hwy
718 Avenida de Independence
1756 Park Ave
67 Oneida Street
city
New York
New York
Princeton
Princeton
Dallas
Dallas
New York
Oak Brook
Oak Brook
Atlanta
postalcode
10105-0196
10105-0196
08543-5297
08543-5297
75261-9100
75261-9100
10577
60523
60523
30313
firstName
Betty
Holly
Martha
Elizabeth
Miguel
James
Lily
Miguel
Andrea
Ronnie
lastName
Smith
Terry
Johnson
Pope
Cortes
Kellock
Parker
Cortes
Allen
Fischer
In the Customer table change customerName to “Avon Inc” for CustomerID 1.
Allowed
Delete UserID 1 from the Users Table.
Add CustomerID 8 to the Customer Table.
Page 35
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Referential Integrity
customerID
1
2
3
4
5
6
7
customerTypeID
2
2
2
2
2
2
2
Parent Table: Customer
customerName
Avon Products Inc.
Church & Dwight Co. Inc.
Kimberly-Clark
Pepsico, Inc.
McDonald's Corp.
The Coca-Cola Company
Kellogg Co.
customerSymbol
AVP
CHD
KMB
PEP
MCD
KO
K
Child Table: Users
userID
22
39
148
227
75
100
171
139
190
69
customerID
1
1
2
2
3
3
4
5
5
6
street
11873 Springs Rd
440 W Laurel Ave
320 Beard Creek Rd
2003 Bluegrass Cir
718 Avenida de Independence
300 Highway 10 E
3800 Eastside Hwy
718 Avenida de Independence
1756 Park Ave
67 Oneida Street
city
New York
New York
Princeton
Princeton
Dallas
Dallas
New York
Oak Brook
Oak Brook
Atlanta
postalcode
10105-0196
10105-0196
08543-5297
08543-5297
75261-9100
75261-9100
10577
60523
60523
30313
firstName
Betty
Holly
Martha
Elizabeth
Miguel
James
Lily
Miguel
Andrea
Ronnie
lastName
Smith
Terry
Johnson
Pope
Cortes
Kellock
Parker
Cortes
Allen
Fischer
Change CustomerID 1 to CustomerID 2 in the Customer Table.
Delete CustomerID 3 from the Customer Table.
Not Allowed
Add UserID 999 with a CustomerID 8 to the Users Table.
Page 36
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Adding Constraints
create table Users (
userID
INTEGER not null,
customerID
INTEGER not null,
provinceID
INTEGER not null,
roleID
INTEGER not null,
street
VARCHAR2(100),
city
VARCHAR2(50),
postalCode
VARCHAR2(25),
firstName
VARCHAR2(50),
lastName
VARCHAR2(50),
phone
VARCHAR2(25),
email
VARCHAR2(50),
fax
VARCHAR2(25),
constraint pkusers primary key (userID),
constraint fkuser_customer foreign key (customerID) references Customer (customerID));
Page 37
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Implementing Referential Integrity
DELETE CASCADE
When rows of a Parent Table are deleted, all associated Child Table
rows are also deleted.
create table Users (
userID
INTEGER not null,
customerID
INTEGER not null,
provinceID
INTEGER not null,
roleID
INTEGER not null,
street
VARCHAR2(100),
…
fax
VARCHAR2(25),
constraint pkusers primary key (userID),
constraint fkuser_customer foreign key (customerID) references Customer (customerID)
on delete cascade);
Is this a good thing to do?
Page 38
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Implementing Referential Integrity
You can also alter the Tables and add the referential integrity rules.
To Create the Primary Key and Foreign Key for the Customer and User Tables:
ALTER TABLE Customer
ADD CONSTRAINT pkCustomer PRIMARY KEY (CustomerID);
ALTER TABLE Users
ADD CONSTRAINT
fkCustomer_Users FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);
Page 39
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Using Primary and Foreign Keys
When using primary and foreign keys remember :
• Use the same field name for both the primary and foreign key
• Names should be descriptive
• Keep the same data types between primary and foreign keys
• Keys should be numeric
• Improve load performance by adding constraints after the data is loaded
Page 40
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Alter Table - Examples
To add a column to the Product Table:
ALTER TABLE Product
ADD (IssueDate Date);
To increase the column size to 60 characters:
ALTER TABLE Product
MODIFY (ProductNameVARCHAR(100));
To set the default value of a column.
ALTER TABLE Incident
MODIFY (IncidentStatusID INTEGER DEFAULT 1);
To drop a referential integrity constraint on a table:
ALTER TABLE Product
DROP CONSTRAINT pkProduct CASCADE;
Note: The CASCADE option drops any foreign keys that reference the primary key.
ALTER TABLE Product
DROP CONSTRAINT fkProduct_ProductType;
Page 41
•Confidential & Proprietary
Copyright
Copyright©©2009
2009Cardinal
The Nielsen
Directions,
Company
Inc.
Improving Performance
Indexes for Performance
Disk Space Requirements
Locks during Update
Primary Key Constraints
Query Performance
•
Oracle provides two types of indexes: Unique and Non-unique.
•
Unique indexes enforce unique key constraints and improve query performance.
•
Non-unique indexes improve query performance.
•
Indexes are implemented internally as B-tree structures.
Index rules include:
•
An Order By statement in a Select statement can reference any column in a table – whether or not the
column is indexed.
•
The maximum number of columns in an index is 16.
•
An index does not store Null values.
•
An index can be created only for a Table, not a View.
Page 43
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Create Index
CREATE INDEX ULastName on Users (LastName);
<Jensen
Jensen
Branch Blocks
Jensen
Monroe
Withrow
<Blackman
Blackman
Harris
Adams
Allen
Blackman
Dobler
Einstein
Leaf Blocks
Harris
Janus
Jensen
Miller
Jensen - RowID
Miller
- RowID
Page 44
Monroe
Thomas
Stevens
Withrow
Wronski
Oracle uses B-Tree indexes that are balanced
to equalize access time to any row.
Branch blocks point to lower level index blocks.
The lowest level index blocks (leaf blocks)
contain the corresponding RowID that is used
to locate the actual row.
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Create Index
CREATE UNIQUE INDEX idxCustomerName
ON Customer (customerName);
CREATE INDEX idxCity ON Users (city);
• When you create an Index, Oracle automatically allocates the
index data in the database.
• Unique indexes enforce unique key constraints and improve
query performance.
• Non-unique indexes improve query performance.
Page 45
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Database Catalog
Databases are Self Describing
Oracle Catalog
A Database is self-describing. All the information about the database is
stored in the database.
•
CATALOG
The Catalog holds all database
objects.
•
The Catalog is a set of
Relational Tables.
•
The Catalog can be queried to
gather information.
•
The Catalog can only be
updated with DDL commands.
Update, Insert and Delete SQL
commands cannot be issued
“directly” against the catalog.
Relational Objects
Databases
Indexes
Create Database
Create Table
Tables
Views
Create View
Create Index
Columns
Page 47
Keys
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog - Describe
DESCRIBE Customer;
Page 48
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
DBA Catalog versus USER Catalog
• For MANY of the examples we will look at, there is a DBA and a
USER view.
• For example: DBA_Catalog and USER_Catalog view.
• As you would guess, the DBA views are only available if you
have DBA privileges.
• The type of data shown by each view is the same. The
difference is that the DBA view will show the data for ALL users
and the USER view will show the data only for the current user.
Page 49
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
USER_CATALOG: Contains one row
for every Table or View owned by the
current user.
SELECT *
FROM User_Catalog;
Note: Use this User_Catalog query to
determine if all the Tables in the next workshop
are created successfully.
Page 50
TABLE_NAME
TABLE_TYPE
CUSTOMERTYPE
TABLE
CUSTOMER
TABLE
GEOGRAPHICREGION
TABLE
COUNTRY
TABLE
PROVINCE
TABLE
ROLES
TABLE
USERS
TABLE
DELIVERYTYPE
TABLE
PRODUCTTYPE
TABLE
PRODUCT
TABLE
SUBSCRIBEDPRODUCT
TABLE
PROCESS
TABLE
SUBPROCESS
TABLE
FACTORYSCHEDULE
TABLE
FACTORY
TABLE
SUBSCRIBEDPRODUCTUSERS
TABLE
LOGONEVENT
TABLE
SCHEDMAINT
TABLE
INCIDENTPRIORITY
TABLE
INCIDENTCATEGORY
TABLE
INCIDENTTYPE
TABLE
INCIDENTSTATUS
TABLE
INCIDENT
TABLE
CORPORATEUSER
TABLE
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
USER_CONSTRAINTS: Contains a description of the referential constraints placed on
Tables in the Catalog. Search this Table on the Table_Name column.
For example, Where Table_Name = “PRODUCT”.
USER_CONS_COLUMNS: Contains rows that match the constraint name to Table
columns.
SELECT C.owner, C.constraint_name, constraint_type,
C.table_name, column_name
FROM user_constraints C, user_cons_columns CC
WHERE C.owner = CC.owner and
C.constraint_name = CC.constraint_name and
C.table_name = CC.table_name and
C.table_name = 'PRODUCT';
OWNER
CONSTRAINT_NAME
CONSTRAINT_TYPE TABLE_NAME
CORPORATEOLTP
SYS_C0030667
C
PRODUCT
PRODUCTID
CORPORATEOLTP
SYS_C0030668
C
PRODUCT
PRODUCTTYPEID
CORPORATEOLTP
SYS_C0030669
C
PRODUCT
PRODUCTNAME
CORPORATEOLTP
PKPRODUCT
P
PRODUCT
PRODUCTID
CORPORATEOLTP
FKPRODUCT_PRODUCTTYPE
R
PRODUCT
PRODUCTTYPEID
Page 51
COLUMN_NAME
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
USER_TABLES: Contains one row for each table in the Catalog. Only tables for
the current user will be listed. For example, Where Owner = ‘CORPORATEOLTP’.
USER_TAB_COLUMNS: Contains one row for every column of each table
including the columns of the Catalog tables.
SELECT COLUMN_ID, COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'PRODUCT'
ORDER BY COLUMN_ID;
COLUMN_ID
COLUMN_NAME
DATA_TYPE
DATA_LENGTH
1
PRODUCTID
NUMBER
22
0
N
2
PRODUCTTYPEID
NUMBER
22
0
N
3
PRODUCTNAME
VARCHAR2
100
Page 52
DATA_PRECISION
DATA_SCALE
NULLABLE
N
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
USER_INDEXES: Contains one row for every index, including the indexes on
Catalog tables.
USER_IND_COLUMNS: Contains rows that match the index name to Table
columns.
SELECT Index_Name, Index_Name, Table_Name, Column_Name
FROM USER_IND_COLUMNS
WHERE Table_Name = 'CUSTOMER';
INDEX_NAME
INDEX_NAME
TABLE_NAME
COLUMN_NAME
PKCUSTOMER
PKCUSTOMER
CUSTOMER
CUSTOMERID
IDXCUSTOMERNAME
IDXCUSTOMERNAME
CUSTOMER
CUSTOMERNAME
Page 53
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog
USERS_VIEWS: Contains one or more rows for each view. The text of each
View in the Catalog is kept in this table.
SELECT View_Name, Text
FROM USER_Views;
VIEW_NAME
SAMPLE
TEXT
Select customername, customersymbol, billingcity, billingprovince,
billingpostalcode…….
Note: The text is truncated. To avoid this you must first …
Set Long 8192
Run the SQL again. This time you will get …
VIEW_NAME
SAMPLE
TEXT
Select
customername,customersymbol,billingcity,billingprovince,billingpostalcode,userid,firstname,
lastname from customer c, users u where c.customerid = u.customerid;
Page 54
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Oracle Catalog - Quick Reference
Catalog Table Name
Purpose
USER_CATALOG
Contains one row for every Table or View in the Catalog. Use this catalog table to get a
list of all Tables attached to an Owner, for example ’CORPORATEOLTP’.
USER_CONSTRAINTS
Contains a description of the referential constraints placed on Tables in the Catalog.
Search this Table on the Table_Name column.
For example, Where Table_Name = ‘CUSTOMER’.
USER_CONS_COLUMNS
Contains rows that match the constraint name to Table columns.
USER_INDEXES
Contains one row for every index, including the indexes on Catalog tables.
USER_IND_COLUMNS
Contains rows that match the index name to Table columns.
USER_TABLES
Contains one row for each table in the Catalog. Search this table on the Owner
column. For example, Where Owner = ‘CORPORATEOLTP’.
USER_TAB_COLUMNS
Contains one row for every column of each table including the columns of the Catalog
tables.
USER_USERS
Contains a row for each User in the Catalog. UserIDs and Passwords are kept in this
table. Passwords are encrypted.
USER_VIEWS
Contains one or more rows for each view. The text of each View in the Catalog is kept
in this table.
USER_DATA_FILES
Contains the name of each datafile, its size, and its associated tablespace.
Page 55
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Class Discussion – Review Key Concepts
• How data is stored in a relational database?
• What is the purpose primary and foreign keys?
• What is referential integrity? Why is it important?
• What is the role of indexes?
• What is SQL?
• What is the purpose of the Oracle Catalog?
Page 56
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Workshop
Workshop - Create Tables
1. Add the following tables to the database using a script:
 yourlastname_F_Schedule
 yourlastname_LogonEvent
 yourlastname_Survey
When you code the Create Table statements, make sure that you code Parent tables
before you code Child Tables (always create Parents before Children).
2. In the same file, create a series of Drop Table statements. They will take the format: Drop
Table tablename; Place the drop statements before the create statements. Drop all Tables
in the proper sequence (always drop Children before Parents). Your file will now resemble
this:
•
•
•
•
•
Drop Table users;
Drop Table customer;
Create Table users…
Create Table customer….
Create Index idx_billingcity….
Then save the _CreateSchema.sql file. (See Handout A as an example.)
3. Test your create schema script.
Page 58
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Add these tables to the Database
Column Specifications
yourlastname_F_Schedule
Column Name
Null?
Type
F_SCHEDULEID (PK)
NOT NULL
INTEGER
SUBPROCESSID
NOT NULL
INTEGER
PRODUCTTYPEID
NOT NULL
INTEGER
PROCESSID
NOT NULL
INTEGER
LEVELNUMBER
NOT NULL
INTEGER
DAYDESCRIPTION
NOT NULL
VARCHAR2(50)
ESTIMATEDSTARTDATE
NOT NULL
DATE
ESTIMATEDENDDATE
NOT NULL
DATE
ESTIMATEDELAPSEMINUTES
NOT NULL
NUMBER(38)
Page 59
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Add these tables to the Database
Column Specifications
yourlastname_LogonEvent
Column Name
Null?
Type
LOGONEVENTID (PK)
REQUIREDPRODUCTUSERSID
LOGONDATE
LOGOFFDATE
DATABASEREADS
DATABASEWRITES
RESPONSETIME
DBRESPONSETIME
NETWORKRESPONSETIME
MEMORYUSAGE
CPUUTILIZATION
DISKUTILIZATION
CONCURRENTUSERS
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
INTEGER
INTEGER
DATE
DATE
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
Page 60
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Add these tables to the Database
Column Specifications
yourlastname_Survey
Column Name
Null?
Type
SURVEYID (PK)
CUSTOMERID
USERID
REQUIREDPRODUCTID
SURVEYDATE
Q1
Q2
Q3
Q4
Q5
Q6
Q7
NOT NULL
NOT NULL
NOT NULL
NOT NULL
INTEGER
INTEGER
INTEGER
INTEGER
DATE
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
Page 61
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.