Database Management - Recipe Database We need some immediate information, and a
ID: 3702792 • Letter: D
Question
Database Management - Recipe Database
We need some immediate information, and a plan that will help us safely collect information on a regular, ongoing basis so we can continue to best serve our customers.
What are your recommendations how to create a system that provides this kind of information without slowing down our production database? We are chaning the names of some of our recipes but don’t want to lose sight of the fact that they’re the same. How do we manage this challenge?
In the memo please speak to an ETL and DW strategy, and speak to the concerns about slowly-changing dimensions.
Explanation / Answer
Recommendation : We need to create a database tables which should have various dishes and their ingredients. First step is to create a table which will contain different food categories. Name this table as 'food categories'. Fields can be breads, fish, beef, pork, poultry, soup, pasta, sides, desserts.
Second step is to create a table which will contain name of the recipe and it's details. Name this table as 'recipe detail'. Fields can be recipe name, description, food category (foreign key from above table), ingredient1, quantity1, instruction1, ingredient2, quantity2, instruction2, attachment. Instruction field will contain instruction for each ingredient how much it should be added and for how much time it should be cooked before adding the next ingredient. Attachment can contain photos for various dishes and ingredients.
Note that in second step receipe table will contain information for only 1 serving.
Changing the name of recipe challenge : To manage this challenge we can simple add another column with the name 'new recipe name'. Or we can also create another table named 'recipe' with fields recipe name, new recipe name, food category recipe id. Here recipe name will be foreign key from 'recipe detail' (from second step) and food category will be foreign key from table 'food category'. And we would require to add a column in 'recipe detail' named recipe id which will be foreign key from recipe table.
ETL and DW strategy : We will first create a Businees Requirement Document (BRD) which is understandable to any normal person as well as any technichian guy, so that the requirements can be ful filled and cross checked.
Next step is to start implementing these requirements using any of the ETL tools such as Informatica, Ab-initio.
To implement this we need to first extract the data. Data can extracted from different source systems like sql database, csv files, etc. Then we need to transform the data in the desired format. Like in our case we will need to do the tranformation in such a way that we can populate our recipe detail and recipe table from different source systems. And lastly we will load the data in our target table i.e recipe and recipe detail table.
To do the above implemetation we will create a logical data model which demostrate how our source systems relate to each other, cardinality between the tables and contained columns within each entity. This entity relationship daigram (ERD) will help us to reference our proposed data model and put it into practice.
Data Model : Data model refers to the logical structure of the database that includes entities, attributes and relationships.
Slowly Changing Dimension Concerns : Slowly changing dimesions basically refers to the changes which will occur slowly but unpredictably. In our case it can be like changing recipe name, changing ingredients and their quantity, adding some new ingredients in our existing recipe. So, we can handle all this issue easily with the help help of slowly changing dimension technique. Using this technique we can keep historical data as well as new data in our database.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.