![Page 1: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/1.jpg)
1
Ms. Hatoon Al-SagriCCIS – IS Department
SQL-99 :Schema Definition, Constraints, Queries, and Views
![Page 2: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/2.jpg)
2
Database Design
Steps in building a database for an application:
Real-world domain
Conceptualmodel
DBMS data model
Create Schema
(DDL)
Load data(DML)
![Page 3: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/3.jpg)
3
DDL Statements
The main SQL data definition language statements are used to CREATE, DROP, and ALTER
the descriptions of the tables (relations) of a database.
CREATE SCHEMA DROP SCHEMA
CREATE DOMAIN ALTER DOMAIN DROP DOMAIN
CREATE TABLE ALTER TABLE DROP TABLE
CREATE VIEW DROP VIEW
CREATE INDEX DROP INDEX
![Page 4: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/4.jpg)
4
Notations
Notations to define SQL statements:
• UPPER-CASE letters represents reserved words
• Lower-case letters represents user-defined words
• | indicates a choice among alternatives; (e.g. a | b | c)
• { } indicates a required element
• [ ] indicates an optional element
• … indicates optional repetition of an item zero or more times
• Underlined words represent default values
![Page 5: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/5.jpg)
5
Identifiers
• May contain A-Z, a-z, 0-9, _
• No longer than 128 characters
• Start with letter
• Cannot contain spaces
![Page 6: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/6.jpg)
6
Attribute data types and domains in SQL
Type SQL Data Type Used
NumericINTEGER or INT and SMALLINT
Integer numbers of various sizes. NoRoom SMALLINT
FLOAT or REAL and DOUBLE PRECISION
Floating-point (real) numbers of various precision
NUMERIC (i, j) or DECIMAL (i, j)
Formatted numbers, where i The precision and j the scale.salary DECIMAL(7,2)
Character-string CHAR(n) or CHARACTER(n)
Fixed length, where n is the number of charactersBno CHAR(4)
VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n)
Varying length, where n is the maximum number of charactersName VERCHAR(15)
![Page 7: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/7.jpg)
7
Attribute data types and domains in SQL
Type SQL Data Type Used
Bit-string
BIT(n)
Fixed length, where n is the number of bits(0 or 1)BitSt BIT(4)
BIT VARYING (n) Varying length, where n is the maximum number of bits.BitSt BIT VARYING(4)
Boolean
BOOLEAN
There are three values (TRUE,FALSE and UNKNOWN)
![Page 8: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/8.jpg)
8
Attribute data types and domains in SQL
Type SQL Data Type UsedDate_Time
DATE Has ten positions. Made up of year-month-day in the format yyyy-mm-ddViewDate DATE
TIME Made up of hour:minute:second in the format hh:mm:ssViewTime TIME
TIME(i)Made up of hour:minute:second plus i additional digits specifying fractions of a second. format is hh:mm:ss:i
Timestamp TIMESTAMP Has both DATE and TIME components
INTERVAL Specifies a relative value rather than an absolute value.
![Page 9: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/9.jpg)
9
Schema concepts in SQL
Schema is a named collection of related database objects (tables,
views, domains, ..) who belong to the same database application.
Specifies a new database schema by giving it a name.
An SQL schema is identified by a schema name and includes an
authorization identifier to indicate the user or account who owns
the schema.
![Page 10: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/10.jpg)
10
Creating a DB
SyntaxCREATE SCHEMA [Name | AUTHORIZATION
Identifier]
ExampleCREATE SCHEMA COMPANY AUTHORIZATION Jsmith;
![Page 11: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/11.jpg)
11
Domains in SQL
It is possible to specify the data type of each attribute directly or a domain can be declared and the domain name used with the attribute specification.
Syntax: CREATE DOMAIN domainName [AS] datatype [DEFAULT default option] [CHECK (search condition)];
Example:
CREATE DOMAIN SSN_TYPE AS CHAR(9);
![Page 12: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/12.jpg)
12
Domains in SQL
Example:CREATE DOMAIN DepartmentNo AS INT;..Dno DepartmentNo,
![Page 13: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/13.jpg)
13
Creating a Table
SyntaxCREATE TABLE tablename { ( {columnName dataType [NOT NULL | NULL] [UNIQUE] [DEFAULT defaultOption,]
[CHECK (search condition),] (,…) } [PRIMARY KEY (column (,…) ),] [UNIQUE (column (,…) ),] [FOREIGN KEY (FK column (,…) )
REFERENCES tablename [(CK column (,…))][ON UPDATE ReferentialAction][ON DELETE ReferentialAction], ]
[CHECK (search condition)] ) }
![Page 14: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/14.jpg)
14
Creating a Table
Specifies a new relation by giving it a name, and specifying each of its attributes and their data types.
A constraint NOT NULL may be specified on an attribute. Example:
CREATE TABLE DEPARTMENT (Dname VARCHAR(10) NOT NULL,Dnumber INTEGER NOT NULL,Mgr_ssn CHAR(9),Mgr_start_date CHAR(9) );
![Page 15: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/15.jpg)
15
DROP Schema
The DROP command can be used to drop the whole schema or drop table and its definition.
Two drop behavior options:– CASCADE, drops all objects associated with schema– RESTRICT, schema must be empty .
SyntaxDROP SCHEMA Name [RESTRICT | CASCADE]
Example:
DROP SCHEMA COMPANY CASCADE;
![Page 16: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/16.jpg)
16
DROP Table
The relation can no longer be used in queries, updates, or any other commands since its description no longer exists.
Syntax:DROP TABLE tablename [RESTRICT | CASCADE];
Example: DROP TABLE DEPENDENT CASCADE;
Two drop behavior options:– CASCADE, drop operation drops all column from objects it is referenced by– RESTRICT, drop operation is rejected if the column is referenced by another
database object.
![Page 17: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/17.jpg)
17
DROP Domain
Syntax: DROP DOMAIN DomainName [RESTRICT | CASCADE];
RESTRICT, domain must not be used in any existing table, view or assertion.
CASCADE, any column based on the domain is automatically changed to use the underlying data type, column constraint and default clause.
![Page 18: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/18.jpg)
18
Specifying attribute constraints
Three types of attribute constraints:1. Required data2. Defaults value3. Domain values constraints
![Page 19: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/19.jpg)
19
1. Required data
Null is distinct from blank or zero.When NOT NULL is specified, the system rejects any attempt
to insert a null in the column.
Syntax:columnName dataType [NOT NULL | NULL]
Example:
Dname VARCHAR(10) NOT NULL,
![Page 20: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/20.jpg)
20
2. Defaults value
– It is possible to define a default value for an attribute by appending the DEFAULT value to an attribute definition.
– Example:Dno INT NOT NULL DEFALT 1,
![Page 21: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/21.jpg)
21
3. Domain values constraints: CHECK Restrict attribute values using CHECK.
Syntax: CHECK (search condition)Example:
Attribute (Column-Based CHECK)
Dnumber INT NOT NULL CHECK(Dnumber>0 AND Dnumber<12),
Domain definition (Column-Based CHECK)
CREATE DOMAIN D_num AS INTEGER CHECK(D_num>0 AND D_num<12);
![Page 22: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/22.jpg)
22
Domain values constraints: CHECK Examples
sex CHAR NOT NULL CHECK (sex In (‘M’, ‘F’))
salary DECIMAL NOT NULL CHECK (salary > 10000);
CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’, ‘F’));
![Page 23: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/23.jpg)
23
Domain values constraints: CHECK
– Other table constraints can be specified through additional CHECK clauses at the end of a CREATE TABLE statement.
– Called tuple-based constraints because they apply for each tuple individually and are checked whenever a tuple is inserted or modified.
–Example:
CHECK (Dept_create_date <= Mgr_start_date)
![Page 24: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/24.jpg)
24
Specifying key and referential integrity constraints
PRIMARY KEY UNIQUE FOREIGN KEY
![Page 25: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/25.jpg)
25
Specifying key and referential integrity constraints
PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation.
Syntax:
PRIMARY KEY (attribute (,…))
SQL rejects any operations that attempt to create duplication in the PK column.
PK forbids NULL value.
![Page 26: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/26.jpg)
26
Specifying key and referential integrity constraints
If the primary key has a single attribute, the clause can follow the attribute directly.
Example:Dnumber INTEGER NOT NULL,...PRIMARY KEY (Dnumber),
orDnumber INTEGER PRIMARY KEY,
Composite PK: PRIMARY KEY (cno, pno),
![Page 27: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/27.jpg)
27
Specifying key and referential integrity constraints
The UNIQUE clause specifies alternate (Secondary).
UNIQUE permits NULL value.
Every column that appears in a UNIQUE clause must also be declared in as NOT NULL.
Syntax: UNIQUE(attribute (,…))
![Page 28: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/28.jpg)
28
Specifying key and referential integrity constraints
Example:UNIQUE can appear after a column definition or separately:
Dname VARCHAR(10) NOT NULL,..UNIQUE (Dname),
Or
Dname VARCHAR(10) NOT NULL UNIQUE,
Example:
cno VARCHAR(5) NOT NULL,
pno VARCHAR(5) NOT NULL,
UNIQUE(cno, pno),
![Page 29: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/29.jpg)
29
Specifying key and referential integrity constraints
Referential integrity is specified via the FOREIGN KEY clause. FOREIGN KEY clause is defined in the CREATE & ALTER TABLE
statements. Syntax:
FOREIGN KEY (attribute (,…)) REFERENCES table_name [(attribute (,…))] Example:
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn)
SQL rejects any insert, update or delete operation that attempts to create a foreign key value without a matching PK value key.
The schema designer can specify an alternative action by attaching a referential triggered action on UPDATE or DELETE operation.
![Page 30: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/30.jpg)
30
REFERENTIAL INTEGRITY OPTIONS
Four options are supported when the user attempt to delete or update a PK, and there are matching FKs: CASCADE: automatically delete/update the PK row & all matching (FKs) rows
in child table SET NULL: delete/update the PK row & set the FK values to NULL SET DEFAULT: delete/update the PK row & set the FK values to default. Valid
only if DEFAULT clause is specified. NO ACTION: rejects the delete or update operation.
Syntax FOREIGN KEY (FK column (,…) ) REFERENCES tablename [(CK column (,…))]
[ON UPDATE [CASCADE | SET NULL| SET DEFAULT| NO ACTION] ]
[ON DELETE [CASCADE | SET NULL| SET DEFAULT| NO ACTION] ]
![Page 31: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/31.jpg)
31
REFERENTIAL INTEGRITY OPTIONS
We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys).
CREATE TABLE DEPARTMENT (Dname VARCHAR(10) NOT NULL,Dnumber INTEGER NOT NULL,Mgr_ssn CHAR(9),Mgr_start_date CHAR(9),
PRIMARY KEY (Dnumber),UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE SET NULL ON UPDATE CASCADE);
![Page 32: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/32.jpg)
32
REFERENTIAL INTEGRITY OPTIONS
CREATE TABLE EMPLOYEE(Ename VARCHAR(30) NOT NULL,Essn CHAR(9),Bdate DATE,Dno INTEGER DEFAULT 1,Superssn CHAR(9),PRIMARY KEY (Essn),FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)ON DELETE SET DEFAULT ON UPDATE CASCADE,FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE);
![Page 33: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/33.jpg)
33
“COMPANY” Relational Database Schema
![Page 34: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/34.jpg)
SQL Create Table data definition statements for defining the COMPANY Schema
![Page 35: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/35.jpg)
SQL Create Table data definition statements for defining the COMPANY Schema
![Page 36: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/36.jpg)
36
Specifying Constraints in SQL:
Giving Names to constraints:–The constraints can be given a constraint name, following the keyword CONSTRAINT.
– The name must be unique.– In order to modify or delete an existing constraint, it is necessary that the constraint have a name.
Sex CHAR CONSTRAINT SexTypeValidCHECK (sex IN (‘F’, ‘M’) )
Dept CHAR(4) NOT NULL CONSTRAINT DepNoInList CHECK( Dno IN (SELECT Dept FROM DEPARTMENT))
CONSTRAINT IDISKey PRIMARY KEY (SSN)
![Page 37: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/37.jpg)
37
![Page 38: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/38.jpg)
38
Changing a Table Definition
ALTER consists of six options to:
Add a column to table
Drop a column from a table
Add a table constraint
Drop a table constraint
Set a default for a column
Drop a default for a column
![Page 39: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/39.jpg)
39
Changing a Table Definition
Syntax:ALTER TABLE tablename [ADD [COLUMN] ColumnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (search condition)] ] [DROP [COLUMN] ColumnName [RESTRICT | CASCADE]] [ADD [CONSTRAINT [Constraint Name]] TableConstraint
Definition] [DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]] [ALTER ColumnName SET DEFAULT DefaultOption] [ALTER ColumnName DROP DEFAULT];
RESTRICT, drop operation is rejected if the column is referenced by another database object (e.g. view).
CASCADE, drop operation drops all column from objects it is referenced by.
![Page 40: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/40.jpg)
40
Changing a Table Definition
Example:Add an attribute for keeping track of jobs of staff in the company schemaALTER TABLE staff ADD job VARCHAR(12);
Example:Remove the address attribute from the staff tableALTER TABLE staff DROP address CASCASE;
![Page 41: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/41.jpg)
41
Changing a Table Definition
Example:Change the staff table by removing the default of ‘Assistant’ for the
position column and setting the default for the sex column to female.
ALTER TABLE staff ALTER position DROP DEFAULT;
ALTER TABLE staff ALTER sex SET DEFAULT ‘F’;
![Page 42: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/42.jpg)
42
Changing a Table Definition
Example:Change the PropertyForRent table by removing the constraint that
the staff are not allowed more than 100 properties at a time.
ALTER TABLE PropertyForRent
DROP CONSTRAINT StaffNotHandlingTooMuch CASCADE;
![Page 43: SQL-99 : Schema Definition, Constraints, Queries, and Views](https://reader035.vdocuments.us/reader035/viewer/2022081503/568156de550346895dc483a7/html5/thumbnails/43.jpg)
43
Material for this lecture was obtained from:– Fundamentals of Database Systems, Elmasri and
Navathe, 5th edition, Addison-Wesley, 2007.– Database Systems: A Practical Approach to
Design, Implementation and Management, Thomas Connolly, Carolyn Begg, 3rd edition, 2002.
– The slides of dr. Lilac Safadi, King Saud University.– The slides of T. Abeer Al-Nafjan, Asma AlSaleh
Imam University.