FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


I'm having problems understanding keys





Denvis
Hi, sorry if this comes of 'noobie' but I'm really unsure about keys in flat file databases (f,r,f,c)

There are 4 keys I am to know prior to my exam in 2 weeks, I believe it's the only thing I don't know. The current topics i'm tested on are communication systems, information systems and databases, project management.

Those 4 keys include:

Single key, composite key, primary key and secondary key.

I'm not understanding the big picture the text book is trying to describe. It would be really nice if someone can explain it, relating back to the database and illustrate the differences between all 4 to me.

I've worked with primary keys however all I know is to set it as an autonumber data type and leave it as an increment number. I also know how it operates, +1 every time I add a new row however if they ask me during an exam what a primary key is, I'm not sure if that answer is acceptable. Is it? And as for the other 3, I don't have a clue. Google doesn't help much either.

I don't know if anybody can help me but anybody can, it would be much appreciated.
Thanks,

Denvis
Fire Boar
Primary key
This is the identifier for your tables. A primary key is a field that is unique, and ALL tables should have exactly primary key: it's a requirement for normalization. This is best explained with an example.

Suppose you want to store the names and phone numbers of people: a phonebook database. For simplicity, we have "number" and "name" and nothing else, in our stripped down example. Here, we can use "number" as the primary key, because two contacts are not going to have the same phone number.

Now suppose you had a table with two columns: first name and last name. Which is the primary key? The answer is, neither. First name is likely to be shared by more than one person, especially if you have a lot of people in the database. Last name is also likely to be shared - if the last name is a common one, like Brown, or if the table consists of people who may be related. So instead you have to create a new column and use it as the primary key. So the table ends up with three columns: an ID column, which gives each person a unique number, a first name and a last name.

Primary keys don't have to be numeric fields, they can also be text fields.


Composite Key
This is a special type of primary key. Above, I stated each table must have exactly one primary key, no less, no more. A composite key is itself a primary key, but this time it covers several fields. In other words, if one field is not enough to guarantee uniqueness, and a second field is also not enough, but combined they are always unique, we can have a composite primary key covering both fields. Here's an example.

Suppose you have a swimming gala, with a table to store the score of each swimmer in each race. Each swimmer is given a number, and each race is also given a number. The scores table would therefore have three fields: swimmer number, race number and score. Swimmer number is not enough to guarantee uniqueness, because one swimmer may participate in many races. Equally, race number is not enough, because more than one swimmer participates in any given race. But since one swimmer cannot compete in one race twice, these two fields together guarantee uniqueness and can form a composite key.

Some people use "composite key" to mean any key that spans more than one field, not necessarily a primary key.


Secondary Key
This is also known as an index. Basically, you can define a secondary key on a table and the database will index the data by that field. So if you need to search records - say - by date, you could define a secondary key on the "date" column which the database system will be able to use to quickly locate the right records. Note that the secondary key may or may not be unique, and may contain empty values if you allow it to. The main goal of a secondary key is to speed up access.


Single Key
I've never heard this terminology before, but I think it's probably another word for a key that is not composite, that is, it only indexes one field.


I think what you're aiming for with those definitions is, in a nutshell:
- Single key: A means of indexing one field.
- Composite key: A means of indexing and grouping more than one field. Every key is either single or composite.
- Primary key: The primary identifier for any record in a table. Must be defined as a single or composite key on every table exactly once.
- Secondary key: Any other indices needed to search data by, used to speed searches up from linear time (look at every record to see if it's the right one) to logarithmic time (use clever tricks to eliminate a lot of possible records quickly, usually reducing the number of results to search each time by a half) or constant time (choose the right one immediately by knowing its location). Zero or more secondary keys may exist.
Denvis
Fire Boar wrote:
Primary key
This is the identifier for your tables. A primary key is a field that is unique, and ALL tables should have exactly primary key: it's a requirement for normalization. This is best explained with an example.

Suppose you want to store the names and phone numbers of people: a phonebook database. For simplicity, we have "number" and "name" and nothing else, in our stripped down example. Here, we can use "number" as the primary key, because two contacts are not going to have the same phone number.

Now suppose you had a table with two columns: first name and last name. Which is the primary key? The answer is, neither. First name is likely to be shared by more than one person, especially if you have a lot of people in the database. Last name is also likely to be shared - if the last name is a common one, like Brown, or if the table consists of people who may be related. So instead you have to create a new column and use it as the primary key. So the table ends up with three columns: an ID column, which gives each person a unique number, a first name and a last name.

Primary keys don't have to be numeric fields, they can also be text fields.


Composite Key
This is a special type of primary key. Above, I stated each table must have exactly one primary key, no less, no more. A composite key is itself a primary key, but this time it covers several fields. In other words, if one field is not enough to guarantee uniqueness, and a second field is also not enough, but combined they are always unique, we can have a composite primary key covering both fields. Here's an example.

Suppose you have a swimming gala, with a table to store the score of each swimmer in each race. Each swimmer is given a number, and each race is also given a number. The scores table would therefore have three fields: swimmer number, race number and score. Swimmer number is not enough to guarantee uniqueness, because one swimmer may participate in many races. Equally, race number is not enough, because more than one swimmer participates in any given race. But since one swimmer cannot compete in one race twice, these two fields together guarantee uniqueness and can form a composite key.

Some people use "composite key" to mean any key that spans more than one field, not necessarily a primary key.


Secondary Key
This is also known as an index. Basically, you can define a secondary key on a table and the database will index the data by that field. So if you need to search records - say - by date, you could define a secondary key on the "date" column which the database system will be able to use to quickly locate the right records. Note that the secondary key may or may not be unique, and may contain empty values if you allow it to. The main goal of a secondary key is to speed up access.


Single Key
I've never heard this terminology before, but I think it's probably another word for a key that is not composite, that is, it only indexes one field.


I think what you're aiming for with those definitions is, in a nutshell:
- Single key: A means of indexing one field.
- Composite key: A means of indexing and grouping more than one field. Every key is either single or composite.
- Primary key: The primary identifier for any record in a table. Must be defined as a single or composite key on every table exactly once.
- Secondary key: Any other indices needed to search data by, used to speed searches up from linear time (look at every record to see if it's the right one) to logarithmic time (use clever tricks to eliminate a lot of possible records quickly, usually reducing the number of results to search each time by a half) or constant time (choose the right one immediately by knowing its location). Zero or more secondary keys may exist.


Awesome, thanks a lot! However, I still don't fully understand it. I have some follow up questions I'd like to ask and you to answer, if you don't mind.

I understand what you said about primary keys, giving a field a primary key means making it unique and classifying it as an identifier. However for a composite key, to achieve such key I need to give it to multiple fields but how exactly do I do that? Is a composite key giving primary keys to more than 1 field cause that wouldn't make any sense. Is there an option where I am allowed to choose composite key then select fields which you want to make unique? or is it just simply a term used to describe, when 1 unique field is not enough to make the entire database unique. More fields need to be unique to ensure the database functions properly?

I also kind of understand secondary keys. It's not unique and is added to fields so increase the speed of data access when users want to find something. What happens though if say...

I have a database

ID:
Name:
Surname:
DOB:

Let's say ID is a primary key but everything else is a secondary key. You said the function of a secondary key is to speed up the access of data, what if there was only a primary key and everything else is a secondary key, there would be no speed in accessing data, right? Or does this just not work, period.

In the books it defines a single key as...

A single key is a field in which each item of data is unique. Care must be taken when choosing a single key, as some fields are not always unique.

God I wish I was at class when they taught this.
Fire Boar
Right, I was mistaken. By "composite key" I guess they do mean "composite primary key", as is the usual definition. And by "single key", they mean "unique key". Pretty much, a single key is a field that is not a primary key but must still be unique. Most tables do not have single keys.

Composite keys are a bit more confusing than primary keys, I agree, but that's pretty much exactly what they are. In MySQL, you would make a composite key like this:

Code:
CREATE TABLE table(
  field1 INTEGER,
  field2 INTEGER,
  field3 INTEGER,
  field4 VARCHAR(255),
  PRIMARY KEY (field1, field2, field3)
)


With this structure, the following record set is valid.

Code:
1 | 1 | 1 | Foo
1 | 1 | 2 | Bar
1 | 2 | 1 | Baz
2 | 1 | 1 | Foo


But the following is not.

Code:
1 | 1 | 1 | Foo
1 | 1 | 2 | Bar
1 | 2 | 1 | Baz
2 | 1 | 1 | Foo
1 | 1 | 1 | Bar


In other words, as long as all the fields of a composite key in a record are not the same as all the fields of a composite key in another record, it is valid.


Your question on indexing all other fields: in fact it does speed access, but at a pretty hefty cost in storage. In your example, you would index the primary key ID either way. But adding indices for Name, Surname and DOB would be adding 3 extra data structures, in addition to the table. This would mean that inserting a record would take longer (you have to find where the new location of where each index should be to keep the structure ordered) and each record would take up more disk space.

For example, suppose the indices were represented by a binary tree. Your table looks like this:

Code:
ID | Name | Surname | DOB
1  | John | Doe     | 01/01/1990
2  | Sam  | Bloggs  | 05/02/1990
3  | John | Brown   | 07/10/1990


Then by indexing Name, Surname and DOB you would have three extra binary trees (not counting the ID):

Name:
Code:
   John (1)
   /      \
John (3)    Sam (2)


Surname:
Code:
    Doe (1)
   /
Bloggs (2)
          \
        Brown (3)


DOB:
Code:
01/01/1990 (1)
            \
           05/02/1990 (2)
               \
               07/10/1990 (3)


This is a very small cut-down example, but you see that searching for someone called "John" takes less time with this: instead of looking down each record in the table, it would use the binary tree to find any "John" records (using the left branch as <=, and the right branch as >). This, in the worst case, takes O(log n) time, where n is the number of records, as opposed to O(n) time. I'm using what's called big-O notation here, it means "correct to a constant". O(log n) is better than O(n), because (with base 2, which computers use) log 1 = 1, log 2 = 2, log 4 = 3, log 8 = 4, log 16 = 5, log 16777216 = 24 etc.

But inserting a record. What do we need to do? Without the indices, we need to insert a record (which takes time O(1), i.e. it doesn't matter how many records there are already, it takes the same time). But we also need to update each binary tree, which takes O(log n) time. Hence the performance loss.

And of course, each binary tree will take up space on the disk, which is the other problem with indexing all your fields with a secondary key. That's why indexing should only be done on fields that you search regularly.
Denvis
Okay, I understood everything up until you started making binary trees. However in my course I don't need to know keys so deep like what you've tried to explain to me. I failed to understand.

Primary key - An identifier, unique to one field
Single key - A field that is not a primary key however must still be unique
Secondary key - A key used to index fields. This makes it faster for a user to search
Composite key - Similar to primary key. Composite key is a combination of many fields with primary keys? Used when one field is not enough to make it unique.

Thanks heaps for helpin' out. You seem very knowledgeable. I have some other questions if you're interested in answering. It's on communication systems.
Fire Boar
That's basically it in a nutshell. Smile Sorry for going into a bit too much detail, I wasn't sure how much you needed. You're right: essentially all the binary tree stuff can be summed up as "it makes searching faster but takes up more space".
infinisa
Hi Denvis and Fire Boar
Denvis wrote:
Okay, I understood everything up until you started making binary trees. However in my course I don't need to know keys so deep like what you've tried to explain to me. I failed to understand.

Primary key - An identifier, unique to one field
Single key - A field that is not a primary key however must still be unique
Secondary key - A key used to index fields. This makes it faster for a user to search
Composite key - Similar to primary key. Composite key is a combination of many fields with primary keys? Used when one field is not enough to make it unique.

Thanks heaps for helpin' out. You seem very knowledgeable. I have some other questions if you're interested in answering. It's on communication systems.

I agree with everything that Fire Boar has said, and Denvis has neatly summarized here, except for the concept of a composite key.

Sure, a composite key is composed of several fields, and this may be necessary to form a primary key if no ID type field is used. However, I can see no reason why composite key can't be a secondary (non-primary) key.

I would say that composite key is the opposite of a simple key (i.e. one consisting of a single field - I've just made this term up, but it would seem a useful concept).

Hope this helps.
Fire Boar
Fire Boar wrote:
Composite Key
...

Some people use "composite key" to mean any key that spans more than one field, not necessarily a primary key.


I mentioned that possibility in my first post, but usually in school, "composite keys" are assumed to be primary. It depends on which definition you are using, and the OP might like to look it up in the course book to double-check.
Denvis
Single key - field in which each item of data is unique
Composite key - made by joining two or more fields together
Primary key - single key or compound that must have a value
Secondary key - field that contains useful items of data, often used in searches


You can see why I asked for help. For someone that only has a very general idea of what a key is, the book doesn't help much. It gives a broad statement, and only a broad statement.
Fire Boar
Oh wow, that is vague. Alright, from those definitions it seems that "composite key" refers to any key that is made up of more than one field, that may or may not be primary.
coreymanshack
I'm glad I stumbled upon this post, I learned quite a bit about "keys" in databases.
Related topics
Problems With Index Page
problems
Laptop Bootup Problems...
problems with FTP
Uploading problems
CSS Problems + Cache control
Linux Problems
Problem getting started---total newb
savants
I find python is still not as popular as perl
Accelerating an electron to the speed of light
Anyone else having problems with Yahoo Website and Mail?
Having Problems Logging In
Teachers, and understanding
Reply to topic    Frihost Forum Index -> Computers -> Computer Problems and Support

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.