1 / 17

Understanding SQL Data Definition, Integrity, Views, Transactions, and Access Control

This chapter provides a comprehensive overview of SQL data definition including the supported data types such as BOOLEAN, VARCHAR, and INTEGER. It explains the integrity enhancement features, necessary for ensuring data validity including domain, entity, and referential integrity. Additionally, the chapter covers the creation and management of views, discussing their advantages and disadvantages. It details transactions with COMMIT and ROLLBACK operations ensuring atomicity, and highlights access control through GRANT and REVOKE statements for database security.

maylin
Télécharger la présentation

Understanding SQL Data Definition, Integrity, Views, Transactions, and Access Control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6SQL: Data Definition By Lan Dang CS 157B Fall 2003 Instructor: Dr. S. M. Lee

  2. 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.

  3. SQL data types • Boolean (BOOLEAN) • Character (CHAR or VARCHAR) • Bit (BIT or BIT VARYING) • Exact numeric (NUMERIC, DECIMAL, INTEGER, SMALLINT) • Approximate numeric (FOAT, REAL, DOUBLE PRECISION) • Date/time (DATE, TIME,TIME STAMP) • Interval (INTERVAL) • Character/binary large object (CHARACTER LARGE OBJECT, BINARY LARGE OBJECT)

  4. Integrity Enhancement Feature • Required data • Domain constraints • Entity integrity • Referential integrity • Enterprise constraints

  5. Required data • Some columns contain a valid value must specified NOT NULL. • Example: position VARCHAR(10) NOT NULL

  6. Domain constraints • Every column has a domain. • Example: A domain of Weights should be NUMERIC - pounds or kilograms. A domain of Age should be INTEGER. A domain of Name should be VARCHAR.

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

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

  9. Enterprise constraint • If more than one tables are involved, it prefers to use an ASSERTION to prevent duplicate the check in each table. • Example: CREATE ASSERTION StaffNoHandling CHECK(NOT EXISTS (SELECT StaffNo FROM PropertyForRent GROUP BY StaffNo HAVING COUNT(*) > 100)); To prevent a member of staff from managing more than 100 properties at the same time.

  10. Data Definition • SQL allows database objects such as schemas, domains, tables, views, and indexes to be created and destroyed. Example: CREATESCHEMA SqlTests AUTHORIZEATION Smith; DROP SCHEMA SqlTests;

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

  12. How to create or delete view? • Creating or deleting a view is similar to creating or deleting database table. • Example: CREATE VIEW Manager3Staff AS SELECT * FROM Staff WHERE branchNo = ‘B003’; INSERT INTO StaffPropList VALUES(‘B003’,’SG5’,’PG19’); DROP VIEW Manager3Staff ViewName TableName

  13. What are the advantages of view? • Data independence • Currency • Improve security • Reduced complexity • Convenience • Customization • Data integrity

  14. What are the disadvantages of view? • Update restriction • Structure restriction • Performance

  15. Transactions • A transaction is a logical unit that is guarantee to be atomic with respect to recovery. • COMMIT- statement ends the transaction successfully, making the database changes permanent. • ROLLBACK- statement aborts the transaction, backing out any changes made by the transaction.

  16. 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; Give users Personnel and Director the privileges SELECT and UPDATE on column salary.

  17. Access Control (cont.) • Example: REVOKE ALL PRIVILEGES ON Staff FROM Director; Revoke all privileges from Director on the Staff table.

More Related