Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Create composite primary key sql server management studio

20/11/2021 Client: muhammad11 Deadline: 2 Day

Using Microsoft SQL Server Management Studio 2012

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
('egg noodles, 1/8" thick', NULL), -- 13
('rice vinegar', NULL), -- 14
('sesame paste', NULL), -- 15
('smooth peanut butter', NULL), -- 16
('sugar', NULL), -- 17
('ginger', 'finely grated'), -- 18
('chili garlic paste', NULL), -- 19
('cucumber', 'peeled, seeded, cut into 1/8" by 1/8" by 2" sticks'), -- 20
('roasted peanuts', 'chopped'); -- 21

INSERT INTO ABC_Chef
VALUES
('Aarti Sequeria', 'Food Network'),
('Eddie Schoenfeld', 'Red Farm');

INSERT INTO ABC_Recipe
VALUES
(1, 'Peanut Noodles for Miss Piggy!', 'Drop spaghetti into boiling water, and cook until al dente, according to package instructions.
Meanwhile, throw peanut butter, sesame seeds (reserve 1 tbsp for garnish), ginger, garlic, soy
sauce, red wine vinegar, sriracha, brown sugar/honey, sesame oil, and cilantro into a big food
processor. Whiz ''em up until the sauce is smooth. Taste and adjust seasonings.

Toss veggies with a little sauce in a separate bowl. Drain cooked pasta, and then drizzle with
a little sesame oil. Toss with a pair of tongs. This will help keep the pasta from sticking.
In a big bowl, toss pasta, veggies, reserved sesame seeds, and sauce together.'),

(2, 'Takeout-Style Sesame Noodles', 'Bring a large pot of water to a boil. Add noodles and cook until barely tender, about 5 minutes;
they should retain a hint of chewiness. Drain, rinse with cold water, drain again and toss with
a splash of sesame oil.

In a medium bowl, whisk together the remaining 2 tablespoons sesame oil, the soy sauce, rice vinegar,
sesame paste, peanut butter, sugar, ginger, garlic and chili-garlic paste.

Pour the sauce over the noodles and toss. Transfer to a serving bowl, and garnish with cucumber
and peanuts.');

INSERT INTO ABC_RecipeIngredient
VALUES
(1, 1, '1', 'cup'),
(2, 1, '1/4', 'cup'),
(3, 1, '6', 'tbsp'),
(4, 1, '4', 'cloves'),
(5, 1, '1', 'bunch'),
(6, 1, '1/2', 'cup'),
(7, 1, '4', 'tsp'),
(8, 1, '1', 'tsp'),
(9, 1, '1', 'tbsp'),
(10, 1, '1', 'tsp'),
(11, 1, '1', 'handful'),
(12, 1, '1', 'lb'),
(20, 1, '1', 'whole'),
(13, 2, '1', 'lb'),
(10, 2, '2', 'tbsp'),
(6, 2, '3 1/2', 'tbsp'),
(14, 2, '2', 'tbsp'),
(15, 2, '2', 'tbsp'),
(16, 2, '1', 'tbsp'),
(17, 2, '1', 'tbsp'),
(18, 2, '1', 'tbsp'),
(4, 2, '2', 'tsp'),
(19, 2, '2', 'tsp'),
(20, 2, '1/2', 'whole'),
(21, 2, '1/4', 'cup');
*/

--

GO
PRINT 'CIS 275, Lab Week 8, Question 4 [4pts possible]:
Adding another Recipe
---------------------
Write additional INSERT INTO statements to add the following recipe to the database:

Ree''s Simple Sesame Noodles
By Ree Drummond, Food Network

Quantity Units Ingredient Preparation
-------- ---------- -------------------- --------------------
4 cloves garlic minced
4 whole green onion thinly sliced
1/4 cup soy sauce NULL
3 tbsp sesame oil NULL
2 tbsp rice vinegar NULL
2 tbsp sugar NULL
12 oz thin noodles NULL
1/4 cup canola oil NULL
1/2 tsp hot chili oil NULL

Bring a large pot of water to a boil. Cook the noodles according to the package instructions.

Meanwhile, whisk together the soy sauce, canola oil, sesame oil, sugar, vinegar, chili oil and
garlic in a bowl. Taste and adjust the ingredients as needed.

Drain the noodles. Pour the sauce over the warm noodles and toss to coat. Sprinkle with the
green onions and toss. Serve in a bowl with chopsticks. Yummy!

Note: Reuse existing ingredients instead of creating duplicates. E.g., minced garlic is already
in the database as IngredientID 4, so you don''t need to create a new row for that ingredient in
the Ingredient table.

Hint: Look at how the recipe data is structured in Question 3 and in the ERD.
' + CHAR(10)

GO

--
-- [Insert your code here]

--

GO
PRINT 'CIS 275, Lab Week 8, Question 5 [3pts possible]:
Create a view
-------------
Create a view named all_data (prefixed with your initials). After you''ve added the statement to
create the view, go back to Question 1 and add a SQL command to drop the view if it exists. Then,
add a statement that SELECTs * from your view (you will need to add a GO before the SELECT statement).

Your view should include the following columns:

RecipeID, ChefID, IngredientID, RecipeName, ChefName, ChefOrg, Instructions, IngredientName,
Quantity, Units, Preparation

Do not do any type conversion in your view, just return the raw data in the underlying tables.

Your data should look like this (except for the type conversion):

RecipeID ChefID IngredientID RecipeName ChefName ChefOrg Instructions IngredientName Quantity Units Preparation
-------- ------ ------------ ------------------------------ -------------------- --------------- ----------------------- ------------------------- -------- ---------- ---------------
1 1 1 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... chunky peanut butter 1 cup NULL
1 1 2 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... sesame seeds 1/4 cup toasted
1 1 3 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... ginger 6 tbsp minced
1 1 4 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... garlic 4 cloves minced
1 1 5 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... green onion 1 bunch thinly sliced
1 1 6 Peanut Noodles for Miss Piggy! Aarti Sequeria Food Network Drop noodles into bo... soy sauce 1/2 cup NULL
...
3 3 22 Ree''s Simple Sesame Noodles Ree Drummond Food Network Bring a large pot of... thin noodles 12 oz NULL
3 3 23 Ree''s Simple Sesame Noodles Ree Drummond Food Network Bring a large pot of... canola oil 1/4 cup NULL
3 3 24 Ree''s Simple Sesame Noodles Ree Drummond Food Network Bring a large pot of... hot chili oil 1/2 tsp NULL
' + CHAR(10)

GO

-- [Insert your code here]

--

GO
PRINT 'CIS 275, Lab Week 8, Question 6 [2pts possible]:
Indexes
-------
Create indexes for Name in the Ingredient table and Name in the Chef table. Be sure that both
indexes include your initials in their name.
' + CHAR(10)

GO

--
-- [Insert your code here]

--

GO
PRINT 'CIS 275, Lab Week 8, Question 7 [8pts possible]:
Changing a Recipe
-----------------
Use UPDATE to make the following changes to Peanut Noodles for Miss Piggy!

Substitute 2 tbsp rice vinegar for 4 tsp red wine vinegar.
Substitute 1/2 tbsp sugar for 1 tbsp brown sugar.
Substitute egg noodles, 1/8" thick for this spaghetti.
Change the instructions to the following:

------------------------------------- Text starts here ------------------------------------------
Drop noodles into boiling water, and cook until al dente, according to package instructions.
Meanwhile, throw peanut butter, sesame seeds (reserve 1 tbsp for garnish), ginger, garlic, soy
sauce, rice vinegar, sriracha, sugar, sesame oil, and cilantro into a big food
processor. Whiz ''em up until the sauce is smooth. Taste and adjust seasonings.

Toss veggies with a little sauce in a separate bowl. Drain cooked noodles, and then drizzle with
a little sesame oil. Toss with a pair of tongs. This will help keep the pasta from sticking.
In a big bowl, toss noodles, veggies, reserved sesame seeds, and sauce together.
-------------------------------------- Text ends here -------------------------------------------

Add a SELECT * statement after the last update that shows all the data in the all_data view.

Note: Be sure not to change any of the other ingredients/recipes, and DO NOT insert any new
rows into the database.
' + CHAR(10)

GO

--
-- [Insert your code here]

--

GO
PRINT 'CIS 275, Lab Week 8, Question 8 [3pts possible]:
Cleanup
-------
After the UPDATEs in the previous question, there are now some ingredients that aren''t being
used in any recipes. Write a DELETE statement that deletes all unused ingredients. Use a SELECT
statement as a subquery in the WHERE clause of the DELETE statement that finds the correct
ingredients to delete (i.e., don''t hard-code the ingredient IDs for the ingredients to delete).

Then, SELECT * from all_data and from Ingredient to verify that your delete worked correctly.

Hint: You will be deleting ingredients 7, 9, and 12.
' + CHAR(10)

GO

--
-- [Insert your code here]

--

GO
-------------------------------------------------------------------------------------
-- This is an anonymous program block. DO NOT CHANGE OR DELETE.
-------------------------------------------------------------------------------------
BEGIN
PRINT '|---' + REPLICATE('+----',15) + '|';
PRINT ' End of CIS275 Lab Week 8' + REPLICATE(' ',50) + CONVERT(CHAR(12),GETDATE(),101);
PRINT '|---' + REPLICATE('+----',15) + '|';

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Ideas & Innovations
Engineering Help
Assignment Hub
Calculation Master
Unique Academic Solutions
George M.
Writer Writer Name Offer Chat
Ideas & Innovations

ONLINE

Ideas & Innovations

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$17 Chat With Writer
Engineering Help

ONLINE

Engineering Help

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$49 Chat With Writer
Assignment Hub

ONLINE

Assignment Hub

I have read your project details and I can provide you QUALITY WORK within your given timeline and budget.

$36 Chat With Writer
Calculation Master

ONLINE

Calculation Master

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$35 Chat With Writer
Unique Academic Solutions

ONLINE

Unique Academic Solutions

I find your project quite stimulating and related to my profession. I can surely contribute you with your project.

$22 Chat With Writer
George M.

ONLINE

George M.

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$17 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Message passing in os - Technical project paper information systems security - SLR Paper - Analyzing & Visualizing Data - Ba entry control board uk - 3 step problem solving approach and organizing framework - 3 tier architecture in php - NURS 601- REPLY TO DISCUSSION RUTH - Managing organizational change a multiple perspectives approach ppt - Princ. Of Mgmnt - Netiquette Discussion - The context of business understanding the canadian business environment pdf - Lina compra zapatos italianos en el centro - How to find the stem of a latin noun - Beverly hills puppies inc - Quadrennial homeland security review 2018 pdf - How to find stationary points on a curve - Singapore airlines address for cover letter - Deliverable 4 - Hypothesis Tests/ Word Doc and Excel File - The ones who walk away from omelas essay questions - What you pawn i will redeem thesis statement - Contrec batch controller 414 - Grade 5 words to spell - Order the following functions by growth rate n - Libby libby and short financial accounting 8th edition - Eigrp feasibility condition definition - Ctecs workplace readiness skills practice test - How does embedding the public key in a digital certificate protect it from impersonators - Crypto Week 1 discussion - Marketing - 978 1 259 29061 9 - Chronicled ico - Landscape Architect - Week 5 _African American Studies - Gantt chart for inventory management system - El fútbol en europa es muy similar al fútbol americano. cierto falso - Short essay - Physical chem take home exam - What is the difference between a soliloquy and a monologue - Shel silverstein poems with similes and metaphors - What is a growing perpetuity - Comprehensive pharmacy services human resources phone number - Coal a human history - Which philosopher proposed that nerve pathways allowed for reflexes - Thesis statement for dante's inferno - Beneath her shirt a book was eating her up - Toyota - Simple 5 paragraph essay - Calculating heat loss through a wall - I need 4000 words Report the Importance of Personal Finance - Importance of engineering ethics ppt - Ads b equipment manufacturers - Joy jalal landmark forum leader - How to make genogram on microsoft word - What is fad,x, the x-component of the force exerted by the infinite wire on segment ad of the loop? - Review document and answer questions in detail, no plagiarism. - X20 ashington to newcastle - Butl_ Learnign Feamework - Analysis on the Threats Defense Argument - Patricia benner novice to expert theory - Personal Statement to nursing school - Soccer coach responsibilities resume - Performance Task: The Hiring Process from A–Z - How to make wbs in word - Design Choices - Can someone help with a research paper about prison reform? - Swallow the air quotes - Grand harvest square raid blade and soul - Chern's case study executive summary - How does nike use management information systems - Internal and external sources of whs information and data - Apple inc in case analysis - Educ 200 quiz 1 - Struggles with society - Taussig technologies corporation has been - Persuasive speech topics about veterans - The chillenden murders watch online - Open university teaching assistant - Icp ms skimmer cone - Cisco cucm 10.5 eol - Letter 2: Negative Message - Gartner magic quadrant for security awareness computer-based training - Four consecutive odd integers whose sum is 56 - Example of hess law of constant heat summation - My choice computech sp road - Week 5 environmental science - NEED A REWRITE ON WEEK ONE Quality Control - English to snacklish - Leonardo hotels head office - Metsec lattice beams brochure - 7 eugenia street rivett - Woolworths online car insurance - Assembly line vs continuous flow - The fashion channel case study solutions - How did kiowa die in the things they carried - My country tis of thee sweet land of liberty - Adverb comparative superlative list - Dork diaries reading fair project - Wild nights wild nights emily dickinson sparknotes - Art spiegelman in the shadow of no towers pdf - Egotism - Topic 1: Accounting Research and the Codification -2