Online Store Database:

Design a relational database schema for an online store that sells products. Include tables for customers, orders, products, and inventory.

$1\mathrm{.}1$ Tables

These tables form the foundation for managing bookstore$-$related data.

Feel free to adjust the column names, data types, and constraints based on your specific requirements.

Feel free to expand the schema by adding more tables related to inventory, transactions, or other relevant aspects!

$1\mathrm{.}1\mathrm{.}1$ Books Table:

Stores information about books available in the store.

Includes columns for book ID$,$ title, author ID$,$ genre, and ISBN.

CREATE TABLE Books $($ book$\_$id INT PRIMARY KEY, title VARCHAR$(255),$ author$\_$id INT, publish$$er$\_$id INT, price DECIMAL$(10,2),)$; stock$\_$quantity INT

$1\mathrm{.}1\mathrm{.}2$ Authors Table:

Contains details about authors.

Columns include author ID$,$ first name, last name, and birth year.

CREATE TABLE Authors $($ author$\_$id INT PRIMARY KEY, $)$; author$\_$name VARCHAR$(100)$

$1\mathrm{.}1\mathrm{.}3$ Publishers Table:

Stores information about book publishers.

Columns include publisher ID$,$ publisher name, and location.

CREATE TABLE Publishers $($ publisher$\_$id INT PRIMARY KEY, publisher$\_$name VARCHAR$(100),$ location VARCHAR$(100)$

;

CIS $182-$ W$24$

Team Project $-$ Suggested Projects

Page $1$ of $8$

$1\mathrm{.}1\mathrm{.}4$ Customers Table:

Records customer details.

Includes columns for customer ID$,$ first name, last name, email, and phone number.

CREATE TABLE Customers $($

customer$\_$id INT PRIMARY KEY,

first$\_$name VARCHAR$(50),$

last$\_$name VARCHAR$(50),$

email VARCHAR$(100),$

phone$\_$number VARCHAR $(20),$

address VARCHAR$(255)$

;

$1\mathrm{.}1\mathrm{.}5$ Orders Table:

Tracks customer orders.

Columns include order ID$,$ customer ID$,$ order date, and total amount.

CREATE TABLE orders $($ order$\_$id INT PRIMARY KEY, customer$\_$id INT, order$\_$date DATE, total$\_$amount DECIMAL $(10,2),$

;

FOREI$$N KEY $($customer$\_$id$)$ REFERENCES customers$($customer$\_$id$)$

$1\mathrm{.}2$ Requirements

Implement CRUD $($Create$,$ Read, Update, Delete$)$ operations for managing customer data, product details, and order history.

Create complex queries to retrieve information such as top$-$selling products, customer order summaries, and inventory levels.