Ch 3: Intro to SQL

Question 1

3 Basic

Integrity Constraints

that can be defined in a Relation

Answer 1

primary key( A_j1, A_j2, … , A_jm)

Specifies given attributes as the primary key (all together)

Primary key attributes must be non-null and unique

foreign key( A_k1, A_k2, … , A_kn) references Relation_S

Attributes must be the Primary Key of some tuple in the other relation

not null

Added to a specific attribute

example: name varchar(20) not null,

Question 2

Important Parts/Features

of the SQL Language

(7)

Answer 2

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Integrity
• View Definition
• Transaction Control
• Embedded SQL/Dynamic SQL
• Authorization

Question 3

SQL Features:

Data Definition Language( DDL )

Answer 3

Commands for

• defining relational schemas
• modifying relational schemas

Question 4

SQL Features:

Data Manipulation Language( DML )

Answer 4

Commands for:

• Querying data from the database
• Insert/delete tuples
• Modify tuples

Question 5

SQL Features:

Integrity

Answer 5

Part of the DDL part of SQL

SQL provides commands for specifying Integrity Constraints

Also prevents updates from violating Integrity Constraints

Question 6

SQL Features:

View Definition

Answer 6

SQL includes commands for

Defining Views

Part of being DDL

Question 7

SQL Features;

Transaction Control

Answer 7

Commands for specifying the

beginning and end

of transactions

Question 8

SQL Features:

Embedded SQL/Dynamic SQL

Answer 8

Defines how SQL statements can be

embedded within

general purpose languages

Question 9

SQL Features:

Authorization

Answer 9

Includes commands for specifying

Access Rights

to relations and views

Question 10

Two Aspects of

SQL as a Language

Answer 10

The SQL Language provides functionality for:

• Data Definition (DDL)
  
  • Declaring and modifying database schemas
• Data Manipulation (DML)
  
  • Operations for working with actual data
  • Queries and modifications of tuples

Question 11

Three kinds of Relations

in SQL

Answer 11

• Stored Relations
  
  • Called Tables
  • kind of relation we deal with normally
  • relations that are stored in the database
• Views
  
  • relations defined by a computation
  • not stored
  • constructed in whole or part when needed
• Temporary Tables
  
  • constructed by the SQL language when executing queries/modificatons
  • thrown away after query, not stored

Question 12

SQL Statement

to Define a Relation Schema

Answer 12

CREATE TABLE

• Gives a name for the table, its attributes and their data types
• Also allows declaring a key, or several, for a relation
• Allows constraint and index declarations, among others

Question 13

SQL Primitive Data Types

Answer 13

CHAR(n), VARCHAR(n)

BIT(n), BITVARYING(n)

BOOLEAN

INT, or INTEGER

FLOAT, or REAL, DOUBLE PRECISION

DECIMAL( n, d)

DATE

TIME

Question 14

SQL Example:

Declare this schema in SQL:

Movies( title: string, year:integer, genre: string)

Answer 14

CREATE TABLE Movies (

title CHAR(100),

year INT,

genre CHAR(10)

);

end with semicolon, each attribute has the form:

name TYPE

Question 15

The SQL DDL

allows Specification of

What Relation Properties?

Answer 15

• Schema for each relation
• Types of Values associated with each attribute
• Integrity Constraints on values
• Set of Indices to be maintained for each relation
• Security and Authorization info for each relation
• Physical Storage Structure of each relation on disk

Question 16

Basic SQL Table Commands (5)

Answer 16

CREATE TABLE r( A₁ D₁, A₂ D₂, … );

creates a table with the specified attributes and types

DROP TABLE r;

remove a table from the database

ALTER TABLE r ADD a d;

add the attribute ‘a’ with type ‘d’ to the table

ALTER TABLE r DROP a;

remove attribute ‘a’ from the table

DELETE FROM r;

INSERT INTO r values( v1, v2, … , vn);

add a tuple into the table r