Question

In: Computer Science

Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in...

Consider the following database schema:

LIKE(person, sport),

PRACTICE(person, sport),

where person and sport are keys in both tables. The table LIKE gives the sports a person likes, the table PRACTICE gives the sports a person practices. We assume that a person likes at least one sport and practices at least one sport. We assume also that a person does not like a sport if the sport is not listed among the sports that person likes.

Express the following queries in relational algebra

  1. List the people who practice at list one sport they like
  2. List the people who practice at least one sport they do not like
  3. List pairs of people who practice at least one common sport
  4. List the people who like all the sports they practice
  5. List the people who practice all the sports they
  6. List the people who practice all the sports John

Solutions

Expert Solution

1. LIKE.person LIKE.person = PRACTICE.person ^ LIKE.sport = PRACTICE.sport (LIKE X PRACTICE)
2. person ( (PRACTICE) - (LIKE) )
3. a.person, b.person a.sport = b.sport ^ a.person != b.person (PRACTICE a X PRACTICE b)
// self join practice where sport is same but people different
4. person ( (PRACTICE) (LIKE) )
// whatever they practice is a subset of sports they Like
5. person ( (LIKE) (PRACTICE) )
// whatever they like is a subset of sports they Practice
6. person ( (PRACTICE) (( sport (LIKE) ) ( sport (PRACTICE)) ) )
// Practice relation is divided by all the sports in the union of Like and Practice

venereology answered 1 year ago
Next > < Previous

Related Solutions

Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in...
Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in both tables. The table LIKE gives the sports a person likes, the table PRACTICE gives the sports a person practices. We assume that a person likes at least one sport and practices at least one sport. We assume also that a person does not like a sport if the sport is not listed among the sports that person likes Express the following queries in...
Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in...
Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in both tables. The table LIKE gives the sports a person likes, the table PRACTICE gives the sports a person practices. We assume that a person likes at least one sport and practices at least one sport. We assume also that a person does not like a sport if the sport is not listed among the sports that a person likes Express the following queries...
Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in...
Consider the following database schema: LIKE(person, sport), PRACTICE(person, sport), where person and sport are keys in both tables. The table LIKE gives the sports a person likes, the table PRACTICE gives the sports a person practices. We assume that a person likes at least one sport and practices at least one sport. We assume also that a person does not like a sport if the sport is not listed among the sports that person likes List the people who practice...
Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and...
Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and the one-to-one or one-to-many relationships between the tables. Also describe in a small paragraph the scope of the back-end database, by explaining the different tables and their relations in your schema.
We use the WMCRM database and here is the summary of the database schema (where schema...
We use the WMCRM database and here is the summary of the database schema (where schema is used in its meaning of a summary of the database structure): VEHICLE (InventoryID, Model, VIN) SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate, CommissionRate, OfficePhone, EmailAddress, InventoryID) CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress, NickName) PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType) CONTACT(ContactID, CustomerID,ContactDate,ContactType,Remarks) Where InventoryID in SALESPERSON must exist in InventoryID in VEHICLE NickName in CUSTOMER must exist in NickName in SALESPERSON CustomerID in PHONE_NUMBER...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys...
Consider the following relational schema about a University (the primary keys are underlined and foreign keys are italic) STUDENT(StudentID, name, major, year, age) CLASS(ClassName, meetsAt, room, LecturerID) ENROLLED(StudentID, ClassName, mark) LECTURER(LecturerID, name, DepartmentID) DEPARTMENT(DepartmentID, name) Write the SQL statements for the following query: B1. Find the age of the oldest student. B2. Find the ID’s of lecturers whose name begins with “K” \ B3. Find the age of the youngest student who is enrolled in Mechatronics. B4. Find the age...
Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName,...
Consider the following relational schema (the primary keys are underlined and foreign keys are italic) ITEM(ItemName, ItemType, ItemColour) DEPARTMENT(Deptname, DeptFloor, DeptPhone, Manager) EMPLOYEE(EmpNo, EmpFname, EmpSalary, DeptName, SupervisedBy) SUPPLIER(SupNo, SupName) SALE(SaleNo, SaleQty, ItemName, DeptName) DELIVERY(DeliNo, DeliQty, ItemName, DeptName, SupNo) Write the SQL statements for the following queries: C1. Find the names of items sold on first and second floors. [1 mark] C2. For each department, list the department name and average salary of the employees where the average salary of the...
1. Discuss the elements of a database system. Like The database schema Schema objects Indexes Tables...
1. Discuss the elements of a database system. Like The database schema Schema objects Indexes Tables Fields and columns Records and rows Keys Relationships Data types 2. Discuss the key components of a database management system architecture and how they collaborate. 3. Discuss why a database management system needs a good query optimizer.
Consider the following database schema for a BOOKSTORE database: Books (bookid, title, author, year) Customers (customerid,...
Consider the following database schema for a BOOKSTORE database: Books (bookid, title, author, year) Customers (customerid, name, email) Purchases (customerid, bookid, year) Reviews (customerid, bookid, rating) Pricing (bookid, format, price) The Books relation stores information about books sold by the bookstore. Note that bookid is the primary key. An example tuple is as follows: (105, 'JAVA PROGRAMMING', 'JOHN DOE', 2001) The Customers relation stores information about the customers of the bookstore. Note that customerid is the primary key. An example...
Consider the following universal relation THE following database schema is in 4NF. What can you infer...
Consider the following universal relation THE following database schema is in 4NF. What can you infer about multi-valued dependencies? A C D A B C E E F A-It does not have multi-valued dependencies. B-The multi-valued dependency A ->-> C does not hold. If the multi-valued dependency A ->-> C held, the database would not be in 4NF. C-The multi-valued dependency A ->-> B does not hold. If the multi-valued dependency A ->-> B held, the database would not be...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT