Download
1 / 29
290 likes | 422 Vues
UC BERKELEY IEEE STUDENT CHAPTER DATABASE. Team Members: Jesus Castellanos, Agustin Del Rio, Marco Innao, Crystel Roberts, Jonatan Rodriguez, Pranthik Samal, Bryan Vallieres, Yang Yang. WHAT IS IEEE? .
E N D
UC BERKELEY IEEE STUDENT CHAPTER DATABASE Team Members: Jesus Castellanos, Agustin Del Rio, Marco Innao, Crystel Roberts, Jonatan Rodriguez, Pranthik Samal, Bryan Vallieres, Yang Yang
WHAT IS IEEE? • As an active organization on campus here, at the University of California, Berkeley, IEEE provides lab services, promote student professional awareness, participate in high school outreach programs, play a role as a backbone for student-industry networking by hosting informational sessions and other things of that sort, and plan student social activities to maintain balance with the technical atmosphere.
DP I Summary • We created: • Overall Project Summary • Textual summary of database requirements • Simplified EER diagram from information from client IEEE with more than 20 entities including superclass/subclass divisions and a weak entity as well as more than 20 relationships
DP II Summary • Revised simplified EER diagram • Relational Schema from EER diagram • 5 interesting queries described and why they are interesting to our client • 1: Member participation tracker • 2: Forecast demand based on moving average • 3: Forecast the attendance of a specific type of event • 4: Member of the semester award • 5: Best event
DP III Summary • Executive summary including: • Client Description • Revised simplified EER diagram • Revised relational design schema • Created and implemented tables in Microsoft Access • Created relationships between the tables • Screencapture of tables of relational design • 5 interesting queries written in: • English • Relational algebra and/or SQL
Normalization Analysis • 1NF Member_Course: {{MID, CCN}⟶ {Course_Name, Past_Or_Current}, CCN ⟶ Course_Name } Assumptions: a. CCN can identify a certain course. b. Different courses with different CCNs can share the same Course_Name.
Improvement Separate Course_Name into a new relation, together with CCN: • Member_Course (MID, CCN, Past_Or_Current) • Course_Name (CCN, Course_Name)
2NF Person: {PID ⟶ {Fname, Lname, Mname, Cell_Phone_Number, Home_Phone_Number, Street_Address, City, State, Zip}, Home_Phone_Number ⟶ State} Assumption: 1. zip code 2. area code 3. share phone
Possible Improvement: separate State into a new relation, together with Home_Phone_Number • Person (PID, Fname, Mname, Cell_Phone_Number, Home_Phone_Number, Street_Address, City, Zip) • Home_Phone_State (Home_Phone_Number, State) But is this a good choice?
3NF (also in BCNF) • Event: EID ⟶ {EName, EDate, Location, Time, General_Member_Turnout, Head_Count, Cost, Meal_Ordered_by_MID, Chairs_by_CName, Advertisement_Mode, Type} • Lab: LABID ⟶ {Number_Of_Computers, Number_Of_Servers, Number_Of_Chairs, Number_Of_Tables, Size}
QUERIES QUERY 1: officer participation for the purposes of promotion • This is a query that tracks officer participation for the purposes of promotion on the officer board. Officers who have chaired at least one committee and have attended at least two events are assigned a weighted score with 2 points given for being on a committee, 3 points given for chairing a committee, 1 point for mentoring another officer, and ½ point for attending an event.
QUERY 2: three-month moving average for inventory. • This query is a three-month moving average for inventory. Taking into account the inventory used by member and for events, we are able to keep estimate the how much inventory is needed for each month. For example, this query forecasts in the inventory for paper over a three-month period. select (1/3)*(sum(m.Quantity)+sum(e.Quantity)) AS moving_avg from Member_Uses_Inv m, Event_Uses_Inv e, Inventory i where i.Name='paper' and e.INVID=m.INVID and i.INVID=e.INVID and e.Date<='2008-10-31' and E.date>='2008-08-01' and m.Date<='2008-10-31' and m.Date>='2008-08-01';
QUERY 3: forecast the attendance for a certain type of event. • Suppose the event type of our interest is info sessions; this query will pull out all attendance information for info sessions and export the data to excel. Excel then uses exponential smoothing based on the data to calculate the forecast. That information is then imported back into MS Access.
QUERY 4: all the members who have attended all the events except for officer meeting in a given semester • The purpose of a query like this would be to award a “Member of the Semester” on the basis of attendance
QUERY 5:model a “best” event based on cost, attendance, and the people in attendance. • This query attempts identifies and ranks the most successful event by modeling a “best” event based on cost, attendance, and the percentage of high GPA students in attendance. This query can be useful because if we know which events are the best, we can try to find something in common between them. We can use this commonality to improve all other events. This also helps with the planning for future quality events considering the available funds.
