Assignment Report: Part B
Assignment Report: Part B
Database Schema
Branch Table
BranchID: Primary Key (PK), INT
BranchSuburb: VARCHAR(255)
BranchState: CHAR(3)
Member Table
MemberID: PK, INT
MemberStatus: CHAR(9) DEFAULT 'REGULAR'
MemberName: VARCHAR(255)
MemberAddress: VARCHAR(255)
MemberSuburb: VARCHAR(25)
MemberState: CHAR(3)
MemberExpDate: DATE
MemberPhone: VARCHAR(10)
Publisher Table
PublisherID: PK, INT
PublisherName: VARCHAR(255)
PublisherAddress: VARCHAR(255) DEFAULT NULL
Book Table
BookID: PK, INT
BookTitle: VARCHAR(255)
PublisherID: INT (Foreign Key to Publisher)
PublishedYear: INT
Price: NUMERIC(5,2)
Author Table
AuthorID: PK, INT
AuthorName: VARCHAR(255)
AuthorAddress: VARCHAR(255)
Authoredby Table
BookID: INT (Foreign Key to Book)
AuthorID: INT (Foreign Key to Author)
Holding Table
BranchID: INT (Foreign Key to Branch)
BookID: INT (Foreign Key to Book)
InStock: INT DEFAULT 1
OnLoan: INT DEFAULT 0
Borrowedby Table
BookIssueID: PK, UNSIGNED INT
BranchID: INT (Foreign Key to Branch)
BookID: INT (Foreign Key to Book)
MemberID: INT (Foreign Key to Member)
DateBorrowed: DATE
DateReturned: DATE DEFAULT NULL
ReturnDueDate: DATE
(Foreign Keys to Branch, Book, and Member)
Database Schema: Explanation
Branch Table
BranchID; Primary Key (PK) an integer value that uniquely identifies each branch.
BranchSuburb; A string of, up to 255 characters representing the suburb where the branch is located.
BranchState; A 3 character code indicating the state where the branch is situated.
Member Table
MemberID; Primary Key (PK) an integer value that uniquely identifies each member.
MemberStatus; A string of up to 9 characters representing the status of the member with a default value of 'REGULAR'.
MemberName; A string of up to 255 characters containing the name of the member.
MemberAddress; A string of up to 255 characters indicating the address of the member.
MemberSuburb; A string of up to 25 characters representing the suburb where the member resides.
MemberState; A 3 character code indicating the state where the member lives.
MemberExpDate; The expiration date for membership stored as a date value.
MemberPhone; A string of up to 10 characters specifying the phone number associated with the member.
Publisher Table
PublisherID; Primary Key (PK) an integer value that uniquely identifies each publisher.
PublisherName; A string of up to 255 characters containing the name of a publisher.
PublisherAddress; An field, allowing for storing an address for publishers. It is represented as a string with a default value set as NULL if not provided.
Book Table
BookID ; Primary Key (PK) an integer value that uniquely identifies each book record in our database system.
BookTitle ; The title or name attributed to a book, stored as a string with a length limit set at 255 characters.
PublisherID ; An integer field serving as a reference linking this table with its respective publisher record.
PublishedYear ; An integer value indicating the year when the book was published.
Price ; A field representing the cost of the book with a maximum of 5 digits and 2 decimal places allowed.
Author Table
AuthorID ; Primary Key (PK) an integer value that uniquely identifies each author in our database system.
AuthorName ; A string field with a length limit set at 255 characters containing the name of an author.
AuthorAddress ; A string field with a length limit set at 255 characters indicating the address associated with an author.
Authoredby Table
BookID; An integer field serving as a reference linking this table with its respective book record.
AuthorID; An integer field serving as a reference linking this table with its respective author record.
Holding Table
BranchID; An integer field serving as a reference linking this table with its respective branch record.
BookID; An integer field serving as a reference linking this table with its respective book record.
InStock; An optional integer field indicating the quantity of books available in stock set to 1 by default.
OnLoan; An integer field indicating the quantity of books, on loan set to 0 by default.
Borrowedby Table
BookIssueID; Primary Key (PK) an integer value that uniquely identifies each borrowed book issue.
BranchID; An integer field serving as a reference linking this table with its respective branch record.
BookID; An integer field serving as a reference linking this table with its respective book record.
Task 1: Logging Fine Fees for Overdue
By including a column named FineFee in the Borrowedby table, I altered Task 1. Specifically, all fines related to books are tracked in this column. I updated the table's schema using the ALTER TABLE statement to put this modification into effect. added a column called FineFee, whose default value is 0.0. With this modification, we can now keep track of and store any late fees for books.
ALTER TABLE BorrowedbyADD COLUMN FineFee NUMERIC(8,2) DEFAULT 0.0;
I used the ALTER TABLE statement to modify the existing table structure by adding the FineFee column. This allows us to store and track fine fees for overdue books.
Task 2: Implementing BR8 and Handling Exceptions
I was able to effectively implement Business Rule 8 (BR8) for Task 2, which deals with how members handle outstanding items. If a member owns an item that is past its expiration date, BR8 states that their membership will be suspended, and their charge will increase by $2 per day.
DELIMITER //
CREATE TRIGGER UpdateMemberStatusBEFORE INSERT ON BorrowedbyFOR EACH ROW
BEGIN
DECLARE overdue_days INT; IF NEW.DateReturned IS NULL THEN
SET overdue_days = DATEDIFF(NEW.ReturnDueDate, NEW.DateBorrowed);
IF overdue_days > 0 THEN
UPDATE Member
SET MemberStatus = 'SUSPENDED'
WHERE MemberID = NEW.MemberID; END IF; END IF;END;//
DELIMITER ;To achieve this, I created a trigger that calculates the number of days the item is overdue. If it is indeed overdue the trigger updates the members status to 'SUSPENDED.'
Task 3: Terminating Members with Overdue Items
In order to detect members who have overdue goods and suspend their membership if they have had two suspensions in the previous three years, I developed a stored procedure in Task 3 called TerminateMembersWithOverdueTwice. The process operates in this manner:
The current date and a cutoff date from three years ago are calculated.
It makes a makeshift table to hold members' suspensions and past-due stuff.
It counts suspensions and adds data to the temporary table.
A member's membership status is changed to "TERMINATED" if they satisfy the requirements for two or more suspensions.
Task 4: Testing the Trigger and Stored Procedure
In Task 4, I verified the operation and accuracy of the trigger and stored process by testing them in a variety of settings. To show that the trigger and the stored procedure both function as planned, I included SQL queries and their outcomes in my workbench.
Conclusion
I have clearly defined the pertinent keys and provided the schema for the generated tables in this report. I have explained the trigger's and stored procedure's functions, given proof of my work, and detailed how I handled each of the four jobs in Part A. After testing, it was determined that the trigger and procedure satisfied the requirements.
