Plan and Create the Books-Authors Database in Access



hey this is Ralph and in this video I want to go over a scenario I posed in the class the other day for putting in a books authors database so basically a small database that keeps track that keeps track of books and their authors and I threw in a couple caveats specifically that one book can have many authors and one author can work and write many books ok so that's the scenario we've got to go with and I just want to kind of document how that would probably work and I'm going to start off here and Excel just kind of uses use it as a visual aid and I have three books on my desk at the moment and I'm going to keep track of those here so I'm gonna go ahead and pretend I'm gonna have a books table and my books table is gonna keep track of a book ID a book title and it could do other fields stuff like a number of pages is it paperback is it hardback book the publisher and things like that but I'll keep it simple here to start off with these particular fields so since I have 3 books I'm gonna go ahead and assign IDs to them three different books I'm gonna go and put in some titles here I've got a Dreamweaver cs5 book I've got a book called web usability and I've got another book Photoshop cs5 so my books table is gonna keep track of my book IDs my ID numbers these could be ISBN number certainly and the titles of these books I'm gonna go ahead and take this these book IDs and I'm just gonna format them as text there we go so I've got text fields there and my book ID would be the primary key so I'm gonna go ahead and bold that so we can recall that's that's my books table put some borders on there now I want to keep track of the authors now I'm not gonna put book author inside of my books table because one of my books in particular has three authors and I don't want to list three different authors all in one cell and I don't want to create fields for potential authors like I don't have author one author to author three because what happens when I finally get a book that is five authors well I don't have fields for that or most of my books might only have one author so all those fields are gonna be sitting empty so I'm not gonna create an author field for my books table it's gonna have information about the book title publisher number of pages softbank's does it include a CD yes or no that kind of stuff I'm gonna have another table over here that's gonna keep track of authors and this is gonna have things like my author ID author name I could also have things like date of birth so I'll start off with just these few fields and how many authors do I have I've got three on one book let me just go and I'll just kind of write em as I go here so so one of my authors is Posey Boone R and I'm just putting in some last names another one of my authors is Gellar a third author is Hart a fourth author is Lazar and then for my other book there those authors are already listed so out of my three books I have four different authors involved so I've got three books here Dreamweaver cs5 web usability Photoshop cs5 and there are four authors between all three of those books go and put some borders on there I'm gonna format these IDs as text and once again author ID would be my primary key so I'll go ahead and bold that for now and let me put borders on this one too okay so I've got these two tables books and authors and let's PluralEyes that just to be consistent one book can have many authors and one author can have many books that's a many-to-many relationship and I'm gonna resolve that by creating a junction table or an associative entity and that's a third table that keeps track of the combinations of books and authors and I'll just put it right down here and I'm gonna call mine books authors because I don't have a better name for it and I'm gonna have ABA ID number and then I'm gonna keep track out of the book ID in question and the author ID in question now my book authors ID is going to be the primary key so I'll go ahead and bold that and now I have to keep track of all the various combinations as an example my book number one is Dreamweaver cs5 okay so book ID one is using the author's heart and Gellar okay so that means author two which is Gellar in another row book ID number one same book also has the author heart three okay sell and I'll put in a couple ID numbers here and I'm gonna actually convert all of these to text so let's see what I have here I've got two records or two entries in my book books authors table and they're all about book number one which for me is Dreamweaver cs5 it has two authors book number one was written by author – Gellar and book number one was also written by author three which is heart so that records my Dreamweaver book now I also have references for another book book number two is web usability and it was only it only had one author his name was Lazar Jonathan Lazar so that was author number four okay so there's author four for that book and there's my third record now I go to my last book which is my book number three Photoshop cs5 and this one has three authors which means I'm gonna have three extra entries in my books authors table so I'm gonna have four five and six and the book ID in question is book three and the authors in question are hos ebin R which is author number one Gellar which is author number two and heart which is author number three excellent so let me go ahead and put borders on this and this is the structure for my associative entity my junction table which resolves the many to many relationship here so one book can have many authors one all can write many books and that's organized in this third table notice that book author ID is my primary key and it's unique no two records have the same ID number but notice that book ID would not be a good primary key nor would author ID be a good primary key because there are duplicates see there's a duplicate therefore book number one and I also have duplicates here for author number two C's used in two different places same thing author number three is used in two different places so book ID is not a good primary key author ID is not a good primary key now I have two one-to-many relationships there's a one to many relationship between books and books authors there's a one to many relationship between authors and books and books authors so that's my plan there in Excel now if I was doing this and access let me jump over to access real quick and I'm just gonna create a new database and I'll call it books authors and create a blank database I know I'm gonna need three tables I'm gonna jump to design view my first table is gonna be books and let's see I'm gonna have my book ID and I'm gonna put in text I'll just type in those numbers myself and I'm gonna have book title and that will also be text there we go got that taken care of primary key is book ID and let me go ahead and enter this data in so I'll jump over to datasheet view and I'll put in book 1 let's say I'll try to do these in the same order that it did before so I did Dreamweaver web usability Photoshop cs5 okay so I'm jump back over to access Dreamweaver cs5 oops let's get back here book to web usability and book 3 Photoshop cs5 there we go so now I've got my book centered I'm gonna create another table in design view and let's see this is gonna be my author ID text and author name and that'll also be text author ID is gonna be my primary key so let me go to datasheet view it's prompting me to save I will do so this is gonna be mine authors table and let's see on Excel I did hose eben are Gellar heart bizarre hose eben are Gellar heart and Lazar so now I have my author's listed so I have my author's table and my books table are created there's my books with my three books listed there's my authors of my three authors listed both of these tables have a primary key an ID number that keeps them all unique one more table to go I'm going to do create and I'm going to do table design and this is going to be my books authors ID and that'll be text and then I'm gonna have a book ID which is text and an author ID which is text keep track of all this information I'm gonna jump to datasheet view I'm gonna save when prompted I will call this books authors click OK oops I forgot to set a primary key so let's make sure it's set the right primary key it didn't it created a new primary key for me so I'm gonna go ahead and fix that right now I'm gonna make this field my primary key and I'm gonna delete that primary key that made for me so now I do have a primary key here my books author's ID go back to datasheet view safe when prompted and I can kind of reference my excel sheet here but basically I'm gonna have 1 1 2 2 1 3 1 1 2 2 1 3 3 to 4 4 3 1 3 2 4 4 3 1 5 3 2 6 3 3 5 3 2 6 3 3 now if all these numbers seem confusing remember these are simply the ID numbers that reference my various books and/or authors so my first reference here my first record in my books authors table record number 1 involves book 1 and author 2 so if I go to my books table book one was Dreamweaver author to go to my authors table was Gellar and if I go to my Dreamweaver book I look down yep sure enough Gellar was one of the authors for that particular book my other reference my second record here in my books authors table also referenced book 1 and author 3 book 1 author 3 book one is Dreamweaver author 3 is heart and if they look at the cover of my book I can see that Gellar and Hart were the authors for that particular book so that's what all these code numbers represents now let me jump over to actually let me go ahead and close the tables database tools relationships I'm gonna bring in all three tables and I'm gonna kind of arrange them so book books authors is kind of in the middle author ID to author ID enforce referential integrity create book ID to book ID enforce referential integrity create so instead of having a mutt a many-to-many relationship I have two one-to-many relationships so this is what the relationship window should look like for this particular database scenario now that we have this kind of stuff you can create queries and reports that reference these tables what if I wanted to get a the names of all of the authors for a particular book no problem I would use my book authors table to find out which authors wrote a particular book and then that would look over to the authors table to get the author's name out of there and the books table to get the books title out of there so that's a little bit for this simple database scenario

6 thoughts on “Plan and Create the Books-Authors Database in Access

  1. Thank you for making this so simple. Because this much information in Excel becomes cumbersome to weed through, I'm creating a database to keep track of the books I've written and the agents to whom I send queries, so I've got a many-to-many but I had no idea where to start. This made things simple and while I've got a long way to go, I feel like I've at least got a good starting point and understanding of how the relational tables work. Thank you!!!

  2. Hi thank you for making this video. I was wondering if there was a way to NOT type the books ID by yourself? I mean, making Access to autonumber the BookID and with the possibility to repeat the number. Same with the authors

  3. Thanks for videos, although I teach formal language and artificial Intelligence, this semester I got a task to teach Access the students without  skills from informatics, but they know very good English. So your videos are great for start….thanks a lot!

  4. Great (and relevant) starting point. Which relationship to use to add records to the join table? Ex: once authors and books are entered onto their respective tables, create a form for authors, and "add" their works through the subform?

  5. @dashby1969 thanks for watching the video. For your DB, use sql to create a new table based off of a select query from an existing table. Check the create-table html page at the dev.mysql .com web site. Or, create a copy of your existing table and delete fields you no longer need.

Leave a Reply

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