Database Systems (SET11101)
Muhammad Najeeb | Database Systems
(SET11101) | 25/11/2023
1
Table of Contents
Task D............................................................................................................................2
CREATE STATEMENTS.........................................................................................2
Task E.............................................................................................................................7
INSERT STATEMENTS...........................................................................................7
Task F...........................................................................................................................11
Query 1.....................................................................................................................11
Query 2.....................................................................................................................11
Query 3.....................................................................................................................12
Task G..........................................................................................................................12
2
Task D
CREATE STATEMENTS
Create table Book
CREATE TABLE `book` (
`BookID` int(11) NOT NULL,
`Title` varchar(40) NOT NULL,
`Author` varchar(40) NOT NULL,
`ISBN` varchar(40) NOT NULL,
`PublicationDate` date NOT NULL,
`Genre` varchar(40) NOT NULL,
`lateFine` int(11) NOT NULL,
`checkoutCount` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `book`
ADD PRIMARY KEY (`BookID`);
Create table Copy
CREATE TABLE `copy` (
`copyID` int(11) NOT NULL,
`status` varchar(30) NOT NULL,
3
`BookID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `copy`
ADD PRIMARY KEY (`copyID`);
Create table Member
CREATE TABLE `member` (
`MemberID` int(11) NOT NULL,
`Name` varchar(40) NOT NULL,
`ContactDetails` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `member`
ADD PRIMARY KEY (`MemberID`);
Create table Loan
CREATE TABLE `loan` (
`LoanID` int(11) NOT NULL,
`BorrowDate` date NOT NULL,
`ReturnDate` date NOT NULL,
`copyID` int(11) NOT NULL,
4
`memberID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `loan`
ADD PRIMARY KEY (`LoanID`),
ADD KEY `loanCopy` (`copyID`),
ADD KEY `loanMember` (`memberID`);
Create table Reservation
CREATE TABLE `reservation` (
`ReservationID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `reservation`
ADD PRIMARY KEY (`ReservationID`);
Create table book_Fine
CREATE TABLE `book_fine` (
`FineID` int(11) NOT NULL,
`daysLate` int(11) NOT NULL,
`IsPaid` varchar(40) NOT NULL,
`memberID` int(11) NOT NULL,
5
`bookID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `book_fine`
ADD PRIMARY KEY (`FineID`),
ADD KEY `fineBook` (`bookID`),
ADD KEY `fineMember` (`memberID`);
Create table BookCopy Table
CREATE TABLE `bookcopy` (
`BookCopyID` int(11) NOT NULL,
`BookID` int(11) NOT NULL,
`CopyID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `bookcopy`
ADD PRIMARY KEY (`BookCopyID`),
ADD KEY `Foreign key` (`BookID`),
ADD KEY `Fore` (`CopyID`);
Create table Borrower Table
CREATE TABLE `borrower` (
6
`BorrowerID` int(11) NOT NULL,
`MemberID` int(11) NOT NULL,
`BookCopyID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `borrower`
ADD PRIMARY KEY (`BorrowerID`),
ADD KEY `Forei` (`BookCopyID`),
ADD KEY `Foreign ke` (`MemberID`);
Create table ReservationDetail Table
CREATE TABLE `reservationdetail` (
`ReservationDetailID` int(11) NOT NULL,
`ReservationID` int(11) NOT NULL,
`BookCopyID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
ALTER TABLE `reservationdetail`
ADD PRIMARY KEY (`ReservationDetailID`),
ADD KEY `detailReservation` (`ReservationID`),
ADD KEY `bookcopyReservation` (`BookCopyID`);
7
Task E
INSERT STATEMENTS
INSERT INTO `book` (`BookID`, `Title`, `Author`, `ISBN`, `PublicationDate`,
`Genre`, `lateFine`, `checkoutCount`) VALUES
(101, 'Book A', 'Author A', 'ISBN-A-123', '2022-01-01', 'Fiction', 3, 4),
(201, 'Book B', 'Author B', 'ISBN-B-456', '2022-02-01', 'Non-Fiction', 8, 5),
(301, 'Book C', 'Author A', 'ISBN-A-123', '2022-03-01', 'Fiction', 12, 7),
(401, 'Book D', 'Author C', 'ISBN-C-789', '2022-04-01', 'Science', 9, 11),
(501, 'Book E', 'Author D', 'ISBN-D-111', '2022-05-01', 'Mystery', 8, 15),
(601, 'Book F', 'Author D', 'ISBN-D-165', '2022-06-01', 'Science', 4, 12),
(701, 'Book G', 'Author A', 'ISBN-D-187', '2022-07-01', 'Mystery', 12, 16);
INSERT INTO `bookcopy` (`BookCopyID`, `BookID`, `CopyID`) VALUES
(601, 101, 1001),
(602, 101, 2001),
(603, 201, 3001),
(604, 201, 4001),
(605, 301, 5001),
(606, 401, 6001),
(607, 501, 7001);
8
INSERT INTO `book_fine` (`FineID`, `daysLate`, `IsPaid`, `memberID`, `bookID`)
VALUES
(501, 5, 'No', 1201, 101),
(502, 6, 'Yes', 1202, 301),
(503, 7, 'Yes', 1202, 401),
(504, 11, 'No', 1203, 501),
(505, 7, 'Yes', 1203, 101),
(506, 6, 'No', 1203, 701),
(507, 4, 'Yes', 1201, 501);
INSERT INTO `borrower` (`BorrowerID`, `MemberID`, `BookCopyID`) VALUES
(701, 1201, 601),
(702, 1201, 602),
(703, 1202, 603),
(704, 1202, 604),
(705, 1203, 605),
(706, 1206, 605),
(707, 1207, 607);
9
INSERT INTO `copy` (`copyID`, `status`, `BookID`) VALUES
(1001, 'On Loan', 101),
(2001, 'On Loan', 101),
(3001, 'Available', 103),
(4001, 'Available', 104),
(5001, 'On Loan', 103),
(6001, 'On Loan', 102),
(7001, 'Available', 105);
INSERT INTO `loan` (`LoanID`, `BorrowDate`, `ReturnDate`, `copyID`,
`memberID`) VALUES
(301, '2022-03-01', '2022-03-21', 3001, 1201),
(302, '2022-03-05', '2022-03-25', 2001, 1201),
(303, '2022-03-02', '2022-03-22', 3001, 1202),
(304, '2022-03-03', '2022-03-23', 4001, 1202),
(305, '2022-03-04', '2022-03-24', 5001, 1203),
(306, '2022-03-05', '2022-03-25', 1001, 1206),
(307, '2022-03-06', '2022-03-26', 5001, 1204);
INSERT INTO `member` (`MemberID`, `Name`, `ContactDetails`) VALUES
10
(1201, 'Christopher R. Edwards', '[email protected]'),
INSERT INTO `reservation` (`ReservationID`) VALUES
(401),
(402),
(403),
(404),
(405),
(406),
(407);
INSERT INTO `reservationdetail` (`ReservationDetailID`, `ReservationID`,
`BookCopyID`) VALUES
(801, 401, 603),
11
(802, 401, 604),
(803, 402, 601),
(804, 402, 602),
(805, 403, 604),
(806, 405, 603),
(807, 406, 607);
Task F
Query 1
SELECT checkoutCount, Title, BookID FROM `book` ORDER BY checkoutCount;
Query 2
SELECT borrower.BorrowerID, borrower.BookCopyID, bookcopy.BookID,
reservationdetail.ReservationID, book.Title, copy.status FROM `borrower` INNER JOIN
bookcopy ON borrower.BookCopyID = bookcopy.BookCopyID INNER JOIN
`reservationdetail` ON bookcopy.BookCopyID = `reservationdetail`.`BookCopyID` INNER
JOIN `book` ON bookcopy.BookID = book.BookID INNER JOIN `copy` ON
bookcopy.CopyID = copy.copyID WHERE `copy`.`status` = 'Available';
12
Query 3
SELECT `book_fine`.`memberID`, `book_fine`.`IsPaid`, `book`.`lateFine`*
book_fine.daysLate AS Fine_amount, `member`.`Name` FROM `book_fine` INNER JOIN
book ON `book_fine`.`bookID` = `book`.`BookID` INNER JOIN member ON
`book_fine`.`memberID` = member.MemberID WHERE `book`.`lateFine`*
book_fine.daysLate>5 AND book_fine.IsPaid ="No";
Task G
The county library is a popular library managing several books in the present times
and the business is functioning with the help of proper business rules for ensuring that
effective data management would be done. The main constraints or business rules of the
system are:
1. One book could be identified by the ISBN
2. One book could have several physical book copies
3. Respective book copy would be recognized by combination of the copy number
and the ISBN
4. Respective book copy would belong to one of the library location
13
5. Only one patron would be permitted for borrowing one book copy
6. One book copy could easily be borrowed by sole on member at any provided time
7. One book would belong to respective one category
8. One book could have several authors
9. Respective author could write several books
10. One book could have two primary status of “Available” or “On Loan”
11. The price of the book would vary by the copy
12. The library staff could easily suspend the privileges of borrowing of any member
if the books are being returned significantly late frequently than permitted number
of times
From the above list of the business rules presented, the only business rule not
implemented in the present database is “One book could have several authors”. This
constraint could be implemented further by adding another table in the database containing
information of the book authors and containing detailed associated among the book and the
authors of the book. This would help in tracking the authors of the books significantly easily
and track multiple authors of books.
