**5. A5: Relational Schema, Validation and Schema Refinement**

We mapped the classes, attributes and multiplicities of our UML in our relation schema using the relation compact notation.<br>
We identified the functional dependencies and showed the relational schema below. It was necessary to make any decomposition since they were already in BCNF.

**5.1 Relational Schema**

|Rule number|RelationName|
|---|---|
|R01|user(<u>idUser</u>, email **UK NN**, name **NN**, password **NN**, bio, isModerator **NN**)
|R02|post(<u>idPost</u>, idUser->user, date **NN CK** date > 2022-01-01, score **NN CK** score > 0, content **NN**)|
|R03|answer(<u>idPost</u>->post, idQuestion->question, isVerified **NN**)|
|R04|question(<u>idPost</u>->post, title **NN**)|
|R05|tag(<u>idTag</u>, name **NN**)|
|R06|community(<u>idCommunity</u> , name **NN**, date **NN**)|
|R07|assUserTag(<u>idUserTag</u>, idUser->user, idTag->tag,isFavorite **NN**, isBlocked **NN CK** isFavorite!=isBlocked)
|R08|followed(<u>idUser</u>->user, <u>idPost</u>->question)|
|R09|userCommunity(<u>idUser</u>->user, <u>idCommunity</u>->community|
|R10|questionTag(<u>idQuestionTag</u>, idTag->tag, idQuestion->question)

We chose to map the generalizations in the Object Oriented strategy (O-O-S), given that the child classes have their own different attributes and as both will have a large number of rows in the table, it would be a waste of space and time.

Legend
- UK = UNIQUE KEY
- NN = NOT NULL
- CK = CHECK

**5.2 Domains**

|Domain Name | Domain Specification|
|---|---|
|Post|ENUM('Question', 'Answer')|
|User |ENUM('Guest', 'Askitter' ,'Moderator')|
|content|VARCHAR(500)|
|bio|VARCHAR(250)|
|name|VARCHAR(25)|

**5.3 Schema validation**

|TABLE R01 | User |
|---|---|
| Keys | {idUser}, {email} |
|Functional Dependencies:||
|FD0101| {idUser} -> {email, name, password, bio, isModerator}|
|FD0102| {email}->{idUser, name, password, bio, isModerator} |
|Normal Form|BCNF|

|TABLE R02 | Post |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0201| {idPost} -> {date, score, content}|
|Normal Form|BCNF|

|TABLE R03 | Answer |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0301| {idPost} -> {idQuestion, isVerified}|
|Normal Form|BCNF|

|TABLE R04 | Question |
|---|---|
| Keys | {idPost}|
|Functional Dependencies:||
|FD0401| {idPost} -> {title}|
|Normal Form|BCNF|

|TABLE R05 | Tag |
|---|---|
| Keys | {idTag}|
|Functional Dependencies:||
|FD0501| {idTag} -> {name}|
|Normal Form|BCNF|

|TABLE R06 | Community |
|---|---|
| Keys | {idCommunity}|
|Functional Dependencies:||
|FD0601| {idCommunity} -> {name, date}|
|Normal Form|BCNF|

|TABLE R07 | AssUserTag |
|---|---|
| Keys | {idUserTag}|
|Functional Dependencies:||
|FD0701|{idUserTag} -> {idUser, idTag, isBlocked, isFavorite} |
|Normal Form|BCNF|

|TABLE R08 | Followed |
|---|---|
| Keys | {idUser, idPost}|
|Functional Dependencies:|none|
|Normal Form|BCNF|

|TABLE R09 | userCommunity |
|---|---|
| Keys | {idUser, idCommunity}|
|Functional Dependencies:|none|
|Normal Form|BCNF|

|TABLE R10 | questionTag |
|---|---|
| Keys | {idQuestionTag}|
|Functional Dependencies:||
|FD1001 {idQuestionTag} -> {idQuestion, idTag}|
|Normal Form|BCNF|

Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF and, therefore, the schema does not need to be further normalised.

**SQL CODE

---- TYPES -----

CREATE TYPE User_ AS ENUM ('Askitter', 'Guest', 'Moderator');

--- TABLE ----

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS community CASCADE;
DROP TABLE IF EXISTS assUserTag CASCADE;
DROP TABLE IF EXISTS followed CASCADE;
DROP TABLE IF EXISTS userCommunity CASCADE;


CREATE TABLE users (
idUser SERIAL PRIMARY KEY,
email TEXT NOT NULL CONSTRAINT user_email_uk UNIQUE,
name TEXT NOT NULL,
password TEXT NOT NULL,
bio TEXT,
isModerador BOOL NOT NULL
);

CREATE TABLE post (
idPost SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
date DATE NOT NULL,
score INTEGER NOT NULL,
content TEXT NOT NULL
);

CREATE TABLE question (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
title TEXT NOT NULL
);

CREATE TABLE answer (
idPost INTEGER PRIMARY KEY REFERENCES post (idPost) ON UPDATE CASCADE,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE,
isVerified BOOLEAN NOT NULL
);

CREATE TABLE tag (
idTag SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);

CREATE TABLE community (
idCommunity SERIAL PRIMARY KEY,
name TEXT NOT NULL,
date DATE NOT NULL
);

CREATE TABLE assUserTag (
idUserTag SERIAL PRIMARY KEY,
idUser INTEGER REFERENCES users (idUser) ON UPDATE CASCADE,
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE,
isFavorite BOOLEAN NOT NULL,
isBlocked BOOLEAN NOT NULL,
CONSTRAINT fav_blocked_ck CHECK (isFavorite != isBlocked)
);

CREATE TABLE followed (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idPost INTEGER NOT NULL REFERENCES post (idPost) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idPost)
);

CREATE TABLE userCommunity (
idUser INTEGER NOT NULL REFERENCES users (idUser) ON UPDATE CASCADE,
idCommunity INTEGER NOT NULL REFERENCES community (idCommunity) ON UPDATE CASCADE,
PRIMARY KEY (idUser, idCommunity)
);

CREATE TABLE questionTag (
idQuestionTag SERIAL PRIMARY KEY,
idQuestion INTEGER REFERENCES question (idPost) ON UPDATE CASCADE, 
idTag INTEGER REFERENCES tag (idTag) ON UPDATE CASCADE
);