for Mere. Mortals®. A Hands-on Guide to Relational. Database Design. Third Edition SQL Queries for Mere Mortals® (Addison-Wesley, ). Mike has been. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition): Computer Science Books @ musicmarkup.info . Praise for SQL Queries for Mere Mortals®, Second Edition. Unless you are Kenneth D. Snell, Ph.D., Database Designer/Programmer. I don't think We created the third set of databases using the popular open-source.
|Language:||English, Spanish, Indonesian|
|ePub File Size:||19.60 MB|
|PDF File Size:||12.30 MB|
|Distribution:||Free* [*Sign up for free]|
3rd Edition. Addison-Wesley Professional, p. ISBN: 7. The 1 Easy, Commonsense Guide to Database Design Michael J. Hernandez . Database Systems: Design, Implementation, and Management, Ninth Edition. Database Design for Mere Mortals Database Design Third Edition Relational. [PDF] Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition) by Michael J. Hernandez ePub^.
The primary audience for Mere Mortals books is that of readers who have little or no background or formal training in the subject matter. Books in the series avoid dwelling on the theoretical but instead take you right into the heart of the topic with a matter-of-fact, hands-on approach. The books are not designed to address all the intricacies of a technology, but they do not avoid or gloss over complex, essential issues either. Instead they focus on providing core, foundational knowledge in a way that is easy to understand and that will properly ground you in the topic. This practical approach provides you with a smooth learning curve and helps you to immediately begin to solve your real world problems. It also prepares you for more advanced treatments of the subject matter should you decide to pursue them. And it even enables the books to serve as solid reference material for those of you with more experience.
This is a final opportunity to add tables to the preliminary table list. Defining the Final Table List Its time to transform the preliminary table list into the final table list. To do this, youll need to add two elements: table type and table description. There are four table types: o o o Data: This type of table stores data used to supply information. Linking: This table is used to establish a link between two tables in a many-tomany relationship.
Subset: This table contains supplemental fields that are related to a particular data table and further describe the subject of that table in a very specific manner. Validation: This type of table is used to implement data integrity.
Table descriptions are used to provide a clear definition of the subject represented by the table. One final task in developing the final table list is to refine the table names. Refining the Table Names There are certain guidelines that should be used when creating a table name. These guidelines ensure that a name is clear and unambiguous, that it is descriptive and meaningful, and that each table is named in a consistent manner.
Guidelines for creating a table name: o o o o o o o o Create a unique, descriptive name that is meaningful to the entire organization. Create a table name that accurately, clearly and unambiguously identifies the subject of the table.
Use the minimum number of words necessary to convey the subject of the table. Do not use words that convey physical characteristics. Do not use acronyms and abbreviations. Do not use proper names and other words that will unduly restrict the data that can be entered into the table. Do not use names that implicitly or explicitly identify more than one subject.
Use the plural form of the name in contrast, field names are always singular. Indicating the Tables Types Youll indicate each tables type on the final table list i. Each item on the list will be a data table because it represents a single subject that is important to the organization. Linking and validation tables will be missing because you have not yet defined any relationships or data integrity.
These issues will be addressed later in the design process. Subset tables are missing as well because they are defined after fields have been assigned to data tables.
Composing the Table Descriptions Table descriptions are important for understanding why each table exists and why the organization is concerned with collecting the data for each table.
It must explicitly define the table and state its importance. If you are unable to explain the importance of a table, youll need to further investigate when and how the table was identified and whether it really is necessary. Guidelines for Composing a Table Description: o o o o o o Include a definition statement that accurately identifies the table.
Include a statement that explains why this table is important to the organization.
Compose a description that is clear and succinct. Do not include implementation-specific information in your table description. Do not make the table description for one table dependent on the table description of another table. Do not use examples in a table description. Associating Fields with Each Table The next stage of the database design process is to assign fields to each table on the final table list.
These fields are taken from the preliminary field list. To assign fields to a table, determine which fields best represent characteristics of the tables subject and assign them to that table.
Repeat this procedure for every table on the final table list. To begin this process, write the names of each table across the top of the paper. Repeat this procedure, using as many sheets as you need to account for every table on the list. Assign fields from the preliminary field list to each table. Start with the first table and determine which fields best describe its subject.
List these fields under the table name. After assigning all fields you believe to be appropriate for the table, move on to the next table and repeat the process. Continue in this manner until youve assigned fields to all of the tables.
Refining the Fields This step is preparation for the process of refining the table structures, which entails establishing that the appropriate fields have been assigned to the table and that the table is structurally sound. Guidelines for Creating Field Names o o o o o o o Create a unique, descriptive name that is meaningful to the entire organization.
Create a name that accurately, clearly and unambiguously identifies the characteristic represented by the field. Use the minimum number of words necessary to convey the meaning of the characteristic the field represents. Do not use acronyms, and be discriminating in the use of abbreviations. Do not use words that could confuse the meaning of the field name.
Do not use names that implicitly or explicitly identify more than one characteristic. Use the singular form of the name. Using the Ideal Field to Resolve Anomalies Unless you know what to look for, its hard to determine whether any of the fields in a table is going to cause problems. The best way to identify potentially problematic fields is to determine whether they are in accordance with the Elements of the Ideal Field: o o o o o o It It It It It It represents a characteristic of the subject of the table.
Resolving Multipart Fields Multipart Fields are difficult to work with because they contain more than one item of data. Its hard to retrieve information from a multipart field and its hard to sort or group the records in the table by the fields value. To resolve this, you need only identify the separate items making up the field and treat each one as an individual field.
Resolving Multivalued Fields Multivalued fields bring about the same set of problems as do multipart fields. Unlike a multipart field, whose value represents two or more separate items, a multivalued field represents two or more occurrences of the same value. This poses a problem if you try to enter more occurrences of the value than the field will allow.
To resolve a multivalued field, first remove the field from the table and use it as the basis for a new table. Next use a field or set of fields from the original table to link the original table with the new table. Assign an appropriate name, type and description to the new table and add the table to the final table list.
Refining the Table Structures The objective in this phase of the design process is to make sure that the appropriate fields have been assigned to each table and that each tables structure is properly defined. Redundant Data and Duplicate Fields Redundant data is a value that is repeated in a field as a result of the fields use as a link between two tables, or is the result of some field or table anomaly. In the first instance, the redundant data is appropriate; by definition, fields used to link tables together contain redundant data.
In the second instance, the redundant data is entirely unacceptable because it poses problems with data consistency and data integrity. Duplicate fields are fields that appear in two or more tables for any of the following reasons: they are used to link a set of tables together, they indicate multiple occurrences of a particular type of value, or they are there as a result of a perceived need for supplemental information.
The only instance in which they are necessary is in the case of linking two tables together. It does not contain unnecessary duplicate fields. It contains only an absolute minimum amount of redundant data. Chapter 8 Why Keys are Important o o o They ensure that each record in a table can be properly identified. They help establish and enforce various types of integrity. They are used to establish table relationships.
When the keys are appropriate, you guarantee that the table structures are sound, redundant data within each table is minimal, and the relationships between tables are solid. Establishing Keys for Each Table The function of a key within a table is determined by the key type.
There are four types of keys: Candidate, Primary, Foreign and Non-keys. Candidate Keys. It is from the pool of available Candidate keys that a Primary key is chosen. It is a field or set of fields that uniquely identifies a single instance of the subject represented by the table. Elements of a Candidate Key: o o o o o o o o It must uniquely identify each record in the table. It must contain unique values. It cannot be null. It cannot be a multipart field. It comprises a minimum number of fields necessary to define uniqueness.
Its value is not optional in whole or in part. It must directly identify the value of each field in the table. Its value can only be modified in rare or extreme cases. Artificial Candidate Keys. If none of the fields in a table qualifies as a Candidate key, you can use an Artificial Candidate key. You establish an Artificial Candidate key by creating a new field that conforms to the elements of a Candidate key and then adding that field to the table. Primary Keys. The next step is to establish a Primary key.
A Primary key must conform to the exact same elements as the Candidate key. It is the key that officially identifies the table throughout the database structure. It is used to enforce table-level integrity, helps establish relationships with other tables and accurately identifies and refers to a particular record within the table. Here are a couple of guidelines to use when selecting an appropriate Primary key: o o If you have a simple single field Candidate key and a Composite Candidate key, choose the simple Candidate key.
Choose the Candidate key that uses a field that incorporates part of the table name within its name. Choose the key that you believe is the most meaningful to everyone in the organization. Elements of a Primary Key o o o o o o o o It must uniquely identify each record in the table.
It should contain the minimum number of fields necessary to define uniqueness. It is not optional in whole or in part. Each Primary key within the database should be unique no two tables should have the same Primary key unless one of them is a subset table. Table-Level Integrity o o o o There are no duplicate records in a table. Every record in a table is identified by a Primary key value. Every Primary key value is unique. Primary key values are not null. Reviewing the Initial Table Structures Once the fundamental table definitions are complete, youll need to conduct interviews with users and management to review the work youve done so far.
During these interviews, you will: o o Ensure that the appropriate subjects are represented in the database. Make certain that the table names and table descriptions are suitable and meaningful to everyone. Verify that all the appropriate fields are assigned to each table. Chapter 9: Field Specifications Why Field Specifications are Important o o o o Field-level integrity is established and enforced as a result of defining Field Specifications.
Defining Field Specifications for each field enhances overall data integrity. Defining Field Specifications compels you to acquire a complete understanding of the nature and purpose of the data in the database. Field-Level Integrity A field has field-level integrity after a full set of Field Specifications has been defined for the field. Field Specifications help to ensure that: o o o o The identity and purpose of each field is clear, and that all of the tables in which it appears are properly identified.
Field definitions are consistent throughout the database. The values of the field are consistent and valid. The types of modifications, comparisons and operations that can be applied to the values in the field are clearly identified. General Elements Field Name. The field name is the unique identifier for the field itself. It is the set of absolute minimal words used to identify a particular field throughout the database.
A label is an alternate name for the field that may be used to identify the field in a RDBMS program. For example, a label for a field named Quantity on Hand might be Qty on Hand. A label is typically a shorter form of the Field Name. Parent Table. A field represents a characteristic of a particular tables subject. The table that represents this subject is referred to as the parent table of the field, and it is the only table in which the field will appear.
Shared By. This element is used to list the names of other tables that share this field. The only table names that should appear here are those that use this field as a connection to its parent table. Alias es.
An alias is a name that a field assumes under very rare circumstances. One instance when a field would use an alias is when there must be two occurrences of the field in the same table. In the description you provide a complete interpretation of the field. Guidelines for Composing a Field Description o o o o o o o Use a statement that accurately identifies the field and clearly states it purpose Write a statement that is clear and succinct Refrain from restating or rephrasing the field name Avoid using technical jargon, acronyms, or abbreviations Do not include implementation-specific information Do not make this description statement dependent on the description of another field Do not use examples Physical Elements The Physical Elements category pertains to the structure of a field.
Data Type. In the Data Type setting you indicate the nature of the data that is stored in the field. This element is used to indicate the characters that are permitted to be entered into the field. The total number of characters that can be entered into a particular field is indicated by this element. The maximum number of characters you allow for a field will depend on the RDBMS program you use to implement the database.
Decimal Places. The number of digits to the right of the decimal point is indicated by this element. Input Mask. This element is used to indicate the manner in which the data should be entered into the field. Controlling the way data is entered results in consistent entries within the field. Display Format. This element allows you to indicate how the value of the field should be presented. Logical Elements This category of elements pertains to the values of the field.
These elements indicate whether the values should be unique, when they should be entered, whether they can be edited, the types of comparisons and operations that can be performed on the values, and the range of acceptable values that can be entered into the field.
Type of Key. In the Type of Key element you indicate the role of the values within the field, e. This element determines whether the value of a field should be unique. In the case of a Primary key, this element will be set to Unique. Required Value. Whether a user must enter a value into a given field is indicated by this element. Null Support. This element indicates whether null values should be allowed in the field. Edit Rule. This element indicates at what point in time a value must be entered into the field and whether that value can be modified.
Comparisons Allowed. The types of comparisons that can be made to a particular value of this field are indicated by the Comparisons Allowed element. There are six types of comparisons: equal to, not equal to, greater than, less than, greater than or equal to, and less than or equal to. Operations Allowed. This element indicates the types of operations that can be performed on the values in the field. Four types of operations are allowed: addition, subtraction, multiplication and division.
Please try again later.
Paperback Verified download. First off, this book is well-written. Hernandez does a good job of explaining the "why" behind his explanations. He also provides tips that just seem to make sense. He advises that a table have a name that is plural, because each record is one "instance" of the data related to the table.
As an example, a table called CarColors would have several rows, each containing one car color. My only real criticisms are: There was not enough discussion of the various "normalized" forms, when they should be used, and so forth. This is covered in a "flyover" manner that makes sense, but perhaps a more "in-depth" discussion of normalized forms is beyond the scope of the book.
I felt like I knew most of the information, but I didn't feel this book was a waste of money, as many of his "common sense" approaches I will utilize in my own DB designs in the future.
Thus far I see no difference between this and the 2nd Edition, so if you already have that version you don't need this one. That being said, it doesn't make this edition any less deserving of five stars. My professor, a full time Software Architect, recommended this book to me four years ago. Now, as a successful Software Developer, I'm recommending it as well. Based on my experience, this book covers everything one needs to know about database design. Kindle Edition Verified download.
I found this book very thorough at an introductory level and well written. I think someone with no database experience at all could read this and help with database design.
The only criticism I have is the verbosity. Some things are repeated or can be assumed. This book does not get into normalization or any details, which I liked. I like that book. It was easy to read, unfortunately there is to much of copy paste. I would rate it 5 stars if book took just pages, seriously there is not much in order to make pages. But approach brought by author is interesting, anyway I would like to ask the question.
How good does this approach fit at with agile, scrum? I am not sure that I'll get back to read this book one more time, which is really important sign for me, maybe because it is easy book. What I don't like very much, author mention that competitor authors don't know something, well This book provides the basic concepts for good relational database design. It is structured in a very easy to read format. It clearly walks you thought design objectives and defining tables, keys, fields, table relationships, as well as business rules.
I wish more technical primers were written like this. All new terms are clearly explained. One person found this helpful. No, this is not the book for you if you have extensive experience in software design and have already absorbed the basic concepts of database design by osmosis.
This is a book for "mere mortals," just like it says. I'm a reasonably smart, reasonably well-educated person who had some computer experience, but didn't even really know what a relational database was. Through circumstances too odd to mention, I was given primary responsibility for designing a commercial web application.
I read this book cover to cover, and designed the database that became the core of the product. The product actually works, and seems to be selling. Later in the development cycle, I worked with a brilliant MIT-trained software engineer, and he found my basic db design just ducky. I definitely owe Mr. Hernandez a beer. Any character other than letters and numbers that can be entered from the keyboard. Any character that must be entered by some means other than the keyboard.
Controlling the way data is entered results in consistent entries within the field. The maximum number of characters you allow for a field will depend on the RDBMS program you use to implement the database. Primary keys. This element is used to indicate the manner in which the data should be entered into the field. This element determines whether the value of a field should be unique.
Whether a user must enter a value into a given field is indicated by this element. Foreign keys. Numbers This element indicates the types of operations that can be performed on the values in the field. This element indicates whether any of the elements in this specification have drawn their settings from another field specification. This element indicates how values are entered into a field.
This element indicates at what point in time a value must be entered into the field and whether that value can be modified. This element indicates the name of the generic Field Specification upon which the current specification is based. Specification Information The elements under this category pertain to the nature of the field specification as a whole. A default value is a value that used when an entry is required but not yet available.
A Field Specification falls into one of the following categories: The Range of Values element lets you determine every possible value that can be entered into a field. Either the user will enter each value manually of the values will be entered automatically by the database application.
Source Specification. You should only use a default value if it is meaningful. Four types of operations are allowed: There are six types of comparisons: The types of comparisons that can be made to a particular value of this field are indicated by the Comparisons Allowed element.
Two types of questions that can be asked: Problems with Many-to-Many Relationships Before you can use the data from tables involved in many-to-many relationships. Many-to-many relationships exist between a pair of tables if a single record in the first table can be related to one or more records in the second table.
Many-to-Many Relationships. Both tables will contain some amount of duplicate data because of the redundancies. Type of Relationships One-to-One Relationships. Chapter Table Relationships A relationship is a crucial part of the database because o o o o It establishes a connection between a pair of tables that are logically related to each other in some form.
A pair of tables are defined as having a one-to-one relationship if a single record in the first table is related to one and only one record in the second table. It is the mechanism that allows data from multiple tables to be drawn together simultaneously.
Identifying Existing Relationships In the first step. A one-to-many relationship is defined as one in which a single record in the first table can be related to one or more records in the second table. Only look for direct relationships. It helps to further refine table structures and minimize redundant data. Relationship-level integrity is established when a relationship is properly defined.
Tables that are indirectly related will be implicitly connected through a third connecting table. One-to-Many Relationships. It will be difficult to insert. You create the linking table by taking a copy of the Primary key from each table involved in the relationship and using those Primary keys to create the new linking table. Establishing Relationship Characteristics These characteristics indicate what will occur when a record is deleted.
A many-to-many relationship is established using a linking table. The One-to-Many Relationship. The technique used to establish a one-to-many relationship is similar to the one used to establish a one-to-one relationship. The Many-to-Many Relationship. In this type of relationship. Two options are available for the deletion rule: A one-to-one relationship is established by taking a copy of the Primary key from the main table and inserting it into the subordinate table.
In this case. Can a single record in name of first table be associated with one or more records in name of second table? This type of question contrasts a single instance of the subject represented by the first table against multiple instances of the subject represented by the second table.
Relationship-Level Integrity Relationship-level integrity is a result of properly establishing a table relationship and defining its characteristics in the proper manner.
There are two types of participation: There must be at least one record in this table before you can enter any records into the other table. Ensure your ability to insert new records into each table in a meaningful manner. Establish a meaningful limit to the number of records that can be interrelated within the relationship.
Ensure your ability to delete an existing record without creating adverse affects. Identifying the Degree of Participation for Each Table This is a matter of identifying the total number of records in one table that can be related to a single record in the other table. Business Rules A Business Rule is a statement that imposes some form of constraint on elements within a field specification for a particular field or on characteristics of a relationship between a specific pair of tables. Related records must be deleted before the requested record can be deleted.
The degree of participation is symbolized by two numbers. In this instance 1. There is no requirement for any records to exist in this table before you can enter any records into the other table. Establish the rules by modifying the appropriate Field Specifrication elements.
Field-Specific Business Rules o o o o o o Select a table. Review each field and determine whether you need to impose any constraints on it.
Relationship-Specific Business Rules: Constraints imposed by relationshipspecific business rules affect the characteristics of a relationship between a particular pair of tables. Defining and Establishing Business Rules These rules are based on the manner in which your organization perceives and uses its data. Record the rule on a Business Rule Specification sheet. Review each relationship characteristic and determine whether a constraint is warranted by the way the organization functions.
Application-oriented Business Rules are statements that impose constraints that cannot be established by modifying a Field Specification or relationship diagram. Define the necessary Business Rule. Record the rule on the Business Rule Specification sheet. Establishing Relationship-Specific Business Rules o o o o o o Select a pair of tables that share a relationship. Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database.
Determine what actions will test the rule. Business Rules under this category impose constraints on the elements of a Field Specification for a particular field. Establish the rule by modifying the relationship characteristics. Define the necessary Business Rules for the field. Determine what actions test the rule. Aggregate Views are used to display information that is the result of aggregating a particular set of data in a specific manner.
It is recreated each time it is accessed. It commonly limits the range of values to a specific set of valid entries. There are no multivalued fields in the table. Validation Tables A validation table holds data specifically used to implement data integrity. There are instances where a rule affects the range of values for a particular field.
Views A View is a virtual table that comprises the fields of one or more tables in the database it may also contain fields from other views. These entries can be stored in a validation table. Make certain the table conforms with all of the following points: Views can be based on a single table. In many cases. It does not store or contain data — it gets its data from the tables upon which it is based.
There are three categories of Views: Reviewing Data Integrity Reviewing data integrity is simple if you sequentially review each component of the overall data integrity at the: Table Level. An aggregate View includes one or more calculated fields that incorporate the functions that aggregate the data. Validation Views are similar to validation tables in that they are used to implement data integrity.
There are no calculated fields in the table. A set of Field Specifications has been defined for each field. Make certain that: Field Level. The appropriate Field Specification elements or table relationship characteristics have been properly modified. Each filter returns the appropriate set of records. The appropriate validation tables have been established.
Each View is accompanied by a View Specifications sheet. Field Specifications sheet. There are no duplicate records in the table. Each View has a View diagram. The design repository should consist of the: Each calculated field provides pertinent information or enhances the manner in which the data is displayed.
The proper category has been determined for the rule. Business-Rule Level. Every record in the table is identified by a Primary key value. They should now be assembled into a central repository. Assembling the Database Documentation Throughout the database design process. Each Primary key conforms to the Elements of a Primary key. View Level. Make sure field-level integrity has been properly established by making certain that: A Business Rule Specification sheet has been completed for each rule.
Each view has been assigned the appropriate fields. Relationship Level.
Each rule is properly defined and established. Potential problems are: This type of single-table design has many problems: You can be sure that you have a properly designed database and that its implementation will proceed smoothly. Business Rule Specifications sheets.
It does not make a good relational database. It can be used to determine the effects and consequences of any modifications. Relationships diagrams. The design documentation is vital for three reasons: View diagrams. Bending or Breaking the Rules There are two specific circumstances under which it is permissible to break the rules of proper database design: A spreadsheet design is a good tool if used properly. It provides a complete set of specifications and instructions on how the database should be created during the implementation process.
Another common reason for breaking the rules. Table structure diagrams. The anticipated effects on the database and the application program. If you still find it is necessary to break the rules of proper database design.
Review the database structure. The method by which you make the modifications. Other ways to improve performance: Review the implementation of the database. The items that should be recorded include: The design principle you are violating. In conclusion. Fine-tune the operating system. Flag for inappropriate content. Related titles. Jump to Page. Search inside document. Hernandez Introduction It is important to have a properly designed database so that accurate information can be provided to an organization.
Keerthi mathi. Preetanjali Ray. Leah Ojales. Laura Head. April Cook. Bilas Joseph. Anonymous h5CJOXc. Naveenkumar Gajavalli. Pratik Shah. Devarakonda Kondayya.
Adarsh Nair. Muhammad Bilal. Michael Hodges. Michele Brady. Damith Srimobile. Dani Kirky Ylagan. Yelena Bytenskaya. Hamza S. Deny Kurniawan. Nidhin Ramachandran. Dushyant Singh. More From Juan Osorio. Juan Osorio. Popular in Information Retrieval. Yamuna Jayabalan.