Chapter#6
Database Management System
Notes
Schema and Catalog Concepts:
SQL introduced the concept of a schema to group together tables and other constructs
belonging to the same database application.
A schema includes tables, types, constraints, views, and other elements.
Schemas are created using the CREATE SCHEMA statement.
Not all users can create schemas; privileges must be granted by the system administrator
or DBA.
A catalog is a named collection of schemas and typically includes a default schema like
INFORMATION_SCHEMA to provide information on all schemas and elements.
CREATE TABLE Command:
Used to create a new relation (table) by specifying its name, attributes, and initial
constraints.
Attributes include name, data type (numeric, character string, etc.), and constraints (e.g.,
NOT NULL).
Entity integrity, key, and referential integrity constraints can be specified within the
CREATE TABLE statement or added later using ALTER TABLE.
Base tables are physically created and stored by the DBMS, while virtual relations
(created by CREATE VIEW) may or may not correspond to physical files.
Attribute Data Types and Domains:
Basic data types include numeric, character string, bit string, Boolean, date, and time.
Numeric types include integers, floating-point numbers, and formatted numbers.
Character-string types can be fixed-length or varying-length, and case-sensitive.
Bit-string types can be fixed or varying length, preceded by a 'B' to distinguish them.
Boolean types have values TRUE, FALSE, and UNKNOWN due to SQL's three-valued
logic.
Date and time types have specific formats for representation.
Domains can be created to specify common data types, improving schema readability and
allowing for easier changes.
1|Page
SQL also supports user-defined types (UDTs) using the CREATE TYPE command.
Attribute Constraints and Defaults:
Constraints like NOT NULL ensure that attribute values cannot be NULL.
Default values can be specified for attributes using the DEFAULT clause.
The CHECK clause restricts attribute or domain values based on specified conditions.
Key and Referential Integrity Constraints:
Primary keys are specified using the PRIMARY KEY clause, while alternate keys are specified
using the UNIQUE clause.
Referential integrity is maintained using the FOREIGN KEY clause, which specifies
relationships between tables.
Actions for handling integrity violations include RESTRICT, SET NULL, CASCADE, and SET
DEFAULT, specified with ON DELETE or ON UPDATE.
Naming Constraints:
Constraints can be given names using the CONSTRAINT keyword, which helps in identification
and management.
Constraints on Tuples using CHECK:
Additional constraints on individual tuples can be specified using the CHECK clause.
More general constraints can be specified using the CREATE ASSERTION statement.
Attribute Constraints and Defaults:
Constraints like NOT NULL ensure that attribute values cannot be NULL.
Default values can be specified for attributes using the DEFAULT clause.
The CHECK clause restricts attribute or domain values based on specified conditions.
Key and Referential Integrity Constraints:
Primary keys are specified using the PRIMARY KEY clause, while alternate keys are
specified using the UNIQUE clause.
Referential integrity is maintained using the FOREIGN KEY clause, which specifies
relationships between tables.
Actions for handling integrity violations include RESTRICT, SET NULL, CASCADE,
and SET DEFAULT, specified with ON DELETE or ON UPDATE.
Naming Constraints:
Constraints can be given names using the CONSTRAINT keyword, which helps in
identification and management.
Constraints on Tuples using CHECK:
Additional constraints on individual tuples can be specified using the CHECK clause.
More general constraints can be specified using the CREATE ASSERTION statement.
2|Page
Substring Pattern Matching: SQL allows for pattern matching within string attributes
using the LIKE operator, where % represents an arbitrary number of characters and _
represents a single character.
Arithmetic Operators: Standard arithmetic operators such as addition, subtraction,
multiplication, and division can be used in SQL queries, primarily for numeric values or
attributes with numeric domains.
Concatenation Operator: The || operator can be used to concatenate string values in SQL
queries.
Comparison Operator: The BETWEEN operator can be used to specify a range of values
for comparison, as demonstrated in Query 14.
Ordering Query Results: SQL allows for ordering the result of a query using the ORDER
BY clause. Results can be ordered in ascending (default) or descending order based on
one or more attributes.
INSERT Statement: The INSERT statement is used to add new tuples (rows) to a relation
(table) in the database. It can either insert a single tuple or multiple tuples, and values
must be provided in the same order as specified in the table schema.
DELETE Statement: The DELETE statement removes tuples from a relation based on
specified conditions in the WHERE clause. If no WHERE clause is provided, all tuples
from the relation are deleted.
UPDATE Statement: The UPDATE statement modifies attribute values of selected tuples
in a relation. It uses the SET clause to specify the attributes to be modified and their new
values, and the WHERE clause to select the tuples to be updated.
Complex Retrieval Queries: SQL supports various techniques for specifying complex
retrieval queries, including nested queries, aggregate functions, grouping, joined tables,
outer joins, case statements, and recursive queries.
SQL Programming Techniques: SQL provides methods for writing programs in
different programming languages with embedded SQL statements to access databases.
Techniques include embedded SQL, SQL/CLI, SQL/PSM, JDBC, and SQLJ.
Transaction Control Commands: SQL includes commands for specifying units of
database processing for concurrency control and recovery purposes, discussed in Chapter
20.
Privilege Management: SQL offers language constructs for granting and revoking
privileges to users, controlling access to SQL commands and database objects, covered in
Chapter 20 for security and authorization.
Triggers: SQL allows for creating triggers, actions automatically triggered by events like
database updates, discussed in Section 26.1 as part of active database concepts.
Object-Relational Capabilities: SQL incorporates features from object-oriented models,
enhancing relational systems with capabilities like complex-structured attributes, abstract
data types (UDTs), object identifiers, and type operations, detailed in Chapter 12.
3|Page
Interactions with New Technologies: SQL and relational databases can interact with
technologies such as XML and OLAP/data warehouses, covered in Chapters 13 and 29,
respectively.
4|Page
