Question
In this task, you will design the tables for a relational database from the description and err diagram given below. You need to use ms
In this task, you will design the tables for a relational database from the description and err diagram given below. You need to use ms access for the task.
Description: mix n’ drinks, a company that sells liquor beverages, wants to become technologically savvy in order to enhance business. The business sells different kinds of liquors such as Bacardi, baileys, Johnny walker, and wine such as zinfandel, Chablis, sauvignon, chardonnay, and side items to add to drinks such as mixers, margarita salt, and napkins. The purpose of the database is to help the manager of mix n’ drinks better track customers, employees, transactions, products, and inventory information. If certain types of items run out, the manager or an employee can order a new batch of liquor/items. Employees will use this system to handle customers’ purchases and returns. The store must be able to keep track of the entire inventory for the products (liquor, wines, and miscellaneous items). Product information includes product id, product name, and sale price. For wine products, the database records year, origin (country), winery, and types of grapes included in a wine. Liquor products include information about the amount (in liters) and alcohol (percentage). Each product may have one or more inventories: inventory id (unique only for each product), original price, arrival date, and number of items in stock. The database will keep track of information about customers who have the mix n’ drinks membership. It includes the customer name, address, customer id, date of birth, discount rate for special members, and email address for the customers in the mailing list. The manager also keeps track of employee information such as first name, last name, social security number, phone number, supervisor, and the date hired. when a customer places an order (purchase or return) with the items, the employee scans the items, and the system records transaction_id, the employee who did the transaction, the payment method (for purchases), reason (for return), and date and time of the transaction, each product that was purchased (or returned), and quantity of the product. For each return, the system also records a transaction number of the corresponding purchase (identified from the receipt). If a customer has a store membership, the system identifies the membership information of the customer for the transaction. This database will help the manager keep track of the inventory, and evaluate employees’ job performance. He can also make marketing strategies based on the analysis of purchase patterns of customers. Some example queries are: which products are top sellers? Who is the best employee of the last month for sales? How many bottles of liquor have been sold on a certain day? Which products are out of stock? Who are the wine lovers?
Step by Step Solution
3.38 Rating (154 Votes )
There are 3 Steps involved in it
Step: 1
Description The Database contains the following tables Customer The customer table has unique primar...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started