A5: Relational Schema, Validation and Schema Refinement [05] §
This artefact contains the Relational Schema obtained 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)
Primary keys are underlined. 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 as an annex. ¶
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. ¶