Lesson 4 - SQL - Data Definition

**Referential Integrity:**

- A foreign key links each row in the child table to the row of the parent table. - Referential integrity means that a foreign key value must refer to an existing, valid row in the parent table. - **Foreign Key Definition:** - Example: `FOREIGN KEY(branchNo) REFERENCES Branch` - **Actions for UPDATE or DELETE:** - `CASCADE`: Delete the row from the parent table and automatically delete matching rows in the child table. - `SET NULL`: Delete the row from the parent table and set the foreign key value(s) in the child table to NULL. - `SET DEFAULT`: Delete the row from the parent table and set each component of the foreign key in the child table to the specified default value. - `NO ACTION`: Reject the delete operation from the parent table (default if `ON DELETE` rule is omitted). - **Example of DELETE Rule:** ```sql -- Delete rule: Set foreign key to NULL FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL ``` - **Example of UPDATE Rule:** ```sql -- Update rule: Cascade the update FOREIGN KEY (ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE ```

**Entity Integrity:**

- The primary key of a table must contain a unique, non-null value for each row. - Supports entity integrity with the `PRIMARY KEY` clause. - **Primary Key Definition:** - Example: `PRIMARY KEY(propertyNo)` - For composite primary keys, list multiple column names separated by commas. - Example: `PRIMARY KEY(clientNo, propertyNo)` - **Unique Keys:** - Use the `UNIQUE` keyword for alternate keys. - Every column in a `UNIQUE` clause must be declared as `NOT NULL`. - Multiple `UNIQUE` clauses are allowed per table. - **Example:** ```sql -- PropertyForRent table primary key PRIMARY KEY(propertyNo) -- Viewing table composite primary key PRIMARY KEY(clientNo, propertyNo) -- Alternate key example clientNo VARCHAR(5) NOT NULL, propertyNo VARCHAR(5) NOT NULL, UNIQUE (clientNo, propertyNo) ```

This chapter summary provides a concise overview of the key points covered in the chapter. Here's a breakdown of the summary:

1. **Base Data Types:** - The ISO standard defines eight base data types, including boolean, character, bit, exact numeric, approximate numeric, datetime, interval, and character/binary large objects. 2. **Data Definition Language (DDL) Statements:** - DDL statements like CREATE and DROP are used to define and manipulate database objects. - CREATE and DROP SCHEMA for schemas, CREATE, ALTER, and DROP TABLE for tables, and CREATE and DROP INDEX for indexes. 3. **Integrity Constraints:** - Various clauses in CREATE and ALTER TABLE statements handle integrity constraints, such as NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY, and more. - Domains can be defined using CREATE DOMAIN. - General constraints can be defined using CHECK and UNIQUE clauses or CREATE ASSERTION. 4. **Views:** - Views are virtual tables created using CREATE VIEW, representing subsets of data from base tables or views. - Views can simplify the database structure, make queries easier, and provide security by restricting access. - View resolution and view materialization are two approaches for handling views. 5. **Transactions:** - COMMIT and ROLLBACK statements are used for transaction management. - COMMIT signals successful completion, making changes permanent, while ROLLBACK aborts the transaction, undoing changes. 6. **Access Control:** - SQL access control involves authorization identifiers, ownership, and privileges. - Authorization identifiers are assigned to users, and each object has an owner. - GRANT and REVOKE statements are used to manage privileges, including USAGE, SELECT, DELETE, INSERT, UPDATE, and REFERENCES. - Privileges can be restricted to specific columns, and users can pass on privileges with the WITH GRANT OPTION. This summary provides a comprehensive overview of the chapter, covering data

Large objects

A large object is a data type that holds a large amount of data, such as a long text file or a graphics file. Three different types of large object data types are defined in SQL: • Binary Large Object (BLOB), a binary string that does not have a character set or collation association; • Character Large Object (CLOB) and National Character Large Object (NCLOB), both character strings.

Boolean data

Boolean data: TRUE, FALSE. If not NULL-constrained, supports UNKNOWN truth value (equivalent to NULL). All boolean values and SQL truth values are comparable and assignable.

Base Data types in SQL

Boolean, character, bit (removed from SQL:2003), exact numeric, approximate numeric, datetime, interval, large object.

Required Data:

Certain columns must have valid values and are not allowed to contain nulls. Nulls represent data that is not available, missing, or not applicable. Implementation: The ISO standard includes the NOT NULL column specifier in CREATE and ALTER TABLE statements. Example: To ensure the column position in the Staff table cannot be null: Define the column as position VARCHAR(10) NOT NULL.

**Domain Constraints:**

Certainly, here's a version with simpler characters: - **Domain Constraints:** - Every column has a domain, representing a set of legal values. - **Implementation Mechanisms:** 1. **CHECK Clause:** - Format: `CHECK (searchCondition)`. - Constraints on a column or the entire table. - Example: `sex CHAR NOT NULL CHECK (sex IN ('M', 'F'))`. 2. **CREATE DOMAIN Statement:** - Format: `CREATE DOMAIN DomainName [AS] dataType [DEFAULT defaultOption] [CHECK (searchCondition)]`. - Defines a domain explicitly. - Example: ``` CREATE DOMAIN SexType AS CHAR DEFAULT 'M' CHECK (VALUE IN ('M', 'F')); ``` - **Usage in Column Definition:** - Example: `sex SexType NOT NULL`. - **Removing Domains:** - Use the `DROP DOMAIN` statement. - Syntax: `DROP DOMAIN DomainName [RESTRICT | CASCADE]`. - `RESTRICT`: Fails if the domain is in use. - `CASCADE`: Automatically adjusts columns and constraints based on the domain.

Creating a Database

Creating and managing databases involve the use of specific SQL statements. Below is a summary of the information related to creating and destroying databases: **Creating a Database Schema:** - To create a schema, use the `CREATE SCHEMA` statement. - Syntax: ```sql CREATE SCHEMA [Name | AUTHORIZATION CreatorIdentifier]; ``` - Example: ```sql CREATE SCHEMA SqlTests AUTHORIZATION Smith; ``` **Destroying a Database Schema:** - To drop a schema, use the `DROP SCHEMA` statement. - Syntax: ```sql DROP SCHEMA Name [RESTRICT | CASCADE]; ``` - Example: ```sql DROP SCHEMA SqlTests CASCADE; ``` - If `RESTRICT` is specified, the schema must be empty for the operation to succeed. - If `CASCADE` is specified, the operation will cascade to drop all objects associated with the schema.

Integrity Enhancement Feature:

Focuses on facilities in the SQL standard for integrity control. Aims to impose constraints to prevent database inconsistency. Five Types of Integrity Constraint: Required data. Domain constraints. Entity integrity. Referential integrity. General constraints. Implementation: Constraints can be defined in CREATE and ALTER TABLE statements.

Advantages and Disadvantages of Views

Here's a summary of the advantages and disadvantages of views in SQL: **Advantages:** 1. **Data Independence:** Views provide a level of abstraction between the user and the underlying database tables. Users can interact with views without needing to know the details of the table structures. This enhances data independence. 2. **Improved Security:** Views allow for the restriction of user access to specific columns or rows. This enhances security by controlling what data users can see and manipulate. 3. **Reduced Complexity:** Views can simplify complex queries and provide a simplified and more understandable representation of data, especially when dealing with multiple tables. 4. **Convenience:** Users can create customized views that present the data in a way that is most convenient for their needs. This can include aggregations, filtering, and sorting. 5. **Customization:** Views can be tailored to meet the specific needs of different user groups, providing a customized view of the data. 6. **Data Integrity:** Views can help enforce data integrity by allowing the definition of constraints and rules when creating views. **Disadvantages:** 1. **Update Restriction:** Some views may not be updatable, especially those involving multiple tables or complex queries. This can limit the ability to modify data through the view. 2. **Structure Restriction:** Changes to the underlying structure of tables (e.g., adding or removing columns) may impact views. This can introduce some level of rigidity, especially when dealing with complex views. 3. **Performance:** In some cases, using views might introduce some performance overhead, especially if the view involves complex queries or aggregations. It's important to note that the advantages and disadvantages of views can vary depending on the specific use case and how views are imple

General Constraints

In SQL, general constraints, governed by enterprise rules, can be applied to ensure real-world transaction integrity during table updates. These constraints are specified using the CHECK and UNIQUE clauses in the CREATE and ALTER TABLE statements, as well as the CREATE ASSERTION statement. Here's an overview: CHECK Constraint: Ensures that a specified condition holds true for the data being updated. Example:sqlCopy codeCHECK (salary > 0) UNIQUE Constraint: Ensures distinct values in a column or set of columns. Example:sqlCopy codeUNIQUE (employeeID) CREATE ASSERTION Statement: Defines a general constraint not directly linked to a table definition. Example:sqlCopy codeCREATE ASSERTION StaffNotHandlingTooMuch CHECK (NOT EXISTS (SELECT staffNo FROM PropertyForRent GROUP BY staffNo HAVING COUNT(*) > 100)) These constraints help maintain data consistency based on enterprise rules, allowing checks and conditions to span multiple tables when necessary.

Exact Numeric Data

Represents numbers precisely. Includes digits, an optional decimal point, and an optional sign. Components: Precision: Total significant decimal digits. Scale: Number of decimal places. Ways to Specify: NUMERIC [ precision [, scale] ] DECIMAL [ precision [, scale] ] INTEGER (INT), SMALLINT, BIGINT Defaults and Special Cases: NUMERIC and DECIMAL default scale is 0. INTEGER for large whole numbers. SMALLINT for small whole numbers. BIGINT for very large whole numbers. Examples: rooms SMALLINT (number of rooms in a property). salary DECIMAL(7,2) (handles values up to 99,999.99).

Character Data:

Sequence of characters from an implementation-defined character set.Set is defined by the vendor of the specific SQL dialect. Examples include ASCII and EBCDIC. Format for Character Data Type: CHARACTER [VARYING] [length]Abbreviated as CHAR or VARCHAR. Length Specification: Maximum number of characters the column can hold (default length is 1). Fixed vs. Varying Length: Fixed length pads with blanks if fewer characters are entered.Varying length stores only entered characters, using less space. Examples: branchNo CHAR(4) address VARCHAR(30)

Changing a Table Definition (ALTER TABLE)

The `ALTER TABLE` statement in SQL allows for modifications to the structure of an existing table. Below is a breakdown of the syntax and options available in the `ALTER TABLE` statement: ```sql ALTER TABLE TableName [ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)]] [DROP [COLUMN] columnName [RESTRICT | CASCADE]] [ADD [CONSTRAINT [ConstraintName]] tableConstraintDefinition] [DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]] [ALTER [COLUMN] SET DEFAULT defaultOption] [ALTER [COLUMN] DROP DEFAULT]; ``` - **Add a New Column:** - `ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)]`: Adds a new column to the table. - **Drop a Column:** - `DROP [COLUMN] columnName [RESTRICT | CASCADE]`: Drops a column from the table. - `RESTRICT`: Rejects the DROP operation if the column is referenced by other database objects (default). - `CASCADE`: Proceeds with the DROP operation and automatically drops the column from any referencing objects. - **Add a New Table Constraint:** - `ADD [CONSTRAINT [ConstraintName]] tableConstraintDefinition`: Adds a new table-level constraint (PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK). - **Drop a Table Constraint:** - `DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]`: Drops a table-level constraint. - `RESTRICT`: Rejects the DROP operation if the constraint is referenced by other database objects (default). - `CASCADE`: Proceeds with the DROP operation and automatically drops the constraint from any referencing objects. - **Set Default for a Column:** - `ALTER [COLUMN] SET DEFAULT defaultOption`: Sets a default value for a column. - **Drop Default for a Column:** - `ALTER [COLUMN] DROP DEFAULT`: Removes the default value for a column. These options provi

Creating a Table (CREATE TABLE)

The `CREATE TABLE` statement in SQL is used to define the structure of tables within a database. Here's a breakdown of its syntax and key components: ```sql CREATE TABLE TableName ( columnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)], ..., PRIMARY KEY (listOfColumns), UNIQUE (listOfColumns), FOREIGN KEY (listOfForeignKeyColumns) REFERENCES ParentTableName [(listOfCandidateKeyColumns)] [MATCH {PARTIAL | FULL} [ON UPDATE referentialAction] [ON DELETE referentialAction]], CHECK (searchCondition), CONSTRAINT ConstraintName ); ``` - **Table Definition:** - `CREATE TABLE TableName`: Creates a table with the specified name. - **Column Definitions:** - `(columnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)], ...)`: Defines the columns of the table. - `columnName`: Name of the column. - `dataType`: Data type of the column. - `NOT NULL`: Specifies that the column cannot contain NULL values. - `UNIQUE`: Ensures that each value in the column is unique. - `DEFAULT defaultOption`: Provides a default value for the column. - `CHECK (searchCondition)`: Defines a check constraint on the column. - **Table Constraints:** - `PRIMARY KEY (listOfColumns)`: Specifies the primary key for the table. - `UNIQUE (listOfColumns)`: Ensures uniqueness of values across specified columns. - `FOREIGN KEY (listOfForeignKeyColumns) REFERENCES ParentTableName [(listOfCandidateKeyColumns)] [MATCH {PARTIAL | FULL} [ON UPDATE referentialAction] [ON DELETE referentialAction]]`: Defines a foreign key relationship to another table. - `MATCH {PARTIAL | FULL}`: Specifies how NULLs are treated in foreign key comparisons. - `ON UPDATE referentialAction`: Specifies the action to be taken when the referenced key is updated. - `ON DE

create and remove a view

The `CREATE VIEW` statement is used to define a view in a relational database. The syntax is as follows: ```sql CREATE VIEW ViewName [(newColumnName [, . . . ])] AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION] ``` - `ViewName` is the name of the view. - `newColumnName` is an optional list of names assigned to each column in the view. - `subselect` is the defining query, specified using the SQL `SELECT` statement. If the `WITH CHECK OPTION` is specified, it ensures that any row failing to satisfy the `WHERE` clause of the defining query is not added to the underlying base table of the view. To remove a view, the `DROP VIEW` statement is used: ```sql DROP VIEW ViewName [RESTRICT | CASCADE] ``` - `RESTRICT`: The `DROP` operation is rejected if other objects depend on the view. - `CASCADE`: The `DROP` operation proceeds and deletes all dependent objects. The default setting is `RESTRICT`. Example: ```sql CREATE VIEW Manager3Staff AS SELECT * FROM Staff WHERE position = 'Manager3'; DROP VIEW Manager3Staff; -- To remove the view ``` Note: Appropriate privileges are required to create or drop views, as discussed in Section 7.6.

Transactions

The provided text discusses transactions in the context of SQL databases, particularly focusing on the ISO standard's transaction model based on COMMIT and ROLLBACK statements. Here's a breakdown of the key points: 1. **Transaction Basics:** - A transaction is a logical unit of work consisting of one or more SQL statements. - It is atomic, meaning it is guaranteed to be either fully completed (COMMIT) or fully aborted (ROLLBACK) with respect to recovery. 2. **Transaction Completion:** - A transaction can complete in four ways: COMMIT, ROLLBACK, successful program termination, or abnormal program termination. - COMMIT makes changes permanent, while ROLLBACK undoes changes. - Transactions initiated by SELECT, INSERT, UPDATE, or similar statements. 3. **Transaction Nesting:** - SQL transactions cannot be nested. 4. **SET TRANSACTION Statement:** - Configures certain aspects of the transaction. - Specifies READ ONLY or READ WRITE, indicating if the transaction involves write operations. - Specifies ISOLATION LEVEL, determining the degree of interaction allowed from other transactions. 5. **Isolation Levels and Serializability:** - Describes different isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. - Discusses phenomena (dirty read, nonrepeatable read, phantom read) and their violations by each isolation level. - SERIALIZABLE is the safest level for generating serializable schedules. 6. **Immediate and Deferred Integrity Constraints:** - Describes situations where integrity constraints may be checked immediately or deferred until transaction commit. - INITIALLY IMMEDIATE or INITIALLY DEFERRED modes can be specified, and the mode can be changed with [NOT] DEFERRABLE. - The SET CONSTRAINTS statement is used to set the mode for specified constraint

Discretionary Access Control

This passage discusses discretionary access control in database management systems (DBMSs) and focuses on two authorization mechanisms: discretionary access control and mandatory access control. 1. **Discretionary Access Control:** - **Definition:** Users are given access rights (privileges) on specific database objects. Users can obtain privileges when creating an object and may pass some or all of these privileges to other users. - **Concerns:** This approach is flexible but may be vulnerable to unauthorized users tricking authorized users into revealing sensitive data. 2. **Mandatory Access Control:** - **Definition:** Each database object is assigned a classification level (e.g., Top Secret, Secret, Confidential, Unclassified), and each user or program is given a designated clearance level. Subjects require the necessary clearance to read or write a database object. - **Importance:** Essential for government, military, and corporate applications. The Bell-LaPadula model is a commonly used mandatory access control model. 3. **SQL and Access Control:** - SQL supports only discretionary access control through the GRANT and REVOKE statements. - **Authorization Identifiers and Ownership:** - An authorization identifier is used to establish a user's identity, assigned by the DBA. - Each database object has an owner identified by the authorization identifier in the AUTHORIZATION clause of the schema. - The owner has initial control over the object and can perform operations on it. - **Privileges:** - Privileges are actions a user is permitted on a base table or view. - Standard privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, and USAGE. - INSERT and UPDATE privileges can be restricted to specific columns. - REFERENCES privilege can be restricted to sp

Bit Data:

Used for defining bit strings, sequences of binary digits (bits) with values 0 or 1. Format for Bit Data Type: BIT [VARYING] [length] Example: To hold the fixed length binary string "0011": Declare a column bitString BIT(4)

Datetime Data:

Used for defining points in time with a certain degree of accuracy. Includes examples like dates, times, and times of day. ISO Standard Subdivisions: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. Three Types of Datetime: DATE: Stores calendar dates (YEAR, MONTH, DAY). TIME [timePrecision] [WITH TIME ZONE]: Stores time (HOUR, MINUTE, SECOND). TIMESTAMP [timePrecision] [WITH TIME ZONE]: Stores date and times. Precision: timePrecision determines the decimal places of accuracy for the SECOND field. Default precision is 0 for TIME and 6 for TIMESTAMP. Time Zone: WITH TIME ZONE includes TIMEZONE_HOUR and TIMEZONE_MINUTE fields. Examples: viewDate DATE for storing the date a client viewed a property.

Approximate Numeric Data:

Used for numbers without an exact representation, like real numbers. Utilizes floating-point notation, akin to scientific notation (mantissa times power of ten). Ways to Specify: FLOAT [precision] REAL DOUBLE PRECISION Precision: Controls the precision of the mantissa. Implementation Details: Precision of REAL and DOUBLE PRECISION is implementation-defined.

Interval Data:

Used to represent periods of time. Consists of a subset of fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Two Classes: Year-month intervals. Day-time intervals. Format: INTERVAL {{startField TO endField} singleDatetimeField}

**Creating an Index:**

```sql CREATE [UNIQUE] INDEX IndexName ON TableName (columnName [ASC | DESC] [, . . .]); ``` - Use `CREATE INDEX` to create an index on specified columns in a table. - `UNIQUE` enforces uniqueness for the indexed column(s). - `ASC` or `DESC` specifies the sort order; `ASC` is the default. - **Removing an Index:** ```sql DROP INDEX IndexName; ``` - Use `DROP INDEX` to remove a specific index from the database. - Exercise caution, as it can impact query performance.

Views

in a relational database are dynamic results generated by relational operations on base relations. A view is a virtual relation that doesn't necessarily store data but is produced on request. It appears like a table to users but is defined by a query on one or more base tables or views. The view's definition is stored in the database. When a user references a view, the DBMS translates the request into an equivalent query against the source tables and performs the operation. This process, known as view resolution, is explained in Section 7.4.3. Alternatively, view materialization involves storing the view as a temporary table, updating it as the base tables change (discussed in Section 7.4.8). Now, let's explore how to create and utilize views.

Data Definition Language (DDL)

is responsible for creating and managing database objects. The main DDL statements are: CREATE - to create a database schema or table; ALTER - to change the structure of a table once it has been created; DROP - to delete a table