Chapter 9 - Disk Storage and Indexing Structures for Files presents about Disk Storage Devices (Preferred secondary storage device for high storage capacity and low cost, Data stored as magnetized areas on magnetic disk surfaces,.), Files of Records, Indexing Structures for Files. | CSC271 Database Systems Lecture # 9 Summary: Previous Lecture Cartesian product Join Theta join, equijoin, natural join Outer join (left, right, full) Semijoin Division R ÷ S Assume relation R is defined over the attribute set A and relation S is defined over the attribute set B such that B⊆ A (B is a subset of A) Let C= A− B, that is, C is the set of attributes of R that are not attributes of S Division operation defines a relation over the attributes C that consists of set of tuples from R that match combination of every tuple in S Division Division operation can be expressed in terms of basic operations: T1←ΠC(R) T2←ΠC((T1× S) − R) T ← T1− T2 Example: Division Identify all clients who have viewed all properties with three rooms (ΠclientNo, propertyNo(Viewing)) ÷ (ΠpropertyNo(σrooms = 3(PropertyForRent))) Aggregate Operation ℑAL(R) Applies aggregate function list, AL, to R to define a relation over the aggregate list AL contains one or more (, ) pairs Main aggregate functions are: COUNT, SUM, AVG, MIN, and MAX Example: Aggregate Operation How many properties cost more than £350 per month to rent? R(myCount) ℑ COUNT propertyNo (σrent >350 (PropertyForRent)) Find the minimum, maximum, and average staff salary ρR(myMin, myMax, myAverage) ℑ MIN salary, MAX salary, AVERAGE salary (Staff) Grouping Operation GAℑAL(R) Groups the tuples of relation R by the grouping attributes, GA, and then applies the aggregate function list AL to define a new relation AL contains one or more (, ) pairs The resulting relation contains the grouping attributes, GA, along with the results of each of the aggregate functions Example: Grouping Operation Find the number of staff working in each branch and the sum of their salaries ρR(branchNo, myCount, mySum)branchNo ℑCOUNT staffNo, SUM salary(Staff) The Relational Calculus Relational calculus Query specifies what is to be retrieved rather than how to retrieve it Predicate In . | CSC271 Database Systems Lecture # 9 Summary: Previous Lecture Cartesian product Join Theta join, equijoin, natural join Outer join (left, right, full) Semijoin Division R ÷ S Assume relation R is defined over the attribute set A and relation S is defined over the attribute set B such that B⊆ A (B is a subset of A) Let C= A− B, that is, C is the set of attributes of R that are not attributes of S Division operation defines a relation over the attributes C that consists of set of tuples from R that match combination of every tuple in S Division Division operation can be expressed in terms of basic operations: T1←ΠC(R) T2←ΠC((T1× S) − R) T ← T1− T2 Example: Division Identify all clients who have viewed all properties with three rooms (ΠclientNo, propertyNo(Viewing)) ÷ (ΠpropertyNo(σrooms = 3(PropertyForRent))) Aggregate Operation ℑAL(R) Applies aggregate function list, AL, to R to define a relation over the aggregate list AL contains one or more (, ) pairs .
