Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jim Beam
Posting Yak Master
137 Posts |
 Posted - 2010-10-20 : 08:26:13
|
| Hi all, I'm looking for ideas/guidance for an interesting project geared at helping me practice my T-SQL skills (in addition to my reading the T-SQL section here). Long overdue as I never really got around to the MS Design+Implementation curriculum, all those moons ago...The key word here is "interesting", as I could use the A/Works sample DB, but that's about as sexy as...watching Susan Boyle pick her nose. I will, however, practice query (re)writing on A/Works as a last resort.I've got some ideas for raw data (I'd have used some data for around the house, but my electricity bill isn't particularly volatile) possibly from the ONS (Office of National Statistics). The advantadges here are that, firstly, they can detailed data on, say, Accounting examination results or road traffic accidents further subdivided into Region, Occcupation, Gender, engine size etc, and secondly as this will already be in tabular format there won't be much table design, but then I won't need to do much. After analysis of existing data, my project should comprise the following determinations , which is just a rough idea:1) what new data I'd like to derive from the existing data;2) what columns are needed;3) which columns should be grouped into a table;4) what objects each column/table needs (constraints etc)5) how tables should be linked (junction tables, FK's etc);Then...6) Write "Create Table" statements;7) Create Sprocs to retrieve "interesting" data; (Most important)8) Create Sprocs/SSIS to insert/update;9) Create Reports to display/pipe out to XML to publish online;If you have any ideas or experience doing this, I'd be grateful for your input.Cheers,Jim. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-20 : 10:48:27
|
You've got a good foundation listed, my only suggestion is a particular, small project: a deck of cards.  Play around with as many different designs for modeling a deck of cards as you can think of. Figure out the best way to ensure integrity, figure out a design that includes or excludes Jokers from the deck, see how many (or few...or any?) tables you need. You'll get a lot of insight into keeping designs simple, and some experience in watching designs get too big or otherwise out of control.Once you've got the deck modeled, work on procedures for shuffling, dealing poker hands, and rating one hand against another (this could be delivered as a report). If you get through this quickly, throw in options like wild cards and different game types (straight, Texas Hold 'Em, or non-poker games like Go Fish). |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-10-20 : 23:14:25
|
create table TwoOfClubs(WhichPlayerHasIt int not null)just 51 to go...
elsasoft.org |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-10-21 : 00:37:52
|
| Hey Jim I have a very nice project in my mind for you.I will send you a mail with the details if you want.Let me know.PBUH |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-21 : 02:48:01
|
| Jim, this is absolutely the best way to learn. The key is to find a project that intersts YOU.I play fantsy football, so years ago I created a database for that.If you're a baseball fan, or a fan of any sport, a database to store your favorite teams/players records, scores etc can be fun. Write queries to retrieve all kinds of statistics.Movie buff? Put your favorite flicks and actors in a database and write queries to retrieve movies by actor/director/producer and queries to retrieve actors by movie etc.Budget/checkbook databases are fairly straight-forward, but a little mundane, unless that is intersting to you.I'm a DBA, but on the side I shoot fireworks shows. I created a database to track product, scheduled shows, and such. It's basically an inventory and scheduling app, but it can apply to almost any activity.Work out a lot? Create a db to track your exercises, measurements etc.What interests you? Surely your other hobbies/activities can be represented as a database. Again, the key is to find something you find interesting. If it has utility, even better.If you still need a kick start, give us some ideas of what interests you and we'll help ya turn it into database design ideas. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 03:30:37
|
| Other ideas of possible interest:Issue tracking (if you need that for work) - Issues, People, Assign issue-to-person, reports on Due date, Priority, Work loading ...Forum - posts / replies / tags / membersWeather - temperature / rainfall / windspeed. Going to take you ages to collect your own data ;) but Weather Underground probably has amateur recorders near you who's data you could harvest for starters. You can build your own weather station from Cheap & Cheerful sensors. There is a very good book on the subject called something like "Build your own weather station" (I'll go find the exact details if anyone is interested). That would be a project for a soldering iron too!! |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-10-21 : 04:39:57
|
| If you take a look on [url]data.gov.uk[/url] there is not only some data to play with (take a browse through it, perhaps something will inspire you) there is also an ideas section where people have made suggestions of what they would like to see - though I would imagine, judging from the recent excercise, some of these may be illegal, immoral or simply impractical, again there may be something there to inspire you.steve-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 07:25:30
|
| "some of these may be illegal, immoral or simply impractical, again there may be something there to inspire you."Bit of an implicit JOIN there Steve!!!! |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-10-21 : 08:42:12
|
quote:
Originally posted by Kristen
"some of these may be illegal, immoral or simply impractical, again there may be something there to inspire you."Bit of an implicit JOIN there Steve!!!!
Damn those robotic eyes-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
Jim Beam
Posting Yak Master
137 Posts |
Posted - 2011-02-01 : 09:21:29
|
| Just to resurrect this old chestnut, I've decided to do it on the basis of my workouts. Currently I train 4 times a week. At the mo I've got a 2-column Excel spreadsheet below, partioned vertically by Bodypart (whatever part of my body I'm training that day) and Target Weight (whatever I managed to achieve last time I did a movement), and horizontally by movement, (grouped by bodypart) as below (I'll probably lose the formatting totally - how do I turn on HTML?)...As the purpose is to lose flab (at the moment) I think I'll just need a single table, though I possibly will want a 2nd table to track increases in arm/chest etc measurements, and compare with progress in strength. Though I am probably not normalising as much as I should/will, for now I propose starting with on table for OLTP : Create Table 'Strength'(Date (PK), BodyPartID (FK), MovementID (FK), Target Weight And two relatively static tables:Create Table Bodypart 'BodypartID' (PK), 'BodyPart' Create Table Movement 'MovementID' (PK), Movement SAMPLE DATALegs Squat 130 Deadlift 150 Leg Extension 140 Leg Curl 60 Calf Raise 15 Seated Calf Raise 80 Chest/Shoulders Bench 80 OHP (standing) 60 BB Cradle Raise 80 Chest Flys 20 Shoulder Flys 15 Bent-over Flys 15 Shrugs 110 DB Press 30 Back Bent-over Rows 90 Lat Pulldown 80 Seated Cable Rows 70 Good Mornings 90 Isolation/Arms Barbell Curl 50 Bicep Curl 15 Wrist Curl 20 Wrist Rise 10 RG Tricep Pulldowns 30 Dips (Reverse stance) B/w |
|
|
|
|
|
|
|
|
Study project wanted