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 withproduct_id
,name
, andquantity
.
-
Create Categories Table:
CREATE TABLE myshop.categories (
"category_id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"name" varchar(50)
);- Creates a
categories
table withcategory_id
andname
.
- Creates a
-
Create Clients Table:
CREATE TABLE myshop.clients (
"client_id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"name" varchar(50)
);- Creates a
clients
table withclient_id
andname
.
- Creates a
-
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 withorder_id
,client_id
, andorder_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 usingproduct_id
andcategory_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 usingproduct_id
,order_id
, andquantity
.
- Creates an
-
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 theorders
table referencingclient_id
in theclients
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 theproduct_category
table referencingcategory_id
in thecategories
table.
- Adds a foreign key constraint on
-
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 theproduct_category
table referencingproduct_id
in theproducts
table.
- Adds a foreign key constraint on
-
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 theordered_products
table referencingorder_id
in theorders
table.
- Adds a foreign key constraint on
-
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 theordered_products
table referencingproduct_id
in theproducts
table.
- Adds a foreign key constraint on