Hernandez could improve on the first edition of Database Design for Mere Mortals I wouldn Database Design for Mere Mortals. 89 Pages·· KB· Michael J. Hernandez’s best-selling Database Design for Mere Mortals® has earned worldwide respect as the clearest, simplest way to learn relational database design. SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in. With John L. Viescas, he coauthored. [PDF] Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition) by Michael J. Hernandez ePub^.
|Language:||English, Spanish, German|
|Distribution:||Free* [*Sign up for free]|
for Mere. Mortals®. A Hands-on Guide to Relational. Database Design. Third Edition . Chapter 1: The Relational Database 3. Topics Covered in This Chapter 3. Praise for SQL Queries for Mere Mortals®, Second Edition. Unless you are Kenneth D. Snell, Ph.D., Database Designer/Programmer. I don't think they do this in . CHAPTER 3 A Concise History of SQL. Topics Covered. 3rd Edition. Addison-Wesley Professional, p. ISBN: 7. The 1 Easy, Commonsense Guide to Database Design Michael J. Hernandez .
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: 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: 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: Make certain that the table names and table descriptions are suitable and meaningful to everyone. Make certain that the field names 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: 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. Anatomy of a Field Specification o General Elements: Specification Information: 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.
The most common types of data are: Character Support. This element is used to indicate the characters that are permitted to be entered into the field. Any character other than letters and numbers that can be entered from the keyboard; and Special characters: Any character that must be entered by some means other than the keyboard, such as through a special software program. 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: 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: Values Entered By. This element indicates how values are entered into a field. Either the user will enter each value manually of the values will be entered automatically by the database application. Default Value. A default value is a value that used when an entry is required but not yet available, and when Nulls are not allowed.
You should only use a default value if it is meaningful. Range of Values. The Range of Values element lets you determine every possible value that can be entered into a field. Specification Information The elements under this category pertain to the nature of the field specification as a whole. Specification Type. A Field Specification falls into one of the following categories: Based on Existing Specifications. This element indicates whether any of the elements in this specification have drawn their settings from another field specification.
Source Specification. This element indicates the name of the generic Field Specification upon which the current specification is based. 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.
It helps to further refine table structures and minimize redundant data. It is the mechanism that allows data from multiple tables to be drawn together simultaneously.
Relationship-level integrity is established when a relationship is properly defined. Type of Relationships One-to-One Relationships. 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.
One-to-Many Relationships. 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, but a single record in the second table can be related to only one record in the first table.
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, and a single record in the second table can be related to one or more records in the first table. Problems with Many-to-Many Relationships Before you can use the data from tables involved in many-to-many relationships, several problems must be resolved: Both tables will contain some amount of duplicate data because of the redundancies.
It will be difficult to insert, update and delete data in the participating tables. Identifying Existing Relationships In the first step, youll identify the relationships that currently exist between the tables. Only look for direct relationships. Tables that are indirectly related will be implicitly connected through a third connecting table. Two types of questions that can be asked:. 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. In this type of relationship, one of the tables is referred to as the main table and assumes a dominant role in the relationship; the other table is referred to as the subordinate table.
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, where it becomes the Foreign key. 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. In this case, you take a copy of the Primary key from the table on the one side of the relationship and insert it into the table on the many side, where it becomes a Foreign key.
The Many-to-Many Relationship. A many-to-many relationship is established using a linking table. 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.
Next, you give the linking table a meaningful name, one that represents the nature of the relationship between the two tables.
Establishing Relationship Characteristics These characteristics indicate what will occur when a record is deleted, the type of participation each table bears within the relationship, and to what degree each table participates in the relationship. Establishing a Deletion Rule for Each Relationship This rule defines what will happen if a user wants to delete a record in the main table of a one-to-one relationship or in the one side of a one-to-many relationship.
This helps to guard against orphaned records, which are records that exist in a subordinate table of a one-to-one relationship but have no related records in a main table, or records that exist in the many side of a one-to-many relationship that have no related records in the one side. Two options are available for the deletion rule: The requested record cannot be deleted if there are related records in the subordinate table of a one-to-one relationship or the many side of a one-.
Related records must be deleted before the requested record can be deleted. The requested record will be deleted as well as related records in the subordinate table of a one-to-one relationship or the many side of a one-tomany relationship.
Identifying the Type of Participation for Each Table A tables type of participation determines whether a record must exist in that table before a record can be entered into the other table. 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. There is no requirement for any records to exist in this table before you can enter any records into the other table.
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. The degree of participation is symbolized by two numbers, separated by a comma, and enclosed in parentheses. In this instance 1,8 a single record in one table can be related to a minimum of one record in a maximum of eight records in the other table. Relationship-Level Integrity Relationship-level integrity is a result of properly establishing a table relationship and defining its characteristics in the proper manner.
Youll need to: Ensure your ability to insert new records into each table in a meaningful manner. Ensure your ability to delete an existing record without creating adverse affects. Establish a meaningful limit to the number of records that can be interrelated within the relationship. 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.
For example, a ship date cannot be prior to an order date for any given order. Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database. Application-oriented Business Rules are statements that impose constraints that cannot be established by modifying a Field Specification or relationship diagram; they must be established within the physical design of the database or within the design of a database application.
Business Rules under this category impose constraints on the elements of a Field Specification for a particular field. 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, which will depend on the way the organization functions. Youll need to work with users and management to define and establish the appropriate Business Rules. 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. Define the necessary Business Rules for the field. Establish the rules by modifying the appropriate Field Specifrication elements.
Determine what actions test the rule. Record the rule on a Business Rule Specification sheet. Establishing Relationship-Specific Business Rules o o o o o o Select a pair of tables that share a relationship. Review each relationship characteristic and determine whether a constraint is warranted by the way the organization functions. Define the necessary Business Rule.
Establish the rule by modifying the relationship characteristics. Determine what actions will test the rule. Record the rule on the Business Rule Specification sheet. 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. It commonly limits the range of values to a specific set of valid entries. In many cases, the set of values is made up of a relatively fixed number of entries with values that will rarely change.
These entries can be stored in a validation table. 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.
It does not store or contain data it gets its data from the tables upon which it is based. Only a Views structure is saved in the database. It is recreated each time it is accessed. Views can be based on a single table, multiple tables, other Views, or a combination of tables and views. There are three categories of Views: Aggregate Views are used to display information that is the result of aggregating a particular set of data in a specific manner.
An aggregate View includes one or more calculated fields that incorporate the functions that aggregate the data, and one or more data fields to group the aggregated data. Validation Views are similar to validation tables in that they are used to implement data integrity. Establishing Views o o o Use calculated fields where appropriate Impose criteria to filter the data Use a View specification sheet to record the View.
Reviewing Data Integrity Reviewing data integrity is simple if you sequentially review each component of the overall data integrity at the: Table Level. Make certain the table conforms with all of the following points: There are no calculated fields in the table. There are no multivalued fields in the table.
There are no multipart fields in the table. There are no duplicate records in the table. Every record in the table is identified by a Primary key value. Each Primary key conforms to the Elements of a Primary key. Field Level. Make sure field-level integrity has been properly established by making certain that: A set of Field Specifications has been defined for each field.
Relationship Level. Make certain that: Business-Rule Level. The proper category has been determined for the rule. Each rule is properly defined and established. The appropriate Field Specification elements or table relationship characteristics have been properly modified.
The appropriate validation tables have been established. A Business Rule Specification sheet has been completed for each rule. View Level. Each view has been assigned the appropriate fields. Each calculated field provides pertinent information or enhances the manner in which the data is displayed.
Each filter returns the appropriate set of records. Each View has a View diagram.
Each View is accompanied by a View Specifications sheet. Assembling the Database Documentation Throughout the database design process, many lists, specification sheets, and diagrams were generated. They should now be assembled into a central repository. The design repository should consist of the: Calculated field list; Table structure diagrams; Relationships diagrams; Business Rule Specifications sheets; View diagrams; and View Specifications sheets.
The design documentation is vital for three reasons: It provides a complete set of specifications and instructions on how the database should be created during the implementation process. It can be used to determine the effects and consequences of any modifications, should it become necessary to modify the database structure during the implementation process. Now that the integrity review is complete and youve assembled all of the documentation, the logical database design process is complete.
You can be sure that you have a properly designed database and that its implementation will proceed smoothly. This type of single-table design has many problems: Spreadsheet Design. A spreadsheet design is a good tool if used properly. It does not make a good relational database. Potential problems are: Bending or Breaking the Rules There are two specific circumstances under which it is permissible to break the rules of proper database design: Another common reason for breaking the rules, is when a multitable query is slow to process and the solution is to modify a table so that every field necessary for the query is included in the table.
Other ways to improve performance: If you still find it is necessary to break the rules of proper database design, you must document each rule you break and each action you take. The items that should be recorded include: In conclusion, you should now possess the knowledge and skills necessary to design a sound and reliable relational database structure. 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.
This book focuses only on the logical design of a database. Music Theory American History Instructor ID Types of Participation There are two types of participation that a table can have within a relationship: 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.
Two types of questions that can be asked: The requested record cannot be deleted if there are related records in the subordinate table of a one-to-one relationship or the many side of a one- to-many relationship. Establishing Views o o o Use calculated fields where appropriate Impose criteria to filter the data Use a View specification sheet to record the View Chapter Bilas Joseph.
April Cook. Laura Head. Naveenkumar Gajavalli. Anonymous h5CJOXc. Pratik Shah. Preetanjali Ray. Keerthi mathi. Leah Ojales. Dani Kirky Ylagan. Hamza S. Yelena Bytenskaya. Dushyant Singh. Nidhin Ramachandran. Deny Kurniawan. Adarsh Nair. Devarakonda Kondayya. Michael Hodges. Muhammad Bilal. Damith Srimobile. Michele Brady. More From Juan Osorio. Juan Osorio. Popular in Database. Journal of Computing. Zubear Mustafa. Zha Ieza. Anonymous jPgC3W.
Nikos Plevris. Kondala Rao. Karthik Ramya. Munir Thanwarani. Fabian Fuchile. Diego Andres Guffante Martinez. Wasif Saeed. Ravi Yalala. Suteja Sugi Mangunharjo. Upcoming SlideShare. Like this presentation? Why not share!
An annual anal Embed Size px. Start on. Show related SlideShares at end. WordPress Shortcode. Published in: Full Name Comment goes here. Are you sure you want to Yes No. Be the first to like this. No Downloads. Views Total views. Actions Shares. Embeds 0 No embeds. No notes for slide.