Sample Database for Exercise

Sample database for exercise

You could try the classic MySQL world database.

The world.sql file is available for download here:

http://dev.mysql.com/doc/index-other.html

Just scroll down to Example Databases and you will find it.

SQL exercises/queries with sample database

There is a lot out there! Here are a few nice ones that offer (online) practice:

SQLZOO - lets you practice interactively with SQL queries for different database management systems;
SQLCourse.com - also an interactive tutorial that lets you
practice SQL queries;
Introduction to Databases - a Stanford online course with a lot
of explanatory video lectures. It is much more theoretical, but also
provides the possibility to practice with the stuff that's taught. (This course seems now to have been chopped up into 5 different smaller courses.);
GalaXQL - another interactive SQL tutorial using SQLite;
SQL-EX - seems to provide SQL exercises, but requires an
account.

Some sample databases can be downloaded here:

SQL Server 2008/2012/2014 -> AdventureWorks sample databases;

SQL exercises/queries with sample database

There is a lot out there! Here are a few nice ones that offer (online) practice:

SQLZOO - lets you practice interactively with SQL queries for different database management systems;
SQLCourse.com - also an interactive tutorial that lets you
practice SQL queries;
Introduction to Databases - a Stanford online course with a lot
of explanatory video lectures. It is much more theoretical, but also
provides the possibility to practice with the stuff that's taught. (This course seems now to have been chopped up into 5 different smaller courses.);
GalaXQL - another interactive SQL tutorial using SQLite;
SQL-EX - seems to provide SQL exercises, but requires an
account.

Some sample databases can be downloaded here:

SQL Server 2008/2012/2014 -> AdventureWorks sample databases;

Database Schema for a Gym Exercise Log App

Is it optimal to store all the individual set log data in a single
table (Log Entries)?

In all likeliehood yes, the exception would be if the number of rows grew to adversely impact response times.

Should the Exercise Type and Equipment tables be separate or just
columns in the Exercise Table? Same goes for Workout Type

They should be separate to reduce unnecessary duplication and issue e.g. if you were to change a type or and equipment then you'd have to apply that change to all rows in the exercise table with a separate table you just have to make the one change.

Would this be a good approach in terms of performance/usability?

With the exception of the Log table (as it stands including wanting the date) then it appears to be a good and efficient approach. The Log table isn't needed as the Log Entries table has a column for the date time (So in response to the comment yes it would be better to make use of the date/time in the Log Entries and to do away with the log table).

Public SQL database for educational purposes

Try SQL Exercises

Start with learning stage

Sample sql product Data

Try doing a search for Northwind database samples. Microsoft also has some free database setups to us for practice. You can download these from their website.

Which database design is better?

A good data model falls out of a proper understanding of the domain. Your domain has three entities:

EXERCISE: particular type of weightlifting move (name and weight)
SET: number of reps of a given EXERCISE (depending on training goal - strength, muscle, endurance?)
SESSION: number of SETs undertaken on a given date

So you need at least three tables. At least, because EXERCISE has two levels of detail: one is the exercise name and the other is the exercise weight . It's quite likely you will need to store SETs of different combination of names and weights (Bicep curl / 10kg, Bicep curl / 15kg, etc) in which case you need a look-up table EXERCISE name and a fourth table SET_EXERCISE to store the weight used for a particular SET of reps.

Having gone through this exercise (o ho!) we can see that your foreign keys are wrong. A SESSION comprises a number of SETs; a SET comprises a number of EXERCISEs (SET_EXERCISEs).

Hence the logical data model should look something like:

EXERCISE (ID, Name, Weight, etc)
SET (ID, FK_Exercise, Reps, etc)
SESSION (ID, FK_Set, Date, etc)

Although this is not quite accurate: SET:SESSION is in fact a many-to- many relationship, as a SESSION will normally comprise more than one SET and a SET can be done in more than one SESSION.

When it comes to a physical data model i.e. tables you should have five tables:

EXERCISE (ID, Name, etc)
SET_EXERCISE (ID, FK_Exercise, FK_Set, Weight, etc)
SET (ID, FK_Set_Exercise, Reps, etc)
SESSION_SET (FK_Set, FK_Session, Set_Number, etc)
SESSION (ID, Date, etc)

The SESSION_SET table is necessary to resolve the many-to-many relationship between SET and SESSION .

The final model has five tables: three tables for the original entities and two intersection tables which join those entities. It so happens that all the relations between the logical entities (EXERCISE, SET, SESSION) have been implemented as intersection tables rather than foreign keys. This doesn't always happen when transforming from a Logical to a Physical data model.

This is not the only way of modelling the domain. As a design activity data modelling is about interpreting the rules to fit the data you need to record. The data is the starting point.

"it seems I didn't make myself clear regarding the Session entity...he naming is probably bad and misleading"

This is why I said the data model follows from a proper understanding of the domain. EXERCISE, SET and SESSION are domain terms. You are of course welcome to make your own definitions of things for your private projects, but in real life data models are a mechanism for communication between Development and Business: the meaning of things is crucial, and must conform to a common understanding. We cannot build a data model where SESSION means something different from what the business understands by "session".

"I also don't understand how a Set can be done in more than one Session?"

A SET is a pattern of EXERCISE for a number of reps. So #1 / benchpress / 130KG / 8 reps is a SET and #2 / benchpress / 100KG / 12 reps is a different SET. If you benchpressed 130KG eight times on Monday and Wednesday then that's the same SET in two different SESSIONs. Maybe it's a layer of detail too far; but if you're going to build a database app to track your workouts instead of using a spreadsheet like most people you might as well build the best data model you can :-)

Again, data modelling is an exercise with a large dose of opinion: if your data model is good enough for your current needs then it is good enough. The thing is, a more rigorous data model is paradoxically more flexible (because enforcing data integrity rules makes it easier to write queries and be sure that the results are correct). What might be good enough now might be a terrible brake on innovation in the future.

Sample database for PostgreSQL

There's a PgFoundry project that contains several example PostgreSQL databases. Most of these haven't been updated for a while, but will still work with recent PostgreSQL versions.

If you need a bigger database, the MusicBrainz music metadata database has full database dumps available for download.

Sample Database for Exercise