Comp 521: Files and Databases -- Fall 2012 Problem Set #1 Issued: 8/28/2012 Due: In class 9/11/2012
Homework Information: Some of the problems are probably too long to attempt the night before the due date, so plan accordingly. Late homework will penalized by a factor of 70.71% for each late class period. Feel free to work with others, but the work you hand in should be your own.
Question 1
Create an E-R model/diagram and define table structures that satisfy the following requirements:
A registered dog has a "name", "sex", and "breed"
A litter results from mating of two dogs; it has a "birthday", and results in puppies that are registered dogs
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:
An invoice is written by a salesrep. Each salesrep can write many invoices, but each invoice is written by a single salesrep.
The invoice is written for a single customer. However, each customer may have many invoices.
An invoice may include many line items, which describe the products bought by the customer.
The product information is stored in an inventory entity.
The product's vendor information is found in a vendor entity.
Question 3
Consider the following database schema:
EMPLOYEE
FirstName
Initial
LastName
SSN
Birthdate
Address
Sex
Salary
MgrSSN
DeptNum
DEPARTMENT
DeptName
DeptNum
MgrSSN
MgrStartDate
DEPT_LOCATION
DeptNum
DeptLocation
PROJECT
ProjName
ProjNum
ProjLocation
DeptNum
WORKS_ON
EmpSSN
ProjNum
Hours
DEPENDENT
EmpSSN
DependentName
Sex
Birthdate
Relationship
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
Retrieve the names of employees in department 5 who work more than 10 hours per week on the "ProductX" project.
List the names of employees who have one or more dependents older than themselves.
Finde the name of employees who are directly supervised by "Franklin Benjamin"
For each project, list the project name and the total number of man-hours per week spent on that project.
Retrieve the names of employees who work on every project
Retrieve the names of employees who do not work on any project
For each department, retrieve the department name, and the salary of the department's manager
Retrieve the average salary of all female employees
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
List the last names of department managers who have no dependents
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.