Part 1 BOOKS, AUTHORS & PUBLISHERS – Library Database System

in the previous tutorial I created this books table that we see here in front of us and what I'm going to do now is I'm going to create the two different tables authors and publishers because I have foreign keys that should be related to those two tables in the books table and I need to make a relationship between them so to create a new authors table I'm going to go to the create tab in Access click on the table icon and I'm back again into creating a new table view and again I'm going to rename that primary key instead of having it just labeled as ID the naming convention that I'm using insists that it will be called author ID and note as well that the primary key field will generally use the singular version of the element names of the elements that we're talking about the databases author or authors the primary key will usually be named author singular underscore ID because every record in this table is a single author and so that makes sense so author ID is there the author name is the next field and again author name is a text field so author name using camelcase is the naming convention that I'm using author name and that's my table down essentially I'm just going to save that table I'm going to save it as authors again using uppercase letters using the naming convention that I'm using authors plural because the authors table contains many different records that all are an individual author and the author name that I'm going to put in here as my first record of dummy data will be own color okay we see that he's been giving an automatic one for the his author ID now we can see here that in the author's table I've got an author name called own conferred he's got an author ID of one that relates very very easily back to this foreign key in my books table because this book Artemis Fowl has been written by own Carver and that's exactly what it says over here this author foreign key field it's saying it has been written by author number one whoever is listed as author number one underneath the primary key field in the authors table that is who the author is of this book now we can see that relationship here just by eyeballing between those two different fields the primary key field and authors in the foreign key field in books but we need to explicitly say that to access and the access database management system so we need to create a relationship between both of those two different fields and to do that I need to go into the relationships you but before I do I'm going to close down those two tables because anytime you create relationships between two tables and access it requires you to have the both the tables closed and I'm going to go into database tools it's where we find the relationships icon click on relationships it comes up with this show table dialog box the first time I go into the relationships view and I've asked me what tables I want to add in and so I can select both those two different tables there click Add and I can see a nice visual representation of each the two different tables the primary keys are denoted by a little key icon beside each of them on each of the different tables and then you can see now why we follow the naming convention that we did it's very easy to see what the primary key is and it's also very easy to see what the foreign keys are so to make a relationship between the two fields that we've spoken about before the author ID primary key from the authors table and the author foreign key in the books table I just need to click and drag from author ID and drop on to the author underscore FK field this edit relationships dialog box comes up and I always advise my students to click on the enforce referential integrity box what that does is it makes sure that the different data in both of those two different fields match each other so for example if you go into the author foreign key field in the books table and you try and put in a number that doesn't exist back in the primary key field of the authors table it won't let you do it because it's corrupting the database you're putting in data into into the database that doesn't make sense so enforce referential integrity will force that to happen they will put up an alerts if you try and put boss data so I'm going to create that relationship between those two there and that relationship is created if those two different pieces of data didn't match up it wouldn't happen so now I see a nice relationship there and I see the symbols one on the primary key side of the relationship too many on the foreign key side of the relationship and what that means is that one author can have many different books associated with him or her but a book can only have one author and that makes sense again if I go into the books table I can only fit in one author number in here in the author foreign key field I can't go 1 comma 3 to say that it's been written by two authors I can only put in one author now later on we may want to make a many-to-many relationship where you might like to have a book with more than one author but for the moment the way that we're designing this database we can just have one author per book but an author can write many different books and again we can see that that if I had lots of different records in there I could have many different records each with the number 1 in the author foreign key field so that's the authors table the books table and the relationship between them and just to show you the next relationship which is between the publishers and the books table I'm going to go through pretty much exactly the same process as I went with put authors and books because it's exactly the same type of relationship so I'm going to click on create and table and the primary key fields I'm going to rename according to my naming convention as publisher ID if I can publisher ID that's fine and the next field is a text field and that's just going to be publisher name great and so let's give a publisher name here dealer macmillan that's fine they've been giving the publisher ID of one from the auto number fields which is the primary key field in access and finally i'm going to save that table and i'm going to call it publishers plural click okay and that's that table done again i have to make a relationship between this publisher ID primary key and the publisher foreign key in the books table so before i do that i'm just going to close down those two tables go into my relationship view i have to add in the publisher into this relationship so i'm going to go show table and click on publishers click add that pops that in there and again it's exactly the same process as before because it's exactly the same type of relationship i'm pulling from a primary key in one table into a foreign key in another and it's very easy to see them there is i'm just going to click and drag across there drop on that enforce referential integrity making sure that the data in each one of those tables matches up create and i get a perfect relationship there again one-to-many and that's a nice chart to my database there with those three tables just to point out with the access system if I want to click into any of these different tables into the datasheet view for instance once I create those relationships I see this little symbol come up beside the different records in that database and that's called an expand indicator if I click on that expand indicator what it does is it drills down across the relationship from the primary key side which is in the authors table into the foreign key side which is in the books table and it shows me all the books that are associated with that author which is a really nice effect and really nice feature in our case here and at the example we've only got one dummy dummy record in each of those different tables so it's not hugely impressive but we get the general idea that's the same at the publishers table as well if I drill down from the publishers table I get the same effect it doesn't want from the books table because because that's on the foreign key side of the relationship each book can be related to just one author or one publisher so drilling back up through that doesn't really make sense for the moment that's that tutorial in the next tutorial I'm going to add on the next few tables which have to do with loans and members and the different copies of books as well so look forward to that

16 thoughts on “Part 1 BOOKS, AUTHORS & PUBLISHERS – Library Database System

  1. how can you make an sql view of this Library database system you make? Need that info
    to make a reference for my database system..

  2. There is no way to add so many auto numbers to one table. I tried everything please help me.

  3. Thanks to trial and error and sheer perseverance I'm starting to get to grips with it 🙂

  4. Thanks for your tutorial. I am having difficulties with this right at the beginning unfortunately. I can't establish the relationship between the author_ID and author_FK. I am being told that the relationship must be on the same number of fields and the same data types. I imagine that I am making a basic error. Could I ask what value you are giving to your ISBNs (short text or number? as some ISBNs can be a combo of text and number)? I also notice your author_FK and publisher_FK are prep-populated. Can I ask if this was entered manually previously? Thanks.

  5. You said in the previous tutorial you did the books, where is this tutorial?

  6. How to build a full library there are many user see pdf book, they are use its local area network, I don't know. Please if you possible shear related tutorial.

  7. Did you make anymore videos on creating a Library Database?  I saw parts 1, 2, and 3.  They were very helpful.

  8. Didn't post one on creating the books table. This series of tutorials is more about relationships between tables.

  9. very good but I cant find the previous tutorial where you made the books table….

Leave a Reply

Your email address will not be published. Required fields are marked *