Help with bookstore db (1 Viewer)

[D.J.Ida]

I realize that this thread started in 2004 and as been added to ever since. For the basics of database structure (normalization) that has been referenced, I am going to suggest this tutorial from RogersAccessLibrary. It leads you through a process to work from business requirements to a normalized database. It provides a problem, intermediate steps and a solution. You will learn and what you learn can be used with any database. The secret is to work through the example. (~30 -45 minutes). There are more tutorials from RogersAccessLibrary, as well as many articles from various sites in the Database Planning and Design link in my signature.
Good luck. Please let us know the status as things evolve.

Hi, just one question if I may: I'm going through the ZYX laboratories example, and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example. Am I supposed to be working in database view or design view? Because it seems that the primary key can only be assigned in design view. But then it's impossible for the attribute grid to look like this (database view):

design view:

I don't understand how to make the SS# the primary key???

 

[Pat Hartman]

If your job is to order and stock the books, you are concentrating on the wrong data. Do not take on data entry/management that is not required to do your job. For example, you have no Order and OrderDetails tables. Are you going to prepare the RFQ's and Orders by hand?

The employee table will have an autonumber PK. When you enter an employee, the BeforeUpdaete event of the form should look for another employee with the same name and ask if the new one is a duplicate. You can't make name the PK because names can be duplicated and you need to allow that to happen.

Job History also has an autonumber PK with a foreign key to EmpID. Do NOT use table level lookups!!!!!

Certifications Could have a natural key but it is better to use an autonumber and make a unique index on the Cert name. You also need an Employee Certification table.

BTW, this is no longer the database you described. It has become all about employees and not about ordering and managing an inventory. Are you saying that the school doesn't already have a database to manage employees?

This is sounding more like a school assignment than a real world application.

 

[jdraw]

Hi, just one question if I may: I'm going through the ZYX laboratories example, and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example. Am I supposed to be working in database view or design view? Because it seems that the primary key can only be assigned in design view. But then it's impossible for the attribute grid to look like this (database view):

View attachment 98588
design view:

View attachment 98587

I don't understand how to make the SS# the primary key???

His attribute grid is simply a grid (not a database table)--could be just on a piece of paper. It is not part of the database per se. Just think of the grid as a piece of paper or notepad. He is leading you through the process of identifying things that should be tables and/or fields and how tables are related.

 

[mike60smart]

Hi, just one question if I may: I'm going through the ZYX laboratories example, and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example. Am I supposed to be working in database view or design view? Because it seems that the primary key can only be assigned in design view. But then it's impossible for the attribute grid to look like this (database view):

View attachment 98588
design view:

View attachment 98587

I don't understand how to make the SS# the primary key???

Hi
In the example shown of table "Fields"

The first column shows the fields for tblEmployees and it is recommended that you use EmployeeID as the PK

The Second Column shows the fields for tblJobHistory and it is recommended that you have JobHistoryID as the PK - You then need to add an additional field "EmployeeID" which will be a LongInteger - Number and will be the FK linked to tblEmployees - PK

the 3rd Column shows the fields for tblCertifications and it is recommended that you have CertificationID as the PK - You then need to add an additional field "EmployeeID" which will be a LongInteger - Number and will be the FK linked to tblEmployees - PK

 

[FrJonathan]

@Pat Hartman, I'm pretty sure that D.J. said that the db that D.J. is working with just now is "the ZYX laboratories example," - just trying to learn the skills - not the db for D.J.'s bookstore. So of course it sounds like a school assignment. That doesn't contradict D.J.'s statement about the reason D.J. is learning Access - D.J. wants to develop the bookstore db with the skills D.J. is learning from the ZYX exercise and other exercises.

@D.J.Ida Yes, the place where you assign or change the primary key is, in fact, the design view. You're just using the table like a scrap of paper to organize notes on the data elements you need. Therefore, the primary key on your attributes grid is not important, as you won't connect that grid to any other tables.

Each column of related data on your attributes grid represents a table you will create with that data in it. Each of THOSE tables will have primary keys to uniquely identify each record in the table and - as needed - foreign keys to connect the tables to one another.

By the way, it is NOT recommended to use the Social Security Number as the employee's identification number. It has nothing to do with database management; it creates significant risks of identity theft. I tried to post a link to an article on the subject but the forum won't let me. For some reason, inserting a link causes the website to ID it as "spam-like".

 

[Pat Hartman]

Please explain what job history has to do with ordering books.

 

[Pat Hartman]

If you want to have a discussion regarding a sample database, start a new thread. Do not mingle it in with the original thread. No one will be able to follow the discussion.

@D.J.Ida please confirm the diversion and I will try to split the thread.

 

[CJ_London]

and I'm trying to create the attribute grid, but I'm having trouble assigning primary keys as in the example.

see my example, using excel in post #7

@Pat Hartman - there is no diversion, OP was working through an example posted by another responder

 

[FrJonathan]

Here's an example of the table organizations you're identifying in your attributes grid. Because each employee can have more than one certification, you'll put the certification data in a separate table, and create a foreign key field that you will use to connect it to a specific employee, by storing a value equal to the primary key from the employee information table. [I added certification expiration because - for obvious reasons - this would be critical information in managing personnel.]

Using the Relationships tab, you will tell Access that the two fields are related, and define the nature of their relationship. (In this case, many-to-one. The "many" side of the relationship is marked with the infinity symbol and the "one" side with the numeral 1.)

The application to your bookstore database is obvious...
Faculty members teach more than one course.
Students enroll in more than one course.
Courses require more than one book.
etc.

However, as you go along, you will realize that...
Faculty members teach more than one course, and the same course will be taught by more than one faculty member.
Courses require more than one book, and the same book may be required in more than one course.
etc.

This will, eventually, require you to establish many-to-many relationships... but you can cross that bridge when you get to it. You have to master many-to-one, first.

 

[Pat Hartman]

There WAS a diversion. we are no longer discussing the actual application. You are all wrapped up in the sample. Have fun going down the rabbit hole.

 

[mike60smart]

There WAS a diversion. we are no longer discussing the actual application. You are all wrapped up in the sample. Have fun going down the rabbit hole.

Sorry Pat but the Op said he did not understand how to relate the fields.

If he doesn't understand the initial steps then he has no chance of building the database he requires.

 

[Pat Hartman]

That's fine. The discussion should be separate from the question if you are going to be explaining the example. I was just offering to clean up the thread. I will go away and leave you to it.

 

[D.J.Ida]

If your job is to order and stock the books, you are concentrating on the wrong data. Do not take on data entry/management that is not required to do your job. For example, you have no Order and OrderDetails tables. Are you going to prepare the RFQ's and Orders by hand?

The employee table will have an autonumber PK. When you enter an employee, the BeforeUpdaete event of the form should look for another employee with the same name and ask if the new one is a duplicate. You can't make name the PK because names can be duplicated and you need to allow that to happen.

Job History also has an autonumber PK with a foreign key to EmpID. Do NOT use table level lookups!!!!!

Certifications Could have a natural key but it is better to use an autonumber and make a unique index on the Cert name. You also need an Employee Certification table.

BTW, this is no longer the database you described. It has become all about employees and not about ordering and managing an inventory. Are you saying that the school doesn't already have a database to manage employees?

This is sounding more like a school assignment than a real world application.

No, this is an example from one of the links I was recommended to look into, in order to understand how Access handles data better. It's been 10 years or more since I opened Access, and even then it was a very short course where we were guided on what to d and how to do it. So I'm trying to understand how exactly the person who wrote this managed to assign this PK. I don't even know what table level lookups are.

 

[D.J.Ida]

His attribute grid is simply a grid (not a database table)--could be just on a piece of paper. It is not part of the database per se. Just think of the grid as a piece of paper or notepad. He is leading you through the process of identifying things that should be tables and/or fields and how tables are related.

Alright, got it!

 

[Pat Hartman]

I'm not going to attempt to split the thread but I am also not going to participate because I don't want to have to figure out if we are talking about some example or your initial request.

 

[D.J.Ida]

@Pat Hartman, I'm pretty sure that D.J. said that the db that D.J. is working with just now is "the ZYX laboratories example," - just trying to learn the skills - not the db for D.J.'s bookstore. So of course it sounds like a school assignment. That doesn't contradict D.J.'s statement about the reason D.J. is learning Access - D.J. wants to develop the bookstore db with the skills D.J. is learning from the ZYX exercise and other exercises.

@D.J.Ida Yes, the place where you assign or change the primary key is, in fact, the design view. You're just using the table like a scrap of paper to organize notes on the data elements you need. Therefore, the primary key on your attributes grid is not important, as you won't connect that grid to any other tables.

Each column of related data on your attributes grid represents a table you will create with that data in it. Each of THOSE tables will have primary keys to uniquely identify each record in the table and - as needed - foreign keys to connect the tables to one another.

By the way, it is NOT recommended to use the Social Security Number as the employee's identification number. It has nothing to do with database management; it creates significant risks of identity theft. I tried to post a link to an article on the subject but the forum won't let me. For some reason, inserting a link causes the website to ID it as "spam-like".

Yes, I'm trying to understand these concepts; I've used Excel for several years to manage our book orders, but it has become increasingly difficult since the university is growing and offering more departments and courses. I was told Access would be a better choice. The developers are currently dealing with several major projects and cannot work on this one, so I might as well try to learn as much as I can.

 

[D.J.Ida]

I'm not going to attempt to split the thread but I am also not going to participate because I don't want to have to figure out if we are talking about some example or your initial request.

Fair enough; thank you for trying to help in any case.

 

[Pat Hartman]

Based on your responses to me (and in #36 above), YOU are making this application much harder than it needs to be. YOUR application does not need any information about the programs run by the school. All it needs to know is what books are required for what courses for the upcoming semester. And then what students are taking what courses so you know how many books to order and can package them if you have the staff and space to do so. When you want other advice on the app, please start a new thread.

 

[D.J.Ida]

Here's an example of the table organizations you're identifying in your attributes grid. Because each employee can have more than one certification, you'll put the certification data in a separate table, and create a foreign key field that you will use to connect it to a specific employee, by storing a value equal to the primary key from the employee information table. [I added certification expiration because - for obvious reasons - this would be critical information in managing personnel.]

Using the Relationships tab, you will tell Access that the two fields are related, and define the nature of their relationship. (In this case, many-to-one. The "many" side of the relationship is marked with the infinity symbol and the "one" side with the numeral 1.)

View attachment 98598

The application to your bookstore database is obvious...
Faculty members teach more than one course.
Students enroll in more than one course.
Courses require more than one book.
etc.

However, as you go along, you will realize that...
Faculty members teach more than one course, and the same course will be taught by more than one faculty member.
Courses require more than one book, and the same book may be required in more than one course.
etc.

This will, eventually, require you to establish many-to-many relationships... but you can cross that bridge when you get to it. You have to master many-to-one, first.

OK, I think I'm beginning to see. One of the linked examples mentioned the many-to-many relationships, and if I remember correctly, said that the way to deal with this was to introduce a table as a go-between for the table that are in a many-to-many relationship.

One thing I noticed as I was trying to work out how many ... do you call these tables? fields? I need to create and testing relationships, I am not seeing the symbols on the line connecting these tables. There's no number one, not the infinity symbol - it's just an ordinary line.

And I was going to say that we only have one book per course, but then I remembered that while 90% of courses do follow that, there are a couple of them where the lecturers said they must have an additional book. Students get their assigned reading from the library or the instructors create readers for them.

... yeah I know this is going to be difficult. I am a rank beginner, but my colleagues are absolutely swamped and we're already outsourcing several things. I either have to wait or try my best, possibly prepare the database in such a way that they import the data into whatever system they make for me.

 

[mike60smart]

OK, I think I'm beginning to see. One of the linked examples mentioned the many-to-many relationships, and if I remember correctly, said that the way to deal with this was to introduce a table as a go-between for the table that are in a many-to-many relationship.

One thing I noticed as I was trying to work out how many ... do you call these tables? fields? I need to create and testing relationships, I am not seeing the symbols on the line connecting these tables. There's no number one, not the infinity symbol - it's just an ordinary line.

And I was going to say that we only have one book per course, but then I remembered that while 90% of courses do follow that, there are a couple of them where the lecturers said they must have an additional book. Students get their assigned reading from the library or the instructors create readers for them.

... yeah I know this is going to be difficult. I am a rank beginner, but my colleagues are absolutely swamped and we're already outsourcing several things. I either have to wait or try my best, possibly prepare the database in such a way that they import the data into whatever system they make for me.

Hi
You said "There's no number one, not the infinity symbol - it's just an ordinary line."

If you double click on the ordinary line and select the option "Enforce Referential Integrity" you will get the 1 to Infinity Symbols