use EduvosLibrary

db.createCollection("Author")

db.createCollection("Book")

db.createCollection("BookAuthor")

db.createCollection("Member")

db.createCollection("BorrowedBooks")
db.Author.insertMany([
{
authorID:1,
firstName:"John",
lastName:"Smith"
},
{
authorID:2,
firstName:"Sarah",
lastName:"Jones"
}
])
db.Book.insertMany([
{
ISBN:"9781111111111",
title:"Database Systems",
publicationYear:2027,
available:"Online",
quantity:10
},
{
ISBN:"9782222222222",
title:"Computer Networks",
publicationYear:2028,
available:"Store",
quantity:8
}
])
db.BookAuthor.insertMany([
{
ISBN:"9781111111111",
authorID:1
},
{
ISBN:"9782222222222",
authorID:2
}
])
db.Member.insertMany([
{
memberID:1001,
firstName:"Thabo",
lastName:"Mokoena",
email:"thabo@gmail.com",
phoneNo:"0821234567"
},
{
memberID:1002,
firstName:"Lerato",
lastName:"Nkosi",
email:"lerato@gmail.com",
phoneNo:"0839876543"
}
])
db.BorrowedBooks.insertMany([
{
memberID:1001,
ISBN:"9781111111111",
dateBorrowed:new Date("2026-05-01"),
dueDate:new Date("2026-05-15"),
returnDate:new Date("2026-05-20"),
fineAmount:50
},
{
memberID:1002,
ISBN:"9782222222222",
dateBorrowed:new Date("2026-06-01"),
dueDate:new Date("2026-06-15"),
returnDate:new Date("2026-06-14"),
fineAmount:0
}
])
db.Book.find(
{
publicationYear:
{
$gt:2026
}
}
)
db.BorrowedBooks.aggregate([
{
$group:
{
_id:null,
TotalRevenue:
{
$sum:"$fineAmount"
}
}
}
])
db.BorrowedBooks.aggregate([
{
$group:
{
_id:null,
TotalRevenue:
{
$sum:"$fineAmount"
}
}
}
])
db.BorrowedBooks.deleteOne(
{
fineAmount:50
}
)
db.BorrowedBooks.aggregate([
{
$lookup:{
from:"Member",
localField:"memberID",
foreignField:"memberID",
as:"member"
}
},
{
$lookup:{
from:"Book",
localField:"ISBN",
foreignField:"ISBN",
as:"book"
}
},
{
$unwind:"$member"
},
{
$unwind:"$book"
},
{
$project:{
_id:0,
memberID:"$member.memberID",
firstName:"$member.firstName",
lastName:"$member.lastName",
bookTitle:"$book.title",
dateBorrowed:1,
dueDate:1,
returnDate:1,
fineAmount:1
}
}
])