Suppose we are given a database with the following schema. Users User IDINTEGER,

Question

Suppose we are given a database with the following schema. Users User IDINTEGER, Name CHAR(30), Age INTEGER, ReviewCount INTEGER) Businesses (BusinessID INTEGER, BName CHAR(30), City CHAR(20), State CHAR(2) Checkins (BusinessIDINTEGER, Weekdays INTEGER, Weekends INTEGER) Reviews (ReviewID INTEGER, UserIDINTEGER, BusinessID INTEGER, Stars REAL) Reviews (UserID) is a foreign key referring to Users (UserID). Reviews (BusinessID) is a foreign key referring to Businesses (BusinessID). Checkins (BusinessID) is a foreign key referring to Businesses (BusinessID) A page is 8 kB in size. The RDBMS buffer pool has 10,000 pages, all of which are usable. Initially, the buffer pool is empty. The relation instances have the following statistics. Assume there are no NULL values. Each integer or real is 8B, and each character is 1B (so as an example CHAR(20) is 20B). Additionally, the record id of each tuple is 8B. Relation Number of Pages Number of Tuples 75,684 10m. Users 41,504 5mm Businesses 19,532 5mm Checkins Reviews 488,282 100m

Explanation / Answer

Relational Algebra:

Relations are seen as sets of tuples, which means that no duplicates are allowed. We can use some basic set theory.

Review of concepts and operations from set theory

Projection: It is a unary operation.

Input to projection: Relation and a list of attributes of that relation as input.

Output of Projection: Relation containing the specified list of attributes with duplicate tuples removed.

// Projection is equivalent to SELECT DISTINCT in SQL.

Operation

My HTML

Symbol

Projection

PROJECT

Selection: It returns those tuples in a relation that fulfil a condition.

// Selection is equivalent to SELECT in SQL.

Selection

SELECT

Relational algebra Query:

B Name (Stars > 4, ReviewCount >=100(( Users Reviews) Businesses))

The queries for the given data:

Select Bname from Businesses INNER JOIN   Users ON Review .UserID = User.UserID INNER JOIN Reviews ON Businesses .UserID = Review .UserID where Stars > 4 and ReviewCount >= 100;

INNER JOIN – returns all rows from multiple tables where the join condition is met.

Operation

My HTML

Symbol

Projection

PROJECT

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.