Phase P6: Complex DB Reports (SQL, Stored Procedures, Relational Algebra)

Conceptualize and implement 2 more complex analytical calculations for each team member and perform them with SQL.

You need to try to come up with as complex questions as possible and solve them with just one SQL query, so that they can then be used as reports within your application on a quarterly, semi-annual, annual or multi-year level and would give a picture of the long-term operation in your database.

As an example, here are a few possible ideas and directions that you should think about, for a e-commerce oriented project. For other types of projects infer based on these examples:

• products that bring the most profit, customers that bring the most profit, good employees, whether discounts can improve sales and earn more…
• forecast - which products can be sold out very quickly and the stocks should be replenished
• what locations would be a good place for a new store location or a new warehouse would be needed so that customers do not walk around a lot,…
• recommendation - discovering customer interests and recommending appropriate products based on interests, based on similar other customers, based on current trends, …

If you are unsure about the level and complexity of the queries, discuss your ideas with your teacher during consultation hours.

In some cases it might not be possible to solve the problem with pure SQL query, in which case you might turn to solving the problem with using helper views, stored functions and stored procedures.

If you happen to have a good idea for a query, but you lack the appropriate data in your database design, it is advisable to modify the design to include the necessary structures for such data. Any changes should be made to both the ERModel and the RelationalDesign and submitted to the course accordingly.

Finally, write create Relational Algrebra statements for your SQL solutions.

Create a wiki page called AdvancedReports and use the structure from this template:

Advanced Reports

Data requiremnts idea/concept title

Data requirements description.

• Solution SQL
• Solution Relational Algebra

Another idea/concept title

…

AI Use

You are free to use an AI to improve your work, provided that you adhere to the following rules:

• you use AI in this phase of the project only to improve your own initial work
• you use AI to create alternative ideas, that you can learn from and borrow and use in your own work
• you document the changes you have made to your own work based on the ideas from the AI tool in the history above
• you document the entire usage of the AI tool in a new wiki page named AdvancedReportsAIUsage linked here, that you structure according to the following template

Advanced Reports AI Usage

Name of AI service/solution that was used

URL:

Type of service/subscription:

Final result

Diagram:

Results in details / description:

Entire AI usage log

1. Line-by-line Log of all interactions with the AI (prompt and response)
2. …