DDL (for MyShop)

Create Schema:
```
CREATE SCHEMA myshop;
```
- CREATE SCHEMA: Creates a new schema in the database.
- Creates a schema named myshop.
Create Products Table:
```
CREATE TABLE myshop.products (
  "product_id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" varchar(50),
  "quantity" integer DEFAULT 0
);
```
- CREATE TABLE: Creates a new table.
- BIGINT GENERATED BY DEFAULT AS IDENTITY: Creates an auto-incrementing column for unique identifiers.
- PRIMARY KEY: Defines the primary key for the table.
- DEFAULT: Sets a default value for the column.
- Creates a products table with product_id, name, and quantity.

Create Categories Table:

CREATE TABLE myshop.categories (
  "category_id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" varchar(50)
);

Creates a categories table with category_id and name.

Create Clients Table:

CREATE TABLE myshop.clients (
  "client_id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" varchar(50)
);

Creates a clients table with client_id and name.

Create Orders Table:
```
CREATE TABLE myshop.orders (
  "order_id" bigint PRIMARY KEY,
  "client_id" bigint,
  "order_date" date DEFAULT (now())
);
```
- now(): A function that returns the current date and time.
- Creates an orders table with order_id, client_id, and order_date.
Create Product_Category Table:
```
CREATE TABLE myshop.product_category (
  "product_id" bigint,
  "category_id" bigint,
  PRIMARY KEY ("product_id", "category_id")
);
```
- PRIMARY KEY ("product_id", "category_id"): Defines a composite primary key.
- Creates a product_category table to associate products with categories using product_id and category_id.

Create Ordered_Products Table:

CREATE TABLE myshop.ordered_products (
  "product_id" bigint,
  "order_id" bigint,
  "quantity" integer,
  PRIMARY KEY ("product_id", "order_id")
);

Creates an ordered_products table to track ordered products using product_id, order_id, and quantity.

Add Foreign Key to Orders Table:
```
ALTER TABLE myshop.orders ADD FOREIGN KEY ("client_id") REFERENCES myshop.clients ("client_id");
```
- ALTER TABLE: Modifies an existing table.
- ADD FOREIGN KEY: Adds a foreign key constraint to a column.
- Adds a foreign key constraint on client_id in the orders table referencing client_id in the clients table.
Add Foreign Key to Product_Category Table (Category ID):
```
ALTER TABLE myshop.product_category ADD FOREIGN KEY ("category_id") REFERENCES myshop.categories ("category_id");
```
- Adds a foreign key constraint on category_id in the product_category table referencing category_id in the categories table.
Add Foreign Key to Product_Category Table (Product ID):
```
ALTER TABLE myshop.product_category ADD FOREIGN KEY ("product_id") REFERENCES myshop.products ("product_id");
```
- Adds a foreign key constraint on product_id in the product_category table referencing product_id in the products table.
Add Foreign Key to Ordered_Products Table (Order ID):
```
ALTER TABLE myshop.ordered_products ADD FOREIGN KEY ("order_id") REFERENCES myshop.orders ("order_id");
```
- Adds a foreign key constraint on order_id in the ordered_products table referencing order_id in the orders table.
Add Foreign Key to Ordered_Products Table (Product ID):
```
ALTER TABLE myshop.ordered_products ADD FOREIGN KEY ("product_id") REFERENCES myshop.products ("product_id");
```
- Adds a foreign key constraint on product_id in the ordered_products table referencing product_id in the products table.