1) A new company recently started a business where they buy highly discounted items in bulk...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1) A new company recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. Create a relational model to manage the database of this company. You have the freedom to make necessary assumptions about relations and attributes to create a realistic model. 2) Make sure you define primary and foreign key constraints properly. Give descriptive names to relations and attributes. 3) Design the database schema in LucidChart following the conventions used in the textbook. For each relation schema, provide justifications for your choice of the primary and foreign keys. 4) Use SQL commands to create the schema of your database. Also use SQL commands to enter at least 5 dummy tuples in each relation. 5) Create examples of SQL query employing each of the SQL clauses: a. Union – 1 example b. Aggregate + Group by - 2 examples c. Nested query -2 examples For each example, include the query in natural language and corresponding SQL statements. Add additional tuples if required to run your queries successfully. For each query, provide the screenshot of the output of the query on MYSQL server. 1) A new company recently started a business where they buy highly discounted items in bulk from different companies who are going out of business or need to get rid of additional items to save warehouse space. This new company has retail stores in big cities across the country where they sell the items to individuals. They also do business as wholesalers where they sell items in bulk to local retailers in small cities. Therefore, they have two types of customers: individuals and small retailers. The company has four departments: purchase, warehouse, sales (retail and wholesale) and accounting. They employ five different types of employees: managers, accounting staffs, warehouse staffs, retail associates, and purchase agents. Each staff, agent and associate works under a manager of the respective department. Purchase agents hunt for discount items, and they get a commission based on the items they are successfully able to buy. Create a relational model to manage the database of this company. You have the freedom to make necessary assumptions about relations and attributes to create a realistic model. 2) Make sure you define primary and foreign key constraints properly. Give descriptive names to relations and attributes. 3) Design the database schema in LucidChart following the conventions used in the textbook. For each relation schema, provide justifications for your choice of the primary and foreign keys. 4) Use SQL commands to create the schema of your database. Also use SQL commands to enter at least 5 dummy tuples in each relation. 5) Create examples of SQL query employing each of the SQL clauses: a. Union – 1 example b. Aggregate + Group by - 2 examples c. Nested query -2 examples For each example, include the query in natural language and corresponding SQL statements. Add additional tuples if required to run your queries successfully. For each query, provide the screenshot of the output of the query on MYSQL server.
Expert Answer:
Related Book For
Business Statistics For Contemporary Decision Making
ISBN: 978-1118749647
8th edition
Authors: Black Ken
Posted Date:
Students also viewed these accounting questions
-
Do wholesalers and retailers need to worry about new product planning just as a producer needs to have an organized new-product development process? Explain your answer.
-
You need to hire some new employees to staff your start-up venture. You know that potential employees are distributed throughout the population as follows, but you cant distinguish among them: What...
-
You are a CPA who works for a local accounting firm. While having lunch at Willie's Communication Skills Diner last Thursday, you overheard Beth Murray describe how Bart (her spouse) was able to get...
-
Yamini and Fatima, two students of Class IX of a school, together contributed Rs. 100 towards the Prime Ministers Relief Fund to help the earthquake victims. Write a linear equation which satisfies...
-
What form of partnership allows some of the investors to limit their liability? Explain briefly.
-
East Hill Home Healthcare Services was organized on January 1, 2014, by four friends. Each organizer invested $10,000 in the company and, in turn, was issued 8,000 shares of $1.00 par value stock. To...
-
When calculating a firms return on total assets (ROA) ratio, some investment professionals modify the ROA ratio by adding back the interest expense paid by a firm to its creditors, on a net-of-taxes...
-
budget director for Kanosh Cleaning Services prepared the following list of expected selling and administrative expenses. All expenses requiring cash payments are paid for in the month incurred...
-
With DHCP, the Discover packet is a(n) _________, so if it hits a router interface it will need a(n) _______________ or else it will be dropped.
-
Match the four functions of payroll activities with their related internal controls: 1. Hiring employees. 2. Timekeeping. 3. Preparing the payroll. 4. Paying the payroll. ________ a. An independent...
-
It will cost $2,600 to acquire a small ice cream cart. Cart sales are expected to be $1,400 a year for three years. After the three years, the cart is expected to be worthless as that is the expected...
-
Chris and Mary are married, but Chris has recently moved out of the family residence and intends to file for a divorce. The parties have one eight year-old son named Max. Chris seeks your advice as...
-
Question: How can the arbitration method solve disputes between two states according to international law?
-
Write a java program to print below pattern? 1234 1234 1234 1 2 3 4
-
Writeajavaprogramtoprintbelow pattern? **** **** **** ****
-
A project costs 100,000 with a planned life of 12 years. The benefits have been forecast to be 15,000 per year. What will be the IRR (to the nearest integer) for this project?
-
Question 1: Describe the method of generating involute curve in spur gear. Question 2: Design an experiment to measure the error of roundness for a cylindrical shaft.
-
An auto-parts manufacturer is considering establishing an engineering computing center. This center will be equipped with three engineering workstations each of which would cost $25,000 and have a...
-
Suppose that in the bookkeeping operation of a large corporation the probability of a recording error on any one billing is .005. Suppose the probability of a recording error from one billing to the...
-
Use a chi-square goodness-of-fit test to determine whether the following observed frequencies are distributed the same as the expected frequencies. Let α =.01. Category 214 235 279 281...
-
Use the data given to test the following hypotheses. Assume the data are normally distributed in thepopulation. Ho: -7.48 Ha: < 7.48 x = 6.91, n 24, 1.21, = .01
-
Oligopolists may charge a price lower than the profit-maximizing price to _________ new firms from entering the market. This strategy will be effective when new firms face _________ costs than...
-
In the traditional prisoners dilemma, a(n) _________ matrix is used to illustrate the various possibilities and results for the two parties.
-
In _________, each firm sets its policy without consulting other firms.
Study smarter with the SolutionInn App