4.1 Let S = {red, yellow, green} and T = {plaid, stripe, dot}. Find the Cartesian product of S and T.
4.2 Let Q = {Tom, Mary, Jim} and R = {art, biology}. Create a relational table instance with Q and R as domains.
4.3 Consider the relation schema containing book data for a bookstore: Book (title, author, isbn, publisher, pubDate, pubCity, qtyOnHand).
Write out the table for an instance of this relation having 10 rows.
Identify a superkey, a candidate key, and the primary key, writing out any assumptions you need to make to justify your choice.
4.4 Consider the following database instance, which contains information about employees and the projects to which they are assigned:
Emp |
---|
empId | lastName |
---|
E101 | Smith |
E105 | Jones |
E110 | Adams |
E115 | Smith |
Assign |
---|
empId | projNo | hours |
---|
E101 | P10 | 200 |
E101 | P15 | 300 |
E105 | P10 | 400 |
E110 | P15 | 700 |
E110 | P20 | 350 |
E115 | P10 | 300 |
E115 | P20 | 400 |
Proj |
---|
projNo | projName | budget |
---|
P10 | Hudson | 500000 |
P15 | Columbia | 350000 |
P20 | Wabash | 350000 |
P23 | Arkansas | 600000 |
Show all the tables (including the intermediate ones) that would be produced by each of the following relational algebra commands:
SELECT Emp WHERE lastName = 'Adams' GIVING T1
T1 JOIN Assign GIVING T2
Symbolically this is
(σlastName='Adams'(Emp))⋈ Assign
SELECT Proj WHERE budget > 400000 GIVING T1
T1 JOIN Assign GIVING T2
PROJECT T2 OVER empId GIVING T3
Symbolically this is
ΠempId((σbudget>400000(Proj))⋈ Assign)
PROJECT Assign OVER projNo GIVING T1
T1 JOIN Proj GIVING T2
PROJECT T2 OVER budget GIVING T3
Symbolically this is
Πbudget(ΠprojNo(Assign)⋈ Proj)
4.5 Consider the following schema for a database that keeps information about business trips and their associated expenses by employees, noting that SSN is a foreign key in Trip that refers to Employee, and TripId is a foreign key in Expense that refers to Trip;
Employee(SSN, name, deptNo, jobTitle, salary)
Trip(tripId, departureCity, destinationCity, departureDate, returnDate, SSN)
Expense(tripId, item, date, amount)
Write relational algebra queries for each of the following:
Get a list of all the different destination cities where the employees have taken trips.
Find all the employee information for employees who work in Department 10.
Get complete trip records (but not their associated expenses) for all trips with departure dates after January 1 of the current year.
Find the names of all employees who have departed on trips from London.
Find the SSN of all employees who have any single expense item of more than $1,000 for any trip.
Find the names of all employees who have any expense item called “Entertainment.”
Find the destination cities of all trips taken by employees who have the job title of “Consultant.”
Find the names and departments of all employees who have any single expense item of over $1,000 since January 1 of this year.
Find the items and amounts of all expenses for a trip to Cairo beginning on January 3 of this year taken by employee Jones.
Find the names, departments, and job titles of all employees who have any expense item with value “Service Charge” for trips taken to Melbourne last year, along with the date and amount of the expense.
4.6 Design a relational database schema corresponding to the Customer and Order ER diagram shown in FIGURE 4.12.
Exercises 4.7 through 4.18 use Chapter 3, exercises 3.2 through 3.14.
4.7 Design a relational database schema for the data about the book collection described in Exercises 3.2 and 3.3.
4.8 Design a relational database schema for the data about college students, academic advisors, clubs, moderators, and activities described in Exercise 3.4.
4.9 Design a relational database schema for the data about the small dental practice described in Exercise 3.5.
4.10 Design a relational database schema for the data about the interior design firm described in Exercise 3.6.
4.11 Design a relational database schema for the data about the automobile body repair shop described in Exercise 3.7.
4.12 Design a relational database schema for the data about the physical therapy center described in Exercise 3.8.
4.13 Design a relational database schema for the data described in Exercise 3.9, including the specialization of book sellers.
4.14 Design a relational database schema for the data described in Exercise 3.10, including the Person generalization.
4.15 Design a relational database schema for the data described in Exercise 3.11, including the types of dentists and dental assistants.
4.16 Design a relational database schema for the data described in Exercise 3.12, including the different types of clients and contractors.
4.17 Design a relational database schema for the data described in Exercise 3.13, including different types of automobile repairs and technicians.
4.18 Design a relational database schema for the data described in Exercise 3.14, including the Person generalization and types of equipment and devices.