Changes between Version 1 and Version 2 of DML final


Ignore:
Timestamp:
09/29/25 18:36:01 (3 weeks ago)
Author:
213231
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DML final

    v1 v2  
    2121);
    2222
     23CREATE TABLE procurement (
     24  TransactionID integer PRIMARY KEY,
     25  EmployeeID integer,
     26  CustomerID integer,
     27  ProcurementDate date,
     28  Quantity integer,
     29  Status varchar(10) DEFAULT 'Pending',
     30  Notified boolean DEFAULT FALSE,
     31  GroupID varchar(255),
     32  CONSTRAINT procurement_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
     33  CONSTRAINT procurement_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
     34);
     35
     36CREATE TABLE wallet (
     37  WalletID serial PRIMARY KEY,
     38  CustomerID integer NOT NULL,
     39  Balance numeric(10,2) NOT NULL DEFAULT 0.00,
     40  CardNumber varchar(16),
     41  ExpiryDate date,
     42  CVV varchar(4),
     43  CardHolderName varchar(100),
     44  CONSTRAINT wallet_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
     45);
     46
     47CREATE TABLE procurement_request (
     48  RequestID serial PRIMARY KEY,
     49  CustomerID integer NOT NULL,
     50  WalletID integer NOT NULL,
     51  ProcurementID integer NOT NULL,
     52  Quantity integer DEFAULT 1,
     53  RequestedAt timestamp DEFAULT CURRENT_TIMESTAMP,
     54  Status varchar(10) DEFAULT 'Pending',
     55  PaymentMethod varchar(20),
     56  PaymentStatus varchar(10) DEFAULT 'Reserved',
     57  MonthlyPay numeric(10,2),
     58  TotalPrice numeric(10,2),
     59  Duration integer,
     60  CardID integer,
     61  TransactionType varchar(10) DEFAULT 'Buy',
     62  GroupID varchar(36),
     63  CONSTRAINT procurement_request_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
     64  CONSTRAINT wallet_fk FOREIGN KEY (WalletID) REFERENCES wallet(WalletID),
     65  CONSTRAINT procurement_fk FOREIGN KEY (ProcurementID) REFERENCES procurement(TransactionID),
     66  CONSTRAINT procurement_request_status_check CHECK (Status IN ('Pending','Approved','Rejected')),
     67  CONSTRAINT procurement_request_paymentstatus_check CHECK (PaymentStatus IN ('Reserved','Charged','Refunded')),
     68  CONSTRAINT procurement_request_transactiontype_check CHECK (TransactionType IN ('Buy','Rent'))
     69);
     70
    2371CREATE TABLE product (
    2472  ProductID integer PRIMARY KEY,
    … …  
    3280  CONSTRAINT procurement_fk FOREIGN KEY(ProcurementID) REFERENCES procurement(TransactionID),
    3381  CONSTRAINT procurement_request_fk FOREIGN KEY(ProcurementRequestID) REFERENCES procurement_request(RequestID)
    34 );
    35 
    36 CREATE TABLE procurement (
    37   TransactionID integer PRIMARY KEY,
    38   EmployeeID integer,
    39   CustomerID integer,
    40   ProcurementDate date,
    41   Quantity integer,
    42   Status varchar(10) DEFAULT 'Pending',
    43   Notified boolean DEFAULT FALSE,
    44   GroupID varchar(255),
    45   CONSTRAINT procurement_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
    46   CONSTRAINT procurement_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
    4782);
    4883
    … …  
    78113);
    79114
    80 CREATE TABLE wallet (
    81   WalletID serial PRIMARY KEY,
    82   CustomerID integer NOT NULL,
    83   Balance numeric(10,2) NOT NULL DEFAULT 0.00,
    84   CardNumber varchar(16),
    85   ExpiryDate date,
    86   CVV varchar(4),
    87   CardHolderName varchar(100),
    88   CONSTRAINT wallet_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
    89 );
    90 
    91115CREATE TABLE customerfeedback (
    92116  FeedbackID integer PRIMARY KEY,
    … …  
    100124  CONSTRAINT feedback_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
    101125  CONSTRAINT feedback_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
    102 );
    103 
    104 CREATE TABLE procurement_request (
    105   RequestID serial PRIMARY KEY,
    106   CustomerID integer NOT NULL,
    107   WalletID integer NOT NULL,
    108   ProcurementID integer NOT NULL,
    109   Quantity integer DEFAULT 1,
    110   RequestedAt timestamp DEFAULT CURRENT_TIMESTAMP,
    111   Status varchar(10) DEFAULT 'Pending',
    112   PaymentMethod varchar(20),
    113   PaymentStatus varchar(10) DEFAULT 'Reserved',
    114   MonthlyPay numeric(10,2),
    115   TotalPrice numeric(10,2),
    116   Duration integer,
    117   CardID integer,
    118   TransactionType varchar(10) DEFAULT 'Buy',
    119   GroupID varchar(36),
    120   CONSTRAINT procurement_request_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
    121   CONSTRAINT wallet_fk FOREIGN KEY (WalletID) REFERENCES wallet(WalletID),
    122   CONSTRAINT procurement_fk FOREIGN KEY (ProcurementID) REFERENCES procurement(TransactionID),
    123   CONSTRAINT procurement_request_status_check CHECK (Status IN ('Pending','Approved','Rejected')),
    124   CONSTRAINT procurement_request_paymentstatus_check CHECK (PaymentStatus IN ('Reserved','Charged','Refunded')),
    125   CONSTRAINT procurement_request_transactiontype_check CHECK (TransactionType IN ('Buy','Rent'))
    126126);
    127127
    … …  
    159159  CONSTRAINT employee_maintenance_maintenance_fk FOREIGN KEY (MaintenanceID) REFERENCES maintenance(MainID)
    160160);
     161
    161162}}}