creating database objects
TRANSCRIPT
-
7/27/2019 Creating Database Objects
1/23
Creating database objects
Presented BySatrio Agung Wicaksono
-
7/27/2019 Creating Database Objects
2/23
Learning Outcome
List object hierarchy Create the following objects:
Schema, Table, View, Alias, Index Review the use of Temporary Tables Explore the use and implementation of Check
Constraints, Referential Integrity and Triggers Explore the need for and the use of Large
Objects
-
7/27/2019 Creating Database Objects
3/23
Object Hierarchy
-
7/27/2019 Creating Database Objects
4/23
Creating objects
Create Schema Create Table
Create View Create Alias Create Index Create Trigger
-
7/27/2019 Creating Database Objects
5/23
Schema
A schema is a collection of named objects A schema contain : tables, views, nicknames,
triggers, functions, packages, and otherobjects
Sintax : CREATE SCHEMA PAYROLL AUTHORIZATION
DB2ADMIN; COMMENT ON Schema PAYROLL IS 'schema for
payroll application';
-
7/27/2019 Creating Database Objects
6/23
Set current schema
connect to musicdb user Keith;
select * from employee;
Will select from KEITH.EMPLOYEEset current schema = 'PAYROLL ;
select * from employee; Will select from PAYROLL.EMPLOYEE
-
7/27/2019 Creating Database Objects
7/23
Table Databases store data in tables Table consists of data logically arranged in columns and rows To create a table :
must be connected to a database, either implicitly or explicitly. must have SYSADM or DBADM authority, or, must have CREATETAB privilege and one of either IMPLICIT_SCHEMA
or CREATEIN privilege on the schema. must have USE privilege over all table spaces referenced.
DB2 supports page sizes of 4, 8, 16, and 32 KB Regular tablespaces use a 4-byte Row ID, which allows 16 million
pages with up to 255 rows per page Large table spaces use a 6-byte Row ID, which allows up to 512 million
pages to be addressed
-
7/27/2019 Creating Database Objects
8/23
Types of tables
Regular tables Multidimensional clustering (MDC) tables
Range-clustered tables (RCT) Temporary tables Materialized query tables
-
7/27/2019 Creating Database Objects
9/23
Create Table statement
-
7/27/2019 Creating Database Objects
10/23
Declared Temporary Table
A System Administrator creates the usertemporary table space
The application uses SQL statements todeclare and access the table
-
7/27/2019 Creating Database Objects
11/23
Large objects: The Need
-
7/27/2019 Creating Database Objects
12/23
VIEW
A view is an efficient way of representing datawithout the need to maintain it.
A view is not an actual table and requires nopermanent storage
A view provides a different way of looking atthe data in one or more tables
-
7/27/2019 Creating Database Objects
13/23
the relationship between tables andviews
-
7/27/2019 Creating Database Objects
14/23
Depending on how a view is defined
Deletable views Insertable views
Updatable views Read-only views
-
7/27/2019 Creating Database Objects
15/23
Deletable views Each FROM clause of the outer fullselect identifies only one table (with no OUTER
clause), deletable view (with no OUTER clause), deletable nested table expression,or deletable common table expression.v
The database manager needs to be able to derive the rows to be deleted in thetable using the view definition. Certain operations make this impossible
A grouping of multiple rows into one using a GROUP BY clause or column functions result in aloss of the original row and make the view non deletable.
Similarly when the rows are derived from a VALUES there is no table to delete from. Again theview is not deletable.
The outer fullselect doesn't use the GROUP BY or HAVING clauses. The outer fullselect doesn't include column functions in its select list. The outer fullselect doesn't use set operations (UNION, EXCEPT, or INTERSECT)
with the exception of UNION ALL The tables in the operands of a UNION ALL must not be the same table, and each
operand must be deletable. The select list of the outer fullselect does not include DISTINCT
-
7/27/2019 Creating Database Objects
16/23
Insertable views A view is insertable if an INSTEAD OF trigger for the
insert operation has been defined for the view, or atleast one column of the view is updatable(independent of an INSTEAD OF trigger for update),
and the full select of the view does not include UNIONALL. A given row can be inserted into a view (including a
UNION ALL) if, and only if, it fulfills the checkconstraints of exactly one of the underlying tables.
To insert into a view that includes non-updatablecolumns, those columns must be omitted from thecolumn list
-
7/27/2019 Creating Database Objects
17/23
Updatable views
The view is deletable. The column resolves to a column of a table (not
using a dereference operation) and the READONLY option is not specified.
All the corresponding columns of the operands of a UNION ALL have exactly matching data types
(including length or precision and scale) andmatching default values if the fullselect of theview includes a UNION ALL.
-
7/27/2019 Creating Database Objects
18/23
Read-only views
A view is read-only if it is not deletable,updatable, or insertable.
A view can be read-only if it is a view thatdoes not comply with at least one of the rulesfor deletable views
-
7/27/2019 Creating Database Objects
19/23
-
7/27/2019 Creating Database Objects
20/23
Index
Indexes might allow more efficient access torows in a table.
Unique indexes ensure uniqueness of theindex key.
An index key is a column or an orderedcollection of columns on which an index isdefined
-
7/27/2019 Creating Database Objects
21/23
Overview of triggers: The need
-
7/27/2019 Creating Database Objects
22/23
Business rules that might be supportedby triggers include
Validation of input Automatic generation of a value for an
inserted row Reading from other tables for cross-
referencing purposes Writing to other tables for audit-trail purposes Maintaining business rules Providing support for user alerts
-
7/27/2019 Creating Database Objects
23/23