Database systems (ISD 242(
Basic SQL 2
Prepared by
Dr . Abdelhamid Khattab
1
Schema and Catalog Concepts in SQL
CREATE SCHEMA statement
CREATE SCHEMA COMPANY AUTHORIZATION
‘Ahmed’;
Catalog
Named collection of schemas in an SQL
environment
SQL also has the concept of a cluster of catalogs.
Slide 6- 2
The CREATE TABLE Command in SQL
Specifying a new relation
Provide name of table
Specify attributes, their types and initial
constraints
Can optionally specify schema:
CREATE TABLE [Link] ...
or
CREATE TABLE EMPLOYEE ...
Slide 6- 3
COMPANY relational database schema
Slide 6- 4
One possible database state for the
COMPANY relational database schema
Slide 6- 5
One possible database state for the COMPANY
relational database schema – continued
Slide 6- 6
SQL CREATE TABLE data definition statements for
defining the COMPANY schema from previous Figure
continued on next slide
Slide 6- 7
SQL CREATE TABLE data definition statements
for defining the COMPANY schema from
continued
Slide 6- 8
Attribute Data Types and Domains in SQL
Basic data types
Numeric data types
Integer numbers: INTEGER, INT, and SMALLINT
Floating-point (real) numbers: FLOAT or REAL, and
DOUBLE PRECISION
Character-string data types
Fixed length: CHAR(n), CHARACTER(n)
Varying length: VARCHAR(n), CHAR
VARYING(n), CHARACTER VARYING(n)
Slide 6- 17
Attribute Data Types and Domains in SQL
Bit-string data types
Fixed length: BIT(n)
Varying length: BIT VARYING(n)
Boolean data type
Values of TRUE or FALSE or NULL
DATE data type
Ten positions
Components are YEAR, MONTH, and DAY in the
form YYYY-MM-DD
Multiple mapping functions available in RDBMSs to
change date formats
Slide 6- 18
Attribute Data Types and Domains in SQL
Additional data types
Timestamp data type
Includes the DATE and TIME fields
Plus a minimum of six positions for decimal
fractions of seconds
Optional WITH TIME ZONE qualifier
INTERVAL data type
Specifies a relative value that can be used to
increment or decrement an absolute value of a date,
time, or timestamp
DATE, TIME, Timestamp, INTERVAL data types can
be cast or converted to string formats for comparison.
Slide 6- 19
CREATE TABLE events ( event_id INT PRIMARY KEY, event_name
VARCHAR(255), event_time TIMESTAMP);
INSERT INTO events (event_id, event_name, event_time)VALUES (1,
'Meeting Start', '2025-12-04 10:30:00');
SELECT event_name, event_time FROM events WHERE event_id = 1;
SELECT task_id, start_date, end_date FROM tasks WHERE (end_date -
start_date) > INTERVAL 5 DAY;
Slide 6- 12
Attribute Data Types and Domains in SQL
Domain
Name used with the attribute specification
Makes it easier to change the data type for a
domain that is used by numerous attributes
Improves schema readability
Example:
CREATE DOMAIN SSN_TYPE AS CHAR(9);
Slide 6- 20
Specifying Constraints in SQL
Basic constraints:
Relational Model has 3 basic constraint types that
are supported in SQL:
Key constraint: A primary key value cannot be
duplicated
Entity Integrity Constraint: A primary key value
cannot be null
Referential integrity constraints : The “foreign key
“ must have a value that is already present as a
primary key, or may be null.
Slide 6- 21
Specifying Attribute Constraints
Other Restrictions on attribute domains:
Default value of an attribute
DEFAULT <value>
NULL is not permitted for a particular attribute
(NOT NULL)
CHECK clause
Dnumber INT NOT NULL CHECK (Dnumber >
0 AND Dnumber < 21);
Slide 6- 22
Key and Referential Integrity Constraints
PRIMARY KEY clause
Specifies one or more attributes that make up the
primary key of a relation
Dnumber INT PRIMARY KEY;
UNIQUE clause
Specifies alternate (secondary) keys (called
CANDIDATE keys in the relational model).
Dname VARCHAR(15) UNIQUE;
Slide 6- 23
Key and Referential Integrity Constraints
FOREIGN KEY clause
Default operation: reject update on violation
Attach referential triggered action clause
Options include SET NULL, CASCADE, and SET
DEFAULT
Action taken by the DBMS for SET NULL or SET
DEFAULT is the same for both ON DELETE and ON
UPDATE
CASCADE option suitable for “relationship” relations
Slide 6- 24
Giving Names to Constraints
Using the Keyword CONSTRAINT
Name a constraint
Useful for later altering
Slide 6- 25
Default attribute values and referential integrity
triggered action specification (Fig. 6.2)
Slide 6- 26
Specifying Constraints on Tuples Using CHECK
Additional Constraints on individual tuples within a
relation are also possible using CHECK
CHECK clauses at the end of a CREATE TABLE
statement
Apply to each tuple individually
CHECK (Dept_create_date <=
Mgr_start_date);
Slide 6- 27