150 likes | 298 Views
Librarian Survey Database. Brandon, McKay, and Matsangaise. Database Project. Survey conducted of American Academic ARL Libraries Conducted in Spring 2012 Survey contained 28 questions Multiple check box, yes/no and open ended . Database Project. Goals
E N D
Librarian Survey Database Brandon, McKay, and Matsangaise
Database Project Survey conducted of American Academic ARL Libraries • Conducted in Spring 2012 • Survey contained 28 questions • Multiple check box, yes/no and open ended
Database Project Goals • Query data based on multiple parameters • Make database accessible to external researchers • Store and access data from survey and other sources
Flow Chart Data Generation
Flow Chart Analysis Without Database
Flow Chart Analysis With Database
Business Rules Each respondent has between zero and many attendances; each attendance is had by one and only one respondent. Each conference has between one and many attendances; each attendance occurs at one and only one conference. Each conference has one primary topic; each primary topic is had by between one and many conferences. Each conference has one primary sponsor; each primary sponsor sponsors between one and many conferences.
Query 1 Show the number of attendances reported to each of the conferences and the names of the conferences, order by number of attendances (highest to lowest)
SQL SELECT COUNT(attendance.conf_id), conference.name FROM attendance JOIN conference ON conference.id = attendance.conf_id GROUP BY conference.name, attendance.conf_id ORDER BY COUNT(attendance.conf_id) DESC
Query 2 Create a view of all conference attendances reported by librarians who also reported having an MLS/MIS/MLIS degree, then show how many librarians who reported having an MLS/MIS/MLIS degree went to each conference
SQL CREATE VIEW Master_Attendances AS SELECT * FROM attendance WHERE attendance.respondent_id IN ( SELECT surveyanswers.id FROM surveyanswers JOIN attendance ON attendance.respondent_id = surveyanswers.id JOIN conference ON conference.id = attendance.conf_id JOIN confsponsor ON confsponsor.id = conference.sponsor_id JOIN conftopic ON conftopic.id = conference.topic_id WHERE surveyanswers.q2 LIKE '%M.L.S./M.I.S./M.L.I.S.%' ) SELECT COUNT(DISTINCT Master_Attendances.conf_id), conference.name FROM Master_Attendances JOIN attendance ON attendance.respondent_id = Master_Attendances.respondent_id JOIN conference ON conference.id = attendance.conf_id GROUP BY conference.name, attendance.conf_id ORDER BY COUNT(attendance.conf_id) DESC
Query 3 Show the survey responses of librarians who attended any of the top 5 most attended conferences
SQL SELECT * FROM surveyanswers JOIN attendance ON attendance.respondent_id = surveyanswers.id JOIN conference ON conference.id = attendance.conf_id JOIN confsponsor ON confsponsor.id = conference.sponsor_id JOIN conftopic ON conftopic.id = conference.topic_id WHERE conference.name IN (SELECT conference.name FROM attendance JOIN conference ON conference.id = attendance.conf_id GROUP BY conference.name, attendance.conf_id ORDER BY COUNT(attendance.conf_id) DESC LIMIT 5)