Creating Tables and Relationships for Accounting Database Video Lecture

in this lab you will be creating the
Westlake database for the rest of the
research hospital in this lab we are
going to use what we've already done in
the previous labs this is for the group
a labs previously we created the
physical design the physical design will
look very similar to what you see on my
screen now we're going to use that
physical design the one that's provided
for us in the book to create the
database
we'll begin by opening up management
Studio Express if it's not already open
you're going to use the connect object
Explorer to bring up the window to
connect to your database I'm going to
log in to your database I'm gonna be
using my SI account and once logged in
you'll see a tree on the left-hand side
you're going to expand databases you'll
see mine I have no databases but yours
you may have the action works database
I'm going to right click and click on
new database to create my database the
name of my database is Westlake I'm
going to click Add sorry
you clicked add hit remove I'm going to
put Westlake I meant to click ok so I'm
going to click OK when the box goes away
you'll see your Westlake database now
displays under the databases in the
databases tree of course we have no
tables yet so the first thing we'll need
to do is create some tables the first
table we'll create is the doctors table
which here is in the center it says
table 1 which is a mistake from our
textbook but we're going to make that
the doctor's table and that table is
going to contain two columns the doctor
key which will be the primary key and
the doctor name create your table are
going to right click on tables select
new table when this dialog displays
under column name we'll type our column
name so the first column I'm going to
use is dr. E the data type for this
column is going to be n charge 50 and
we're going to uncheck allow Knowles
for the second column we're going to use
doctor name with a data type of bar
chart 50 note that we also uncheck allow
nulls for this column once you completed
this once your table looks similar to
this
obviously here it's called table 1 in
the tab at the top when we hit ctrl s to
save you can give it a new name I'm
going to call mine doctor once that's
saved we can close this tab and if we
right click and refresh you'll now see
the doctor table and if we open up the
tree you'll see the two columns doctor
key and the doctor name but we have to
make doctor key the primary key to do
this we can right click on doctor and
click to go to design mode this is how
we can edit a table after we've already
created it so if I right-click on doctor
key I can hit select primary and it is
now the primary key I should be able to
hit ctrl s to save that table it's now
saved and I can close it when I right
click and refresh when I look at my
columns you'll see that the doctor key
column now has the icon for the key next
we're going to create the patient's
table
note that the patient's table has both a
primary key and a foreign key the
patient key column is the primary key
and the doctor key column will be the
foreign key so let's make this table
we'll do this in the same way we're
going to right click on tables or create
a new table and we'll fill in these
columns so note the data types some of
the data types are n char and some are
varchar' it's important to note the
difference between n chart and varchar'
especially how it will store that data
in the database this would be a good
discussion point under the allowed nulls
presumably the middle name and the phone
number could be optional in this
database so we allow Knowles so if
someone does not insert a value they're
still able to create a record in that
table
we also need to make the patient key the
primary key so we're going to right
click and hit set primary we're now
ready to save this table I'm going to
hit ctrl s I'm going to call this table
patient you can see in my refresh list
I now have both a doctor and a patient
table I'm going to expand the patient
table so you can see the column names
this would be a good point to pause to
make sure that your column names are the
same as my column names if you need to
make a change to your table simply right
click click on design and you can make
your changes it's important to note that
if you try to make changes after you've
added table or added data to your table
it may not be able to save so it's
important to get this correct although
in many cases you can make edits by
using the sequel command line next we'll
create the drop two patient's table and
drop the patient's table is going to
have three columns so make sure that
your table looks similar to the table
that I've created here and when you're
done you'll save this table and we're
going to call it drop patients
up to this point we've used the
graphical user interface to create our
tables however we can also create tables
using sequel to do that you're going to
click on the button at the top that says
new query you should have a new query
button in some cases you can right-click
and click new query on your database
so I'm going to click the button for a
new query and in this window I can type
my sequel command so for example here we
have create table table name is groups
the first column is group key which is
the primary key and the second column is
group description which cannot be known
so you can type the same command on your
sequel console and once you're ready
you'll click the button at the top that
says executed if you want to check for
errors first you can click the parse
button to check box it will verify you
have no errors in your sequel syntax and
then once you're ready click on execute
and you'll see that the command is
completed successfully to verify you've
created the table you can see here that
we do not have a groups table but if I
right click and refresh and now have my
groups table and it contains the two
columns the group key and the group
description pause the video and do the
same command in your database finally
the last table we need to create is the
patient groups which is a linking table
so we have the patient's table and a
groups table since many patients can be
in many groups we have to use a linking
table to connect those two tables which
we've talked about in class in order to
do that we're going to create our new
table but before we do that we need to
take a look at the columns that we have
under patient groups we have to think
about the foreign keys notice that the
primary key for groups is the group key
and the primary key for patient is the
patient key and the data types for both
those is n chart in my groups table I've
made an error with the data type or the
group key as an N chart but it's only
five characters rather than 15 so I'm
going to update mine to be 15 characters
so I'm going to right click on groups go
to design mode and I'm going to edit
this to 15 characters when I try to save
it says that saving changes is not
permitted so you can't save changes
using the graphical user interface so
we're going to have to make this change
using sequel so back to my screen where
I have my create table statement I'm
going to add to this statement a drop so
I'm going to type drop table groups and
this will first drop the table I'm going
to make sure that that happens first by
typing the go command so it's going to
drop the table groups next I'm going to
go to the typo from our textbook where
they had n char 5 and a change at the 15
which is the intended value and type go
after that so it just created our first
sequel script that will first drop the
group's table then it's going to create
a table again calling it groups but this
time with an N char of 15 the length of
15 it's important that this be set to 15
since the primary key in this table is
going to also be a foreign key in
another table and we want both of those
to be the same length I'm fairly certain
that this is a typo in the book so we're
going to make this change so once we
made those changes to this script and
we're going to click on execute and it
should complete the statement now when I
right click click on my groups table and
refresh and now have an n char 15 rather
than an n char 5 pause the video write
that script and execute it we're now
ready to create our patient group
assignment so our patient group
assignment is going to have a patient
key and the group key the patient key is
going to be the primary key
if you're going to use the GUI to create
this table with a right-click on table
click on new table I'm going to type in
my two column names so once you've typed
these two group names don't forget to
right-click and set your primary key for
the patient key which should match our
diagram which shows the patient key is
the primary key or create the barn keys
in just a few minutes to pause the video
and type in these column names and then
we'll save this so to save this I'm
going to ctrl s and I'm going to call it
patient group assignment now when I
right-click and refresh my tables I have
my doctor drop patient groups and
patient tables we're now ready to do our
database diagram so under your westlake
database the first object the first
folder you should see in the tree is
going to be database diagrams you're
going to right-click and click new
database diagram now I have the add
table dialog on some of your database
engines it's going to tell you that
you're lacking certain components in
order to create the database diagram
just click yes and then you should get
this dialog once this dialog appears
we're going to add all of these tables
to our diagram so if you keep clicking
add you'll have all of your database
tables in the diagram and you'll be able
to arrange these tables by clicking and
dragging them on the screen if some of
the tables are off-screen you may have
to scroll over I'm going to scroll back
and then we're going to arrange these
tables similar for the way they're
arranged in the book well take a moment
to arrange your tables similar to how
you see it on my screen you can pause
the video and once you have your tables
arranged in the same way and we'll
create our relationships so the database
diagram utility allows us to create our
foreign key relationships so the
database engine knows
how these tables relate to one another
it's a graphical utility so we can click
for example on the doctor key and then
we can drag it to the table to which
it's related in this case my doctor
relates to the patient on the doctor key
when you release when you release it
we'll see this dialog box you'll select
your table and then the keys so in my
doctor table it is the doctor key and my
foreign cable table it is also the
doctor key so we're going to have the
doctor key on both sides now notice on
the left-hand side over here I have my
doctor key and the doctor table has an
inch are 50 which is an error that I
made when creating that table where my
patient tables doctor key is an inch are
15 and if you recall if our primary key
and firing Keys don't match data types
and we cannot create a foreign key one
is an N char and the other one is a var
chart so I have to modify the data type
on the doctor key and the doctor key on
the patient level I have to make sure
both of the same data type and the same
length so I need to change the length of
the doctor key and the doctor table to
15 as close to 15 so to do that I'm
going to cancel these windows let's show
you a little trick if I right-click on a
table you can click script table as
create two new query window so this will
automatically create a database script
to generate that table so let's try that
so again you're going to right-click on
doctor script table as create two new
query window now you can see that this
query looks much more complicated than
the previous query the first section
here tells sequel to use the westlake
database so make sure that we're in the
correct database there's some things
going on here that we really don't need
and since we're already in the westlake
database we can delete all that content
down here this is a different way to
create a primary key the way we did it
when we wrote the script
is much simpler but this is really the
correct way to do it so we're going to
leave that part we also don't need this
padding on the bottom so we'll get rid
of that so this is the database script
to generate our database the change we
want to make is to edit the doctor key
to be only 15 characters as opposed to
50 if you recall in order to get rid of
that table we can use drop table doctor
type go to make sure that that happens
first
this should drop the table first and
then recreate it within n char 15 for
the doctor key field I'm going to click
execute man completed successfully I
right click on doctor refresh and now
have the correct length it's important
to note that if you did this with data
in the table all of your data would be
lost so this is convenient while we're
trying to design our database we would
not want to do this after our database
is in production so it's really
important to get to design correct
before you begin using the database AHS
the video for a few minutes and do the
same procedure I did to drop and
recreate your table with the correct
length for that field in order to create
this foreign key relationship since the
properties of this table are cached in
the database diagram tab we have to
close that tab and then we're going to
reopen it by hitting new database
diagram once again we have to add all of
our tables and rearrange them similar to
the textbook now in my database in order
for me to create that relationship
because it cached the previous column
data types from when I created the
database diagram before I actually had
to disconnect by clicking the button in
the object Explorer to disconnect and
then reconnect to my database you may
have to do the same thing if so once you
reconnect we're going to go back to
database diagrams but new database
diagram add our tables and arrange them
as in the textbook
take a moment to make sure that your
screen is just as mine and once you're
ready we're going to click on the key
for doctor key we're going to drag it to
the doctor key in the patient table if
you're careful to drag to the same
column name it will automatically select
both in this dialog and then once you
click OK it'll create your relationship
once that relationship is created you're
going to create the rest of your
relationships so I'm going to use my
drop two patient and drag the patient
key to the patient key click OK
I now have a one-to-one relationship
between patient and dropped patient
using the patient key in both tables and
do the same thing for the patient group
assignment we'll drag that patient key
to the patient key ok and then finally
the group key to the group assignment
and then I'll click OK
take a moment to make those
relationships just like I did once
you've completed your diagram we're
going to save it I'm going to hit ctrl s
to save it on my system I'm just going
to leave it as diagram 0 and click OK so
it's letting me know that it's going to
actually change those tables I'm going
to click yes you can see the asterisk is
now gone so it's saved that relationship
and let's take a look at our tables so
you'll note in your patient table once
you refresh in your columns you'll see
that there's a now a gray key next to
the doctor key indicating that that is a
foreign key and it adds the foreign key
identifier so we now have a foreign key
in the patient table it also sees that
we have a foreign key our patient key is
not only the primary key but also the
foreign key and all of this is going to
match with what we see in our database
diagram once you've completed this we
now have a physical database design we
have to do two things one we have to
create some data it's in order to create
some data one easy way to do that we
haven't learned how to do insert
statements yet so we can use the
graphical user interface to do this if
you right click on a table and click
Edit top 200 rows you can type values in
for each field I've added several
records to my doctor table you could do
the same on yours notice that there's a
red circle with an exclamation point
that appears next to my record in the
fourth row the way this works is this
record what that's telling me is this
record has not been inserted to the
database until I click the next row it
does not commit that row each time you
put a new row in as you click to the
next row it will commit the previous row
so now that I've added this data to my
database if I close it when I right
click on dr. and just say select top
1,000 rows you can see that that data is
in my database now this is an example of
a select statement which we're going to
learn about in
next lesson it's not important to
understand it now but it is interesting
to see it and we'll use it later on you
can now proceed to create test data for
the rest of your tables for example the
patient table right click go to edit top
200 rows so you'll notice when you try
to insert a patient record we have a
foreign key constraint violation in
order to continue we're going to have to
hit escape so escape will abort this
insert so you hit escape a few times
you'll have nothing inserted and you'll
be able to close that table so we can
correct the problem so to make this
change we're going to click on database
diagrams here's the saved diagram we
created earlier I'm going to right click
click on modify so here's my diagram so
you'll notice that there are two
relationships that may be an issue here
we have the relationship between patient
and drop to patient and a relationship
between patient and patient groups
assignment because we have a foreign key
and a primary key on both sides of the
relationship and both our primary keys
imagine we try to create a patient
record but that patient key does not
exist in the drop patient record right
now our database is going to try to
enforce that that key exists on both
sides which means I won't be able to
insert a record to drop patient or
patient unless the other record exists
so we have to change this relationship
same goes for the patient group
assignment so to fix this I'm going to
click on the relationship between
patient and drop patient I'm going to
right click and go to properties you'll
see an option that says enforce foreign
key constraint I'm going to say no so
will not enforce the constraint I'm
going to do the same thing for patient
to patient groups assignment I'm going
to select no you'll see that there's an
asterisk now next to patient drop
patient and patient groups assignment or
it's going to modify the schema and all
three of those we save our diagram so
I'm going to hit control s verify that I
want to change these tables I'm going to
say yes now I'm going to close my
I agree going back to my patient record
I'm going to click on edit top 200 rows
I'm going to create a record here so
here's a test record that I'm going to
insert and of course it's really hard to
test our database without some data in
the database we're only going to create
a few records in this lab but in the
real world we would use tests they'd
test sets to create our database we
contain many different names we do some
funds testing with our fuzz testing with
those so it would be important to have a
good good set of records to testing but
for now we're just going to use this
edit option and paste some things in
here or type some things in here now I'm
trying to add a record to this patient
table I've just typed in some columns
some values for each of these columns or
fields I'm going to click the next row
which should commit that to the database
but again an error message and if you
take a look at the error message it says
that there's a foreign key constraint
with patient-doctor it says basically
that the column doctor key is violating
the key constraint so if you think about
it I'm trying to add a patient with a
doctor key or a doctor that does not
exist that's why we added the doctors
first you can't have patients without
doctors so I have to use a key that
existed in the other table and I happen
to know that I used the key one two
three four one of my doctors that was
dr. Jones
so this patient's doctor is dr. Jones
now click the next column and it commits
the record now in this next record that
I try to commit notice that I left the
middle name null I didn't type a value
in for the middle name but when we
created that table I'll show you over
here on the left if we look at the
middle name it allows null so we should
be able to commit this record and it
works so use your system to create a few
test records we're going to use these
test records later on I've added these
two records to my groups table I've
each of my patients to a group using the
group assignment linking table so one -
24 is in Group one patient 513 is in
Group one and patient 415 is in group
two I've also used the drop patient
table to drop this is effectively going
to remove 4 - 15 patient for 15 from the
study even though he still appears in
the patient database in the patient
table rather this indicates that he's
been dropped from the study on January
1st 2016 for high blood pressure to
complete this lab you're going to want
to open up your diagram again he'll take
a screen capture of the diagram this
will be part 1 of your submission and
part 2 is you're going to create a
script for your database so if you right
click on your database name select
script database as create two new query
window this is going to generate a
script that would create your new
database now unlike previously when we
scripted our tables where it would
create the table this only creates the
database this would not actually create
all the tables so it's important to note
that if you you can't really backup a
database by right-clicking and doing a
script - however you could right-click
and do a backup I would recommend at the
conclusion of each of these labs that
you right click go to tasks and create a
backup of your database right click and
go to back up filing dialog will open up
and you can create a full backup to disk
so I usually do a full backup to disk
and click OK and it would create a
backup that's one way to save the
database you can also right-click go to
tasks and detach the database this will
remove this database from your database
and you can copy the file separately and
store it separately in case you need to
use this database again in which case
you would simply right-click go to tasks
you would attach the new
database you do them in their database
and you can attach a new database just
by finding the MDF file for this lab
submission you can create this database
script and simply turn this in along
with the screenshot of your database
relationships in lab 5.2 B or lab 5.2
for Group B which is Bill wait the
second half the group B's you're going
to create a database on your own but
you're going to use your own entity
relationship diagram that you developed
previously so you would have developed
an ER D and then in lab 4.2 you took
your er D and created a physical design
very similar to the one that we worked
with in this lab so you'll have a design
similar to this one for the grande field
college so you're going to go through
the same steps using your physical
design your diagram you're going to
create your tables you're going to make
sure you create your foreign keys make
sure you're cognizant of the data types
for each one of those columns make sure
they match where you know you're going
to have a foreign key relationship and
then you're going to create the
relationships in the database diagram
tool you're also going to want to put
some test data into your database so
you're going to follow the same steps
that you did for this database but
rather than telling you which columns to
use and I'm showing you step by step
you're going to do this on your own so
that's going to be lab 5.2 you should
turn in the same thing that you turned
in for this lab so you'll turn in your
create database statement as well as the
database diagram from sequel so you'll
take a screenshot if you have any
questions please let me know and good
luck with the lab