Data Manipulation Language (DML) Data Definition ..., Summaries of Database Management Systems (DBMS)

Download Data Manipulation Language (DML) Data Definition ... and more Summaries Database Management Systems (DBMS) in PDF only on Docsity!

Data Definition Lang. (DDL)

Data Manipulation Lang. (DML)

Views & Indexes

EECS3421 - Introduction to Database Management Systems

SQL Main Components

• Queries − Subset of SQL for read-only access to database − SELECT statements
• Data Definition Language (DDL) − Subset of SQL used to describe database schemas − CREATE, ALTER, DROP statements − Data types − Integrity constraints
• Data Manipulation Language (DML) − Subset of SQL used to manipulate data in databases − INSERT, DELETE, UPDATE statements

Creating (Declaring) a Schema

• A schema is essentially a namespace − it contains named objects (tables, data types, functions, etc.)
• The schema name must be distinct from any existing schema name in the current database
• Syntax: CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ] Examples: CREATE SCHEMA myschema; CREATE SCHEMA myschema AUTHORIZATION manos;

Creating (Declaring) a Relation/Table

• To create a relation:

CREATE TABLE (

• To delete a relation:

DROP TABLE ;

• To alter a relation (add/remove column): ALTER TABLE ADD ALTER TABLE DROP

Examples

• To create a relation: CREATE TABLE employees ( id INTEGER, first_name CHAR(50), last_name VARCHAR(100));
• To delete a relation: DROP TABLE employees;
• To alter a relation (add/remove column): ALTER TABLE employees ADD age INTEGER; ALTER TABLE employess DROP last_name;

SQL Values

• Integers and reals are represented as you would

expect

• Strings are too, except they require single quotes.

− Two single quotes = real quote, e.g., ’Joe’’s Bar’

• Any value can be NULL

− Unless attribute has NOT NULL constraint E.g.: price REAL NOT NULL

INTEGRITY CONSTRAINTS

10

Running Example

Beers(name, manf)

Bars(name, addr, license)

Drinkers(name, addr, phone)

Likes(drinker, beer)

Sells(bar, beer, price)

Frequents(drinker, bar)

Underline = key (tuples cannot have the same value in all

key attributes)

− Excellent example of a constraint

Declaring Keys

• An attribute or list of attributes may be declared

PRIMARY KEY or UNIQUE

− Either says that no two tuples of the relation may agree in all the attribute(s) on the list − There are a few distinctions to be mentioned later − Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute. CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY , manf CHAR(20) );

Declaring Multi-attribute Keys

• A key declaration can appear as element in the list of elements of a CREATE TABLE statement
• This form is essential if the key consists of more than one attribute CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY (bar, beer));

The bar and beer together are the key for Sells

Foreign Keys

• Values appearing in attributes of one relation must appear

together in certain attributes of another relation

Example:

We might expect that a value in Sells.beer also appears

as value in Beers.name

Beers (name, manf)

Sells (bar, beer, price)

Expressing Foreign Keys

• Use keyword REFERENCES , either: − After an attribute (for one-attribute keys) REFERENCES () − As an element of the schema:

FOREIGN KEY ()

REFERENCES ()

• Referenced attributes must be declared PRIMARY KEY

or UNIQUE

Example: As Schema Element

CREATE TABLE Beers (

name CHAR(20) PRIMARY KEY ,

manf CHAR(20) );

CREATE TABLE Sells (

bar CHAR(20),

beer CHAR(20),

price REAL,

FOREIGN KEY (beer) REFERENCES Beers(name));

Enforcing Foreign-Key Constraints

• If there is a foreign-key constraint from relation R to relation S , two violations are possible: − An insert or update to R introduces values not found in S − A deletion or update to S causes some tuples of R to “dangle” Example: suppose R = Sells , S = Beers
• An insert or update to Sells that introduces a non-existent beer must be rejected
• A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways (next slide).