We model a MySQL database using a diagram in MySQL workbench. We then forward engineer the diagram to create MySQL database on the server. MySQL workbench is a powerful tool for modeling and creating databases and we take advantage of it's relationship tool to create many to many relationship between our main movie table, and stars, directors and genres tables. I don't go into too much detail on how the relationships work in this video on purpose, I wanted to make a general overview of how MySQL workbench can be used to quickly created the model of a database with relationships between tables.
Hey guys, I'm now using Patreon to share improved and updated video lesson material. For a small fee you can access all the downloadable files from this lesson (source code, icons & graphics, cheat sheets) and everything else included in the video from the Patreon page. Additionally, you will get access to ALL Clever Techie videos in HD format with no ads. Thank you so much for supporting Clever Techie :)
Download this video's files here:
This download (Patreon unlock) includes:
(Scrape IMDB function php source code, workbench movies relational tables sql source code)
( You also get access to ALL source code and any downloadable content of ALL Clever Techie videos, as well as access to ALL videos in HD 1080p quality format with all video ads removed! )
From the video:
Let's go ahead and create the database model which will hold all the data now using MySQL Workbench (a free program that you can download here). When you first open the Workbench, create a new connection by clicking on the + icon.
I'm going to name my connection localhost and and put "root" for the username. Then press press the storage vault button and enter your password. You can then click on "test the connection" and you should get the message: "successfully made the MySQL connection." At this point, click OK, and then double click on this newly created connection to connect to your MySQL database server.
Now go to File---New Model and double click on Add Diagram. This is the part of the program where we're going to be designing our database model. You will see a toolbar on the left side along with all the tools available which we can use to model the database. Go ahead and double-click on the New Table icon which will place a new table in the diagram. When the table shows up on the diagram, double-click on it, the new box will show up at the bottom of the screen where we can rename our table as well as specify all the column names and their appropriate data types.
Name this table that we have just created "movies", and then for the very first column, enter "id" which will be the unique identifier for this table. This id will also act as primary key which is going to auto increment so make sure the primary key and auto-increment check boxes are both checked as well as not null (NN). Now let's go ahead and enter the rest of the column names.
( YouTube Channel ) https://www.youtube.com/c/CleverTechieTube
( Google Plus ) https://goo.gl/J71p6f - clever techie video tutorials.
( Facebook ) https://www.facebook.com/CleverTechie/
( Twitter ) https://twitter.com/theclevertechie
hi there, great work btw. When I'm trying to connect localhost/phpmyadmin, it says browser can't connect to the server. I've checked MySQL status(up and running) and also I am able to connect in my terminal and create db etc... How can't I connect to my phpmyadmin page?
Thanks for the tutorial. Excuse my total noobness but I guess the only reason why you're using phpAdmin to view all of the data is because this tutorial is for the IMDB php script. Why didn't you just confirm the database existed in MySQLWorkbench? It's all there under schemas.
Hi .Correct me if I am wrong :Movie has one or more genre period.Genre can't have many movies.So this should be a 1 to 1 or 1 to n relationship utmost. Director can direct many movies but movies can't have multiple directors ! I believe the n:m relationship here is only with actors and movies and actors and directors.
I'm confused on the phpmyadmin part. Can I download that if I have a Mac? Huge SQL newbie over here so I was following pretty well with the workbench stuff but then got confused once you went to the phpmyadmin part
Thank you for this tutorial. I have two questions and want your thought to be clear. 1. When populating the tables with data which table should be consider first since there exist relationships and some constraints. 2. Do we also populate the intermediate tables that were created as a result of the many to many relationships (.i.e. movies_directors, movies_genres and movies_stars)? Or these are just used for relationships
Hy Clever Techie! Great tut!! Im trying to build a Real Estate Website Database Schema, but i don´t have much experience. But i can use this tutorial for that?...its kind of the same thing? Can u give me some general hints??
Hi I love your video thank you so much for your advices. But there's a point that i didn't understand. In fact in the stars & directors tables you select Unique box for "name" attribute. But I learnt that attritbute like name,lastname,firstname cant be unique cause it's possible to have several people with the same first and last name. So can you explain me the reason why you did that ?
I don't actually understand why the relation of movies to genres is many to many, because 1 movie has only 1 genre ,and 1 genre can be assigned to many movies so its a 1:n relationship. Please correct me if I'm wrong!
It is incledible! Great tutorial and great presentation. I have a project with about 50 tables and many relationships. I bumped into your video and solved all my complexity problems with that! Many, many thanks to you. :)
I'm using Windows 7 theme, and yes if you want to new relations with existing database model, you first have to "reverse engineer it". So to do that go to Database -> Reverse Engineer and then pick the one you want generated as a diagram.
Governors: senators (or knights) who ruled the provinces of the Roman empire.
The first Roman province, Sicily, was conquered after the First Punic War (241 BCE), and the Senate decided that it had to be ruled by a praetor. This meant that civil (not military) law was applied -at least under normal circumstances- and that the new territories were governed by magistrates who served a limited time. The Romans never did change these principles, although other types of governorship became more important: the propraetor and proconsul were, as their names suggest, former praetors and consuls who stayed in a territory they had recently or not yet fully conquered. The revolutionary politician Gaius Sempronius Gracchus legislated that these promagistrates were to be appointed by the Senate (123 or 122).
The governor of any Roman province always had four tasks.
To start with, he was responsible for the taxes. As the Senates financial agent, he had to supervise the local authorities and the private tax collectors, the notorious publicans. To facilitate things, a governor could mint coins and negotiate with wealthy institutions (e.g., temples) that could advance the money. His second task was that of accountant: he inspected the books and supervised large scale building projects. Next to these financial tasks, the governor was the provinces supreme judge. Appeal was not impossible, but the voyage to Rome was expensive. He was supposed to travel through the main districts of his province to administer justice in the assize towns. Finally, he commanded an army. In the more important provinces, this could consist of legions; but elsewhere, there were only auxiliaries.
Under the late republic, the number of provinces rapidly increased, and therefore, Pompey the Great proposed a new law, the Lex Pompeia de provinciis , in which former praetors and consuls were obliged to become governor five years after their term in office (53). At more or less the same time, he had himself elected as governor of several provinces, which were not governed by himself, but by his representatives, the legati .
The emperor Augustus copied this idea when he changed the empire, until then ruled as a republic, into a monarchy. He was made governor of almost all provinces with legions, and used legati to rule them. At the same time, the rest of the empire was governed by proconsuls. So, there were two types of governors:
Proconsuls. In fact, these men were not former consuls, but former praetors. They governed the senatorial provinces and typically served twelve months. Only the rich provinces -Asia and Africa- were entitled to a proconsul who was indeed an ex-consul. Legati Augusti pro praetore. These men served in the emperors provinces with the armies (the imperial provinces ). Usually, their term in office lasted thirty-six months, although the emperor Tiberius preferred longer terms.
There was a third group of governors. In several unimportant provinces, prefects were appointed. Usually, these military men governed parts of larger provinces. The best known example is Pontius Pilate, who governed Judaea, an annex to Syria. Prefects were not senators but knights. Egypt was also governed by a prefect, not because it was unimportant, but because it was the emperors own possession. When Septimius Severus conquered Mesopotamia, he used the same construction.
After the mid-first century, the prefects were gradually replaced by procurators (except for Egypt). The only difference is that prefects were soldiers and procurators were fiscal officials. It tells something about the success of the Pax Romana .