db.AUTHOR.insertMany([
{ authorID: 1, firstName: "John", lastName: "Smith" },
{ authorID: 2, firstName: "Sarah", lastName: "Jones" }
]);

db.BOOK.insertMany([
{ ISBN: "978111", title: "Database Systems", publicationYear: 2027 },
{ ISBN: "978222", title: "MongoDB Basics", publicationYear: 2025 }
]);

db.MEMBER.insertMany([
{ memberID: 101, firstName: "Mike", lastName: "Brown", email: "mike@gmail.com", phoneNo: "0812345678" },
{ memberID: 102, firstName: "Lisa", lastName: "Taylor", email: "lisa@gmail.com", phoneNo: "0823456789" }
]);

db.BOOKAUTHOR.insertMany([
{ ISBN: "978111", authorID: 1 },
{ ISBN: "978222", authorID: 2 }
]);

db.BORROWEDBOOKS.insertMany([
{
memberID: 101,
ISBN: "978111",
dateBorrowed: new Date("2027-01-10"),
dueDate: new Date("2027-01-20"),
returnDate: new Date("2027-01-18"),
fineAmount: 0
},
{
memberID: 102,
ISBN: "978222",
dateBorrowed: new Date("2027-02-01"),
dueDate: new Date("2027-02-10"),
returnDate: new Date("2027-02-20"),
db.BORROWEDBOOKS.deleteOne({ fineAmount: 50 });
}
]);

db.BOOK.find({ publicationYear: { $gt: 2026 } });
db.BORROWEDBOOKS.aggregate([
{
$group: {
_id: null,
totalFineRevenue: { $sum: "$fineAmount" }
}
}
]);
db.BORROWEDBOOKS.aggregate([
{
$lookup: {
from: "MEMBER",
localField: "memberID",
foreignField: "memberID",
as: "memberDetails"
}
},
{ $unwind: "$memberDetails" },
{
$lookup: {
from: "BOOK",
localField: "ISBN",
foreignField: "ISBN",
as: "bookDetails"
}
},
{ $unwind: "$bookDetails" },
{
$project: {
_id: 0,
memberID: "$memberDetails.memberID",
firstName: "$memberDetails.firstName",
lastName: "$memberDetails.lastName",
bookTitle: "$bookDetails.title",
dateBorrowed: 1,
dueDate: 1,
returnDate: 1,
fineAmount: 1
}
}
]);