slide1
Download
Skip this Video
Download Presentation
Class Will Start Momentarily…

Loading in 2 Seconds...

play fullscreen
1 / 32

Class Will Start Momentarily… - PowerPoint PPT Presentation


  • 157 Views
  • Uploaded on

Relational Model & Relational Algebra. IS8080 – Data Application, Design and Implementation . Class Will Start Momentarily…. Relational Databases (Codd, 70). Structural Aspect: viewed as Relations (Tables) (Physical structure hidden from user).

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Class Will Start Momentarily…' - mariko


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1
Relational Model & Relational Algebra

IS8080 – Data Application, Design and Implementation

  • Class
      • Will
          • Start
          • Momentarily…
relational databases codd 70
Relational Databases (Codd, 70)
  • Structural Aspect: viewed as Relations (Tables) (Physical structure hidden from user).
  • Integrity Constraints: Relations satisfy certain integrity constraints.
  • Manipulative Aspect: Set of Operators that derive a relation from relations (relational algebra, relational calculus).
terminology
Terminology
  • Relation = Table
  • Tuples = Rows
  • Cardinality = Number of Rows of a Relation
  • Degree = Number of Columns of a Relation
  • Attribute = named column of a relation.
  • Domain = set of allowable values for one or more attributes.
  • Relational Database = collection of normalized relations with distinct relation names.
instances
Instances

Instances of Branch and Staff (part) Relations

database relations
Database Relations

Relation is a table that has no multi-valued attributes.

For example,

Employee [eid, ename, salary, skills]

Is a Table, but it is not a relationship because skills is a multi-valued attribute

slide6
Keys
  • Candidate Key = UNIQUE, NOT NULL
    • Attribute or set of attributes that is unique within a table (relation).
  • Primary Key ****
    • Candidate key selected to identify tuples (rows) uniquely within relation (table).
  • Foreign Key ****
    • Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.
    • Null = unknown value != 0
integrity constraints
Integrity Constraints
  • Entity Integrity
    • In a base relation, no attribute of a primary key can be null.
  • Referential Integrity
    • If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null.
  • Enterprise Constraints
    • Additional rules specified by users or database administrators.
views
Views
  • View

Virtual Table

Example: Query in MS-Access

Provides simplicity and security

Will be discussed further in Chapter 6

relational algebra and relational calculus
Relational algebra and Relational calculus
  • Relational algebra and relational calculus are formal languages associated with the relational model.
  • Both are equivalent to one another.
  • A language that produces a relation that can be derived using relational calculus is relationally complete.
restriction or selection
Restriction (or Selection)
  • Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate).

Example: List all staff with a salary greater than US$10,000.

salary > 10000 (Staff) -- RA Codd notation

R = STAFF Where Salary > 10000 -- RA Dr. G notation

SELECT * FROM Staff Where Salary > 10000; -- SQL notation

projection
Projection
  • col1, . . . , coln(R)
    • Works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.
  • Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details.

staffNo, fName, lName, salary(Staff) -- Codd notation

Staff [staffNo, fName, lName, salary] -- Dr. G notation

SELECT staffNo, fName, lName, salary FROM Staff; -- SQL notation

union
Union
  • R  S
    • Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated.
    • R and S must be union-compatible.
  • If R and S have I and J tuples, respectively, union is obtained by concatenating them into one relation with a maximum of (I + J) tuples.

Example: List all cities where there is either a branch office or a property for rent.

  • Pcity(Branch) union Pcity(PropertyForRent) -- Codd notation
  • R = Branch[city] union PropertyForRent[city] -- Dr. G. notation
  • SELECT pcity FROM Branch UNION SELECT pcity FROM PropertyForRent; -- SQL notation
difference minus
Difference (Minus)
  • R – S
    • Defines a relation consisting of the tuples that are in relation R, but not in S.
    • R and S must be union-compatible.
  • List all cities where there is a branch office but no properties for rent.

city(Branch) – city(PropertyForRent)

Or R = Branch [city] - PropertyForRent [city]

SELECT city FROM Branch MINUS SELECT city FROM PropertyForRent; -- SQL notation

intersection
Intersection
  • R  S
    • Defines a relation consisting of the set of all tuples that are in both R and S.
    • R and S must be union-compatible.
  • Expressed using basic operations:

R  S = R – (R – S)

Example: List all cities where there is both a branch office and at least one property for rent.

city(Branch) city(PropertyForRent)

SELECT city FROM Branch MINUS SELECT city FROM PropertyForRent; -- SQL notation

cartesian product multiplication
Cartesian Product (Multiplication)
  • R X S
    • Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.
  • List the names and comments of all clients who have viewed a property for rent.

(clientNo, fName, lName(Client)) X (clientNo, propertyNo, comment (Viewing)) -- Codd

Client [clientNo, fName, lName] x Viewing [clientNo, propertyNo, comment ] -- Dr. G notation

SELECT clientNo, fName, lName, clientNo, propertyNo, comment FROM Client, Viewing; -- SQL

slide18
Join
  • Join is a derivative of Cartesian product.
  • It contains a restriction and a Cartesian Product
  • One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems.
slide19
Join
  • Join is a derivative of Cartesian product.
  • Equivalent to performing a Selection, using join predicate as selection formula, over Cartesian product of the two operand relations.
  • One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems.
  • Various forms of join operation
    • Natural join (defined by Codd)
    • Outer join
    • Theta join
    • Equijoin (a particular type of Theta join)
    • Semijoin
natural join
Natural Join
  • List the names and comments of all clients who have viewed a property for rent.
  • 1) (clientNo, fName, lName(Client)) Join (clientNo, propertyNo, comment(Viewing)) 2) Client [clientNo, fName, lName] Join Viewing [clientNo, propertyNo, comment ]
  • 3) SELECT clientNo, fName, lName, clientNo, propertyNo, comment FROM Client, Viewing

WHERE CLIENT.clientNo = Viewing.ClientNo

outer join
Outer Join
  • To display rows in the result that do not have matching values in the join column, use Outer join.
  • R Left Outer Join S
    • (Left) outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation.

Example:

  • Produce a status report on property viewings.

propertyNo, street, city(PropertyForRent)

Left Outer Join Viewing

  • SELECT propertyNo, street, city, ClientNo, ViewDate, Comment FROM Client, Viewing

WHERE CLIENT.clientNo = Viewing.ClientNo (+);

division
Division
  • Identify all clients who have viewed all properties with three rooms.

(clientNo, propertyNo(Viewing))  (propertyNo(rooms = 3 (PropertyForRent))) -- codd notation

Viewing [clientNo, propertyNo] / PropertyForRent [propertyNo]

more relational algebra and sql
More Relational Algebra and SQL
  • I) Go to SQL and Relational Algebra
  • II) Go to Database Courseware
        • SQL (module)

Interactive SQL (sub-module)

  • III) WinRDBI from Arizona State
  • IV) www3 Schools
  • V) SQL Tutorial
assignments
Assignments
  • Assingment # 0 – should be done with it
  • Assignment # 1 – Due next Tuesday
  • Assignment # 2 – Due next Thursday
  • Extra-credit assignment
    • Evaluate DB coursewares – will send specifications (post on Moodle) on Friday
end of lecture
End of Lecture

End

Of

Today’s

Lecture.

ad