The Basics of PostgreSQL Database | Ahmad Mohey | Skillshare

The Basics of PostgreSQL Database

Ahmad Mohey, Software Developer & Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
21 Lessons (2h 16m)
    • 1. Introduction To The Course

      2:47
    • 2. What is Relational Databases and What PostgreSQL

      3:40
    • 3. Install PostgreSQL

      3:35
    • 4. PgAdmin

      3:51
    • 5. PostgreSQL Database on AWS

      11:50
    • 6. Create and Drop Databases in PostgreSQL

      6:04
    • 7. Create Users

      14:34
    • 8. Grant Privileges

      6:58
    • 9. Database Schema

      6:53
    • 10. Exercise Create Database, Schema and User

      2:35
    • 11. Database Tables

      11:36
    • 12. Data Types

      2:53
    • 13. Primary Keys

      7:58
    • 14. Constraints

      5:36
    • 15. Exercise Create Tables Employees and Jobs

      8:25
    • 16. Foreign Keys

      13:27
    • 17. Execrise Create Tables Previous Experience

      5:09
    • 18. Import Data Into Database

      5:16
    • 19. Exercise Import Data (Employees and Cities)

      3:06
    • 20. Entity Relationship Diagram (ERD) DbVisualizer

      8:57
    • 21. The Final Lecture

      1:00

About This Class

  Welcome to The Basics of PostgreSQL course, The practical guide to learn the basics of PostgreSQL and pgAdmin.

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. 

Learning PostgreSQL is one of the best ways to boost your career! As it is getting popular each day and many giant companies are using PostgreSQL as a relational database. 

This course will start with you from zero level experience in PostgreSQL and relational databases, starting to level up as we are going through the course. explaining all the questions you maybe asking yourself such as 

What is a PostgreSQL?  what is relational database?  what is a cloud database?

This course will guide you how to install PostgreSQL on a local machine step by step and even how to create your own cloud databases on the one of the most required cloud services providers in the market...Amazon Web Services (AWS).

You will learn what is meant by concepts like DBaaS and Amazon Relational Database Service (RDS)

Some of the points we will be covering :

  • How to install the PostgreSQL.

  • How to install and use pgAdmin.

  • Create databases with PostgreSQL.

  • Understand what is schema and how to create a schema.

  • How create tables using SQL script.

  • How to create tables using pgAdmin.

  • Use different datatypes available in PostgreSQL.

  • How to create views.

  • Understand what does DBaaS mean.

  • You will learn how to create users to access your database based on specific permissions you gave them.

  • How to create different constraints.

  • Create primary keys in PostgreSQL.

  • Create foreign keys in PostgreSQL.

  • How to import a bulk of data in and out of your database.

  • Generate Entity Relationship Diagram (ERD) using another powerful tool called DbVisualizer.

Give your CV a huge boost by learning PostgreSQL...do not hesitate enrolling.

Transcripts

1. Introduction To The Course: welcome everyone to the postage receipt will full beginner's course. My name is Ahmed Mahi and I will be your instructor throughout the course in the very first section we can't understand what is a religion? That base on what? Here. The posting receipt. Then we will be installing the posting Rece equal on the local machine step by step on navigating through the BG Aardman which had the graphical user interface. We will be using work where the Pasta Gris database engine. By now it would be suitable to try the cloud databases on. We will be creating Boston Greece database server on one of the most popular cloud and the market which had the Amazon whips services or commonly known as the AWS. And we will be using the RTs off the AWS and all of these concepts are going to be explained then moving to the next section. You're going to create your first post agree sequel database annual decorating users on a grand them a specific privileges based on the level of authority under the base in distance . And we're going to do this. The step by step culture will be learning. What is the debate schema and why it is important on how to create the schemer Imposter Gris database moving to the next section, which fall of interesting on the board in concepts it will be time to create tables, the heart off the relation of little bases. We're going to do this using the sequel script on the BG abdomen, which is going to be very easy on fast. With the help of the BG admin, we will explore the different designs available for us in the cost agree sequel Can. I will be applying different constraints on, of course, after understanding what is a constraint on White is important. After that, you will learn what is a primary key. What is a Frankie on how to work with them? Imposing re sequel Using BG Amblin We'll be creating relationships between different tables . Then we'll have a chance to fell our tables with real later, using the comport option, which is a great feature, MBG admit, as you're going to see, also will be drying out toe called Devi Visualize er we will install this tool and make a connection to our post Idris database, and we will generate the entity relationship diagram and you will learn. What did this mean? If you're facing any problem off the mentioned features, just send me Discourse is going to be abated every week with new lectures, exercises, art kills on more. Let's get started. 2. What is Relational Databases and What PostgreSQL: Let's start with what is relational databases? On what is most agree sequel, a relational database simply is a collection of data items was pre defined relationships between them. These items are organized acids, tables with columns and rows. Tables are used to hold information about the objects to be represented in the database. Let's take a look at this example in here. I think that we have a nimbly a leisure. For example, Levi has a first name, last name. First, they a couple of information in your building number is living in history to city. She's leaving and hold through a job company her day, and the job itself has data such as Job Main, the minimum salary, the maximum salary. And we can keep adding items if you think of it. There is some condoms relation between all of this information. We can say that there is a group of data or a table. Full information about Olivia like the first name last name and birthdate and etcetera, and we can add other elements like the email, phone number, nationality and many others. So we can guess there's this information into a table. As you can see in here a day before the addresses A table foods job. So we have three tables, maybe the tables for information. Personal information about Bolivia on not only Levy, of course, other. And please, if we have other employees and our database and we can have another table for the building or others for simply, as we can see here in the second table and we can have information about the jobs and each employees working in which company and the hard eight. So each one as we can see in here we can have a force table, full job itself natural then please. But for the jobs, the job name, the minimum salary for the job, the maximum salary for the job, the requirements and we can keep adding items like this. As you can see, there is some kind of link in here. What's linking the three tables that going toe information about him, please. It's the I d number, as you can see in here. So you see here in the I d number, the I D number and the I. D number, and we can link this three of them on. We can link this table in here. The dog with this field in here, the job as we're going to see later. I'm just explaining briefly the concept off relational database. So what is Sq? His? Gill is a structured query language. Those towns were structured. Query language is the primary interface used to communicate with relational databases. S A gear or sequel is astounded language for relational database management system. Such has passed, agree, Seek World Bicycle, Secret Server, Oracle and Maria DB and many others. We have some sequel statements that we used before. Tasks such as of the data in the database overachieve data from a database. Not only does we have a lot of statements that we can use as we're going to see, like the insert, create select Lee Wardrobe and many others. So this is just a brief introduction about relationship debates and post degree sequel. Next, we're going to see how to install the Boston Greece equal that based See you in the next Victor 3. Install PostgreSQL: Okay, Now let's see. How can win is told it posted re sequel. So first you need to go to this. You're all here posting Greece equal the orig from this. You're ill in here. We can go to their live button for me. You're gonna choose the Virgin operating system. We're going to use it from the window. So I'm going to click on Windows. No. Load the installer on from here. Select your virgin. I'm going to choose the latest virgin available for me right now. Selective printing system. We're going to choose Windows. You can choose from Sergi two or 64. I'm using a operating system, Windows 64. So I'm going to 64 I'm going to click dollar. So go ahead on deluded. I haven't really alerted in here, so I'm going to start the installation. Terrible double click on the post in Greece. Fire. That has been deluded. That's very straightforward. There's no complication, as you will see in a bit. Okay, here is the set of window will go ahead on first. Next. This is the installation directory. I'm gonna leave it as a default on this oldie component. It will be in a strolled, so it's going to install the post degree sequel server, the BG Haveman, which is the graphical user interface it will be using to do almost everything through the database. The steak builder and the common line are using the command lanes using for if you like to validate database using a command line and the stag builder is used. If you like to manage of AIDS and matches something like this to go ahead and click next, the data also choose it as a default default if you change it, of course, if you want. So here we going to choose a password for the super users Sink fit as an administrator called Posting Grace. So I'm gonna on my fast word who had anti of yours, Chris. Next. This is the default. Or leave it as it is. We're gonna leave these, as it says on it will start an installation immediately. Brooks folks on. Here it is. It's being installed to. It was a video until the restoration done on. And it should be fast. Okay, the installation is done, as you can see. Now go ahead and finish. Okay. You can go ahead. And now and cancel this set up from here. Just Cruz. Cancel it now. From the start menu, you're gonna store the post degrees. Here it is. Let's go. Answer for it. Here it is. Most agree. Sequel 10 on here. The BG admin. Open it. It should work. As you can see, the management holds for the post agree sequel. There is other ideas that we can use our management pools. But this is the default one that is installed with the poster. Your sequel itself. So here it is. We are done. Next, we're going to explore some of the features off the PG admin. See you in the next lecture. 4. PgAdmin: Okay, Right now let's explore the PG Amon. The graphical user interface it will be using to manage the pasta Grease sequel that bits. So here, as you can see, we have only left two servers. If you open this right here, we can see that we have a server Goal Post, Greece 10. Disagree Sequel 10 Who had an open it. And if he knows, where is this read Crossing here. This means that the servers disconnected when it connected to the server. Connect. Go ahead and expand it. It's going to pass quality password that we've specifying when you're installing the post agrees database. Go ahead, do your password. Go ahead and save it if you'll learn she was okay on here it is the first will t. Here is a very nice day, sport. As you can see, it's still in me server sessions as there is. Any connection is going to the database transactions, but seconds really cool features that we can see or managed out the debates. From here here, you can see on the left that we have a dead basis. We opened the debate, says we have to get a baseball post, agrees Of course, we can come in here and create a database. I can just right click in here, create database. I'm not just I'm doing this for the administration. We're not doing anything, Actually, just show me he was the future of the DVD 100 house. So I can come in here and committed the base may be called H or And if you know that the stories that have been here cold SQL it's going to generate the SQL code for us. We don't have to do anything, which is kind of nice, as you can see created the Basij. Are you'll be familiar with this cold in here? Don more about this. So go ahead and create the database on presidency. Now we haven't you to debate its being created with some seconds, it will be Life has been here. It is on inside of this Debbie's. We have some other options we have The scheme is inside. The scheme is we have probably can make it private. Of course, on we have tables, tables is the main core of the debates that the debates is built on tables, among other things. Of course, so we can come in here and committee tables, right click again. Pregnancy is very easy. Here it is. It's killing me. The name off the database, All the table actually. Serve the table is gonna go wickets he is. Go ahead and change the owner and we're going through all of this devilry, Schemer, table of space. You can add a comment if you like this as the table to score all the information about him , please. For example, Go ahead and specify the columns on. As you can see, as I'm timing, it's telling me or telling me the sequel Command Musical. It's now you have created table bubbly because it's public. And please, I changed it. Maybe to him please jobs going to afflict immediately, as you can see. So I created it. It's now an empty able. There is no coolers inside of it, just just administration, as I told you on the bus degree sequel does the race. It's already created for us and have the same almost has the same things public as you can see. Ebel's has triggered fund shows, types, views. I'm not going to see most of this feature served. Don't worry, this is just e breathed administration off the BG admin to get you familiar with. So I'm gonna stop the video here on like this. We're going to see how to install the pasta. Greece equal on the cloud. See your ending extraction. 5. PostgreSQL Database on AWS: Okay. Now, let's see. How can we create the post? Agree Sequel debates on AWS ology Amazon Web searches First you need to go to this year, Ellen. Here. A w ask dot Amazon come from Israel. You will be able to create your free account. As you can see. Here's old he serves it on. It's a lot of services we're going to use. All of this, of course, will be only interested in the database features off the aid of the S. So go ahead and creative recount. It will charge you only for one dolar from domestic card or your visa for will months off free tier access. And we're going to explore what does will months of free to your access means. So go ahead and create your account. Okay, I hope so. Easy. After you finish creating your account, you will have access to the council. From here you will see all of this services that we can be using. So from all of the services will be choosing relational database service granting Quicken. It is the hardiest. As you can see right now, it's showing me that I have here DB stands for the debates instances. I have one out 40 I'm going to create and you want. So I'll go inside of the TV instances. We're gonna create lunch Devi distance. I'm instilling me to select the database engines or I'm going to choose most agree sequel and I'm going to press next. But before I go ahead on first next using this option in here all the enable options believing for the RDS free dear usage. So what is happening with the AWS freed here? Bland is a dear giving you a set of options. You can choose you configuration off the server as we're going to see next. I'm going to check this. Go ahead and press next on. Yes, this is Dave or test Go ahead and church asking me this is a production or a live an test. So this is a development. It just so I'm going to press next. And as you can see, this is working under the RDS free two year usage. As you can see the snakes. Now it's asking me a couple of questions. They're the model. It's only wonders ever. Four straight. Now the Virgin Off the database. I'm going to choose the last one of them over Mean, which is 10.1. Now let's read this blue books in here. The Amazon RDS redo provides a single D B T to micro the DP to Tito Micro. Actually, this is a selection off options so you can find many options that you can select from, but they will has to pay for them at the service. So as you can see, the T to Micro is the smallest, all the free one that we can views. It's giving you one Cebu and one case of friends. If we, for example, we wanted to chew the M two m 42 x large. As you can see here, it's eight cp use on 32 gigs of friends. So depending on your choice, it will in charge you or not the largest one. As you can see here, it's 64 CB use on about 500 gigs of Ram, which is huge. I'm going to choose. He he do micro to be sure that you only selecting the free to use is that you don't have to obey anything. Just go ahead and check this box. Now it's join me on Lee. The available option for the free tier. Okay, now it's asking me, but it's not available. If I would like to have a multi availability zone if l back to drove my server or my database on many locations, and that's not the case right now on Death's the storage for my database. Now I need to give it a name here. Dispositive fire names is unique. For all the DB instance on behind your AWS account in the current region, you re confirmed a region you can go ahead and change region right now on on as you can see here, us East higher. I'm going to choose a name that is unique in this region for my account literal the AWS accounts force. So go ahead and choose any name. Maybe it's or nervous. Enjoy your server inside of the Etch Server, we will be creating the charted ways. So the master named this is the user. As I told you before, this is the admin or the super users that we have seen when we're in extolling the mustard a sequel. So I'm going to choose the name, maybe my name, maybe make it any name that you would like. I'm just going to choose my name on the password going through the Basque word on, Please. Remember it, Empress. Next, we're about to be done either. Visited really nice. Really. Finances a lot of configuration. Just leave. All of this is the public accessibility is said to know. Go ahead and click it. Yes. So that you can access it from outside of the aid of Lee s everything. The database name. If you would like to create a database, as you can see, if you don't specify database name, the eminent RDS doesn't created at the base would like it to create it for me. I'm gonna call it It's our database so that when we see or connect to the database, we will have you get a basic, ungrateful us called HR database. But I had just leave all of this for now, asking you about the backup seven days or eight days. You would like to take you back out. One training. It's just that is important for their where I had on press learns DB instance. It's going to take a couple of minutes, so relax. Until it's done, you need to refresh it every time. Sometimes it's done or employed was out killing us or it takes some time to tell us. So go ahead, relax on, Wait for it to be done. But before we go ahead and do this, just go on view db instance details that you can have as you can see now, the hits theatres of all database is creating. So keep monitoring it until it's done on was a video until it's created. Okay, now the database instance is available and created successfully, as you can see in here. Now we can see that we have some access to some information like the CBE utilization, the database connections of free storage develop of us. Right now, it's actually really nice. And as you can see here, this is the name a few server. So if you'd like to connect, is this database server or your database Post agrees. Database You're going to use this complicated came in here only could be it unvisited. I'm going to do this right now and in here in the details you can see here there is the information about your it obeys the maintenance of security and They were really nice information. You can go. I haven't ticket yourself. Play with it. If you like to modify it, you can go ahead on. But if I It's not a problem Now, I'm going to take this name the and buoyant. It's available and here and really connect. Having here found its available as well in here. Here it is. So I can take it from here or here. I take it that the copy go to the BG admin interface. Right now we have in the servers I have boss Agrees Sequel 10 The one from the installation . And you should have this as well. I can come in here and right click on the community to serve a group or a certain server were simply at a server inside of this tab in here. The servers. What if I wanted to separate my cloud? That a basis so I can come in here and created you server groom? I'm gonna call this cloud databases old servers ahead and save it. Now. We should have any group cloud server cracked servers go ahead and create. And you server Now the name off the server, not the name from here. This is the name that will be displayed in the B G admin. Not this one. Here. Great. This is called the host name as we're going to see. So I'm gonna call this. This is eight of us database. Go to the next up. The connections on you can adhere the host name or the address. Now, this is the name. Leave everything as it is. This is the default poor. As I told you on the years or name we didn't choose Boasted grace as a Here's your name for the outman, which was one for me. I choose my name, please. Remember yours. Now go ahead and enter the passport. Click safe. And here it is. And then we have a new server group, gold claret server. And we haven't you database called eight of years database. We open the database. As you can see, we have each other divisions. The database that we've created it's created for us. When we created the cloud that space on older. Yes, go ahead and expanded on Here. It is the dashboard for this database. As you can see, her database connected successfully. It's on the cloud on if he open the schemers but like you can see that we have old access to all this. Options ended at the base where that we didn't explore. Add them for you're going to explore all of this. But we have access to it. You don't need to win this tool. The Post agrees on your local machine or your server, you can create it in eight of years or any other cloud service provider and connected. Use BBg admin. As you can see, One interesting thing. Actually. It's funny to show you I can change the color off this. Make it. Maybe not the default. Boyd and Black. I can't change it like this, sir, I can come in here first. You need to disconnect the server. Right. Click on the server name on Glick Disconnects server. That's what you like. You disconnect? Yes, I'd like to disconnect. As you can see that we have the Red Cross. If we read like properties now I have the foreground color and the background color. It can change him so I can go ahead and maybe the background color. Make it Go ahead. Choose you. Hers. I'm going to cheer. Is something that will impact. You can go ahead and leave this recommending. Here. This is a cloud that vase posted on Amazon services created on, um, specificity had likely safe on when you connect the next times, of course, going to ask you for the past world. Go ahead and give the password and, as you can see here, easy colors, hideout. Maybe like it. But you can change the colors as you want to make it more interesting for you. So that's it for now, installing or curating a most agree sequel databases on AWS to your NZ next lecture. 6. Create and Drop Databases in PostgreSQL: we come back in this electric, we going to see how to curate undergrowth databases in most agree sequel. But first, let's define a get a base. What is a database? Simply We can define a database as an organized collection of data collection of tables, queries, functions, views on other elements. Are we going to see most of this elements on more in the upcoming Lee? Let's go ahead and see how can create 100 road databases. MBG. So I'm going to collect the full database in here. The most Agrees sequel 10 that's created by T Fall for us when we installed the most agree sequel. When you try to connect to it as when these air is across a red crossing here, this means that this databases not connected with connected this database. If I click on getting here or locally, it's going toe past me for a password. A password for the user Post to Grace Boston Chris User. If you remember from the installation, this is the administrator uses this created mighty fold when we were installing the Posting re sequel. I hope that you will have in the basket because it could be a problem if you don't remember the best word right now. So go ahead and collect. Here it is now it's connected as you can see, how easy the passport and we've seen this before. How can I create together base? I can see right now we have a default database called Coast agrees. How can I create more databases? We can come in here from object out, choose, create database. He's okay, or we can come in here and right click and choose database. Also, he said, we can use it so we can use a sequel code to create another race. If I double click on here on, I will choose Query Tool. And don't worry, it's going to be very straightforward. Called the ethical codes Very straightforward. It's very near to the human language. So if I need to create database, I can simply come in here and shoes or time create data base. I give it a name, so I'm gonna give it a name. Maybe just this is as it is. What? Go ahead on prison this like, um, going to execute this query or this code in here? As you can see, create database query returned successfully in three seconds. That's really nice. What ways? Database. Just go ahead on right quick under fresh hand here in the test database, but it's not connected as you concede the gray color of the database. I've been here around the Red Cross. This means it's not connected, but its course. I can come in here and connect to these database very easy. If I would like to grow up this, that a baseboard deleted just a changes that create Andi making drunk. So if I execute those right now, it's not going to drop it. Can you figure out why? As you can see, here is the reason why it's not going to drop it. There is one opposition using the database, so I can simply because we are really connected to the database. I can simply coming here until it to disconnect database You is a confirmation message. Are you sure you want to disconnected it? A bassist. One. Yes, I would like to do this. Go ahead on. It's you did again on here. It is successful on a like for this right now. No distance. As you can see in here, let's try the even easier. Miss it. If I press right, click and choose create database. But I'm going to name it may be human The sources I'm the owner with the most of Greece user I can go ahead climb recommend if I like. And one of the amazing creatures is this having here the SQL Top presidency It converted What we've done using the BG Admin options to e e sequel code in here Create database human resources. Let's go ahead and create it. We're safe on here. It is you mergers. Is that a basis? Created any problems? The same as if we wrote the code. What if I wanted to drive it? I can just come in here. Spread, click on choose bleed or group. Or I can use the drug commander from here. So I'm going to choose drop database Human Going to your tricking here? Green sources. What do you think will happen if I tried to execute this? Will that obeys be dropped or deleted or not? If I try to do this, it's not going to be removed. And it's love because it there is a connection to this. The base first is going to tell me that there is problems. A connection? Just go ahead on Disconnect it. This is a problem. So there is a connection. Yes, ma'am, it's disconnected. Let's try to lead a drink out. Andi, as you can see in their database, your resources Doesn't that exist? What does this mean? But there is a database called Human Resources. How is this possible? Because we re created the database. We'll try to create anyone I'm going to choose, create database that same name going to excuse us. Just show you something. Human resource is if we go to ethical have we can find that there is a double quotation here. There is in the front on the at the end. So I need to add them in here. Called the Drug Command work. You can see if I drive one this command right now it's going to work. Here it is successfully. If I refresh it, it's working. No humors off his database. So that's how to great Aunt Group databases in Boston, Greece. Sequel to you in the next picture 7. Create Users: will come back and the selected. We're going to see how to great users imposed. Agree sequel databases using BG admin interface. So let's go ahead and try to do it. So right now I'm going to connect with the Post agrees 10 database that, if old one that really created when we install the database, it's all, as you can see notes asking me for the password for the user. Post agrees, as we have seen before on degree that the post aggress user is the super user or the administrator off the database. It's created by default as well, to go ahead. Given the password on now, I'm connected to create another user. Like the poster Grace or even something similar have, maybe lower permissions look like the poster Grace will you to do is just go to the Logan on the group roles, right click on Create Logan or group broke. So now we have this window in here from this will be able to create a Logan or Corporal Luke. Assembly is a user. You're creating a new user with specific permission as we're going to see how to sit it or were cool. What is it group role. Imagine with me if I would like to create multiple users. 10 users, for example, five users with specific permissions, maybe read from seven stables, or only access one database. I can just create the group role, answered this role toe access this one database or this one or set of tables and then assign old users to this one role. That's why it's called Grupo. Let's go ahead and try to create Let's go ahead and give it a name. I'm just gonna keep it simple for now. I'm gonna name it this user. Go ahead on that comment. You're like this is created to access database. Think I would just go ahead and at some information for this reason on? Just remember, when you come back, you can just remember why you created this year's Let's Go to the definition town definition times very straightforward. The password gonna keep it simple. Maybe 1 to 3 on the A current expires. When you would learn this account to be expired, I can simply make today well, maybe make it the end of the months or the end of the year, or get making infinite. Let now Okay, now it's open it. As you can see, just Chris lead home that's ever flown. Should be in Flint Woolsey. Now, the connection limit is how many connections you would like your user to be able to make to see how this would work. I'm gonna make it toe only one. So we will be able on the use of this users. This test user will be able on Lee to have one connection if he tries to do another one. The Postiga sequel database will prevent this user from access the database because there is already another collection. Let's go to the next step, which is the privileges privileges for me. Actually, this is really interesting. Hasn't can see can Logan. This is very straightforward. This is very powerful option. Right now. It's said to know which means that this user, even if I created this user, he will not be able to look. And because I didn't give him the privilege. Can't Logan, Why does is useful? What? I need this. If I have a user, for example, I want to stop this user from accessing my database. I don't need to delete it, because if I had lead it. I will lose only objects. He created all those tracks, all his log. Why would they do this? I can just simply stop him from this option. Here. I can just also use a super user option. Just if I make it. Yes, he will be just the same as the post agree sequel user that's created by the default, which is the administrator, all the super user created by the full If I even turned it this yes, on your teeth. All of the other options are turned on because this is the super user. Let's go ahead and click. Can't look in great roles as we agreed that rube roles are. A set of permissions can be assigned to multiple looking or users. I can make him a lack of creative zone looking or not one of the interesting things in the BD admin and we've seen it before. The last of the SQL is converting all the options that we're choosing into a co create user test with Is Logan most of our user and if I changed some things that right now it's no super user, no agreed to be no create role if I go to privileges and give him the super user privilege now it's changed. Logan Super User Created, Be created. This is really interesting to see that the BG admin is creating everything for your we're going to interact was co. Don't worry about this. It's very straightforward. And we're going to do a lot of exercise says about occurred practicing. So don't worry. But if there is some option and I'm telling you, it is more practical from the production point of view, if you're going to create tables or user, it's faster to do with using the interface read article course. Okay, right now I'm going to make it. Not a super user. If I'm stuck with one of these permissions in any window, I'm going to frickin here. Maybe, for example, I don't remember what is a super user. What is this? What did this mean? You don't need to go to search into documentation or Google or you have to do Is click one of these two bottles in here. The question mark helpful. This the loge, all this one. Yes, it will helpful this object type so we can have Let's try this one the B G admin documentation for this one explaining for you. What is this? What this plan will do it with a screenshot on This is really, really helpful. It will help you in really good situation. Will, maybe something that you're confused. What does this mean? What's the difference between this and that? You can just go ahead and read about it directly without you searching anything. As you can see now it's showing me that. But I can go ahead and create mission. Go back, go back definitions in here, it's telling me provide a password that will be associated with role provide exploration. They account expires. Well, it's explaining what's in the interface. I'm even in here someone or you don't understand. Once you can just go ahead and read about it. I'm not talking just about this. I'm going to explain to you what is this? But if something maybe you were using in your future having discourse or something that's new, you can just go ahead and read about it. This is from the PD admin point of view. You can also use the SQL coat. It's like Chris this year against years of the confrontation for the post degree sequel Cold itself. As you can see here, the cold create role. Three straight forward and then he concedes, giving your description create role as a new role. Troubles in Greece. Equal database cluster And here is even description for all of them, sir, What is inherent? No one here it in here. So it's telling you that they inherited this one here and headed prides during your really good description. That's just through this roll. With the inherent attribute camp automatically used whatever database privileges have been granted toe old bulls, it is directly or indirectly member off without the inherent member in another role on Lee Grant's ability to central. You see, it's very good to see how I can get information from with MBG admin itself without. Even as you can see, I didn't even go to Google Crume, and it opened any browser. Just one click. I have old information, but I need so, as we have seen, that create roles will allow the user to create rolls great at the base. Very straightforward allowed the user or the rolled great that the basis of the catalogue only enable agency it's disabled. If I parent on this Super Users is only a privilege with the Super User as you can instant change or initiate streaming replication and backup. This is something for the administrators if he would like to do it. Application on backups Some of the interesting parts but full of the demonstrators it's left forward. Developers go ahead to the security. You can hold the membership. You can keep having rolls if you life to make this user role part of other roles so he can hurt from it. As you can see right now, I have my cold ready If I just click. OK, it's created. As you can see, it's ready here. The test on my can start using it to I can create and you right click on the servers. Create new server when your name is does user server. Go ahead, name it. Anything you want. Also, the commencing comments really helpful. You can go ahead and add it Now the host name or the others. You can make it like this local posted seemed before, or you can enter the ivy. I'm going to try both of them. So now, right now. Local. Oh, this is a default. Pour it. Lead the mental INS user name right now. It's just the passport. Was Pontus Tree? Yes. Go ahead. Say the password. If you like safe. Maybe the vast word. Isn't it correct? Let's go ahead and go back. Something is wrong. Is good. Do you have a trace it through properties definition, Maybe the account Explorer. They go ahead and sit at the end of the months or they showed you. Let's just make it the end of violence. Okay, on the best word, I don't remember. Actually, just make it 123 Great. Let's go ahead and try it again. Now I hope it works. Let's go ahead. Server. Oh, this is just just sure again the connection. Loco Post. Yeah, User test. Most word 123 Just making vory simple. If you elect change and go ahead and change it now, it's working OK now. Like as you can see, any connects is the basis schema. As I can even create items that's try and create cells. A table. There is a hole section powered people, so don't worry about table tables are very important. We're going to discuss a lot of tables. This is just a tester. Go ahead and create this table. Maybe names contains some names and safer, and here it is. It's created successfully. So this is how you can create years or using the BG admin and how you can get information about the process using the SQL Code or the BG admin. Let's go ahead and try to lead it. So right now I'm going to the hosted Kristen server in here. Outrighted leader. Just go ahead. Bread click on lead. Old room. Are you sure you want to drop the test? Yes, I'm sure it is. It's not the lead hang. You know why? The reason? It's not easy. Actually, if you have done is very intelligent. It's telling me y it's love being deleted because there is, Let's read. The message cannot be dropped because some objects depend on it. The detail owner off table names. So right now it's telling me that I'm bleeding user or role that is the owner of this table . What if this table has sort of information? You're missing something after BG Admin or the Boston Chris equal is allowing me to do this to be able to do this, I have to change the ownership off the table. Oh, just they need the table if I would like right now and let's using the tables so I can just come in here and deleted, too. Lead it from anywhere. So those tumors bubbly Ebel's names. It's empty. Actually, you can see in here here from the profits, you can get old information you would like. I can from here change the honor. You have life to keep it. Let's actually keep it. This is a better practice. Go ahead and say that they changed it. Do the pasta gris. Right now I'm going to be the test now. There is no objects related or connected to the test user, so it should be deleted without any problem on. Here it is. It's leaving without any really piece of cake, a gas using the BG admin. You don't have to worry about the code SQL code, and even if you like to write it, it's here for you. You can read how to create it, but I haven't given you an advice. If there is something that you can replace decode using the interface like the BG admin. Or if you're using another management tool, go ahead and do it using Zittel. This is my boy interview. And this is, I guess what? Ah, people doing to get things done fast. I hope this was fun and interesting. See you in the next Lichter. 8. Grant Privileges: we have seen previously that we can create Logan's or group rules and gives them certain permissions. But this permissions, all privileges can be given server warrant. What if I wanted to give a specific user a specific option on a specific people or a database? Like, for example, what if I wanted to greater uses that can only select from a table? Absolutely. We're maybe to be able to delete from this table and maybe an asserted user to be able to add on updated data. How can I do this in this video? We're going to see how to grant privileges to the level off the add on bleed on select from database tables. So you lived the scenario. I'm going to grade and you user in here. This user will be given specific permissions a couple of minutes, so I'm gonna name it. It's our sources. I'm gonna give it the password form just three symbol password. Go ahead and choose any one that you want. I'm gonna give it a comments. So the account would be expired within a month. Go ahead. Give it any date. If you like the privileges can a little bit of course arm can create databases. That's enough for this user. Safer. But as you can see from this, I mean, you're here a window. I couldn't give it specific permissions, Read or date or read from the table in the database. So how can I do this? We're going to see this. Don't worry. So right now I'm going to create and you table in here. I know that was tilted in disgust. Able? Don't worry. We're going to discuss that in the next section. Just cope with me. Great. A table. It's empty. We're gonna call it, maybe please. Um, save it. Create a new one on one bullet. Meaning managers. If this is an empty table, there is no Coghlan's no data, nothing. Just something to be displayed when I select the user to give it certain permissions. So right now I'm going to grant the HR user permissions on these tables. Specific permissions, maybe read, maybe the lead other going to see. So how can I do this? This is not an option in here. So you're usually just threat week on tables and choose ground wither on. As you can see, here is all the objects invited the base right now. If I didn't create this to table, this will be empty. I will not be able to continue the steps. So right now I'm going to like the employee table. Andi press next. Now, from this table in here, the privileges, the grantee and the one that will be benefited from If I can say this from the permissions in here. So the one that I will need to give September man at the H R user on the grand tour is the posting Grace over the administrator. What kind of frivolous I would like to give to the HR. And here it is. As I told you in the introduction, that there is a sequel statements insert, select off, they bleat on many other. And we're going to this cousin in Dips and Andy Carroll. Don't worry. But right now I can choose the HR user can have older permission organ only selected later from the table, or can only lead the table data from the table or can do triggers. Really, really powerful and very easy to use on die can from this option Here, give it with grand option. If you cheek this chick books in here The Etch our user will be able to give permissions toe others only the permission that they have. He cannot give insert that he can give select. He cannot give the elite but he can give select. If I check on the lead, he will be able to do this. Let's go ahead on and next. Here is the sequel statement again. The BG have money Don't a grade top in it. So grand select delete on table And here the public is the scheme And next we're going to discuss about schemers and how to create them. And why would we need to create schemers on to the end of the statement? Go ahead and finish on. We've given him the permission. Teoh, Add answer like data choosy table in please. Let's go ahead and try. And and real, it's a matter. So I'm going to create and you server. This will be separately and sprayed. And so human resources will be the name on the connection. I can go ahead and make it the local host. Or, of course, if you have your server on the cloud or somewhere else. Not on this PC When? Go ahead and give it the ivy. Others. Or you can simply give this idea resistance. The local off this machine Not utterly. My machine your machine on almost in this mean the local address. Saidi Anders. So I'm gonna change the user name to which are on the best word was one resemble Now we haven't you server in here is what Zizek listened. It was very funny. This elephant in the year old like it Okay, let's go ahead and open letter base pasta grease Schemers but Blick tables Let's go ahead. I'm trying to act or open these electing here The manager I didn't give permissions to the table managers I only said it do the M please. So let's go ahead. I'm trying to view the data in here. All rose many years is as you can see Permission lied for relation Manager If I tried to open the in please really later. Actually it's clothed this right now and here it is. There is no at once here successfully, it's run successfully and the message down here. But as you can see, there is no columns, no doubt about its not giving me the warning about that. I don't have permission. All this user doesn't have sufficient permission that I have seen in here again. Here is the difference. Error. Permission. So I hope this is interesting for you because this is really helpful. When you're working with re awarded application, you would need to do this to give certain users certain permission. So this can be assigned easily using the BG admin interface to you in Z fixed. 9. Database Schema: will come back in this lecture. We're going to see how to create database exclude but through slits, understand what is a schema? So in money with me, if you're working in real war, let's scenario Ever have a database probably will not have one or two tables. You will have a lot of tables, a lot of use, a lot of procedures. Let a little boost objects. All of these little bitch objects needs to be group. So, for example, if I'm doing e database about the company and H R System, for example, human Resource is database, so there is some information will be related to the simply its health. So I would like to group all of the items related to the simply under one category. This category is a schemer, so it's a way of organized group objects related objects in the database. Together, let's go ahead and see how can you create it. So in here, what do you need to do first, let's actually connect with the base in here. We have already at the fold schema created for us inside the most agree sequel it Inside this scheme and breezy public it schemer What if we wanted to create a Newman? Just right Click Pray Schemer, as we have seen with the BG of mens rea flexible re hopeful. So I'm going to name it may be in please So I am doing, for example, a database for the H More on there is some information on Onda objects related to the M. Please is going to be stored or organized together so the owner will be The Post agrees If I like medical men to go ahead and add it security in here. This is a very important have. So the privilege that I'm going to give to this a schemer Go ahead and at the blessing here , as you can see diagram he and the one is going to be benefited from. This is scheming. So who is going to be invented? If I have another you there he will be in here and the grand burn The user is going to grand the privilege. Let's go ahead and cancel this. Yes, you know it's it's pointing. We're going to get back to it. Let me create a user. First, I'm going to create a new user or in create something meaningful. So the main I'm going to create for this user. Maybe it's or user on the definition of this word movie one. Just keeping it with the minimum. Go ahead, make the connection. One learn the religious he can look in. Make him great roles on the leases. That's a good and save it. No word on here, is he? It's your user. Now I'm going back to the scheme. I wreck late grade schemer Poland for please, uh equals or is the most recent upper level of this the privileges. Now we have the grandeur of the administrator or the post. A great user, the grand He, on the other hand, will be the HR user. I'm the privilege that I'm going to give create. Well, the users are gonna give it all like to do almost everything. I'm thinking coming here. If I have a table right now, I have a lot of tables, actually, so I can give the HR user civic permissions on the table. It's health so I can make him insurance elect. A data would eat all of them. Believe it. All of the consequences functional the same or even hives. I'm going to explain all of that. Worry about this. The ehskyoo created for us again. Wilkens doing anything? Go ahead and say for her. And we should have a new scheme. Where? Here it is. So if I create a new table inside of this schema rig leg great tables on Don't worry. We have a whole section about tables. Was a very important right now just cope with me. I'm gonna name it. Maybe m please. Adverse Is this is the address systems in, please. You know the table old gonna name him, please. Actually, because this scheme France Golden please are going to be cooling toe. Have employees not in please. The mattresses call. This is names is the lanes Please all context It is You're his name's hand addresses Africans, you know, inside the they are all inside one that obeys but they are separated into multiple schemers depend on the relation between all of this database objects on this is actually a very good way to organize things as your let's go ahead and create one. Maybe in this here in this public schemer aren't going to read a little Thank you Leave. The scenario is make sure I'm going to create now. You scheme if all the jobs no one was for the employees and one for the drops. So the security there's a schemer again the ground. He is going to be the entire user on the religious Ole. Of course, I can come in here and give it with grand options so this user can ground other people privileges the same religious. He has England and safe it. Then we have sort of steamer jobs on. This was toward the information about the drops Maybe girls available right now on the or leave it as it is not gonna give it in cold right now. And here it is, as you can see, jobs available. So this is how you create schemers on start to organize them in your debates. I hope you're saying that this is useful because we were working really with large database objects and many of them it's going to commit, So it's better to have them separated like this. If you don't want to use schemers, it's OK. You can go ahead and create all your tables in the public. Schemer, it's up here. Um, see you in mixed lecture 10. Exercise Create Database, Schema and User: time for a small exercise. I need you to go ahead 100. Create database. Call it human resource is create a schema inside of the human resources database and call it in, please. And create a user 11 of permissions to create tables and force reading dr from the tables off the database and cold user ajar and score Aban. I'm going to pose a video. Told you, Go ahead and do it on your own. Okay? Yeah, it's easy. Let's go ahead. I'm trying to do this exercise together. So here I have my database. I'm going to create you did a base quick gray database. Call it human. Hey, Soos is the owner is going to be the pasta Chris. No, we'll see if it it is, it's created. That's go ahead and create the U jer in a cool him. Rare. But your abloom this mission just maybe on the password. Keep it simple. Your life to something complicated. Go ahead, choose it. It's up to you, Nick. Natural limit, maybe five religious can look at, of course, can create databases since it for now, go ahead and save the user on this. Created here is Now let's go to the human resources that a base on skewers on, right, click on the schemers create schemer. Go ahead, give it a name and fees. The owner of the schemer. On the other hand, can me the our admin of the security privileges the which or abdomen can have create and usage? There are problem on that centuries echoed. Go ahead and safe it. It's created here. Is there any problem I can even go ahead on? But we don't have any tables to the grand withered and start giving permissions, but right now we don't have any tables. So that's enough for now. I hope it's easy and fun. Do you really think so? 11. Database Tables: OK, now it's time to discuss tables. Let's define what is a database tape. Little Miss table. Okay, finally, it's time to discuss database tables. First, let's define tales. A relational database consists of many components, as I'm going to see, but the most important one at the table. It's the heart of the debates. If there is no table, there is no reason for a relational debates to exist that the base table is where all the data into the debates is this tour. That's why it is a horrid off debates. It's the basis, all the operation, to go home to be this element on the debits. Let's take a look at this example Here is a very simple table. Have the first name last name city and country praise a bunch of data. So the 1st 1 the first name this is called a cola, second colon, surgical them and full schooling. And each record off data is called a rope in here. So you a have from Colon calls its name from the Golan Gold last name, city and country. And they have the Fares room, which have George Henson City L. A R and use a mentor. I can keep adding items to this very symbol date. So how can I design such structure in most agree sequel? Let's go ahead and see How can we do this? We going to do both of them? Impulse ways we're going to do with Impulse Way is we're going to do which, using SQL script now we're going to use it using the BG Admin interface. Let's try the first boat. So I'm going to You're using the human resources at the base that we've created if you don't have a go ahead and created, and I'm going to respect like her and go to the very tall and those are creating offer stick. So the first thing that I'm going to use oh, time is great. You will see that Mexico script is very straightforward, that we're going to see it's not that hard like C plus plus or Java. Was he sure it's very straightforward on morning into the human language, so create what I'm going to create a table. What is the name of the table persons? We want to create a table called persons, and we need to open appearances and glow them like this of East toward Army End On between them, I'll be specifying the columns, all the attributes off this table. So I'm going to add the 1st 1 at the person Heidi, that the idea of each person. Then we have the person name. Then we heard maybe the city around the country. I detect. No, we need to specify each digit time that's going to be stored in each goal. So I need to tell it that the person I d Obviously, this is going to be a number 123 or 10 on sounds and half Tera. So it's going to be some kind of a numeric data type. I'm going to discuss the design in detail later, so it's going to be a very famous that are called and all injured that I'm going to chew the person I d. Which is going to be. He text obviously so going to choose variable corrected and we've specify the Linz. There is plenty of ways to add types for strings. We can use character or very tractor and other. You're going to discuss them armed, see the differences between them. But right now we're going to use for a chore or the variable corrector on. We need specific violence, which is in our case, 100. You can go ahead and increase it if you want 150. Same for the city is going to be variable character. You 100 on the country is going to be variable look as well maybe 150. And if you don't eat the comma after each call told it the most in Greece, equal the BG admin. Know that you're done with this Kulemin here because we can ever going to see we're going to add more attributes in here. We cannot constrains foreign keys or primary keys. So how can BJ admin or the caustically sequel moves that you've done with this? Let's go ahead and run this right now. I don't have any table. If I go to the scheme in, I'm going to work with the public schemer. Have any tables? It's empty. Let's go ahead and execute this using this icon in here or Chris five. Is it going seen here? So Christie, fire for Mrs Aiken has a conceit, derision and successfully on here. The time that's your fresh tables and here is the people created for us persons with the columns. That was great personality named city armed country. What we wanted to Maybe this is probably going to happen. I need to descend on this table you carried table have been with time. Maybe there is a requirement changes you need at some information to change some information. I'm not talking about the data time. I'm discovering this structure off the table itself, the colons with the attributes. So let's imagine that I would like to alter the table and maybe add or modify that they will have some Coghlan's to it. Okay. How can I article okay again? They told you secure a script is very straightforward. So all you need to do is you this keyword alter. This means that we're going toe alter it only to find something altar table, because we're going after a component that's off type table. Because there is other components as we have seen. And we're going to see more options as we go. I'm gonna get older. The table. What is the name off the table that you go to older persons pain and we're done with the first line Let's go ahead and tell her that I would like Teoh in Colon, you say it's like a robot. I'm feeling that I'm going to older this table, how to call into the stable. Now I'm going to I have this feeling your year off Curse. Do each person to know when each person wore horn. What was born, So I'm going to add this as we can use E data, is it is our cold day, but I don't need the holy I want only the year. So I'm going to Z and again the injured goods going to still remember. So if I run this right now, it's going to execute all in here in the query. But I don't need to run this again because this is going toe produce an error because the person's has already existed or created, and right now it's going to try to create it again, which is going to result in an air. So I need to want Onley this so I'm going to select it on them going to click on this icon . I'm here it is. It's successfully executed. That's Jake Persons table again. Never use. Wish you it is fresh. Okay? As you can see now we have a new cola year off. First traded for us. It's easy career. You might need to revive this couple of times, but you will get going. So what if we wanted to find something else? Like, for example, out like to remove a coal? How can I do this? I would like to move. Maybe the city. I need only the country. I don't need the city right now. Oh, for specific barrier. So to do this again, we're going to use older table Christians on going to you. This key water from a cooler drub Colin on space for the name of the government going to drop city. That's it. So older able persons drug, Colon City, Let's run it on. The city will fresh will be removed. As you can see, there is no city only becomes something. That's how you can use the quick retooling here to use well at sequel descriptor before different tasks like having tables, performing or altering and columns in here. So I can do this easily with a BG admin interface so I can come in here. Let me for his drop this able or deleted to drop the people itself. Simply table again. The name with people. Go ahead on this, it's dropped. As you can see, if I air fresh in here no tables. So to grade the table, simply right, click on the tables. Great people going to use this using the e g. Evelyn. Sir, The name of the table is going to be persons. And this is the owner. This would be super user. All the administrator can go ahead and change it. If it likely curated the HR Haldeman, we can choose it, but I'm going to stay with the Post address on. We can choose here, the Eastern American making be Republican, going through them, please. Two different things. Coghlan's the second have been here. We can go ahead and start having so I'm going to have the first column is going to be person already. I'm the disease or I was going to be, as you can see here. But you have been to displaying to me all the available data types. There is a lot of the time, but I'm going to choose. And I am only into that. I'm going to use on journey. Here it is. If I tell you that what will happen, it's going to go to the the lens, its default on they can sit the primary key from here. Primary key said it two years. Oh, no. This is going to be the primary key and we're going to check this later. So just leave it as it is. No, for now. And go ahead and add the colon. Maybe the person they on this is going to be variable director on a grand on the one, but it's five against them. Forget to specify. It may be one hundreds on may be the one last colon city I was going to be you variable correctable as the BG admin is calling it character Varying. I said, maybe 150. I'm just save it If we check the s and fuel in here, it almost did what he did. You know, we have the person already injured corrective ring. That's the differences and we corrected. But this is the same concept. I'm going to go for the So you put in here a Zaken seen here? It created it and the employees scheme a lot in the public schemer that you can see persons here. So that's it about creating tables. Next, we're going to see more about the times. See you in the next little 12. Data Types: welcome back and distinct. We're going to create table and see more Didn't types, but it we'll come back and sticks. We're going to see more of the times and boasting for a sequel. So in here I have the persons table that have created in the previous section going to delete it. So choose radically. The legal drug scanned me, deleted. I'm going to read a new one cable given the same name. Persons late. Everything else I'm gonna move to. Columns have been here. I'm gonna start adding my columns. So the first bomb is going to be Heidi Insulated desire. It's going to be sure. No. Well, you and your Here it is at one. Maybe this time in the person name. What's going to be you variable tractable towards bring he for example 150 and the other bun. This time we're going to using you. Or maybe this Halory, don't you? The salary for this person on the Senate going to use it is called numeric. Here it is in America, thanks to em. Burt's 1st 1 at the limbs. Maybe you're going to use eight. The precision is going to be too. This is the numbers to the point I'm going to need to no more than this. That's good. Didn't make it non or you. That is good. That's other than the one Maybe we need to. The data burst the obeah today. The purse will. I'm going to choose e d. The type off date going story date. It's on another one. Maybe this time I want to add something different. I would like to know this person isn't service on. Not so I'm going to have this corn and service like this is a question on the available editorial for this It's going to be Polian 10 dear. A true holds. Yes, I'm going to choose. It is like a 1,000,000,000. Here it is on now. I'm ready to go. If we take a look at the S, secure the script. It's created for us. Very nice. There's against you can use any American ex to him. Butts. Let's go ahead on secret. As you can see, it's created successfully whether we'll see How can we have Digit is a stable so that we can see them there so that certain is here in the unexpected 13. Primary Keys: Welcome back. Now I'm starting to discuss primary keys for his name to define primary. What is it? Primary. It's a special relation database table colon. Multiple columns in some scenarios that we're going to see does uniquely identified all the people burgers, primary king main features or its unique value fatal. It cannot contain no. So this means that it cannot p empty handed killer be duplicated. It must be unique. Primary key concept is critical to inefficient religion in database. It is a main point. Do the relation to the base. It's the main point to connect tables together as we're going to see almost all of us dealing with primary season all day. We left knowing that we're dealing with them for example, all the citizen well, though, going in details with which country are from. But you must have some kind of a unique number that undefined you that I didn't defies you for the government. Same as for the passport correlations is pink counts on even the employee ideas in a company. Let's go ahead and see How can we? Great bravery, keys, impulse secrecy. So I'm going to you the person's table again. If you haven't already go ahead and drop it, and I'm going to create it again, but with primary key, so I'm going to use. But I have seen before. Trait came in persons on in here. I'm going Give it some colons, Grayson idea of time and Olinger, and I'm gonna give it the tacked on work this as a primary key. Simply you. The keyboard. Primary care Now prosecute sequel that this is the primary key of the table. Let's continue adding a colon to person name variable corrector, maybe 100 on the difference between it's at the city director. The difference could mean the variable corrector on the character who any describe with variable on one with a with A very well in that the none variable, for example, the character. And here is a fix Ed Lin's boiled variable with the variable lengths from its neck. This mean that the storied requirements are different. Director always takes the same amount of space regardless of what your store. On the other hand, the variable is very depending on the specific string. All the limbs that used tour. What does this mean? So if I have, for example, here in this lends its 100 I'm going to store string or a name in this case off 50 letters . Olin's the other 50. I'm not going to be stored. So actually, what's going to be stored is only the 55 towards 30 70 will have the store, on the other hand, with the character. If I stored on Lee 25 27 on a going to be ignored, you're going to be saved on gonna be stored as spaces, which is a waste of memory. So that's boy variable character in this case is better variable and little situations is better. Some situations, any. Do you have the character, for example? Because it's no before that. This field is going to be, for example, five letters or six or seven or even 10. But you know that every time zone beat 10 letters. Okay, well, did you get it so I can mark the person i d as primary key in here? Or you can simply hand it in here. I was very finished adding the columns in here. I'm going to tell it that I'm going to add a primary key on this primary thing is going to be the person fighting symbol of this grand on here it is successfully created. Let's refresh it. What's going to be created in the public French on here is our table persons where the Coghlan's on the primary key is somehow a type of we're gonna strange. So if we open the stream here, I can find that there is a person be here for the primary. Primary key is created for us. We'll make you created this primary Keep So let's try to do this using a constraint way. So I'm going to create on your table Great table You may be cable. Please not start building the humerus also terribly. So I'm going to create the stable Going to have an empty i d is going to be an empty on e name. Two going to be variable corrector, maybe 100. Or maybe the country. Oh, it is. He is from assuming that this is an international company and also 100. Now I'm going to specify constraints. I'm going to have this in here. Police trained As you can see, it's a keyword. The killer has turned. This is purple against are your name for the primary key. All the constraints. I'm gonna name it. Okay, I d And I'm gonna specify that this time off, the constraint is the primary thing. Because there is other life of constraints and we're going to see them on Foreign keys are also constraints. So I need to remind that this is a primary key we occurrences in here. I need to give it. He called them. Except what I just did in here is that after I'm creating my columns, I told the BG admin all the most Idris equal that I'm going to create a constraint on. They give it a name on the type. It's going to be a type of primary key on. They give it the colon itself. So go ahead. I'm grandness again. We should have a new column and you table actually created. Let's refresh, as you can see in here, straight ID was they call them that we specified on the constrained off primary key that be specified. As you can see in here, this is the same name that we get to it. No, this is some different ways to curate primary keys using the vascular script. We can do this in a very easy way using the BG. I haven't Let's go ahead on the lead. The persons I never believed it. I'm going to create it again too, Right quick, New Haven Give it a name, persons. I'm going to the columns and I'm going toe on the personality go living here Kenya's before ulcer Una chewed the injured at a time So here, that's really a lot of this carbs. Maybe Don't need all of these other times, but it's available. So in here all I have to do it just made this on this switch on. Now this turn into a groundbreaking when they come your own person name. They can come in here until it that this is you variable corrector. Everything that's going to be, for example, 100. I'm gonna leave it like this. Let's go ahead and see if it Andi, if we not at the we have a new Able was persons was only two colon personality and Damon the constraint off. Right. Thank you. Okay, So that's it about primary keys for now. Until you in the next picture 14. Constraints: welcome back. And this later regard to discounts, constraints, constraints are simply condition, multiple conditions. You would like to add multiple constraints. So these conditions are used to specify certain behavior to the data entered into the database. What does this mean? So what if I wanted to? Well, make some feel this mandatory. Oh, maybe I would like to specify at the fold value for certain fields. Or I would like to check some conditions. For example, if the salary is below zero, then the database will not let me safe it. So let's go ahead and see this when Rheal exam. So in here, I'm going to create table person. They're going to add some columns, but first I'm going to drop it. So going to drop on going, show you some chicken here. So drop table persons. Now, if I execute this right now, if I open here, I'm using the human resource. Is that a base force armed? Amusing the public schema. If I opened this plus in here and the people as agency, I have to table the person with a capital P on the person with the capital or small p. It's case since that if you have to be aware of this and you have to take care of this. So if I won this statement right now, it's going to grow this table in here, this one with a small be that have been deleted. Let this one. So you have to be careful when using the Kevin below. A small letters aim costing risks. Sequel motivate. So let's go heaven trying to create this table again. So go ahead and trade table Christians, I'm going to add Make the 1st 1 is going to be the person I D. Andi. I would like to make a constraint to this field. It's going to be, Of course, a primary key is going to be the primary key for this table. But I would like it to total inclement without me doing anything. So it's a little bit different than the maybe the physical server. Or maybe be or any other. The base it's going to be a dated hard coal cereal is going to be a number of goals aren't going to be implemented without me doing anything to go ahead to the next colon person named on the person name is going to be variable corrector, maybe 100. Andi. I would like to specify another constraint or conditioning here to make sure that he personally is given. So if it's everything that it will not let me save this Ryker. So I'm going to have this condition not know, re symbol. It's not going to let you safe it. If you didn't give the brace in any, then go ahead. Maybe yeah, grass. The address also is agreeable corrector. I am I going to specify any conditions for the address right now? Maybe the passport number. The basketball number, on the other hand, must be given toe on Lee. One person, not multiple person, should have the same passport number. That's illegal. That's that's not trying. So to make this happen without making it the primary cage because we know that primary key is something unique. G we can you the key word symbol of this and my maybe type of the passport having Grable characters go. Sometimes it contains letters. Maybe we have the country. Country may be very well cracked. Old show you 100 going trees. The lens if you like. Not me, not you having nuts So now I'm going to have the salary and the salary. I'm going to use multiple conditions. So I'm going to specify it's going to be disciplined own number here, like I want to none. Andi, I would like to make that a full value for the salary. Maybe Do sound or C sounds aren't going to chew this keyword be phone. As you can see, it's a keyword on them going to specify the members. I can come in here and make it this number or change into anything. But I'm not going to stop. But here, I'm going to do a check. So if this salary is blow the rope, the database will not like me safe. So what I'm going to do is have another condition. Check condition. This is very powerful, and I'm going to see some more complex checks later. But this is a simple one. So I'm going to check when the data is entered into my table in the salary is greater than dear. So here I have to condition that he fold is 3500 Andi another condition or constrained that it's chicken. If the data is given for this field and it's blue. There is not going to let me save it as we going to see when we're giving data into all data bits so we can discuss more about kind of strains. There is a lot of other constraints, but you can go ahead and play with the checks. You can add multiple checks if you like it for the lands off the fields that we're going to see. Don't that's it about constraints and see you in the next later. 15. Exercise Create Tables Employees and Jobs: time for a whole exercise. In this exercise, I need to go ahead and trade two tables. 1st 1 called in, please. On the 2nd 1 called Jobs. The columns, all the structure of these tables are going to be as following simply I d first goal. A man's going to be the primary key off the table. First name. The last name on both of them can be empty. Gender can be male or female and cannot be empty as well. Country deter burst Well number, email, address and remarks are going to be normal fields. You might need to check something in here. Oh, to search for them. Chris trains that you're going to apply for this field in here. The second table is going to be big. Jobs is going to contain the job I d again to going to be the primary key, which is not going to be duplicated or empty. The job title cannot be empty, the minimum salary and you can go ahead and set the default for the anybody that you would like maybe for his house and any number with you. Prepare the job Maximum salary, on the other hand, and Also, the default is going to be maybe 10,000 or more or less, go ahead and choose. Any value that you're comfortable with is managerial is going to be a different field. So it's going to be a Polian value to see if this job that we added is a managerial or not On the default is not so what was a video? Go ahead on. Try to do it on your okay. I was easy. Let's go ahead. I'm trying to do with together. But first, if you have a name, please table that we've created earlier that, um, deleted. We can using please with the capital E but it's going to be a little bit confusing. So go ahead and grab it from table and please. So that begins toward clean. I don't execute. This are literally free, refreshing tables. Maybe you would like to leave this line or multiple lines, but don't execute it. So to do this, we can use this symbol in here, which is going to be cold comment in here. As you can see, this is a commenter on. Go ahead and type anything. But if I would like to maybe create table and go ahead and give it a name. If I would like get to be there without being executed, I can just make this simple and run of it. This is cold. E commit. Okay, so it's going to be there for me to see or even remove it to executed later. That's not going to be executed. If I run this, What now? Nothing is going to execute. As you can see. Cannot execute on empty query. That's a nice trick that you can use. So let's start build our table. The 1st 1 yard to be called in, please, I'm going to create. It's a room, this David. So he's that I would start to have columns simply why D is going to be cereal to going to be able to implement that you can go ahead and make a number or injured your life going to mark this at the primary. Keep first name of you. Variable Have to maybe 100. I was going to be enough low. It must be provided. You last name Able director again? Maybe 100 you left? No, sir. On. Then we have the gender. There is going to be a variable director were corrected that I know that the maximum length for it is going to be six j the female. So it going to be Onley carrying two options a female or a map. So I'm going to leave it at six on to do this to make it on Lee. Except do values We need to do it. Check constrained. I'm going to do this gender equal single codes. And I'm going to see more about these operators later. Female, or you can use gender equal now. Okay, isn't the next we have the country to going to be variable character, maybe 150. Then we have the They have birds going to be date. We have the phone number old, the mobile number weeds 15 and then we have the last one. Email variable corrector me 50. He's enough, I guess. Think this down here, that you can see all of it. Maybe you can go ahead and add remarks. That's good. We will have to maybe 150 because we can use it to leave any remarks or any other information that is not mentioned in the previous columns. Now let's go hand and trying to execute this. It said the same cold in here. Go ahead. And here we have. He commended line the drop. So it's not going to talk pleasing or open air to go ahead and execute this on. Here it is. It's execute it. If we run right now, we're a fresh. We have the new table in, please, and you columns, as you can see here when we can see that we have constraints do constraints, the primary key on the one for the gender. So that's for the first people it's gonna having correct the next one. Great cable jobs. It's going to contain old jobs. Todo the job I d Only the drops without who's working, want or doing what it's on. Lee the names on the information about the job. So job make this cereal going to be a primary que and come in here and have a job title going to be you very well could mean 250. That's very long, But maybe we have a long names with jobs going to be enough. No must be provided job minimum salary to be decimal, maybe 10. Um, maybe two. I want to be that he fold as we seeing in the prison. Did you go ahead and give any number that you were lying? Maybe I don't leave it full 1000 slides. Then we can move to the job. Maximum salary. Don't be dissimilar as we agreed that the decimal takes two events. 10 on two on may be the default for the maximum. Don't be and 1000. And we need to add the last one which is going to be the he is managerial is going to be his type Bull on default is going to be false. Go, Adam. Trying to execute this, sir right now is why run all of this? It's going to execute this as we agreed. And this is going to happen here because there is already a week old simply so I'm going to live on Lee. This well, of course, come to the operation code off the in, please and commended as we have seen when we added the command to the and please a drop of the MP stable in here. But I'm going to execute it this way to go ahead and create it. It's great successfully. Let's refresh all tables here. We can see that we have you table bold jobs. Let me have one constraint which hit the primary. Okay, great. I hold it was easy to you in the next picture. 16. Foreign Keys: welcome back and stick to regard to discuss foreign keys. But first, let's define what years before in key Foreign Key simply is one whole columns in the table that refers to the primary key in a table. It's used the link two tables together as we're going to see. So what I'm going to do is I'm going to create to your table. The 1st 1 is going to be cold countries. It's gone, store all the countries, and then we are going to create another table Cold cities on. It's going to hold information about the city's answer's going to be some kind of full link , which is the foreign key. Link it to the country, which is going toe link all the cities to a country hasn't gone to see. We're going to do this using the SQL script, so let's go ahead and trying to great ah first table, which scold countries I'm going to create. You're great people. Country is those towards specifying the colon, and this table to the first column is going to be the idea for this country. It's going to be cereal that's going to be the primary key. I can go. And this was fight now. Leader Glove. See, I come now on maybe at the name the country name. This column is going to hold the name of the country. It's going to be available correct 1 £50. Believe it like this. Then we can also specify the abbreviation all the acronym for the country. The evil that land or Germany choose USA or UK so you can go ahead of store. Old obligation for each country. Variable. Correct. Well, maybe this is going to be only five on. We can go ahead and even at the Capitol. Yeah, it's going to be also very well correct On maybe 100. Then we need to see find primary key on the primary key is going to be great. Murky. This is a nice way. Also, primary keys sprayed it from the creation of the columns. Oh, it's going to be the country I d column that we specified the in here. Okay, to go ahead and grand s sounds might have little problem in here. We need to be It's right. India's census fully created that they will. Let's refresh all tables in here on here with the able trade. It's less, very less. Let's go out have on trying to create the next table. Which is he, sit is which is going toe. Have a link between this table out itself. What has been going to see that we're going to create Damon going to be called cities? Hands there may have been mad around the city will have a relation with the country. Go. Each city is related or belongs to a country. So we're going to specify an ivy for the city itself. So be Syria. Okay, then regarding to specify the name Abdi, call the angry, correct 15 grand in making the floor who like we'll just leave it as it is on in here. I'm going to have the colon, which in the country I d. And I'm going to store in this on. And it's going to solve the members both each country's air, if you say, is containable one or two. A 10. It's going to store in here 100. Are we going to see this? So it's going to link to this so that I can get the from this number in here as we're going to see the name of the country that privation the capital of this country. It's not going to store the name on Lee, the I D or the number to link it to the stable as you're going to see how to link. So that's for the structure. Now let's see for the primary key, my Ricky also using this way and it's going to be the city. Chraidi, Andi Right now let's see. How can we create if we're in key? Because all what we have done is just creating some table that you have seen all of this before. So the greater foreign key I need to specify foreign key. As you can see in here, it's so in key on. Then we going to open princes and close them until that the country I D, which is this cooler and the cities in here, the country I D and the city's table is going to be the cold that I'm going to link it on. We're going to use a new keyboard, cold references on and in the references I'm going spurs fine to predators. 1st 1 at the table that I'm going to look, too, and in this case, we're going to link this colony here. So this table, which here the countries and I'm going to specify which colon I am going to link it. So what I've just done in here is that specified and you constrained or a key of type foreign, religious foreign key on. They told the the admin in here that it's going to be the country I d. Golam in the city's tabled, and it's going to reference. Do the countries table to the country idea in the country's table that surround this and see what is going to give us. Do you successfully with fresh our tables? Um, you're it is cities, and if you go to the cola, you can find that we have this recall others have created on. If we go to the constraint, we can find that we haven't you constraint in here the golden one in here at the primary key. Here it is on this one in here, which is called Cities Underscore City I D F. G, which stands for Foreign Key. It's random to rated name from the cable name, which is the city on the column that is used to link toe the people. You can go ahead and change the name later. It is it just the name. But if you can see that it's link it. Do this table in here. The countries Okay, so this is how we can create foreign keys. Using SQL script, we can go ahead and curated using the BG abstinence. It's so I'm going to great and you chemical departments and I'm going toe link it. Do the persons table So I'm going to do is just create red click Create table on going to the name for the stable departments. It's going to contain older departments and my company, for example, on in Nicola tell from going to act in columns will be the board land I d. Time to be on injure. Here it is. It's going to be the primary keen. It's make this Yes, Adam of the world's going to contain the name itself off the coordinates of the apartment name. It's going to be variable character. We court of ring, maybe her 50 on. Go ahead and say this. It's great. It's successfully in here as you can see the apartments go ahead and make it small, be we want didn't can match on the naming convention that we have so going to make it small just to keep things similar. So right now what I'm going to do a free created these street tingles is going to use then police table and link it to two tables that I've created. The 1st 1 is the country's If you remember that we have a country. But the country that we have is time, character or variable character just stooping a character. Now I don't need to store the characters. I can go ahead a link it to the country's people, and they would like that also the department and then police table to see or have the information about beach department teaching, please. Borking camp. So let's go ahead and trying to add these two columns. Choose. Look, please choose properties on right now. I'm going. Colon's on. I'm going to add the cola from here. Chris, have glad and move it up. Words would like. I'm just gonna leave it as it is, So go ahead and change department. I d make it a small let's go to be an injured, and it is. Then we need to go to constraints on select foreign keys. A primary case. We're going to create a foreign Keep click. Add on them going to specify a name. We're gonna keep it. Symptoms. He's department. I d Can you let us was for that? This is a foreign key right now. I need you to focus. I'm Glick on this icon in here is a small life on hero for them. Just click it on. No, we haven't. You table in here that we can manage things better. So I'm going to choose definition the second tap, go to colonise again, seen here that I can go ahead and do some other options that I'm gonna leave it as it is right now that I'm going to columns now, the local goal in which the column that I'm going to link it duty outside thing on the in this case, I'm going toe link than you created. Call Emergency Department Heidi on this little to select the foreign key. I'm going to select the department's table and I'm going to choose the colon in the reference over the foreign cable which in the department idea what I just did is the same have the script in here. Remember that we've created the foreign key. He can try. T is the local Gholam in this table and then refreezing it a little table on a lot of cooler in this table to let's go ahead on. Try to save this first editing here. But I know it's a bit confusing, but this is how it's done. You think to be Jim. No, it's safe, as you can see in here. Then please department idea in a school foreign key on this relation Here, go ahead. They can go ahead and take the SQL. Here it is, it's generated and it out of the in a strange cold. Please. Department I d. Foreign key has been seen here. It's the board mint on the preferences toe Portland's table to this call. Go ahead on. Save it on right now is Ira freshness. Where is we should have and you created Colon. Now we should have a new constrained here. It it it is in peace department that we've created that's going to be a foreign key, which is the silver. Let's go ahead and try to link the country in here. I'm going to drop the school of sound created again and link it to the country's table. Do the column, Country I d. In here to make a relation between the country's armed the empty. So I'm going to choose Appease properties going to the columns. I'm going to drop this in here. It's much cleaner to had it from the beginning that I'm going to add another column. Any country I d going to be used. Time injure Earlham Going again to go Restraints Soaring key. I have a little one. Give it a name going to be called in, please. I'm going to choose any name that you just make it something meaningful. So I'm going to choose country I d nesky and I'm going to click on this small icon in here . I long to Collins Andi column of the local called I'm going to work with here, the country I d around ever since Table is going to be the country's on the reference Kohler was known to be the country. Go ahead and save this, but I can make sure that you add it as you can see that we have to foreign keys just go out and save it how to work if they're afraid. The peace table. Now we should see that we have another foreign key, as you can see in here, which is linking the and please to the countries on the first of the grated, which is linking the employees to departments as we can see in here. So that's how you can create foreign keys using the BD admin on using the SQL script. I know it's a little bit confusing, but you're going to used it. We start so that's it into you. Anti mixed. Thank you. 17. Execrise Create Tables Previous Experience: Okay. Now it's darling for small exercise. In this exercise, I need you to go ahead. And creative people called previous experience. This table two contains any experience any of lead had in the past. The main components of this, they will should be start date and the company name on the city. On your going toe. Link the city in this you created table through the table cities and you might have to sink about some important columns. It needs to be added. So go ahead. I'm trying to figure it out, and we're going to solve it after you try. Okay. It was easy that's gonna had. I'm trying to figure it out. So I'm going to great. Knew they weren't going to created using the BG apple using the has given Scripture. I'm going to do the name for the stable girl. Choose previous experience. Ongoing Teoh are the columns. So the first colon that we need the most important information is the complete medium. I d I'm going to cheat this to be an under It's going to be the primary key for this. Able didn't going to add another cola. If we remember that we have this torrid eight. This is when this simply started working for this company, going right into the date. Very easy. That's gonna have. Aren't you the end date going to be also a date and then we're going to link it to we this city. Oh, company. First I choose you agreeable Director nanotube the city and this column which is going to be linking the city I d is going to be if type Inger go down. And did you find the injure here? Okay, right now we're going to link the city I d in a while. But first, this structure have a very big problem right now have the employee I d. Only key. It's going to be a problem because this mean that each employee is going to have on Lee one record. So, for example, if and plenum one on a like to have a start date and they the company and the city, I've added one experience Foreign plenum one If I wanted to have another experience because it simply may have 12 or three or even 10 different experiences from 10 different places, it won't let me because the impunity it the only primary key. And as we agreed that the primary key is unique, it gonna be duplicated on that is going to be a problem. So to solve it, we can come in here. Andi, have another caller we need to specify. And the cola, which is going to be called the serial. It's going to be the plenum belongs real number 123 or even 10 or even more. That's going to be Inger. We're going to market of primary key as well. If we save the table right now, Now we have created the previous experience with something different this time that it has to primary keys. Which is he The i d on this serial in here. Okay, so what I'm going to do right now is to link the city I d. In the previous experience table to the city's already in the city's table in here. So I'm going to choose previous experience going to choose constrains. Well, I'm going to choose for inky going to add a soaring key. Nicole lived, maybe. You okay? Experience City I d. We didn't choose you and flooring. You just make it meaningful than to this letter, I come go to the Coghlan's through the column that meaning toe languages, the city i d. The foreign table is going to be the cities. The column and the foreign table is going to be the city idea. You don't need to chris out. Um, it's safe. It home is done. If we're fresh right now, the constraints. We should have a new constraint, as you can see in here with this silver color key indicating that this is easy for a key on that for this exercise to you in the next. 18. Import Data Into Database: welcome back and that's actually going to do something really interesting. We're going to import some data into our database. Let's go ahead and see how can we do this? So the important data into the boxing receipt while using the BG abdomen garment in need sees REFILES easier if I could create them using so she like this. They're just normal. Fine. So here I have the country's I have a couple of countries here. I'm going toe low with this data into our human resources that obeys the table countries the same as excel fun. Come in here and see that I have also some lesions render data that I've entered. So let's go ahead on try toe Important. So I'm going to choose the people that I'm going toe poor the data into it. So I'm going to choose, for example, the 1st 1 in the country. So just click quit choose import slash export that in here you can see the during this which that switching between the import and export and the one that I'm using right now in the important to make sure that you're using import So he right need to specify the five name. I'm going to choose the fine name. He really and I'm going to the acre off the country's, um here is format. You can see that I am selecting to seize These results will fixed on quite a but we're using the seas v for Just leave everything l z o I d. In here is the system generated value and we don't need the system generated value for us. I have a really icky Or remember how this option is also important. The header if you have a header. And if you remember that Al filed Harry header in here, it's not only a data, so you need to switch it here with the header. We don't want this to be included into our table two. I'm going to speech that there is he hit. You can go ahead and select also the delivered ER which specify the character that separates the colon within each were whole line off the file so we can go ahead and select comma the one that a meeting at the color and this file or this is also and be defiant to a tab or any of this symbols. So I'm going to select Pickle now in the next cabin here, you can see that here is all the columns to import. As you can see in here, I can go ahead on, Move anyone. If I don't want important, I'm going to like, okay, and I hope everything works. So it is. It's great it successfully, if we now go to the country's red leg view. Old Rose, we will be able to see that we have the data from the seas before uploaded. Oh, important into our able country. That's really interesting. Later, we're going to see how toe insert off the records in each table. But this is as we're importing data as a book. Good. So as you can see, here is the first table loaded ready? No, let's go have on try to upload into one. So maybe a like a blow. The department's right Now it's empty. If I go to view and edit old worlds, it's D as you can see in here. So I'm going to select it and select import, apparently export switch on a gang. Go to the fire. You can find these files in the resources and the lecturer so I'm going to select the Portland's again. Don't forget to mark the hitter. Read him first. Okay, let's read it again. It's created, successfully removed, proficient or run this query the select we're going to discuss. Of course, it's elect in detail. So don't worry about this about selecting the data going to master this. So just click this icon, and here we can find that the data to support it successfully into the departments. Also, let's do one more, which in the jobs moon selected import, switch it. Solidify that mutual load, which is the jobs. Andi, Where can he hit her? It's OK, it's elliptic right now. You all the records on you. It is, as you can see, all the jobs that I've added for the season so I can go ahead and create any jobs you like . Uploaded if you want. That's it for the applauding. Next, we're going to see how toe a blurt the city's because cities arm in please are a little bit different because if you remember, the decided there is some kind of relation between the cities on the countries. There is a foreign key relation with mean. Remember here the key, the foreign key for the country. I d linking the city to the country on the same for the increase linking the employees to the countries on the department's. So we need tohave the countries and apartments uploaded first. Then we need to a blow the in, please on the city's so to you in the next lecture. 19. Exercise Import Data (Employees and Cities): Okay. Now it's starting for small exercise. They needed to go ahead on try on your own two important piece to table. The impedes understates. You can find the files and the resource is so go ahead and try to upload them. Okay, let's go ahead and try to do together to the 1st 1 that I'm going to import is the city's. So I'm going through cities import slash explored. Switch it while name. Where you see it is it's the 1st 1 which he hit her, too. Yes, look, OK, let's take a look at a dread Clegg view data. Well, this one earned. Here it is. Here is all the cities. And if you noted that the rid the country I D in here, it's one New York. A tangle of Chicago used him. He next. Philadelphia. If you go to the country's now and take a look at number one, let's go to countries. It is. We can find that number one in the United States of America. So if I go for example to some other country maybe number three king behind baking, you can find that this are Chinese city and Number Street would be China on. That's totally correct. You see now the foreign key and relation between these two tables. Next, we're going to see how to view this relation in a better way. But I hope you're imagining what's happening here. And if you take a look before it's Moscow ST Petersburg, you can go to check the country's before is Russia. And then we have Egypt number five. You know, we have kind of Alexandria Books or US Blanc Harlem. Shake all of these. Gipsy entity. That's gotta have now and try to import the police Selected import slash export. Switch it The file at C M please. On market hitter Chris. OK, take a look at it. We should find some Rickards. Here it is. John, John and Sofia Noah Tongue flatware. Disa character, G. Ali, Emily. On this all of here are just e dummy data and he confirmed that we have also the country's that's related to each off these in please armed the department each employees working Answer if I take a look at number six in here to and please n department on the SEC's. If we take a look toe the department number six just It's a lick older departments and you can find that it's the human be source of the or the HR, so I hope that's interesting for you and see you in the next electric. 20. Entity Relationship Diagram (ERD) DbVisualizer: we'll come back and sexual guard to discuss something really important, which is the e. R. The diagram or the entity relationship they so imagine, was meeting in Really, what's in their view, probably read. It is getting larger. And as you can see, we have this small database in here that you Murray School says it's getting larger was time, and it's going to be hard to keep track of relations. What relation connects two tables or even multiple tables? So what is empty? Relationship? Entity relationship is a visual form for electing a debate. It showed the entities or the tables in the database ons of relationships between them. It's essential to have the already feels admit things easier as we're going to see. He already normally display the tables. Attributes having the relationships between them. So I'm going to do is I'm going to use another in the face called DB Visual Laser to made this relation. That's really nice. Warm. It's not free. This one. You need to pie it if you like to use all the features, but we can go have on use some of features for free, so I'm going to click on the loading here, make no load. We'll take you to the alert. Beijing Here. Go. Haven't select your operating system on the loaded are and I'm going to install it with you . I haven't already in a stole, but I can go ahead and we stole it like next. Angry? Yes, God and stole it. You really that you can see that we have a lot of drivers for different basis. And here is the one that we interested in which he had the post agree sequel. That ever will be the loaded the river is being used. Uh, that you can uses database engine Teoh. I'm going to flick next. Remove this. I don't want the issue files to be opened with the D V visible laser. For now, Gatting created just a bygone or week lunch icon creating, And I'm sure that sometimes can be boring, but I hope it will pass very fast. Okay. The installation has been completed on liken Go ahead on Run the db visualize er now running. Uh huh. This is the can. If I can't say there's the look and feel if you'd like to make it. Dracula has the name Middle or any other option of these options is available here. I'm just leaving it for the default. Now, don't worry. Okay, Here it is. The first screen, all the foreman here in the new connection strings there. This he, Rick worst. And I'd be on anarchism using the local host, which is the local machine in here, and go to Chris next. Now it's asking you for the driver to see or to make it fit, Which the debates engine. Are you using African scene here? It's really price tool. We can use a lot of the debases bicycle TV to really variety of databases in here and even use the oracle. The one that I'm interested in. Andy posted re Sequels are selected. What's next? Now in here with this Ivy address? All the local hold. But I'm using right now. I gotta leave Notes the server m for Just leave it evident the that of the server is this. If you are going to connect to a remote server, go ahead and enter the I. B. You were there. The domain name, all the I P address for interest server on. He will be Pour it in other ways. It's the default settings you remember from the B G hardman she remember in here? If I go to the database correctly on the posting risk Excuse me. I recollect properties. We can find here that the connection we have the local host how that's them. That kind of change in the port number. The user, which he had the super user is the pasta gris or any other use that have created. I'm going to connect using the pasta grace to the sport toe this service. So I'm going to choose the is ready to go to the post Gris. And I hope that you remember the passport for the administrator. Okay, go ahead on click. Finish on. We should connect it. OK, now it's collecting to the Post A Grace data, Bates. That's what I want. What I want is to connect it to the human resource. Is that a base? Because if you remember that we've created different database right now, it's connecting to this database. But I wanted to connect with your resources that a base So I'm going to disconnected change the option. I'm going to choose the human resources. Armed has to be in case since there If I added for example, human resource is was a small are it's not going to connect this. Why try to connect? Hope of an error has again seen here to the base Human resources doesn't exist If I make the or capital has in the creation just you had some time. Okay, so I'm going to choose human resources on connect on that It connected to it without problem. And if I opened the human resources at the base I should find that I have the in Please Schemer, about creating the table. The ending the schemer Remember I'm using the public schema in here. This is the the fold scheme up But I can go ahead force and created anti please But I'm using the default scheme. Here is all the tables I could now go and open the public schemer. Then they can find all the table that I created in here The cities, the countries all of them are in here really nice features. Who had a level clicker open the object on I can see all the options The Coghlan's you read the city I d city name and the country already and the data type on the size of each of the desire, the data itself. I can see it as you can see here, that is, that we've important using the beauty admin account of the road, the primary keys. And here's one interesting option. The references it's displayed visually in here, as you see here is the city stable connecting to the country's table using the country I d cooler Really interesting. So I can come to the bubbly can hear I'm just doubles electorate often the object choose references on and here it is. We have the er de your is every easy way to see the rd I can go happen the man or the amount or even it is I have a zoomed in from here from missing year. I can if I have a larger did the base force I will add Move through it. As you can see, here is the table. They have the magnifier in here a lot of to ing device something. Then please table older The guitar abs on the columns resident seen here Here is the relationship times all the links between the tables we have the link between the M please and the apartments using the department. I d on link between them, please, On the country's on cities, on countries Really, really very powerful. And here you can see that the jobs is not being link it to any off these table. So maybe by mistake, I forget some relations so I can see visual in here on connected and on the right side in here I can go ahead on display on the reference A table enable Just has relationships mean each other, As you can see in here to move any other people that doesn't have relation. We haven't stopped any made. I can show the link name. As you can see, the Lincoln was displaying in here and the links you go out and show one link, we'll call them, as you can see right now. So it was really nice feature to have. So I hope that you enjoyed this different in the face and this body sing. Andi, do you in the next lecture 21. The Final Lecture: as we're reaching the end of the squares. I would like to say that most agree sequel is a very powerful relation to debates. It's getting popular each day many companies are using. Boss agrees sequel answer. Database companies like uber Netflix Instagram I am to be and many more. We didn't cover everything in the Boston Chris equal in the scores, but it's really huge, but we discussed some really important points. By now, you should have learned the basic concepts off relations at the Bases Boss Idris sequel, including and extolling it on the local machine and stalling it or creating innocents on the cloud. How to work with the databases off the coast of Greece equaled how to work with tables, and we have seen how to do this. We covered some important points, but learning never ends at the end. I hope that you've learned something to you from the scores. I mean that you enjoyed it as much as I did enjoy preventing this scores. There will be no next lecture, and it's time to say goodbye