Skip to content

a5

Jump to bottom
Martim de Carvalho e Sousa Pinto da Silva edited this page Jun 12, 2020 · 1 revision

A5: Relational Schema, validation and schema refinement

The project consists in developing a global marketplace which specializes in the sale of gaming related digital products using redemption keys.

In this fifth artefact the main goal is to develop the physical schema of the database that will be used by the database. By the end of the this iteration we accomplished:

  • Translation of the UML to a relational model design
  • Normalization of the original relational model
  • Creation of the SQL schema script

1. Relational Schema

Relation reference Relation Compact Notation
R01 category(id, name UK NN)
R02 genre(id, name UK NN)
R03 platform(id, name UK NN)
R04 image(id, url UK NN)
R05 product(id, name NN UK, name_tsvector DF NULL, weight_tsvector DF NULL, description, category → category, image → image NN DF 1, launch_date NN, deleted DF false NN, num_sells NN DF '0' CK (num_sells >= 0))
R06 product_has_genre(genre → genre NN, product →product NN)
R07 product_has_platform(genre → platform NN, product →product NN)
R08 regular_user(id, username UK NN, name_tsvector DF NULL, weight_tsvector DF NULL, email UK NN, description, password NN, rating NN CK (rating >= 0 AND rating <= 100), birthdate NN birthdate CK (age(birthdate) >= '18 years'), paypal, image → image NN DF '0', num_sells NN DF '0' CK (num_sells >= 0))
R09 offer(id, price NN CK (price > 0), init_date NN CK (init_date <= now()) DF now(), final_date CK (final_date IS NULL or final_date >= init_date), profit CK (profit >= 0), id_platform → platform NN , seller → regular_user, product → product, stock NN DF 1 CK (stock >= 0)
R10 discount(id, rate NN CK (rate > 0 AND rate < 100), start_date NN CK (start_date >=now()), end_date NN CK (end_date >= start_date, offer → offer NN)
R11 banned_user(regular_user → regular_user)
R12 admin(id, username UK NN, email UK NN, description, password NN, image → image NN DF '0')
R13 ban_appeal(banned_user → banned_user, ban_appeal NN, date NN DF now() CK (date <= now()), admin → admin)
R14 orders(number UK NN, date NN CK (date <= now()) DF now(), buyer → regular_user)
R15 key(id, key NN UK, price_sold DF NULL CK (price_sold IS NULL OR price_sold > 0), offer → offer NN, orders -> order DF NULL CK (orders IS NULL AND price_sold IS NULL) OR (orders IS NOT NULL AND price_sold IS NOT NULL))
R16 feedback(id, evaluation NN, comment, evaluation_date NN DF NOW CK (evaluation_date <= NOW()), buyer → regular_user, key → key NN UK)
R17 report(id, date NN DF now() CK (date <= now()), description NN, status NN, title NN, key → key UK NN, reporter → regular_user, reportee → regular_user CK (reporter <> reportee))
R18 message(id, date NN CK (date <= now()) DF now(), description NN, regular_user → regular_user, admin → admin, CK ((regular_user IS NULL AND admin IS NOT NULL) OR (regular_user IS NOT NULL AND admin IS NULL)), report → report NN)
R19 cart(id, buyer → regular_user, offer → offer)
R20 about_us(id,description NN)
R21 faq(id,question NN, answer NN)
Table 1: Relational schema table

Note: UK means UNIQUE KEY, NN means NOT NULL, DF means DEFAULT, CK means CHECK, IN means IS NULL, INN means IS NOT NULL and N means NULL

2. Domains

Domain Name Domain Specification
- -
Table 2: Domains table

3. Functional Dependencies and schema validation

TABLE R01 category
Keys { id }, { name }
Functional Dependencies:
FD0101 id → {name}
FD0102 name → {id}
NORMAL FORM BCNF
Table 3: FD's category

TABLE R02 genre
Keys { id }, { name }
Functional Dependencies:
FD0201 id → {name}
FD0202 name → {id}
NORMAL FORM BCNF
Table 4: FD's genre

TABLE R03 platform
Keys { id }, { name }
Functional Dependencies:
FD0301 id → {name}
FD0302 name → {id}
NORMAL FORM BCNF
Table 5: FD's platform

TABLE R04 image
Keys { id }, { url }
Functional Dependencies:
FD04001 id → {url}
FD04002 url → {id}
NORMAL FORM BCNF
Table 6: FD's image

TABLE R05 product
Keys { id }, { name }
Functional Dependencies:
FD0501 id → {name,name_tsvector,weight_tsvector, description, category, image, deleted, launch_date, num_sells}
FD0502 name → {id,name_tsvector,weight_tsvector, description, category, image, deleted, launch_date, num_sells}
NORMAL FORM BCNF
Table 7: FD's product

TABLE R06 product_has_genre
Keys { genre, product }
Functional Dependencies: none
NORMAL FORM BCNF
Table 8: FD's product_has_genre

TABLE R07 product_has_platform
Keys { platform, product }
Functional Dependencies: none
NORMAL FORM BCNF
Table 9: FD's product_has_platform

TABLE R08 regular_user
Keys { id }, { username }, { email }
Functional Dependencies:
FD0801 id → {username, name_tsvector, weight_tsvector, email, description, password, rating, birthdate, paypal, image, num_sells}
FD0802 username → {id, email, name_tsvector, weight_tsvector, description, password, rating, birthdate, paypal, image, num_sells}
FD0803 email → {id, username, name_tsvector, weight_tsvector, description, password, rating, birthdate, paypal, image, num_sells}
NORMAL FORM BCNF
Table 10: FD's regular_user

TABLE R09 offer
Keys { id }
Functional Dependencies:
FD0901 id → {price, init_date, final_date, profit, platform , regular_user, product, stock}
NORMAL FORM BCNF
Table 11: FD's offer

TABLE R10 discount
Keys { id }
Functional Dependencies:
FD01001 id → {rate, start_date, end_date, offer}
NORMAL FORM BCNF
Table 12: FD's discount

TABLE R11 banned_user
Keys { regular_user }
Functional Dependencies: none
NORMAL FORM BCNF
Table 13: FD's banned_user

TABLE R12 admin
Keys { id }, { username }, { email }
Functional Dependencies:
FD012001 id → {username, email, description, password, image}
FD012002 username → {id, email, description, password, image}
FD012003 email → {id, username, description, password, image}
NORMAL FORM BCNF
Table 14: FD's admin

TABLE R13 ban_appeal
Keys { banned_user }
Functional Dependencies:
FD013001 banned_user → {ban_appeal, date, admin}
NORMAL FORM BCNF
Table 15: FD's ban_appeal

TABLE R14 orders
Keys { number }
Functional Dependencies:
FD014001 number → {date, buyer}
NORMAL FORM BCNF
Table 16: FD's order

TABLE R15 key
Keys { id }, { key }
Functional Dependencies:
FD015001 id → {key, price_sold, orders, offer}
FD015002 key → {id, price_sold, orders, offer}
NORMAL FORM BCNF
Table 17: FD's key

TABLE R16 feedback
Keys { order_number }, { key }
Functional Dependencies:
FD016001 order_number → {key, date, evaluation, comment, evaluation_date, buyer}
FD016002 key → {order_number, date, evaluation, evaluation_date, comment, buyer}
NORMAL FORM BCNF
Table 18: FD's feedback VER

TABLE R17 report
Keys { id }, { key }
Functional Dependencies:
FD017001 id → {key, date, description, title, status, reporter, reportee}
FD017002 key → {id, date, description, title, status, reporter, reportee}
NORMAL FORM BCNF
Table 19: FD's report

TABLE R18 message
Keys { id }
Functional Dependencies:
FD018001 id → {date, description, regular_user, admin, report}
NORMAL FORM BCNF
Table 20: FD's message

TABLE R19 cart
Keys { id }
Functional Dependencies:
FD01901 id → {buyer, offer}
NORMAL FORM BCNF
Table 21: FD's cart

TABLE R20 about_us
Keys { id }
Functional Dependencies:
FD020001 id → {description}
NORMAL FORM BCNF
Table 22: FD's about_us

TABLE R21 faq
Keys { id }
Functional Dependencies:
FD021001 id → {question,answer}
NORMAL FORM BCNF
Table 23: FD's faq

4. SQL Code

Access the SQL SCRIPT here

-----------------------------------------
-- Drop old schmema
-----------------------------------------

DROP SCHEMA IF EXISTS  public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

-----------------------------------------
-- Tables
-----------------------------------------

CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE genre (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE platform (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE image (
  id SERIAL PRIMARY KEY,
  url TEXT NOT NULL UNIQUE
);

CREATE TABLE product (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  name_tsvector tsvector DEFAULT NULL,
  weight_tsvector  tsvector DEFAULT NULL,
  description TEXT,
  category INTEGER REFERENCES category (id) ON DELETE SET NULL ON UPDATE CASCADE,
  image INTEGER DEFAULT 1 NOT NULL REFERENCES image (id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  deleted BOOLEAN NOT NULL DEFAULT FALSE,
  launch_date DATE NOT NULL,
  num_sells INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT num_sells_chk CHECK (num_sells >= 0)
);

CREATE TABLE product_has_genre (
  genre INTEGER NOT NULL REFERENCES genre(id) ON DELETE CASCADE ON UPDATE CASCADE,
  product INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (genre, product)
);

CREATE TABLE product_has_platform(
  platform INTEGER REFERENCES platform(id) ON DELETE CASCADE ON UPDATE CASCADE,
  product INTEGER REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (platform, product)
);

CREATE TABLE regular_user (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  description TEXT DEFAULT NULL,
  name_tsvector tsvector DEFAULT NULL,
  weight_tsvector  tsvector DEFAULT NULL,
  password TEXT NOT NULL,
  rating INTEGER DEFAULT NULL,
  birth_date date NOT NULL,
  paypal TEXT,
  image INTEGER NOT NULL DEFAULT 0 REFERENCES image(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  num_sells INTEGER NOT NULL DEFAULT 0,

  CONSTRAINT rating_ck CHECK (rating >= 0 AND rating <= 100),
  CONSTRAINT birthdate_ck CHECK (date_part('year', age(birth_date)) >= 18),
  CONSTRAINT num_sells_ck CHECK (num_sells >= 0)
);

CREATE TABLE offer (
  id SERIAL PRIMARY KEY,
  price REAL NOT NULL,
  init_date date NOT NULL DEFAULT NOW(),
  final_date date,
  profit REAL DEFAULT 0,
  platform INTEGER NOT NULL REFERENCES platform(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  seller INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
  product INTEGER REFERENCES product(id) ON DELETE SET NULL ON UPDATE CASCADE,
  stock INTEGER NOT NULL DEFAULT 1,    
  CONSTRAINT price_ck CHECK (price > 0),
  CONSTRAINT init_date_ck CHECK (init_date <= NOW()),
  CONSTRAINT final_date_ck CHECK (final_date IS NULL OR final_date >= init_date),
  CONSTRAINT profit_ck CHECK (profit >= 0),
  CONSTRAINT stock_ck CHECK (stock >= 0)
);

CREATE TABLE discount (
  id SERIAL PRIMARY KEY,
  rate INTEGER NOT NULL,
  start_date date NOT NULL,
  end_date date NOT NULL,
  offer INTEGER NOT NULL REFERENCES offer(id) ON DELETE CASCADE ON UPDATE CASCADE,
  
  --   TODO:
--   CONSTRAINT start_date_ck CHECK (start_date >= NOW()),
  CONSTRAINT end_date_ck CHECK (end_date > start_date),
  CONSTRAINT rate_ck CHECK (rate >= 0 AND rate <= 100)
);

CREATE TABLE banned_user (
  regular_user INTEGER PRIMARY KEY REFERENCES regular_user(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE admin (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  description TEXT,
  password TEXT NOT NULL,
  image INTEGER NOT NULL DEFAULT 0 REFERENCES image(id) ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE ban_appeal (
  banned_user INTEGER PRIMARY KEY REFERENCES banned_user(regular_user) ON DELETE CASCADE ON UPDATE CASCADE,
  admin INTEGER REFERENCES admin(id) ON DELETE SET NULL ON UPDATE CASCADE,
  ban_appeal TEXT NOT NULL,
  date date NOT NULL DEFAULT NOW(),
  
  CONSTRAINT date_ck CHECK(date <= NOW())
);

CREATE TABLE orders (
  number SERIAL PRIMARY KEY,
  date DATE NOT NULL DEFAULT NOW(),
  buyer INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
    
  CONSTRAINT date_ck CHECK(date <= NOW())
);

CREATE TABLE key (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL UNIQUE,
  price_sold REAL DEFAULT NULL,
  offer integer NOT NULL REFERENCES offer(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  orders integer DEFAULT NULL REFERENCES orders(number) ON DELETE RESTRICT ON UPDATE CASCADE,
  
  CONSTRAINT price_ck CHECK(price_sold IS NULL OR price_sold > 0),
  CONSTRAINT sold_key_ck CHECK((price_sold IS NULL AND orders IS NULL) or (price_sold IS NOT NULL AND orders IS NOT NULL))
  
);

CREATE TABLE feedback (
  id SERIAL PRIMARY KEY,
  evaluation BOOLEAN NOT NULL,
  comment TEXT,
  evaluation_date DATE NOT NULL DEFAULT NOW() CONSTRAINT fb_date_ck CHECK(evaluation_date <= NOW()),
  buyer INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
  key INTEGER UNIQUE NOT NULL REFERENCES key(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE report (
  id SERIAL PRIMARY KEY,
  date date NOT NULL DEFAULT NOW(),
  description TEXT NOT NULL,
  title TEXT NOT NULL,
  status BOOLEAN NOT NULL,
  key INTEGER NOT NULL UNIQUE REFERENCES key(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  reporter INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
  reportee INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
  
  CONSTRAINT user_ck CHECK(reporter <> reportee),
  CONSTRAINT date_ck CHECK(date <= NOW())
);

CREATE TABLE message (
  id SERIAL PRIMARY KEY,
  date date NOT NULL DEFAULT NOW(),
  description TEXT NOT NULL,
  regular_user INTEGER REFERENCES regular_user(id) ON DELETE SET NULL ON UPDATE CASCADE,
  admin INTEGER REFERENCES admin(id) ON DELETE SET NULL ON UPDATE CASCADE,
  report INTEGER NOT NULL REFERENCES report(id) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT date_ck CHECK(date <= NOW()),
  CONSTRAINT user_type_ck CHECK((regular_user IS NULL AND admin IS NOT NULL ) OR (regular_user IS NOT NULL AND admin IS NULL))
);

CREATE TABLE cart (
  id SERIAL PRIMARY KEY,
  buyer INTEGER NOT NULL REFERENCES regular_user(id) ON DELETE CASCADE ON UPDATE CASCADE,
  offer INTEGER NOT NULL REFERENCES offer(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE about_us (
  id SERIAL PRIMARY KEY,
  description TEXT NOT NULL
);

CREATE TABLE faq (
  id SERIAL PRIMARY KEY,
  question TEXT NOT NULL,
  answer TEXT NOT NULL
);

Revision history

Changes made to the first submission:

  • Removed classes associated with the deleted product
  • Added new elements: derived and those related with the ts_vector
  • Added new XOR restrictions.
  • Fixed Foreign Key NN restrictions to work correctly with instructions ON UPDATE/DELETE SET NULL
  • Updated the schema validation according the changes made in the relation model
  • Updated the SQL script

GROUP2043, 25/03/2020

Clone this wiki locally