Question

Create an order table and an orderItems table in the books database to storc orders placed by customers. The order table should store an orderID, an orderDate and the email address of the customer who placed the order. [Note: You will need to modify the form in Exercise 11.6 to include the customer's e-mail address]. The order Items table should store the orderID, ISBN, price and quantity of cach book in the order. Modify process.jsp so that it stores the order information in the order and orderttems tables.

Short answer

Create 'orders' and 'orderItems' tables, add email to the form, and update process.jsp to store order data.

Step-by-step solution

Design the Database Schema

Define the structure for the new tables within the existing 'books' database. Two tables are needed - 'orders' and 'orderItems'. The 'orders' table will contain columns: 'orderID', 'orderDate', and 'email', and the 'orderItems' table will contain: 'orderID', 'ISBN', 'price', and 'quantity'.

Create Tables

Write and execute SQL commands to create the 'orders' and 'orderItems' tables: 1. For the 'orders' table: ``` CREATE TABLE orders ( orderID INT PRIMARY KEY AUTO_INCREMENT, orderDate DATE, email VARCHAR(255) ); ``` 2. For the 'orderItems' table: ``` CREATE TABLE orderItems ( orderID INT, ISBN VARCHAR(13), price DECIMAL(10,2), quantity INT, FOREIGN KEY (orderID) REFERENCES orders(orderID) ); ```

Modify Form in Exercise 11.6

Add a field to capture the customer's email address in the form used to place orders. Ensure that this field is properly named and connected to the input model that collects order data.

Update process.jsp to Insert Order Data

Edit 'process.jsp' to handle inserting the order data into the 'orders' and 'orderItems' tables. This involves capturing order details from the form, executing an SQL insert into the 'orders' table first to create the order, and then inserting each book in the order to the 'orderItems' table: - Insert into 'orders': ```jsp String orderQuery = "INSERT INTO orders (orderDate, email) VALUES (?, ?)"; // Prepare and execute the query using Java's PreparedStatement ``` - Insert items into 'orderItems': ```jsp String itemQuery = "INSERT INTO orderItems (orderID, ISBN, price, quantity) VALUES (?, ?, ?, ?)"; // Loop through books in the order and use PreparedStatement to execute ```

Key concepts

SQL Commands

SQL commands are the backbone of managing data in a relational database. They are used to perform tasks such as creating tables, inserting data, updating records, and retrieving information. In the context of the solution provided, the SQL commands used were primarily for creating tables in a database, reflecting the design and schema structure that was predefined.

The command `CREATE TABLE` is a crucial instruction for defining new tables. For example, in our exercise, we used this command to establish the 'orders' and 'orderItems' tables. Each table was meticulously defined with specific columns, like `orderID` and `orderDate`, to store necessary information for each order.

SQL commands can also manage data through `INSERT`, `UPDATE`, `DELETE`, and `SELECT` commands. In our solution, the `INSERT` command is vital as it populates data into our freshly created tables. Using placeholders like `?` in the `INSERT` statements helps in executing dynamic queries while enhancing security by preventing SQL injection through Java's `PreparedStatement`.

These SQL commands need to be understood in their syntax and application, as they are essential to achieving efficient data management in databases.

Database Schema

A database schema represents the logical configuration of a database. It consists of tables, columns, data types, and the relationships between the tables. In our exercise, the importance of designing a carefu database schema is clear as it sets the foundation for how data will be stored and accessed.

The schema presented in the exercise includes two tables: 'orders' and 'orderItems'. The 'orders' table holds information about individual customer orders, such as the `orderID`, `orderDate`, and the customer's `email`. Meanwhile, the 'orderItems' table records specific details about each book within an order, including `orderID` (linking it back to the 'orders' table), `ISBN`, `price`, and `quantity`.

Understanding the concept of primary and foreign keys is crucial in schema design. A primary key, like `orderID` in the 'orders' table, uniquely identifies each record and ensures no duplicate entries. The 'orderItems' table references this primary key as a foreign key, establishing a logical link between tables and maintaining data integrity.

• Primary Key: Uniquely identifies records.
• Foreign Key: Establishes links between tables.
• Data Types: Defines the kind of data stored.

Careful planning during schema design helps in efficient query execution, data consistency, and avoids redundancy, making it a cornerstone of database management.

Entity-Relationship Model

The Entity-Relationship (ER) Model is a conceptual blueprint of a database's structure. It serves to visually represent entities, attributes, and relationships between different data elements, aiding in the organization of data requirements.

Entities represent objects or things you are storing information about, such as `orders` and `orderItems` in our exercise. Each entity contains attributes, which hold data values like `orderID`, `orderDate`, `email`, `ISBN`, `price`, and `quantity`. These attributes are the fields that constitute an entity's record.

The ER Model also highlights relationships which are the logical connections between entities. In our case, the relationship between 'orders' and 'orderItems' is exemplified by the `orderID` field. This linkage illustrates how the two tables interconnect, with 'orderItems' relying on 'orders' for associated data.

An ER Model benefits the database design by:

• Clarifying data requirements and relationships before actual database creation.
• Providing a diagrammatic representation, making it easier for stakeholders to understand.
• Assisting in the detection of potential design flaws like redundant data.

Designing with an ER model ensures a well-organized database, facilitating streamlined operations and accurate data retrieval.