JCL

FEUP/DEI & INESC TEC

User Tools

Site Tools


teach:lbaw:artefacts:a05

A5: Relational Schema, validation and schema refinement [04]

This artefact contains the Relational Schema obtained by mapping from the Conceptual Data Model.

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:

Table1(id, attribute NN)
Table2(id, attribute → Table1 NN)
Table3(id1, id2 → Table2, attribute UK NN)
Table4((id1, id2) → Table3, id3, attribute)

UK means UNIQUE and NN means NOT NULL.

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

Today DATE DEFAULT CURRENT_DATE
Priority ENUM ('High', 'Medium', 'Low')

To show the domains of the attributes and the CHECK constraints of attributes and tuples, in addition to this representation, the relational schema is also presented in SQL.

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.

A5 MediaLibrary example | A5 template | A5 checklist

teach/lbaw/artefacts/a05.txt · Last modified: 03/03/2020 15:26 by Correia Lopes