Scribd
Upload
36 views6 pages

Programming Assignmen Database 4

The document outlines a programming assignment for a library management database schema and SQL implementation as part of a Computer Science degree. It details the entities involved—Books, Members, and Loans—along with their attributes, SQL scripts for creating tables, inserting records, and performing operations. The assignment concludes by emphasizing the effective management of library resources and member information through SQL operations.

Uploaded by

munyendoadam9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
36 views6 pages

Programming Assignmen Database 4

The document outlines a programming assignment for a library management database schema and SQL implementation as part of a Computer Science degree. It details the entities involved—Books, Members, and Loans—along with their attributes, SQL scripts for creating tables, inserting records, and performing operations. The assignment concludes by emphasizing the effective management of library resources and member information through SQL operations.

Uploaded by

munyendoadam9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Programming Assignment Unit 4

An Assignment submitted in partial fulfillment


of the requirements for the degree of
Bachelor of Science
in
Computer Science

CS 2203 – Databases
Term 1 - 2025
University of The People

September 2, 2024
Library Management Database Schema and SQL Implementation

Overview of the Database Schema

The library management system consists of three main entities: Books, Members, and Loans. Each
entity has specific attributes that facilitate the management of library operations. Below is a brief
description of each entity:

1. Books: This table stores information about the books available in the library.

 Attributes:

 ISBN: Unique identifier for each book.

 Title: The title of the book.

 Author: The author of the book.

 Genre: The genre of the book (e.g., Fiction, Non-Fiction).

 Quantity: The number of copies available in the library.

2. Members: This table contains details about library members.

 Attributes:

 MemberID: Unique identifier for each member.

 Name: The name of the member.

 Email: The email address of the member.

 Phone: The phone number of the member.

3. Loans: This table tracks which members have borrowed which books and when they are due
back.

 Attributes:

 LoanID: Unique identifier for each loan transaction.

 MemberID: Foreign key referencing the Members table.

 ISBN: Foreign key referencing the Books table.

 LoanDate: The date when the book was borrowed.

 ReturnDate: The date when the book is returned.

SQL Script
Below is the SQL script to create the database schema, insert records, and perform various operations
on the library database:

sql

-- Create Books table

CREATE TABLE Books (

ISBN VARCHAR(13) PRIMARY KEY,

Title VARCHAR(255) NOT NULL,

Author VARCHAR(255) NOT NULL,

Genre VARCHAR(100),

Quantity INT NOT NULL

);

-- Create Members table

CREATE TABLE Members (

MemberID INT AUTO_INCREMENT PRIMARY KEY,

Name VARCHAR(255) NOT NULL,

Email VARCHAR(255) UNIQUE NOT NULL,

Phone VARCHAR(15)

);

-- Create Loans table

CREATE TABLE Loans (

LoanID INT AUTO_INCREMENT PRIMARY KEY,

MemberID INT,

ISBN VARCHAR(13),

LoanDate DATE,
ReturnDate DATE,

FOREIGN KEY (MemberID) REFERENCES Members(MemberID),

FOREIGN KEY (ISBN) REFERENCES Books(ISBN)

);

-- Insert new records into Books table

INSERT INTO Books (ISBN, Title, Author, Genre, Quantity) VALUES

('978-3-16-148410-0', 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 5),

('978-0-14-028333-4', '1984', 'George Orwell', 'Dystopian', 8),

('978-0-06-112008-4', 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 3);

-- Insert new records into Members table

INSERT INTO Members (Name, Email, Phone) VALUES

('John Doe', 'johndoe@[Link]', '1234567890'),

('Jane Smith', 'janesmith@[Link]', '0987654321');

-- Insert new records into Loans table

INSERT INTO Loans (MemberID, ISBN, LoanDate, ReturnDate) VALUES

(1, '978-3-16-148410-0', '2024-10-01', NULL),

(2, '978-0-14-028333-4', '2024-10-02', NULL);

-- Query to retrieve all information about books borrowed by a specific member

SELECT [Link], [Link], [Link]

FROM Books b

JOIN Loans l ON [Link] = [Link]


WHERE [Link] = 1;

-- Update the quantity of a particular book in the Books table

UPDATE Books

SET Quantity = Quantity - 1

WHERE ISBN = '978-3-16-148410-0';

-- Delete a member record from the Members table

DELETE FROM Members

WHERE MemberID = 2;

Explanation of SQL Operations

1. Creating Tables: The script begins by creating three tables with appropriate data types and
constraints to ensure data integrity. Primary keys uniquely identify records in each table.

2. Inserting Records: New records are inserted into each table using the INSERT INTO statement.
This populates our database with initial data.

3. Retrieving Data: A query retrieves all information about books borrowed by a specific member
(in this case, MemberID = 1). It uses a JOIN operation to combine data from both the Books and
Loans tables.

4. Updating Records: The quantity of a specific book is updated using an UPDATE statement to
reflect that one copy has been loaned out.

5. Deleting Records: A member record is deleted from the Members table using
a DELETE statement based on their unique MemberID.

Conclusion

This assignment demonstrates how to implement a simple library management system using SQL. By
defining clear database constructs and executing various operations, we can effectively manage library
resources and member information.

References
Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Harrington, J. L. (2016). Relational Database Design Clearly Explained (3rd ed.). Morgan Kaufmann. Feel
free to modify any part of this template or let me know if you need additional information!

Common questions

Powered by AI

The schema design facilitates efficient book borrowing tracking by using a relational structure with clear relationships between tables. The 'Loans' table connects 'Books' and 'Members' through foreign keys. This allows the system to record which members have borrowed which books and track loan dates, enabling efficient queries and updates .

Not having the 'Quantity' column in the 'Books' table would prevent the library system from easily tracking available copies of each book. This could lead to over-borrowing and stock discrepancies, making it difficult to maintain accurate inventory levels and affecting the system's ability to efficiently manage resources .

Foreign key constraints in the 'Loans' table maintain referential integrity between loans, members, and books by ensuring that each loan corresponds to an existing member and book. This prevents the creation of invalid loan records. However, it also restricts entries that might be temporarily unavailable, such as if a book or member is excluded after deletion, potentially requiring additional handling processes to manage deletions .

Constraints on the 'ReturnDate' column are crucial to ensure that returned dates are reasonable and valid, preventing future-dated or incorrect returns. Implementing such constraints upholds data integrity by ensuring entries reflect actual events and helps in timely returns tracking, reducing inventory inaccuracies .

Enforcing a UNIQUE constraint on the 'Email' column ensures that each member has a distinct email address, reducing data redundancy and preventing duplicate records. This can improve data integrity and simplify communication by ensuring each email address uniquely identifies a member .

The database schema's design directly influences library operations by structuring data to reflect real-world entities and their relationships. By organizing data into 'Books', 'Members', and 'Loans' tables with key relationships through foreign keys, the schema facilitates efficient data retrieval, updating, and integrity maintenance. This structured approach allows for scalable and accurate tracking of loans, member activities, and inventory levels, essential for operational efficiency .

The SQL scripts provided perform several key functions: creating tables with constraints (ensuring unique and non-nullable columns), inserting initial records, retrieving data with JOIN operations, updating records to reflect changes in book quantities, and deleting records based on unique identifiers. These functions ensure data integrity by enforcing primary key constraints, maintaining referential integrity with foreign keys, and allowing structured data manipulation .

JOIN operations enhance data retrieval by allowing the combination of data across multiple related tables, such as retrieving the list of books borrowed by a specific member by joining the 'Books' and 'Loans' tables. This enables complex queries, combining data from different sources to provide comprehensive insights into library operations .

Deleting a member record when they have active loans could lead to orphaned records in the 'Loans' table, violating data integrity. Without the member's information, it becomes challenging to track who has borrowed specific books, potentially complicating returns and inventory management. Constraints or checks should be in place to prevent deletion of members with active loans .

Using AUTO_INCREMENT for 'MemberID' and 'LoanID' is effective as it automatically generates unique identifiers for each new record, ensuring that each member and loan transaction can be distinctly identified. This feature simplifies data management by eliminating manual ID tracking and reducing the likelihood of duplicate entries .

You might also like