okay uh so this is our second lab
and in this lab we are going to use the
er diagram
or the er model uh to help us design
tables that can be used in
most popular relational database
specifically we're going to create
several tables that we're going to link
those tables together
so i can define relationships and
by using your diagram and er diagram
we are also can be converted into sql
code
that can be executed in most relational
database
so in this uh lab we are going to use
this online
website that you can generate my model
so which
is at least has a free
version that we can use for our lab and
there are also other tools that
are available um and also some are free
and
some are not free but i've i found that
this one worked pretty well for me so
if this is our first time to use that
one so you may need to sign up
an account or if you have
an existing
google account or github account so you
can also log in with your google account
or github account so here i'm going to
use
login with my google account
alright so here you can see i logged in
with my google account
and you can see my previous projects
so i'm going to start a new project
and for this new project so you can see
you can create different type of the
project
so for this one i'm going to create a
project for the relational database
so rds
okay um and i'm going to create a blank
project so i will
call it lab2
so i think you i might be wrong but i
think you have to put that one as
public otherwise
it will not be free so i i just put it
as public
and then i hit create
again there's a concise version of this
tutorial on my youtube
channel so if you want to follow that
one so feel free to go to my youtube
channel and check that
one that is faster but here i'm going to
do step by step so here i have my
project that created
that is for rds
and then i'm going to open the editor
so we can do everything online
all right so here you can see uh i'm not
on the diagram page
so everything is empty uh
so here you can see you can add tables
and you can add
columns to the tables and you can link
different tables
uh by adding reference uh so let's say i
want at
my first table and for this table
so if you double click you can define
the table names our first table will be
professor
and i will add in the i will now add the
columns so you can drag
columns by default that will be our
primary key
but the primary key i will call it email
all our coins uh professor email so i
will call p
email
um and for the date type
so right now you can see the properties
is
uh open see here you can see the
property channel so we're
here you can check uh div at different
constraints so
like whether or not you want allow it to
be noun value
do you want to add some chat like we did
in the first lab but instead
here we are doing it in this er diagram
do you want that to be unique and do you
want to set a default value
so here since the primary key
uh is a the professor email so i think
you don't need to check the others
because it is already and primary key
and for the date type because i want to
use
the the email so i think the chart
will be the appropriate one and you can
guess
give it a good size so here just
type 50. okay so that is my first one
and let me continue add the second one
so
the second one i will call it professor
name
um and
i'll give it a a little bit longer
size um and
i don't think it can be noun
and i don't think it should be unique so
i will just
uncheck the nullable and also unique
and i don't think he should have a
default value
and let me continue office number
okay so this will be
office numbers i just call it office
um and also character
and i think this one can be short so i
just give
10 and i think that can be numb because
some professors you may not be able to
find out their
office information okay so that's
my professor table
and let me go ahead add my second one so
that will be
the cost table
and for the cost table we will have
the cost number
okay and the cost number will be a
character
and so i'll give it let's see
about 10 character um
and that is the primary key
and the second one will be c name
and also that is a character
okay uh i guess the varying character
will be
better actually so
i'll call it 20 um
yeah i will change all the answers as a
character varying
because then uh so um
in that case if you have say less than
the size so um the outpost jerry circle
will not fail in spaces so
yeah it's interesting i didn't see that
one here okay
and kept varying character um
and also room number so
room number
and i think 10 will be fine
okay
okay and then i will add a foreign key
so
the following means that each class has
one unique professor
uh so to make sure that um the form key
has the same data type
so i just simply can copy this one
this column from the professor table and
then i just paste
okay and then i uncheck this one
okay so i on time so the the pro
professor email will
not be a primary key on this table okay
and also you can decide whether or not
this is nullable so i
i think i will not check that one
because
each course must has one
professor okay and next i'm going to
create the student table
so student
and the first will be a student name
uh sorry the student id so student
student email actually
so as email
and that will be also a very a varying
character
and i gave it 50.
and the student name
and the varying character and i gave it
100
um and also student major so
because student major is is only column
that called major on student table so
i've just called
major um the type i just
called varying character and the major i
think can be very short
okay so that is for the student table
um and as i said that student
in the relationship between students and
those courses are many too many
so to enable to handle that one we
need to have um
an enroll table so a correlation table
to resolve
that many-to-many relationship so for
this table i called enrollees
and i would not leave any i would not
have any spaces on the table names or
answer
column name so instead i just use n
score to separate those letters
so i copies student email
to this table
and this is not a primary key
and i also copy the cost number
and this is not a primary key okay
however those two columns
together will be the primary key
so i'm not sure that that delete was
necessary or not but yeah just select
both together and
select both as a primary key
okay so here you can see we have four
tables
and you can also add index so you can
see which one do you want using adding
that
will be necessary
let's say we add an index
to a column
let's say we we won't check a student
email a lot
okay so we add that one
okay so this student in release has an
index to student email
and let's say we want also
for the cost we also want to add index
and that index can be added to the
let's see uh the uh
the um the room let's see we won't check
because we want to find out the room
allowed um
you can also add more indexes
so if you like again remember that
adding more index will also
slow down the speed of insert and also
delete update etc so you just you should
only add index
uh to the columns that you need
okay next uh we're going to
add the reference or we're going to add
relationships
okay so the first is course and
professor so we already have the foreign
key here
so one course okay
uh we have multiple professors
one course can only have one professor
and one professor can teach multiple
courses
okay and now you can see that this is
mining to one relationship and you can
see the p email on the course now has
also a k icon so that means this is
a foreign key let's also add one more
constraint so let's say that
all the offices should be unique
okay so let's also maintain that
constraint
and similarly we can link
the relationship between enrollees and
now
student table and also enroll list and
also cost table
so let's say student
okay and also course
all right okay so this is the er diagram
that you should submit
in your lab report um
you can see here we have four tables and
we have all the
required attributes
other um and also we have all the
necessary constraints
and also we also define the the
appropriate
data type so i would i prefer using
varying characters so that you don't
need
you don't you will not have the
unnecessary space
okay uh so before we move on let's make
sure that our model is saved
so um
okay it looks like they automatically
saved okay so that's great
next let's say we're going to so we have
those
er diagrams so we're going to generate
an a a piece of sql
code that can create those tables in our
database
folders so let's go to the generator
and we choose uh my generators i
remember
oh no we have used the same
sample generators and we are going to
generate the
for s database sql
and you can see posterior circle is one
that
is supported so let's write
those code are not the sql code those
code are just a code that on the website
that used folder
okay so let's write
okay and that will be saved as
unzip file so you can save that one and
you can unzip that
okay uh so here i'm in my download
folder
and you can see it's in a zip file so
uh you can use like here i'm using 7-zip
to extract that folder
and here you can see we have an sql code
and actually you can view that sql code
okay so here this is an sql code
and we'll talk about sql code
in the next week so um i think but
it's pretty straightforward so you can
create table if not exist
uh you can create table if not exist so
cost table and also
here we are going to define those
columns and also we'll
tell which is the primary key
and also we also see okay some tables
are should
some columns should be not noun and some
should be
unique okay um
and and also you can see here we can
link those tables by creating those
by creating those foreign keys and also
we can create those indexes
okay so that is the sql code that's
pretty neat
um you can still go back and also change
your diagram if
if you think something that you are not
happy with
and then you can regenerate the code
those sql code
so you can download that one
so if you have problems in
opening that zip file select and also
view those as a qr code you can also
just preview the code here
okay and so you can see it's pretty nice
that you can see those code that
is already here on the right side okay
so i'm going to use a code that in the
preview
and i will copy those code and i'm going
to run those code
in postgre circle so in the database
so i'm going to switch to the pg admin
okay so that's the uh
gui that can control and execute
different
uh sql
code and also connect to our database so
you can just follow the same
instruction that as we did in the last
week to go to your pg admin
um and then i will
open if i will choose this uh uh
the schema that is assigned to my
account
and llc okay i will try to run some sql
code okay it's in the tool and also it's
query tool
okay so this is the query editor so i
just simply paste
the sql code that i copied uh
from the website in the preview
and i paste it here okay
and then i just try to write
okay uh so here i have an error because
i already have an
index okay
okay i think this is a bug that's
something that
the website can create so here you can
see we are going to create
two index with the same index name that
is actually not
allowed so that for the first index i'm
going to call that
cos
room index and for the second index
i'm going to call that
enroll list student
email index okay and now let's try
try to write again
okay so now it's success and now you can
see if i refresh my tables
wow okay so i have i have the cost table
okay uh you can see you have those
um columns that has been created
it with exactly the same names and also
the
size and also we have the enroll list
okay so you can see the columns and also
see if the constraints the foreign keys
primary keys
and also the index okay so we do have
index that's not created
and also for the professors okay so the
professor email
name and also the constraints
and also uh i don't think that we have
index no we don't have index on that one
and also student table okay the student
name major
and also you can check the constraints
uh okay and those in-depth if you have
okay so i think the next step is that
you need to fill in
some records for each of the table and
also remember that you should save your
table
or you don't just don't delete your
tables and we will use those tables in
the next week
so for example for the cost table
or here for the professor table so you
can type
for the course table you can type see
the cost number so
i 340
and course name it is called date
mining
the room uh it is online
and the professor email okay so this is
the tricky part
okay uh so i should not do that in this
way but
okay since i already did we we are going
to have an
error so the reason we are going to have
an error is because
uh remember that you have to have
that professor before you can create
this table
so if now we try to save it
we have this error because we don't have
a professor that have this email
so that's why we cannot save it
so the right sequence is that we have
go to the professor table first
okay and we have the professor
okay because that is a one-to-many
relationship
okay uh my office
all right so now i save this one so
once we do have that professor
and you go to that class and now you
save it
so now you can see it has been saved
okay
because if you recall our model
you do need a professor that to teach
that class
okay so that's why we do have the we
have we need to have a record in the
professor table
and then we can create the class that
that professor is teaching
and that is the same story for the
student table and also enroll list
so we we make sure we do have need to
have
a cost table and a record in the cost
table and also record in the student
table
and then we can create that enroll list
so
my student table we need to create some
dummy variables
so s1 at
jmu.edu
the cname is s1 the major is
intelligence analysis
okay and then we create we fill in the
enroll list
okay so in the enrollees we
must have that student
and we also must to have that class
okay otherwise we will have errors
okay so now we have successfully
uh type some records in those tables so
uh you can try type more records
and also you can try to say okay so
remember those constraints that we set
up and see if those constraints will
also work for you
