VIEWS

1
VIEWS

• Views
• Create Views
• Benefits of views
• Assigning Privileges
• Index- Advantages Disadvantages
• System Catalog- Its Purpose Use
• Integrity Constraints-
• Create
• Purpose

2
What is the difference between the base and view
tables?

• A view is a derived table from the base table
• It usually includes less information than the
  base table
• Prevents users from accessing sensitive columns
  or tables

3
How to create a view.

• A view is defined by creating a defining query.
• The defining query is an SQL command that
  indicates the rows and columns that will appear
  in the view.
• Example
• SQLgt CREATE VIEW view name AS
• SELECT column names
• FROM table name
• WHERE condition
• Count the customers with the same credit_limit.
• SQLgt CREATE VIEW view name AS
• SELECT CREDIT_LIMIT, COUNT()
• FROM CUSTOMER
• GROUP BY CREDIT_LIMIT

4
Can you have different column names in the view
than in the base table?

• When renaming columns, you include the new column
  names in parentheses.
• EXAMPLE
• SQLgt CREATE VIEWview name new column namesAS
• SELECT column names
• FROM table name
• WHERE condition

5
Benefits of using views.

• The data can be customized to meet each users
  needs.
• Can simplify greatly the users perception of the
  database.
• Security

6
Views that join tables.

• Example
• SQLgt CREATE VIEW SLSREP AS
• SELECT S.SLSREP_NUMBER, LAST, FIRST,
  TOTAL_COMMISSION
• FROM SLSREP_DEMO as S, SLSREP_FIN as F
• WHERE S.SLSREP_NUMBER F.SLSREP_NUMBER
• Dropping a View
• DROP VIEW view name

7
Security

• Prevention of unauthorized access to the
  database.
• Privileges that can be granted
• SELECT-Permits users to retrieve data.
• INSERT- Add data.
• UPDATE-Update data.
• DELETE-Delete Rows.
• INDEX-Create an index.
• ALTER-Change a tables structure.
• ALL-User has all privileges.
• PUBLIC-All users in the database.

8
How to grant privileges.

• GRANT privilege name ON column name TO
  user
• To take away a privilege.
• REVOKE privilege name ON column name FROM
  user

9
Indexes

• You can create and use an index to speed up the
  searching process.
• An index in a DBMS is similar to an index in a
  book.
• Creating a index
• CREATE INDEX index name ON table for index
  (column for index)
• DROP INDEX index name

10
Creating Unique Indexes

• The unique index has all the properties of
  indexes already discussed with one additional
  property the DBMS refuses to accept any update
  that would cause a duplicate value in the column.
• CREATE UNIQUE INDEX column name ON table for
  index (column for index)

11
The System Catalog

• The system catalog contains information
  concerning the tables known to the DBMS.
• To list the table names owned by a specific user
  the command is as follows
• SQLgt SELECT TABLE_NAME
• FROM DBA_TABLES
• WHERE OWNER users name

12
System Catalog Cont

• To list the name of every view owned by a
  specific user the command is as follows
• SQLgt SELECT VIEW_NAME
• FROM DBA_VIEWS
• WHERE OWNER users name
• To list every column and their data types, from a
  certain table the command is as follows
• SQLgt SELECT TABLE_NAME, COLUMN_NAME,
  DATA_TYPE
• FROM DBA_TAB_COLUMNS
• WHERE OWNER user name
• AND TABLE_NAME table name

13
Integrity Constraints

• An integrity constraint is a rule that the date
  in the database must follow.
• 1. Legal Values To ensure that the only legal
  values for item Grad are A, B or C, use one of
  the following
• CHECK (ITEM_GRAD IN(A,B, C))
• Or
• CHECK(ITEM_GRAD A OR ITEM_GRAD B OR
  ITEM_GRAD C)

14
Integrity Constraints Cont

• 2. Primary Keys- The primary key, or the column
  or columns that uniquely identify a row in the
  table, is specified through the ADD PRIMARY KEY
  clause.
• Example
• ADD PRIMARY KEY (column name)
• 3. Foreign keys A column/columns in one table
  whose values match the primary key of another
  table.
• Example
• ADD FOREIGN KEY (column name) REFERENCES (name of
  associated table)

15
Benefits of foreign key.

• After creating the foreign keys, the DBMS rejects
  any update that violates the foreign key
  constraint.