1 / 27

Client Background

Client Background. The Lawrence Hall of Science. The Lawrence Hall of Science (LHS) is UC Berkeley’s public science center to provide the general public with hands-on exhibits Mission to inspire and foster learning of science and math

will
Download Presentation

Client Background

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. Client Background The Lawrence Hall of Science • The Lawrence Hall of Science (LHS) is UC Berkeley’s public science center to provide the general public with hands-on exhibits • Mission to inspire and foster learning of science and math • Composed of general exhibits, special programs, public programs, and workshop classes

  2. Objectives / Implementations LHS objectives • Organize data more efficiently • Implement data quickly and easily • Pull data from previous records to track attendance and profits for specific events Our objectives • Organize tables and relationships • Create Access forms and reports • Determine optimal queries to increase museum sales and visitor attendance, improve operation efficiency, and reduce museum costs

  3. EER Diagram

  4. Relational Design Relational Design 1. Admission(VID8, Public_Program16, Admission_Type2, Public_Program_Supplement)2.       Admission_Details(Type, Price)3.       Category(CID, Category_Name, Description)4.       Hired_Workshop(GID8a, WID21, Day_Contracted9, Month_Contracted11, Year_Contracted) 5.       Employee(SSN, First_Name, Last_Name, Address, Phone_Number, Start_Date, PTO_to_date) a.       Director(DSSN5, Department, Annual_Wage) b.      Facilitator(FSSN5, Days_Available20, Hourly_Wage) c.       Cashier(CSSN5,  Days_Available20) d.      Instructor(ISSN5, Age_Group, Class/Camp, Annual_Wage) 6.       Exhibit(EID, Category3, Exhibit_Name, Organizer, Location, Start_Date, End_Date, Description) 7.       Gift_Shop_Purchase(Product15, VID8, Price, Quantity, Date_Bought)8.       Visitor(VID, Date_Visited) a.       Group(GID8, Group_Name, Size, Interest) i.      Non-School_Group(GID8a) ii.      School_Group(GID8a) b.      Individual(IID, VID8, First_Name, Last_Name, DOB, Student) i.      Member(MID, IID8b, Expiration_Date9, Expiration_Month11, Expiration_Year, Start_Day9, Start_Month11, Start_Year, Sex, Address, Phone_Number, Payment_Type, Disabled_Person, Member_Type10) ii.      Non-Member(VID8, Payment_Type, Events, Guest_of_MID8bi)

  5. Relational Design Relational Design 9.       List_of_Days(Day)10.     Membership_Details(Type, Cost, Member_Cards_Allotted, Guest_Passes_Allotted, Planetarium_Passes_Allotted, Parking_Passes_Allotted, Gift_Offered, Facility_Rental_Discount, Special_Events_Invitation) 11.   Months_of_the_Year(Month #, Month_Name)12.   Order(SSN5, OID13, Day_Placed9, Month_Placed11, Year_Placed)13.   Order_Details(OID, Size, Amount, Payment_Method, Date_Received)14.   Order_History(OID13, Product15)15.   Product(PID, Product_Name, Age_Range, Category3, Description)16.   Public_Program(PPID, Public_Program_Name, Start_Date, End_Date, Exhibit6, Admission_Cost) 17.   Special_Program(SPID, Exhibit6, Special_Program_Name, Start_Date, End_Date)18.   SuppliedBy(Product15, SID19, Ship_Date, Unit_Cost, Quantity)19.   Supplier(SID, Supplier_Name, Address, Phone_Number, Supplier_Rep, E-mail)20.   Work_Days/Times(Day & Time)21.   Workshop(WID, Age_Group, Type, ISSN)

  6. Relationship View

  7. Query Design#1 Query 1 1.) What is the economic ordering quantity (EOQ) for a product type? What is the reorder point assuming a continuous review policy and a 95% service level? Implementation • Calculate the demand per week by summing the quantity of a particular product sold over a start and end date length divided by the period • Use the lead time and holding cost inputs from the user to calculate the EOQ. • Use the average aggregate function to calculate average demand and use the given holding cost and lead time inputs to calculate the reorder point

  8. Query Design#1 SQL SELECTDISTINCTp.pid, sqr((2*(sum(b.size)/datediff('ww',[Enter start date],[Enter end date])*[Enter Fixed Cost per order]))/[Enter holding cost]) AS ["Reorder Quantity"], [Enter Lead Time]*avg(b.size)/datediff('ww',[Enter start date],[Enter end date])+1.69*(stdev(b.size))*sqr([Enter Lead Time]) AS ["Reorder Point"]FROM [order details] AS b, product AS p, [order history] AS rWHEREb.oid=r.oidANDr.product=p.pidGROUP BYp.pid;

  9. Query Design#1 Significance • Improve the LHS inventory management policy • Determine the optimal ordering quantity based on past demand and inform them of at which inventory level they should place an order • Reduce costs in terms of holding and ordering costs while still maintaining a high service level.

  10. Query Design#2 Query 2 2.) Forecast the number of workshop attendees by quarter, normalized for seasonality. Implementation • Use Winter’s Method to incorporate seasonality • Initialize the data based on years 2009 and 2010 to forecast for 2011. • Split up a year into 4 quarters because monthly variation is not large enough to warrant further sectioning of the year • Find the seasonal factors • Generate the 2011 forecast for each quarter.

  11. Query Design#2 SQL (…) SELECT [Workshop Quarters].Quarter, Count([Workshop Quarters].GID) AS [NumGroups that Attended Workshop], [Workshop Quarters].[Year Contracted]FROM (SELECT [Hired Workshop].GID, [Hired Workshop].[Year Contracted], IIf([Hired Workshop]![Month Contracted]<=3,1,IIf([Hired Workshop]![Month Contracted]>3 AND [Hired Workshop]![Month Contracted]<=6,2,IIf([Hired Workshop]![Month Contracted]>6 AND [Hired Workshop]![Month Contracted]<=9,3,4))) AS Quarter FROM [Hired Workshop])  AS [Workshop Quarters]GROUP BY [Workshop Quarters].[Year Contracted], [Workshop Quarters].Quarter;

  12. Query Design#2 Significance • Forecasts the workshop attendance for the next year, divided into 4 quarters, by incorporating seasonality. • Better allocate resources depending on demand forecasts.

  13. Query Design#3 Query 3 3.) Calculate the return on investment for memberships per year. Implementation • Determine number of sales of a membership type in a year and multiply it by its unit price • Subtract the sum of the product of the total number of guests brought and the unit price of admission and the product of the number of times a discount is used and unit price of admission • Divide this difference by the sum Query ROI Formula MembershipRev–(LossFromMembershipDiscount+LossFromGuestDiscount) (LossFromMembershipDiscount+LossFromGuestDiscount)

  14. Query Design#3 SQL (…) SELECT (([Membership Revenue]![Membership Revenue]-([Loss from Members Using Membership]![Amount of Loss]+[Loss from Members Bringing Guests]![Amount of Loss]))/([Loss from Members Using Membership]![Amount of Loss]+[Loss from Members Bringing Guests]![Amount of Loss])) AS ROI, [Loss from Members Using Membership].Year AS [Year] FROM ([Loss from Members Bringing Guests] INNER JOIN [Loss from Members Using Membership] ON [Loss from Members Bringing Guests].Year = [Loss from Members Using Membership].Year) INNER JOIN [Membership Revenue] ON ([Loss from Members Bringing Guests].Year = [Membership Revenue].[Start Year]) AND ([Loss from Members Using Membership].Year = [Membership Revenue].[Start Year]);

  15. Query Design#3 Significance • See which years provided the highest return on investment based on current price and usage • Adjust membership pricing levels and benefits to maximize revenue

  16. Query Design#4 Query 4 4.) Rank categories by an “interest factor” based on number of items bought, groups interested, and visitors during an exhibit for a specific category. Implementation • Rank by the number of visitors that visited while an exhibit of that category was on display. • Rank by the number of items bought of that category • Rank by the number of groups interested in that category Interest Factor Formula .4*NumberOfGroupsInterested+.4*NumberOfVisitors+.2*NumberOfItemsBought ***prioritize higher attendance (number of groups and visitors)

  17. Query Design#4 SQL (…) SELECTfa.cid, (.4*fa.NumOfVisitors+.4*q2.Group_Quantity+.2*q1.Buys_Quantity) AS Interest_FactorFROM [Category Rank by Visitors] AS fa, [Category Rank by Items] AS q1, [Category Rank by Groups] AS q2WHEREfa.cid=q1.cid and q1.cid=q2.cidORDER BY (.4*fa.NumOfVisitors+.4*q2.Group_Quantity+.2*q1.Buys_Quantity) DESC;

  18. Query Design#4 Significance • See which categories are most appealing to groups and visitors in terms of both attendance and gift purchases • Plan future exhibits in categories that will attract the most interest

  19. Query Design#5 Query 5 5.) Determine the earliest day when a given exhibit falls below the expected visitor attendance value determined by the client in a preselected date range. Implementation • Determine the number of visitors per day for a client selected exhibit. • Calculate a moving average for past 5 days. • Select the earliest date where the average number of visitors fall below a client selected value in a client selected date range. • Record the earliest date where an exhibit is not as visited as client inputted data.

  20. Query Design#5 SQL (…) SELECT dc.[DateVisited] as MADate, Avg(dc.NumOfVisitors) as MovingAverageINTOMovingAverageFROMDailyCount as dc, DailyCount as rtWHERE (dc.[DateVisited]>=dc.[StartDate]+5) AND dc.[DateVisited] >= rt.[DateVisited]-5 AND dc.[DateVisited] <=  rt.[DateVisited]GROUP BY dc.[DateVisited]ORDER BY dc.[DateVisited] SELECT Min(ma.[MADate]) AS EarliestQuitINTOEarliestQuitFROMMovingAverage AS maWHERE ma.[MovingAverage] < [Enter Minimum Visitor Count] AND (ma.[MADate] >= [Enter Search Start Date]) AND (ma.[MADate] <= [Enter Search End Date])

  21. Query Design#5 Significance • Determine the optimal number of days to display an exhibit based off attendance • Reduce costs and tailor their exhibits to attract the most number of individuals

  22. Normalization Analysis Normalization 1 Individual(IID, VID8, First_Name, Last_Name, DOB, Student) Functional Dependencies: IID  {VID, First_Name, Last_Name, DOB, Student} 1NF: VID is a multi-valued attribute Individual(IID, First_Name, Last_Name, DOB, Student Individual_Visits(IID, VID8) 2NF: There are no partial dependencies and no composite keys 3NF: No non-prime attributes of either relation are transitively dependent on the primary key BCNF: All attributes are functionally dependent on a super key Normalization 2 SuppliedBy(Product15, SID19, Ship_Date, Unit_Cost, Quantity) Functional Dependencies: {Product, SID} Unit_Cost {Product, SID, Ship_Date}  {Quantity, Unit_Cost} 1NF: No multiple values attributes and all attributes are atomic 2NF: Unit_Cost was only partially dependent on the primary key. SuppliedBy1(Product, SID, Unit_Cost) SuppliedBy2(Product, SID, Ship_Date, Quantity) 3NF: No non-prime attributes of either relation are transitively dependent on the primary key. BCNF: All attributes are functionally dependent on a super key

  23. Normalization Analysis Normalization 3 Admission(VID8, Admission_Type, Public_Program16, Public_Program_Supplement, Price) Functional Dependencies: VID  {Admission_Type, PublicProgram, Public_Program_Supplement, Price} Admission_Type Price PublicProgramPublic_Program_Supplement 1NF: No multiple valued attributes and all attributes are atomic. 2NF: No partial dependencies and no composite keys. 3NF: Public_Program_Supplement was transitively dependent on the primary key in Admission1 in 2NF. Price also transitively dependent on VID through Admission_Type. Admission1(VID8, Public_Program16) Admission2(Admission_Type, Price) Admission3(PublicProgram16, Public_Program_Supplement) BCNF: All attributes are functionally dependent on a super key

  24. Normalization Analysis Normalization 4 Supplier(SID, Supplier_Name, Address, Phone_Number, Supplier_Rep, E-mail) Functional Dependencies: SID  {Supplier_Name, Address, Phone_Number, Supplier_Rep, E-mail} Supplier_Rep E-mail 1NF: No multiple valued attributes and all attributes are atomic. 2NF: No partial dependencies or composite keys 3NF: No non-prime attributes are transitively dependent on the primary key BCNF: E-mail is not dependent on a super key (Supplier_Rep is not part of candidate key). Supplier(SID, Supplier_Name, Address, Phone_Number, Supplier_Rep) Rep(Supplier_Rep, E-mail) Normalization 5 Order_Details(OID, Size, Amount, Payment_Method, Date_Received) Functional Dependencies: OID  {Size, Amount, Payment_Method, Date_Received} Cannot be further normalized (already in BCNF). All attributes dependent on OID, the candidate key.

  25. Future Work Future Work • Create additional queries • Track the success of special events such as Speaker Series • Compare attendances of events between years. • Track ticket price changes’ effect on visitor attendance • Compare tickets prices and number of museum visitors • Drive implementation of current queries • Organize collected data and input into Access

  26. Q & A Questions?

More Related