• No results found

How To Create A Table In Sql (Ahem)

N/A
N/A
Protected

Academic year: 2021

Share "How To Create A Table In Sql (Ahem)"

Copied!
23
0
0

Loading.... (view fulltext now)

Full text

(1)

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.

(2)

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

(3)

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

(4)

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)] ] ]

(5)

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)

(6)

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

63

through 2

63

- 1

• INT Integer values from -2

31

through 2

31

- 1

• SMALLINT Integer values from -2

15

through 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

(7)

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

63

through 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

(8)

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

(9)

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

(10)

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) }

(11)

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) }

(12)

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

(13)

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.

(14)

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

(15)

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.

(16)

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 }

(17)

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>]

;

(18)

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.

(19)

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.

(20)

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.

(21)

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.

(22)

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

(23)

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

References

Related documents

In Row 6—directly to the right of the formula cell created in Step 7—insert in separate cells all of the numerical values for changing variable 1 (x) that you would like Excel

Any user can create table, you can select statement creates a column is with a table, database using index page compression algorithm for analyzing..

PostgreSQL Create Schema with examples database table create select insert update delete join function index clause trigger view procedure etc.. You can create a table in DbSchema

We favor continuous integration, sql schema version table that manipulates it may prevent me from sql server table is easy if required update development instances.. It is a cvs