# A5: Relational Schema, validation and schema refinement

> Brief presentation of the product.
> Brief presentation of the artefact goals.

## 1. Relational Schema

> The Relational Schema includes the relation schemas, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK.
> Relation schemas are specified in the compact notation:

| Relation reference | Relation Compact Notation |
| ------------------ | ------------------------------------------------ |
| R01 | Table1(__id__, attribute NN) |
| R02 | Table2(__id__, attribute → Table1 NN) |
| R03 | Table3(__id1__, id2 → Table2, attribute UK NN) |
| R04 | Table4((__id1__, __id2__) → Table3, id3, attribute CK attribute > 0) |

## 2. Domains

> The specification of additional domains can also be made in a compact form, using the notation:

| Domain Name | Domain Specification |
| ----------- | ------------------------------ |
| Today | DATE DEFAULT CURRENT_DATE |
| Priority | ENUM ('High', 'Medium', 'Low') |

## 3. Functional Dependencies and schema validation

> To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas is accomplished. Should it be necessary, in case the scheme is not in the Boyce–Codd Normal Form (BCNF), the relational schema is refined using normalization.

| **TABLE R01** | User |
| -------------- | --- |
| **Keys** | { id }, { email } |
| **Functional Dependencies:** | |
| FD0101 | id → {email, name} |
| FD0102 | email → {id, name} |
| ... | ... |
| **NORMAL FORM** | BCNF |

> If necessary, description of the changes necessary to convert the schema to BCNF.
> Justification of the BCNF.

## 4. SQL Code

> SQL code necessary to build (and rebuild) the database.
> This code should also be included in the group's git repository as an SQL script, and a link include here.