Database Design Introduction

hello my name is Adam Erickson and this is my
first assignment for Ferris State University’s masters in
information systems management assignment #1 the
video is a brief introduction into database design, I will be
reviewing a simple database I have created and covering database
design standards normalization designs, referential
integrity, indexes and primary keys along with a few other things. The database
is called certification records it is a database that would probably be used by larger companies like Google Intuit, a database where consultants who need certification can have that certification record with human resource so that they can track time with
classes certifications, how much they’ve been
paying it’s pretty much where the database begins and it could be expanded upon in the future, for this assignment only 5 database tables are necessary so I
cut it up short okay let’s start with normalization normalization is a process for
organizing data in a database this includes creating tables establishing
relationships between those tables according to rules designed, both to
protect the data and to make the database more flexible by eliminating two factors redundancy and inconsistent dependency, redundant
data waste disk space and creates maintenance problems if they did that exist in more than one place must be changed the data must be
chasing exactly the same way and all locations, a customers address changes is much easier to implement if that data is stored only the customer
table and nowhere else in the database, there are a
few rules for database normalization each rule is called a normal form – first normal form (1NF) also called atomic and in first normal form
it dictates that for every row by column position in a given
table there exists only one value not an array
of lists of values an example would be right here
where I have city state and zip. if lists of values are stored in a
single column there’s no simple way to manipulate
those values retrieval of data becomes much more
laborious and difficult to generalize those can actually be broke out in a table one column for City one column for state. one column for zip second normal form (2NF) “no repeating groups”again under TrainingCenter_BAD. which is not how you want to name things… we will get to that later. You see I have phone, phone1 phone2 & phone3 if you’re gonna have repeating groups you
should break those off into a separate table maybe just a table called phone. That a
second normal form third normal form (3NF) “no redundancies” everything should depend on the key as it is so also on this bad table I created just
for this example you’ll see we also have certifications and
classes. That is redundant data because we have class table and a certification table, redundant data means if I was to change the certification and the
class I’d also have go into this table and change
it also another obvious example of a dependency is a
calculation column for example now that we have the cost for classes the cost for tests and I wanted to find what the total that consultants has spent on a yearly record for those
two classes and I entered a total column here it would
have to write out total every time I changed costs it
would be redundant you should actually save that for a
query next list discuss indexes, an index is a separate file that is sorted and
contains only the fields you are interested in sorting on, if you create a
an Index on lets say certification name like I have, a SQL database can find
the corresponding record very quickly indexes work in a very similar way to
an index in a book imagine paging through a technical book
on more often a scrambled pile of notes looking for the topic on optimize a SQL database, an
index saves you an immense amount of time. other indexes that I have put are under
consulted name consultant first name and last name,
another ruling is you should not have more than three on a table
because for each time that you write information to that table, if
you had four indexes on top of just the information on the table you
actually have to rate five times and that could start slow the database down
after a while, let’s now go to primary and foreign keys there are two types keys primary and foreign, both of them can be created
when you create the table or after the fact as long as you do not violate
any of the rules say placed in effect, the concept of primary key is
pretty simple it’s a value or set a values that makes a
particular row in a record in a table unique, while working in Vizio everything bold above the first line here with the PK is my primary key an inner joining table like class center here
that joins class and training center uses the primary ID from each table together to form the primary key for class
Center this also breaks the many to many relationship which you cannot, you do not want to have for redundant reasons primary key of a table table has to be unique a primary key can be made up fof several
columns because sometimes one column alone does not make their row unique like classCenter
this type of primary key is also called a composite key because it’s made up of several columns.
While primary key enforces integrity integrity as explained
earlier foreign keys are used to enforce referential
integrity, referential integrity guarantees that references from one table to another ar
enforce by the database and not just in queries from here that could also be enforced by
constraints or triggers another thing that I need to go over with you today is called data types: In a SQL server each column has its own data type is an attribute that specifies the
type of data that object can hold usually it is an entiger, character… it
could be monetary, DateTime, binary and there’s a lot more but we’re not
going to go into that depth right now when 2 expressions that have different
datatypes or length that are combined by an operator the characteristics of those results could be determined, but I
have to go away to far in depth for and Intro to Database Discussion basically if cost here was an integer and cost here was a character that would be like like trying to add a+1 which would equal 2 probably because so
one character, but what you want to try to do is keep it like I have, I use both my cost
fields as decimal visio automatically makes it
10,2 to which is 10 decimals to left and 2 decimals to the
right which can be used for monetary value also 10 is like a million dollars and I don’t
think any class or tests gonna cost that much but
I cannot change the 10 this pretty much sums up data-types,
indexes normalization and primary / foreign keys BAD RECORDING OF VIDEO HERE
example ->CamelCase ThisIsHowCamelCaseWorks BAD RECORDING OF VIDEO HERE Each new word has a capitol letter to start (no spacing) class no space, both cities
capitalized this is referred to as camel case also
on columns same as table names are lower case and the
first character of each word is in uppercase there no spaces between words certification name – capital C
certification Capital N – Name no space in the name today I have shown in over view of relational database theory or normalization, I have tried to
the best I can to explain keys, data-types and index decisions oh well, I haven’t really decided, I haven’t told you why I Chose to index name – FirstName but usually because there quicker to find if you have a problem finding someone
usually ask well what’s your last name then when you
pull up their last name you might find that there’s, my last name
is Erickson, and there might be 4 Erickson’s. So then you ask them for there first and then you may find only 1 Adam Erickson If you need to go more in depth, ask for there birth date or last 4 of there social security number BAD RECORDING AGAIN explaining that this table I’m deleted BAD RECORDING AGAIN to do so I’ll be remove it before finish
this tutorial TrainingCenter Bad also has improper naming conventions the primary key is an address well if the address is a building and there is 4
companies in that building that doesn’t make a good primary key I have
also decided to mess up all three normal
forms inside one table be easier to just get rid of that table and
not have it connected to anything and that concludes my preview of database design Thank you for watching and have a nice day.


Add a Comment

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