Question

The following is about databases: queries on relational algebra

Consider the following schemas for a LinkedIn-like professional network. Note this is a slightly simplified version from your last assignment. Assume that the connections are mutual.

Person (PID, first_name, last_name, age, gender)

School (school_ID, school_name, type, city, state)

Education (PID, school_ID, degree, major, year)

Company (company_ID, company_name, total_asset, headquarter)

Branch (company_ID, branch_num, city, state)

WorksAt (PID, company_ID, branch_num, start, end)

Connection (PID, friend_ID)

For each relation, the attribute(s) of the primary key is(are) underlined. In addition the following foreign key constraints hold:

In Education, PID is a foreign key that references Person(PID), and school_ID is a foreign key that references School(school_ID)

In Branch, company_ID is a foreign key that references Company(company_ID)

In WorksAt, PID is a foreign key that references Person(PID); (company_ID, branch_num) is a foreign key that references Branch(company_ID, branch_num)

In Connection, PID is a foreign key that references Person(PID); friend_ID is a foreign key that references Person(PID)

The Connection relation stores pairs of users who are connected.

Write the following queries in Relational Algebra. Note “dsmith” is a unique PID. Tips: Break a problem into smaller parts and use temporary relations.

1. Print the names of all alumni of George Mason University (school_ID = ‘GMU’), along with their degrees/majors/years.
2. Print the names (first name, last name) of all dsmith’s connections.
3. Print the names of users who have no connection in the network.
4. Print the names of users who studied different subjects for their BS and MS degrees
5. Print the names of schools that no user in the network has attended.
6. Find the names of dsmith’s friends who graduated from the same college that he got his BS degree from.
7. Find the names of users who have worked at all branches of company “ABC Software.”
8. Print all users and the schools they graduated from, with the corresponding degrees/majors/years. It’s possible for a person to have multiple entries (if he/she has multiple degrees). If a person does not have any education history, he/she should also be included in the list, with “null” values placed in the relevant fields.

Answer 1

Note: We are required to do the first four parts of the question if no particular ones are specified from multiple parts. So I would be doing the same.

Print the names of all alumni of George Mason University (school_ID = ‘GMU’), along with their degrees/majors/years.

Π_{first_name, last_name, degree, major, year} (σ_{school_ID = ‘GMU’}(Person ⋈ Education))

Print the names (first name, last name) of all dsmith’s connections.

Π_{first_name, last_name} (σ_{Connection.PID = 'dsmith' ​​​​​}(Person ⋈_{Person.PID = Connection.friend_ID} Connection))

Print the names of users who have no connection in the network.

Π_{first_name, last_name} (Person ▷ Connection)

Print the names of users who studied different subjects for their BS and MS degrees

Π_{first_name, last_name} (Person ⋉ (ρ_e((ρ_e1 (σ_{degree = 'MS'} (Education))) ⋈_{e1.PID=e2.PID ^ e1.major <> e2.major} (ρ_e2 (σ_{degree = 'BS'} (Education))))))