Le Éclair is a French Bakery and Café which specializes in making French breads,
ID: 2477170 • Letter: L
Question
Le Éclair is a French Bakery and Café which specializes in making French breads, cuisine and pastries. They are based in Dallas, Texas and currently are expanding to large metropolitan markets via both corporate expansion and franchisees. They are in need of a database to meet their current needs and projected growth which the CEO, Laurene Bonheur projects to be 25% over the next 5 years. Laurene has asked you to first design a database capable of handling sales transactions Le Éclair. Laurene describes the following sales process to you.
Le Éclair sales products to both customers on a retail basis and products and services to customers on a wholesale basis. Since Laurene was originally a marketing major, she wants to be able to track and contact customers and people interested in Le Éclair and wants a database to be capable of storing customer information regardless of whether or not they have bought anything from Le Éclair. Laurene notes she hopes to use Big Data concepts to generate repeat business and fully expects retail and wholesale customers to continue to purchase from Le Éclair. For simplicity purposes, Laurene wants only one customer to be listed on each sales transaction, but a customer is not required. She also has asked for simplicity regarding employee data and has asked that employees be stored in only one location in the database regardless of their various functions (Head Chef, Sous Chef, Pastry Chef, Bus Boy, Cashier, corporate employees, etc.). As a result not all employees will be able to handle sales transactions. Laurene has asked for the ability to track the number of sales each cashier performs so she can analyze the cashier’s performance over time and the sales transactions they process and sales they generate. Each sales transaction will require the use of only one employee.
For the Sales Process in particular, store and wholesale sales will and must have an inventory item, but Laurene notes she wants the ability to track the number of items she expects each sale to average over time. She wants Inventory items to be tracked in the database, specifically in the inventory table so that items can be added without having a sales transaction. She expects her mostly popular item Le Chocolat Éclair to be sold thousands of times and wants to database to accommodate this expectation. Laurene notes that for her wholesale customers they purchase food items just like retail customers, but in bulk. Sometimes they ask Laurene to provide catering services, but not always, for which she asks and pays employees. Catering services involve: food prep, service, and clean-up, to name a few. Laurene mentions that services like, food items should be maintained in a service table and that future services can be added without being associated with a specific sales transaction. She notes that Le Éclair’s most popular catering service is Le Crepe Station, where both desert and savory crepes are made fresh. To help Laurene analyze all catering data, she expects the service table to contain information about the rate of each service. She then wants the employee table to store information about the employee’s salary and wages (some employees earn a salary some earn a wage) so that she can then track employee billed rates to the service rates. Laurene also notes that customers will be given discounts on times and services based on amounts purchased over time. Because of this, Laurene notes that employees like chefs and waiters can perform many catering services (i.e. a sous chef prepares food, but also can work at the crepe station and can even help wait tables where necessary), but not all employees will perform catering services. Each service performed must have an employee, but some services require several employees.
Laurene also describes the cash receipts process. She notes retail customers must pay in full, but that certain wholesale customers can pay for catering services in payments for up to three months (they must leave a deposit for 25% of the catering billed if they select this option). Laurene, as mentioned, expects repeat customers and as such will have customers generate several cash receipts, but since customers can be added to the database without a sales transaction, they might not have a cash receipt either. Cash receipts are generated from other transactions than sales and customers, such as franchise fees from franchisees. Regardless, each cash receipt can only come from one source. Sometimes wholesale customers will combine payments for multiple sales into one check and Laurene wants the database to provide for this detail. Only cashiers can handle cash receipts (Chef are really bad at this Laurene notes), but will handle many over the course of their employment. Each cash receipt regardless must be processed by one employee. Laurene notes that cash receipts are not immediately deposited, but when they are they are deposited to one bank account# (i.e. never split). Cash accounts are not always opened/maintained with cash receipts (i.e. sometimes just transfers), but if they do receipt cash receipt deposits will do so with many over their lifetime.
Explanation / Answer
Laurene also describes the cash receipts process. She notes retail customers must pay in full, but that certain wholesale customers can pay for catering services in payments for up to three months (they must leave a deposit for 25% of the catering billed if they select this option). Laurene, as mentioned, expects repeat customers and as such will have customers generate several cash receipts, but since customers can be added to the database without a sales transaction, they might not have a cash receipt either.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.