Database Alternatives

The business owner Mrs. Benedict was struggling with managing designer work and their work hours to the corresponding task. The lack of management results in the difficulty of keeping track with the project process and the hurdle of calculating the allocation of payroll cost based on each project. Furthermore, Mrs. Benedict currently has to calculate the payroll of the designers manually, timing up the hourly wage to every single time slot on the timesheet from different designers individually, and then sum up to import corresponding information to the accounting system for further payments.

Client

Design Alternatives is a local interior design business located in Lafayette, IN. The company is owned by Susan Benedict, a certified kitchen designer whose expertise is unsurpassed in the area. Founded in 1994, Design Alternatives started out offering solutions to kitchen and bathroom designs that match people’s lifestyles but now extends its business to designing more areas in the house.

Programming language

SQL

Authors

• Li-Ci Chuang
• Mu-Hua Hsu
• Xue Han
• Yu-Lin Tai

Dataset description

The company provides five datasets, some of which are paper documents, and some of which are excel files.

• 1. Task: Includes the task name, the related contacting customer information, and corresponding designers responsible for this task. However, this dataset is quite messy because the task name is irregular and it is combined by the customer name and some descriptions. Redundant issues are also common since several tasks might belong to one customer, but contact information is repeatedly recorded.
• 2. Designer: Includes the designer's wage, position, and private information.
• 3. Vendor: Contain all information of vendors that the company once cooperated with.
• 4. Checks: Includes the money checks that the company has signed, including checks signed for the designers and the vendors.

Data

• Checks
• Designer
• DesignerCheck
• Project
• ProjectCustomer
• ProjectDesginer
• ProjectTask
• Timesheet
• Vendor
• VendorCheck

Project component

• Business Requirements
• Conceptual Data Modelling (ER diagram)
• Relational Data Model
• SQL Query

Project presentation

https://www.youtube.com/watch?v=u8kzqiJws5g&feature=youtu.be