SQL Data Definition Language - BFH-TI Staff

Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Database Systems
Unit 5
Database Implementation:
SQL Data Definition Language
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Learning Goals
• In this unit you will learn
– how to transfer a logical data model into a physical
database,
– how to extend or modify the structure of an existing
database,
– how to ensure data integrity, and
– how to control access to the data in the database.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-2
1
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Languages
• Implementation of tables and other objects of a database
system is done by data definition language (DDL)
statements.
• Insertion, Modification and Deletion of the objects of a
database system is done by data manipulation language
(DML) statements
• Both sublanguages are included in SQL (Structered Query
Language).
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Implementation
Operation
CREATE ...
... DATABASE
... USER
... TABLE
... VIEW
... INDEX
INSERT UPDATE
SELECT DELETE
GRANT
CREATE ...
... PROCEDURE
... FUNCTION
... TRIGGER
DB / PRM1 / 11.06.2007
GRANT
REVOKE
Maintenance & Optimization
CREATE / ALTER
... TABLE
... INDEX
... PROCEDURE
... USER
/ DROP
... VIEW
... FUNCTION
... TRIGGER
... DATABASE
GRANT / REVOKE
db07_sql_ddl.ppt / Version 2.0
7-4
2
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Databases and Schemas
• Usually, a database system maintains more than one database
• Within one database, the objects (tables etc.) may be grouped
into different schemas.
• Database objects are named uniquely within the same schema
• To specify objects in another schema, you have to use a
qualified name, the schema name and the object name
separated by a period, e.g., "CarDB.Car"
(except for objects in the default schema)
• To specify objects in another database, it has to be qualified
with the database and schema name, e.g., "t4a.prm.Employee"
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Databases and Schemas
CREATE SCHEMA company AUTHORIZATION JSMITH;
• In general, not all users are authorized to create schemas
and schema elements.
• The privilege to create schemas, tables, and other constructs
must be explicitely granted to the relevant user assounts by
the system administrator or DBA.
• In addition to the concept of a schema, SQL-92 uses the
concept of a catalog –a named collection of schemas in an
SQL environment. A SQL environment is basically an
installation of an SQL-compliant RDBMS on a computer
system.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-6
3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
CREATE TABLE Commands in SQL
CREATE TABLE company.table …;
• The relations declared through CREATE TABLE statements
are called base tables (or base relations); this means that the
relation and its tuples are actually created and stored as a file
by the DBMS..
• Base relations are distinguished from virtual relations,
created through the CREATE VIEW statement, which may or
may not correspond to an actual physical file.
• In SQL the attributes in a base table are considered to be
ordered in the sequence in which they are specified in the
CREATE TABLE statement.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
CREATE TABLE Commands in SQL
CREATE TABLE table
(column-definition
[, column-definition | table-constraint
...]);
column-defintion =
column
datatype
[ [NOT] NULL ]
[ [DEFAULT constant_expression]
| [IDENTITY [(seed, increment)] ] ]
[ column-constraint ]
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7-8
4
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
CREATE TABLE Commands in SQL
CREATE TABLE employee
(
FNAME
VARCHAR(15) NOT
LNAME
VARCHAR(15) NOT
SSN
CHAR(9)
NOT
BDATE
DATE,
ADDRESS
VARCHAR(15),
SEX
CHAR,
SALARY
DECIMAL(10,2),
SUPERSSN
CHAR(9),
DNO
INT
NOT
…
);
DB / PRM1 / 11.06.2007
NULL,
NULL,
NULL,
NULL
db07_sql_ddl.ppt / Version 2.0
DEFAULT 1,
7-9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Alphanumeric
• CHAR(n)
• VARCHAR(n)
• TEXT
Fixed-length non-Unicode character data with a
maximum length of 8'000 characters
Variable-length non-Unicode character data
with a maximum of 8'000 characters
Variable-length non-Unicode data with a
max. length of 231 - 1 characters
• NCHAR / NVARCHAR / NTEXT
represent the same types as above, but for Unicode characters
(maximal capacity is half as specified above)
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 10
5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Numeric Values: Integers and Floating Point Values
•
•
•
•
BIGINT
INT
SMALLINT
TINYINT
• FLOAT[(n)]
• REAL
DB / PRM1 / 11.06.2007
Integer values from -263 through 263 - 1
Integer values from -231 through 231 - 1
Integer values from -215 through 215 - 1
Integer values from 0 through 255
Floating point values from
-1.79E+308 to -2.23E-308, 0 and
2.23E-308 to 1.79E+308
n (1 to 53) is the nb. of bits for the mantissa
-3.40E+38 to -1.18E-38, 0 and
1.18E-38 to 3.40E+38
db07_sql_ddl.ppt / Version 2.0
7 - 11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Decimal and Numeric
• DECIMAL[(p[, s])]
Fixed precision and scale numeric
values from -1038 +1 through 1038 –1
– Specifies the maximum total number of decimal digits that can
be stored, both to the left and to the right of the decimal point
– The precision (p) must be a value from 1 through the maximum
precision
– The scale (s) specifies the maximum number of decimal digits
that can be stored to the right of the decimal point
• NUMERIC[(p[, s])] Functionally equivalent to Decimal
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 12
6
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Date and Time
• DATETIME
• SMALLDATETIME
DB / PRM1 / 11.06.2007
Date and time values from January
1, 1753, through December 31,
9999, with an accuracy of threehundredths of a second, or 3.33
milliseconds
Date and time data from January 1,
1900, through June 6, 2079, with an
accuracy of one minute
db07_sql_ddl.ppt / Version 2.0
7 - 13
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Money
Monetary data values from -263 through
263 – 1, with accuracy to 1/10'000 of a
monetary unit
• SMALLMONEY Monetary data values from -214,748.3648
through +214,748.3647, with accuracy to
1/10'000 of a monetary unit
• MONEY
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 14
7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Data Types Overview
Access
SQL-Server
Oracle
MySQL
PostgreSQL
boolean
Yes/No
Bit
Byte
N/A
Boolean
Integer
Number (integer)
Int, BigInt
Smallint, Tinyint,
Numeric, Decimal
Number
Numeric
Int
Integer
(synonyms)
Integer
Int
float
Number (single)
Float, Real
Number
Float
Numeric
currency
Currency
Money, Smallmoney
N/A
N/A
Money
string (fixed)
N/A
Char, NChar
Char
Char
Char
string
(variable)
Text (<256)
Memo (65k+)
Varchar
NVarchar
Varchar
Varchar2
Varchar
Varchar
binary object
OLE Object
Memo
Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)
Long Raw
(<2GB)
Blob
Text
Binary
Varbinary
Date & Time
Date/Time
DateTime
SmallDateTime
Timestamp
Date
Timestamp
Date, Time,
DateTime
Timestamp
Date, Time
Timestamp
Interval
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 15
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Binary Data and Semi-Structured Data
• BINARY(n)
Binary data with fixed length n between 1
and 8000
• VARBINARY(n) Binary data with variable length
(maximal n) between 1 and 8000
• IMAGE
Binary data with a maximal size of 231-1
• XML
DB / PRM1 / 11.06.2007
New in SQL Server 2005;
Used to store XML documents or fragments
db07_sql_ddl.ppt / Version 2.0
7 - 16
8
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
CREATE TABLE Commands in SQL
CREATE DOMAIN ssn_type AS CHAR(9);
• A domain can be declared, and the domain name used with
the attribute specification.
• This makes it easier to change the data type for a domain
that is used by numerous attributes in a schema and
improves schema readability.
• We can use ssn_type in place of CHAR(9).
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 17
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
The Constraint Concept
• Constraints are properties or predicates assigned to a
column or a set of columns within a table
• They specify integrity rules and prevent that values that do
not fulfill the rules may be inserted in the table
• Technically we differentiate between
– Column-Constraints – Part of the column-definition and
only affecting this column
– Table-Constraints – Defined independently from the
column definitions and may affect multiple columns
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 18
9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Constraints and Data Integrity
• Constraints may be used to enforce different types of data
integrity, namely:
– Entity Integrity ensures that there are no duplicate rows
in a table
– Domain Integrity enforces valid entries for a given
column by restricting the type, the format, or the range of
possible values
– Referential integrity ensures that rows may not be
deleted, when they are referenced by other rows
– User-Defined Integrity enforces some specific business
rules that do not fall into entity, domain, or referential
integrity categories
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 19
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Syntax for a Column Constraint
[ CONSTRAINT constraint-name ]
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| [ REFERENCES RefTable [(RefColumn)]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
| CHECK (boolean-expression)
}
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 20
10
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Syntax for a Column Constraint: Example
CREATE TABLE departement
(
DNAME
VARCHAR(15)
DNUMBER
INT
MGRSSN
CHAR(9)
MGRSTARDATE
DATE,
…
);
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
NOT NULL,
PRIMARY KEY,
NOT NULL,
7 - 21
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Table Constraint
[ CONSTRAINT constraint-name ]
{ [ { PRIMARY KEY | UNIQUE } (column-list) ]
| [ FOREIGN KEY (column-list)
REFERENCES RefTable [(RefColumn)]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
| CHECK (boolean-expression)
}
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 22
11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Table Constraint: Example
CREATE TABLE Employee
(
Lastname
VARCHAR(30) NOT NULL,
Firstname
VARCHAR(30) NOT NULL,
DeptId
INT NOT NULL,
CONSTRAINT PK_Employee
PRIMARY KEY (Lastname, Firstname),
CONSTRAINT FK_Emp_Dept
FOREIGN KEY (DeptId) REFERENCES Department(Id)
ON DELETE SET DEFAULT ON UPDATE CASCADE,
…
);
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 23
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Primary Key Constraint
•
•
•
A PRIMARY KEY constraint is a unique identifier for a row
within a database table
– Every table should have a primary key constraint to
uniquely identify each row
– Only one primary key constraint can be created for each
table
The primary key constraints are used to enforce entity
integrity
Most DBMS provide a mechanism to create unique key
values without any semantical meaning (e.g., Identity)
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 24
12
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Foreign Key Constraint
•
•
•
•
A FOREIGN KEY constraint is a reference from one table to
another (or the same)
A foreign key in one table points to a primary key in another
table
Foreign keys enforce referential integrity by
– preventing violating actions (NO ACTION; default)
– cascading delete or update actions (CASCADE)
In the latter case a referential triggered action is specified;
alternatives are SET NULL, SET DEFAULT.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 25
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Foreign Key Constraint
•
•
•
The action for SET NULL or SET DEFAULT is the same for
both on ON DELETE or ON UPDATE; the value of the
affected referncing attributes is changed to NULL for SET
NULL, and to the specified default value for SET DEFAULT.
ON DELETE CASCADE is to delete all the referencing
tuples.
ON UPDATE CASCADE is to change the value of the
foreign key to the updated (new) primary value for all
referencing tuples.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 26
13
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Unique Constraint
•
•
•
A UNIQUE constraint enforces the uniqueness of the values
in a set of columns, so no duplicate values are entered
The unique key constraints are used to enforce entity
integrity as the primary key constraints
Unique constraints are also often used for the candidate
keys which have not been chosen to be the primary key.
Typically, the attributes to be unique are then declared with
the NOT NULL modifier.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 27
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Check Constraint
•
•
•
•
A CHECK constraint is used to limit the values that can be
stored in a given column (Column constraint)
It may also be used to prevent invalid or senseless value
combinations within rows (Table constraint)
The check constraints are used to enforce domain integrity
and/or business rules
The argument of a check constraint is a simple or complex
boolean expression containing one or more attributes and to
be satisfied by the attribute values of each tuple contained in
the table.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 28
14
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Syntax for a Column Constraint: Example
CREATE TABLE departement
(
…,
DNUMBER INT NOT NULL CHECK
(DNUMBER>0 AND DNUMBER<21),
…
);
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 29
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Schema Change Statements in SQL
DROP SCHEMA company {CASCADE|RESTRICT};
• If the RESTRICT option is chosen in place of CASCADE, the schema is
dropped only if it has no elements in it; otherwise, the DROP command
will not be executed.
DROP TABLE table {CASCADE|RESTRICT};
• If the RESTRICT schema is chosen instead of CASCADE, a table is
dropped only if it is not referenced in any constraints (for example, by
foreign key definitions in another relation) or views.
• With the CASCADE option, all such constraints and views that refernce
the table are dropped automatically from the schema.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 30
15
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Schema Change Statements in SQL
ALTER TABLE company.employee ADD job VARCHAR(12);
• The definition of a base table can be changed by using the ALTER
command.
• This command adds a new column to the table
ALTER TABLE company.employee DROP address
{CASCADE|RESTRICT};
• If the RESTRICT is chosen, the command is successful only if no views
or constraints reference the column.
• With the CASCADE option, all such constraints and views that reference
the table are dropped automatically from the schema.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 31
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Schema Change Statements in SQL
ALTER TABLE table
{ ADD column-definition | table-constraint
[, column-definition | table-constraint ...]
| ALTER COLUMN column-definition
| DROP constraint
| DROP COLUMN column }
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 32
16
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Commands for Manipulation the Database in SQL
ALTER TABLE company.employee ADD job VARCHAR(12);
• The definition of a base table can be changed by using the ALTER
command.
• This command adds a new column to the table
ALTER TABLE company.employee DROP address
{CASCADE|RESTRICT};
• If the RESTRICT is chosen, the command is successful only if no views
or constraints reference the column.
• With the CASCADE option, all such constraints and views that reference
the table are dropped automatically from the schema.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 33
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT <ATTRIBUTE ANF FUNCTION LIST>
FROM <TABLE LIST>
[WHERE <CONDITION>]
[GROUP BY <GROUPING ATTRIBUTE(S)>]
[HAVING <GROUP CONDITION>]
[ORDER BY <ATTRIBUTE LIST>]
;
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 34
17
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
• The SELECT clause lists the attributes or functions to be
retrieved
• The FROM clause specifies all relations (tables) needed in
the query, including joined relations.
• The WHERE clause specifies the conditions for selection of
tuples from these relations, inclduing joined conditions if
needed.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 35
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT FNAME, LNAME FROM EMPLOYEE;
• Lists all the name pairs of the employees.
SELECT * FROM EMPLOYEE;
• List all attributes of each each employee tuple.
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE SEX = M;
• Lists all male employees.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 36
18
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
• GROUP BY specifies grouping attributes to partition the set
of tuples into a set of subgroups.
• HAVING specifies a condition on the groups being selected
rather than on the individual tuples.
• Finally, ORDER BY specifies an order for displaying the
result of a query.
• The built-in aggregate functions COUNT, SUM, MIN, MAX,
and AVG are usually used in conjunction with grouping, but
they can also be applied to all the selected tuples in a query
without a GROUP BY clause.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 37
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT FNAME, EMPLOYEE.NAME
FROM EMPLOYEE, DEPARTEMENT
WHERE DEPARTEMENT.NAME=‘Research’ AND
DEPARTEMENT.DNUMBER=EMPLOYEE.DNUMBER;
• If a query refers to two or more attributes with the same
name, we must qualify the attribute name with the relation
name to prevent ambiguity.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 38
19
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT E.FNAME, E.LNAME,S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN;
• It is possible to declare alternative relation names, called
aliases or tuple variables (here E and S for the EMPLOYEE
relation).
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 39
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTEMENT;
• This statement produces all the combinations of EMPLOYEE
SSN and DEPARTEMENT DNAME.
SELECT *
FROM EMPLOYEE, DEPARTEMENT;
• This statement produces the cross product of both relations.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 40
20
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
SQL Queries
SELECT ALL SALARY FROM EMPLOYEE;
• This statement generates possibly duplicate tuples.
Duplicates are not eliminated
SELECT DISTINCT SALARY FROM EMPLOYEE;
• This statement eliminates duplicate tuples.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 41
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
The Concept of Indexes
•
•
•
•
•
The order of tuples in a relation is not defined!
Searching in unsorted data is very slow (linear search).
Indexes are constructs that speed up searching or ordering
of data in tables given a specific subset of attributes.
We may declare more than one index for one and the same
relation.
In most RDBMS, the presence of indexes is transparent to
users and programmers.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 42
21
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Creating and Removing Indexes
• Create a new index:
CREATE [UNIQUE]
INDEX index ON table
(column [ASC|DESC]
[,column [ASC|DESC] ...]);
• Remove an existing index:
DROP INDEX Tablename.index;
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 43
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Unique and not-unique Index
• Indexes may be helpful or even necessary in the following
cases:
– unique indexes for the primary keys of all tables,
– unique indexes for candidate keys,
– non-unique indexes for often used foreign keys, and
– non-unique indexes for specific sort orders and search
procedures.
• Most DBMS automatically create indexes for primary keys,
for foreign keys with forced referential integrity, and for
unique constraints.
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 44
22
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Remarks
• The DROP INDEX statement does not apply to indexes
automatically created by the system for constraint
specifications
• All indexes are bound to exactly one table; dropping this table
also drops any index defined on the table
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 45
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
DB / PRM1 / 11.06.2007
db07_sql_ddl.ppt / Version 2.0
7 - 46
23