MySQL, Java, JSP, Servlet, SSH, Tomcat Web Hosting Blog

Primary Keys A primary key is used to uniquely identify a record in a table. Unique identification for each record is required because there is no other way to find a record without the possibility of finding more than one record, if the unique identifier is not used. Figure 3-21 shows primary key fields of AUTHOR_ID for the AUTHOR table and PUBLICATION_ID for the PUBLICATION table, each being primary key fields for the two tables. Figure 3-21: A primary key uniquely identifies a record in a table. Unique Keys Like a primary key, a unique key is created on a field containing only unique values throughout an entire table. In Figure 3-21, and throughout the rest of this chapter, you may be wondering why integers are used as primary keys rather than the name of an author or a publication, and otherwise. The reason why will be explained later in this book but in general integer value primary keys are known as surrogate keys because they substitute as primary keys for names. For example, the AUTHOR_ID field in the AUTHOR table is a surrogate primary key as a replacement or surrogate for creating the primary on the AUTHOR table NAME field, the full name of the author. It is very unlikely that there will be two authors with the same name. Surrogate keys are used to improve performance. So, why create unique keys that are not primary keys? If surrogate keys are used and the author name is required to be unique, it is common to see unique keys created on name fields such as the AUTHOR table NAME and the PUBLICATION table TITLE fields. A unique key ensures uniqueness across a table. A primary key is always unique, or at least a unique key; however, a primary key is also used to define relationships between tables. Unique keys are not used to define relationships between tables. 1 2 3 4 5 6 7 8 9 10 11 12 2 2 3 3 3 3 3 4 4 4 6 7 Cities in Flight A Case of Conscience Foundation Second Foundation Foundation and Empire Foundation s Edge Prelude to Foundation The Complete Works of Shakespeare Lucifer s Hammer Footfall Ringworld Hocus Pocus PUBLICATION_ID AUTHOR_ID TITLE 1 5 6 Orson Scott Card James Blish Isaac Azimov Larry Niven Jerry Pournelle William Shakespeare Kurt Vonnegut AUTHOR_ID NAME 2 3 4 7 Publication Author Author author_id name Publication publication_id subject_id (FK) author_id (FK) title PUBLICATION_ID uniquely identifies a publication AUTHOR_ID uniquely identifies an author 59 Database Modeling Building Blocks
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

Figure 3-20: Identifying, non-identifying, and dependent relationships Keys are used to identify and ultimately retrieve records from a database at a later date. Understanding Keys Relational databases use the terms index and key to indicate similar concepts. An index is like an index in a book used to find specific topics, on specific pages, in a book, very quickly (without having to read the entire book). Similarly, an index in a relational database is a copy of a part of a table, perhaps structured in a specific format such as a BTree index. An index can be created on any field in a table. A key, on the other hand, is more of a concept than a physical thing because a key is also an index. In a relational database, however, a key is a term used to describe the fields in tables linking tables together to form relationships (such as a one-to-many relationship between two tables). A key is both a key and an index. A key is an index because it copies fields in a table into a more efficient searching structure. A key is also a key, its namesake, because it creates a special tag for a field, allowing that field to be used as a table relationship field, linking tables together into relations. There are three types of keys: a primary key, a unique key, and a foreign key. Author author_id name Publication publication_id subject_id author_id (FK) title CoAuthor coauthor_id (FK) publication_id (FK) Dependent entity is a rounded rectangle shape Parent primary keys part of primary key Identifying relationship Coauthor uniquely identified by Author and Publication Non identifying relationship Publication not uniquely identified by Author Parent primary keys not part of primary key Independent entity is not rounded 58 Chapter 3
We recommend high quality webhost to host and run your jsp application: christian web host services.

Figure 3-19: One implies a record must be present and zero the record can be present. Identifying and Non-Identifying Relationships Figure 3-20 shows identifying relationships, non-identifying relationships, and dependent tables. These factors are described as follows: . Identifying relationship The child table is partially identified by the parent table, and partially dependent on the parent table. The parent table primary key is included in the primary key of the child table. In Figure 3-20, the COAUTHOR table includes both the AUTHOR and PUBLICATION primary keys in the COAUTHOR primary key as a composite of the two parent table fields. . Non-identifying relationship The child table is not dependent on the parent table such that the child table includes the parent table primary key as a foreign key, but not as part of the child table s primary key. Figure 3-20 shows a non-identifying relationship between the AUTHOR and PUBLICATION tables where the PUBLICATION table contains the AUTHOR_ID primary key field from the AUTHOR table. However, the AUTHOR_ID field is not part of the primary key in the PUBLICATION table. . Dependent entity or table The COAUTHOR table is dependent on the AUTHOR and PUBLICATION tables. A dependent table exists for a table with an identifying relationship to a parent table. . Non-dependent entity or table This is the opposite of a dependent table. 198711905 345308999 345306275 345438353 553278398 553293362 553293370 553293389 893402095 1585670081 5557076654 1150 1200 1800 2000 1900 1050 1950 1100 1850 1000 1250 130 140 ISBN RANK INGRAM_UNITS Rank Edition 198711905 246118318 345308999 345323440 345333926 345334787 345336275 345338353 449208133 553278398 553293362 1150 1200 1800 2000 1900 1050 1950 1100 1850 1000 1250 ISBN RANK INGRAM_UNITS 553293370 553293389 893402095 1585670081 5553673224 5557076654 Hardcover Hardcover Paperback Paperback Paperback Paperback Paperback Paperback Hardcover AudioCassette AudioCassette Non-highlighted editions do not have rankings 57 Database Modeling Building Blocks
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Figure 3-18: One implies a record must be present and zero the record can be present. Figure 3-19 shows the equivalent of the one-to-one table structure representation using similar but a little more data than the data in Figure 3-13. ISBNs 198711905 and 345308999 both have RANK and INGRAM_UNITS value entries and thus appear in the RANK table as unique records. On the contrary, the edition with ISBN 246118318 does not have any information with respect to rank and Ingram unit values, and thus RANK and INGRAM_UNITS field values would be NULL valued for this edition of this book. Since values are NULL valued, there is no record in the RANK table for the book with ISBN 246118318. Publisher publisher_id name Publication publication_id subject_id author_id title Rank ISBN (FK) rank ingram_units Edition ISBN publisher_id (FK) publication_id (FK) print_date pages list_price format rank ingram_units Edition ISBN publisher_id publication_id print_date pages list_price format One to zero, one or many An edition does not have to be ranked but if a Rank row exists there must be a related edition Zero or one to exactly one 56 Chapter 3
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

Figure 3-17: Resolving a many-to-many relationship. Zero, One, or Many Relationships between tables can be zero, one, or many. Zero implies that the record does not have to exist in the target table; one with zero implies that it can exist; one without zero implies that it must exist; and many simply implies many. The left side of Figure 3-18 shows a one-to-zero (or exactly one) relationship between the RANK and EDITION tables. What this implies is that an EDITION record does not have to have a related RANK record entry. Because the zero is pointing at the RANK table, however, the same is not the case in reverse. In other words, for every RANK entry, there must be exactly one record in the EDITION table; therefore, individual editions of books do not have to be ranked, but a ranking requires a book edition to rank. There is no point having a ranking without having a book to rank in fact, it is impossible to rank something that does not exist. Similarly, on the right side of Figure 3-18, a publisher can be a publisher, if only in name, even if that publisher currently has no books published. When you think about that, in reality it sounds quite silly to call a company a publisher if it has no publications currently produced. It s possible, but unlikely. However, this situation does exist in this database as a possibility. For example, a publisher could be bankrupt where no new editions of its books are available, but used editions of its books are still available. This does happen. It has happened. 1585670081 345438353 246118318 5553673224 5557076654 345334787 345308999 893402095 345336275 553293362 553293370 553293389 553298398 449208133 345323440 345333926 Overlook Press Ballantine Books HarperCollins Publishing Books on Tape Books on Tape Del Rey Books Del Rey Books L P Books Ballantine Books Bantam Books Spectra Spectra Spectra Fawcett Books Del Rey Books Ballantine Books Cities in Flight A Case of Conscience Foundation Foundation Foundation Foundation Foundation Foundation Foundation Second Foundation Foundation and Empire Foundation s Edge Prelude to Foundation Lucifer s Hammer Footfall Ringworld TITLE PUBLISHER ISBN PRINTED 28-Apr-83 31-Jan-20 31-Dec-85 31-Jan-51 28-Feb-83 31-May-79 31-Jul-86 31-May-85 31-Jul-96 30-Nov-90 Each edition is uniquely identified by ISBN unique to each new edition of the same title 55 Database Modeling Building Blocks
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

Figure 3-16: Resolving a many-to-many relationship. In Figure 3-17 there are seven different editions of the publication Foundation. How is this so? Isaac Azimov is an extremely popular author who wrote books for decades. This particular title was written many years ago and has been in print ever since. Searching for this particular publication without the unique ISBN number unique to each edition would always find seven editions in this database. If only one of the Books On Tape editions was required for a query, returning seven records rather than only one could cause some serious problems. In this case, a many-to-many join resolution table in the form of the EDITION table is very much needed. Publisher publisher_id name Publisher publisher_id name Publication publication_id subject_id author_id title Publication publication_id subject_id author_id title Edition ISBN publisher_id (FK) publication_id (FK) print_date pages list_price format rank ingram_units Many-to-many implies a publisher can publish many books and a single book can be published by many publishers, when assuming multiple editions for a single book The Edition entity resolves books published more than once, by different publishers 54 Chapter 3
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Figure 3-15 shows a data diagram with authors on the right of the diagram and their respective publications on the left, in a one-to-many relationship. One author has two titles, another five titles, another three titles, two authors have one each, and two other authors have nothing published at all at least not in this database. Figure 3-15: One-to-many implies one entry to many entries between two tables. Many-to-Many A many-to-many relationship means that for every one record in one table there are many possible records in another related table, and visa versa (for both tables). The classic example of a many-to-many relationship is many students enrolled in many courses at a university. The implication is that every student is registered for many courses and every course has many students registered. The result is a many-tomany relationship between students and courses. This is not a problem as it stands; however, if an application or end-user must find an individual course taken by an individual student, a uniquely identifying table is required. Note that this new table is required only if unique items are needed by end-users or an application. In Figure 3-16, from left to right, the many-to-many relationship between PUBLISHER and PUBLICATION tables is resolved into the EDITION table. A publisher can publish many publications and a single publication can be published by many publishers. Not only can a single publication be reprinted, but other types of media (such as an audio tape version) can also be produced. Additionally those different versions can be produced by different publishers. It is unlikely that a publisher who commissions and prints a book will also produce an audio tape version of the same title. The purpose of the EDITION table is to provide a way for each individual reprint and audio tape copy to be uniquely accessible in the database. 1 2 3 4 5 6 7 9 10 11 8 12 7 7 7 7 7 7 7 7 7 7 15 16 2 2 3 3 3 3 3 4 4 4 6 7 Cities in Flight A Case of Conscience Foundation Second Foundation Foundation and Empire Foundation s Edge Prelude to Foundation Lucifer s Hammer Footfall Ringworld The Complete Works of Shakespeare Hocus Pocus PUBLICATION_ID SUBJECT_ID AUTHOR_ID TITLE 1 5 6 Orson Scott Card James Blish Isaac Azimov Larry Niven Jerry Pournelle William Shakespeare Kurt Vonnegut AUTHOR_ID NAME 2 3 4 7 53 Database Modeling Building Blocks
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

Figure 3-13 shows the equivalent of the one-to-one table structure representation using real data. ISBN numbers 198711905 and 345308999 both have RANK and INGRAM_UNITS value entries, and thus appear in the RANK table as unique records. In Figure 3-13, there is exactly one record in the EDITION table for every record in the RANK table, and visa versa. Figure 3-13: A one-to-one relationship implies exactly one entry in both tables. One-to-Many One-to-many relationships are extremely common in the relational database model between tables. Figure 3-14 shows that an AUTHOR table record can have many publications because an author can publish many books (PUBLICATION record entries). Figure 3-14: One-to-many implies one entry to many entries between two tables. Author author_id name Publication publication_id subject_id (FK) author_id (FK) title One-to-many implies that there can be many published works for each author 198711905 345308999 345366275 345468353 553278398 553293362 553293370 553293389 893402095 1585670081 5557076654 1150 1200 1800 2000 1900 1050 1950 1100 1850 1000 1250 188 140 Rank Edition 160 200 180 110 190 1 1 1 1 ISBN RANK INGRAM_UNITS 198711905 345308999 345366275 345468353 553278398 553293362 553293370 553293389 893402095 1585670081 5557076654 Hardcover Paperback Paperback Paperback Paperback Paperback Hardcover Audio Cassette ISBN FORMAT 52 Chapter 3
We recommend high quality webhost to host and run your jsp application: christian web host services.

Figure 3-11: A crow s foot represents the many sides of a one-to-many relationship. One-to-One One-to-one relationships are often created to remove frequently NULL valued fields from a table. They are hopefully rare in relational database models, unless in exceptional circumstances because the price of storage space is cheap. One-to-one relationships are typical of 4th Normal Form transformations (see Chapter 4) where potentially NULL valued fields are removed from the parent table, possibly saving storage space. Not only is storage space cheap in modern times, but variable-length records in most relational databases tend to make this type of normalization bad for performance. SQL code joins get bigger as more tables are created. SQL code with more tables in joins can cause serious performance issues. Bad database performance means slow applications and unhappy users looking to pay your competitors what they pay you. Figure 3-12 shows a one-to-one relationship between the EDITION and RANK tables such that for every EDITION entry, there is exactly one RANK entry, and visa versa. Figure 3-12: A one-to-one relationship implies exactly one entry in both tables. Edition ISBN publisher_id (FK) publication_id (FK) print_date pages list_price format Rank One-to-one implies that there is exactly one row in the Rank entity for every row in the Edition entity ISBN (FK) rank ingram_units Author author_id name Publication publication_id subject_id (FK) author_id (FK) title This is a Crows Foot! Each author has written and published many different books 51 Database Modeling Building Blocks
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

An ERD displays tables and relationships between those tables. Figure 3-10 shows an example ERD for tables in a schema containing published books. Figure 3-10 shows what an ERD is and what it looks like. ERDs are simple. There is nothing complicated or inscrutable about ERDs. A table is often referred to as an entity in an ERD. Figure 3-10: An Entity Relationship Diagram (ERD). At this point, you don t need to understand how relationships are created between the tables shown in Figure 3-10. Creating the relations is a little too advanced for this chapter and will be covered in later chapters, mostly in Chapter 4. Crows Foot A crow s foot is used to describe the many side of a one-to-many or many-to-many relationship, as highlighted in Figure 3-11. A crow s foot looks quite literally like the imprint of a crow s foot in some mud, with three splayed toes. (How many toes a crow has exactly I am not sure.) By now, you should get the idea that many toes implies more than one and thus many, regardless of how many toes a crow actually has. Figure 3-11 shows a crow s foot between the AUTHOR and PUBLICATION tables, indicating a one-to-many relationship between AUTHOR and PUBLICATION tables. Publisher publisher_id name Author author_id name Subject subject_id parent_id title Publication publication_id subject_id (FK) author_id (FK) title Review review_id publication_id (FK) review_id (FK) text CoAuthor coauthor_id (FK) publication_id (FK) Edition ISBN publisher_id (FK) publication_id (FK) print_date pages list_price format rank ingram_units 50 Chapter 3
Check Tomcat Web Hosting services for best quality webspace to host your web application.