Relational Databases

[Hackurtu]

Hey everyone,

I'm just wondering when you are supposed to actually add a relationship between tables in Access (we are using 07). I think it is (the only reason I can come up with) the reason that my reports and queries are coming up with weirded out information. I'll attach it to this post so people can have a look and see if that is the reason too.

I don't understand the relationships between databases that much either and can't find a good place that describes them.

Thanks in advance.

http://www.mediafire.com/?mi7askuv6bfxa5d
(1.78Mb)
Aside: I'm going to be scanning the practice SAC this is from soon but can't right now, so when I do, if anybody wants it just message me.

[MJRomeo81]

I'm just wondering when you are supposed to actually add a relationship between tables in Access (we are using 07). I think it is (the only reason I can come up with) the reason that my reports and queries are coming up with weirded out information. I'll attach it to this post so people can have a look and see if that is the reason too.

First read the three different types of relationships (one to one, one to many and many to many), and also data normalisation (to prevent anomalies). The process of creating relationships is to "join tables". e.g. consider the following database



In the employee table the foreign key is department number. In the department table, department number is the primary key. So this is the key that connects (or joins) the two tables. If we know the dept number of an employee, then I also know the name of the department, its location and mail number. Think of the foreign key as a pointer.

Now imagine if I didn't have this one to many relationship (one department has many employees, but one employee belongs to one department). If I stored this all in one table, what happens when I have to change the mail number of the CS department, or the location of the chemistry department. Can you see what would happen? We would have to change EVERY instance of the value we want to change. So this is why tables are normalised.

You add relationships based on your table design. Before you create the db in access, create an ER model of your database (entities, relationships, attributes).

e.g. if I had a student table and a subject table and I want to manage students enrolling into subjects, I would create a many to many relationship. Whenever you have a many to many relationship, you create a third table (called a junction table) and inside you put the two primary keys of the connecting entities.

Whenever you have a one to many relationship, the foreign key goes on the "many side". Like the example above. A department has MANY employees. But one employee belongs to a single department.

Harness the power of relational databases. They blow everything else away.

[Hackurtu]

Spoiler

First read the three different types of relationships (one to one, one to many and many to many), and also data normalisation (to prevent anomalies). The process of creating relationships is to "join tables". e.g. consider the following database

(Image removed from quote.)

In the employee table the foreign key is department number. In the department table, department number is the primary key. So this is the key that connects (or joins) the two tables. If we know the dept number of an employee, then I also know the name of the department, its location and mail number. Think of the foreign key as a pointer.

Now imagine if I didn't have this one to many relationship (one department has many employees, but one employee belongs to one department). If I stored this all in one table, what happens when I have to change the mail number of the CS department, or the location of the chemistry department. Can you see what would happen? We would have to change EVERY instance of the value we want to change. So this is why tables are normalised.

You add relationships based on your table design. Before you create the db in access, create an ER model of your database (entities, relationships, attributes).

e.g. if I had a student table and a subject table and I want to manage students enrolling into subjects, I would create a many to many relationship. Whenever you have a many to many relationship, you create a third table (called a junction table) and inside you put the two primary keys of the connecting entities.

Whenever you have a one to many relationship, the foreign key goes on the "many side". Like the example above. A department has MANY employees. But one employee belongs to a single department.

Harness the power of relational databases. They blow everything else away.

Wow that helped a lot. Thank you so much!
I thought I understood but I was kind of just doing it without really thinking how or why and it wasn't explained very well (although blaming is bad and I should have asked my teacher). That was a great explanation.
Databases are really fun ^.^

[Lasercookie]

MJRomeo's given a pretty good explanation of how database relationships work, so looking at the database you've created. I'm making a lot of guesses and assumptions about what you're trying to do, so apologies if I interpret it wrongly.

You actually haven't set up any relationships between tables. Some of those one-to-one relationships between Tables and Queries you have seem a bit odd, I'm assuming that's just something Access has done automatically. Just hiding all those and looking at the tables only, this is what you have. 



What's the advantage of a relational database system? This is very much linked to the idea of normalising a database. Easier to use, more powerful queries can be performed and hence it becomes a more useful database etc.

Do you need a relationship between the data in those tables? Well looking at your tables your Transactions are not linked to a specific customer or a book. Who bought an item and what they bought is probably useful information for a transaction. Which brings up your queries and what seems to be the very odd way you've got it set up. It looks like you've actually stored that data in your queries.

Looking at your input form, a store clerk at the checkout doesn't enter in the books that have been bought? Or who is buying it? Anyway, where that data actually got first entered, I have no idea. I'm guessing in one of your queries.

So guessing that qrtCustomerTransactions is the point where that data might have been entered. It seems you've linked up them all up by just sorting transaction IDs down and letting the other data fill up that way. I guess that makes filling your database with dummy data easy, but if you were to actually try and picture this database in use, I don't know how it would work. Which also raises this point



wat. More than one customer for a single transaction? Unless I've pictured how this database should work incorrectly (store has books, customer brings books to checkout counter, store clerk processes transaction), I'm not sure why this should be the case. TransactionID and CustomerID should have a one to one relationship. 

It seems you've done that you've done it that way so that you can have more than one item bought in a transaction. This is where a many to many relationship would be useful. You have many transactions that can have many books being bought. http://office.microsoft.com/en-au/access-help/database-design-basics-HA001224247.aspx#_Toc270678234 "Creating a many to many" relationship is a pretty decent explanation of one way you could go about it.

[Hackurtu]

Spoiler

MJRomeo's given a pretty good explanation of how database relationships work, so looking at the database you've created. I'm making a lot of guesses and assumptions about what you're trying to do, so apologies if I interpret it wrongly.

You actually haven't set up any relationships between tables. Some of those one-to-one relationships between Tables and Queries you have seem a bit odd, I'm assuming that's just something Access has done automatically. Just hiding all those and looking at the tables only, this is what you have. 

(Image removed from quote.)

What's the advantage of a relational database system? This is very much linked to the idea of normalising a database. Easier to use, more powerful queries can be performed and hence it becomes a more useful database etc.

Do you need a relationship between the data in those tables? Well looking at your tables your Transactions are not linked to a specific customer or a book. Who bought an item and what they bought is probably useful information for a transaction. Which brings up your queries and what seems to be the very odd way you've got it set up. It looks like you've actually stored that data in your queries.

Looking at your input form, a store clerk at the checkout doesn't enter in the books that have been bought? Or who is buying it? Anyway, where that data actually got first entered, I have no idea. I'm guessing in one of your queries.

So guessing that qrtCustomerTransactions is the point where that data might have been entered. It seems you've linked up them all up by just sorting transaction IDs down and letting the other data fill up that way. I guess that makes filling your database with dummy data easy, but if you were to actually try and picture this database in use, I don't know how it would work. Which also raises this point

(Image removed from quote.)

wat. More than one customer for a single transaction? Unless I've pictured how this database should work incorrectly (store has books, customer brings books to checkout counter, store clerk processes transaction), I'm not sure why this should be the case. TransactionID and CustomerID should have a one to one relationship. 

It seems you've done that you've done it that way so that you can have more than one item bought in a transaction. This is where a many to many relationship would be useful. You have many transactions that can have many books being bought. http://office.microsoft.com/en-au/access-help/database-design-basics-HA001224247.aspx#_Toc270678234 "Creating a many to many" relationship is a pretty decent explanation of one way you could go about it.

Okay yeah my database is a bit weird
I completely forgot about setting up relationships while I was making everything and it made my queries (such as the one you posted a picture of) all weird. I can only conclude that this is because of the lack of relationships. I think my friend had a look at the relationships and just did all of them but it made no difference.
As for the input forms I have no idea. My teacher said to do them like that and seeing as she is the only one marking them I thought it best to follow her instructions.
Thanks for the help though
I think when I download Access at home tonight I'll remake the whole thing from scratch and make sure I'm doing everything correctly.
You've both helped lots so thanks heaps :3