REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 48: RESEARCHING SOME DATA AND POPULATING THE RELATIONAL TABLES WITH IT

Jetzt ist es an der Zeit, um ein ... oh, sorry, I thought you were German for a second there.

What we need to do now is to populate the Tables we created in DBWB with some actual data.

While searching for the data we will need to populate the Tables, I realized that I had forgotten one piece of data that is definitely needed – the year the movie was released. After all, I want to make this data filterable on several criteria that matter to people, one of which is when the movie was made. Some people don’t like old movies, some people only want to watch old movies.

So, I added another column to the Movies_Main Table, year_released, with a data type of year.

What people want to know, typically -- when they want to filter a list of movies so as to show only the ones that they might be interested in – is the following:

“Who’s in it?” (which Actors appear in the movie)

“What type of movie is it?” (IOW, is it a comedy, action, drama, documentary, or what?)

“What did viewers think of it?” (IOW, what is it rated on a scale going – in the case of imdb – up to 10)

“What is its MPAA rating?” (Is it G? PG? PG-13?”)

...and again:

“When was it made?” (within which range of years was the movie made)

Later (when we get into Web stuff, and create a Web site), we will get into populating the Movie Tables in a big way -- and in a different (and much more “elegant”) way -- but for now I’m just adding the data for a few Movies (21, to be precise) by simply searching for them on the imdb website and hand-populating a CSV file with the pertinent information.

Once we’ve got the CSV file, we will populate the Database Tables in a similar way to how we did that before, but this time getting the Ancillary/Helper tables involved, too (that is, the Lookup Tables and the Many-to-Many Tables).

Actually, instead of a literal CSV (Comma-Separated Values) file, this time we will use a semicolon-separated file. Why the change? Because some of the Movie Titles may have commas in them, and I don’t want to have to omit any part of a Title in order to get the code to work correctly.

So the SCSV (SemiColon-Separated Values) will have this structure:

title; mpaa_rating; imdb_rating; movie_length; year_released; director; screenwriter; genre1; genre2; genre3; actor1; actor2; actor3

You see that the data in this test set of data is going to be limited (for example, it can accommodate only up to three Actors) and also contain empty “fields” at times, as not all movies have more than two, or even more than one, genres attached to them, but even so I’ve got three slots for genre. This perhaps shows you why it is necessary, or at least very much preferred, to have Relational Tables in our Database. The structure of this “SCSV” file is klunky and only suitable for “quick-and-dirty” testing like this.

After doing the “dirty work” (searching and noting the various bits of data needed), here is the SCSV file, containing the key data for 21 Movies:

The Princess Bride; PG; 8.1; 98; 1987; Rob Reiner; William Goldman; Adventure; Family; Fantasy; Cary Elwes; Mandy Patinkin; Robin Wright

Life of Pi; PG; 7.9; 127; 2012; Ang Lee; David Magee; Adventure; Drama; Fantasy; Suraj Sharma; Irrfan Khan; Adil Hussain

Wadjda; PG; 7.5; 98; 2012; Haifaa Al-Mansour; Haifaa Al-Mansour; Comedy; Drama; ; Waad Mohammed; Reem Abdullah; Abdullrahman Al Gohani

The King of Comedy; PG; 7.8; 109; 1982; Martin Scorsese; Paul D. Zimmerman; Comedy; Crime; Drama; Robert De Niro; Jerry Lewis; Diahnne Abbott

The Wizard of Oz; PG; 8.0; 102; 1939; Victor Fleming; Florence Ryerson; Adventure; Family; Fantasy; Judy Garland; Frank Morgan; Ray Bolger

The Majestic; PG; 6.9; 152; 2001; Frank Darabont; Michael Sloane; Drama; Romance; ; Jim Carrey; Martin Landau; Bob Balaban

Emma; PG; 6.7; 124; 2020; Autumn de Wilde; Eleanor Catton; Comedy, Drama; ; Anya Taylor-Joy; Johnny Flynn; Mia Goth

Bang the Drum Slowly; PG; 6.9; 96; 1973; John D. Hancock; Mark Harris; Drama; Sport; ; Michael Moriarty; Robert De Niro; Vincent Gardenia

Little Women; PG; 7.8; 135; 2019; Greta Gerwig; Greta Gerwig; Drama; Romance; ; Saoirse Ronan; Emma Watson; Florence Pugh

A Beautiful Day in the Neighborhood; PG; 7.3; 109; 2019; Marielle Heller; Micah Fitzerman-Blue; Biography, Drama; ; Tom Hanks; Matthew Rhys; Chris Cooper

The Art of Racing in the Rain; PG; 7.6; 109; 2019; Simon Curtis; Mark Bomback; Comedy; Drama; Romance; Kevin Costner; Milo Ventimiglia; Jackie Minns

Paper Moon; PG; 8.1; 102; 1973; Peter Bogdanovich; Alvin Sargent ; Comedy; Crime; Drama; Ryan O'Neal; Tatum O'Neal; Madeline Kahn

The Biggest Little Farm; PG; 8.1; 91; 2018; John Chester; John Chester; Documentary; ; ; John Chester; Molly Chester; Lydia Marie Hicks

A Dog's Purpose; PG; 7.2; 100; 2017; Lasse Hallström; W. Bruce Cameron; Adventure; Comedy; Drama; Josh Gad; Dennis Quaid; Peggy Lipton

We Are Marshall; PG; 7.1; 131; 2006; McG; Jamie Linden; Drama; Sport; ; Matthew McConaughey; Matthew Fox; Anthony Mackie

Joyeaux Noel; PG-13; 7.7; 116; 2005; Christian Carion; Christian Carion; Drama; History; Music; Diane Kruger; Benno Fürmann; Guillaume Canet

Forrest Gump; PG-13; 8.8; 142; 1994; Robert Zemeckis; Eric Roth; Drama; Romance; ; Tom Hanks; Robin Wright; Gary Sinise

Winged Migration; G; 7.9; 89; 2001; Jacques Perrin; Jean Dorst ; Documentary; ; ; Jacques Perrin; Philippe Labro;

Hoosiers; PG; 7.5; 114; 1986; David Anspaugh; Angelo Pizzo; Drama; Sport; ; Gene Hackman; Barbara Hershey; Dennis Hopper

The Goonies; PG; 7.8; 114; 1985; Richard Donner; Chris Columbus; Adventure; Comedy; Family; Sean Astin; Josh Brolin; Jeff Cohen

The Sandlot; PG; 7.8; 101; 1993; David Mickey Evans; David Mickey Evans; Comedy; Drama; Family; Tom Guiry; Mike Vitar; Art LaFleur

Save that content as “Flicks.CSV” to whatever location on your hard drive makes sense to you. Even though it’s not a true CSV file, “CSV” is still a sensible extension for the file, as that allows you to open the file in a Spreadsheet app (such as Microsoft Excel) and – with a little guidance from you – be able to display its contents properly.

Note: It turns out that, in this case, I exercised more caution than I needed to when I opted to make it a Semicolon-delimited file rather than a Comma-delimited file – in the event that using commas as the delimiter between the values would step on the toes, so to speak, of one of the Movie titles. Yes, that ended up being an overcautious move on my part, for ... alas!  There are no movies among the 21 chosen with commas in their titles. So sometimes you code defensively, and it turns out you didn’t need to, after all. But it also didn’t hurt anything – we will just Split the lines on “;” instead of “,” in the parsing code – no big deal.

POPULATE THE DATABASE TABLES WITH THE SAMPLE DATA

Now we will read and parse the “CSV” file, and populate the various Movies* Tables with it.

Note: By “Movies* Tables” I mean all of the Tables in the Movies database – the main table, the Lookup Tables, and the Many-to-Many Tables.

First, start a new C# Windows Forms Project in Visual Studio.

You probably know the drill by now, but by way of a concise reminder, here are the steps to take:

0) In Visual Studio, select Create New Project (or File > New Project...), then choose “Windows Forms App (.NET Framework)”, as shown here:

1) Click the “Next” Button.

2) Name the Project “Popul8MovieData” (or something else, if you prefer that)

3) Select the “Create” Button.

4) Change the Form’s Text property (what appears on the Title bar) if you want to.

5) Change the value of the Form’s StartPosition property from “WindowsDefaultLocation” to “CenterScreen” – you don’t have to do this, but I find it much preferable for the Form to display in the Center of the screen rather than in a seemingly random “off-center” location when you run the app.

6) Drag and drop a Button from the Toolbox to the Form, naming it btnPopul8MovieData and giving it a Text value of “Populate Movie Data.” You will probably need – or at least want – to resize the Button, making it larger (both wider and taller). That’s up to you, though, if you don’t mind puny Buttons.

7) 2-click the Button to open its Click Event Handler in the code window.

We will now open the file, read it, parse it, and populate its contents into the various Database Tables. I will show the code we need in order to do that, and then explain it piece by piece after that.

If we were to get into that now, though, we would go “overtime” (or “overspace” as there is a limit to how long a Newsletter post can be), as it’s going to take awhile to go through it, so we will let you out of class early today. Just one more tip/reminder, though, before you go:

Note: Remember that you will have to install the MySQL package in Visual Studio in order to work with the MySQL Database. Right-click the problem area (the compiler will complain about not having been formally introduced to the MySql stuff when you try to compile it) and select Install Package ‘MySql.Data’ > find and install latest version (rectangled below):

With that, I bid you adieu, and ... see you next time, on the next Step in our Journey.

Until then!

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: