Homework 1 Relational Algebra and Relational Calculus

Homework 1 
Part 1. Relational Algebra and Relational Calculus (25 points) Consider the relational schema for a library database as shown in Figure 1. 1. Provide a relational algebra expression for each of the following queries: A. Retrieve the names of borrowers who do not have any books checked out. B. For each book that is loaned out from the WLafayette branch (i.e., branch name is WLafayette), retrieve the book title, the borrower’s name, and the borrower’s address. C. Retrieve the names of borrowers who have checked out books from at least two different library branches. D. Retrieve the names of branches, which have copies of all books authored (or coauthored) by Stephen King. E. Retrieve the names and phone numbers of borrowers who have checked out at least one book authored (or coauthored) by Dostoevsky and (at least) one book authored (or coauthored) by Tolstoy from the library with branch_id 5.
2. Provide a tuple calculus expression for each query in Question 1 (Part 1).
3. Provide a domain calculus expression for each query in Question 1 (Part 1).
2 of 4
Figure 1
Part 2. ER-Diagram Fundamentals (25 points) Consider the ER diagram shown in Figure 2 below for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans. Answer the following questions: A. List the strong (nonweak) entity types in the ER diagram. B. Is there a weak entity type? If so, give its name, its partial key, and its identifying relationship. C. List the names of all relationship types, and specify the (min, max) constraints on each participation of an entity type in a relationship type (i.e., draw subsets of the diagram
3 of 4
showing only the two entities involved in the relationship, the relationship type and the (min, max) constraints). D. Suppose that every customer must have at least one account but is restricted to at most two loans at a time, and that a bank branch cannot have more than 1000 loans. How does this show up on the (min, max) constraints? (redraw the diagrams from (C)).
Figure 2
Part 3. Designing an E-R Diagram (25 points) You are asked to design the E-R diagram of a system managing the software projects developed by a software development company. The system manages the software projects including their tasks (e.g., modules) and their details. State any assumptions that you make. Make sure that your design captures the following aspects: A. Information about the departments in the software company. Each department has a unique identifier, a name (e.g., Database Developers, Frontend Developers, Architecture Group), and a head (i.e., a software engineer). B. Information about the software engineers. Each software engineer is affiliated to one department and has first name, last name, and an email. C. Information about the projects (e.g., Blackboard). Each project has an identifier, a starting date, a budget, a delivery date, and a main contact who is a software engineer.
4 of 4
D. Information about the tasks (e.g., Login form) of each project. Each task has an identifier, name, number of hours. E. Each task has one or more assigned software engineers, and a single software engineer leading that task. F. Information about the areas of expertise (e.g., Oracle Development, SharePoint). Each area of expertise has an identifier and name. G. Each software engineer is associated with zero or more areas of expertise.
Part 4. Converting an E-R Diagram to the relational model (25 points) Convert the E-R model you designed in Part 1 into the relational model. Show relation names, attributes, primary keys, and the foreign keys (using connector lines with arrows pointing towards the attribute referred to).