SkyMall
SkyMall is a specialty publishing firm headquartered in Phoenix, Arizona. Founded in 1990, the company produces a quarterly in-flight publication, SkyMall, with an annual circulation of approximately 20 million copies distributed in airplane seat pockets. SkyMall is a multi-channel, direct marketer offering products through direct marketers and manufacturers through its SkyMall catalog. The SkyMall catalog is seen by approximately 88% of all domestic air passengers in the United States, reaching more than 650 million air travelers annually. SkyMall also offers turn-key merchandise and fulfillment solutions for loyalty marketing programs.
It originally offered goods from other companies' catalogs for same-day delivery to customers arriving at select U.S. airports or, if the customer preferred, to their home or office via express shipment. To accomplish the same-day delivery promise, SkyMall operated its own warehouses located near the selected airports.
Shortly after launching, the company purchased contracts from another company that allowed it to offer catalog merchandise to travelers flying many of the nation's air carriers. This move required a significant shift in SkyMall's focus and operations, ultimately forcing the company to abandon its same-day delivery service.
SkyMall now offers goods from other companies' catalogs for dropship to the customer via third-party transport firms. Several companies that participate in the catalog include:
Goods offered are organized in the following categories:
The company needs improved customer relations. SkyMall has seen the need to streamline their catalog sales in order to keep up with the growing customer demand for products. Presently, each system is manual. Inventory is difficult to track and too many errors are made in counting inventory levels. Stock orders to suppliers are not organized and many times, more than a few products are out of stock. Product backorders are becoming difficult to handle and customers often complain of late deliveries. More telephone operators have been allotted for the upcoming season, but current phone inquiries cannot be handled in a timely manner because the salespeople do not have access to present inventory levels. Last month, the entire customer billing system broke down. Because they were not able to collect receivables, they could not
page 2
process any more sales. There has also been a major demand to change the catalogs from paper-based to electronic-based to satisfy recent environmental demands.
SkyMall has decided to ask for very detailed Requests For Proposal (RFP) from various groups in competition for the lucrative contract to revise their current system into a complete redesign from scratch, moving from manual systems to an integrated, auditable SQL Server database with an Access front end and Tableau connection to help them make better business decisions.
Your team has decided to compete for the contract and will revise the current manual systems starting with a redesign of the database. SkyMall would like to keep data on each company and will store their inventory, customer orders, customer billing and customer shipping data including shipping charges.
The goal of your team is to computerize the inventory system by storing it in a database to control inventory levels and to keep track of customer orders.
The following are outputs that are mandatory for SkyMall needs. These must be in your final deliverable, and you should create your own samples based on SkyMall’s business and your database design.
1. Which company is most popular?
2. Names of customers making more than 5 purchases in the previous month.
3. Names of customers that have not made a purchase in the last 3 months.
4. Descriptions of items that have never been sold.
5. Items that have not been sold in the past 4 months.
6. Items for which QOH is less than 5.
7. A listing of customers in decreasing order by number of purchases.
8. Customers making three or more purchases of the same item (different sales; one purchase involving multiple sales of the same item is not considered).
9. Items with monthly average sales exceeding last year’s average for the same month.
1.SKYMALL is the most popular company in united states.
2.Names of customers making more than 5 purchases in the previous month.
3.Names of customers that have not made a purchase in the last 3 months.
the entire customer billing system broke down. Because they were not able to collect receivables, they could not
process any more sales. so the entire billing related customers did not made a purchase in the last 3 months.
4.Descriptions of items that have never been sold.
5.Items that have not been sold in the past 4 months.
Get Answers For Free
Most questions answered within 1 hours.