Hello All

Let's start off by talking about what a database is and how it would be efficient in using it. It wasn't until about a year and half ago now that I was first introduced to this new age of Database. I discovered a lot of neat things and even learned about databases the very long way.

A database is exactly how it sounds its data that has a base. The base being the place where you store all your data. If you have ever been to a library perhaps you have used the Index to search for an author, this is a database of authors. Ever seen a rolodex on someone desk, imagine multiple rolodex’s that were huddled together and were joined together by links of information. Simple it sounds but there is a lot more to it than that, but once you learn the simple stuff it makes a hell of a lot more sense. A phone book is a database of phone numbers; stored alphabetically by last name. You can simply look up Griffin Peter, until you find the Griffin Peter you’re looking for. A database is exactly that, you can use it to store information, and instead of you looking it up, you simple query the database to look up the information in the column in your rolodex.

Let’s get some terms and definition’s out of the way. You don’t need to fully understand what the definition mean. Just know that these are term’s used in Data basing is important. You eventually will get the meaning.

C.R.U.D.
Create, Read, Update, and Delete.

Primary Key
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
Source (http://dictionary.reference.com/browse/primary%20key)

Table
A collection of records in a relational database.
Source (http://dictionary.reference.com/browse/table)

Database
One or more large structured sets of persistent data, usually associated with software to update and query the data. A simple database might be a single file containing
many records, each of which contains the same set of fields where each field is a certain fixed width.
Source (http://dictionary.reference.com/browse/database)

Record
An order set of fields usually stored continuously. Sometimes also called a “row” in data basing, and in spreadsheets it’s always called a “row”.
Source (http://dictionary.reference.com/browse/record)

Normalization
In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships
Source (http://webopedia.internet.com/TERM/n/normalization.html)

So let’s just create a small database that will store student’s class schedule. I am not going to go into the steps of creating it in Mysql for this is just a theory lesson and understanding on how to create a proper database.

So if you have a database of student schedules you’re going to have to store certain information. So we need to store Names of all the students, where they live and there courses, what time there courses are, and grade. Now this sounds easy enough, you have to remember that when creating a database you want to have as few records as possible and little redundancy also, preferably none, and absolutely no anomalies. Because you want have your database spend the least amount of time looking for the information, and never have data that can’t C.R.U.D. with out creating an anomaly.

Most databases have complicated ways of looking up information stored in there databases. A Database like Oracle is really efficient because they have written algorithms so that it can spend less time searching and return results faster. A simple Mysql program doesn't have such complicated ways, though it is very fast if you have millions and millions of records it will slow it down. And if you have millions of millions of people constantly asking the computer where this information is it can really task a computer and it will be lagy in response. The algorithms in databases are not the only thing that makes them different.

So let’s create a skeleton for a database where we want to store a student schedule.

Table Name: Class Attendants
AttendantID, First Name, Last Name, Address,

Table Name: Classes
CourseID, Course Name, Teacher, Time, Credits

Table Name: Student Course Schedule
SchedualID, StudentID, CourseID

This is a good database design for a school schedule because it is flexible and can be applied to many different schools if your running any kind of school it can be used. If you considered each table as sort of a rolodex you were looking at, perhaps this would be an easier concept of tables. If you look at the table Class Attendants, you will notice that it contains the Name of the student there last name and there address along with a Primary Key, Attendant. This unique identification (Attendant) allows for multiple students and teachers even if they have the same name, to be stored in the database. Same as the Class Table, along with the Student Course Schedule. The table Student Course Schedule is what we call at junction table, it connects both Class Attendants table and the Classes table together. If we didn’t have a Unique ID for each person in the Class Attendant table we would have to use something else to use another unique system of identifying our Class Attendants from one another. Let’s say our table looks like this.

Table Name: Class Attendants
First Name, Last Name, Address,

Table Name: Classes
CourseID, Course Name, Teacher, Time, Credits

Table Name: Student Course Schedule
SchedualID, First Name & Last Name, CourseID

With out the unique identifier to tell our students apart, when we try to C.R.U.D we would have problems. Such as we want to pull to look at all the classes Peter Griffin is in. We would call on our database to look at First Name & Last Name and pull all records pertaining Peter Griffin and find out that there are 32 classes for Peter Griffins in the school. Since your school only has 8 Classes for each student and there are 32 for this one student there must be an anomaly. Because there actually must be 4 different Peter Griffins going to your school and because you didn’t uniquely identify them apart you have no idea which class this Peter Griffin goes to. We could how ever identify them apart by there address, oh unless your kids come from George Forman’s house. Then you have multiple students with the same name from same house so that wouldn’t work either. So it is important when Data basing to take all these considerations in to play because when you C.R.U.D. your data you don’t want to have anomalies such as 4 Peter Griffins with no course schedule because you can’t tell any of them apart.

So our original skeleton of a database is actually quite fine and will work well. Though I am sure there are some out there that would create it differently, I will attempt to explain why you might create a similar database that works fine also but doesn’t even look close to mine. Creating a database is almost like an art, the creation of your own database will be exactly what is needed by you, there is but one true rule in creating a database. No anomalies, a bad database will have redundant data in it. But redundant data though it slows the system down and is just a crappy database, you cannot have a database that has anomalies, or you won’t be able to C.R.U.D your data and you don’t want that.

For a nice tutorial on database normalization and all the rules that go with it please go to
http://www.rsolutions.net/RSweb/TOC.htm and click on either Database Normalization or No Slide Title.

I know this article is not the greatest but I hope other’s instead of ranting how poor it is would contribute to what I have missed or didn’t make sense of.

Cheers

Lenbot

Well done Lembot- a spiffy guide to first time creators of Databases.

Also If I might add something of my own...please guys if your looking at starting a database project for the first time please look at MySQL first..Im pretty confident it will be what you need.
The reason I say this is because most ppl start off in MS Access because its already on thier pc as part of an office install and they get bogged down in the crappy way access runs.

MySQL is free to use has lots of great GUI packages to help you build your database and make scripts,views,quiries on it.
If you couple Open Office to MySQL then you can manage data in spreadsheets etc with complete ease...unlike trying to get live data over to Excel in the Ms world ;((-Its an absolute hell!!

Also it makes sence to use MySQL if your posting here...because Astahost uses MySQL on thier servers so as part of your hosting package you have free access to it.

Hope this helps...

Mark420;)

Thanks lenbot! That helped me to better understand 'primary key' a little better. I've read about it a little in a tuturial, but was never exatly sure about it. Your example with the Student ID was pretty clear to me. While I'm still learning about databases, this was a help for me.

Good summary, and great normalization example. I don't know how many times I've had students make exactly that mistake and not test enough to catch it. A book I used to have on learning Oracle started with some guys 19th century ledger and built a database from it. It was an excellent, interesting, yet simple set of examples that showed many of the common pitfalls and gradually went from a table or two to a non-trival database with triggers and views. Someone walked off with the book after a class at some point, so I no longer have it, but real world examples like this I think are the best teachers and finding the right one is an art form.

Very nice summary, and thanks for the effort explaining thins which are supposed to be obvious.
SQL stands for "simple query language", so everybody forgets that it has concepts, syntax and rules.

Thankx that tutorial was really very useful ... but I have a problem that has not been taken here
I want to do practice on my PC
For that i want to setup up a server on my pc for that I am using SUN ONE SERVER 6.1 developer edition but i have been unable to connect it with the database....I tried with MSacess and db2(IBM database) both are SQL but both time the result was ZERO .MSaccess got connected but the out put was in some alien language and so far DB2 is concerned i was having problem in establishin the connection itself....If you know the solution please post it or send by mail to me my mail add is nikhil@icqmail.com

This post has been edited by nikhil: Oct 19 2006, 12:42 PM

Some other servers are easier to setup for your PC.
Try downloading and installing easyphp.
It will install and automatically configure the web server, the database and a php environment, and phymyadmin which will help you create the dabases and their users.

Thanks for the tutorial, I found it very useful. I do have one question though. How do I create random numbers with SQL? Hope its not a silly question