Final Project: Building a Comprehensive Database System

In this final lesson, you will apply all the knowledge you've gained throughout the course to design and implement a comprehensive database system. This project will encapsulate various aspects of database design, SQL queries, and best practices.

Objectives

• Design a relational database schema based on a given scenario.
• Implement the database using SQL.
• Populate the database with sample data.
• Write complex queries to retrieve and manipulate data.

Scenario

Imagine you are tasked with creating a database for a small online bookstore. The database should manage books, authors, customers, and orders. Here’s a brief overview of the entities involved:

• Books: Each book has an ID, title, genre, price, and author.
• Authors: Each author has an ID, name, and biography.
• Customers: Each customer has an ID, name, email, and phone number.
• Orders: Each order has an ID, customer ID, book ID, order date, and quantity.

Step 1: Designing the Database Schema

Start by designing the tables based on the entities described above. Here’s a suggested schema:

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Biography TEXT
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Genre VARCHAR(50),
    Price DECIMAL(10, 2) NOT NULL,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(15)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    BookID INT,
    OrderDate DATE,
    Quantity INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

Step 2: Implementing the Database

Once the schema is designed, you can implement it in your SQL environment. Run the above SQL commands to create the tables.

Step 3: Populating the Database with Sample Data

Now that the tables are created, let’s insert some sample data:

INSERT INTO Authors (AuthorID, Name, Biography) VALUES
(1, 'George Orwell', 'English novelist and essayist'),
(2, 'J.K. Rowling', 'British author, best known for the Harry Potter series');

INSERT INTO Books (BookID, Title, Genre, Price, AuthorID) VALUES
(1, '1984', 'Dystopian', 15.99, 1),
(2, 'Harry Potter and the Sorcerer''s Stone', 'Fantasy', 29.99, 2);

INSERT INTO Customers (CustomerID, Name, Email, Phone) VALUES
(1, 'Alice Smith', 'alice@example.com', '123-456-7890'),
(2, 'Bob Johnson', 'bob@example.com', '098-765-4321');

INSERT INTO Orders (OrderID, CustomerID, BookID, OrderDate, Quantity) VALUES
(1, 1, 1, '2023-10-01', 1),
(2, 2, 2, '2023-10-02', 2);

Step 4: Writing Queries

Now that you have a populated database, you can write queries to retrieve and manipulate data. Here are some examples:

Retrieve all books with their authors:

SELECT b.Title, a.Name AS AuthorName, b.Price
FROM Books b
JOIN Authors a ON b.AuthorID = a.AuthorID;

Find total orders by customer:

SELECT c.Name, COUNT(o.OrderID) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID;

Best Practices

• Normalization: Ensure your database is normalized to avoid redundancy.
• Data Types: Use appropriate data types for each field to optimize storage and performance.
• Constraints: Implement foreign keys and other constraints to maintain data integrity.

Common Mistakes: - Forgetting to set up foreign key relationships can lead to data inconsistency. - Not handling NULL values properly can cause issues in queries and data integrity.

Conclusion

In this lesson, you designed and implemented a comprehensive database system. You practiced creating tables, inserting data, and writing queries, which are essential skills for any database professional. Remember to keep practicing and exploring advanced SQL features to enhance your skills further.

Exercise 1: Modify the Schema

Add a new table called Reviews that allows customers to leave reviews for books. The table should include fields for ReviewID, BookID, CustomerID, Rating, and Comment. Write the SQL command to create this table.

Exercise 2: Complex Query

Write a SQL query to find the average price of books for each genre and list the results in descending order of average price.

Exercise 3: Data Deletion

Write a SQL command to delete all orders made by a specific customer (e.g., CustomerID = 1). Ensure that this does not violate any foreign key constraints.

• You designed a database schema for an online bookstore, implementing tables for authors, books, customers, and orders.
• You populated the database with sample data to simulate real-world scenarios.
• You wrote complex SQL queries to retrieve and manipulate data effectively.
• Best practices include normalization, appropriate data types, and maintaining data integrity with constraints.