Changes between Initial Version and Version 1 of DDL FINAL SCRIPT


Ignore:
Timestamp:
09/25/25 21:09:03 (5 weeks ago)
Author:
213231
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DDL FINAL SCRIPT

    v1 v1  
     1
     2{{{
     3-- Drop tables and views if exist
     4DROP TABLE IF EXISTS aktivniprodukti CASCADE;
     5DROP VIEW IF EXISTS aktivniprodukti CASCADE;
     6
     7-- Customer
     8CREATE TABLE customer (
     9  CustomerID INT PRIMARY KEY,
     10  CustomerName VARCHAR(100),
     11  CustomerSurName VARCHAR(100),
     12  Email VARCHAR(100),
     13  Address VARCHAR(255),
     14  CustomerContact VARCHAR(20),
     15  Password VARCHAR(255) NOT NULL
     16);
     17
     18-- CustomerFeedback
     19CREATE TABLE customerfeedback (
     20  FeedbackID INT PRIMARY KEY,
     21  CustomerID INT,
     22  ProductID INT,
     23  Rating INT CHECK (Rating BETWEEN 1 AND 5),
     24  Comment TEXT,
     25  FeedbackDate DATE,
     26  TransactionID INT,
     27  CONSTRAINT customerfeedback_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
     28  CONSTRAINT customerfeedback_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
     29  CONSTRAINT customerfeedback_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
     30);
     31
     32-- Employee
     33CREATE TABLE employee (
     34  EmployeeID INT PRIMARY KEY,
     35  EmployeeName VARCHAR(100),
     36  EmployeeSurName VARCHAR(100),
     37  Position VARCHAR(100),
     38  Department VARCHAR(100),
     39  Email VARCHAR(100),
     40  Password VARCHAR(100)
     41);
     42
     43-- Maintenance
     44CREATE TABLE maintenance (
     45  MainID INT PRIMARY KEY,
     46  EmployeeID INT,
     47  ProductID INT,
     48  MainDate DATE,
     49  Description TEXT,
     50  Cost NUMERIC(10,2),
     51  Status VARCHAR(10) DEFAULT 'Pending',
     52  StartTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     53  EndTime TIMESTAMP,
     54  CONSTRAINT maintenance_status_check CHECK (Status IN ('Pending', 'Completed')),
     55  CONSTRAINT maintenance_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
     56  CONSTRAINT maintenance_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
     57);
     58
     59-- Procurement
     60CREATE TABLE procurement (
     61  TransactionID INT PRIMARY KEY,
     62  EmployeeID INT,
     63  CustomerID INT,
     64  ProductID INT,
     65  ProcurementDate DATE,
     66  Quantity INT,
     67  Status VARCHAR(10) DEFAULT 'Pending',
     68  Notified BOOLEAN DEFAULT FALSE,
     69  GroupID VARCHAR(255),
     70  CONSTRAINT procurement_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
     71  CONSTRAINT procurement_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
     72  CONSTRAINT procurement_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
     73  CONSTRAINT procurement_status_check CHECK (Status IN ('Pending', 'Approved', 'Rejected'))
     74);
     75
     76-- Product
     77CREATE TABLE product (
     78  ProductID INT PRIMARY KEY,
     79  Model VARCHAR(100),
     80  Price NUMERIC(10,2),
     81  LicensePlate VARCHAR(50),
     82  Status VARCHAR(15) NOT NULL,
     83  CONSTRAINT product_status_check CHECK (Status IN ('available', 'sold', 'rented', 'maintenance'))
     84);
     85
     86-- T_Type
     87CREATE TABLE t_type (
     88  TransactionID INT PRIMARY KEY,
     89  Type VARCHAR(10) NOT NULL,
     90  Duration INT,
     91  MonthlyPay NUMERIC(10,2),
     92  TotalPrice NUMERIC(10,2),
     93  CONSTRAINT t_type_type_check CHECK (Type IN ('Rent', 'Buy')),
     94  CONSTRAINT t_type_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
     95);
     96
     97-- Trailer
     98CREATE TABLE trailer (
     99  ProductID INT PRIMARY KEY,
     100  Capacity NUMERIC(10,2),
     101  CONSTRAINT trailer_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
     102);
     103
     104-- Truck
     105CREATE TABLE truck (
     106  ProductID INT PRIMARY KEY,
     107  HP INT,
     108  CONSTRAINT truck_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
     109);
     110
     111-- Views (Customer views product)
     112CREATE TABLE views (
     113  CustomerID INT NOT NULL,
     114  ProductID INT NOT NULL,
     115  PRIMARY KEY (CustomerID, ProductID),
     116  CONSTRAINT views_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
     117  CONSTRAINT views_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
     118);
     119
     120-- Wallet
     121CREATE TABLE wallet (
     122  WalletID SERIAL PRIMARY KEY,
     123  CustomerID INT NOT NULL,
     124  Balance NUMERIC(10,2) NOT NULL DEFAULT 0.00,
     125  CardNumber VARCHAR(16),
     126  ExpiryDate DATE,
     127  CVV VARCHAR(4),
     128  CardHolderName VARCHAR(100),
     129  CONSTRAINT wallet_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
     130);
     131
     132-- View aktivniprodukti (PostgreSQL syntax)
     133CREATE OR REPLACE VIEW aktivniprodukti AS
     134SELECT product.ProductID,
     135       product.Model,
     136       product.Price,
     137       product.LicensePlate
     138FROM product
     139WHERE product.Status = 'available';
     140
     141}}}