engineers’ joint council
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/1.jpg)
Group #2Elliot Chow
Quijano FloresJim Huang
David KeeganSophia Law
Elysia MessahEsha Ranganath
![Page 2: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/2.jpg)
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.
![Page 3: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/3.jpg)
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.
![Page 4: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/4.jpg)
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.
![Page 5: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/5.jpg)
Estimated Data Requirements 4 types of members:
1. Alumni2. Officers3. Potential
4. Others 8 different committees.
1. Financial committee2. Corporate committee 3. Public relations committee4. Marketing committee5. IT/Webmaster committee6. Facilities committee7. Community outreach committee8. Alumni relations committee
26 different Engineering Societies.
![Page 6: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/6.jpg)
![Page 7: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/7.jpg)
Relational Schema1. 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)
2. Engineering_Society(EName)3. Department(EName2)4. External_Contact (ContactID, Contact_Name,
Company_Name, Company_Address, Company_Phone_Number, Contact_Address, Contact_Phone_Number, Contact_Email)
5. Transaction(TID, SID1, Amount, Date, Regulated_By_CName6)
5a. Withdrawal(TID5, EName2) 5b. Deposit(TID5, ContactID4)
![Page 8: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/8.jpg)
Relational Schema continued
6. Council_Committee(CCName, Description, Officer_SID1b)
7. 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)
8. Advertisement(AdID, Media_Type, Date_Distributed, Quantity)
9. Location(LID, Location_Name, Room_Num, Capacity)
10. Ad_Funds(AdID8, With_TID5a)
![Page 9: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/9.jpg)
Relational Schema continued11. Event_Funds(EventID7, With_TID5a)12. CC_Meeting (CCName6, Meeting_EID7d)13. Contacts(CCName6, ContactID4)14. Reserves(LID9,CCName6)15. CC_Organizes(CCName6, EventID7)16. ES_Organizes(EName2, EventID7)17. Recruits (Potential_SID1c, EventID7)18. Ad_For (AdID8, EventID7)19. CC_Creates (AdID8, CCName6)20. Member_Attends (SID1, EventID7)21. EC_Attends (ContactID4, EventID7)22. Represents (SID1, EName2)23. Is_Part_Of (SID1, CCName6)24. SubmitsBR (EName2, CCName6)25. Coordinates (Meeting_EID7d, EventID7)
![Page 10: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/10.jpg)
Normalization1st 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)
![Page 11: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/11.jpg)
Normalization2nd 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)
![Page 12: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/12.jpg)
Normalization3rd 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)
![Page 13: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/13.jpg)
Relations View
![Page 14: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/14.jpg)
QueriesQuery 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 AGroupBy EC.Contact_name;
![Page 15: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/15.jpg)
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 sWhere ma.SID = m.SID AND C.Meeting_EID = ma.EventID;
![Page 16: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/16.jpg)
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 wGroup By es.Ename;
![Page 17: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/17.jpg)
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 maWhere e.EventID = adf.EventID AND adf.AdID = af. AdID AND af.With_TID = w.TID AND ma.EventID = e.EventID;
![Page 18: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/18.jpg)
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.
![Page 19: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/19.jpg)
Queries continued
Select Avg( Select count(am1.*) * Sum(d.Amount) From Alumni_Member am1, Deposit dWhere am1.ContactID = d.ContactIDGroup 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;
![Page 20: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/20.jpg)
Main Switchboard
![Page 21: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/21.jpg)
Screenshot – Query 1
![Page 22: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/22.jpg)
Screenshot – Query 2
![Page 23: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/23.jpg)
Forms – Add Member
![Page 24: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/24.jpg)
Forms – Print Transactions
![Page 25: Engineers’ Joint Council](https://reader036.vdocuments.us/reader036/viewer/2022081420/568160dc550346895dd00af9/html5/thumbnails/25.jpg)
A Few Groups under EJC