Transcript The Lawrence Hall of Science (LHS)

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
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
EER Diagram
Relational Design
Relational Design
1.
2.
3.
4.
5.
6.
7.
8.
Admission(VID8, Public_Program16, Admission_Type2, Public_Program_Supplement)
Admission_Details(Type, Price)
Category(CID, Category_Name, Description)
Hired_Workshop(GID8a, WID21, Day_Contracted9, Month_Contracted11,
Year_Contracted)
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)
Exhibit(EID, Category3, Exhibit_Name, Organizer, Location, Start_Date, End_Date,
Description)
Gift_Shop_Purchase(Product15, VID8, Price, Quantity, Date_Bought)
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)
Relational Design
Relational Design
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
List_of_Days(Day)
Membership_Details(Type, Cost, Member_Cards_Allotted, Guest_Passes_Allotted,
Planetarium_Passes_Allotted, Parking_Passes_Allotted, Gift_Offered,
Facility_Rental_Discount, Special_Events_Invitation)
Months_of_the_Year(Month #, Month_Name)
Order(SSN5, OID13, Day_Placed9, Month_Placed11, Year_Placed)
Order_Details(OID, Size, Amount, Payment_Method, Date_Received)
Order_History(OID13, Product15)
Product(PID, Product_Name, Age_Range, Category3, Description)
Public_Program(PPID, Public_Program_Name, Start_Date, End_Date, Exhibit6,
Admission_Cost)
Special_Program(SPID, Exhibit6, Special_Program_Name, Start_Date, End_Date)
SuppliedBy(Product15, SID19, Ship_Date, Unit_Cost, Quantity)
Supplier(SID, Supplier_Name, Address, Phone_Number, Supplier_Rep, E-mail)
Work_Days/Times(Day & Time)
Workshop(WID, Age_Group, Type, ISSN)
Relationship View
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
Query Design#1
SQL
SELECT DISTINCT p.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
r
WHERE b.oid=r.oid AND r.product=p.pid
GROUP BY p.pid;
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.
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.
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;
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.
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)
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]);
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
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
.4*NumberOfGroupsInterested+.4*NumberOfVisitors+.2*NumberOfItemsBought
Formula
***prioritize higher attendance (number of groups and visitors)
Query Design#4
SQL
(…)
SELECT fa.cid,
(.4*fa.NumOfVisitors+.4*q2.Group_Quantity+.2*q1.Buys_Q
uantity) AS Interest_Factor
FROM [Category Rank by Visitors] AS fa, [Category Rank
by Items] AS q1, [Category Rank by Groups] AS q2
WHERE fa.cid=q1.cid and q1.cid=q2.cid
ORDER BY
(.4*fa.NumOfVisitors+.4*q2.Group_Quantity+.2*q1.Buys_Q
uantity) DESC;
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
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.
Query Design#5
SQL
(…)
SELECT dc.[DateVisited] as MADate,
Avg(dc.NumOfVisitors) as MovingAverage INTO
MovingAverage
FROM DailyCount as dc, DailyCount as rt
WHERE (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 EarliestQuit INTO
EarliestQuit
FROM MovingAverage AS ma
WHERE ma.[MovingAverage] < [Enter Minimum Visitor
Count] AND (ma.[MADate] >= [Enter Search Start Date])
AND (ma.[MADate] <= [Enter Search End Date])
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
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
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
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.
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
Q&A
Questions?