I needed a database project

Old college threads.

I needed a database project

Postby Daegan » Thu Oct 29, 2009 4:24 am

So I did one.

http://173.30.99.237/qbtps/

This is an early beta of a program that will have an in-game stat management system. Eventually, you will be able to manage stats for a tournament, which will provide people with the ability to see a tournament that's in progress in a "live" state. You'll see stats change as they're entered as they happen.

Currently the website allows posting of tournaments, and additional functionality will be added over the next few days, weeks, and months. Something that'll be added some time this week is a more detailed information page for the tournaments, which will feature an embedded google map. I'm slating a rough version of the stats to be functional in a few weeks.

Also, I'll have an iPhone version of this website up (I'm a whore for my own technology) whenever it gets finished.

It's posted on a development server currently until my web host gets its problems sorted out.

You may feel free to register an account, all passwords are encrypted.

I'd like any comments/feedback on this project, including what sort of stats you'd like to see (that SQBS doesn't have, as it's serving as a requirement for what I need to have in my system).

I'm aware the template is awful, rest assured it's only temporary. :)
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri

Re: I needed a database project

Postby grapesmoker » Thu Oct 29, 2009 11:19 am

Hey, this is really cool. If you're working on a stat management project, one thing that would be very useful is for every bonus part to have its own field, so we can see which bonus parts are being converted and which ones aren't. The other thing (the holy grail of quizbowl statistics) would be something that allows you to see where someone buzzed. I've been thinking that the easiest way for someone to do this is to just provide a field into which you can paste how much of the tossup was heard before the person answering correctly buzzed, but maybe that's not the most efficient way.
Jerry Vinokurov
LJHS 2000
UC Berkeley '05
Brown '10
Carnegie Mellon staff
User avatar
grapesmoker
groom of totemic guanacos
 
Posts: 5816
Joined: Sat Oct 25, 2003 5:23 pm
Location: Pittsburgh, PA

Re: I needed a database project

Postby Mike Bentley » Thu Oct 29, 2009 12:35 pm

I think the easiest way to track when people buzzed is to include line numbers in questions and have people write this information down on a scoresheet next to the point value of a tossup. This will give reasonable accuracy in where people buzzed without adding too much annoyance to the person entering score (although it's a little harder if you have a scorekeeper. In this case, the moderator would need to call out the line number).

1: For instance, I could see something like this working with reasonable effectiveness:
2: The Wiskott-Aldrich protein activates another protein that binds to this protein,
3: and on the cell membrane these proteins complex with archvillin and supervillin.
4: Prokaryotic analogues of this protein include MreB and ParM, and another protein
5: that binds to it is mutated in Finnish-type amyloidosis, known as gelsolin. A common
6: method of viewing this protein in cell culture involves using fluorescently-tagged
7: phalloidin, and they tend to collect when nucleated by the Arp2/3 complex.
8: Cadherins and catenins bind to it via vinculin, and ATP binding causes this protein
9: to transition between its globular and fibrous forms. Polymers of it are surrounded by
10: nebulin in one structure, and this protein forms lamellopodia and the contractile ring
11: in cytokinesis. It also binds to troponin and tropomyosin in sarcomeres. For 10 points,
12: name this protein that when arranged in a helix and polymerized forms microfilaments,
13: and which pairs with the motor protein myosin.
ANSWER: Actin

This might make reading the question a bit harder, though. It would take some experimentation with formatting to make this look nice and to not break importation of packets to databases.

Of course this also means that entering stats is probably going to be harder. One potential solution to this is to have people enter stats in that Excel Spreadsheet as they go. On a similar note, I think the technology already easily exists to get conversion rates for specific bonuses without any additional work on the scorekeeper or stats enterer's part by using these spreadsheets. All that needs to change is that SQBS or whatever needs to be able to read directly from these scoresheets and it can automatically fill in values for Bonus 1 round 1. It probably also wouldn't be that much harder to track individual bonus parts, you'd just need someone to enter 3 values rather than 1 value for each bonus (this may be asking too much and may be prone to inaccuracy, though).

I'm entertaining working on a replacement to SQBS (by this I mean it would still be a downloadable program, might only run on Windows, isn't intended for multiple people entering stats) that would incorporate a lot of the above features. I think it would be a lot less ambitious than the previous stats program I thought about making and thus more likely to actually be done, but we'll see...
Mike Bentley
Treasurer, Partnership for Academic Competition Excellence
Adviser, Quizbowl Team at University of Washington
University of Maryland, Class of 2008
Host of the Quizbowl Cast, a Quizbowl Podcast.
Visit Wastebin, a trash packet archive.
User avatar
Mike Bentley
groom of totemic guanacos
 
Posts: 4560
Joined: Fri Mar 31, 2006 11:03 pm
Location: Bellevue, WA

Re: I needed a database project

Postby Avram » Thu Oct 29, 2009 1:07 pm

There are at least two open-source online stats projects already available and in use: my own qbsql (http://code.google.com/p/qbsql) and the system which NAQT used for HSNCT 2009, which I believe they are releasing source for. Each is in a different language (PHP, Python (iirc), ASP), but I hope you look at how others have approached handled this before reinventing too many wheels.

I'd also like to see a nice system for entering stats tossup-by-tossup -- I hope that you will be releasing your code under an appropriate license so that your approach can be adapted for use with other stats systems.
Avram Lyon
Kazan Federal University '11
UCLA '14 (or so)
Grinnell '06
Avram
potter wasted among his clays
 
Posts: 90
Joined: Thu Oct 09, 2008 5:45 pm

Re: I needed a database project

Postby grapesmoker » Thu Oct 29, 2009 1:12 pm

One problem with specifying line number is that different computers size the view differently, depending on the user's requirements.
Jerry Vinokurov
LJHS 2000
UC Berkeley '05
Brown '10
Carnegie Mellon staff
User avatar
grapesmoker
groom of totemic guanacos
 
Posts: 5816
Joined: Sat Oct 25, 2003 5:23 pm
Location: Pittsburgh, PA

Re: I needed a database project

Postby BGSO » Thu Oct 29, 2009 2:03 pm

would it be possible to implement something so that bonuses could be entered at intergers?

I.e. 1=first part only
23= second two parts

and follow that trend for all six of the possible combinations?

I think another problem is the more data we want the more that has to be entered, but would it be possible to take all od this fata from an excel spreadsheet like the ones that Hunter offers?

NOTE: please take all if these for what they really are which ideas. My actual knowledge of programming and how these ideas could be implemented is non existent, I have just had these ideas floating around in my head for a while now.
David Garb-
Buffalo Grove High School '09
UIUC-'13

Former member of the most dysfunctional scholastic bowl team in Illinois.
(11:23:30 PM) garb: Wait, are you talking about the porn or the reeses?
User avatar
BGSO
Amazon of buried jaguars
 
Posts: 685
Joined: Sat Aug 11, 2007 12:36 pm
Location: Champaign-Urbana and Arlington heights IL

Re: I needed a database project

Postby grapesmoker » Thu Oct 29, 2009 2:10 pm

Encoding the bonus values and making the option to specify points earned on each part are both trivial operations. You can either do it by assigning a different field to each part (for logical clarity) or encoding the various parts as a binary mask (111 = all 3 parts answered, 101 = first and last, etc.), if you want to be clever and save space. I generally find that unless you're expecting a ridiculous amount of queries, you're better off with something that reads easier than something that is clever and saves you a little bit of space, just because some day you'll have to come back and read that code.
Jerry Vinokurov
LJHS 2000
UC Berkeley '05
Brown '10
Carnegie Mellon staff
User avatar
grapesmoker
groom of totemic guanacos
 
Posts: 5816
Joined: Sat Oct 25, 2003 5:23 pm
Location: Pittsburgh, PA

Re: I needed a database project

Postby Avram » Thu Oct 29, 2009 2:26 pm

BGSO wrote:would it be possible to implement something so that bonuses could be entered at intergers?
I.e. 1=first part only
23= second two parts
and follow that trend for all six of the possible combinations?


Usefully applying this and other approaches to looking conversion of individual bonus parts depends on knowing which bonus was read, which is sufficiently unpredictable (out-of-order bonuses are not entirely rare) as to make this probably more trouble than it's worth.

BGSO wrote:I think another problem is the more data we want the more that has to be entered, but would it be possible to take all of this data from an excel spreadsheet like the ones that Hunter offers?


The Hunter spreadsheets are sufficient to provide tossup-by-tossup accounting of how many bonus points are earned, which would shed light on exactly how the relative difficulty of bonus parts plays out in real games-- i.e., do teams with BConv=15 convert 30 / 0 (all or nothing), 20/10 (easy and maybe mid, never hard part), etc. Perhaps most importantly, it would provide clear data on tossup conversion that might be easily compared between mirrors if designed properly.

If I find the time and motivation, I'll probably be adding something like the Hunter scoresheet to QBSQL, or an importer for Hunter scoresheets. If someone else has the time and motivation, that would be a nice coding project that multiple stats programs could benefit from.
Avram Lyon
Kazan Federal University '11
UCLA '14 (or so)
Grinnell '06
Avram
potter wasted among his clays
 
Posts: 90
Joined: Thu Oct 09, 2008 5:45 pm

Re: I needed a database project

Postby Daegan » Thu Oct 29, 2009 6:48 pm

It may be possible to do an after-the-fact "where did they buzz in?" type system, but I don't see it being used frequently. It'd require the reader to mark where they were buzzed, and the stat keeper to mark either the line number or the word number on where they buzzed in. Another potential problem is that if it literally tells you what words they buzzed in at, you would have the problem of giving away tournaments that may have more games to be played on the same set.

I didn't account for having which bonus questions they got right, but I'll be sure to make this available for usage, thank you for the suggestion.

This web project (I think I stated earlier) is meant to be used as a game is happening. This mean's that it'll be round-for-round data entry, but the UI will not be very daunting. Usability is my primary concern, and I'm going to do everything I can to provide the most statistical information possible without making you enter 800 different values for an individual match.

I'm not sure if I'm going to release the source code at this point. The primary reason is that this is running on ASP.NET using customized MissouriState Web Controls for some code situations, and the database is using MSSQL. None of these are "free" and there'd be a significant investment in putting them into production. I'm also not sure how useful it'll be to people, but by request I'd be happy to talk with anyone interested about this project. I'll think on open sourcing it, though.

I'm aware that there are other database options out there, I looked at QBSQL pretty heavily when I was designing my database tables, but this solution is offering a lot more capabilities, at least in my opinion.
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri

Re: I needed a database project

Postby cornfused » Thu Oct 29, 2009 9:46 pm

Wasn't Andrew Hart working on something like this?
Greg Peterson
Maine South HS '07
Lawrence University '11
User avatar
cornfused
groom of totemic guanacos
 
Posts: 2078
Joined: Sun Feb 12, 2006 3:22 pm
Location: Chicago, IL

Re: I needed a database project

Postby Not That Kind of Christian!! » Fri Oct 30, 2009 9:22 pm

Daegan wrote:It may be possible to do an after-the-fact "where did they buzz in?" type system, but I don't see it being used frequently. It'd require the reader to mark where they were buzzed, and the stat keeper to mark either the line number or the word number on where they buzzed in. Another potential problem is that if it literally tells you what words they buzzed in at, you would have the problem of giving away tournaments that may have more games to be played on the same set.


Also, since it's polite (and since it's good gameplay) to wait after someone negs until the question ends to answer, this would distort the info on "where did they buzz in." If someone negs on a buzzer race in line 3 or 4, for instance, even if the other team knows the answer at that line, there's no way to work that into the statistics.
Hannah Kirsch
Brandeis University 2010
NYU School of Medicine 2014

"Wow, those Scandinavians completely thorbjorned my hard-earned political capital."
User avatar
Not That Kind of Christian!!
iceman of Andean tears
 
Posts: 848
Joined: Mon Feb 26, 2007 10:36 pm
Location: Manhattan

Re: I needed a database project

Postby Mechanical Beasts » Fri Oct 30, 2009 9:29 pm

Not That Kind of Christian!! wrote:
Daegan wrote:It may be possible to do an after-the-fact "where did they buzz in?" type system, but I don't see it being used frequently. It'd require the reader to mark where they were buzzed, and the stat keeper to mark either the line number or the word number on where they buzzed in. Another potential problem is that if it literally tells you what words they buzzed in at, you would have the problem of giving away tournaments that may have more games to be played on the same set.


Also, since it's polite (and since it's good gameplay) to wait after someone negs until the question ends to answer, this would distort the info on "where did they buzz in." If someone negs on a buzzer race in line 3 or 4, for instance, even if the other team knows the answer at that line, there's no way to work that into the statistics.

Presumably you just don't record a buzz point for neg pickups. Or you do, but with an encoded caveat.
Andrew Watkins
User avatar
Mechanical Beasts
groom of totemic guanacos
 
Posts: 5673
Joined: Thu Jun 08, 2006 10:50 pm

Re: I needed a database project

Postby Not That Kind of Christian!! » Fri Oct 30, 2009 9:38 pm

Crazy Andy Watkins wrote:Presumably you just don't record a buzz point for neg pickups. Or you do, but with an encoded caveat.


It's probably true that negs happen often enough and on a broad enough spread of questions (It would be interesting to know how many distinct questions were negged at a tournament) that this would result in not-so-accurate statistics, especially considering that at many tournaments with n gamerooms, even 1/n rooms negging a question will be significant enough to upset the statistics.
Hannah Kirsch
Brandeis University 2010
NYU School of Medicine 2014

"Wow, those Scandinavians completely thorbjorned my hard-earned political capital."
User avatar
Not That Kind of Christian!!
iceman of Andean tears
 
Posts: 848
Joined: Mon Feb 26, 2007 10:36 pm
Location: Manhattan

Re: I needed a database project

Postby Daegan » Sat Oct 31, 2009 2:50 am

How can it upset the statistics when there's no current system that even tracks the total distinct negs in a tournament?

And to clarify, do you mean:

Match A Round X Room A. Player Negs.
Match A Round X Room B. Player Negs.

Would this count as "one distinct", as they're on the same question and negged?
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri

Re: I needed a database project

Postby grapesmoker » Sat Oct 31, 2009 7:13 am

Daegan wrote:How can it upset the statistics when there's no current system that even tracks the total distinct negs in a tournament?

And to clarify, do you mean:

Match A Round X Room A. Player Negs.
Match A Round X Room B. Player Negs.

Would this count as "one distinct", as they're on the same question and negged?


I think what's meant here is that if a player waits to hear the whole question after another team has negged, it will distort the statistics that indicate where someone buzzed in because that player's knowledge may have enabled him or her to get the tossup before the end. While this is true, I don't think it's a big deal. For whatever analysis we should want to do it ought to be easy to exclude questions where the other team negged and it still provides valuable information.
Jerry Vinokurov
LJHS 2000
UC Berkeley '05
Brown '10
Carnegie Mellon staff
User avatar
grapesmoker
groom of totemic guanacos
 
Posts: 5816
Joined: Sat Oct 25, 2003 5:23 pm
Location: Pittsburgh, PA

Re: I needed a database project

Postby ezubaric » Sat Oct 31, 2009 6:39 pm

grapesmoker wrote:One problem with specifying line number is that different computers size the view differently, depending on the user's requirements.


Not if the packets are pdf.

Anyhow, the ideal solution would be to incorporate question delievery into scorekeeping. The question is on the screen. You click where they buzzed, then it goes to the bonus, you click a "Yes" or "no" next to each part as they answer it. The program keeps a running score. This would require the questions to be delivered in some machine readable (e.g. XML) format.
Jordan Boyd-Graber
UC Boulder
UMD (College Park, MD), Faculty Advisor 2010-2014
Princeton '09
Caltech '04
Ark Math & Science '00

Annotate QB Questions for science, fun, and profit:
http://qb.umiacs.umd.edu/tutorial.php
User avatar
ezubaric
mason high on your treacherous scaffolding
 
Posts: 241
Joined: Mon Feb 09, 2004 8:02 pm
Location: boulder, co

Re: I needed a database project

Postby Daegan » Sat Oct 31, 2009 11:49 pm

ezubaric wrote:
grapesmoker wrote:One problem with specifying line number is that different computers size the view differently, depending on the user's requirements.

Anyhow, the ideal solution would be to incorporate question delievery into scorekeeping. The question is on the screen. You click where they buzzed, then it goes to the bonus, you click a "Yes" or "no" next to each part as they answer it. The program keeps a running score. This would require the questions to be delivered in some machine readable (e.g. XML) format.


I don't see much hope in this going anywhere. I think there should be less dependence on a computer in matches, if it's absolutely necessary. Sticking questions into a tournament manager (although being a cool idea) would probably end up being more trouble than it's worth.

For example: If a computer crashes, there's no hope of recording score or even getting questions. At least if there's questions on paper you can still play the game.
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri

Re: I needed a database project

Postby Daegan » Sun Nov 01, 2009 1:25 am

Most of the back-end is finished. I'm still adding support for the stats page (the UI is finished, though. It just won't post data to the database).

If you'd like to login to QBTPS using a test account I've created, you can post a tournament and see how things work. The Tournament System UI will be redesigned after I get all of the subpages finished. Basically I'll consolidate them and make it easier to navigate. I'll likely move the search filter to an "Advanced List of Tournaments" page so it'll uncram the landing page.

You can login with the credentials: test // test

Note:
You must be a moderator of a tournament to manage the stats/matches for that tournament. This is true even if you posted it (not all directors are moderators).

In the round management there will be three options:
Submit - If someone negs, this is the button you'd use. It'll not increment the round # but post the data to a database.
Next Round - Will submit the data in the fields (if any) and increment the round number.
Dead Tossup - Will record the round as dead and increment the round number. This may be useful for statistical tracking of tossups.

I've added support for bonus part tracking in the database, but it's not present in the UI yet.

If anyone have some ideas on how they'd like the data to be output (in terms of seeing the data for printing/website) I'm open to suggestions. I don't like the amount of "data overload" the typical SQBS format gives, and seeing as this system tracks more round information than SQBS, I don't see why it should look like it. This system is also sortable and searchable. This is probably the single largest contribution anyone can make to this project in terms of community opinions.

I'm aware the breadcrumbs are a little off. This'll be fixed whenever I get all of the sub pages laid out and I can generate a useful and meaningful sitemap.

Deadlines:
I'm slating the stat part to be finished by early next week (week of 11/3-11/9). I have a test coming up in one of my classes, but I'll try to finish this part of the project.

I don't know when the overall data output will be finished. I'm thinking of some ways to output it in a meaningful and not overloaded manner.
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri

Re: I needed a database project

Postby Daegan » Tue Nov 03, 2009 3:58 am

I've registered a domain and moved this website onto a live server.

Jerry Vinokurov has expressed interest in providing additional functionality with this web application in regards tournament question submission and editing, but that's yet to come.

The website is stable enough for tournaments to be posted on it, and additional functionality regarding teams (and the finalizing of the in-game stat tracker) should be done some time this week.

I'd love for some tournaments to be posted on this so that I can test it out with additional data on it. If nothing else, it'll be another way for you to promote your tournament :-)

Registration to the website takes <30 seconds.

http://qbtournaments.com is the permanent address of this website.
Ray Lehnhoff
Missouri State University '12
Daegan
potter wasted among his clays
 
Posts: 15
Joined: Mon Jan 19, 2009 1:41 am
Location: Missouri


Return to College area archives

Who is online

Users browsing this forum: No registered users and 1 guest