Assignment #2 Database Systems

Problem 1. [24pts] Consider the following relational database that stores information about the performance of credit card companies:Issuer( bank, card) Bank location(bank, location) Max limits( card, max limit) An instance of the database is the following: Issuer Bank Card Amex American Express Fist Federal Visa Fist Federal MasterCard Chase Visa Citizens Visa Citizens MasterCard Citizens Discovery Fleet Visa Bank location Bank Location Amex Chicago Fist Federal LA Chase NY Citizens Boston Fleet Boston Max limits Card Max limit Visa $50,000 MasterCard $100,000 Discovery $100,000 American Express $500,000 Write the following queries in relational algebra: 1. Which credit cards are issued by banks in Boston? 2. Which credit cards are not issued in NY? 3. Which banks issue credit cards with a limit less than $100,000? 4. Which banks issue only one credit card? 5. Which banks issue MasterCard and Visa but no other cards? 6. Which banks issue all credit cards? 1 Problem 2. [24pts] Consider a database with the following schema: Employee(SSN, name, salary, DNo) Department(DNo, DeptName, MgrSSN) Project(PNo, location, ProjName) HourLog(SSN, PNo, hours) The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department’s manager (MgrSSN). Each department has only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. Write the following queries in Relational Algebra. You may use assignment of intermediate results for long queries. a) Find the name and the SSN of everyone who works more than 100 hours on project number 5. b) Find the name and SSN of everyone who works for department number 1 and also works on project number 2. c) Find the name and the SSN of everyone who works on at least two projects. d) Find the name and the SSN of everyone who works on all projects. Problem 3. [36 pts] Consider the following schema: WORKS ( person-name, company-name, salary) LIVES ( person-name, street, city) LOCATED-IN ( company-name, city) MANAGES ( person-name, manager-name) The WORKS relation contains information about where an employee works and with what salary, the LIVES relation tells us where an employee lives, the LOCATED-IN relation gives the location of each company, while the MANAGES relation provides the employees and their managers. Give an expression in relational algebra for each of the queries below: 1. Find the names of all employees who work for First Bank Corporation. 2. Find the names and city of all employees who work for First Bank Corporation. 3. Find the names of all employees who live in the same city as the company they work for. 4. Find the names of all employees who live in the same city and on the same street as their manager. 5. Find the names of all employees who are not a manager. 6. Find the names of all manager who manages more than one employee. 2 Problem 4. [16pts] Consider the following two tables, T1 and T2: T1 P Q R 10 a 5 15 b 8 25 a 6 T2 A B C 10 b 6 25 c 3 10 b 5 Show the results of the following relational algebra queries: 1. T1 1T1.P=T2.A T2 2. T1 1T1.Q=T2.B T2 3. T1 1 T2 (assume the natural join happens for columns P and A) 4. T1 1T1.P=T2.A AND T1.R=T2.C T2 3