Question 1

Create an E-R model/diagram and define table structures that satisfy the following requirements:

1. A registered dog has a "name", "sex", and "breed"
2. A litter results from mating of two dogs; it has a "birthday", and results in puppies that are registered dogs
3. A dog can only appear as puppy in one litter, but it may participate as a dam or sire in many litters.

Question 2

Create an E-R model/diagram and define table structures for the following:

1. An invoice is written by a salesrep. Each salesrep can write many invoices, but each invoice is written by a single salesrep.
2. The invoice is written for a single customer. However, each customer may have many invoices.
3. An invoice may include many line items, which describe the products bought by the customer.
4. The product information is stored in an inventory entity.
5. The product's vendor information is found in a vendor entity.

Question 3

Consider the following database schema:

The attributes MgrSSN in DEPARTMENT and EmpSSN in WORKS_ON and DEPENDENT are foreign keys referencing the SSN attribute of EMPLOYEE. Likewise the DeptNum attribute of Employee is a foreign key referencing the attribute with the same name in DEPARTMENT. You can also assume that ProjLocation attribute of Project and the DeptLocation attribute of Department are from a common domain.

Specify the following queries using relational algebra operators

1. Retrieve the names of employees in department 5 who work more than 10 hours per week on the "ProductX" project.
2. List the names of employees who have one or more dependents older than themselves.
3. Finde the name of employees who are directly supervised by "Franklin Benjamin"
4. For each project, list the project name and the total number of man-hours per week spent on that project.
5. Retrieve the names of employees who work on every project
6. Retrieve the names of employees who do not work on any project
7. For each department, retrieve the department name, and the salary of the department's manager
8. Retrieve the average salary of all female employees
9. Find the names and addresses of employees who work on at least one project located in Houston but whose department is not located in Houston
10. List the last names of department managers who have no dependents
11. Generalize Query i from above to list the names and addresses of employees who work on a project in one city, but whose department has no location in that city.

Question 4

Specify queries a, b, c, e, f, i, j, and k of Question 4 in both tuple relational calculus and the domain relational calculus.

Question 5

Show how to specify the following relational algebra operations in both tuple and domain relational calculus.

1. σ_A=C(R(A,B,C))
2. π(R(A,B,C))
3. R(A,B,C) * S(C,D,E)
4. R(A,B,C) ∪ S(A,B,C)
5. R(A,B,C) ∩ S(A,B,C)
6. R(A,B,C) - S(A,B,C)
7. R(A,B,C) × S(D,E,F)
8. R(A,B) ÷ S(A)