1 / 22

GloSolar

GloSolar. IEOR 115 Database Design Project Review 1 Group 6. Company Overview. GloSolar is a small Startup Solar Installer Headquarters: Downtown Berkeley Market: Business done primarily in Bay Area/Sonoma County Company Size: Eight employees total; three work in the Berkeley Office.

louise
Download Presentation

GloSolar

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. GloSolar IEOR 115 Database Design Project Review 1 Group 6

  2. Company Overview • GloSolaris a small Startup Solar Installer • Headquarters: Downtown Berkeley • Market: Business done primarily in Bay Area/Sonoma County • Company Size: Eight employees total; three work in the Berkeley Office.

  3. Existing Database Structure • First 3 Months: Began with customer tracking by storing its customer data on a single sheet of a large Google Spreadsheet. • Current customer resource management database: • Google Apps & Relational Database based on Excel for orders • Need for Project Management as well as data tracking • No existing relational database structure to track the physical Solar Systems and the different customers from start to finish state, as well as each and every single order. • Company Difficulties: • Operations are spread out throughout California • Database must be able to both track projects as well as entire company operations including payroll and purchases

  4. Final Relational Schema

  5. Final Relational Schema

  6. Final Relational Schema

  7. Table Relationships

  8. Switchboard

  9. Forms

  10. Query 1 • Give a list of sales representatives and their number of paying customers, from highest to lowest.

  11. Query 2 • Which advertisement generates the most revenue per dollar spent on that advertisement?

  12. Query 3 • In what months are the most projects started?  Order months from those with the most projects to those with the fewest?

  13. Query 4 • Which customers have given the most referrals?

  14. Query 4 • Provides a clean report for list of referrals

  15. Query 5 • Which projects that have not yet been completed have been in progress the longest?

  16. Normalization Analysis • Person BCNF Person(Person_id, Last_Name, First_Name, MI, Address, Phone_Number, Email_Address) Functional Dependencies: Person_idFirst_Name, Last_Name, MI, Address, Phone_Number, Email_Address

  17. Normalization Analysis • CreditCard 2NF CreditCard(Payment_id, CreditCardNumber, CreditcardCompany, CCV) Functional Dependencies: Payment_idCreditCardNumber, CreditCardCompany, CCV CreditCardNumberCreditCardCompany, CCV • Normalized into BCNF: CreditCard(Payment_id, CreditCardNumber) CreditCard(CreditCardNumber, CreditCardCompany, CCV)

  18. Normalization Analysis • Project BCNF Project(Project_id, Cutsomter_id1b, Completion_Date, Start_Date, Total_Price) Functional Dependencies: Project_idCustomer_id, Total_Price, Start_Date, Completion_Date

  19. Normalization Analysis • Order BCNF Order(Order_id, Employee_id1a, Project_id4, Order_time, Payment_id22, Shipper_id18,Product_id19) Functional Dependencies: Order_idEmployee_id, Project_id, Order_time, Amount, Payment_Method_id, Shipper_id. Product_id

  20. Normalization Analysis • Supplier 3NF Supplier(Supplier_id, Name, Address, Email_Address, Phone) Functional Dependencies: Supplier_id Name, Address, Email_Address, Phone Phone Supplier_id • Can be normalized into BCNF: Supplier(Supllier_id, Name, Address, Email_Address) Supplier_Phone(Supplier_id, Phone)

  21. Questions? Thank you!

More Related