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.
AI Use
You are free to use an AI to improve your modeling, provided that you adhere to the following rules:
- you use AI in this phase of the project only to improve your own initial model
- you use AI to create an alternate model for ideas that you can borrow and use in your own model
- you document the changes you have made to your own model based on the ideas from the AI tool in the model history above
- you document the entire usage of the AI tool in a new wiki page named RelationalDesignAIUsage linked here, that you structure according to the following template
