1 / 29

INTRODUCTION

International Student College Experience Enhancement Program. Team Members. Alice Zhang Florence Liao Huan Guo Jake Magner Li Shubin Viraj Mohan Zahin Ali. INTRODUCTION. DP SUMMARIES . QUERIES. FORMS. NORMALIZATION. Project Background. Project Objective.

gerald
Download Presentation

INTRODUCTION

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. International Student College Experience Enhancement Program Team Members Alice ZhangFlorence Liao HuanGuo Jake Magner Li Shubin Viraj Mohan Zahin Ali INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  2. Project Background Project Objective To design a database for a website that helps international students with various aspects of “settling in”, by providing a platform for interaction between students, local communities, cultural organizations and employers Client XiYiRen, a start up social utility website will be using a small part of our expansive project, focusing on Chinese students. INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  3. DP I Summary Progress • Project Background: Objective and Client description • Summary of entities involved • Database capabilities • Simplified EER diagram with 10 entities, 3 Weak entities/relationships, and superclass/subclass division INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  4. DP II Summary Progress • Revised simplified EER diagram • Including more entities and 30 relationships • Implementation of queries in relational algebra • Realized need for more complex queries utilizing IEOR methods: forecasting, optimal event locating, etc. INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  5. DP III Summary Progress • Revised simplified EER diagram • Relational schema • Five queries implemented in SQL and Access • Focused on client-centric queries INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  6. EER INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  7. Relational Schema • Person(Pid, Fname, Lname, MI, Birth_date, Profile5) • Student(Pid1, Housing7, University14, Pickup_Person3, Flight, Country11, price_preference, year, sleep, wakeup, study, friends, outgoing) • Community_Member(Pid1, occupation) • Alumni(Pid1, Class, Occupation, Donation_Amount) • Profile(Profile_id, Pic, Email, Phone) • Location(Street, City, State, Apt_Suite, Zip, x, y) • Housing(Hid, offered_by_person1, Street6, Apt_Suite6, Zip 6, offered_by_org8, org_profile5, price, availability_date, furnished, number_rooms, number_bathrooms, water, electice, garbage, gas, internet, move-in special) • Organization(OrgName, Profile_id5, Street6, Apt_Suite6, Zip 6, type, description) • Department(DepName, University14) • Event(EventName, Profile_id5, Street6, Apt_Suite6, Zip 6, description, attendance, date, time) • Country(Name, Capital, Population) • Language(Name, Countries_spoken_in) • Resource(Rid, Owner1, Price, Quantity) • University(Name, student_population, ranking) • Donation(Did, Amount, Time, Date, Pid1) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  8. Relational Schema (contd) • Mentors(Mentor1, Mentee2) • Student_University(Student2, University14) • Person_in_Org(Person1, OrgName8, OrgProfile5) • RSVP(Person1, EventName10, EventProfile5, SurveyScore) • Student_in_Department(Student2, DepName9, UniName14) • Person_speaks_language(Person1, Language12) • Housing_near_Uni(Housing7, UniName14) • Organization_University(OrgName8, OrgProfile5, UniName14) • Org_holds_event(OrgName8, OrgProfile5, EventName10, EventProfile5) • Org_speaks_Language(OrgName8, OrgProfile5, Language12) • Org_Country(OrgName8, OrgProfile5, Country11) • Dep_sponsors_event(DepName9, UniName14, EventName10, EventProfile5) • Event_speaks_language(EventName10, EventProfile5, Language12) • Event_country(EventName10, EventProfile5, Country11) • Country_Language(Country11, Language12) • Alumni_Uni(Pid4, UniName14, class_of) • Alumni_Dept(Pid4, DepName9) • Person_gives_donation(Pid1, Did15) • Rommates(Pid11, Pid21) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  9. Relational Design INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  10. Query 1: Roommate Matching Description • Shows all possible roommate combinations ordered by MatchRating. • A dorm/off-campus housing facility can use it to pair up students interested in their housing INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  11. Query 1: Roommate Matching SQL Code SELECT P.Fname, P.Lname, Q.Fname, Q.Lname, Min(0.2*(Abs(S.sleep- R.sleep))+0.2*(Abs(S.wakeup-R.wakeup))+0.2*(Abs(S.outgoing- R.outgoing))+0.2*(Abs(S.study-R.study))+0.2*(Abs(S.friends- R.friends))) AS Matchrating FROM Student AS S, Student AS R, Person AS P, Person AS Q WHERE (((S.pid)=[P].[pid]) AND ((Q.pid)=[R].[pid] And (Q.pid)<[P].[pid])) GROUP BY P.Fname, P.Lname, Q.Fname, Q.Lname HAVING (((([P].[Fname]=[Q].[Fname]) And ([P].[Lname]=[Q].[Lname]))=False)) ORDER BY Min(0.2*(20-Abs(S.sleep-R.sleep))+0.2*(20-Abs(S.wakeup- R.wakeup))+0.2*(20-Abs(S.outgoing-R.outgoing))+0.2*(20- Abs(S.study-R.study))+0.2*(20-Abs(S.friends-R.friends))); INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  12. Query 1: Roommate Matching INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  13. Query 2: New Student Forecasting Description • Extracts the data of how many new students come each year which can then be used to forecast the future number of students • The year table is a one attribute table containing a list of years • Uses regression equation y=ax+b with slope b = (N∑XY - (∑X)(∑Y))/(N∑X2 - (∑X)2), and intercept a = (∑Y - b(∑X))/N. Where N = number of tuples, X =year, and Y = number of students SQL Code SELECT y.year AS [Year], count(s.pid) AS Number_Of_Students, u.name AS University FROM [year] AS y, student AS s, university AS u WHERE s.year=y.year AND s.university=u.name GROUP BY y.year, u.name ORDER BY y.year; INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  14. Query 3: Event Interest SQL Code Description SELECT e.EventName, e.Attendance/(Count(r.person)) AS Attendance_Rate, Avg(r.SurveyScore) AS Surveyed_Interest, Avg(r.SurveyScore)*e.Attendance/(Count(r.person)) AS Interest_Metric FROM Event AS e, RSVP AS r WHERE (((r.EventProfile)=[e].[Profile_id])) GROUP BY e.EventName, e.Profile_id, e.Attendance ORDER BY Avg(r.SurveyScore)*e.Attendance/(Count(r.person)) DESC; • Outputs a list of all events along with their computed attendance rate, the average level of student interest, and a metric combining surveyed interest with actual attendance • Organizations throwing events with low attendance but high survey scores may need to look into changing venues or increasing advertising. INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  15. Query 3: Event Interest INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  16. Query 4: Optimal Event Location Description • Selects optimal potential event location on UC Berkeley campus in relation to attendee housing locations. • By utilizing P-Median approach for event location that minimizes total demand weighted distances • Assume P = 1 and calculate Dij by utilizing Euclidean distance formula: INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  17. Query 4: Optimal Event Location SQL Code SELECT e.EventName, l2.street AS Potential_Location, sum(((l.x-l2.x)^2)+((l.y- l2.y)^2)^0.5) AS distance, AVG(s.EventInterest) AS Demand FROM Student AS s, RSVP AS p, Housing AS h, location AS l, location AS l2, Event AS e WHERE s.PID=p.person And p.EventName=e.EventName And s.housing=h.hid And h.street=l.street And h.state=l.state And h.city=l.city And h.apt_suite=l.apt_suite And h.zip=l.zip GROUP BY e.EventName, l2.street ORDER BY e.EventName, sum(((l.x-l2.x)^2)+((l.y-l2.y)^2)^0.5); INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  18. Query 4: Optimal Event Location INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  19. Query 5: Min Airport Pick-up Cost Description • Assumptions: • (1)Only take students who arrive at the airport between 8am to 7:59 pm into account • (2)Buses leave the airport on the hour. • (3)The opportunity cost of each student waiting for a bus for an hour is $10. (4) Each type I bus has a total of 5 seats and each type II bus has a total of 10 seats. • (5) We only deal with the arrival hour of each student, (student arriving at 1:01pm is treated the same as a student arriving at 1:59pm in this query implementation. and a ten-seat-vehicle to the airport and back cost $50 and $100, respectively. • For date, airport extract # of students arriving in each time interval Ci • A≤i≤L; Ci is interpreted as the number of students arriving at the airport no earlier than (i-1) o’clock but prior to i o’clock INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  20. Query 5: Min Airport Pick-up Cost Decision variables: tij= 1 if a type j bus is arranged to pick up students at i o’clock. tij = 0 otherwise; (For A≤i≤L, 1≤j≤2) Objective Function (Cost Min.): Subject to. People_constrain {Z in A,B,C,D,E,F,G,H,I,J,K,L}: SQL Code Formulation SELECT s.airport AS Airport, s.arr_date AS Arr_Date, s.flight_arr_hour AS Arr_Time, COUNT(*) AS Number_of_Students FROM student AS s GROUP BY s.flight_arr_hour, s.arr_date, s.airport; INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  21. Query 5: Min Airport Pick-up Cost INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  22. Normalization Analysis: 1NF 1NF R is in 1NF if the domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute. Profile (Profile_id, Pic, Emails, Phones)  Pic (Profile_id, Pic) Email (Profile_id, Email) Phone (Profile_id, Phone) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  23. Normalization Analysis: 2NF 2NF R is in 2NF if R is in 1NF and every nonprime attribute A in R is fully functionally dependent on the primary key of R. Location (Street, City, State, Apt_Suite, Zip, x, y) Assumption: ZIP_CODE determines CITY and STATE.  Location1 (Street, Apt_Suite, Zip, x, y) Zip (Zip, City, State) Organization (OrgName, Profile_id5, Street6, Apt_Suite6, Zip6, type, description) Assumption: The name of an organization determines its type.  OrgName (OrgName, Type) Organization1 (OrgName, Profile_id5, Street6, Apt_Suite6, Zip6, description) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  24. Normalization Analysis: 3NF 3NF R is in 3NF if R is in 2NF and no nonprime attribute of R is transitively dependent on the primary key. Housing (Hid, offered_by_person1, Street6, Apt_Suite6, Zip 6, offered_by_org8, org_profile5, price, availability_date, furnished, number_rooms, number_bathrooms, water, electricity, garbage, gas, internet, move_in_special, ready_to_move_in) Assumption: For a housing place to be “ready to move in”, it has to have Internet, water, electricity, gas and garbage.  Housing1 (Hid, offered_by_person1, Street6, Apt_Suite6, Zip 6, offered_by_org8, org_profile5, price, availability_date, furnished, number_rooms, number_bathrooms, move_in_special, Water, Electricity, Garbage, Gas, Internet) Ready_to_move_in (ready_to_move_in, Water, Electricity, Garbage, Gas, Internet) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  25. Normalization Analysis: BCNF BCNF R is in BCNF if whenever a nontrivial functional dependency XA holds in R, then X is a superkey of R. Student (Pid1, Housing7, University14, Pickup_Person3, Flight, Country11, price_preference, year, sleep, wakeup, study, friends, outgoing) INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  26. Organization Form INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  27. Person Form

  28. Student Report INTRODUCTION DP SUMMARIES QUERIES FORMS NORMALIZATION

  29. Questions?

More Related