chapter 2 relational data model-part 2

RELATIONAL DATA MODEL PART 2 (Additional Notes)

DFC2033 Database System

Course Learning Outcome

¨  CLO1 : Explain the fundamentals concepts of database management and relational data model to create a database based on an organization’s requirements.

¨  CLO3 : Solve an organization’s requirements by selecting the correct database query formulation using an appropriate commercial Database Management System (DBMS).

FP 304 – Database System

Learning Outcome

¨  Use operators of relational algebra.

¨  Identify the fundamental operators used to retrieve information from a relational database.

¨  Describe the purpose and input of each of the operators.

¨  Use the operators to write the expressions based on given relational tables.

¨  Show the output of the fundamental operators based on a given database.

¨  Define the traditional set of operators for relational tables.

¨  Draw the Venn diagrams for traditional set operators.

¨  Define union compatibility.

¨  Apply the union, intersect, and difference set operators to two given relational tables.


Relational Algebra

Relational Algebra is : ¨  The formal description of how a relational database operates

¨  An interface to the data stored in the database itself

¨  The mathematics which underpin SQL operations

¨  Operators in relational algebra are not necessarily the same as SQL operators, even if they have the same name. For example, the SELECT statement exists in SQL, and also exists in relational algebra. These two uses of SELECT are not the same.

¨  The DBMS must take whatever SQL statements the user types in and translate them into relational algebra operations before applying them to the database.


Restrict (select)

¨  SELECT operation is used to select a subset of the tuples from a relation that satisfy a selection condition.

¨  It is a filter that keeps only those tuples that satisfy a qualifying condition – those satisfying the condition are selected while others are discarded.

¨  Symbol : ¤  σ (sigma) used to denote the SELECT operator

¤  (R) denote relation name.

¨  Syntax :

¤ σ<selection condition > ( R )

¤  Normally combine with {=,<,>, <=, >=, =}


Restrict (select)

¨  Symbol : ¤  σ (sigma) used to denote the SELECT operator ¤  (R) denote relation name.

¨  Syntax :

¤ σ<selection condition>( R ) ¤  Normally combine with {=,<,>, <=, >=}

¨  May combine with Boolean Operator: ¤  AND

¤  OR ¤  NOT


Restrict (select)

¨  Example: ¤ To select the EMPLOYEE tuples whose department

number is four ¤ Select salary is greater than $30,000 the following

notation is used:


σDNO = 4 (EMPLOYEE)

σSALARY > 30,000 (EMPLOYEE)

¨  Select the tuples for all employees who either work in department 4 and make over RM2500 per year, or work in department 1 and make over RM3000.


Restrict (select)

σ(Dno=4 AND Salary>2500) OR (Dno=1 AND Salary>3000)(EMPLOYEE)

Example

Table : EMP_RESULT


EMP_RESULT ß σ(Dno=4 AND Salary>2500) OR (Dno=1 AND Salary>3000)(EMPLOYEE)

Project

¨  Project operation selects certain columns from the table and discards the other columns.

¨  The PROJECT creates a vertical partitioning – one with the needed columns (attributes) containing results of the operation and other containing the discarded Columns.

¨  Symbol -: Л (pi) ¨  Syntax : Л <attribute list> (R)


Example

¨  List each employee’s staff number first, last name and salary.

STAFF_NEW ß Л staffNo,fname,lname,salary(STAFF)


Example FP 304 – Database System

Table : STAFF_NEW

Project and select operators

¨  We can combine project with select operators when we want to select certain column that specify specific conditions.

¨  Example: ¤ Select student name and address for people who live in

Seremban. ¤ First : Select from temporary table who live in

Seremban ¤ Select : List name and address à temporary table


Example

Retrieve the first name, last name, salary and branch number of all employees who work in branch B003. The sequence is :

TEM_STAFF ßσbranchNo=B003(STAFF) RESULT ß ЛFname,Lname,Salary,branchNo(TEMP_STAFF)


Example

TEM_STAFF ßσbranchNo=B003(STAFF) RESULT ß ЛFname,Lname,Salary,branchNo(TEMP_STAFF)

You can also combine the operation as below: ЛFname,Lname,Salary,branchNo(σbranchNo=B003(STAFF))


Exercise

¨  Using relational algebra ¤  Select Employee whose test score is greater than 91. ¤  List all the employee number, test code and score for test record. ¤  List all the test number, test code for employee number 110.


Table : Employee

Cross product

¨  This operation is used to combine tuples from two relations in a combinatorial fashion.

¨  Concatenation of every tuple of relation R with every tuple of relation S.

¨  Denoted by X ¨  R X S ¨  Syntax: (Л <attributelist>(R))X (Л <attributelist>(S))


Example

ClientNo fName lname

CR76 John Kay

CR56 Aline Stewart

CR74 Mike Ritchie

CR62 Mary Tregear

ClientNo propertyNo comment

CR56 PA14 Too small

CR76 PG4 Too remote

CR56 PG4

CR62 PA14 No dining room

CR56 PG36

TABLE: CLIENT TABLE: VIEWING

TABLE: RESULT


TABLE: RESULT


RESULT ß(Л clientNo, fName, lName(Client)) X ((Л clientNo, propertyNo, comment (Viewing))

RESULT

Join

¨  Two relations R and S over all common attributes. ¨  One occurrence of each common attribute is eliminated from

the result. ¨  Symbol : ¨  Syntax:(Л <attributelist>(R)) (Л <attributlist> (R)) ¨  Types of Join:

¤  Inner Join ¤  Outer Join ¤  Theta Join ¤  Natural Join ¤  Equi Join ¤  Semi Join


Please Find the definition

Inner join

¨  An INNER JOIN produces a result table containing composite rows created by combining rows from two tables where some join condition evaluates to true.

¨  Rows that do not satisfy the join condition will not appear in the result table of an inner join.

¨  The inner join is the default join type, therefore the keyword INNER is optional and may be omitted.


ClientNo fName lname

CR76 John Kay

CR56 Aline Stewart

CR74 Mike Ritchie

CR62 Mary Tregear

ClientNo propertyNo comment

CR56 PA14 Too small

CR76 PG4 Too remote

CR56 PG4

CR62 PA14 No dining room

CR56 PG36

TABLE: client TABLE: VIEWING


Example

Example

Client.ClientNo fname lname comment.ClientNo propertyNo comment

CR76 John Kay CR76 PG4 Too remote

CR56 Aline Stewart CR56 PA14 Too small

CR56 Aline Stewart CR56 PG4

CR56 Aline Stewart CR56 PG36

CR62 Mary Tregear CR62 PA14 No dining room

Result ß (Л clientNo, fName, lName(Client)) (Л clientNo, propertyNo, comment (Viewing))

TABLE: Result


Viewing.

Outer join

¨  There are two types of outer join: ¤ Left Outer Join ¤ Right Outer Join


Left outer join

¡  The left outer join operation keeps every tuple in the first or left relation R in R and S;

¡  If no matching tuple is found in S, then the attributes of S in the join result are filled with null values.

¡  Symbol :


Left outer join

Result ß (Л P.PROJNO, PPROJNAME, DEPNO(Project)) Department


Null Null

Right outer join

¡  A similar operation with left outer join, right outer join, keeps every tuple in the second or right relation S in the result of R and S.

¨  Symbol :


Right outer join

Result ß (Л P.PROJNO, PPROJNAME, DEPNO(Project)) Department)


Null Null Null

Traditional set operator from set theory

¨  There are 3 traditional set operator : ¤ Union ¤  Intersection ¤ Difference

Union

¨  Relation that includes all tuples that are either in R or in S or in both R and S.

¨  Table must have the same number of attribute. ¨  Duplicate tuples are eliminated. ¨  Symbol : ∪ ¨  Syntax : Л <attribute list>(R) ∪ Л <attribute list>(R)


Union FP 304 – Database System

Union List all cities where there is in branch office or in property for rent.

Result1 ß Л city(Branch) Result2 ß Л city(PropertyForRent) Result_Union ß Result1 ∪ Result2



Result_Union ß Л city(Branch) ∪ Л city(PropertyForRent)

Result

Union compatibility

¡  The operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes, and the domains of corresponding attributes must be compatible; that is, dom(Ai)=dom(Bi) for i=1, 2, ..., n.

¡  In other words, two relations are union compatibles if they

have the same degree and the ¡  same number attribute ¡  and domains


Union compatibility

¨  The relations below is said to be union compatibility relation


Intersection

¨  Only those tuples that appear in both of the named relation are given as an output result.

¨  The two operand must be type compatible. ¨  Symbol : ∩ ¨  R ∩ S ¨  Syntax: Л <attributelist>(R) ∩ Л <attributelist>(R)


Intersection

TION Example


Intersection

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

¨  Л city(Branch) ∩ Л city(PropertyForRent)


Difference

¨  Subtracts from the first named relation those tuples that appear in the second named relation and create a new relation.

¨  The two operand must be type compatible. ¨  Symbol : - (minus) ¨  Syntax: Л <attributelist>(R) – Л<attributelist>(R)


Difference FP 304 – Database System

Difference

¨  List all cities where there is a branch office but no properties for rent.

Л city(Branch) – Лcity(PropertyForRent)


chapter 2 relational data model-part 2

Education