tailieunhanh - Beginning Database Design- P22

Beginning Database Design- P22:This book focuses on the relational database model from a beginning perspective. The title is, therefore, Beginning Database Design. A database is a repository for data. In other words, you can store lots of information in a database. A relational database is a special type of database using structures called tables. Tables are linked together using what are called relationships. You can build tables with relationships between those tables, not only to organize your data, but also to allow later retrieval of information from the database | Advanced Database Structures and Hardware Resources Hash Keys and ISAM Keys There are other less commonly used indexes such as hash keys and Indexed Sequential Access Method ISAM keys. Both are somewhat out of date in the larger-scale relational database engines however Microsoft Access does make use of a mixture of ISAM BTree indexing techniques in its JET database. Both ISAM and hash indexes are not good for heavily changing data because their structures will overflow with newly introduced records. Similar to bitmap indexes hash and ISAM keys must be rebuilt regularly to maintain their advantage in processing speed advantage. Frequent rebuilds minimize on performance killing overflow. Clusters Index Organized Tables and Clustered Indexes Clusters are used to contain fields from tables usually a join where the cluster contains a physical copy of a small portion of the fields in a table perhaps the most commonly accessed fields. Essentially clusters have been somewhat superseded by materialized views. A clustered index index organized table or IOT is a more complex type of a cluster where all the fields in a single table are reconstructed not in a usual heap structure but in the form of a BTree index. In other words for an IOT the leaf blocks in the diagram shown in Figure 13-3 would contain not only the indexed field value but also all the rest of the fields in the table not just the primary key values . Understanding Auto Counters Sequences are commonly used to create internally generated transparent counters for surrogate primary keys. Auto counters are called sequences in some database engines. This command would create a sequence object CREATE SEQUENCE BAND_ID_SEQUENCE START 1 INCREMENT 1 MAX INFINITY Then you could use the previous sequence to generate primary keys for the BAND table see Figure 13-1 as in the following INSERT command creating a new band called The Big Noisy Rocking Band. INSERT INTO BAND BAND_ID GENRE_ID BAND FOUNDING_DATE VALUES .