Database Management Systems Relational Model Sule ¨ H. Turgut Uyar ¸ 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 ... TITLE ... Usual Suspects ... Suspiria ... Suspiria ... Being John Malkovich ... Being John Malkovich ... Usual Suspects ...
Order of Attributes ◮ attributes are unordered Example these two relations have no difference: TITLE YR ... YR TITLE ... Usual Suspects 1995 ... 1995 Usual Suspects ... Suspiria 1977 ... 1977 Suspiria ... Being John Malkovich 1999 ... 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 of 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 does not have a value for tuple is unknown that attribute Example Example the director of“Blade”is unknown 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 relation body ◮ set of attributes that create ◮ set of tuples in a relation the relation ◮ effected by data ◮ stated when the relation is manipulation language being created commands ◮ effected by data definition 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)
Recommend
More recommend
