SQL 1. Every time a Recipe is changed (For an existing recipe new ingredient is
ID: 3868609 • Letter: S
Question
SQL
1. Every time a Recipe is changed (For an existing recipe new ingredient is added, or the Qty of an existing ingredient is changed), record this in audit table. Also, update the Recipe date in the Recipes table.
2. Create indexes for all the tables, which might benefit from indexing. The way the application is used is searching by Category Name, Property Name, Ingredient Name, Recipe Name, Recipe Creator and Recipe Name.
3. Create a view listing the Recipe Name and the number of ingredients used for that recipe.
Reclnads RecipePropertv Property Ingds ProplD PropertyName RID 7 Ingdld Ingdld ProplD Mesurld IngdName Qty Persons Person!D FirstName LastName AddressID Recipes RID RName Mesmts RecipeOwners person!D Mesurld RecipeDate MDesc RID Addresses Address!D StAddress City StateCode Zip States RecipeCats RID CID StateCode StateName Categories CatNameExplanation / Answer
1. For the first part, as there are two things that need to be completed collectively (change in RecIngds Table, and change in Recipes Table), the best way is to use a transaction. SQL Transactions are, among other things, atomic - which means that every statement within a transaction will either succeed or fail collectively.
Further, within the transaction, we should first try to insert a new row with given RecipeID and IngredientID. If it was a new ingredient, we are done. If, however, the ingredient already existed and only the quantity was changed, this insertion will raise an error. We can then catch the error, and try to modify the quantity of an existing row instead.
2. Any field which may be used for searching will benefit by getting an index made for it. The downside of indexing is that it adds to the space requirements (as the data structure used for indexing - B Trees - take up almost as much space as your data itself). So building an index on a field will mostly be a judgement call from your end - which parameters are valueable enough to trade space for time.
In essence, you would like to build indices on those fields only which are most used for searching. If your database is small though, or if you have plenty of space, you can go ahead and build indices for all of the said fields.
To build an index, use the following statement -
3. To create the desired view, use the following statement -
Note :- As your question did not specify the db used, all statements are written for SQL Server. There will be minor syntax changes for other dbs such as MySQL, Oracle or PostgreSQL.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.