# 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 reference | Relation Compact Notation |
|:------------------:|:------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| R01 | shopper(<ins>shopper_id</ins>, email __UK NN__, password __NN__, is_admin __NN DF__ False) |
| R02 | client(<ins>client_id</ins> -> shopper, name __NN__, id_image __NN__ __DF__ 1 -> image) |
| R03 | supplier(<ins>supplier_id</ins> -> shopper, name __NN__, address __NN__, post_code __NN__, city __NN__, description __NN__, accepted __NN__, id_image __NN__ __DF__ 1 -> image) |
| R04 | purchase(<ins>purchase_id</ins>, id_client __NN__ -> client, paid NN CK paid > 0, date NN CK date <= today, purchase_type __NN CK__ purchase_type __IN__ purchase_type) |
| R05 | item(<ins>item_id</ins>, id_supplier __NN__ -> supplier, name __NN__, price __NN CK__ price > 0, stock __NN CK__ stock > 0, description __NN__, active __NN__, rating __NN CK__ (rating >=1 AND rating <= 5)), is_bundle __NN__ |
| R06 | image(<ins>image_id</ins>, path __NN__) |
| R07 | item_info(<ins>id_purchase</ins> -> purchase, <ins>id_item</ins> -> item, price __NN CK__ price > 0, amount __NN CK__ (amount > 0) |
| R08 | tag(<ins>tag_id</ins>, value __UK NN__) |
| R09 | tag_item(<ins>id_tag</ins>->tag, <ins>id_item</ins>->item) |
| R10 | coupon(<ins>coupon_id</ins>, code __UK NN__, name __NN__, amount __NN CK__ amount > 0, description __NN__, expiration __NN CK__ expiration > today, coupon_type __NN CK__ coupon_type __IN__ coupon_type) |
| R11 | product(<ins>product_id</ins>->item, unit_type __NN CK__ unit_type __IN__ unit_type) |
| R12 | bundle_product(<ins>id_bundle</ins>->item, <ins>id_product</ins>-> product, quantity __NN__ quantity >= 0) |
| R13 | ship_detail(<ins>ship_det_id</ins>, first_name __NN__, last_name __NN__, address __NN__, door_n __NN__, post_code __NN__, district __NN__, city __NN__, country __NN__, phone_n __NN__, id_client __NN__ -> client) |
| R14 | credit_card(<ins>cc_id</ins>, cardN __NN__, expiration __NN__, cvv __NN__, holder __NN__, id_client __NN__ -> client) |
| R15 | review(<ins>id_client</ins> __NN__ -> client, <ins>id_item</ins> __NN__ -> item, rating __NN CK__ rating >= 1 AND rating <= 5, description __NN__) |
| R16 | favorite(<ins>id_client</ins> __NN__ -> client, <ins>id_item</ins> __NN__ -> item) |
| R17 | cart(<ins>id_client</ins> __NN__ -> client, <ins>id_item</ins> __NN__ -> item, quantity __NN__) |
| R18 | product_image(<ins>id_product</ins> __NN__ -> product, <ins>id_image</ins> __NN__ -> image) |
### 2. Domains
| Domain Name | Domain Specification |
| ------------- | ------------------------------ |
| today | DATE DEFAULT CURRENT_DATE |
| unit_type | ENUM ('Kg', 'Un') |
| coupon_type | ENUM ('%', '€') |
| purchase_type | ENUM ('SingleBuy', 'Day', 'Week', 'Month') |
### 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** | shopper |
| -------------- | --- |
| **Keys** | {shopper_id},{email} |
| **Functional Dependencies:** | |
| FD0101 | {shopper_id} → {email, password, is_admin} |
| FD0102 | {email} → {shopper_id, password, is_admin} |
| **NORMAL FORM** | BCNF |
| **TABLE R02** | client |
| -------------- | --- |
| **Keys** | {client_id} |
| **Functional Dependencies:** | |
| FD0201 | {client_id} → {name, id_image} |
| **NORMAL FORM** | BCNF |
| **TABLE R03** | supplier |
| -------------- | --- |
| **Keys** | {supplier_id} |
| **Functional Dependencies:** | |
| FD0301 | {supplier_id} → {name, address, post_code, city, description, accepted, id_image} |
| **NORMAL FORM** | BCNF |
| **TABLE R04** | purchase |
| -------------- | --- |
| **Keys** | {purchase_id} |
| **Functional Dependencies:** | |
| FD401 | {purchase_id} → {id_client, amount, date, purchase_type} |
| **NORMAL FORM** | BCNF |
| **TABLE R05** | item |
| -------------- | --- |
| **Keys** | {id} |
| **Functional Dependencies:** | |
| FD501 | {id} → {id_supplier, name, price, stock, description, active, rating, is_bundle} |
| **NORMAL FORM** | BCNF |
| **TABLE R06** | image |
| -------------- | --- |
| **Keys** | {image_id} |
| **Functional Dependencies:** | |
| FD601 | {image_id} → {path} |
| **NORMAL FORM** | BCNF |
| **TABLE R07** | item_info |
| -------------- | --- |
| **Keys** | {id_purchase, id_item} |
| **Functional Dependencies:** | |
| FD701 | {id_purchase, id_item} → {price, amount} |
| **NORMAL FORM** | BCNF |
| **TABLE R08** | tag |
| -------------- | --- |
| **Keys** | {tag_id} |
| **Functional Dependencies:** | |
| FD0801 | {tag_id} → {value} |
| **NORMAL FORM** | BCNF |
| **TABLE R9** | tag_item |
| -------------- | --- |
| **Keys** | {id_tag, id_item} |
| **Functional Dependencies:** | |
| **NORMAL FORM** | BCNF |
| **TABLE R10** | coupon |
| -------------- | --- |
| **Keys** | {coupon_id}, {code} |
| **Functional Dependencies:** | |
| FD01001 | {coupon_id} → {code, name, amount, description, expiration, coupon_type} |
| FD01002 | {code} → {coupon_id, name, amount, description, expiration, coupon_type} |
| **NORMAL FORM** | BCNF |
| **TABLE R11** | product |
| -------------- | --- |
| **Keys** | {product_id} |
| **Functional Dependencies:** | |
| FD01101 | {product_id} → {unit_type} |
| **NORMAL FORM** | BCNF |
| **TABLE R12** | bundle_product |
| -------------- | --- |
| **Keys** | {id_bundle, id_product} |
| **Functional Dependencies:** | |
| FD1201 | {id_bundle, id_product} → {quantity}|
| **NORMAL FORM** | BCNF |
| **TABLE R13** | ship_detail |
| -------------- | --- |
| **Keys** | {ship_det_id} |
| **Functional Dependencies:** | |
| FD1301 | {ship_det_id} → {first_name, last_name, address, door_n, post_code, district, city, country, phone_n, id_client} |
| **NORMAL FORM** | BCNF |
| **TABLE R14** | credit_card |
| -------------- | --- |
| **Keys** | {cc_id} |
| **Functional Dependencies:** | |
| FD1401 | {cc_id} → {card_n, expiration, cvv, holder, id_client}|
| **NORMAL FORM** | BCNF |
| **TABLE R15** | review |
| -------------- | --- |
| **Keys** | {id_client, id_item} |
| **Functional Dependencies:** | |
| FD1501 | {id_client, id_item} → {rating, description} |
| **NORMAL FORM** | BCNF |
| **TABLE R16** | favorite |
| -------------- | --- |
| **Keys** | {id_client, id_item} |
| **Functional Dependencies:** | |
| (none) | |
| **NORMAL FORM** | BCNF |
| **TABLE R17** | cart |
| -------------- | --- |
| **Keys** | {id_client, id_item} |
| **Functional Dependencies:** | |
| FD1701 | {id_client, id_item} → {quantity} |
| **NORMAL FORM** | BCNF |
| **TABLE R18** | product_image |
| -------------- | --- |
| **Keys** | {id_product, id_image} |
| **Functional Dependencies:** | |
| (none) | |
| **NORMAL FORM** | BCNF |
Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF and therefore there is no need to be refined it using normalisation.
### 4. SQL Code
[Link para script (não adicionado ainda)](#)
Sign in with Wallet
Connect another wallet