|
|
The Very BasicsMar 02 '01 Write an essay on this topic.
Popular Products in Books
The Bottom Line A relational database stores data in a manner that reduces redundancy and makes storage flexible (for access).
Note: This is very hard to explain without the ability to draw pictures, but I will give it a go What is a relational database? First let's start with what a database is. A database is simply a way to store and organize information. You can consider your filing cabinet or your telephone/address book a database. A database management system (DBMS) is a computer application that helps a user store, manage, and extract useful data. If you store your addresses and phone numbers online, that file where you store the information, can be considered a database. In a simple DBMS you might store data in a text file. Let's use your personal address book as an example. To store your address book in a file you would use one line for each entry and information about each entry would be stored across as follows: Name Phone Number Address So for each person in your address book you would be allowed one name, one phone number, and one address. To get you familiar with database terms, each row, or each person would be considered a record. Each particular portion of information kept in a record would be considered a field. So Name, Phone Number, and Address are fields. Well, what happens if you have an entry with more than one phone number? Nowadays this is not only possible but probable. You probably have a cell phone number and a beeper phone number and the second line phone number for the house. You have two choices for storing these extra phone numbers, you can either add more fields, or you can add more records. So for phone number #2 you can add another record, where you would type in the name of the person again and add another phone number for the person, and perhaps the same address. Or, you can add another field called Phone Number #2 and add the second phone number on the same record. Now, there are problems with both of these methods of adding that second phone number. If you use method number 1, adding another row, you are duplicating information. In this example, you are duplicating the name again. This wastes space because you are storing the name again and leaves you open to errors because you might type in the name incorrectly. Lastly, you are storing redundant information because you are storing the person's name again. If you use method number 2 you also run into a problem. Are you going to limit each of your friends to having only two phone numbers? Probably not, so then you would have to add more fields for phone numbers. But how many fields? You might decide 10 fields for phone numbers will be able to handle all your needs. This may be true. Two things happen, however, if you add 10 fields for phone numbers. First, you are wasting space for everyone that doesn't have 10 phone numbers. The unused phone number fields take up space (for simplicity assume that if you don't have an entry it is filled with spaces). Second, what if someone comes along that has 11 phone numbers? Are you going to add the 11th field at this point? What happens then? Relational databases solve the problems we experienced with an attempt to create a simple phone book database. Relational databases store information in such a way as to save space and reduce redundancy. The process of doing this is called normalization and database designers spend a lot of time making sure that their relational database is designed to be fully normalized (yes, there are standardized rules for this). Before I describe how you would put your phone book in a relational database, let's define a table. Tables are used in relational databases. Tables are simply made up of columns and rows. Rows are always unique from each other ... so you would never have two rows with the same exact information. Columns are the fields we discussed above ... they describe the information that is to be kept in that column. In a relational database, the first rule of normalization is, that you should never have more than one field in a table that contains the same type of information. So, we should never have more than one field (in the same record) that holds a phone number in a relational database. Instead you would have one table that contains the name of the person in your phone book and then a separate table that contains the phone number(s). In this manner, you are not wasting space. If a person only has one phone number, then there will only be one record for that person in the phone number table. If a person has four phone numbers, then that person would have four records in the phone number table. Easy right? Not too hard to understand. Now, how do you relate the records in the name table to the phone numbers for that person in the phone number table? Remember my definition of a row in a table? I said it had to be unique. Names aren't unique .. you could have two friends called John Doe. So, to make everything kosher, you need to add a unique identifier to your name table. Each person in the name table would by identified by something unique. So, what number could you use that is unique? You can make one up and just start numbering sequentially. This is probably the best method for the database we are trying to build. {{Now you understand why people always want your social security number to store information about you. A social security number is unique and it is also unique to you.}} So the first few entries in your name table could look like this: 1 John Doe 2 Maria Rodriguez 3 Xiao Wong To relate the records in your name table to their corresponding phone numbers, you would use the unique identifier. So, the entries in your related phone number table could look like this: 1 505-555-1212 1 222-474-1818 3 686-247-8456 Notice that there is no entry for Maria Rodriguez in the phone number table. The great feature about this methodology is that I can have any number of related entries in the phone number table including 0. So, I can have a friend who has 15 phone numbers, and a friend who has none. When you design your database, you decide if a related table must have an entry for every row in another table, or if no rows are allowed. In our case, no rows are allowed. However, imagine an employee database. Employee information is kept in one table and department information is kept in another. The employee table contains information about the employee including what department s/he works for. The department table keeps basically the department number and name and maybe the address or floor. In this case, you really can't have an employee belong to a department that doesn't exist and you really can't have a department without any employees. So there needs to be a related entry in each table. Unique identifiers make it easy to relate information from table to table and have an added advantage that they are super-fast if you set them up right. If I wanted to view the phone numbers for each person, I could write some SQL (structured query language - used to retrieve and manipulate information in databases) to bring back the information. I would get back something like this: John Doe 505-555-1212 John Doe 222-474-1818 Xiao Wong 686-247-8456 What has described above is very simply a relational database. Relational databases can get very complex, having thousands of related tables or can be very simple like ours, only having two. In this article, I have really only touched the surface on what relational databases are and can do. As an exercise, you might want to design your own relational database (on paper) for you phone book. Take into account that people might have more than one address, that you might want to store birth dates, that you might want to store spouse and child(rens) names, etc. Then, if you have MS Access, you might take a look at their telephone address book (created by the wizard) tables to see if you got it right. Good computing.  |
| Read all comments (2)|Write your own comment |
|
Ads by Google
|
