1 / 15

Librarian Survey Database

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

guy
Download Presentation

Librarian Survey Database

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. Librarian Survey Database Brandon, McKay, and Matsangaise

  2. 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

  3. Database Project Goals • Query data based on multiple parameters • Make database accessible to external researchers • Store and access data from survey and other sources

  4. Flow Chart Data Generation

  5. Flow Chart Analysis Without Database

  6. Flow Chart Analysis With Database

  7. 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.

  8. Entity Relationship Diagram

  9. Queries

  10. 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)

  11. 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

  12. 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

  13. 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

  14. Query 3 Show the survey responses of librarians who attended any of the top 5 most attended conferences

  15. 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)

More Related