Welcome to this course on modeling databases!
It consists of three parts:
In the first part, you’ll discover the concept of a database: its purpose and how to use it.
In the second part, you’ll learn how to model a database by creating a UML class diagram.
And finally, in the third part, you’ll learn how to translate your UML diagram into a relational model, which will give your database its structure.
Each part has a short quiz at the end to check that you’ve picked up the critical points before moving on to the next part.
Most chapters consist of some textual content and a short video summarizing the most important points.
Here’s the scenario that we’re going to use:
You are part of a team of mobile application developers for a phone app called NeoNomad, which provides a whole new way to visit a city.
One of the original features of this app is the ability to visit filming locations for your favorite films and televisions series (for scenes filmed on location in public spaces).
Your role on the team is to design the database structure. Your colleagues will be programming the application functionality.
The application functionality will access your database using SQL queries.
You won’t learn any code in this course. However, your colleagues gave you some lines of code written in SQL, which are examples of what the application layer might send to your database to “ask it some questions,” for example:
Which films were shot at the Golden Gate Bridge?
Where was Mrs. Doubtfire filmed?
At the end of the course (as long as you got your modeling right!), you’ll just need to copy these SQL query statements into your software, and it will send back the correct answers — as if by magic!
Thankfully, you’re not starting from scratch. You have a file from the internet that has been made freely available by the city of San Francisco containing locations and details of many movie scenes filmed there.
The file is in CSV format. You can open it using spreadsheet software, such as Microsoft Excel or LibreOffice Calc (which, unlike Excel, is free but still has comparable functionality).
This file is a table with rows and columns. Each row represents a filmed scene for a particular movie, and each column provides the attributes for the shoot. For example:
Scene_id: an identifier for the row (e.g., 887937).
Filming type (i.e., what was filmed): TV film, feature film, TV or web series.
Title: the title of the film.
Release Year: what year the film was released.
Director: the director of the film.
Production Co: the production company.
Location: where the scene took place.
Start_date: when the filming took place.
End_date: when the filming ended.
Are you ready to get started? Okay, see you at the first chapter!
