>

DJ Dates

16Jan/112

Database Literacy: Paper Data Models for One to Many and Many to Many relationships

I designed a pair of paper data models, pamphlets basically, to assist with teaching database concepts. The two paper data models use data about books and authors to demonstrate a flat file database, a one to many database relationship, and a many to many database relationship. These pamphlets are then used by students during class to recreate the same data models in Microsoft Access.

Files to print your own can be found here.

The first pamphlet shows a potential data anomaly that was possible when using a spreadsheet as a database. While we can easily store books with a single author in the spreadsheet, how do we store more than one book per author?

Spreadsheet vs Database Pamphlet

This pamphlet shows how a relational database would use a one to many relationship to resolve this potential anomaly. The pamphlet was designed to be folded into thirds. The pamphlet is printed on a single piece of a paper and folded in half so that both printed sides face each other. The right half of the sheet is folded again, but this time in the opposite direction. This creates a series of folds that hides the middle half of the sheet containing the relationships and keys until the pamphlet is fully opened.

As the discussion of the concepts progress during class, the pamphlet is opened to reveal the changes that occur now that a database is used. When closed, the second pamphlet shows a one to many relationship, specifically the books and authors outcome of the unfolded first pamphlet, and asks the reader to think about what would happen if a book with multiple authors, such as Grimm’s Fairy Tales, was added to this database. When the pamphlet is opened, the addition of a third table and second relationship is revealed, showing the components of a many to many relationship.

One to Many vs Many to Many Relationships

The pamphlet is designed to be printed on both sides of a single sheet of paper, with the final third of the sheet folded over to become the starting position showing the single one to many relationship. This pamphlet used both sides of the paper to accommodate the removal of the foreign keys from the book table which could not be simulated with only a single side of the paper. In order to produce pamphlet, the pamphlet must be printed or copied on a duplexing printer or copier, or one side must be printed, and then reloaded into the printer to print the other side. Faint guidelines indicate where the folds should fall on the paper, and once folded, excess paper from the margins must be removed using a paper cutter.  These paper data models are prepared prior to class and kept closed using a paper clip.

These pamphlets are licensed under the Creative Commons BY-NC-SA license, meaning that you free to distribute and use and modify these pamphlets, as long as you are not charging for their distribution and continue to attribute me as the original author. Please free feel to use these tools in your own classroom.

Printable PDF files (you may need to shrink to fit when printing)
database.v.spreadsheet.v1.1.pdf
1M.v.MM.v1.1.pdf
1M.v.MM.pg2.v1.1.pdf

Original Adobe Illustrator Files
database.v.spreadsheet.v1.1.ai
1M.v.MM.v1.1.ai
1M.v.MM.pg2.v1.1.ai

Comments (2) Trackbacks (1)
  1. So far I think that this video is fantastic, the only thing wrong is that he sometimes forgets to “CALL’ all his key strokes apart from that keep up the good work

  2. Okay, I was going well until he at the1minute 30 second mark in Access 2007 Tutorial 1.3. Many to Many relationship went from the RELATIONSHIP view to an EXCEL view with no explaination of how he got there, gee that makes it hard to follow when he doesn’t verbally say what he is doing OFF SCREEN


Leave a comment


Trackbacks are disabled.