Our Services

Get 15% Discount on your First Order

A pharmaceutical retail company possesses various stores and manages its

November 15, 2021

A pharmaceutical retail company possesses various stores and manages its current sales, procurement and materials availability at the stores through Excel sheets. The company executives are now finding this process of data maintenance tedious and would like to develop a complete data warehouse architecture to store historical data and to help making better business decisions. For this reason, you are asked to design a star schema to help manage and record the company order operations, with information related to the ordered medicines, the suppliers, the various stores information and when orders were made. Your star schema should be designed so that the following information can be obtained:

• What is the name and complete address (Street, City, Zip code) of the supplier associated with a specific medicine order?

• What is the complete address (Street, City, Zip code) of the store associated with an order made on a specific day, month, or year?

• Not all medicine can be taken by both kids and adult. Actually, each medicine is designed for people within a specific age range, recorded using a starting age and an ending age. We are interested in knowing the name of all medicine available for anyone within a specific age range.

• How many orders of a specific medicine were made by a specific store on specific day, month or year? Each store is represented by its own store code. You can also consider the following information:

• The order cost should be recorded as facts.

• The data mart will contain 4 years worth of data

• The data mart will be used to manage 3 different stores.

• Each store carries no more than 300 different medicines at the same time

• The medicines can be supplied by 5 different suppliers Based on this information, answer the following questions:

a. Design a star schema for this problem. (10 pts)

b. Estimate the number of rows in the fact table. (2 pts)

c. Based on your star schema, write the SQL code allowing you to answer the following questions: (8 pts, 4 pts each)

• What is the name and complete address (Street, City, Zip code) of the supplier associated with a medicine named “Tylenol” and suited for anyone above 18 years old?

• For each city, indicate the total amount of order cost of all medicines ordered by a store in this city in November 2020. Your query should display the list of cities where stores are located and the associated total order cost.

Share This Post

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Ship, CruiseShip, CargoShip Classes, and Displayable Interface Design an abstract

Ship, CruiseShip, CargoShip Classes, and Displayable Interface Design an abstract Ship class that has the following members: • A field for the name of the ship (a string).• A field for the year that the ship was built (a string). • A constructor and appropriate accessor and mutators.• A toString

What can movies and television shows communicate to people about

What can movies and television shows communicate to people about gender roles and identities? If you were to put on your cultural anthropologist “hat” and analyze a program, you would think about the messages it conveys regarding gender roles and identities. For example, “Leave it to Beaver” and other

Financial Management: Capital Rationing- Compare and contrast the

Compare and contrast the Internal Rate of Return (IRR), the Net Present Value (NPV) and Payback approaches to capital rationing. **do not define the NPV, IRR and the Payback Period.** All that is needed is the comparison and difference between the following 1) (NPV and IRR), 2) (IRR and Payback)

. Compare and contrast service learning and civic engagement as

Text book: “The SocialPsychology of Prosocial Behavior” Chapter 7 – Being the Helper and Being Helped Each answer should be a minimum of two paragraphs. Use examples to support your answers. 1. What is known as the combination of empathy, a sense of responsibility, and the capacity to feel concern

The United States spends far too much political energy on forcing a few states not to proliferate nuclear weapons

Topic: Nuclear proliferation is not a big deal. The United States spends far too much political energy on forcing a few states not to proliferate nuclear weapons, far out of proportion to their threat to our national security. Do you agree or disagree? Why or why not? (Will attach an

Human Design (Historical, Cultural) Aspects

d Year Architecture), date, class, instructor, nice image representing the project (not included in 4 written minimum pages) ● Formal Outline of paper ● Mention why you chose the particular case study areas ● Summary of case study area’s regions using mostly primary sources (census report data) and secondary sources

Course Name: ITS-699 – Information Technology Project Abstract – 1

Course Name: ITS-699 – Information Technology Project Abstract – 1 page Why did your group select the topic for your research project (min 75-words)? Cite Two Peer-Reviewed References What are the major contributions of this project to your organization (min 100-words)? Cite Two Peer-Reviewed References Can you use this research

It is desired to abate the transmitted vibration and shock

It is desired to abate the transmitted vibration and shock of a diesel generator installed in the engine room of a large boat, from the machine to the hull and from the hull to the machine. This should be done without making the diesel generator experience excessive motion. The machine

Assistive technology (AT) is a supplemental resource that may be

Assistive technology (AT) is a supplemental resource that may be a mandated requirement for many students with disabilities within their IEPs. In a typical resource room, there are a variety of AT devices that may be incorporated into instruction by the special education teacher. Research two low tech, two high

UMGC Alternative Project Management Software Tools Essay Nursing Assignment Help

Microsoft Project is a Microsoft proprietary software application developed for project management. Using the Internet resources describe this software application. What are its features and functions that help track the EHR implementation project progress? What are the pros and cons of using it for managing EHR projects? What could be

Poverty in the classroom

A four-unit research paper in APA formatting centered around the effects of poverty in education. The first unit needs to be about the effects poverty has on students in the classroom and needs to be 8-10 pages. Second unit is on the effects of substance abuse on students in the

Assessment Description Moral and ethical development in children affects student

Assessment Description Moral and ethical development in children affects student learning and school/classroom interactions. Teachers need to ensure moral and ethical instruction and interactions are taking place in the classroom/school. For this assignment, you will create a 12-15 slide digital presentation on the subject of the moral and ethical development

High Performing Work Team Analysis

Organizations often use surveys or questionnaires to obtain information regarding employee satisfaction, the organizational issues impacting staff performance, perceptions of pay and benefits, organizational culture, etc. Sometimes, questionnaires are used in classes for staff development and focus on behavioral styles, conflict styles, or team behaviors. Additionally, they may be used

Before viewing the Chapter 5 content, use the Gender Traits

Before viewing the Chapter 5 content, use the Gender Traits Handout (posted above) as a reference to answer the following questions: Which traits do you think are more often linked with men, women, or both? You can include traits that are not on the handout and even include traits that

Pretend that you have been tasked with informing your colleagues

Premium Paper Help is a professional writing service that provides original papers. Our products include academic papers of varying complexity and other personalized services, along with research materials for assistance purposes only. All the materials from our website should be used with proper references.

What challenges may occur with collaboration? Give an example of

What challenges may occur with collaboration? Give an example of the importance of interprofessional teamwork. Be sure to apply concepts from the readings and research. You must cite (with reference) at least one source: your textbook, scholarly resources, or ATI textbook (no blogs, Wiki, or other school of nursing website)

Final Project Case Studies Case 1. Hot burglary At 16,

Final Project Case Studies Case 1. Hot burglary At 16, Ray seemed to have a good life. He had a high IQ and a 4.0 GPA. He had lots of friends, and they enjoyed hanging out. What no one knew was that Ray’s home life was miserable. His stepfather,

19-year-old student, Sally, has been arrested by the police for

19-year-old student, Sally, has been arrested by the police for possession of a small amount of marijuana while driving home from a college party. Assume that the police officer had probable cause to stop Sally for drunk driving. During the search of Sally’s pockets incident to her arrest, the police find an unregistered handgun,