SQL Data Definition: Understanding Data Types, Views, Transactions, and Access Control, Slides of Database Management Systems (DBMS)

Download SQL Data Definition: Understanding Data Types, Views, Transactions, and Access Control and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Chapter 6

SQL: Data Definition

Overview

• Data types supported by SQL.
• Integrity enhancement feature of SQL.
• Purpose and how to create views.
• Advantage and disadvantage of views.
• Transactions.
• Access Control.

Integrity Enhancement Feature

• Required data
• Domain constraints
• Entity integrity
• Referential integrity
• Enterprise constraints

Required data

• Some columns contain a valid value must specified NOT NULL.
• Example:

position VARCHAR(10) NOT NULL

Entity integrity

• The primary key of a table must contain a unique, non-null value.

Referential integrity

• A foreign key in each row of the child table links to the matching candidate key in the parent table.

Data Definition

• SQL allows database objects such as schemas, domains, tables, views, and indexes to be created and destroyed.

Example: CREATE SCHEMA SqlTests AUTHORIZEATION Smith;

DROP SCHEMA SqlTests;

What is View?

• View is a virtual table that does not necessarily exist in the database but can produced upon request by a user.

What are the advantages of view?

• Data independence
• Currency
• Improve security
• Reduced complexity
• Convenience
• Customization
• Data integrity

What are the disadvantages of view?

• Update restriction
• Structure restriction
• Performance

Access Control

• SQL provides the GRANT and REVOKE statements to ensure the security on the database.
• Example:

GRANT SELECT, UPDATE (salary) ON Staff TO Personnel, Director;

Access Control (cont.)

• Example:

REVOKE ALL PRIVILEGES ON Staff FROM Director;