190 likes | 298 Views
Chapter 6 SQL: Data Definition. By Lan Dang CS 157B Fall 2003 Instructor: Dr. S. M. Lee. 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. SQL data types.
E N D
Chapter 6SQL: Data Definition By Lan Dang CS 157B Fall 2003 Instructor: Dr. S. M. Lee
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.
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)
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
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.
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.
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.
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;
What is View? • View is a virtual table that does not necessarily exist in the database but can produced upon request by a user.
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
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
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.
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.
Access Control (cont.) • Example: REVOKE ALL PRIVILEGES ON Staff FROM Director; Revoke all privileges from Director on the Staff table.