Fine Calculation for Student Hire Returns

The assignment content describes a database design for a student hire and return system. The system has four tables: STUDENT, ITEM_DETAILS, ITEM_COPIES, and STUDENT_HIRE_RETURN_DETAILS. The tables have various columns to store information such as student details, item names and quantities, item status, and hire and return dates. The database is designed in 3NF (Third Normal Form) and the SQL code provided creates the tables and inserts data into them. The assignment also includes four queries that can be used to generate reports on items on loan, items in repair, overdue hires, and indebted hirers.

[ LUSH DATABASE DESIGN ]
Student Name:
Student ID:
Course Name:
Course ID:
Faculty Name:
University Name:

1
Contents
LIST OF BUSINESS RULES.........................................................................................................2
ER DIAGRAM................................................................................................................................3
ENTITIES AND THEIR CHARACTERISTICS:...........................................................................3
DATA DICTIONARY....................................................................................................................4
DATABASE DESIGN & TABLE STRUCTURE..........................................................................5
NORMALIZATION........................................................................................................................7
Sql Code...........................................................................................................................................7
CREATE & INSERT COMMANDS..........................................................................................7
ALL REPORT GENERATED QUERIES................................................................................10
Database Implementation..............................................................................................................12
2.1..............................................................................................................................................12
2.2..............................................................................................................................................12
2.3..............................................................................................................................................15

2
LIST OF BUSINESS RULES
1) Each items has quantities. There can be more than one item for one type of item.
2) Regular student’s details are stored in database.
3) If student id is not available then student driving licensing number will be stored.
4) Student can hire more than one item at a time and each time student hire items the hiring
date and return date is stored in database and the initial charges will be imposed to
student and these details are saved into database.
5) When student will return the item then the status of the item will be saved into database
and it will checked there is any fine or not and final payment will be taken from student.

3
ER DIAGRAM
ENTITIES AND THEIR CHARACTERISTICS:
Student
 This table is used to store the student data uniquely. A unique student id is declared of
integer type, this is unique because this is declared as primary key. This id never be null.
 If the student id is not available then there is one another column “driving license
number” this also declared as unique. The values of this column can be null but cannot
be duplicate so this column value is also used to identify students uniquely.

4
Item_Details
 This table contains all the type name of the thing which are currently presented with their
quantities.
 To recognize all the items uniquely a unique id is used. This is an integer type and auto
incremented.
Item_Copies
 As we know there can be many items of same type and name with their different
conditions (status). So they need to store into database with the unique id. So in this
table a column “item_copies_id” is declared, this is an integer auto incremented column
and this is declared as primary key.
 In this table, there is a foreign key that itemID belong to Item_Details table. This id can
be repeated in this table because here can be many items of same type or with same item
type id with different conditions.
 There on other column “status” this defines the item current status that can be available
( available in stock), on loan ( can be hired by any hirer), in repair ( the item is damaged
and this is in under process).
Student_Hire_Return_Details
 There is three foreign keys, one is for hirer id (student id) this refer to student table.
 Second one for driving license number refer to student table column that is license
number.
 Third for item_copy_id belong to item_copies table.
 The hirer item loan date and item return date is saved with the item return status,
fine, total bill to pay.
DATA DICTIONARY
Student: students are the hirer they hire items and then they return. Regular student details are
saved into database. If student id is not available then student driving licensing number is stored
and this acts as unique id so that a student details are recognize better.
Item_Details: Here is all item details are saved into database with the item name and type and
the total number of quantity of items that are available in the stock.

5
Item_Copies: This is assumed that there can be more than one item of same type or same name
and they are numbered uniquely by the integer values and these all are stored into database. Each
item have status means item is available or is on loan or is in repair.
Student_Hire_Return_Details: this table store all item loan details, like who hire the item,
when item is hired, and the return date and hire date and the return status of item (this status
defines the item condition ) ,is there any fine for student to pay?, what is the total charge for
student to pay? This table cares about these all information.
Return_status: Item status in Student_Hire_Return_Details table can be any from these ‘IN
GOOD ORDER','NEEDS CLEANING','REQUIRES SERVICE’, the charges for student is
totally depend on the item current condition.
DATABASE DESIGN & TABLE STRUCTURE
Table: STUDENT
COLUMN NAME DATATYPE CONSTRAINTS
studentID INT AUTO_INCREMENT
PRIMARY KEY
licenceNum VARCHAR(16)
studentName VARCHAR(30)
gender ENUM('F','M')
contact VARCHAR(12)
emailID VARCHAR(50)
address VARCHAR(100)
Table: ITEM_DETAILS
COLUMN NAME DATATYPE CONSTRAINTS
itemID INT AUTO_INCREMENT
PRIMARY KEY

6
itemName VARCHAR(30)
quantity INT
Table: ITEM_COPIES
COLUMN NAME DATATYPE CONSTRAINTS
itemCopyID INT PRIMARY
KEY(itemCopyID, itemID)
itemID INT FOREIGN KEY (itemID)
REFERENCES
ITEM_DETAILS(itemID)
itemCStatus ENUM('AVAILABLE','IN
REPAIR','ON LOAN')
Table: STUDENT_HIRE_RETURN_DETAILS
COLUMN NAME DATATYPE CONSTRAINTS
studentID INT AUTO_INCREMENT
PRIMARY KEY
FOREIGN KEY (studentID)
REFERENCES
STUDENT(studentID)
itemCopyID INT FOREIGN KEY
(itemCopyID) REFERENCES
ITEM_COPIES(itemCopyID)
studentLicenceNum VARCHAR(16) FOREIGN KEY
(studentLicenceNum)
REFERENCES
STUDENT(licenceNum)
returnedItemStatus ENUM('IN GOOD
ORDER','NEEDS
CLEANING','REQUIRES
SERVICE')
fine DOUBLE
totalCharges DOUBLE
hireDate DATE

7
returnDate DATE
maxDaysToReturn INT
NORMALIZATION
My table structure is already designed in 3rd NF. So I do not need to modify my design.
Sql Code
CREATE & INSERT COMMANDS
CREATE TABLE STUDENT(
studentID INT AUTO_INCREMENT PRIMARY KEY,
licenceNum VARCHAR(16) UNIQUE,
studentName VARCHAR(30),
gender ENUM('F','M'),
contact VARCHAR(12),
emailID VARCHAR(50),
address VARCHAR(100)
);
INSERT INTO STUDENT
VALUES(101,'alex5024512OO412','ALEX','F','123456789','[e-mail]',null),
(102,'john5024512OO412','JOHN','M','123456789','[e-mail]',null),
(103,'OLIVE5024512OO41','OLIVER','M','123456789','[e-mail]',null),
(104,'david5024512OO41','DAVID','M','123456789','[e-mail]',null),
(105,'rose5024512OO412','ROSE','M','123456789','[e-mail]',null);

8
CREATE TABLE ITEM_DETAILS(
itemID INT AUTO_INCREMENT PRIMARY KEY,
itemName VARCHAR(30),
quantity INT
);
INSERT INTO ITEM_DETAILS VALUES(11,'tent',4),
(12,'camping stools',2);

9
CREATE TABLE ITEM_COPIES(
itemCopyID INT,
itemID INT,
itemCStatus ENUM('AVAILABLE','REPAIR','HIRE'),
PRIMARY KEY(itemCopyID, itemID),
FOREIGN KEY (itemID) REFERENCES ITEM_DETAILS(itemID)
);
INSERT INTO ITEM_COPIES VALUES(1,11,'AVAILABLE'),
(2,11,'AVAILABLE'),
(3,11,'AVAILABLE'),
(4,11,'AVAILABLE'),
(5,12,'AVAILABLE'),
(6,12,'AVAILABLE');
CREATE TABLE STUDENT_HIRE_RETURN_DETAILS(
studentID INT,
itemCopyID INT,
studentLicenceNum VARCHAR(16),
returnedItemStatus ENUM('IN GOOD ORDER','NEEDS CLEANING','REQUIRES
SERVICE'),
fine DOUBLE,
initialCharges DOUBLE,
FOREIGN KEY (itemCopyID) REFERENCES ITEM_COPIES(itemCopyID),
FOREIGN KEY (studentID) REFERENCES STUDENT(studentID),
FOREIGN KEY (studentLicenceNum) REFERENCES STUDENT(licenceNum)
);
INSERT INTO STUDENT_HIRE_RETURN_DETAILS VALUES(101,1,null,'IN GOOD
ORDER',0,20),
(101,2,null,'NEEDS CLEANING',10,20),
(101,3,null,'IN GOOD ORDER',0,20);

10
ALL REPORT GENERATED QUERIES
Q-1) Items on loan
CREATE OR REPLACE VIEW ITEM_ON_LOAN AS
( SELECT itemName, itemCopyID FROM item_details i, item_copies ic
WHERE ic.itemID = i.itemID AND ic.itemCStatus = 'ON LOAN');

11
Q-2) Items in repair
CREATE OR REPLACE VIEW ITEM_IN_REPAIR AS
( SELECT itemName, itemCopyID FROM item_details i, item_copies ic
WHERE ic.itemID = i.itemID AND ic.itemCStatus = 'IN REPAIR');

12
Q-3) OVERDUE HIRES
CREATE OR REPLACE VIEW OVERDUE_HIRES AS
(SELECT s.studentID,s.licenceNum,studentName,s.contact,s.emailID, id.itemName,
totalCharges, fine, datediff(shrd.returnDate,shrd.hireDate) DAY_DIFFERENCE
FROM student s,student_hire_return_details shrd, item_copies ic, item_details id
WHERE datediff(shrd.returnDate,shrd.hireDate) > maxDaysToReturn AND
s.studentID = shrd.studentID AND
shrd.itemCopyID = ic.itemCopyID AND ic.itemID = id.itemID
);

13
Q-4) INDEBTED HIRERS
CREATE OR REPLACE VIEW INDEBTED_HIRERS AS
(SELECT s.studentID,s.licenceNum,studentName,s.contact,s.emailID, id.itemName,
totalCharges, fine
FROM student s,student_hire_return_details shrd, item_copies ic, item_details id
WHERE shrd.fine != 0 AND
s.studentID = shrd.studentID AND
shrd.itemCopyID = ic.itemCopyID AND ic.itemID = id.itemID
);

14
Database Implementation
2.1
2.2

15

16

17
2.3
ITEM_COPIES TABLE
ITEM_DETAILS TABLE

18
STUDENT TABLE
STUDENT_HIRE_RETURN_DETAILS TABLE