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 - 3
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).
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Implementation
CREATE ...
... DATABASE ... USER ... TABLE ... VIEW ... INDEX GRANT
CREATE ...
... PROCEDURE ... FUNCTION
Operation
INSERT UPDATE GRANT SELECT DELETE REVOKE
Maintenance & Optimization
CREATE / ALTER / DROP
... TABLE ... VIEW
... INDEX ... FUNCTION
... PROCEDURE ... TRIGGER
... USER ... DATABASE
GRANT / REVOKE
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 5
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"
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
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 7
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.
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)] ] ]
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 9
CREATE TABLE Commands in SQL
CREATE TABLE employee (
FNAME VARCHAR(15) NOT NULL, LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(15),
SEX CHAR,
SALARY DECIMAL(10,2), SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,
… );
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Alphanumeric
• CHAR(n) Fixed-length non-Unicode character data with a maximum length of 8'000 characters
• VARCHAR(n) Variable-length non-Unicode character data with a maximum of 8'000 characters
• TEXT 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 - 11
Numeric Values: Integers and Floating Point Values
• BIGINT Integer values from -2
63through 2
63- 1
• INT Integer values from -2
31through 2
31- 1
• SMALLINT Integer values from -2
15through 2
15- 1
• TINYINT Integer values from 0 through 255
• FLOAT[(n)] 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
• REAL -3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38
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 - 13
Date and Time
• DATETIME Date and time values from January 1, 1753, through December 31, 9999, with an accuracy of three- hundredths of a second, or 3.33 milliseconds
• SMALLDATETIME Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Money
• MONEY Monetary data values from -2
63through 2
63– 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
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 15
SQL Data Types Overview
Date, Time Timestamp Interval Date, Time,
DateTime Timestamp Date
Timestamp DateTime
SmallDateTime Timestamp Date/Time
Date & Time
Binary Varbinary Blob
Text Long Raw (<2GB) Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB) OLE Object
Memo binary object
Varchar Varchar
Varchar Varchar2 Varchar
NVarchar Text (<256)
Memo (65k+) string
(variable)
Char Char
Char Char, NChar
N/A string (fixed)
Money N/A
N/A Money, Smallmoney Currency
currency
Numeric Float
Number Float, Real
Number (single) float
Integer Int Int
Integer (synonyms) Number
Numeric Int, BigInt
Smallint, Tinyint, Numeric, Decimal Number (integer)
Integer
Boolean N/A
Byte Bit
Yes/No boolean
PostgreSQL MySQL
Oracle SQL-Server
Access
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 2
31-1
• XML New in SQL Server 2005;
Used to store XML documents or fragments
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 17
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).
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 - 19
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
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 - 21
Syntax for a Column Constraint: Example
CREATE TABLE departement (
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT PRIMARY KEY,
MGRSSN CHAR(9) NOT NULL,
MGRSTARDATE DATE,
… );
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 - 23
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,
… );
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
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 25
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.
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 - 27
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.
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
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 29
Syntax for a Column Constraint: Example
CREATE TABLE departement (
…,
DNUMBER INT NOT NULL CHECK
(DNUMBER>0 AND DNUMBER<21),
… );
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 - 31
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.
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 - 33
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.
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 - 35
• 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.
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 - 37
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.
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 - 39
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).
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 - 41
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.
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 - 43
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;
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
DB / PRM1 / 11.06.2007 db07_sql_ddl.ppt / Version 2.0 7 - 45
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
Berner Fachhochschule
Hochschule für Technik und Informatik HTI