Textbook Database

1. Textbook Database Please use speaker notes for additional information!

2. The textbook publishing company needs to keep the following information: • ISBN number • Name of textbook/title • Name of author(s) - frequently there is no primary author • Contact information for author • Date of publication • Edition • Price of textbook • Subject of book • Publisher for book (assuming multiple publishing entities within the company) • Publisher information • University, company associated with author • Email address for author • Payment information • Authors current agent • Email address for agent • Address/phone etc. for agent

3. The information about the ISBN number was taken from: http://encyclopedia.thefreedictionary.com/ISBN+Number “The International Standard Book Number, or ISBN, is a unique identifier for books intended to be useful commercially. There is another quite similar system, the International Standard Serial Number (ISSN) for periodical publications such as magazines. The ISBN system was created in the United Kingdom in 1966 (then called Standard Book Numbering SBN) and adopted as international standard IS) 2108 in 1970. Each edition and variation (except reprints) of a book receives its own ISBN. The number consists of four parts: 1. the country of origin, 2. the publisher, 3. the item number, and 4. Checksum digit The different parts can have different lengths and are usually separated by hyphens. These hyphens are not strictly necessary however, since prefix codes are used which ensure that no two codes start the same way. The country field is 0 or 1 for English speaking countries, 2 for French speaking countries, 3 for German speaking countries etc. The country field can be up to 5 digits long. The publisher number is assigned by the national ISBN agency, and the item number is chosen by the publisher. Publishers receive blocks of ISBNs, with larger blocks going to publishers that are expected to need them; a small publisher might receive ISBNs consisting of a digit for the language, seven digits for the publisher, and a single digit for the individual items. Once that block is used up, the publisher can receive another block of numbers, with a different publisher number. As a consequence, different publisher numbers occasionally correspond to the same publisher. The check digit is the sum of the digit number times the digit, modulo 11, with "10" represented by the character "X". For example, to find the check digit for the ISBN whose first nine digits are 0-306-40615: 1·0 + 2·3 + 3·0 + 4·6 + 5·4 + 6·0 + 7·6 + 8·1 + 9·5 = 0 + 6 + 0 + 24 + 20 + 0 + 42 + 8 + 45 = 145 = 13·11 + 2 So the check digit is 2, and the complete sequence is ISBN 0-306-40615-2. Since 11 is a prime number this scheme ensures that a single error (in the form of an altered digit) can always be detected. Because of a pending shortage in certain ISBN categories the international standards organization will soon be moving to a thirteen digit ISBN. This move will also bring the ISBN system into line with the UPC barcode system.”

5. Textbook Table BookIdno primary key booknum edition ISBN DatePublished Price Subject PubIdno The ISBN number does not work as a primary key because different editions of a book can be assigned totally unique ISBN numbers. I have decided to make my primary key BookIdno which will be made up of a unique code for the book plus the edition code. I will carry the ISBN as just another data element/column on the table. Note that we have not added author to this table. A book can have many authors and an author can write many books so a many to many relationship exists between the book and the author. I will deal with this structure on the next slide few slides.

6. Author Table AuthorIdno primary key AuthorName UniversityCompany Email Address Phone AgentCode This is information about the author. Note there is no reference to the book or books the author wrote. Each author can write many books. Remember that each book can be written by many authors as well so there exists a many to many relationship between books and authors.

7. Textbook Table Author Table BookIdno primary key booknum edition ISBN Date Published Price Subject PubIdno AuthorIdno primary key AuthorName UniversityCompany Email Address Phone AgentCode BookAuthor Table (bridge) BookIdno primary AuthorIdno key Payment information The bridge table can simply be the link between the two tables giving me access to book author information or it can also carry data. To meet the rules of normalization, data that is carried must relate to both parts of the primary key. In this case, I can carry payment information which is specific to the book and the author.

8. The textbook publishing company needs to keep the following information. Now I am going to see what we have dealt with up to this point. • ISBN number • Name of textbook/title • Name of author(s) - frequently there is no primary author • Contact information for author • Date of publication • Edition • Price of textbook • Subject of book • Publisher for book (assuming multiple publishing entities within the company) • Publisher information • University, company associated with author • Email address for author • Payment information • Authors current agent • Email address for agent • Address/phone etc. for agent

9. Textbook Table Publisher Table PubIdno primary key PubName PubLocation etc BookIdno primary key booknum edition ISBN Date Published Price Subject PubIdno The relationship between Book and Publisher is a one to many relationship with one book having only one publisher and one publisher having many books. This means that I carry the PubIdno on the book table. If I tried to carry the BookIdno on the publisher table I would have repeating groups and break the rules of normalization. Note that in a one to many relationship, the linking field or foreign key is always carried on the one side.

10. Author Table Agent Table AgentCode primary key AgentName AgentAdr AgentEmail AgentPhone AuthorIdno primary key AuthorName UniversityCompany Email Address Phone AgentCode An author may have an agent. I need to carry information about the agent and it needs to be carried on a separate table because the relationship between author and agent is a one to many relationship. One author has one agent while one agent has many authors. Following the rule of carrying on the one side to avoid repeating groups, I carry the AgentCode (the foreign key) on the Author table.