support@essaywritingexpert.orgINFS2201 – Database Management Systems
Assignment 2 – Implementing the Model
In this assignment you will take an ERD that was developed for the library from Assignment 1 and implement this in MySQL along with some sample data.
Regardless of your solution for assignment 1, you must use the following physical ERD for assignment 2.
INFS2201 – Database Management Systems
Data Definition Language Statements
You will need to write the DDL statements to create all the tables in the provided physical model. This requires creating the correct constraints including primary and foreign keys as well.
You will need to submit the SQL file used to remove and create the database script with comments. A2_60xxxxxx_C.SQL
You will need to write a “clean up” script that can be used to remove only the tables for your project. A2_60xxxxxx_D.SQL Submit all your queries with documentation in a single SQL named A2_60xxxxxx_Q.SQL
Sample Data
You need to load sample data for the 14 tables. The more real the data looks, the better you can understand how the database is structured. The amount of sample data does depend on the table itself; some tables will need more rows than others. These are the minimum requirements; more data will make more sense if you can at least double the number of sample data rows/records of these.
• Author: 5 records
• Member: 4 records
• Staff: 3 records
• Book: 10 records
• Book_Location: Each book has a designated branch, with two books available in both branches.
• Book_Author: Each book has a designated author, with two books featuring multiple authors.
• User_Account: one for each staff and each member
• Checkout 12 records, 2 books were not borrowed and 2 were borrowed by 3 different members.
• Category: 4 unique category records
• Preference: 2 preference records for each member. One category was not selected by any member.
• Reviews: 5 book review records by different members.
• Branch: 2 library branches
• Event: 5 upcoming events
• Showcased_Book: 4 records
To create the sample data, you may utilize any one of the following sources:
• manually – Just type data yourself.
• mockaroo.com – A very useful website that will generate random data.
• ChatGPT – You are allowed to use this tool for generating the sample data only.
• Write a Python Program! – Generates data more easily than any other method.
• You may use a mixture of these for the different tables.
Whatever method you use, you must cite how you generated the contents for the table. This must be saved in a single file called A2_60xxxxxx_Samples.SQL
INFS2201 – Database Management Systems
Queries
Write queries for each of the following things that would likely be of interest to the library system. Please note that your data will be completely different because we all have random data.
Query 1
The library wants to be able to display information on their website page about the total number of books available across all branches. It’s important to note that some books are available in multiple branches, meaning they may be counted more than once in this total
Query 2
Write a query that counts the number of books in each category. Note that some categories may not have any assigned books, in which case the report will display a count of zero for those categories.
The report would look like this:
| Category | Book Count |
| Fiction | 5 |
| Non-Fiction | 1 |
| Mystery | 2 |
| Fantasy | 0 |
Query 3
When new books are added to the library, a catalog entry must be created that includes the title of the book, the names of the authors, and the publication date. If a book has multiple authors, it will be shown multiple times in the list. The output should be ordered by the book title and author name.
The report would look like this:
| Title | Author Name | Publication Date |
| The Great Gatsby | F. Scott Fitzgerald | 4/10/1925 |
| A Game of Thrones | George R.R. Martin | 8/6/1996 |
| The Lord of the Rings | J.R.R. Tolkien | 7/29/1954 |
| The Three-Body Problem | Liu Cixin | 5/17/2008 |
| The Three-Body Problem | Ken Liu | 5/17/2008 |
INFS2201 – Database Management Systems
Query 4
Write a query to identify the titles of books that are due for return, along with the member’s name and email. Include the number of days that have passed since the due date and ensure that the query excludes any books that have already been returned.
The report would look like this:
| Title | Member Name | Due Date | Days Passed | |
| The Great Gatsby | John Doe | john.doe@example.com | 9/15/2024 | 20 |
| To Kill a Mockingbird | Emily Johnson | emily.j@example.com | 9/20/2024 | 15 |
| Pride and Prejudice | Michael Brown | michael.b@example.com | 9/12/2024 | 22 |
Query 5
Write the query/queries required to create a new event for a book signing that runs from the start of the current semester until the end of the semester. The event will feature 5 books (you choose) and will take place at multiple library branches. After creating the event, run a query to display the title of the book, the authors’ names, the event dates, and the branch names.
The report would look like this:
| Book Title | Author Name | Event Start Date | Event End Date | Branch Name |
| Brave New World | Aldous Huxley | 9/10/2024 | 9/10/2024 | Main Library |
| Brave New World | Aldous Huxley | 9/15/2024 | 9/15/2024 | Community Library |
| The Great Gatsby | F. Scott Fitzgerald | 9/1/2024 | 9/5/2024 | Main Library |
| The Great Gatsby | F. Scott Fitzgerald | 9/6/2024 | 9/10/2024 | Main Library |
| To Kill a Mockingbird | Harper Lee | 9/20/2024 | 9/25/2024 | Main Library |
| Pride and Prejudice | Jane Austen | 10/1/2024 | 10/5/2024 | Community Library |
| The Catcher in the Rye | J.D. Salinger | 10/10/2024 | 10/15/2024 | Main Library |
Due Date
This assignment is due on Saturday, Oct 26, 2024, at 11:59pm. Late submissions will NOT be accepted.
INFS2201 – Database Management Systems
| Criteria | Exemplary (20) | Satisfactory (14) | Developing (8) | Unsatisfactory (0) |
| DDL Statement Creation (20 Points) | DDL statements are perfectly crafted to reflect the physical ERD. Every table, primary key, foreign key, and constraint is correctly defined without errors. This level demonstrates a deep understanding of how to translate an ERD into a functional database schema. | DDL statements are mostly correct with only minor mistakes or omissions, such as slight errors in data types or missing a non-critical constraint. The student shows a good understanding of schema translation but may overlook finer details. | DDL statements are present but contain several inaccuracies or omissions that could affect database integrity or performance, such as incorrect primary/foreign key relationships or missing several constraints. There is room for improvement in understanding or attention to detail. | DDL statements are largely incorrect or missing, showing a significant misunderstanding of how to implement the ERD physically. Critical components like tables or keys are incorrectly defined or not included, severely impacting the schema’s functionality. |
| Data Generation and Insertion (10 Points) | Data inserted into the database is comprehensive, covering all tables and accurately representing relationships and constraints within the domain. The dataset includes a variety of cases, from typical to edge cases, effectively testing the schema’s robustness and flexibility. | Data covers most scenarios and tables, with minor gaps. The inserted data is realistic and respects table relationships, but some edge cases or constraints are not fully explored, offering a solid but not exhaustive test of the database schema. | Data insertion covers only the basic scenarios, leaving out complex relationships or edge cases. Some tables may have sparse or unrealistic data, indicating a need for a more thorough approach to testing the database schema. | Data insertion is minimal or incorrect, with many tables left empty or filled with data that doesn’t respect defined relationships or constraints, indicating a lack of effort or understanding in how to populate the database schema realistically. |
| SQL Query Writing (20 Points) | Queries are perfectly written, correctly utilizing joins, subqueries, and aggregation functions to retrieve data as specified. Queries are optimized for performance and clarity, demonstrating advanced SQL knowledge and the ability to handle complex data retrieval tasks. | Queries meet most requirements with minor logical errors or inefficiencies. Joins and aggregation are used correctly, but there might be simpler or more effective ways to achieve the same results. Demonstrates a good grasp of SQL with minor areas for improvement. | Queries show an attempt to meet requirements but contain several errors or inefficiencies. Basic joins may be used, but aggregation or more complex SQL features are incorrectly implemented or missing, indicating a developing understanding of SQL. | Queries are incorrect, missing, or fail to meet the specified requirements, indicating a significant misunderstanding of how to retrieve data from the database or use SQL effectively. |