Reference no: EM133855978

Data Modelling and Database Development

Part (A) Project Demonstration

For the project demonstration, you are required to showcase the database for the given case study using MySQL Workbench.

Prepare a single SQL script file containing all the SQL queries for Part A (Q1 and Q2). The script should contain queries for creating tables, populating them with data, and answering the questions in Q2.

Assessment Details
For Part (A) of this assessment, you are required to design and implement a relational database management system (RDBMS) using MySQL.
For Part (B) of this assessment, you are required to discuss concurrency features for the given case study.

Assessment Description:

Consider the following case study (from AT2).

The Digital Book Expo Australia 2025 is a five-day online event that serves as a literary festival, celebrating books across various genres. Organized and managed by a dedicated team of volunteers, the event is known for its exceptional collection of high-quality pre-loved books. The Book Expo will facilitate e-purchase of the latest books across various genres. Your task is to design an online database system for the Digital Book Expo Australia.

A book is characterized by a title, serial number, genre, unit price, and a publisher. Publishers submit a list of their latest books to an Order Processing Officer of the Book Expo. The Officer compiles a list of books required and sends one or more orders to the Publishers via email. Each order contains the details of the requested books, including their titles, the quantity of each book, the subtotal cost for each book, and the total cost of the entire order. An Officer may be in charge of managing at least one order.

Each Publisher fulfills the orders by supplying the requested books. Upon receiving the books, an invoice is sent to the Book Expo Accountant, which includes the date and the total amount due for payment. A Publisher can have only one invoice, and the amount on this invoice represents the total cost of all the orders fulfilled.

Customers who wish to purchase books must first register as Members. Once registered, members can view books, read reviews, and compare them with other e-books. Members who wish to purchase books can select their preferred books from the Website. The shopping cart on the Website provides a summary of the selected books, including the title of each book, the quantity required for each book, the subtotal for each book, the total cost to be paid, the date of creation, and the status. After making the payment, the Member can print and/or save the receipt. It is important to note that a Member can have multiple carts, with each cart generating a separate receipt that includes the total payment, the date of payment, and the status of payment. The Book Expo will ship the books to the customers within 7 working days, while any books that have not been purchased will be donated.

Part A (Practical):

The relational schema for the case study above is as follows.

Publishers (publisherID, publisherFirstName, publisherLastName, publisherPhoneNumber, publisherStreet, publisherSuburb, publisherState, publisherPostCode, publisherEmail)

Officers (officerID, officerFirstName, officerLastName,officerPhoneNumber, officerStreet, officerSuburb, officerState, officerPostCode, officerEmail)

Members (memberID, memberFirstName, memberLastName,memberPhoneNumber, memberStreet, memberSuburb, memberState, memberPostCode, memberEmail)

Books (bookID, bookTitle, bookSerialNumber, bookPrice, bookGenre, publisherID)

Orders (orderID, orderDate, publisherID, officerID)

OrderDetails (orderDetailID, orderID, bookID, bookQuantity) Invoices (invoiceID, invoiceDate, invoiceAmount, publisherID) Carts (cartID, cartDate, cartStatus, memberID)
CartDetails (cartDetailID, cartID, bookID, bookQuantity)

Receipts (receiptID, cartID, totalPayment, dateOfPayment, statusOfPayment)

Q1. Use MySQL Workbench to create a database based on the relational schema provided above (You may refer to the Week 7 Tutorial for instructions on installing MySQL and MySQL Workbench).
Save your SQL script as {Student CIMID}_MBIS403_AT3.sql (e.g., CIM123456_MBIS403_AT3.sql).
Create a database named {Student CIMID}_AT3 (CIM123456_AT3).

Write SQL statements to create all the tables, specifying appropriate data types for each field, as well as primary keys and foreign keys where applicable.

Write SQL statements to insert meaningful data into all the tables.
Important: Make sure to populate all tables with sufficient data so that the questions in Q2 yield meaningful outputs and not empty query results.

Q2. Write SQL statements for the following questions.
List all the books that belong to a specific genre of your choice (e.g., Fiction). Display all fields from the Books table in the result.

List the books that have not been purchased by any Members. Display the bookID and booktitle.
For each member who has purchased books, list the memberID, cart IDs and the number of different books (bookIDs) in each cart, labeling the field as ‘NumberOfBooks'. Order the list from the highest to the lowest NumberOfBooks, and include a secondary sort by bookQuantity.

List the officers who have processed an order containing a BookID of your choice, and display their respective officerIDs. Duplicate entries for the same officer should not be included.
List the publishers who have received more than one order, along with the number of orders received, labeling the field as ‘NumberOfOrders'. Display the respective PublisherID, PublisherName, and NumberOfOrders. PublisherName should be obtained by concatenating PublisherFirstName and PublisherLastName.

Part B:

Research and critically analyse concurrency control in a multi-user database environment within the context of the given case study. Write a report of 2000 words presenting your analysis and recommendations. Get Professional Assignment Help Service Now!

You may discuss the need for concurrency control by including examples of database transactions from the case study that lead to concurrency issues in a multi-user database system. Propose suitable database techniques to address such issues using the theories and concepts studied in the course.

Include an introduction, main body, conclusion, and bibliography in the report. Structure the body with appropriate subsections as needed.