1 / 19

RELATIONAL DATABASES

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:

tim
Download Presentation

RELATIONAL DATABASES

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 • D1={2,4} and D2={1,3,5} • D1 x D2= {(2,1),(2,3),(2,5),(4,1),(4,3),(4,5)} • R={(2,1),(4,1)} • D1={1,3} D2={2,4} D3={5,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 • {A1:D1,A2:D2,…….An:Dn} is a relation schema • Relation R is a set of n-tuples: • (A1:d1,A2:d2,…..An:dn) such that d1D1, d2D2 …….dnDn • {(Bno: B5, Street: 22 Deer Rd, Area: Sidcup, City: London, Pcode:SW1 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 > 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 1 2 3 1 2 3 a b a a a b b b

  13. Tuples where Renter.Rno = Viewing.Rno •  renter.rno=viewing.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(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 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 London 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

More Related