Instructions on Phase P2: Logical and Physical Design, DB Creation (DDL)

To start work on this phase, it is necessary that you have an approval on the previous phase. If you have made changes in the previous phase after working on this phase, redo those changes in this phase and all subsequent phases. If you made changes to this phase, where you deviate from the model in the previous phase, make corrections to the model in the previous phase. Both phases should present an identical view on the structure of the data.

Develop a relational model with a relational schema of your database based on one of the transformation methods (stable, partial, mapping - according to your wishes and in your opinion which method is most appropriate in your case, if you don't have some specific arguments for another method, do it with partial transformation).

Implement the resulting relations as tables in the assigned project database according to the following instructions:

• You are given access to specific PostgreSQL project databases at the Faculty premises for your project. Consult the ​https://develop.finki.ukim.mk/eprms EPRMS system, where in the Databases section you will be given the necessary parameters and instructions on accessing your project's database.
• In your assigned database, you will need to create a new schema under the name project, which will be considered the official schema where you will create the project and from where it will be viewed. In the project schema, you should always have the latest version of the database for your project. This database and this schema will be used in all subsequent phases.
• In the project schema, you should create tables with all the details: attributes, data types, keys, other constraints, references to appropriate other tables according to the relational model
• You can create as many other schemas as you want, for various experiments and tests, and if needed for other tasks.

Enter sample data in all created tables. The sample data items should seem realistic, and appropriately and logically related among each other. The data should simulate real situations. You should create as many rows as necessary to simulate the necessary situations of your project.

Create a new wiki page RelationalDesign to document the entire work and link it to the frontpage. The wiki page should have the structure described in the following template.

Relational model

Descriptive representation of the relational schema (syntax as used in lectures/exercises - listed relational schemas, attributes, marked primary and secondary keys)

DDL script for creating the database schema and objects:

• The script should (re-)create the schema, all the tables, with all constraints, and all other relevant database objects.
• The script should work in an empty database to fully construct everything needed for the project, and should also work in a database where the schema, tables and other objects are already created and they need to be re-created. So drop existing objects and create them again.
• The script should be named schema_creation.sql
• Link the script in this section in the RelationalDesign wiki page as an attachment to the page.

DML script for filling tables with data:

• The script should (re-)create all the data in your tables
• The script should work with empty tables to fully load all needed data for the project, and should also work in a database where the tables already have data and need to be emptied and the data should be imported again.
• The script should be named data_load.sql
• Link the script in this section in the RelationalDesign wiki page as an attachment to the page.

Relational diagram

• DBeaver is the recommended tool for working with the assigned database, if you follow the instructions for connecting to the database.
• In DBeaver it is possible to create a relational schema diagram for the entire project schema. Double click on the project schema to open it's properties dialog, then switch to the ER diagram tab. The diagram will be automatically created based on the created objects in the schema.
• Switch the diagram to use crow-feet notation.
• Visually adjust the diagram to have the same positioning of tables and relations as the corresponding entities and relations in the ER diagram in the previous phase.
• Export the diagram to a file named relational_schema.jpg and attach it in this section.