Marcia’s Dry Cleaning Case Study: Ms. Marcia Wilson owns and operates Marcia’s D
ID: 3765878 • Letter: M
Question
Marcia’s Dry Cleaning Case Study:
Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out from the competition by providing superior customer service. She wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email.
Assume that Marcia has hired you as a database consultant to develop an operational database having the following four tables:
CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)
INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax, TotalAmount)
INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity, UnitPrice,
ExtendedPrice)
SERVICE (ServiceID, ServiceDescription, UnitPrice)
Assume that Marcia’s Dry Cleaning has the following personnel: two owners, a shift manager, a part-time seamstress, and two salesclerks. Prepare a memo that addresses the following points:
1. The need for database administration.
2. Your recommendation as to who should serve as database administrator. Assume that Marcia’s Dry Cleaning is not sufficiently large to need or afford a full-time database administrator.
For the employees described in part A, define users, groups, and permissions on data in these four tables. Create a table like that in Figure 6-16 in the book chapter. Don’t forget to include yourself as you are the technical consultant.
Explanation / Answer
So coming to memo:
We will create three types of users....
1) sales workers
2) shift manager
3) Adminstrator.
sales workers can only insert new invoice...
GRANT SELECT, INSERT ON database.* TO 'salesWorkers'@'localhost';
shift manager can create or update
GRANT SELECT, INSERT, UPDATE ON database.* TO 'shiftManager'@'localhost';
Administrator
GRANT ALL PRIVILEGES ON database.* TO 'admin'@'localhost';