Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
CS122A, Department of Computer Science Quiz 4
Each question is worth one point.
Suppose that a pharmacy uses a database with two tables as defined below. Example tuples are
Drug (‘ abc’, ‘Advil’, 300) and Supplier (‘ Pfizer’, ’Advil’, 10.9). Drug (did C HAR(3), dname VARCHAR(10), qty INTEGER, PRIMARY KEY(id))
Supplier (sname V ARCHAR(10), dname VARCHAR(10), price REAL, primary key (sname, dname), foreign key (dname) REFERENCES Drug(dname))
Question 1: Relational Algebra
1. If the quantity for a drug is lesser than 10, it needs to be restocked. Write a relational algebra expression to find names of drugs that need to be restocked. Call this resulting
relation Ra .
2. Write a relational algebra expression to find names of suppliers for drugs whose quantity is between 11 and 20 (both inclusive).
3. Give a relational algebra expression to compute names of suppliers that make all the drugs that need to be restocked. You are allowed to use the results Ra from question (1).
4. Are Supplier and Drug relations unioncompatible?
5. What is the degree of “Drug X Supplier” (cross product)?
6. While computing Drug X Supplier, we see these two relations have an attribute with the same name. What operation could you use to resolve this conflict?
Question 2: SQL
1. Write a SELECT SQL query to find names of drugs whose quantity is 30.
2. Write a SQL query to find all distinct drugs with a name of at least 4 characters and starting and ending with ‘M’. Name the result column ‘MCPD’.
3. Write a SQL query to find the names of suppliers for those drugs which have quantity between 11 and 20 (inclusive).