9 hours 41 minutes
I will go to model for this is less than 4.3 implement models to In this lesson we're gonna review are updated er de diagram. I've added a building table to the diagram. This improves room management because the building can have many rooms, and then we can just have the address of the building once
on the building table. I've also added column names to the E R D diagram. I have also added deleted at calm to course and user. This is a column that will help protect against accidental deletion. When the user first attempts to delete a course or a user, the deleted at column fills in,
so the record is still there. And if we really need to delete the record, then we can detect that the deleted at columnist filled in and then actually delete the record. And then we could restore a record by removing the deleted at date data.
So we also need to implement the columns and run the migration. So let's take a quick look at that E R D diagram.
Okay, here's the updated er de diagram that I created. This will be available for download from the resource is. But let's step through some of the things that we are seeing on this diagram that weren't there before.
I've added the column names to many of the tables. Now we are using string a lot. You wouldn't necessarily have to do that.
For example, you might use a manager for the phone number and for the ZIP code, but we're just keeping it a little bit easy here and just using string. And many of the instances
I've added he created out updated that
and the deleted at column on both the user and the course. So what this will help with is avoid an accidental dilation. For example, if someone deletes a user, the record at first will get a date time stamp and then queries that query out. Active records
will require that
column to be no.
Then you can go to a trash area where you can delete the user or the course again. So you effectively create a scenario in which a front end user has to delete the record twice, which helps affirm that that's really what they want to do and not a one time accident.
Another item added to this year. D diagram is a relationship between the great and the course, so we can tell what
course the great belong to.
something to note on this er de diagram is that this is the name of the relationship. For example, grades is not. A column is just indicating that the course has a great relationship.
So let's go ahead and get started with implementing these migrations.
All right, so let's get started. We need to add this building model and migration. So I'm in a shells session with my Bagram machines. Someone hit up,
go to some of my previous commands,
have a make model. So I'm gonna change my last one, which was room to building
and hit. Enter
next. I'm gonna go into the folder where the stuff is
And again it's being generated on the virtual machine in the home vagrant level directory. And that's being sink to my shared directory on my host.
So changed changes that occur and one occurring the other as well.
So let's go to my migrations. Let's go and start filling in our columns.
So let's make sure we have all our users,
all our user data actress one I just to city state zip code
created at Deleted at
to add the deleted at column. We're going to use a
migration command table,
and that's gonna add the deleted at column that we need.
So that appears to be everything we need in the user's table. So let's go ahead and make sure everything's in the grades table that we need.
Be sure to save
you, forget to save. Then, of course, when you migrate, the change won't happen.
So the grades we have the time stamps we have, which will be the time since will be the created and the updated columns.
You have great score,
right? Yeah, a great score letter. Great. So that one's good to go.
Let's open our user types
and our usual type. We need a title
on. We need a
and we have the time steps. So that finishes that one again. Remember, like this user courses is the name of a relationship and not a column.
So let's go ahead and move on to the next one,
which is the user course,
and we need the
and that's gonna be
user. I D
and we need to make sure it's unsigned
because the big increments that air on all of these tables are unsigned. And to make constraints against data types they have or against columns on different tables. They have to be the same data type.
So type I d
on. We're gonna need course I d So I'm just gonna copy this and start pasting
and then we have the time stamps, so that's good to go
Now. Next, let's move on to the next table.
The courses table.
Andi, this one's got a few things on it.
So we need a
gonna pace the big manager from the,
clipboard that have previously. And I'm gonna to change this through room idea.
I don't need to copy again
check this one out table.
I'm gonna require the course code is unique
because we can't have duplicates of the course.
the way that would probably work is that if you had, for example, multiple biology courses, they're the same course, but they would have different course codes,
and that's how we would keep the same course. We would differentiate between them even though they represent the same course,
title doesn't have to be unique.
And some of that just requires knowledge of the domain that you're working in, which you would either understand or someone would make that a requirement for you.
So we're headed to make the max students and imager,
and that can't be
negative. So just make it unsigned, which means positive numbers on Lee, but supports a larger positive number.
And men students were gonna do the same thing.
Also going make the title knowable. And what that means is that it doesn't have to be filled in,
but we're not gonna do the same for the course code. So the course code does have to be filled in,
and now we need
Now we need a start date and then date. So I'm gonna do
start date. So the date the course starts
on a table
and the date
don't forget my semi colons
and the grades is representing a relationship to the great symbols who are not going. Teoh put a column for that.
So we had the created at an up dated at which is created by the time stamps, so we need to delete it. So we're going to table. Soft Deletes.
Okay, I blew. That finishes that one with you on to the next table, which looks like it's going to be the rooms table
Onda way. Have a couple thing on a couple things on here.
actually, no, we don't want that one. So it's a table
You that in the quotes, though, we don't get the syntax correctly. It will, of course, crash.
and kind of juror
So again, it can't be negative number.
That doesn't make sense.
So I'm just gonna go ahead and make it unsigned. Supports a higher positive number that a regular imager
and building I d
wouldn't need that one.
That needs to be a
You spell that right?
Building I d.
And again, unsigned.
And we also have the time stamps there for the created at and updated that combs. So we're going and save that. Let's move on to the next table.
The building table. So we have a couple things on there,
Yeah, let's go ahead and
address as to City State zip
names. At one
rooms represents a relationship and the created it created at an updated that will be
created with the time stamps.
So it looks like we have everything in here.
Let's go ahead and run this migration and see if we can't close this lesson down. So
peace pr. Titian,
my great fresh.
Okay, we have a mistake somewhere.
Let's take a look at the air message and see if we can identify where that is at.
Okay, so I can very quickly see what the problem is. I spelled soft wrong on one of the previous tables, so I need to go back.
There's the problem right there.
And that t And there the correct spelling
hit up on my keyboard to go to the last command rerun The fresh command.
Okay, it looks like it's all successful. I'm gonna bring up Dee Beaver. Take a quick peek
with a refresh
and let's go ahead and peek into one. Let's do courses.
Almost like all the columns air there with the right data types.
Okay, so let's head over to the review and closed the lesson out.
So that brings us to the summary. And what did we do in this lesson? What we reviewed and updated our e R D diagram, we added a building table to improve our room management for the database architecture. We added column names to the RD diagram so we could see all the column names represented on the tables.
We added a deleted at column to the course and user to protect against accidental deletion.
Once we reviewed the RD diagram, we implemented all those columns in our migration files. And then we ran the migration. So that completes this lesson, and I hope to see you in the next Thank you.
The Microsoft Azure Test is a premium Cybrary assessment created by iMocha. The exam will ...
AWS Data Engineering
The AWS Data Engineering test is a premium Cybrary assessment test created by Interview Mocha. ...