Advanced SQL Part 2

Views: Named Queries

Subqueries in FROM

WITH

Access Control

  • Object can be a Table or a View
  • Privileges can be
    • Select
    • Insert
    • Delete
    • References (cols) – allow to create a foreign key that references the specifies column(s)
    • All
  • Can later be REVOKEd
  • Users can be single users or groups

Integrity Constraints

  • Every legal instance of a relation must satisfy ICs.
  • Types of IC’s: Domain constraints, prmary key constraints, foreign key constraints, general consraints
    • Domain constraints: Field values must be of right type
    • Primary key and foreign key constrains:

Keys

  • A set of fields is a superkey if
    • No two distinct tuples can have same values in all key fields
  • A set of files is a key for a relation if it is minimal:
    • It is a superkey
    • No subset of the fields is a superkey
  • One of keys is chosen to be primary key.
    • Other keys are called candidate keys.
  • Specify candidate keys using UNIQUE

General Constraints

  • Create constraints using CHECK
  • Name constraints using CONSTRAINT

Median – Example

Leave a Reply

Your email address will not be published.