Part B Create the entities, subtypes, and relationships according to the business rule below. Then map the entities identified into tables. Do these in SQL developer. BUSINESS RULE: A Seller can be categorized as Retailer or Wholesaler. All sellers have a seller id, first name, last name, contact no and maximum credit. A retailer has a RetailPrice, while a Wholesaler has a WholeSalePrice. Each seller should be categorized only to either a retailer or a wholesaler and cannot be a retailer and a wholesaler at the same time. A seller can have one or more shops and shops must be owned by a seller. A shop can contain many products and many products must be found in at least one shop or more shops. Products Shops price type manufacturer address name

Part B Create the entities, subtypes, and relationships according to the business rule below. Then map the entities identified into tables. Do these in SQL developer. BUSINESS RULE: A Seller can be categorized as Retailer or Wholesaler. All sellers have a seller id, first name, last name, contact no and maximum credit. A retailer has a RetailPrice, while a Wholesaler has a WholeSalePrice. Each seller should be categorized only to either a retailer or a wholesaler and cannot be a retailer and a wholesaler at the same time. A seller can have one or more shops and shops must be owned by a seller. A shop can contain many products and many products must be found in at least one shop or more shops. Products Shops price type manufacturer address name

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

What use cases are best suited for the CROSS APPLY function in SQL?
given data Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary) write the sql queries for the following questions. 1) List all the players who have salary higher than 2M$, and have played for all the teams in the league . 2) List coaches who have also been players, and have coached only teams they once played for. 3)List all the players who won the Stanley Cup during two consecutive years with two different teams.
Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours) Enrollment (regNo, cCode, semester, Grade) Write SQL queries for the following statements. NOTE: you must use “CARTESIAN PRODUCT” query only. Display grade of ‘Ali’ in course ‘CS101’. Display all Grades of ‘Irshad’ in 7th Display names of all courses and grades of ‘Amir’. Display list of courses which are taught in 7th (course name must not repeat) Display list of all students by name and course name they have Display total number of ‘F’ grade by ‘Amir’. Display all those students who have not enrolled any course
QUICK SQL REQUEST (beginner level...) Back at work on Monday morning, the Vice President of Purchasing asks you to help him with the analysis of in-process storage. He asks you to pull information regarding the number of products per storage location. The result of the query should show: - The name of the warehouse (Location) - The number of different products per storage location - The total quantity of products found at this storage location - The average price of all products found at this storage location He mentions that for the moment he is only interested in the locations that have more than 50 distinct products because he wants to start inventorying the biggest locations first. Show the locations in order of smallest to largest number of distinct products. My request is linked to the question but i'm having trouble executing it I would love to know how to do it, thanks in advance.
Using the relations in the appendix, write the following queries in SQL:i. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Your query should use an outer join, and should not use sub-queries.ii. Write the same query as in part a, but using a scalar subquery and not using outer join.iii. Display the list of all course sections offered in spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “—”.iv. Display the list of all departments, with the total number of instructors in each department, without using sub-queries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.
4 and 5
Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours) Enrollment (regNo, cCode, semester, Grade) Write SQL queries for the following statements. NOTE: you must use “CARTESIAN PRODUCT” query only. Display grade of ‘Ali’ in course ‘CS101’. Display all Grades of ‘Irshad’ in 7th Display names of all courses and grades of ‘Amir’. Display list of courses which are taught in 7th (course name must not repeat) Display list of all students by name and course name they have Display total number of ‘F’ grade by ‘Amir’. Display all those students who have not enrolled any course
QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Us the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using: execute topSpenders (2018. 3) 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using: begin topSpenders(2018,10 end: 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent 81…
9. Explain the use of ORDER BY clause in SQL.
Several new built-in operations are available in SQL;2008. To simplify, just enumerate the four (4) applications.
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
Based on the following tables, write appropriate SQL statements to carry out the following tasks: CLIENT CLIENT_NAME LOCATION SAM LEE MELAKA LEE MEI TENG PENANG ΚΙΚΟ ΤΑΝ KL ADAM ALI KL EVA KUMAR MELAKA SALESPERSON SALES_NAME POSITION AREA JAMES ANG JUNIOR MELAKA MARCUS TEO SENIOR PENANG LILY TAY JUNIOR KL (a) Add the following record into the SALESPERSON table. SALES_NAME POSITION AREA JOYCE SENIOR MELAKA (b) Show how all junior salesperson can be removed from SALESPERSON. (c) Change Lily's position as she has been promoted from junior to senior level. (d) Retrieve all clients who are located in Melaka. Sort the result in ascending order by location. (e) Display the number of clients in each location. (f) Display those clients with the surname “Lee". (g) List the salesperson name and position for all senior salesperson from Penang. (h) List the salesperson and client names who are located in the same area.