EBD - dmfrodrigues/feup-lbaw-proj GitHub Wiki

EBD: Database Specification Component

A4: Conceptual Data Model

1. Class diagram

The Conceptual Domain Model contains the identification and description of the entities of the domain and the relationships between them in an UML class diagram.

The diagram of Figure 1 presents the main organisational entities, the relationships between them, attributes and their domains, and the multiplicity of relationships for Tokyo Drift Auction House.

uml

Figure 1: UML conceptual data model

The "diagrams.net" tool was used.

A5: Relational Schema, validation and schema refinement

This artifact contains the Relational Schema obtained by mapping from the Conceptual Data Model. The Relational Schema includes the relation schema, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK.

1. Relational Schema

Relation schemas are specified in the compact notation:

Relation reference Relation Compact Notation
R01 user(id, firstName NN, lastName NN, email EMAIL_T NN UK, username NN UK, password NN, location, about, registeredOn PASTTIMESTAMP, profileImage β†’ image)
R02 global_mod(id β†’ user)
R03 admin(id β†’ user)
R04 seller(id β†’ user)
R05 vehicle(id, owner β†’ user NN, brand NN, model NN, condition CONDITION_T NN, year NN CK year ≀ year(now), horsepower NN CK horsepower β‰₯ 0, description)
R06 auction(id, auction_name NN, vehicle_id β†’ vehicle NN, startingBid EURO_T CK startingBid β‰₯ 0, creationTime PASTTIMESTAMP, startingTime CK startingTime β‰₯ creationTime, endingTime NN CK endingTime β‰₯ startingTime + 1hour, auctionType AUCTIONTYPE_T NN DF 'Public', search TSVECTOR)
R07 auction_mod(auction_id β†’ auction, user_id β†’ user)
R08 image(id, path NN)
R09 invoice(id, user_id β†’ user NN, createdOn PASTTIMESTAMP, vatNumber VATNUMBER_T, value EURO_T CK value β‰₯ 0, description NN)
R10 vehicle_image(vehicle_id β†’ vehicle, image_id β†’ image, sequence_number NN CK sequence_number β‰₯ 0) UK(vehicle, sequence_number)
R11 auction_guest(user_id β†’ User, auction_id β†’ auction)
R12 favorite_auction(user_id β†’ user, auction_id β†’ auction)
R13 comment(id, user_id β†’ user NN, auction β†’ auction NN, time NowTimeStamp CK time ≀ now, content NN)
R14 ban(id, user_id β†’ user NN, createdBy β†’ user NN, createdOn PASTTIMESTAMP, startTime FUTURETIMESTAMP CK startTime β‰₯ createdOn, endTime FUTURETIMESTAMP CK end > start, reason NN, banType BANTYPE_T NN, auction_id β†’ auction) CK (banType = 'AuctionBan' ⇔ auction_id β‰  NULL)
R15 bid(id, user_id β†’ user NN, auction_id β†’ auction NN, amount EURO_T CK amount > 0, createdOn PASTTIMESTAMP) CK user β‰  auction.vehicle.user CK createdOn < auction.endingTime

2. Domains

Specification of additional domains:

Domain Name Domain Specification
PASTTIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK(VALUE <= CURRENT_TIMESTAMP)
FUTURETIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
EURO_T NUMERIC(17, 2) NOT NULL
EMAIL_T TEXT CHECK(VALUE LIKE '%@%.__%')
CONDITION_T ENUM ('Mint', 'Clean', 'Average', 'Rough')
AUCTIONTYPE_T ENUM ('Public', 'Private')
BANTYPE_T ENUM ('BuyerBan', 'SellerBan', 'AllBan', 'AuctionBan')

3. Schema validation

Table R01 (user)
Keys: { id }, { email }, { username }
Functional Dependencies
FD101 { id } β†’ { firstName, lastName, email, username, password, location, about, registeredOn, profileImage }
FD102 { email } β†’ { id, firstName, lastName, username, password, location, about, registeredOn, profileImage }
FD103 { username } β†’ { id, firstName, lastName, email, password, location, about, registeredOn, profileImage }
NORMAL FORM BCNF
Table R02 (global_mod)
Keys: { id }
NORMAL FORM BCNF
Table R03 (admin)
Keys: { id }
NORMAL FORM BCNF
Table R04 (seller)
Keys: { id }
NORMAL FORM BCNF
Table R05 (vehicle)
Keys: { id }
Functional Dependencies
FD201 { id } β†’ { owner, brand, model, condition, year, horsepower, description }
NORMAL FORM BCNF
Table R06 (auction)
Keys: { id }
Functional Dependencies
FD301 { id } β†’ { name, vehicle_id, startingBid, startingTime, endingTime, auctionType }
NORMAL FORM BCNF
Table R07 (auction_mod)
Keys: { auction_id, user_id }
NORMAL FORM BCNF
Table R08 (image)
Keys: { id }
Functional Dependencies
FD801 { id } β†’ { path }
NORMAL FORM BCNF
Table R09 (invoice)
Keys: { id }
Functional Dependencies
FD901 { id } β†’ { user_id, createdOn, vatNumber, value, description }
NORMAL FORM BCNF
Table R10 (vehicle_image)
Keys: { vehicle_id, image_id }, { vehicle_id, sequence_number }
Functional Dependencies
FD1001 { vehicle_id, image_id } β†’ { sequence_number }
FD1002 { vehicle_id, sequence_number } β†’ { image_id }
NORMAL FORM BCNF
Table R11 (auction_guest)
Keys: { user_id, auction_id }
NORMAL FORM BCNF
Table R12 (favourite_auction)
Keys: { user_id, auction_id }
NORMAL FORM BCNF
Table R13 (comment)
Keys: { id }
Functional Dependencies
FD1301 { id } β†’ { user_id, auction_id, time, content }
NORMAL FORM BCNF
Table R14 (ban)
Keys: { id }
Functional Dependencies
FD1401 { id } β†’ { user_id, createdBy, createdOn, start, end, reason, banType, auction_id }
NORMAL FORM BCNF
Table R15 (bid)
Keys: { id }
Functional Dependencies
FD1401 { id } β†’ { user_id, auction_id, amount, time }
NORMAL FORM BCNF

All relations are in the Boyce–Codd Normal Form (BCNF), because for every one of its dependencies X β†’ Y, at least one of the following conditions hold:

X β†’ Y is a trivial functional dependency (Y βŠ† X),
X is a superkey for schema R.

Thus, the relational schema is also in the BCNF and therefore there is no need for it to be refined using normalisation.

A6: Indexes, triggers, user functions, transactions and population

This artefact contains the physical schema of the database, the identification and characterization of the indexes, the support of data integrity rules with triggers, the definition of the database user-defined functions, and the identification and characterization of the database transactions. This artefact also includes the complete database creation script, including all SQL necessary to define all integrity constraints, indexes, triggers and transactions.

1. Database Workload

1.1. Tuple Estimation

Relation Reference Relation Name Order of magnitude Estimated growth
R01 user thousands units per day
R02 global_mod tens units per month
R03 admin tens units per month
R04 seller thousands units per day
R05 vehicle hundreds tens per day
R06 auction hundreds tens per day
R07 auction_mod tens units per month
R08 image thousands tens per day
R09 profile_image thousands units per day
R10 vehicle_image hundreds tens per day
R11 auction_guest hundreds tens per day
R12 favorite_auction thousands hundreds per day
R13 auction_comment thousands hundreds per day
R14 ban hundreds units per month
R15 bid thousands hundreds per hour

1.2. Frequent Queries

Query SELECT01
Description User's Profile
Frequency Hundreds per Day 
SELECT *
FROM "user"
WHERE "user".email = $email;

SELECT *
FROM "user"
WHERE "user".username = $username;
Query SELECT02
Description Users by location
Frequency Dozens per Day 
SELECT *
FROM "user"
WHERE "user".location = $location;
Query SELECT03
Description User's Favourites
Frequency Hundreds per Day 
SELECT "auction".*
FROM "favourite_auction", "auction"
WHERE
    favourite_auction.auction_id = "auction".id
    AND favourite_auction.user_id = $user_id;
Query SELECT04
Description Sign in
Frequency Hundreds per Day 
SELECT id 
FROM user 
WHERE 
    username = $username 
    AND password = $hashedPassword;

SELECT id 
FROM user 
WHERE 
    email = $email 
    AND password = $hashedPassword;
Query SELECT05
Description Auction card
Frequency Thousands per Day 
SELECT "auction".auction_name, "auction".endingTime, "vehicle_image".image 
FROM "vehicle_image", "auction"
WHERE
    "auction".id = $id
    AND "vehicle_image".vehicle  = "auction".vehicle
    AND "vehicle_image".sequence_number = 0;
Query SELECT06
Description Auction
Frequency Thousands per Day 
SELECT *
FROM "auction", "vehicle"
WHERE
    "auction".vehicle = "vehicle".id
    AND auction.id = $id;
Query SELECT07
Description Auction's guests
Frequency Hundreds per Day 
SELECT  "user".*
FROM "user", "auction_guest"
WHERE
    "auction_guest".auction_id  = $auction_id
    AND "user".id = "auction_guest".user_id;
Query SELECT08
Description Auction's info
Frequency Thousands per Day 
SELECT *
FROM "auction"
WHERE
    "auction".id  = $id;
Query SELECT09
Description Auction's images
Frequency Thousands per Day 
SELECT "vehicle_image".image, "vehicle_image".sequence_number 
FROM "auction", "vehicle_image"
WHERE
    "auction".id  = $id
    AND "vehicle_image".vehicle  = "auction".vehicle;
Query SELECT10
Description Seller Auctions
Frequency Hundreds per Day 
SELECT *
FROM "auction", "vehicle"
WHERE
    "auction".vehicle = "vehicle".id
    AND "vehicle".owner = $seller_id;
Query SELECT11
Description Search by Brand
Frequency Dozens per Day 
SELECT *
FROM "auction", "vehicle"
WHERE
    "auction".vehicle = "vehicle".id
    AND "vehicle".brand = $brand;
Query SELECT12
Description Search by Model
Frequency Dozens per Day 
SELECT *
FROM "auction", "vehicle"
WHERE
    "auction".vehicle = "vehicle".id
    AND "vehicle".model = $model;
Query SELECT13
Description Auction's comments
Frequency Thousands per Day 
SELECT "auction_comment".*
FROM "auction_comment", "auction"
WHERE
    "auction_comment".auction_id  = "auction".id;
Query SELECT14
Description Search
Frequency Hundreds per Day 
SELECT *
FROM
    "auction", to_tsquery($search) AS query,
    to_tsvector(auction_name || ' ' || brand || model) AS textsearch
WHERE query @@ textsearch\\
ORDER BY rank DESC;
Query SELECT15
Description Auction's top bid
Frequency Thousands per Day 
SELECT *
FROM "bid", "auction"
WHERE 
    "bid".amount = (
                    SELECT MAX(amount) 
                    FROM "bid", "auction" 
                    WHERE "bid".auction = "auction".id)

1.3. Frequent Updates

Query INSERT01
Description New Comment
Frequency Hundreds per Day 
INSERT INTO "auction_comment" (user_id, auction_id, content) 
VALUES($user_id, $auction_id, $content)
Query INSERT02
Description New Favourite
Frequency Thousands per Day 
INSERT INTO "favourite_auction" (user_id, auction_id) 
VALUES($user_id, $auction_id)
Query INSERT03
Description New Ban
Frequency Units per Month 
INSERT INTO "ban" (user_id, createdBy, startTime, endTime, reason, banType, auction_id) 
VALUES($user_id, $createdBy, $startTime, $endTime, $reason, $banType, $auction_id)
Query INSERT04
Description New Bid
Frequency Hundreds per Hour 
INSERT INTO "bid" (user_id, auction_id, amount) 
VALUES($user_id, $auction_id , $amount)
Query INSERT05
Description Add Auction Guest
Frequency Dozens per Day 
INSERT INTO "bid" (user_id, auction_id) 
VALUES($user_id, $auction_id)
Query UPDATE01
Description Update User Profile
Frequency Units per Day 
UPDATE user 
SET 
    firstName = $firstName, 
    lastName = $lastName, 
    username = $username,
    email = $email, 
    password = $hashedPassword, 
    location = $location, 
    about = $about 
WHERE id = $id;
Query UPDATE02
Description Update Auction info
Frequency Units per Day 
UPDATE auction
SET 
    auction_name = $auction_name,
    startingTime = $startingTime,
    endingTime = $endingTime, 
    auctionType = $auctionType
WHERE vehicle = $id;

UPDATE vehicle
SET 
    brand = $brand, 
    model = $model, 
    condition = $condition,
    manufactureYear = $manufactureYear, 
    horsepower = $horsepower
WHERE id = $id;
Query DELETE01
Description Delete Comment
Frequency Units per Day 
DELETE FROM "auction_comment" 
  WHERE id = $id;
Query DELETE02
Description Delete Auction
Frequency Units per Day 
DELETE FROM "vehicle" 
  WHERE id = $id;

2. Proposed Indices

2.1. Performance Indices

Index IDX01
Related Queries SELECT10
Relation "vehicle"
Attribute owner
Type Hash
Cardinality Medium
Clustering Yes
Justification Table "vehicle" is very large and query SELECT10, used to search the vehicles/auctions of an user, has to be fast because it's executed many times. Doesn't need range query support. Cardinality is medium so it's a good candidate for clustering. 
CREATE INDEX user_vehicles ON "vehicle"
USING hash(owner);
Index IDX02
Related Queries SELECT13
Relation "auction_comment"
Attribute auction
Type Hash
Cardinality Medium
Clustering Yes
Justification Table "auction_comment" is very large and query SELECT13, used to get all comments under an auction, has to be fast because it's executed many times. Doesn't need range query support. Cardinality is medium so it's a good candidate for clustering. 
CREATE INDEX auctions_comments ON "comment"
USING hash(auction_id);
Index IDX03
Related Queries SELECT12
Relation "vehicle"
Attribute model
Type Hash
Cardinality Medium
Clustering Yes
Justification Table "vehicle" is very large and query SELECT12, which is used to get all vehicles of a given model, has to be fast because it's executed many times. Doesn't need range query support. Cardinality is medium so it's a good candidate for clustering. 
CREATE INDEX vehicle_models ON "vehicle"
USING hash(model);
Index IDX04
Related Queries SELECT15
Relation "bid"
Attribute auction
Type Hash
Cardinality Medium
Clustering Yes
Justification Table "bid" is very large and query SELECT15, used to get the current top bid of an auction, has to be fast because it's executed many times. Doesn't need range query support. Cardinality is medium so it's a good candidate for clustering. 
CREATE INDEX auctions_bids ON "bid"
USING hash(auction_id);
Index IDX05
Related Queries SELECT06
Relation "auction"
Attribute endingTime
Type B-tree
Cardinality Medium
Clustering Yes
Justification Query SELECT06 allows to obtain all auctions and is executed very frequently. The index allows searching auctions by ending time range faster. It's clustered to allow for quick range queries. Cardinality is medium. 
CREATE INDEX end_auction ON "auction"
USING btree (endingTime);

2.2. Full-text Search Indices

Index IDX06
Related Queries SELECT14
Relation "auction"
Attribute auction_name
Type GiST
Clustering No
Justification Query SELECT14 is responsible for running full text searches while searching for auctions and their names. The index improves the performance of the search adn should use GiST because it's better for dynamic data. 
CREATE INDEX search_idx ON "auction"
USING GIST (search);

3. Triggers

Trigger TRIGGER01
Description A user can only be banned from an auction by an admin, global_mod or an auction_mod of that auction. A user can only be banned as seller, buyer or totally by an admin. 
CREATE FUNCTION ban_user() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.banType = 'AuctionBan' THEN
        IF NOT EXISTS (
            SELECT * FROM "admin" WHERE "admin".id = NEW.createdBy 
            UNION
            SELECT * FROM "global_mod" WHERE "global_mod".id = NEW.createdBy
            UNION
            SELECT user_id FROM "auction_mod" WHERE NEW.auction_id = "auction_mod".auction_id AND "auction_mod".user_id = NEW.createdBy
        ) THEN
            RAISE EXCEPTION 'User must be banned by Authorised Mod or Admin';
        END IF;
    ELSE
        IF NOT EXISTS (SELECT * FROM "admin" WHERE "admin".id = NEW.createdBy) THEN
            RAISE EXCEPTION 'User must be banned by Admin';
        END IF;
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER ban_user
    BEFORE INSERT OR UPDATE ON "ban"
    FOR EACH ROW
    EXECUTE PROCEDURE ban_user();
Trigger TRIGGER02
Description Auction Guest tables can only exist for private auctions. 
CREATE FUNCTION private_auction_guests() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NOT EXISTS(
        SELECT *
        FROM "auction"
        WHERE NEW.auction_id = "auction".id AND "auction".auctionType = 'Private'
    ) THEN
        RAISE EXCEPTION 'Auction is not Private';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
 
CREATE TRIGGER private_auction_guests
    BEFORE INSERT OR UPDATE ON "auction_guest"
    FOR EACH ROW
    EXECUTE PROCEDURE private_auction_guests();
Trigger TRIGGER03
Description The owner of an auction cannot bid on it. 
CREATE FUNCTION cant_bid_own_auction() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.user_id IN (
        SELECT "vehicle".owner
        FROM "auction"
        JOIN "vehicle" ON "auction".vehicle_id = "vehicle".id
        WHERE "auction".id = NEW.auction_id
    ) THEN
        RAISE EXCEPTION 'Bid cannot be placed by auction owner';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER cant_bid_own_auction
    BEFORE INSERT OR UPDATE ON "bid"
    FOR EACH ROW
    EXECUTE PROCEDURE cant_bid_own_auction();
Trigger TRIGGER04
Description A bid can only be placed before the auction ends. 
CREATE FUNCTION cant_bid_auction_over() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.createdOn >= (
        SELECT endingTime
        FROM "auction"
        WHERE id = NEW.auction_id
    ) THEN
        RAISE EXCEPTION 'Bid cannot be placed after auction is over';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER cant_bid_auction_over
    BEFORE INSERT OR UPDATE ON "bid"
    FOR EACH ROW
    EXECUTE PROCEDURE cant_bid_auction_over();
Trigger TRIGGER05
Description A bid can only be placed on a private auction if the user placing it is a guest of that auction. 
CREATE FUNCTION only_guests_can_bid() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (
        SELECT auctionType
        FROM "auction"
        WHERE id = NEW.auction_id
    ) = 'Private' AND NEW.user_id NOT IN (
        SELECT user_id FROM "auction_guest" WHERE auction_id = NEW.auction_id
    ) THEN
        RAISE EXCEPTION 'Bids can only be placed on private auctions by guests of that auction';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER only_guests_can_bid
    BEFORE INSERT OR UPDATE ON "bid"
    FOR EACH ROW
    EXECUTE PROCEDURE only_guests_can_bid();
Trigger TRIGGER06
Description When a BuyerBan or AllBan is created, that user's bids are removed. 
CREATE FUNCTION banned_bids() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.banType = 'BuyerBan' OR NEW.banType = 'AllBan' THEN
        DELETE FROM "bid" b
        WHERE b.user = NEW.user
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER banned_bids
    AFTER INSERT OR UPDATE ON "ban"
    FOR EACH ROW
    EXECUTE PROCEDURE banned_bids();
Trigger TRIGGER07
Description When a user is removed from a private auction's guest list, it's bids are also removed. 
CREATE FUNCTION removed_from_guest_list() RETURNS TRIGGER AS
$BODY$
BEGIN
    DELETE FROM "bid" b
    WHERE 
        b.user = OLD.user AND
        b.auction = OLD.auction
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER removed_from_guest_list
    AFTER DELETE ON "auction_guest"
    FOR EACH ROW
    EXECUTE PROCEDURE removed_from_guest_list();
Trigger TRIGGER08
Description Keeps TSVECTOR in sync. 
CREATE FUNCTION update_fts() RETURNS TRIGGER AS
$BODY$
DECLARE brand TEXT = (SELECT brand FROM "vehicle" WHERE id = NEW.vehicle_id);
DECLARE model TEXT = (SELECT model FROM "vehicle" WHERE id = NEW.vehicle_id);
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.search = setweight(to_tsvector ('english', NEW.auction_name), 'A') || 
                     setweight(to_tsvector ('english', brand), 'B') ||
                     setweight(to_tsvector ('english', model), 'C');
	END IF;
	IF TG_OP = 'UPDATE' THEN
		IF NEW.auction_name <> OLD.auction_name THEN
			NEW.search = setweight(to_tsvector ('english', NEW.auction_name), 'A') || 
                         setweight(to_tsvector ('english', brand), 'B') ||
                         setweight(to_tsvector ('english', model), 'C');
		END IF;
	END IF;
	RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_fts
    BEFORE INSERT OR UPDATE ON "auction"
    FOR EACH ROW
    EXECUTE PROCEDURE update_fts();

4. Transactions

SQL Reference New User
Justification Creation of a user implies user, image and profile_image row insertion
Isolation Level Serializable 
BEGIN;
INSERT INTO "user" (firstName,lastName,email,username,password,location,about)
VALUES ($first_name, $last_name, $email, $username, $password, $location, $about);

INSERT INTO "image" (path)
VALUES ($path);

INSERT INTO "profile_image" (user_id,image)
VALUES ($user_id, $image_id);
COMMIT;
SQL Reference New Auction
Justification Creation of an auction implies vehice, image, vehicle_image and auction row insertion
Isolation Level Serializable 
BEGIN;
INSERT INTO "vehicle" (owner,brand,model,condition,manufactureYear,horsepower)
VALUES ($user_id, $brand, $model, $condition, $year, $horsepower);

INSERT INTO "image" (path)
VALUES ($path);

INSERT INTO "vehicle_image" (vehicle,image,sequence_number)
VALUES ($vehicle_id, $image_id, $sequence_number);

INSERT INTO "auction" (auction_name,vehicle,startingBid,startingTime,endingTime,auctionType)
VALUES ($auction_name, $vehicle_id, $startingBid, $startingTime, $endingTime, $auctionType)
COMMIT;

Annex A. SQL Code

A.1. Database Schema

DROP DOMAIN IF EXISTS PASTTIMESTAMP     CASCADE;
DROP DOMAIN IF EXISTS FUTURETIMESTAMP   CASCADE;
DROP DOMAIN IF EXISTS EURO_T            CASCADE;
DROP DOMAIN IF EXISTS EMAIL_T           CASCADE;
DROP DOMAIN IF EXISTS VATNUMBER_T       CASCADE;
DROP TYPE   IF EXISTS CONDITION_T       CASCADE;
DROP TYPE   IF EXISTS AUCTIONTYPE_T     CASCADE;
DROP TYPE   IF EXISTS BANTYPE_T         CASCADE;

DROP TABLE IF EXISTS "user"                 CASCADE;
DROP TABLE IF EXISTS "global_mod"           CASCADE;
DROP TABLE IF EXISTS "admin"                CASCADE;
DROP TABLE IF EXISTS "seller"               CASCADE;
DROP TABLE IF EXISTS "vehicle"              CASCADE;
DROP TABLE IF EXISTS "auction"              CASCADE;
DROP TABLE IF EXISTS "auction_mod"          CASCADE;
DROP TABLE IF EXISTS "image"                CASCADE;
DROP TABLE IF EXISTS "invoice"              CASCADE;
DROP TABLE IF EXISTS "vehicle_image"        CASCADE;
DROP TABLE IF EXISTS "auction_guest"        CASCADE;
DROP TABLE IF EXISTS "favourite_auction"    CASCADE;
DROP TABLE IF EXISTS "comment"              CASCADE;
DROP TABLE IF EXISTS "ban"                  CASCADE;
DROP TABLE IF EXISTS "bid"                  CASCADE;

CREATE DOMAIN   PASTTIMESTAMP   AS TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK(VALUE <= CURRENT_TIMESTAMP);
CREATE DOMAIN   FUTURETIMESTAMP AS TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP;
CREATE DOMAIN   EURO_T          AS INTEGER      NOT NULL;
CREATE DOMAIN   EMAIL_T         AS TEXT         CHECK(VALUE LIKE '_%@_%.__%');
CREATE DOMAIN   VATNUMBER_T     AS CHAR(16);
CREATE TYPE     CONDITION_T     AS ENUM ('Mint', 'Clean', 'Average', 'Rough');
CREATE TYPE     AUCTIONTYPE_T   AS ENUM ('Public', 'Private');
CREATE TYPE     BANTYPE_T       AS ENUM ('BuyerBan', 'SellerBan', 'AllBan', 'AuctionBan');

CREATE TABLE "image" (
    id      SERIAL  PRIMARY KEY,
    path    TEXT    NOT NULL
);

CREATE TABLE "user" (
    id              SERIAL          PRIMARY KEY,
    firstName       TEXT            NOT NULL,
    lastName        TEXT            NOT NULL,
    email           EMAIL_T         NOT NULL UNIQUE,
    username        TEXT            NOT NULL UNIQUE,
    password        TEXT            NOT NULL,
    location        TEXT            ,
    about           TEXT            ,
    registeredOn    PASTTIMESTAMP   ,
    profileImage    INTEGER         REFERENCES "image"(id)
);

CREATE TABLE "global_mod" (
    id INTEGER PRIMARY KEY REFERENCES "user"(id) ON DELETE CASCADE
);

CREATE TABLE "admin" (
    id INTEGER PRIMARY KEY REFERENCES "user"(id) ON DELETE CASCADE
);

CREATE TABLE "seller" (
    id INTEGER PRIMARY KEY REFERENCES "user"(id) ON DELETE CASCADE
);

CREATE TABLE "vehicle" (
    id          SERIAL      PRIMARY KEY,
    owner       INTEGER     REFERENCES "user"(id) NOT NULL,
    brand       TEXT        NOT NULL,
    model       TEXT        NOT NULL,
    condition   CONDITION_T NOT NULL DEFAULT 'Mint',
    year        INTEGER     NOT NULL CHECK(year <= date_part('year', CURRENT_TIMESTAMP)),
    horsepower  INTEGER     NOT NULL CHECK(horsepower >= 0),
    description TEXT
);

CREATE TABLE "auction" (
    id              SERIAL          PRIMARY KEY,
    auction_name    TEXT            NOT NULL,
    vehicle_id      INTEGER         NOT NULL REFERENCES "vehicle"(id),
    startingBid     EURO_T          CHECK (startingBid >= 0),
    creationTime    PASTTIMESTAMP   ,
    startingTime    TIMESTAMP       NOT NULL CHECK (startingTime >= creationTime),
    endingTime      TIMESTAMP       NOT NULL CHECK (endingTime >= startingTime + INTERVAL '1 hour'),
    auctionType     AUCTIONTYPE_T   NOT NULL DEFAULT 'Public',
    search TSVECTOR
);

CREATE TABLE "auction_mod" (
	user_id     INTEGER     REFERENCES "user"(id) ON DELETE CASCADE,
    auction_id  INTEGER     REFERENCES "auction"(id),
	PRIMARY KEY(user_id, auction_id)
);

CREATE TABLE "invoice" (
    id          SERIAL          PRIMARY KEY,
    user_id     INTEGER         NOT NULL REFERENCES "user"(id),
    createdOn   PASTTIMESTAMP   ,
    vatNumber   VATNUMBER_T     ,
    value       EURO_T          CHECK(value >= 0),
    description TEXT            NOT NULL
);

CREATE TABLE "vehicle_image" (
    vehicle_id      INTEGER     NOT NULL REFERENCES "vehicle"(id),
    image_id        INTEGER     NOT NULL REFERENCES "image"(id) ON DELETE CASCADE,
    sequence_number INTEGER     NOT NULL CHECK(sequence_number >= 0),
	PRIMARY KEY(vehicle_id, image_id),
    UNIQUE(vehicle_id, sequence_number)
);

CREATE TABLE "auction_guest" (
    user_id     INTEGER     REFERENCES "user"(id),
    auction_id  INTEGER     REFERENCES "auction"(id),
	PRIMARY KEY(user_id, auction_id)
);

CREATE TABLE "favourite_auction" (
    user_id     INTEGER     REFERENCES "user"(id),
    auction_id  INTEGER     REFERENCES "auction"(id),
	PRIMARY KEY(user_id, auction_id)
);

CREATE TABLE "comment" (
    id          SERIAL          PRIMARY KEY,
    user_id     INTEGER         NOT NULL REFERENCES "user"(id),
    auction_id  INTEGER         NOT NULL REFERENCES "auction"(id),
    createdOn   PASTTIMESTAMP   ,
    content     TEXT            NOT NULL
);

CREATE TABLE "ban" (
    id          SERIAL          PRIMARY KEY,
    user_id     INTEGER         NOT NULL REFERENCES "user"(id),
    createdBy   INTEGER         NOT NULL REFERENCES "user"(id),
    createdOn   PASTTIMESTAMP   ,
    startTime   FUTURETIMESTAMP CHECK(startTime >= createdOn),
    endTime     FUTURETIMESTAMP CHECK(endTime > startTime),
    reason      TEXT            NOT NULL,
    banType     BANTYPE_T       NOT NULL,
    auction_id  INTEGER         REFERENCES "auction"(id)
    CONSTRAINT auction_id CHECK ((banType != 'AuctionBan') OR (banType = 'AuctionBan' AND auction_id IS NOT NULL))
);

CREATE TABLE "bid" (
    id          SERIAL          PRIMARY KEY,
    user_id     INTEGER         REFERENCES "user"(id) NOT NULL,
    auction_id  INTEGER         REFERENCES "auction"(id) NOT NULL,
    amount      EURO_T          CHECK (amount > 0),
    createdOn   PASTTIMESTAMP
);

A.2. Database Population

-- Profile Photos --
INSERT INTO "image" (id,path) VALUES
(1, 'profile_photos/1.png'),
(2, 'profile_photos/1.png'),
(3, 'profile_photos/1.png'),
(4, 'profile_photos/1.png'),
(5, 'profile_photos/1.png'),
(6, 'profile_photos/1.png'),
(7, 'profile_photos/1.png'),
(8, 'profile_photos/1.png'),
(9, 'profile_photos/1.png'),
(10, 'profile_photos/1.png'),
(11, 'profile_photos/1.png'),
(12, 'profile_photos/1.png'),
(13, 'profile_photos/1.png'),
(14, 'profile_photos/1.png'),
(15, 'profile_photos/1.png'),
(16, 'profile_photos/1.png'),
(17, 'profile_photos/1.png'),
(18, 'profile_photos/1.png'),
(19, 'profile_photos/1.png'),
(20, 'profile_photos/1.png');

-- Users --
INSERT INTO "user" (id,profileImage,firstName,lastName,email,username,password,location,about,registeredOn) VALUES
(1,1,'Roth','Hampton','[email protected]','roth_hampton','PXA24YEP6CV','Porto',
'I LOVE CARS and so I decided to build this website so I can share it with everyone else.','2021-03-30 12:38:24'),
(2,2,'Bree','Espinoza','[email protected]','bree_espinoza','ZED49VDV4VT','Muradiye',
'I love BMWs and I cant help myself when it comes to buying them. My girlfriend doesnt really approve of that so I need to sell some of them',
'2021-03-30 12:38:24'),
(3,3,'Tanek','Spence','[email protected]','tanek_spence','DTQ16ULU3HP','Barry',
'Ive been in love with cars ever since I was a 4 year old and now I want to make a living out of it.','2021-03-30 12:38:24'),
(4,4,'Fitzgerald','Cash','[email protected]','cash_fitz','UAU42EDT3XL','Perchtoldsdorf',
'Cars, cars, cars, You gotta love them.','2021-03-30 12:38:24'),
(5,5,'Aimee','Cortez','[email protected]','aimee_cortez','XXX07JUG7BH','Sargodha',
'Life without cars is like Rome without the Pope.','2021-03-30 12:38:24'),
(6,6,'Barclay','Sargent','[email protected]','barclay','KDI43MJE4FP','Maunath Bhanjan',
'Just chilling and buying cars.','2021-03-30 12:38:24'),
(7,7,'Fuller','Beck','[email protected]','fuller_beck','NAQ18MSW3TI','Pekanbaru',
'Just trying to have some fun :)','2021-03-30 12:38:24'),
(8,8,'Ulysses','Bennett','[email protected]','ulysses_bennte','CQW48UUT5TJ','Aylmer',
'Impulsive car buyer.','2021-03-30 12:38:24'),
(9,9,'Ian','Walsh','[email protected]','ian_walsh','LQG38WSJ6NO','Borgomasino',
'Im a Bad Ass.','2021-03-30 12:38:24'),
(10,10,'Valentine','Boyer','[email protected]','valentine_boyer','RIX27PKB7IJ','Sakhalin',
'Hello! Just loving cars.','2021-03-30 12:38:24'),
(11,11,'Ulysses','Hayes','[email protected]','ulysses_hayes','PPY94UIU8YF','Scala Coeli',
'Fast and Furious lover. JUst trying to bring things from the screen to real life.','2021-03-30 12:38:24'),
(12,12,'Moses','Stanton','[email protected]','moses_stanton','NPR54ONU1NS','Tallahassee',
'Im boreeeeeeeeeeeeeeeeeeeeeeeeeeeeeeed','2021-03-30 12:38:24'),
(13,13,'Silas','Olsen','[email protected]','silas_olsen','MCH94KSM4NQ','Pishin Valley',
'what am i doing with my life?','2021-03-30 12:38:24'),
(14,14,'Abra','Carson','[email protected]','abra_carson','EWV73ABO7RD','Belogorsk',
'Fast and furious is the best and you cant change my mind','2021-03-30 12:38:24'),
(15,15,'Montana','Poole','[email protected]','montana','EXV51KVN4UG','Saint-Nazaire',
'just trying to find a nice car for my family','2021-03-30 12:38:24'),
(16,16,'Melvin','Burch','[email protected]','melvin_b','KJV18FNQ2TA','BostaniΓ§i',
'hey everyone. Im new to the car passion thing but i really want to get into business.','2021-03-30 12:38:24'),
(17,17,'Jared','Head','[email protected]','NBU44UNP0BB','head_jared','Provost',
'life is boring but cars are not','2021-03-30 12:38:24'),
(18,18,'Yvonne','Odonnell','[email protected]','odonnel_y','WYQ13QQL6SR','Saint-MΓ©dard-en-Jalles',
'Renaults are the best cars in the world.','2021-03-30 12:38:24'),
(19,19,'Palmer','Maldonado','[email protected]','paler_mal','HTJ05GKL1QF','East Linton',
'Hey hey hey hey hey hey!','2021-03-30 12:38:24'),
(20,20,'Geraldine','Farrell','[email protected]','geraldine','RTV38CTE4GF','Surat',
'wanna see me playing with cars while wearing nothing? come to this link: geraldine.naked.com','2021-03-30 12:38:24');

-- Seller Permissions --
INSERT INTO "seller" (id) VALUES (2),(3),(4),(7);

-- Vehicles --
INSERT INTO "vehicle" (id,owner,brand,model,condition,year,horsepower) VALUES
(1,2,'BMW','1 Series','Mint',2008,150),
(2,2,'BMW','3 Series','Mint',2010,200),
(3,2,'BMW','5 Series','Mint',2009,220),
(4,2,'BMW','7 Series','Mint',2012,250),
(5,2,'Mercedes','Class A','Clean',2015,180),
(6,2,'Mercedes','Class C','Average',2015,240),
(7,3,'Rolls Royce','Ghost','Mint',2016,320),
(8,3,'Rolls Royce','Ghost','Clean',2012,320),
(9,3,'Rolls Royce','Dawn','Average',2016,300),
(10,3,'Rolls Royce','Phantom','Rough',2003,380),
(11,3,'Rolls Royce','Ghost','Clean',2014,320),
(12,3,'Rolls Royce','Ghost','Clean',2014,320),
(13,3,'Rolls Royce','Dawn','Mint',2018,400),
(14,3,'Rolls Royce','Phantom','Average',2010,380),
(15,4,'Ferrari','Spider','Average',2016,6800),
(16,4,'Ford','Mustang','Rough',2008,550),
(17,4,'Jaguar','XE','Clean',2019,450),
(18,4,'Lamborguini','Aventador S','Clean',2020,740),
(19,4,'Porsche','Panamera 4 Executive','Mint',2020,450),
(20,4,'Tesla','S','Mint',2021,700);

-- Auctions --
INSERT INTO "auction" (id,auction_name,vehicle_id,startingBid,creationTime,startingTime,endingTime,auctionType) VALUES
(1,'BMW 1 Series 2008 Good State',1,8000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(2,'BMW 3 Series 2010 Mint',2,10000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(3,'BMW 5 Series Exclusive',3,15000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(4,'BMW 7 Series Great for Sports People',4,20000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(5,'Mercedes A Clean',5,10000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Private'),
(6,'Mercedes C Average',6,10000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Private'),
(7,'Awesome Rolls Royce Ghost 16',7,30000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(8,'Clean RR Ghost 12',8,25000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(9,'RR Dawn 16 Perfect for Families',9,25000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(10,'Old but never out of Style RR Phantom 03',10,20000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(11,'Clean RR Ghost 14',11,43000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(12,'Fantastic RR Ghost 14',12,48000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-05 12:00:00','Public'),
(13,'Mint RR Dawn 18',13,60000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-08 12:00:00','Public'),
(14,'10s Phantom for the fearless',14,35000,'2021-03-30 12:59:24','2021-04-03 12:00:00','2021-04-08 12:00:00','Public'),
(15,'Exclusive Ferrari Spider',15,180000,'2021-03-30 12:59:24','2021-04-04 12:00:00','2021-04-08 12:00:00','Public'),
(16,'CR7 Old Mustang',16,400000,'2021-03-30 12:59:24','2021-04-04 12:00:00','2021-04-08 12:00:00','Public'),
(17,'Roar with your new Jaguar XE',17,240000,'2021-03-30 12:59:24','2021-04-04 12:00:00','2021-04-08 12:00:00','Public'),
(18,'Luxury Aventador S',18,390000,'2021-03-30 12:59:24','2021-04-05 12:00:00','2021-04-08 12:00:00','Public'),
(19,'New Panamera 4 Executive',19,160000,'2021-03-30 12:59:24','2021-04-05 12:00:00','2021-04-08 12:00:00','Public'),
(20,'Brand New Tesla S',20,230000,'2021-03-30 12:59:24','2021-04-05 12:00:00','2021-04-08 12:00:00','Public');

-- User Permissions --
INSERT INTO "global_mod" (id) VALUES (2),(5);
INSERT INTO "auction_mod" (user_id,auction_id) VALUES (3,7),(3,8),(3,9),(3,10),(3,11),(3,12),(3,13),(3,14);
INSERT INTO "auction_mod" (user_id,auction_id) VALUES (4,15),(4,16),(4,17),(4,18),(4,19),(4,20);
INSERT INTO "auction_mod" (user_id,auction_id) VALUES (6,15),(6,16),(6,17);
INSERT INTO "admin" (id) VALUES (1);

-- Car Photos --
INSERT INTO "image" (id,path) VALUES
(21, 'car_photos/1/1.png'), (22, 'car_photos/1/2.png'), (23, 'car_photos/1/3.png'),
(24, 'car_photos/2/1.png'), (25, 'car_photos/2/2.png'),
(26, 'car_photos/3/1.png'),
(27, 'car_photos/4/1.png'), (28, 'car_photos/4/2.png'),
(29, 'car_photos/5/1.png'),
(30, 'car_photos/6/1.png'),
(31, 'car_photos/7/1.png'),
(32, 'car_photos/8/1.png'),
(33, 'car_photos/9/1.png'),
(34, 'car_photos/10/1.png'),
(35, 'car_photos/11/1.png'),
(36, 'car_photos/12/1.png'),
(37, 'car_photos/13/1.png'),
(38, 'car_photos/14/1.png'),
(39, 'car_photos/15/1.png'),
(40, 'car_photos/16/1.png'),
(41, 'car_photos/17/1.png'),
(42, 'car_photos/18/1.png'),
(43, 'car_photos/19/1.png'),
(44, 'car_photos/20/1.png');

INSERT INTO "vehicle_image" (vehicle_id,image_id,sequence_number) VALUES
(1,21,1), (1,22,2), (1,23,3),
(2,24,1),(2,25,2),
(3,26,1),
(4,27,1),(4,28,2),
(5,29,1),
(6,30,1),
(7,31,1),
(8,32,1),
(9,33,1),
(10,34,1),
(11,35,1),
(12,36,1),
(13,37,1),
(14,38,1),
(15,39,1),
(16,40,1),
(17,41,1),
(18,42,1),
(19,43,1),
(20,44,1);

-- Public Auction Guests --
INSERT INTO "auction_guest" (user_id, auction_id) VALUES
(8,5),(9,5),(10,5),(11,5),(12,5),(13,5),
(11,6),(12,6),(13,6),(14,6),(15,6);

-- Favourite Auctions --
INSERT INTO "favourite_auction" (user_id, auction_id) VALUES
(8,1),(8,2),(8,3),(8,4),(8,5),
(9,5),(9,6),(9,7),(9,8),(9,9),
(10,8),(10,9),
(11,6),(11,5),
(12,6),(12,11),(12,19),(12,17),
(13,15),(13,16);

-- Auction Comments --
INSERT INTO "comment" (id,user_id,auction_id,createdOn,content) VALUES
(1,8,1,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(2,9,1,'2021-03-31 17:54:38','Lovely Car! Hope I can get it!'),
(3,10,1,'2021-03-31 19:03:38','I love this BMW. It will be the perfect gift for my daughter.'),
(4,8,2,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(5,8,3,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(6,8,4,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(7,8,5,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(8,8,7,'2021-03-31 15:27:38','Cant wait to get my hands on this car!'),
(9,11,6,'2021-03-31 15:27:38','Thank you for inviting me for this auction. I really want this car!'),
(10,13,6,'2021-03-31 13:27:38','Really looking forward for this auction'),
(11,12,6,'2021-03-31 18:27:38','Great Car and great seller. Cant wait for this awesome auction.');

-- Banned Users --
INSERT INTO "ban" (id,user_id,createdBy,createdOn,startTime,endTime,reason,banType) VALUES
(1, 20, 1, '2021-03-31 15:27:38', '2021-03-31 15:27:38','2050-03-31 15:27:38',
'Is a bot.','AllBan'),
(2, 19, 1, '2021-03-31 15:27:38', '2021-03-31 15:27:38','2050-03-31 15:27:38',
'I dont trust him.','BuyerBan'),
(3, 18, 1, '2021-03-31 15:27:38', '2021-03-31 15:27:38','2050-03-31 15:27:38',
'I dont want him selling his cars in my website','SellerBan');
INSERT INTO "ban" (id,user_id,createdBy,createdOn,startTime,endTime,reason,banType,auction_id) VALUES
(4, 8, 3, '2021-03-31 15:27:38', '2021-03-31 15:27:38','2050-03-31 15:27:38',
'I dont trust him for this auction','AuctionBan',14);

-- Bids --
INSERT INTO "bid" (id,user_id,auction_id,amount,createdOn) VALUES
(1,5,1,8000,'2021-04-01 17:06:32'),
(2,6,1,8300,'2021-04-01 17:20:32'),
(3,7,1,8500,'2021-04-01 17:45:32'),
(4,5,1,8800,'2021-04-01 17:56:32'),
(5,6,2,10000,'2021-04-01 17:06:32'),
(6,8,2,10500,'2021-04-01 17:20:32'),
(7,6,2,11000,'2021-04-01 17:45:32'),
(8,8,3,15000,'2021-04-01 17:56:32'),
(9,5,4,20000,'2021-04-01 17:06:32'),
(10,7,4,21000,'2021-04-01 17:20:32'),
(11,12,5,10000,'2021-04-01 17:45:32'),
(12,15,6,10000,'2021-04-01 17:56:32'),
(13,11,7,30000,'2021-04-01 17:20:32'),
(14,12,8,25000,'2021-04-01 17:45:32'),
(15,13,9,25000,'2021-04-01 17:56:32');

-- Invoices --
INSERT INTO "invoice" (id,user_id,createdOn,vatNumber,value,description) VALUES
(1,2,'2021-02-01 00:00:10','205538451', 500, 'Seller monthly fee'),
(2,2,'2021-03-01 00:00:10','205538451', 500, 'Seller monthly fee'),
(3,2,'2021-04-01 00:00:10','205538451', 500, 'Seller monthly fee'),
(4,3,'2021-04-01 00:00:07','794830202', 500, 'Seller monthly fee'),
(5,4,'2021-04-01 00:00:08','993546321', 500, 'Seller monthly fee'),
(6,7,'2021-04-01 00:00:09','120520673', 500, 'Seller monthly fee');

Revision history

Changes made to the first submission:

  1. ...

GROUP2174, 22/04/2021

Diogo Miguel Ferreira Rodrigues, [email protected] (Editor)

JoΓ£o AntΓ³nio Cardoso Vieira e Basto de Sousa, [email protected]

Leonor Martins de Sousa, [email protected]

Rafael Soares Ribeiro, [email protected]

⚠️ **GitHub.com Fallback** ⚠️