Changes between Initial Version and Version 1 of DML final

Timestamp:

09/29/25 18:28:18 (3 weeks ago)

Author:

213231

Comment:

--

DML final

@@ +1 @@
+
+{{{
+CREATE TABLE customer (
+  CustomerID integer PRIMARY KEY,
+  CustomerName varchar(100),
+  CustomerSurName varchar(100),
+  Email varchar(100),
+  Address varchar(255),
+  CustomerContact varchar(20),
+  Password varchar(255) NOT NULL
+);
+
+CREATE TABLE employee (
+  EmployeeID integer PRIMARY KEY,
+  EmployeeName varchar(100),
+  EmployeeSurName varchar(100),
+  Position varchar(100),
+  Department varchar(100),
+  Email varchar(100),
+  Password varchar(100)
+);
+
+CREATE TABLE product (
+  ProductID integer PRIMARY KEY,
+  ProcurementID integer NOT NULL,
+  ProcurementRequestID integer NOT NULL,
+  Model varchar(100),
+  Price numeric(10,2),
+  LicensePlate varchar(50),
+  Status varchar(15) NOT NULL,
+  CONSTRAINT product_status_check CHECK (Status IN ('available','sold','rented','maintenance')),
+  CONSTRAINT procurement_fk FOREIGN KEY(ProcurementID) REFERENCES procurement(TransactionID), 
+  CONSTRAINT procurement_request_fk FOREIGN KEY(ProcurementRequestID) REFERENCES procurement_request(RequestID) 
+);
+
+CREATE TABLE procurement (
+  TransactionID integer PRIMARY KEY,
+  EmployeeID integer,
+  CustomerID integer,
+  ProcurementDate date,
+  Quantity integer,
+  Status varchar(10) DEFAULT 'Pending',
+  Notified boolean DEFAULT FALSE,
+  GroupID varchar(255),
+  CONSTRAINT procurement_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
+  CONSTRAINT procurement_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
+);
+
+CREATE TABLE maintenance (
+  MainID integer PRIMARY KEY,
+  MainDate date,
+  Description text,
+  Cost numeric(10,2),
+  Status varchar(10) DEFAULT 'Pending',
+  StartTime timestamp DEFAULT CURRENT_TIMESTAMP,
+  EndTime timestamp,
+  CONSTRAINT maintenance_status_check CHECK (Status IN ('Pending','Completed'))
+);
+
+CREATE TABLE trailer (
+  ProductID integer PRIMARY KEY,
+  Capacity numeric(10,2),
+  CONSTRAINT trailer_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
+);
+
+CREATE TABLE truck (
+  ProductID integer PRIMARY KEY,
+  HP integer,
+  CONSTRAINT truck_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
+);
+
+CREATE TABLE views (
+  CustomerID integer NOT NULL,
+  ProductID integer NOT NULL,
+  PRIMARY KEY (CustomerID, ProductID),
+  CONSTRAINT views_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
+  CONSTRAINT views_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
+);
+
+CREATE TABLE wallet (
+  WalletID serial PRIMARY KEY,
+  CustomerID integer NOT NULL,
+  Balance numeric(10,2) NOT NULL DEFAULT 0.00,
+  CardNumber varchar(16),
+  ExpiryDate date,
+  CVV varchar(4),
+  CardHolderName varchar(100),
+  CONSTRAINT wallet_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
+);
+
+CREATE TABLE customerfeedback (
+  FeedbackID integer PRIMARY KEY,
+  CustomerID integer,
+  ProductID integer,
+  Rating integer CHECK (Rating BETWEEN 1 AND 5),
+  Comment text,
+  FeedbackDate date,
+  TransactionID integer,
+  CONSTRAINT feedback_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
+  CONSTRAINT feedback_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
+  CONSTRAINT feedback_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
+);
+
+CREATE TABLE procurement_request (
+  RequestID serial PRIMARY KEY,
+  CustomerID integer NOT NULL,
+  WalletID integer NOT NULL,
+  ProcurementID integer NOT NULL,
+  Quantity integer DEFAULT 1,
+  RequestedAt timestamp DEFAULT CURRENT_TIMESTAMP,
+  Status varchar(10) DEFAULT 'Pending',
+  PaymentMethod varchar(20),
+  PaymentStatus varchar(10) DEFAULT 'Reserved',
+  MonthlyPay numeric(10,2),
+  TotalPrice numeric(10,2),
+  Duration integer,
+  CardID integer,
+  TransactionType varchar(10) DEFAULT 'Buy',
+  GroupID varchar(36),
+  CONSTRAINT procurement_request_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
+  CONSTRAINT wallet_fk FOREIGN KEY (WalletID) REFERENCES wallet(WalletID),
+  CONSTRAINT procurement_fk FOREIGN KEY (ProcurementID) REFERENCES procurement(TransactionID),
+  CONSTRAINT procurement_request_status_check CHECK (Status IN ('Pending','Approved','Rejected')),
+  CONSTRAINT procurement_request_paymentstatus_check CHECK (PaymentStatus IN ('Reserved','Charged','Refunded')),
+  CONSTRAINT procurement_request_transactiontype_check CHECK (TransactionType IN ('Buy','Rent'))
+);
+
+CREATE TABLE t_type (
+  TransactionID integer PRIMARY KEY,
+  Type varchar(10) NOT NULL,
+  Duration integer,
+  MonthlyPay numeric(10,2),
+  TotalPrice numeric(10,2),
+  CONSTRAINT t_type_type_check CHECK (Type IN ('Rent','Buy')),
+  CONSTRAINT t_type_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
+);
+
+CREATE TABLE customer_product (
+  CustomerID integer NOT NULL,
+  ProductID integer NOT NULL,
+  CONSTRAINT customer_product_id PRIMARY KEY (CustomerID, ProductID),
+  CONSTRAINT customer_product_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
+  CONSTRAINT customer_product_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
+);
+
+CREATE TABLE product_maintenance (
+  ProductID integer NOT NULL,
+  MaintenanceID integer NOT NULL,
+  CONSTRAINT product_maintenance_id PRIMARY KEY (ProductID, MaintenanceID),
+  CONSTRAINT product_maintenance_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
+  CONSTRAINT product_maintenance_maintenance_fk FOREIGN KEY (MaintenanceID) REFERENCES maintenance(MainID)
+);
+
+CREATE TABLE employee_maintenance (
+  EmployeeID integer NOT NULL,
+  MaintenanceID integer NOT NULL,
+  CONSTRAINT employee_maintenance_id PRIMARY KEY (EmployeeID, MaintenanceID),
+  CONSTRAINT employee_maintenance_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
+  CONSTRAINT employee_maintenance_maintenance_fk FOREIGN KEY (MaintenanceID) REFERENCES maintenance(MainID)
+);
+}}}