1 / 25

Engineers’ Joint Council

Database design for. Group #2 Elliot Chow Quijano Flores Jim Huang David Keegan Sophia Law Elysia Messah Esha Ranganath. Engineers’ Joint Council . Client Description. A student organization who represents and supports the various engineering societies in the College of Engineering.

makala
Download Presentation

Engineers’ Joint Council

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. Database design for Group #2 Elliot Chow Quijano Flores Jim Huang David Keegan Sophia Law Elysia Messah Esha Ranganath Engineers’ Joint Council

  2. Client Description • A student organization who represents and supports the various engineering societies in the College of Engineering. • Holds various events ranging from career fairs and carnivals, to barbeques and community service events. • Collects budget reports from almost 30 engineering groups to request funding form the ASUC.

  3. Current System • The client is using mySQL for database purposes. • Tables are highly unorganized and most of the entries are blank. • EJC’s server has Microsoft Access and is fully capable of running a database. • EJC has most of its information stored on a club officer’s computer, and upon graduation, the officer must manually transfer the club data to a new incoming officer.

  4. Expected Results • Design a structured database model, that, if implemented, all data can be stored on a permanent server that is easily accessible to future officers. • A database management system will be a good solution to EJC’s difficulties in storing, editing, and updating data. • We believe that the database model will be valuable to the development of the council and its success in the future.

  5. Estimated Data Requirements • 4 types of members: 1. Alumni 2. Officers 3. Potential 4. Others • 8 different committees. 1. Financial committee 2. Corporate committee 3. Public relations committee 4. Marketing committee 5. IT/Webmaster committee 6. Facilities committee 7. Community outreach committee 8. Alumni relations committee • 26 different Engineering Societies.

  6. Relational Schema • Member (SID, Fname, Lname, MI, Bdate, Year, Major, Phone_Number, Email, Big_Buddy_SID1) 1a. Alumni_Member (SID1, Grad_Year) 1b. Officer_Member (SID1, Title) 1c. Potential_Member (SID1) 1d. Other_Member (SID1) • Engineering_Society(EName) • Department(EName2) • External_Contact (ContactID, Contact_Name, Company_Name, Company_Address, Company_Phone_Number, Contact_Address, Contact_Phone_Number, Contact_Email) • Transaction(TID, SID1, Amount, Date, Regulated_By_CName6) 5a. Withdrawal(TID5, EName2) 5b. Deposit(TID5, ContactID4)

  7. Relational Schema continued • Council_Committee(CCName, Description, Officer_SID1b) • Event(EventID, Date, Time, Duration, Event_Name, Event_Description, Number_Of_Attendance, LID9) 7a. Fundraising(EventID7) 7b. Educational(EventID7) 7c. Social(EventID7) 7d. Meeting(EventID7, Minutes) • Advertisement(AdID, Media_Type, Date_Distributed, Quantity) • Location(LID, Location_Name, Room_Num, Capacity) • Ad_Funds(AdID8, With_TID5a)

  8. Relational Schema continued • Event_Funds(EventID7, With_TID5a) • CC_Meeting(CCName6, Meeting_EID7d) • Contacts(CCName6, ContactID4) • Reserves(LID9,CCName6) • CC_Organizes(CCName6, EventID7) • ES_Organizes(EName2, EventID7) • Recruits (Potential_SID1c, EventID7) • Ad_For (AdID8, EventID7) • CC_Creates (AdID8, CCName6) • Member_Attends (SID1, EventID7) • EC_Attends (ContactID4, EventID7) • Represents (SID1, EName2) • Is_Part_Of (SID1, CCName6) • SubmitsBR (EName2, CCName6) • Coordinates (Meeting_EID7d, EventID7)

  9. Normalization 1st Normal Form (1NF) • R is in 1NF if all attribute domains include only values that are atomic (indivisible) and single-valued. • Example: External_Contact (ContactID, Contact_Name, Company_Name, Company_Address, Company_Phone_Number, Contact_Address, Contact_Phone_Number, Contact_Email) → 1NF: R1 (ContactID, Contact_Name, Company_name, Company_Address, Company_Phone_Number, Conctact_Address, Contact_Phone_Number) R2: (ContactID, Contact_Email)

  10. Normalization 2nd Normal Form (2NF) • R is in 2NF if it is in 1NF, and every non-prime attribute is fully functionaly dependent on the Primary Key • Example 1: Transaction (TID, SID1, Amount, Date, Regulated_By_CName6) → 1NF: R1 (TID, SID1, Amount, Date, Regulated_By_CCName6) → 2NF: R1 (TID, Amount, Date) R2 (TID, SID, Regulated_By_CCName6)

  11. Normalization 3rd Normal Form (3NF) • R is in 3NF if R is in 2NF and non-prime attributes of R are transitively dependent on the primary key • Example : External_Contact (ContactID, Contact_Name, Company_Name, Company_Address, Company_Phone_Number, Contact_Address, Contact_Phone_Number, Contact_Email) → 2NF: R1 (ContactID, Contact_Name, Company_name, Company_Address, Company_Phone_Number, Conctact_Address, Contact_Phone_Number) R2 (ContactID, Contact_Email) → 3NF: R3 (ContactID, Contact_Name, Contact_Address, Contact_Phone_Number, Company_Name) R4 (Company_Name, Company_Address, Company_Phone_Number) R5 (ContactID, Contact_Email)

  12. Relations View

  13. Queries Query 1: External Contacts in order of priority to contact.Sum(all transactions) – (events went to by ext. contact)(avg demand of every ext. contact) This lists the contact status with regards to an external contact for a donation from the results of an equation. If the result of this equation is a positive number, the external contact is donating more than expected and vice versa. Therefore, EJC can benefit from this query because they can budget their time wisely and only contact external contacts that should be donating more money. Select EC.Contact_name, sum(D.Amount) – (Count(A.EventID) * Average(D.Amount)) From Deposit D, External_Contact EC, EC_Attends A GroupBy EC.Contact_name;

  14. Queries continued Query 2: List of active members. 1/2 ((events_went_to)+(events_planned)(avg_amount_of_events_per_society) /(total_events)+ 1/2 (external contact made/(external contacts assigned +1)) One of the things that EJC wants to keep track of is the level of involvement from its members relative to other members. The equation for this query is based on the events that the member attends/plans. This query is beneficial to EJC because it helps determine who the most active members are and focus on encouraging the less active members to play a greater role. Select m.SID, ((count(ma.*)+ count(DISTINCT C.EventID)* avg(Select es1.Ename, count(*) From ES_Organizes es1 Group By es1.Ename)) / (count( f.EventID) + count(e.EventID) + count(s.EventID)) From Member m, Member_attends ma, Coordinates C, ES_Organizes es, Fundraising f, Educational e, Social s Where ma.SID = m.SID AND C.Meeting_EID = ma.EventID;

  15. Queries continued Query 3: If a society is active. ½((#_events_held_by_society)/(total_events_held_per_society))+1/2((#_of_transactions)/(total_transactions)) One of the things that EJC wants to keep track of is the level of involvement the societies relative to other societies. The scale is from 0 to 1, 0 being low activity and 1 being high activity. This query is beneficial to EJC because it helps determine who the most active societies are. Select es.Ename, 0.5*(count(es.EventID) / (es.Number_Of_Members*count( f.EventID) + count(e.EventID) + count(s.EventID))) + (0.5*count(w.TID) / (Select count (*) From withdrawal w1)) From ES_Organizes es, Fundraising f, Educational e, Social s, Engineering_Society es, Withdrawl w Group By es.Ename;

  16. Queries continued Query 4: If a group should spend money on advertising. money_spent_on_advertising/attendance_of_an_event This query evaluates the effectiveness of advertising for a specific event. The fraction represents how much money is being spent per person for an event. The smaller this number is the more effective a particular advertising campaign is. The larger this number the less effective. This will benefit EJC by reducing money spent on ineffective advertisements. Select e.EventID, sum(w.Amount)/ count(ma.SID) From Ad_Funds af, Event e, Withdrawal w, Ad_For adf, Member_Attends ma Where e.EventID = adf.EventID AND adf.AdID = af. AdID AND af.With_TID = w.TID AND ma.EventID = e.EventID;

  17. Queries continued Query 5: Correlation coefficient between alumni and donation. Cov(#_alumni_external_contact,avg_donation_of_external_contact)/sd(#_alumni_external_contact)sd(avg_donation_of_external_contact) This query determines if there is a correlation between the number of alumni at and external contact and the amount that the external contact donates. If there is a positive correlation then the more alumni at an external contact would mean that we would expect more money from donations.

  18. Queries continued Select Avg( Select count(am1.*) * Sum(d.Amount) From Alumni_Member am1, Deposit d Where am1.ContactID = d.ContactID Group By am1.ContactID) – avg(d.Amount)*avg(Select count(am1.*) ) From Alumni_Member am1, Deposit d Where am1.ContactID = d.ContactID Group By am1.ContactID) ) /(STD(Select count(am1.*)) From Alumni_Member am1, Deposit d Where am1.ContactID = d.ContactID Group By am1.ContactID)) * STD(d1.Amount) From Deposit d1;

  19. Main Switchboard

  20. Screenshot – Query 1

  21. Screenshot – Query 2

  22. Forms – Add Member

  23. Forms – Print Transactions

  24. A Few Groups under EJC

More Related