If possible provide answer using dummy values. 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. Print the names of schools that no user in the network has attended. Find the names of dsmith’s friends who graduated from the same college that he got his BS degree from. Find the names of users who have worked at all branches of company “ABC Software.” 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.
If possible provide answer using dummy values.
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.
Print the names of schools that no user in the network has attended.
Find the names of dsmith’s friends who graduated from the same college that he got his BS degree from.
Find the names of users who have worked at all branches of company “ABC Software.”
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.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
