sql-ddl.pdf, Exercises of History

Download sql-ddl.pdf and more Exercises History in PDF only on Docsity!

SQL

SQL

• SQL = “Structured Query Language”
• Standard query language for relational DBMSs
• History: Developed at IBM in late 70s 1 st standard: SQL- 2 nd standard: SQL- 3 rd standard: SQL-99 or SQL3, well over 1000 pages “The nice thing about standards is that you have so many to choose from!” -Andrew S. Tannenbaum

SQL

data defini.on

language

SQL Data Definition Language (DDL)

• Allows the specification of the database schema a set of relations with information about each relation
• Schema information:
  • The schema of each relation
  • The domain of values associated with each attribute
  • Integrity constraints
• Other information one can specify:
  • The set of indices to be maintained for each relation
  • Security and authorization information for each relation
  • The physical storage structure of each relation on disk

Domain Types in SQL

• char(n) Fixed length character string, with user-specified length n
• varchar(n) Variable length character strings, with user-specified maximum length n
• int Integer (a finite subset of integers that is machine-dependent)
• smallint Small integer (a machine-dependent subset of the integer domain type)

Domain Types in SQL

• numeric(p, d) Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point
• real, double precision Floating point and double-precision floating point numbers, with machine-dependent precision
• float Floating point number, with user-specified precision of at least n digits

and others…

DROP TABLE Command

• Used to remove a relation & its definition The relation can no longer be used in queries, updates, or any other commands since its description no longer exists
• Syntax: DROP TABLE relationName
• Example: DROP TABLE branch

ALTER TABLE Command

• Used to add/drop attributes from a relation
• Add attribute syntax: ALTER TABLE relationName ADD attribName attribDomain All tuples in the relation are assigned null as the default value of the new attribute
• Drop attribute syntax: ALTER TABLE relationName DROP attribName Dropping of attributes not supported by many DBMSs

Integrity Constraints

• Guard against accidental damage to the database by ensuring that authorized changes to the database do not result in a loss of data consistency.
• Examples:
  • A savings account must have a balance greater than $10,000.
  • A salary of a bank employee must be at least $6.00 an hour
  • A customer must have a (non-null) phone number

SQL Integrity Constraints

• On single relations:
  • not null
  • primary key
  • unique
  • check(P), where P is a predicate
• On multiple relations:
  • foreign key

UNIQUE Constraint

• Specifies that a set of attributes form a candidate key
• Syntax: UNIQUE (AttrName 1 , …, AttrNamen)
• Candidate keys are permitted to be null

(in contrast to primary keys)

CHECK Clause

• Enforce a predicate (condition)
• Syntax: CHECK (Predicate)
• Example: Ensure that the values of the assets are non-negative CREATE TABLE branch (branch_name char (15), branch_city char (30), assets integer , primary key (branch_name), CHECK (assets >= 0) )

Referential Integrity

• Ensures that a value that appears in one relation for a

given set of attributes also appears for a set of attributes in

another relation.

• Example: If “La Jolla” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “La Jolla”.

Referential Integrity

• In the CREATE TABLE statement we can use:
  • The PRIMARY KEY clause to list primary key (PK) attributes.
  • The UNIQUE KEY clause to list candidate key attributes
  • The FOREIGN KEY clause to list foreign key (FK) attributes and the name of the relation referenced by the FK. By default, a FK references PK attributes of the referenced table.