Introduction to Database Systems

1
Introduction to Database Systems

• CS363/607
• Lecture 7

2
Defining a relation schema in SQL

• Data definition describing the structure of
  information in the database.
• Data manipulation queries and modifications

3
Relations in SQL

• Stored relations tables
• Views defined by a computation
• Temporary tables, constructed by the SQL language
  processor.

4
Data types

• Character strings of fixed varying length.
• CHAR(n) a fixed-length string of n characters.
• VARCHAR(n) a string of up to n characters
• Bit strings of fixed or varying length.
• BIT(n) bit strings of length n
• BIT VARYING(n) bit strings of length up to n.
• BOOLEAN
• Denote an attribute whose value is logical.
• TRUE, FALSE, UNKNOWN

5
Data types (Cont.)

• INT
• INT and INTEGER are synonyms
• SHORTINT the number of bits may be less
• Floating-point numbers
• FLOAT and REAL are synonyms
• DOUBLE PRECISION higher precision
• DECIMAL(n,d) consists of n decimal digits, with
  the decimal point assumed to be d positions from
  the right. E.g. 0123.45 is in DECIMAL(6,2)
• NUMERIC is a synonym for DECIMAL
• Dates and times
• DATE and TIME essentially, they are character
  strings of a special form.

6
Relation schema creation

• Keyword CREATE TABLE
• Example
• CREATE TABLE MovieStar(
• name CHAR(30),
• address VARCHAR(255),
• gender CHAR(1),
• birthdate DATE
• )

7
Relation schema modification

• Delete a relation R
• DROP TABLE R
• Modify the schema of R
• ALTER TABLE R
• Options
• ADD followed by a column name and its data type
• DROP followed by a column name
• MODIFY followed by a column name and the new data
  type
• Example
• ALTER TABLE MovieStar ADD phone CHAR(16)
• ALTER TABLE MovieStar DROP birthdate
• ALTER TABLE MovieStar MODIFY phone VARCHAR(20)

8
Default values

• How to solve the problem that we do not have
  values for a certain attribute?
• SQL provides the NULL value, which becomes the
  value of any component whose value is not
  specified.
• Exception for some columns, NULL is not
  permitted.
• Solution use default values.

9
Syntax

• When declare an attribute and its data type, add
  the keyword DEFAULT and an appropriate value. The
  value is either NULL or a constant. Certain other
  values such as current time, may also be options
• Example
• gender CHAR(1) DEFAULT ?,
• bithdate DATE DEFAULT DATE 0000-00-00
• ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT
  unlisted

10
Declaring primary keys

• A relation may have only one primary key. Two
  ways to declare
• Declare one attribute to be a primary key when
  that attribute is listed in the relation schema.
• Add to the list of items declared in the schema
  an additional declaration that says a particular
  attribute or set of attributes forms the primary
  key.

11
SQL Command

• For 1), the keywords PRIMARY KEY should be added
  after the attribute and its type
• For 2), the keywords PRIMARY KEY and a
  parenthesized list of the attribute or attributes
  that form this key.
• If the key consists of multiple attributes,
  method 2) need to be used.

12
The effects

• After declaring a set of attributes S to be a
  primary key, then
• Two tuples in relation cannot agree on all
  attributes in set S.
• Any attributes in S are not allowed to have NULL
  value.

13
Example

• CREATE TABLE MovieStar (
• name CHAR(30) PRIMARY KEY,
• address VARCHAR(255),
• gender CHAR(1),
• birthdate DATE
• )
• CREATE TABLE MovieStar (
• name CHAR(30),
• address VARCHAR(255),
• gender CHAR(1),
• birthdate DATE,
• PRIMARY KEY (name)
• )

14
Example

• CREATE TABLE Movie (
• title CHAR(100),
• year INT,
• length INT,
• genre CHAR(10),
• studioName, CHAR(30),
• producerC INT,
• PRIMARY KEY (title, year)
• )

15
A Example database schema

• Movie (title string, year integer, length
  integer, incolor boolean, studioName string,
  producerC integer)
• StarsIn (movietitle string, movieyear integer,
  starname string)
• MovieStar (name string, address string, gender
  char, birthday date)
• MovieExec (name string, address string, CERT
  integer, netWorth integer)
• Studio (name string, address string, presC
  integer)

16
What is an Algebra

• Mathematical system consisting of
• Operands --- variables or values from which new
  values can be constructed.
• Operators --- symbols denoting procedures that
  construct new values from given values.

17
What is Relational Algebra?

• A special algebra Consists of some simple but
  powerful ways to construct new relations from
  given relations.
• When the given relations are stored data, then
  the constructed relations can be answers to
  queries about the data.

18
Roadmap

• It is originally proposed by T. Codd as an
  algebra on set of tuples (of relations) to
  express typical queries about those relations.
• Five operations
• union, set difference, cartesian product,
• selection and projection.
• More operations are added later.

19
Basics of relational algebra

• Consists of operators and atomic operands to
  build an expression.
• In relational algebra, the atomic operands are
• Variables that stand for relations
• Constants, which are finite relations.
• All operands and the results of expressions are
  sets.
• Queries expressions of relational algebra.

20
Core Relational Algebra

• Union, intersection, and difference.
• Usual set operations, but require both operands
  have the same relation schema.
• Selection picking certain rows.
• Projection picking certain columns.
• Products and joins compositions of relations.
• Renaming of relations and attributes.

21
Set operations on relations

• R ? S the union of R and S, is the set of
  elements that are in R or S or both.
• R ? S the intersection of R and S, is the set of
  elements that are in both R and S.
• R S the difference of R and S, is the set of
  elements that are in R but not in S. R S is
  different from S R.

22
Conditions

• R and S must have schemas with identical sets of
  attributes, and the types (domains) for each
  attribute must be the same in R and S.
• The columns of R and S must be ordered for both
  relations.

23
Example
Relation R
Relation S
24
Relation R ? S
Relation R ? S
Relation R S
25
Projection

• R1 PROJL (R2)
• L is a list of attributes from the schema of R2.
• R1 is constructed by looking at each tuple of R2,
  extracting the attributes on list L, in the order
  specified, and creating from those components a
  tuple for R1.
• Eliminate duplicate tuples, if any.
• Or R1 pA1,A2,,An (R2).

26
Example

• pA1,A2,,An (R).

27

• p title,year,length (Movie).
• p incolor (Movie).

28
Example
Relation Sells bar beer price Joes Bud 2.5
0 Joes Miller 2.75 Sues Bud 2.50 Sues M
iller 3.00
29
Selection

• R1 SELECTC (R2)
• C is a condition (as in if statements) that
  refers to attributes of R2.
• R1 is all those tuples of R2 that satisfy C.
• Or R1 sC (R2)

30
Example
Relation Sells bar beer price Joes Bud 2.5
0 Joes Miller 2.75 Sues Bud 2.50 Sues M
iller 3.00
31
Another example
slength gt 100 (Movie)
slength gt 100 AND studioNameFox (Movie)
32
Cartesian Product (Product)

• R3 R1 R2
• Pair each tuple t1 of R1 with each tuple t2 of
  R2.
• Concatenation t1t2 is a tuple of R3.
• Schema of R3 is the attributes of R1 and R2, in
  order.
• But beware attribute A of the same name in R1 and
  R2 use R1.A and R2.A.

33
Example R S
R ( A, B ) 1 2 3 4 S( B, C D ) 2 5
6 4 7 8 9 10 11
34
Example R3 R1 R2
R1( A, B ) 1 2 3 4 R2( B, C ) 5 6 7 8 9 10
35
Natural Join

• A frequent type of join connects two relations
  by
• Equating attributes of the same name, and
• Projecting out one copy of each pair of equated
  attributes.
• A tuple that fails to pair with any tuples of the
  other relation in a join is a dangling tuple.
• Called natural join.
• Denoted R3 R1 JOIN R2 (R1 ? R2).

36
Example R ? S
R ( A, B ) 1 2 3 4 S( B, C D ) 2 5
6 4 7 8 9 10 11
37
Example
Sells( bar, beer, price ) Bars( bar, addr
) Joes Bud 2.50 Joes Maple
St. Joes Miller 2.75 Sues River
Rd. Sues Bud 2.50 Sues Coors 3.00
BarInfo Sells JOIN Bars Note Bars.name has
become Bars.bar to make the natural join work.
38
Example
U ( A, B C ) 1 2 3 6 7 8
9 7 8 V ( B, C D ) 2 3
4 2 3 5 7 8 10
39
Theta-Join

• R3 R1 JOINC R2 (R1 ?C R2)
• Take the product R1 R2.
• Then apply SELECTC to the result.
• As for SELECT, C can be any boolean-valued
  condition.
• Historic versions of this operator allowed only A
  theta B, where theta was , lt, etc. hence the
  name theta-join.
• Examples 2.15, 2.16

40
Example
Sells( bar, beer, price ) Bars( name, addr
) Joes Bud 2.50 Joes Maple
St. Joes Miller 2.75 Sues River
Rd. Sues Bud 2.50 Sues Coors 3.00
BarInfo Sells JOIN Sells.bar Bars.name Bars
41
Example

• Natural join operations can be used to combine
  relations.
• Movie1 title, year, length, filmType,
  studioName
• Movie2 title, year, starName
• Query find the stars of movies that are at least
  100 minutes long.

42
Renaming

• The RENAME operator gives a new schema to a
  relation.
• R1 RENAMER2(A1,,An)(R2), ?R2(A1,,An)(R2)
  makes R1 be a relation with attributes A1,,An
  and the same tuples as R2.
• Simplified notation R1(A1,,An) R2.
• Example 2.18

43
Example
Bars( name, addr ) Joes Maple
St. Sues River Rd.
R(bar, addr) Bars
44
Building Complex Expressions

• Algebras allow us to express sequences of
  operations in a natural way.
• Example in arithmetic --- (x 4)(y - 3).
• Relational algebra allows the same.
• Three notations, just as in arithmetic
• Sequences of assignment statements.
• Expressions with several operators.
• Expression trees.

45
Sequences of Assignments

• Create temporary relation names.
• Renaming can be implied by giving relations a
  list of attributes.
• Example R3 R1 JOINC R2 can be written
• R4 R1 R2
• R3 SELECTC (R4)

46
Expressions in a Single Assignment

• Example the theta-join R3 R1 JOINC R2 can be
  written R3 SELECTC (R1 R2)
• Precedence of relational operators
• Unary operators --- select, project, rename ---
  have highest precedence, bind first.
• Then come products and joins.
• Then intersection.
• Finally, union and set difference bind last.
• But you can always insert parentheses to force
  the order you desire.

47
Expression Trees

• Leaves are operands --- either variables standing
  for relations or particular, constant relations.
• Interior nodes are operators, applied to their
  child or children.

48
Example

• Using the relations Bars(name, addr) and
  Sells(bar, beer, price), find the names of all
  the bars that are either on Maple St. or sell Bud
  for less than 3.

49
As a Tree
Bars
Sells
50
Example

• Using Sells(bar, beer, price), find the bars that
  sell two different beers at the same price.
• Strategy by renaming, define a copy of Sells,
  called S(bar, beer1, price). The natural join of
  Sells and S consists of quadruples (bar, beer,
  beer1, price) such that the bar sells both beers
  at this price.

51
The Tree
Sells
Sells
52
Example

• Query what are the titles and years of movies
  made by Fox that are at least 100 minutes long?
• Solution
• 1. select those movies tuples that length gt 100
• 2. select tuples that have studioname Fox
• 3. compute the intersection of 1 and 2
• 4. project from 3 onto attributes title and year.

53
Dependent and independent operations

• R ? S R ( R S)
• R ?c S sc ( R ? S )
• R ? S p L ( s c ( R ? S ) )
• Example 2.19
• U(A,B,C), V(B,C,D)
• U ? V ?
• p A, U.B, U.C, D ( s U.BV.B AND U.CV.C ( U ? V
  ) )
• U ?AltD AND U.B?V.B V ?
• s AltD AND B?V.B ( U ? V )

54
A linear notation for algebraic expressions

• Invent names for the temporary relations that
  correspond to the interior nodes of the tree and
  write a sequence of assignments that create a
  value for each.
• The notations are
• A relation name and parenthesized list of
  attributes for that relation. The name Answer
  will be used for the final step.
• The assignment symbol
• Any algebraic expression on the right.
• Example 2.20

55
Exercise

• 2.4.1
• 2.4.2
• 2.4.5
• 2.4.7

56
Relational algebra as a constraint language

• Two ways to express constraints
• R Ø the value of R must be empty
• R ? S every tuples in R must also be in S.

57
Referential integrity constraints

• If we have a value v in a tuple of one relation
  R, then we expect that v will appear in a
  particular component of some tuple of another
  relation S.

58
Example

• Movie (title, year, length, incolor, studioName,
  producerC)
• MovieExec(name, address, cert, netWorth)
• p producerC(Movie) ? p cert(MovieExec)
• p producerC(Movie) - p cert(MovieExec) ?
• StarsIn(movieTitle, movieYear, starName)
• Movie (title, year, length, incolor, studioName,
  producerC)
• pmovieTitle, movieYear(StarsIn) ? p
  title,year(Movie)

59
Additional constraint examples

• MovieStar(name, address, gender, birthday)
• FD name ? address
• sMS1.nameMS2.name AND MS1.address?MS2.address(?
  MS1(MovieStar) ? ? MS2(MovieStar) ) ?
• The legal values for gender are F and M.
• sgender?F AND gender?M (MovieStar) ?

60
For other constraints

• Example
• 10,000,000 requirement for the president of a
  movie studio.
• it can not be classified as a domain, single
  value or referential integrity constraint.
• MovieExec(name, address, cert, netWorth)
• Studio(name, address, presC)
• snetWorthlt10000000(Studio ?presCcert
  MovieExec) ?
• p prec(Studio) ? pcert (s netWorth10000000(M
  ovieExec))

61
Exercise

• 2.5.1 a) b) c)