Reference no: EM133898761

Question 1

Discuss the role of database normalization in minimizing data redundancy and ensuring data integrity. How does normalization differ from denormalization in terms of structure, performance, and use cases? Provide examples to support your comparison.

Question 2

Explain the significance of indexing in database systems. In what ways does indexing enhance query performance, and what are the potential trade-offs or limitations associated with its use? Support your answer with examples.

Question 3

Compare and contrast relational databases and NoSQL databases in terms of data structure, scalability, consistency, and typical use cases. Highlight the advantages and limitations of each approach.

Question 4

Case Study: Online Bookstore Database Design.

You have been contracted to design a comprehensive database system for an online bookstore named BookHaven. BookHaven is an emerging e-commerce platform that caters to a wide range of customers by offering an extensive collection of books across various categories, including fiction, non-fiction, academic textbooks, reference materials, self-help, and children's literature. The platform serves both casual readers and educational institutions, making it critical for the company to manage its operations efficiently and accurately.
Currently, BookHaven is experiencing rapid growth in its user base and product catalog, which has led to increased complexity in managing its data manually or through spreadsheets. To support scalability and ensure smooth daily operations, the management has decided to invest in a robust, scalable, and secure relational database system.
The system should handle the following key operations:
Managing a large and dynamic inventory of books with detailed metadata such as author, genre, publication year, and availability status.
Keeping accurate records of customer accounts, including personal details, purchase history, and preferred delivery addresses.
Processing orders placed by customers, where a single order may contain multiple books, and tracking the order status from placement to delivery.
Managing various payment methods, storing transaction details securely, and ensuring that payments are validated before orders are confirmed. Get Assignment Help from the Best tutors!
Additionally, the system should support future enhancements such as promotional discounts, book reviews, wishlists, and integration with external publishing databases. With these goals in mind, you are tasked with designing an efficient and normalized database schema that will form the backbone of BookHaven's digital infrastructure.
Data requirements:
Inventory Management:
Books should be categorized by genre, author, and publication year.
Each book entry must include details like ISBN, title, author, genre, price, quantity in stock, and a short description.
Customer Management:
Each customer should have a unique customer ID, full name, email address, and a default shipping address.
The system must store each customer's order history and allow for future personalization.
Order Processing:
Customers can place orders, and each order can include one or more books.
Each order should include a unique order ID, order date, and status (e.g., processing, shipped, delivered, cancelled).
The system should compute the total cost of each order automatically.
Payment and Transaction Management:
Customers can pay using credit/debit cards, PayPal, or gift cards.
Payment records should include the method used, transaction ID, and timestamp.
The system should validate payment and ensure successful order placement.
Required:
Design an Entity-Relationship Diagram (ERD) for the BookHaven database based on the requirements listed above. Your ERD should:
Clearly identify all entities and their attributes.
Define primary keys for each entity.
Show relationships between entities (including cardinality).
Use appropriate notation (Crow's Foot, Chen, or UML) as per your course standard.

Question 5
Map the ERD you designed in Question 4 into a set of tables. For each table, provide the following details:
Table name
Column names with appropriate data types
Identification of primary keys and foreign keys
Any relevant constraints (e.g., NOT NULL, UNIQUE, DEFAULT, CHECK)
Ensure that your mapping accurately reflects the structure and relationships defined in your ERD and adheres to principles of relational database design and normalization.