Transcript GloSolar
GloSolar Company Schema/ EER Database Queries Normalization 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. Schema/ EER Database Queries Normalization 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 Schema/ EER Database Queries Normalization Company Schema/ EER Database Queries Normalization Final Relational Schema Company Schema/ EER Database Queries Normalization Final Relational Schema Company Schema/ EER Database Queries Normalization Final Relational Schema Company Schema/ EER Database Queries Normalization Table Relationships Switchboard Company Schema/ EER Database Queries Normalization Forms Company Schema/ EER Database Queries Normalization Query 1 Give a list of sales representatives and their number of paying customers, from highest to lowest. Company Schema/ EER Database Queries Normalization Query 2 Which advertisement generates the most revenue per dollar spent on that advertisement? Company Schema/ EER Database Queries Normalization Query 3 In what months are the most projects started? Order months from those with the most projects to those with the fewest? Company Schema/ EER Database Queries Normalization Query 4 Which customers have given the most referrals? Company Schema/ EER Database Queries Normalization Query 4 Provides a clean report for list of referrals Company Schema/ EER Database Queries Normalization Query 5 Which projects that have not yet been completed have been in progress the longest? Company Schema/ EER Database Queries Normalization 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 Company Schema/ EER Database Queries Normalization 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) Company Schema/ EER Database Queries Normalization 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 Company Schema/ EER Database Queries Normalization 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 Company Schema/ EER Database Queries Normalization 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) Company Schema/ EER Database Queries Normalization Questions? Thank you! Company Schema/ EER Database Queries Normalization