RELATIONAL%20DATABASES

1
RELATIONAL DATABASES
2
Relational data Structure

• RELATION Table with columns and rows
• ATTRIBUTE Column of a relation
• DOMAIN Set of allowable values for one or more
  attributes
• TUPLE Row of a relation
• DEGREE of a relation Number of attributes
• CARDINALITY of a relation
• Number of tuples it contains
• Relational Database A collection of normalized
  relations

Fig 3.1 pg 75 Fig 3.2 pg 76
3
Alternate terminology for relational model terms

• Formal Terms Alternative 1 Alternative 2
• Relation Table File
• Tuple Row Record
• Attribute Column Field

4
Mathematical Relations

• D12,4 and D21,3,5
• D1 x D2 (2,1),(2,3),(2,5),(4,1),(4,3),(4,5)
• R(2,1),(4,1)
• D11,3 D22,4 D35,6
• D1 x D2 x D3
• (1,2,5),(1,2,6),(1,4,5),(3,2,5),(3,2,6),(3,4,5),(
  3,4,6)

5
DATABASE RELATIONS

• Relation Schema A relation name followed by a
  set of attribute and domain name pairs.
• A1,A2,..An be attributes with domains
  D1,D2,.Dn
• A1D1,A2D2,.AnDn is a relation schema
• Relation R is a set of n-tuples
• (A1d1,A2d2,..Andn) such that d1?D1, d2?D2
  .dn?Dn
• (Bno B5, Street 22 Deer Rd, Area Sidcup,
  City London, PcodeSW1 4EH, Tel_No
  0171-886-3838, Fax_No 0171-886-3272

6
Properties of Relations

• Relation has a name that is distinct
• Each cell of the relation contains exactly
  (single) value
• Each attribute has a distinct name
• The values of the attribute form the same domain
• The order of the attributes have no significance
• Each tuple is distinct no duplicate tuples
• The order of the tuples has no significance
• Relations do not contain repeating groups
• two telephone nos for a single branch office in a
  single cell.
• First Normal Form

7
Relational Keys

• Superkey An attribute or set of attributes that
  uniquely identifies a tuple within a relation
• Candidate Key A superkey such that no proper
  subset is a superkey within the relation
• several candidate keys for a relation.
• Composite Key when a key consists of more than
  one attribute.
• Primary Key The candidate key that is selected
  to identify tuples uniquely within the relation
• Alternate key The candidate keys that are not
  selected to be the primary key
• Foreign Key An attribute or set of attributes
  within on relation that matches the candidate key
  of some (possibly the same) relation

8
Representing Relational Database Schemas

• A relational database consists of a number of
  relations.
• Relational schemas
• Branch (Bno, Street, Area, City, Pcode,
  tel_No, fax_No)
• Staff (Sno, Fname, Lname, Address, tel_No,
  Position, Sex, DOB, Salary, NIN, Bno)
• Property_for_Rent Pno, Street, Area, City, Pcode,
  Type, Rooms, Rent, Ono,
• Sno, Bno)
• Renter (Rno, Fname, Lname, Address, Tel_No,
  Pref_Type,
• Max_Rent, Bno)
• Owner (Ono, Fname, Lname, Address, Tel_No)
• Viewing (Rno, Pno, Date, Comment)

Fig 3.3 -- pg83
9
Relational Integrity

• Null
• Value currently unknown or not applicable
• Entity Integrity
• No attribute of a primary key can be null
• Referential Integrity
• Foreign key value must match a candidate key
  value
• Enterprise constraints
• Additional rule specified

10
Selection Operation

• List all staff with a salary greater than 10,000
• ?salary gt 10000 (Staff)
• logical operators ? (AND), ? (OR) and (NOT)

11
Projection Operation

• List of salaries for all staff, showing only Sno,
  Fname, Lname and Salary details
• ?sno, fname, lname, salary (Staff)

Sno Fname Lname Salary SL21 John White
30000 SA25 Rene Howe 30000 SF31 Al Lee
30000 S55 Mary Ann 30000
12
Cartesian Product Operation

• (? rno, fname, lname (Renter)) x (?rno,
  no,comment(Viewing))

R S R x S
1 2 3
a b
a a a b b b
1 2 3 1 2 3
13

• Tuples where Renter.Rno Viewing.Rno
• ? renter.rnoviewing.rno ((? rno, fname, lname
  (Renter)) x (?rno, no,comment(Viewing)))
• Renter.Rno Fname Lname Viewing.Rno Pno Comment
• CR76 John Kay CR76 PG4 too remote
• CR79 Rick Bay CR79 PG4
• CR88 Molly Holly CR88 PG4 too small
• CR96 Bob Ray CR96 PG4 no dining room

14
Union Operation

• Construct a list of all areas where there is
  either a branch or a property
• ?area (Branch) ? ?area(Property_for_rent)

Area Sidcup Dyce Patrick Leigh dee Kilbern Hynland
15
Set Difference Operation

• Construct a list of all cities where there is a
  branch office but no properties
• ?city (Branch) - ?city(Property_for_rent)

City Bristol
16
Natural Join Operation

• List the names and comments of all renters who
  have viewed a property
• ?rn,fname,lname(Renter) ?rno,pno,comment(Viewin
  g)
• Renter.Rno Fname Lname Viewing.Rno Pno Comment
• CR76 John Kay CR76 PG4 too remote
• CR79 Rick Bay CR79 PG4
• CR88 Molly Holly CR88 PG4 too small
• CR96 Bob Ray CR96 PG4 no dining room

T U
A B C a 1 x a 1 y
17
Left Outer Join Operation

• Produce a status report on property listings
• ?rpno,street,city(Property_for_Rent) Viewing

Pno Street City Rno Date Comment PA14 16Holhead
Aberdeen CR56 24-May-98 too small PA14 16Holhead
Aberdeen CR62 14-May-98 no dining PL94 6Argyll Lon
don null null null PG4 Lawrence
St. Glasgow CR76 20-Apr-98 too remote PG4 Lawrence
St. Glasgow CR56 27-May-98 PG36 2Manon Rd
Glasgow CR56 28-Apr-98
18
Views

• View A dynamic result of one or more relational
  operation to produce another relation
• A view is a relation, but does not exist in
  storage
• Views are dynamic

19
Purpose of Views

• Hiding parts of the database from some users
• Staff records without Salary attribute
• Same data can be seen by different users in
  different ways at the same time
• A view may be a join of two relations
• Order of columns may be changed