Database Management Systems Relational Model Sule H. Turgut Uyar - - PowerPoint PPT Presentation

Database Management Systems

Relational Model

• H. Turgut Uyar

¸ Sule ¨ O˘ g¨ ud¨ uc¨ u February 2005

Contents

Relational Model Domains Relation Structure Data Definition SQL Data Types SQL Commands Data Manipulation SQL Commands

Data Models

◮ previous models:

◮ inverted list ◮ hierarchic ◮ network

◮ relational model:

◮ Dr. E. F. Codd, 1970

◮ recent models:

◮ object ◮ object / relation

Relational Model

◮ data is modelled as relations:

α ⊆ A × B × C × ...

◮ every element of a relation is a tuple ◮ every data of an element is an attribute ◮ relations are represented by tables

◮ user should perceive all the data as tables ◮ relation → table, tuple → row, attribute → column

Relation Example

Example (Movie Data)

Table: MOVIE

TITLE YR DIRECTOR COUNTRY SCORE VOTES Usual Suspects 1995 Bryan Singer UK 8.7 3502 Suspiria 1977 Dario Argento IT 7.1 1004 Being John Malkovich 1999 Spike Jonze US 8.3 13809 ... ... ... ... ... ...

◮ the element (Usual Suspects, 1995, Bryan Singer, UK, 8.7,

3502) is a tuple of the movie relation

◮ YR is an attribute of the elements of the movie relation

Relation Predicate

Definition

relation predicate: sentence that states the meaning of the relation

◮ every tuple is either True or False with respect to predicate

Predicate Example

Example

the film titled TITLE was directed by DIRECTOR in the country of COUNTRY in the year YEAR; the average of the votes VOTES is SCORE.

◮ (Suspiria, 1977, Dario Argento, IT, 1004, 7.1)

tuple is true

◮ (Suspiria, 1877, Dario Argento, IT, 1004, 7.1)

tuple is false

Order of Tuples

◮ tuples are unordered

Example

these two relations have no difference:

TITLE ... Usual Suspects ... Suspiria ... Being John Malkovich ... TITLE ... Suspiria ... Being John Malkovich ... Usual Suspects ...

Order of Attributes

◮ attributes are unordered

Example

these two relations have no difference:

TITLE YR ... Usual Suspects 1995 ... Suspiria 1977 ... Being John Malkovich 1999 ... YR TITLE ... 1995 Usual Suspects ... 1977 Suspiria ... 1999 Being John Malkovich ...

Duplicate Tuples

◮ there are no duplicate tuples

◮ each tuple should be distinguished from each other

Example

TITLE YR DIRECTOR COUNTRY SCORE VOTES Usual Suspects 1995 Bryan Singer UK 8.7 3502 Suspiria 1977 Dario Argento IT 7.1 1004 Being John Malkovich 1999 Spike Jonze US 8.3 13809 ... ... ... ... ... ... Suspiria 1977 Dario Argento IT 7.1 1004 ... ... ... ... ... ...

❍❍ ❍ ❥ ✟✟ ✟ ✯

Domain

◮ the values of the same attribute should be on the same domain ◮ comparison is only meaningful when it is between two values

• f the same domain

◮ in practice, only data types are used

Domain Example

Example

◮ TITLE should be taken from titles domain, YR should be

taken from years domain, COUNTRY should be taken from countries domain ...

◮ using the data types:

TITLE string, YR integer, COUNTRY string, ...

◮ COUNTRY attribute might have the value“Woody Allen”but

it won’t be meaningful

◮ YR and VOTES values are integers but comparing them is not

meaningful

Null Value

the value of an attribute in a tuple is unknown

Example

the director of“Blade”is unknown tuple does not have a value for that attribute

Example

no one has voted for“Star Wars” , division by zero is not allowed, therefore SCORE is null

Default Value

◮ a default value may be used for an unknown attribute ◮ default value should be not a valid value

Example

the attribute SCORE might have a default value of -1

Relation Structure

relation heading

◮ set of attributes that create

the relation

◮ stated when the relation is

being created

◮ effected by data definition

language commands

relation body

◮ set of tuples in a relation ◮ effected by data

manipulation language commands

Relation Structure Example

Example

Table: MOVIE

ID TITLE YR DIRECTOR COUNTRY SCORE VOTES ... ... ... ... ... ... ... 6 Usual Suspects 1995 Bryan Singer ... 1512 Suspiria 1977 Dario Argento ... 70 Being John Malkovich 1999 Spike Jonze ... ... ... ... ... ... ... ...

◮ the row with blue background color is heading ◮ the rows with white background color is body

Data Definition Language

◮ creating and destroying relations ◮ changes in relation heading

◮ changing relation name ◮ adding attribute ◮ changing attribute name

◮ adding and deleting constraints

Logical Type

◮ BOOLEAN

Numerical Types

◮ INTEGER

◮ SMALLINT

◮ NUMERIC (precision, scale)

◮ precision: number of total digits ◮ scale: number of digits after dot ◮ DECIMAL (precision, scale) synonymous

◮ FLOAT (p)

◮ p: least acceptable precision

String Types

◮ CHARACTER [VARYING] (n)

◮ CHARACTER (n) if the characters of the value are less than n,

value is padded by spaces

◮ CHAR (n) instead of CHARACTER (n)

VARCHAR (n) instead of CHARACTER VARYING (n)

Large Object Types

◮ random length ◮ no query on them ◮ binary: BINARY LARGE OBJECT (n)

◮ BLOB ◮ picture, sound, etc.

◮ text: CHARACTER LARGE OBJECT (n)

◮ CLOB

Date/Time Types

◮ DATE

◮ example value: 2005-09-26

◮ TIME

◮ example value: 11:59:22.078717

◮ TIMESTAMP

◮ example value: 2005-09-26 11:59:22.078717

◮ INTERVAL

◮ example value: 3 days

Creating Tables

Command

CREATE TABLE table name ( column name type [DEFAULT d e f a u l t v a l u e ] [ , column name type [DEFAULT d e f a u l t v a l u e ] ] ∗ )

Drop Table Command

DROP TABLE table name

Create Table Example

Example

CREATE TABLE MOVIE ( TITLE VARCHAR(80) , YR NUMERIC(4) , DIRECTOR VARCHAR(40) , COUNTRY CHAR(2) , SCORE FLOAT, VOTES INTEGER DEFAULT 0 )

Changing Table Names

Command

ALTER TABLE table name RENAME TO new name

Example

ALTER TABLE MOVIE RENAME TO FILM

Adding Columns

Command

ALTER TABLE table name ADD [COLUMN] column name type [DEFAULT d e f a u l t v a l u e ]

Example

ALTER TABLE MOVIE ADD COLUMN LANGUAGE CHAR(2)

Deleting Columns

Command

ALTER TABLE table name DROP [COLUMN] column name

Example

ALTER TABLE MOVIE DROP COLUMN LANGUAGE

Changing Column Names

Command

ALTER TABLE table name RENAME [COLUMN] column name TO new name

Example

ALTER TABLE MOVIE RENAME COLUMN TITLE TO NAME

Changing Default Value of a Column

Command

ALTER TABLE table name ALTER [COLUMN] column name SET DEFAULT new value

Example

ALTER TABLE MOVIE ALTER COLUMN SCORE SET DEFAULT −1

Deleting Default Value of a Column

Command

ALTER TABLE table name ALTER [COLUMN] column name DROP DEFAULT

Example

ALTER TABLE MOVIE ALTER COLUMN SCORE DROP DEFAULT

Data Manipulation Language

◮ tuple

◮ insert ◮ update ◮ delete

◮ processed on set of tuples

◮ all the tuples in a specific condition ◮ inserts generally add one tuple

Insert Row

Command

INSERT INTO table name [ ( column name [ , column name ] ∗ ) ] VALUES ( value [ , value ] ∗ )

◮ order of the values should match the order of columns ◮ unspecified columns will have defalut values ◮ if the coulmn names aren’t specified, all the values of the

columns should be in the order of the table creation

Insert Row Examples

Example

INSERT INTO MOVIE VALUES ( ’ Usual Suspects ’ , 1995 , ’ Bryan Singer ’ , ’UK ’ , 8.7 , 35027 )

Insert Row Examples

Example

INSERT INTO MOVIE (YR, TITLE) VALUES ( 1995 , ’ Usual Suspects ’ )

Delete Row

Command

DELETE FROM table name [WHERE c o n d i t i o n ]

◮ if no conditions are provided, then all the rows will be deleted

Delete Row Examples

Example (delete the movies having score less than 3)

DELETE FROM MOVIE WHERE (SCORE<3)

Delete Row Examples

Example (delete the movies having score less than 3 and votes at least 5)

DELETE FROM MOVIE WHERE ((SCORE<3) AND (VOTES>=5))

Update Rows

Command

UPDATE table name SET column name=value [ , column name=value ]∗ [WHERE c o n d i t i o n ]

◮ if no conditions are provided, then all the rows will be updated

Update Row Example

Example (clear the scores and the votes of all the movies directed before 1997)

UPDATE MOVIE SET SCORE=0, VOTES=0 WHERE (YR<1997)

Delete Row Example

Example (Update the votes and the scores of the movies directed before 1997 such that they have one more vote of 5 points)

UPDATE MOVIE SET SCORE=(SCORE∗VOTES+5)/(VOTES+1) , VOTES=VOTES+1 WHERE (YR<1997)