Final Managing Data(bases) using SQL

Description

We are going to expand the HR system. The company has a small technical library where employees can check out books. In the past, it was based on an honor system, unfortunately, this did not work too well. We have to design a small database that tracks the books, the employee who currently has books checked out, and the check in transaction.

The company wants to empower its employees by giving them the best resources to do their jobs. Therefore, books can be checked out a maximum of 30 to 32 days (depending on the weekday, see below). For every day a book is past due, a fee of $2 is assessed.

Here are the steps to create the database tables:

Books table (also review ERD diagram on next page):

	ISBN			Title			Number of Copies			Main Author
0-07-225790-3				OCP Certification All-in-One Exam Guide		5				John Watson
0-201-43336-2				SQL Queries for Mere Mortals		3				Michael Hernandez
0-201-69471-9				Database Design for Mere Mortals		2				Michael Hernandez
0-07-225364-9				Databases Demystified		7				Andy Oppel
0-12-369379-9				SQL for Smarties		1				Joe Celko

Database ERD diagram

(Columns in bold indicate NOT NULL constraints)

				EMPLOYEES
			PK	EMPLOYEE_ID
	BOOKS		I4	FIRST_NAME
PK	ISBN		I4	LAST_NAME
			U1	EMAIL
	TITLE			PHONE_NUMBER
				HIRE_DATE
	NO_OF_COPIES
			I2	JOB_ID
	AUTHOR
				SALARY
				COMMISSION_PCT
	BOOK_TRANS		FK1,I3	MANAGER_ID
			I1	DEPARTMENT_ID
	PK	BOOK_TRANS_ID
	FK2,U1	ISBN
	FK1,U1	EMPLOYEE_ID
	U1	CHECKOUT_DTE
		DUE_DTE
		RETURN_DTE
		PASTDUE_FEES

A checkout transaction consists of two DML statements:

1. Add a record into the book_trans table.

2. Decrease the No_of_Copies in the books table by one.

A return transaction consists of two transactions:

1. Update the return date in the book_trans table.

2. Increase the No_of_Copies column in the books table by one.

On the next page, you will find the individual tasks necessary to complete this final. For each task or sub task, provide the SQL statement and a screenshot, some procedure as with homework assignments.

Based on the ERD diagram create the Books table. Make sure to add the primary key constraint. In addition to the DDL statement, issue a describe command to show the created table.

2. CREATE TABLE book_trans:

Based on the ERD diagram create the Book_trans table. Choose a number data type for the primary key

Book_trans_ID. This table has four constraints:

• Primary Key constraint

• One unique constraint on ISBN, Employee_ID, Checkout_dte (implementation of business rule for checking out books, see first page)

• Foreign key constraint Employee_ID

• Foreign key constraint ISBN

In addition to the DDL statement, issue a describe command to show the created table.

2. Populate the books table:

Write five DML statements to create the records in the book table (see previous page for book data). After inserting the book records, issue a SELECT statement to show the book records.

2. Create the following checkout transactions:

ISBN 0-07-225790-3 checked out on 15-NOV-2007 by employee 101(book_trans_id=1) ISBN 0-07-225790-3 checked out on 12-NOV-2007 by employee 151(book_trans_id=2) ISBN 0-201-69471-9 checked out on 14-NOV-2007 by employee 175(book_trans_id=3) ISBN 0-12-369379-9 checked out on 16-NOV-2007 by employee 201(book_trans_id=4)

You need to populate the following column values(book_trans): Book_trans_id, ISBN, Employee_id, Checkout_dte You need to update the No_of_Copies(books table) values.

Show book_trans and book records by issuing a SELECT statement.

Instructor: Michael Kremer Page 3 mkremer@berkeley.ed

4. Update the due date in table book_trans:

Write one UPDATE statement using an expression with the Decode function or a CASE WHEN statement and the to_Char function:

UPDATE book_trans SET due_dte = expression

Issue a SELECT statement to show all records and columns from table book_trans.

If you were not able to create the due date expression, simply manually calculate the due date for each book transaction record and hardcode the due date into an UPDATE statement (will cause grade reduction). In this case you need to update the four book transaction records individually. You also need to use a WHERE clause to update the records individually.

UPDATE book_trans SET due_dte = date WHERE book_trans_id = value

4. Create the following check-in transactions:

Employee 151 returned book 0-07-225790-3 on 18-DEC-2007 Employee 201 returned book 0-12-369379-9 on 16-DEC-2007

You need to update the following columns (book_trans): Return_dte You need to update the No_of_Copies(books table)

Issue two SELECT statements to show all records and columns from table books and book_trans.

4. Calculate the Past Due fees using SQL:

Create first a SELECT statement with an expression that calculates the Past Due fees using the book_trans table. Use the Decode function and the sign function or CASE WHEN to test whether:

4. 1. sign(return_dte – due_dte) is -1 or 0 (no fees due, update to 0)

   2. sign(return_dte – due_dte) is 1 (fees due, update to (return_dte – due_dte)*2).

If the book is not returned yet, the whole expression will be null and no update will be performed. Then write an

UPDATE statement to update the Past Due fee column in the book_trans table:

UPDATE book_trans SET PastDue_fees = decode( expression) or CASE WHEN statement Issue a SELECT statement to show all records and columns from table book_trans.

7. Write a SQL statement displaying the following columns:

7. • Employee’s Last_name concatenated with a comma and blank space concatenated with Employee’s first_name. Use Name as an alias, limit this column to the first 30 characters

7. • Title of the book, use Book as an alias and limit this column to the first 40 characters

7. • Checkout_dte, Due_dte, Return_dte, PastDue_fees

7. • Order this SQL statement by last_name

Instructor: Michael Kremer Page 4 mkremer@berkeley.edu