RELATIONAL DATABASES Relational data Structure RELATION Table with

RELATIONAL DATABASES

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

Alternate terminology for relational model terms Formal Terms Relation Tuple Attribute Alternative 1 Table Row Column Alternative 2 File Record Field

Mathematical Relations • D 1={2, 4} and D 2={1, 3, 5} • D 1 x D 2= {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} • R={(2, 1), (4, 1)} • D 1={1, 3} D 2={2, 4} D 3={5, 6} • D 1 x D 2 x D 3 = – {(1, 2, 5), (1, 2, 6), (1, 4, 5), (3, 2, 6), (3, 4, 5), (3, 4, 6)}

DATABASE RELATIONS • Relation Schema: A relation name followed by a set of attribute and domain name pairs. • A 1, A 2, …. . An be attributes with domains D 1, D 2, …. Dn • {A 1: D 1, A 2: D 2, ……. An: Dn} is a relation schema • Relation R is a set of n-tuples: • (A 1: d 1, A 2: d 2, …. . An: dn) such that d 1 D 1, d 2 D 2 ……. dn Dn • {(Bno: B 5, Street: 22 Deer Rd, Area: Sidcup, City: London, Pcode: SW 1 4 EH, Tel_No: 0171 -886 -3838, Fax_No: 0171 -8863272}

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

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

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

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

Selection Operation • List all staff with a salary greater than $10, 000 • salary > 10000 (Staff) • logical operators (AND), (OR) and ~ (NOT)

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 SL 21 John White 30000 SA 25 Rene Howe 30000 SF 31 Al Lee 30000 S 55 Ann 30000 Mary

Cartesian Product Operation • ( rno, fname, lname (Renter)) x ( rno, comment(Viewing)) R a S 1 Rx. S a 1 b 2 a 2 3 a 3 b 1 b 2 b 3

• Tuples where Renter. Rno = Viewing. Rno • renter. rno=viewing. rno (( rno, fname, lname (Renter)) x ( rno, comment(Viewing))) Renter. Rno Fname Lname Viewing. Rno Pno CR 76 CR 79 CR 88 CR 96 Kay Bay Holly Ray John Rick Molly Bob CR 76 CR 79 CR 88 CR 96 PG 4 Comment too remote too small no dining room

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

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

Natural Join Operation • List the names and comments of all renters who have viewed a property • rn, fname, lname(Renter) rno, pno, comment(Viewing) Renter. Rno Fname Lname Viewing. Rno Pno Comment CR 76 CR 79 CR 88 CR 96 John Rick Molly Bob too remote Kay Bay Holly Ray CR 76 CR 79 CR 88 CR 96 T U A B C a 1 x a 1 y PG 4 too small no dining room

Left Outer Join Operation Pno • Produce a status report on property listings • rpno, street, city(Property_for_Rent) Viewing Street City Rno Date Comment PA 14 16 Holhead Aberdeen CR 56 24 -May-98 too small PA 14 16 Holhead Aberdeen CR 62 14 -May-98 no dining PL 94 6 Argyll London null PG 4 Lawrence St. Glasgow CR 76 20 -Apr-98 PG 4 Lawrence St. Glasgow CR 56 27 -May-98 PG 36 2 Manon Rd Glasgow CR 56 28 -Apr-98 too remote

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

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