Draw an ER diagram to represent the database

Assignment Help Database Management System

Reference no: EM133091138

Objectives:
• Learn to implement advance SQL statements
• To practise joining tables for retrieving data
• To practise the use of relational set operators to merge data

Activities

Question 1. Create a new database called Food_Beverages and import the fileFood_Beverages.sql.

Question 2. Draw an ER diagram to represent the database including entities, primary keys, foreign keys, and the relationships

Question 3. Use this new database to implement the SQL statements to complete the following:
a. For the following SELECT statement, how many rows will there be in the output? Explain how you have derived at this number. Describe how you can confirm the number iscorrect.

SELECT P.ProductID, C.CategoryID
FROM Product AS P, Category AS C;

Question 4. Are all the rows in the output of (a) meaningful? How can you modify that SELECT statement so that the rows in the two tables are logically joined and every row in the output ismeaningful?

Question 5. Display the ID and the shipped date of every order along with the ID and name of the company in charge of shippingit.

Question 6. Show the name and the quantity of the product(s) being listed in eachorder.

Question 7. List the full name and the title of the person which each employee reports to. The ID and last name of the employee should be included in theoutput.

Question 8. Display the ID and contact person of the supplier of the discontinued products. The ID of the product and category ID it belongs to should be included in theoutput.

Question 9. Show the average unit price of the products in each category. The name of each category should be included in theoutput.

Question 10. List the name and title of every customer contact person from Berlin, followed by the full name and title of the employees based in London. The city should be included in the output. (hint: use a relational setoperator)

Question 11. Show the orders which have more than 5 products listed in them? The Order ID and the number of products list in it should be in the output.

Question 12. What is the largest discount given to a product being ordered?

Reference no: EM133091138

Questions Cloud

Adopting global benefits strategy : How might today's businesses attract and retain the very best employees around the world by adopting a global benefits strategy?

How much money should you have in the account right now : To achieve your goal of putting a $50,000 down payment on a house at the end of next 5 years, how much money should you have in the account right now

Managing the contractor-customer relationship : Discuss the ethical issues that can arise in contracting. Analyze and discuss the benefits that can arise with contracting.

Calculate the annual tax savings that the firm will realize : If Great Grocery Store is in the 28% tax bracket, calculate the annual tax savings that the firm will realize due to the tax-deductibility

Draw an ER diagram to represent the database : Implement advance SQL statements and practise the use of relational set operators to merge data - Show the name and the quantity of the product(s) being listed

What was the company net income : Operating costs, depreciation and interest were $200 million, $30 million and $25 million respectively. What was the company's net income

Describe strategy and strategic management process : Describe strategy and the strategic management process. Describe an external analysis. Analyze the two levels of the environment.

Prepare a Statement of cash Flow using the INDIRECT method : Question - Prepare a Statement of cash Flow using the INDIRECT method. Not all accounts listed may be used in solving this problem

Customer service training implementation plan : Develop a customer service training implementation plan, including the method of training

User Account

All Pages