Description
This assignment involved using SQL to create and query a relational database for a library management system. After designing the ER model for the library system, I translated it into a relational schema and implemented it using SQL. The tasks included defining tables for books, authors, patrons, and transactions, then writing SQL queries to manipulate and retrieve the data. The goal was to apply SQL operations such as DDL, DML, Aggregate Functions, Nested Queries, and Correlated Queries to manage the library's database.
Features
DDL Operations: Created tables based on the ER model, defining relationships between entities (e.g., a "Borrowed Books" table to track which patrons borrow which books).
DML Operations: Inserted records for books, patrons, authors, and transactions, while also updating the status of borrowed books and deleting records as needed.
Aggregate Functions: Used SQL functions like COUNT, AVG, and SUM to analyze borrowing patterns and calculate metrics, such as the total number of books borrowed by each patron.
Nested and Correlated Queries: Wrote queries to retrieve data such as finding all patrons who have borrowed more than three books or identifying books that have not been checked out in the past six months.
Structured Enquiry: Implemented complex queries to generate reports, filter data based on multiple conditions (e.g., books by a certain author or patrons who have overdue books), and ensure the system supports the library’s daily operations.
Challenges and Solutions
Handling Many-to-Many Relationships Efficiently: The relationship between patrons and borrowed books was many-to-many, which required creating a linking table ("Borrowed Books"). This allowed efficient tracking of book loans, maintaining referential integrity across multiple tables.
Optimizing Queries for Performance: Writing complex queries with multiple joins and conditions was challenging in terms of performance. To solve this, I used indexing and query optimization techniques to ensure fast data retrieval, especially for larger datasets.
Reflection
This assignment helped me solidify my understanding of how to design and query a relational database from an ER model. I gained hands-on experience with SQL, improving my ability to write complex queries that combine multiple operations. I also learned the importance of database normalization, maintaining relationships between tables, and optimizing queries for performance. This assignment gave me a comprehensive view of how SQL is applied in real-world database management scenarios, especially in systems that need to manage and query large amounts of relational data.