Learn Database Online Tutorial

THE RELATIONAL MODEL
Chapter outline

  1. Introduction to relational model
  2. Relational query languages
  3. Objectives of SQL
  4. Writing SQL commands
  5. Data manipulation
  6. Data definition
  7. Integrity constraints over relations
  8. Logical database design : ER to relation

Relational model

  1. Most widely used model
  2. Relation : main construct for representing data
  3. A relation consists of
    1. Relation schema – specifies name of relation, plus name and type of each column

                Students(sid:string, name:string, login:string,
age:integer, gpa:real)

    1. Relation instance – a table, with rows and columns

Relational query languages

  1. A major strength of the relational model :  supports simple, powerful querying of data
  2. Queries can be written intuitively, and the DBMS is responsible for efficient evaluation.
    1. The key : precise semantics for relational queries
    2. Allows the optimizer to entensively re-order operations, and still ensure that the answer does not change

Objectives of SQL

  1. A database language should allow a user to :

                - Create the database and relation structures
- Perform basic data management tasks, such as insert, update, and delete data from relations
- Perform both simple and complex queries to transform the raw data into information


Structured Query Language
(SQL)

  1. SQL is a transform-oriented language, or a language designed to use relations to transform inputs into required outputs
  2. 2 major components :

                - Data Definition Language (DDL) for defining the database structure
- Data Manipulation Language (DML) for retrieving and updating data.


Writing SQL Commands

  1. An SQL statement consists of reserved words and user-defined words
  2. Use a statement terminator (;) to mark the end of each SQL statement
  3. Most components of an SQL statement are case insensitive
  4. Literal character data must be typed exactly as it appears in the database
  5. Use indentation and lineation to make the statement more readable

                - Each clause in a statement should begin on a new line
- The beginning of each clause should line up with the beginning of other clauses
- If a clause has several parts, they should each appear on a separate line and be indented under the start of the clause to show the relationship


Data Manipulation

  1. INSERT  To insert data into  a table
  2. UPDATE               To update data in a table
  3. DELETE  To delete data from a table
  4. SELECT  To query data in the database

***        1. [ ] means optional
2. condition :
- expression op expression (op -> comparison operators)
- use logical connectives AND, OR, NOT to combine expressions
- expression is a column name, a constant, or an (arithmetic or string) expression

INSERT statement

  1. Insert a single row

                INSERT INTO table_name [(column_list)]
VALUES (data_value_list);
ex INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53688, ‘Mike’, ‘mike@ee’, 17, 3.4);

  1. Insert multiple rows (data may come from one or more tables)

                INSERT INTO table_name [(column_list)]
SELECT ...;

  1. note - number, order, data type

UPDATE statement
UPDATE table_name
SET column_name1 = data_value1[, column_name2 = data_value2 …]
[WHERE search_condition];
ex           UPDATE               Students
SET                         age = age + 1
WHERE name = ‘smith’;
DELETE statement
DELETE FROM table_name
[WHERE search_condition];
ex           DELETE FROM Students
WHERE age >= 21 AND age <= 23;
SELECT statement(study in chapter6)
SELECT [DISTINCT|ALL] {*|column_expression [AS new_name]][,…]}
FROM     table_name [alias][,…]
[WHERE condition]
[GROUP BY column_list][HAVING condition]
[ORDER BY column_list];
***           1. [ ] means optional  and              {} means required
2. condition :
- expression op expression (op -> comparison operators)
- use logical connectives AND, OR, NOT to combine expressions
- expression is a column name, a constant, or an (arithmetic or string) expression


Data definition

  1. DDL allows database objects such as schemas, domains, tables, views, and indexes to be created and destroyed.
  2. Main DDL statements are :

                CREATE SCHEMA              DROP SCHEMA
CREATE DOMAIN             DROP DOMAIN
CREATE TABLE  DROP TABLE  ALTER TABLE
CREATE INDEX   DROP INDEX
CREATE VIEW                     DROP VIEW


CREATE SCHEMA

  1. A schema is a named collection of database objects which can be tables, views, domains, and other constructs (such as authorization grants and assertions).

                CREATE SCHEMA [name|AUTHORIZATION creator_identifier]
ex           CREATE SCHEMA sql_tests           AUTHORIZATION Smith;

  1. A catalog is a named collection of schemas. Schemas within the same catalog can also share certain elements, such as domain definitions.

DROP SCHEMA

  1. DROP SCHEMA name [RESTRICT|CASCADE];

                default is RESTRICT : the schema must be empty otherwise the operation fails.
CASCADE : the operation cascades to drop all objects associated with the schema. If any of these drop operations fail, the DROP SCHEMA fails.

  1. In some implementaions,

                                CREATE DATABASE database_name;
is used instead.


Data types

  1. ISO SQL data type (6 types)

1. Character : CHAR, VARCHAR
2. Bit : BIT, BIT VARYING
3. Exact numeric : NUMERIC, DECIMAL, INTEGER, SMALLINT
4. Approximate numeric : FLOAT, REAL, DOUBLE PRECISION
5. Datetime : DATE, TIME, TIMESTAMP
6. Interval : INTERVAL
represent periods of time
ex INTERVAL YEAR(2) TO MONTH
means 0 years 0 months – 99 years 11 months


CREATE/DROP DOMAIN

  1. CREATE DOMAIN domain_name [AS] data_type

                                [DEFAULT default_option]
[CHECK search_condition];
ex           CREATE DOMAIN sex_type AS CHAR(1)
CHECK (VALUE IN (‘M’, ‘F’));
ex           CREATE DOMAIN ssn_type AS CHAR(9);
ex           CREATE DOMAIN ratingval INTEGER DEFAULT 1
CHECK (VALUE >=1 AND VALUE<= 10);

  1. DROP DOMAIN domain_name [RESTRICT|CASCADE];

CREATE TABLE statement


CREATE TABLE table_name (
field_name       domain NOT NULL,
field_name       domain DEFAULT ..,
field_name       domain,
PRIMARY KEY (field_name),
UNIQUE (field_name),
FOREIGN KEY (filed_name)        REFERENCES table_name                                                                                             ON DELETE …
ON UPDATE …);
CREATE TABLE Students (
sid                                CHAR (20),
name          CHAR (20),
login                            CHAR (20),
age                         INTEGER,
gpa                         REAL,
PRIMARY KEY (sid));


DROP TABLE

  1. DROP TABLE table_name [RESTRICT|CASCADE];

                ex           DROP TABLE department;

  1. DROP TABLE statement will delete both data and table
  2. RESTRICT : cannot drop if there are other objects that depend on the table to be dropped
  3. CASCADE : drop table and all dependent objects (be careful to use CASCADE)

ALTER TABLE

  1. To change a table definition
  2. ALTER TABLE table_name

                   [ADD [COLUMN] column_name data_type [NOT                                           NULL][DEFAULT default_option][CHECK(search_condition)]]
[DROP [COLUMN] column_name [RESTRICT|CASCADE]]
[ADD [CONSTRAINT [constraint_name]]
table_constraint_definition]
[DROP CONSTRAINT constraint_name [RESTRICT|CASCADE]]
[ALTER [COLUMN] SET DEFAULT default_option]
[ALTER [COLUMN] DROP DEFAULT]
ex           ALTER TABLE Employee
ADD job VARCHAR(12);
ex           ALTER TABLE Employee
DROP address CASCADE;
ex           ALTER TABLE Department
ALTER mgrssn SET DEFAULT ‘332254122’;
ex           ALTER TABLE Employee
ALTER gender DROP DEFAULT;


INDEX

  1. An index is a structure that provides accelerated access to the rows of a table based on the values of one or more columns. However, since indexes may be updated by the system every time the underlying tables are updated, additional overheads may be incurred.
  2. Suppose we have a relation
  3.                Person (name, age, city)
  4. Sequential scan of the file Person may take long

CREATE/DROP INDEX

  1. CREATE [UNIQUE] INDEX index_name

                                ON table_name (column_name [ASC|DESC] [,…]);

  1. DROP INDEX index_name;

ex           CREATE UNIQUE INDEX ssn_ind ON                         Employee(ssn);
ex           CREATE INDEX  nameIndex ON Person(name)
ex           DROP INDEX ssn_ind;

  1. Indexes can be created only on base tables, not on views.

CREATE VIEW
Ex           CREATE VIEW B_Students (name, sid, course)
AS SELECT S.sname, S.sid, E.cid
FROM    Students S, Enrolled E
WHERE  S.sid = E.studid AND E.grade =‘B’;

  1. This view can be used just like a base table in defining new queries or views

Types of Views

  1. Virtual views:
    1. Used in databases
    2. Computed only on-demand – slow at runtime
    3. Always up to date
  2. Materialized views
    1. Used in data warehouses
    2. Pre-computed offline – fast at runtime
    3. May have stale data

DROP VIEW

  1. DROP VIEW view_name [RESTRICT|CASCADE];

                ex DROP VIEW B_Students CASCADE;

  1. Advantages of  views

                - data independence
- security
- reduced complexity

  1. Disadvantages of  views

- update restriction
- performance


Integrity constraints over relations

  1. An integrity constraint (IC) is a condition specified on a database schema and restricts the data that can be stored in an instance of the database.
  2. An instance in database must be a legal instance
  3. Four types of IC

     - Key constraints
- Foreign key constraints
- Domain constraints
- General constraints
Key constraints

  1. A key constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

 Ex Students (sid : string, name : string, login : string,                       age : integer, gpa : real)
sid is Primary key
cannot - dup rec
- not null
Foreign key constraints
(Referential integrity constraints)

  1. The information stored in a relation is linked to the information stored in another relation. If one of the relations is modified, the other must be checked, and perhaps modified, to keep the data consistent.
  2. Referential integrity is analogous to a prohibition against dangling pointers, or other kinds of dangling references in conventional programs.

                Ex Enrolled (studid : string, cid : string, grade : string)


Foreign key constraints
(Referential integrity constraints)

- Delete all Enrolled rows that refer to the deleted Students row (CASCADE)                       ok?
- Disallow the deletion of the Students row if an Enrolled row refers to it. (NO ACTION) ok?
- For every Enrolled row that refers to it, set the studid column to the sid of some (existing) ‘default’ student. (SET DEFAULT)                            ok?
- For every Enrolled row that refers to it, set the studid column to null. (SET NULL)            ok?
3. What should we do if the primary key value of a Students row is updated?
Ans  The  options are similar to the previous case.


Domain constraints

  1. Domain constraints require that the value of an attribute must be drawn from a specific set of values or lie within a specific range.

     Ex  age : INTEGER (>16) 


General constraints

  1. General constraints are arbitrary assertions that are required to hold in the database.

     Ex  no more that 10 stars be listed for any one movie
** SET CONSTRAINT … CHECK ….
CREATE ASSERTION … CHECK …


Logical database design : ER to relational


1. Entity sets to tables
2. Relationship sets to tables
3. Translating relationship sets with key constraints
4. Translating relationship sets with participation constraints
5. Translating weak entity sets
6. Translating class hierarchies
7. Translating ER diagram with aggregation
8. Additional example


Summary (ER to relational)


1. Strong entity --> Relation
2. Weak entity   --> Relation with PK of owner entity (as FK in this relation)
3. 1:1                     --> ex S and T, choose one relation such as choose S to include PK of T as a FK in S
4. 1:N                    --> include PK of side-1 entity, descriptive attribute of relationship to side-n entity (the PK included is the FK in side-n entity)
5. M:N                  --> create a new relation to represent the relationship. PK of this new relation are from the PK of each entity participated in this relationship