Relational Database Determinants and Keys

OK hello welcome to COP4708 in this session actually in the last session we talked
about entities and that each entity has more than one attribute so for example if we are talking about like a student
and then we are talking about the name of the student maybe the
street address and maybe and the email the City state zip code and so on and all these attributes can form what’s called a record usually we have and many records in the same entity or for the same
entity and all these records can be saved in what’s called a relation again for example we have the student
and the attributes could be the name street address City state zip code email and phone number this
could be also for a patient this could be also for employee so the
same information could be collected for up more than one entity. Other
entities could mean like airplane airplane is entity and I can
gather the attributes for the airplane or a car and I can and other the
information for the car such as them model and name model year and the maker so we can collect many
information about the entity the question is how can we reach and retrieve a record from and these entities so if I want specific record such as looking for John as student or a maybe looking for Mike as a student how can I get the information just for Mike or just for John I need to search within those records and if I do that Oh WOW that could bring for us hundreds maybe
thousands of students with the first name of john how about the last name we will still have hundreds of the same last name or at least few of them with same last name so we need to search for an attribute that can pull the required records and determine all the information needed and
this attribute has to be unique so when I use it it
pull only one unique record and we can call that as determinant or identify it because it will determine
only that record so if you call it identifier or determinant than it should
be unique and can determine other attributes or columns in the relation for example I can use the student email which should be unique to pull all the information related to a
certain student so now this one is my identifier or determinant this attribute actually or this determinant can be considered as a key so it will be the key that opens the door for all other information related to that entity it’s considered the main key or what we are going to call
by the primary key so this is the primary key for the relation the entity that
determines other attributes I mean the attribute
that determines other attributes in the relation it’s called primary key which bring us to the definition of keys and how keys work so keys could be a combination of one or more
columns that is used to identify rows in a relation for example social security
number it can determine the information of a
person the name email the address other
information for that person and that’s only one the combination of maybe first name last name could determine other information although you know you can find it’s not unique and you can find other people the same
first name last name so we should find a different
combination that can determine other attributes for that relation if we use more than one column to determine the other than
we call it composite key, composite key so the composite key composes or
consist of two or more columns we can analyze the
different attributes and nominate some keys that
could be candidates to be a primary key so we
can look at the relation to look at the different columns within that relation and we can see which one is a candidate key to be a determinant for that relation although
those those candidate keys might not be used but they are
considered as candidates to be one of the primary keys so the primary key is a candidate key that is selected as the primary means of identifying rows like we said it’s the key
that opens the door in a relation to other information within that
relation to the other columns or the other
attributes there’s one and only one primary key per
relation we are not allowed to add more than one
and I think from your experience now with
Access and the data that we developed in Access
database you can see that you are not allowed to
use more than one key the primary key maybe a composite key and the ideal primary key is short
numeric and never changes such as social
security number and maybe student ID an employee ID and patient ID and so on and we’re not
talking about only humans because we can say like we said car and the car has VIN
number and the VIN number can be unique we have airplanes we have products
and product that has SKU number order number invoice number all
these are numbers that are unique and can be used as identifier or determinant and can be
called as primary key now going back to our first example we notice that we have the attribute
name street address city state zip code email and phone number that’s what you
know I said and the first slide now the email is considered a candidate key
and can be used has a primary key but is it the ideal key to use or to be the primary key it’s not ideal because its long and it
might have symbols it’s a easy to make mistakes in that one and by doing mistakes than you will not be able to match the different relations so the best option since you know I don’t have any anything that can determine that record or a unique record then I can add another column that is numeric and can determine other attributes such as and for example
here entity ID their additional column would be considered a primary key but
it’s an artificial a primary key is not natural it
does not include is not included within the information
and since I added that as artificial primary key
we call it has surrogate key so the surrogate key is
an artificial column that’s been added just to be the determinant and to be
the primary key for the relation or the database in this
case you see later on that we are not only
dealing with one relation or with one table actually will be dealing with more than one table and those tables will be
connected together they will have some sort of
relationship and we talked about the relationship and the relational database model and we said that it might have one to one or one to many, or many to one, or many to many relationship so we introduced the relationship before and we know that we
would have more than one table or more than one relation how would I
connect two tables together how would I know
that from the information student
information as a table and course information as
another table that student John is taking these type of courses or how can I map these courses to student John since I
have two tables or two relations then at least one of
the columns has to be common by the two since I have student ID as a primary key in
the first table or first relation which is
primary key I need to send an ambassador for that primary key to the other relation since I’m sending that primary key also in the course
column or the course relation then it will be foreign key so the student ID is a primary key in the Student
Information table or relation at the same time it’s listed as an attribute in the course table since it’s listed as an attribute it’s not a primary key
because I cannot use the same primary key for the two tables in this case it’s called foreign key foreign key is the primary key up another elation that’s placed in that table to establish the relationship between the
two tables it is an attribute that is refereed to in another relation and it has the same values but it can create a relationship between the two tables and
we take examples as we go later on I was show you examples on two relations or many relations and how we are using the primary key as foreign key in that second table since we
are using the same column in two tables the same column which is
student ID in this case in two tables the table for student information and the table
for course information I have to enforce what’s called referential integrity constraints so when creating a relationship
between the two tables we have to make sure of this referential integrity constraint which is constraint that limits the value of the
foreign key to those already existing as a primary
key values in the corresponding relation for example if John is student listed in the information table entity and he has student ID for example 474 808 then I have to make sure that the same ID will be in the course table because John is taking these courses so I will point that student ID 474808 is taking those scores taking these courses and that’s to make sure that this person is taking these courses again I have to enforce that referential integrity constraint to make sure of the consistency of the data between
the different relations and we will see more than one
example later on when we start building those tables and building these relations so now we
talked about the keys we have the entities attributes and
we selected one of the attributes as one of the key is that will open the door or determine other attributes the
next session will be talking about functional
dependencies that would be all for this session thank you
and have a great day

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *