Assignment 2 Henry Books Database

In this assignment, you are required to answer the following queries for the Henry Books Database using Relational Algebra. Implement your queries in WinRDBI, the educational tool to achieve this task, and submit the Database and your answers using SCIS Moodle.

 

The class webpage contains files that include all instructions that allow you to install the WinRDBI system, how to load a Relational Database (the .rdb file), and how to write and run a query. I suggest that after installing the WinRDBI on your computer, you practice with the given Henry Books Database as well as your own example queries before solving the given assignment.

 

Relational Model representation of the Henry Books Database:

 

Relations and their Attributes are specified below with the identification of the Primary Key at the end of each Relation:

 

a)       Author - (authorNum/numeric, authorLast/char, authorFirst/char) PK: authorNum

b)      Book - (bookCode/char, title/char, publisherCode/char, type/char, price/numeric, paperback/char) PK: bookCode

c)       Branch - (branchNum/numeric, branchName/char, branchLocation/char, numEmployees/numeric) PK: branchNum

d)      Inventory - (bookCode/char, branchNum/numeric, onHand/numeric) PK: bookCode

e)      Publisher - (publisherCode/char, publisherName/char, city/char) PK: publisherCode

f)        Wrote - (bookCode/char, authorNum/numeric, sequence/numeric) PK: bookCode,authorNum

Sequence refers to the sequence in which this author appears on the cover – first author, second author, etc.

g)       Copy - (bookCode/char, branchNum/numeric, copyNum/numeric, quality/char, price/numeric) PK: bookCode, branchNum, copyNum

 

Note: “char” in WinRDBI represents a String enclosed within single quotes.

 

PROBLEM-1 Queries: Implement these using WinRDBI.

 

1)      For every Artistic book (type is ‘ART’), list the book code, book title, its price, and the name of the author along with the author sequence.

2)      Retrieve the names of authors who have written at least one Fiction Book (type is ‘FIC’).

3)      Retrieve the names of all authors who have written books published by ‘Jove Publications’.

4)      Retrieve the names of authors who have not written any book in the possession of Henry Books.

5)      For every book of ’Good’ quality that the Henry Books possesses at its collection of branches, list the book title, the branch name that carries it, and the name of the author (along with the sequence number) who wrote it.

6)      Retrieve the names of authors who have written at least two books.

7)      The quality of each book available at Henry Books branches is specified in the “Copy” Relation. If all possible qualities of a specific book are available at the Henry Books branches collectively, then output its title.

 

The last two are more challenging ones because WinRDBI neither provides the Division operation nor aggregate functions and grouping.

 

PROBLEM-2:

 

WinRDBI does not support Aggregate Functions and Grouping. Hence, for the following three queries, just write Relational Algebraic Expressions to solve them.

 

8)      List the average price of all copies of books available at ‘Henry Downtown’ branch.

9)      For every book that is possessed by Henry Books, output the book code, the number of total copies available collectively at all its branches, and the average price of a copy of that book.

10)   For every publisher, list the name of the publisher, number of books it has published, and the average base price (Book relation contains the base price of a book) of those books.

 

Submit:

 

1)      The hard copy of one .alg file containing solutions for all parts of Problem-1 and hand-written solutions for the three parts of Problem-2 at the beginning of the class on the due date.

2)      The same two files (.alg and .pdf) in sofy copy form on SCIS Moodle by the deadline specified.