Proposed Database for Loretta’s University Cleaners

1. Proposed Database for Loretta’s University Cleaners KwameAmpem Paitrick Lawyer Brandon Zachary April 15, 2011

2. Objective The objective of this project is to assist a local client in enhancing their DBMS for more efficient storage, accesses and security.

3. Current System • FabriCare Management System • Windows-based application that provides dry cleaning/laundry software for dry cleaning businesses with an average sized clientele • Data is stored in Microsoft Access DBMS • Easy to maintain

4. System Overview

5. Current System This screenshot shows the possible types of alterations that can be done on a given set of garments.

6. Current System This screenshot shows the laundry services that are provided by Loretta’s University Cleaners.

7. Current System This screenshot shows the shoe repair menu. Once an option is selected, you will be asked to enter more information about the shoe.

8. Current System This screenshot shows an in-depth look at what happens after selecting a type of shoe. You are allowed to enter information about the shoe (ex. shoe color).

9. Normalization Forms

10. Normalization Forms

11. Normalization Forms

12. Normalization Forms

13. Normalization Forms • Relations: • INVOICE(IvoiceNo, Name, Number, StreetName, City, ZipCode, {Quantity, GarmentType, ServiceType, Price}) • INVENTORY(ItemName, Quantity, Cost) • ORDERFORM(OrderNo, OrderDate, Quantity, Price, ItemName, SupplierName, SupplierAdd, SupplierNum)

14. 1NF Normalization • INVOICE • InvoiceNum & GarmentType & ServiceType -> All Attributes • GarmentType & ServiceType -> Price • Name & Phone -> StreetName, City, Zip • Primary Key: • Invoice & GarmentType & ServiceType • Partial Functional Dependency: • GarmentType & ServiceType are apart of primary and price depends on those attributes

15. 1NF Normalization • INVENTORY • ItemName -> Quantity, Cost • Primary Key: • ItemName • Partial Functional Dependency: • There are none for this Relation

16. 1NF Normalization • ORDERFORM • OrderNo & SupplierNum -> all attributes • SupplierNum -> SupplierName, SupplierAddress • Primary Key: • OrderNo & SupplierNum • Partial Functional Dependency: • SupplierNum is a part of the primary key but supplierNamea SupplierAddressdepend on that attribute

17. 2NF Normalization • Removed Partial Functional Dependencies • INVOICE • (InvoiceNo, Name, Phone, GarmentType, ServiceType, Quantity) • GARMENT • (InvoiceNo, GarmentType, ServiceType, Price) • CUSTOMERINFO • (Name, Phone, StreetName, City, Zip)

18. 2NF Normalization • INVENTORY • (ItemName, Quantity, Cost) • ORDER • (OrderNo, SupplierName, OrderDate, Quantity, Price, ItemName) • SUPPLIERINFO • (SupplierName, SupplierAddress)

19. 3NF Normalization No Transitive Functional Dependencies INVOICE(InvoiceNo, Name, Phone, GarmentType, ServiceType, Quantity) GARMENT(InvoiceNo, GarmentType, ServiceType, Price) CUSTOMERINFO(Name, Phone, StreetName, City, Zip) INVENTORY(ItemName, Quantity, Cost ORDER(OrderNo, SupplierName, OrderDate, Quantity, Price, ItemName) SUPPLIERINFO(SupplierName, SupplierAddress)

20. Superclass Staff {Mandatory, Or} * .. 1 Inventory 1 .. * 1 .. * Has Front Office Back Office 1 .. * 1 .. * ItemName (PK) Gathers Creates Renders 1 .. 1 * .. * Invoice Receives Services Customer Info InvoiceNo (PK) {Mandatory, Or} Alterations Shoe Repair Press Laundry

21. Loretta’s University Cleaners Employee Login Screen Please enter your ID: ENTER The user will enter his/her ID to gain access to the system. If the user ID exists, the user will have permissions to certain aspects of the system, depending on his/her position.

22. Loretta’s University Cleaners Employee View What would you like to do? VIEW TIMECARD CLOCK IN CLOCK OUT VIEW ORDER If the user is an employee who does not handle transactions, he/she will only have the above options. For security assurance, he/she will not be able to access what other users may.

23. Loretta’s University Cleaners Employee View What would you like to do? CASH OUT DRY CLEAN PRESS ONLY LAUNDRY ALTERATIONS VIEW TIMECARD HOUSEHOLD SHOE REPAIR CLOCK IN HOUSEHOLD PRESS ONLY CLOCK OUT CHANGE ORDER VIEW ORDER If the user is an employee who handles transactions, he/she will have the above options. For security assurance, he/she will not possess the same privileges as the manager user(s). VIEW INVOICE

24. Loretta’s University Cleaners Manager View What would you like to do? QUOTE CASH OUT DRY CLEAN PRESS ONLY LAUNDRY ALTERATIONS VIEW TIMECARD HOUSEHOLD SHOE REPAIR CLOCK IN HOUSEHOLD PRESS ONLY CLOCK OUT CHANGE ORDER VIEW ORDER SEARCH VIEW INVENTORY If the user is an employee who is a manager, he/she will have the above options.

25. Loretta’s University Cleaners Search View Please enter name or invoice number: Doe ENTER Invoice # Name Pick Up Date Operation 56255 Doe, John Mar 12, 2011 Laundry 41203 Doe, Jane Apr 20, 2009 Press Only 17834 Doel, Sam June 23, 2004 Shoe Repair This is a sample of the capabilities of the search function for the manager or employee with similar privilege. They may search for a previous invoice for whatever the need may be, and he/she will receive near real-time results.