******************************************************************************************* */ GO PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’ PRINT ‘Read the questions below and insert your queries where prompted. When you are finished, you should be able to run the file as a script to execute all answers sequentially (without errors!)’ + CHAR(10) PRINT ‘This week, there are 8 questions worth 30 points total. The point totals for each question depend on the difficulty of the question. Please be sure to answer each part of each question for full credit. All SQL should be properly formatted.’; PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’ + CHAR(10) + CHAR(10) GO GO PRINT ‘CIS 275, Lab Week 8, Question 1 [2pts possible]: Cleanup ——- First, do Question 2. Then, come back and complete this part. Write SQL statements that drop the tables you created in Question 2 if they already exist. This will prepare the database for the CREATE TABLE commands in Question 2, so that the script will execute without errors. You have the permissions required to create tables in the CIS275Sandboxx database. Hint: The syntax for dropping a table if it exists in SQL looks like this: ‘ /* IF OBJECT_ID(‘ABC_RecipeIngredient’, ‘U’) IS NOT NULL DROP TABLE ABC_RecipeIngredient; */ + ‘ You will also need to drop the view you create for Question 5. The SQL for that looks like: ‘ /* IF OBJECT_ID(‘ABC_all_data’, ‘V’) IS NOT NULL DROP VIEW ABC_all_data; */ + ‘ Hint 2: Foreign key constraints will cause errors unless you drop the tables in the right order. Drop the tables with foreign keys before you drop the tables with the primary keys those tables depend on. ‘ + CHAR(10) GO USE CIS275Sandboxx — — [Insert your code here] — GO PRINT ‘CIS 275, Lab Week 8, Question 2 [8pts possible]: Create Tables ————- Using the ERD from the Lab 8 assignment document (see course shell), create the tables represented in the ERD. For full credit, please pay attention to the following: 0. Examine the sample data in Question 3 first. You”ll want to make sure the data types you pick for each column are compatible with the data we”re storing in the tables. 1. Prefix the table names with your initials (for example, name your table ABC_RecipeIngredient ABC_RecipeIngredient instead of RecipeIngredient. 2. All primary key fields should be declared with PRIMARY KEY constraints. 3. Declare all non-composite primary keys using IDENTITY(1, 1). 4. Create the correct FOREIGN KEY constraints for all foreign keys. 5. The following values are all optional: a. Preparation in Ingredient b. Organization in Chef c. FK_ChefID in Recipe All other values are mandatory. Add the correct data integrity constraints for this. 6. Name in Recipe should be distinct. Add the correct data integrity constraint for this. 7. FK_IngredientID and FK_RecipeID form a composite primary key for the RecipeIngredient table (as well as being foreign keys on their own). 8. If you have two tables with a primary key/foreign key relationship, you”ll need to create the table with the primary key BEFORE you create the table with the foreign key constraint. ‘ + CHAR(10) GO — — [Insert your code here] — GO PRINT ‘CIS 275, Lab Week 8, Question 3 [1pt possible]: Adding Data ———– Change the INSERT INTO statements below to reference the tables you created in Question 2. You should only need to change ABC_ to match your initials. Be sure to change all four statements. Then, uncomment the lines. (remove the /* at the top and the */ at the bottom). ‘ + CHAR(10) GO — — [Insert your code here] /* INSERT INTO ABC_Ingredient VALUES (‘chunky peanut butter’, NULL), — 1 (‘sesame seeds’, ‘toasted’), — 2 (‘ginger’, ‘minced’), — 3 (‘garlic’, ‘minced’), — 4 (‘green onion’, ‘thinly sliced’), — 5 (‘soy sauce’, NULL), — 6 (‘red wine vinegar’, NULL), — 7 (‘sriracha sauce’, NULL), — 8 (‘brown sugar’, NULL), — 9 (‘sesame oil’, NULL), — 10 (‘cilantro’, ‘chopped’), — 11 (‘thin spaghetti’, NULL), — 12 (‘eg…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |