Archive for the 'MySql' Category


The basic principles of relational database design. Part 6

Tuesday, July 15th, 2008

<< Part 5

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Elements of a Foreign Key

  1. It has the same name as the Primary key from which it was copied.
  2. It uses a replica of the field specifications for the Primary key from which it was copied.
  3. It draws its value from the Primary key to which it refers.

Establishing a Deletion Rule for Each Relationship

  1. Restrict. The requested record cannot be deleted if there are related records.
  2. Cascade. The requested record will be deleted as well as all related records.

Identifying the Type of Participation for Each Table

  1. Mandatory. There must be at least one record in this table before you can enter any records into the other.
  2. Optional. There is no requirement for any records to exist in this table before you can enter any records into the other table.

Relationship-Level Integrity

  1. Made certain that the connection between two tables in a relationship is sound.
  2. Ensured your ability to insert new records into each table in a meaningful manner.
  3. Ensured your ability to delete an existing record without creating adverse affects.
  4. Established a meaningful limit to the number of records that can be interrelated within the relationship.

What Are Business Rules?

A business Rule is 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 table.

Examples:

  • A Ship Date cannot be prior to an Order date for any given order.
  • A student cannot have more than two instruments checked out at the same time.

Types of Business Rules

  1. Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database.
  2. Application-oriented Business Rules are those that impose constraints that cannot be established by modifying a Field Specification or relationship diagram.

Determining and Establishing Views

There are three categories of Views: date Views, aggregate Views, and validation Views.

  1. Data Views are used to examine and manipulate data from base tables.
  2. Aggregate Views are used to display information that is the result of aggregating a particular set of data in a specific manner.
  3. Validation Views are used to implement data integrity. When a Business Rule limits the range of values of a particular field, you can enforce the constraint just as easily with a validation View as you can with a validation table.

Using a View Specifications Sheet to Record the View

Reviewing and Refining Data Integrity

  1. At the table level:
    There are no duplicated fields in the table.
    There are no calculated fields in the table.
    There are no multi-valued fields in the table.
    There are no multipart fields in the table.
    There are no duplicated 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.
  2. At the field level:
    Each field conforms to the Elements of the Ideal Field.
    A set of Field Specifications has been defined for each field.
  3. At the relationship level:
    The table relationship is properly established.
    The appropriate deletion rule has been established.
    The type of participation has been correctly identified.
    The proper degree of participation has been established.
  4. At the level of business rules:
    Each rule imposes a meaningful constraint.
    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.
  5. At the level of views:
    Each View contains the base tables necessary to provide the required information.
    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 is accompanied by a View Specification sheet.

Assembling the Database Documentation

  1. Final table list.
  2. Relationship diagrams
  3. Field Specifications sheets.
  4. Business Rule Specifications sheets.
  5. Calculated field list.
  6. View diagrams.
  7. Table structure diagrams.
  8. View Specifications sheets.

<< Part 5


The basic principles of relational database design. Part 5

Monday, July 14th, 2008

<< Part 4

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Table level integrity

  1. There are no duplicate records in a table;
  2. Every record in a table is identified by a Primary key value;
  3. Every Primary key value is unique;
  4. Primary key values are not null;

Reviewing the Initial Table Structure

  1. Ensure that the appropriate subjects are represented in the database;
  2. Make certain that the table names and table descriptions are suitable and meaningful to everyone;
  3. Make certain that the field names are suitable and meaningful to everyone;
  4. Verify that all the appropriate fields are assigned to each table;

Why Field Specifications are Important

  1. Field-level integrity is established and enforced as a result of defining Field Specifications.
  2. Defining Field Specifications for each field enhances overall data integrity.
  3. Defining Field Specifications compels you to acquire a complete understanding of the nature and purpose of the data in the database.
  4. Field Specifications are valuable when you implement the database in an RDBMS program.

Anatomy of a Field Specification

  1. General Elements: Field Name, Parent Table, Label, Shared By, Alias(es), Description.
  2. Physical Elements: Data Type, Character Support, Length, Decimal Places, Input Mask, Display Format.
  3. Logical Elements: Type of Key, Uniqueness, required Value, Null Support, Edit Rule, Comparisons Allowed, Values Entered By, Default Value, Range of Values.
  4. Specification Information: Specification type, Based on Existing Specification, Source Specification.

Three Types of Relationships:

  1. One-to-One
  2. One-to-Many
  3. Many-to-Many

Problems with Many-to-Many Relationships

  1. One of the tables involved in the relationship will contain a large amount of redundant data.
  2. Both tables will contain some amount of duplicate data because of the redundancies.
  3. It will be difficult to insert, update, and delete data in the participating tables.

Figure 02. The many-to-many relationship established between the Student and Classes tables.

>> Part 6


The basic principles of relational database design. Part 4

Sunday, July 13th, 2008

<< Part 3

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Why Keys Are Important

  1. They ensure that each record in a table can be properly identified.
  2. They help establish and enforce various types of integrity.
  3. They are used to establish table relationships.

Establishing Keys for Each Table

There are four maim types of keys: candidate, primary, foreign, non-keys.
A key’s type determines its function within the table.

Elements of a Candidate Key

  1. It must uniquely identify each record in the table.
  2. It must contain unique values.
  3. It cannot be null.
  4. It cannot be a multi-part field.
  5. It comprises a minimum number of fields necessary to define uniqueness.
  6. Its value is not optional in whole or in part.
  7. Its must directly identify the value of each field in the table.
  8. Its value can only be modified in rare or extreme cases.

Artificial Candidate Keys

If none of the fields in a table, either singularly or as a set, qualifies as a Candidate key. “Artificial” is used in the sense that a Candidate key does not “naturally” occur in the table; you have to manufacture one.

Primary Keys
If you have a “simple” (single field) Candidate key and a Composite Candidate key, choose the “simple” Candidate key. It’s always best to use a Candidate key that contains the least number of fields.
Choose the Candidate key that uses a field that incorporates part of the table name within its name. For example, a Candidate key comprising Sales Invoice Number is the good choice for the SALES INVOICES table.

Elements of a Primary Key

  1. It must uniquely identify each record in the table.
  2. It must contain unique values.
  3. It cannot be null.
  4. It cannot be a multipart value.
  5. It should contain the minimum number of fields necessary to define uniqueness.
  6. It is not optional in whole or in part.
  7. It must directly identify the value of each field in the table.
  8. Its value can only be modified in rare or extreme cases.

Rules for Establishing a Primary Key

  1. Each table must have one and only one Primary key.
  2. 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.

<< Part 5


The basic principles of relational database design. Part 3

Saturday, July 12th, 2008

<< Part 2

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Indicating the Tables Type

The four classification used to identify the table type are data, linking, subset, and validation.

Guidelines for Composing a Table Description

  1. Include a definition statement that accurately identifies the table.
  2. Include a statement that explains why this table is important to the organization.
  3. Compose a description that is clear and succinct. Don’t be too brief or too verbose.
  4. Do not include implementation-specific information in your table description, such as how or where the table is used.
  5. Do not make the table description for one table dependent on the table description of another table.
  6. Do not use examples in a table description.

Interviewing Users and Management

In order to define a good table description for each table, you’ll enlist the help of users and management to establish the table’s definition and importance.

Associating Fields with Each Table

Determine which fields best represent characteristics of the table’s subject and assign them to that table.

Guidelines for Creating a Field Names

  1. Create a unique, descriptive name that is meaningful to the entire organization. There should be only one occurrence of a field name in the entire database. (There is only one exception: when a field is used to establish a relationship between two tables.)
  2. Create a name that accurately, clearly, and unambiguously identifies the characteristic represented by the field.
    Bad Example: “Phone number”; Good Example: “Home Phone”.
  3. Use the minimum number of words necessary to convey the meaning of the characteristic the field represents.
    Bad Example: “Date that employee was hired “; Good Example: “Date Hired”.
  4. Do not use acronyms and abbreviations.
    Bad Example: “Address “; Good Example: “CustAddress”.
  5. Do not use words that could confuse the meaning of the field name.
    Bad Example: “Digital Identification Code Number “; Good Example: “Identification Code”.
  6. Do not use names that implicitly or explicitly identify more than one characteristic.
    Bad Example: “Type & Description “
  7. Use the singular form of the name.

Elements of the Ideal Field

  1. It represents a characteristic of the subject of the table. Subject can be an object or event.
  2. It contains only a single value. Two problems rise with fields that contain more than one value (commonly known as multi-valued fields): data redundancy and difficulty in working with data in the field.
  3. It cannot be deconstructed into smaller components (multi-part field).
  4. It does not contain a calculated or concatenated value.
  5. It is unique within the entire database structure.
  6. It retains all of its characteristics if it appears in more than one table.

Resolving Multi-part Fields

Working with a multipart field is difficult because its value contains two or more distinct items. It’s hard to retrieve information from a multipart field, and it’s hard to sort a group the records in the table by the field’s value.

Resolving Multi-valued Fields

A multi-valued field can potentially store two or more occurrences of the same value. Fortunately, you will recognize a multi-valued field when you see one. The field’s name is often plural and it’s value almost invariably contains a number of commas, which serve to separate the various occurrences that exist within the value itself.

>> Part 4


The basic principles of relational database design. Part 2

Friday, July 11th, 2008

<< Part 1

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Everything has a beginning, and the database design process is no different. Interestingly enough, you start the process by defining the end result.

Figure 1. Each of the tables is represented by a rectangle. The diamand represents the fact that there is a relationship between these two tables.

Guidelines for Creating a Table Names

  1. Create a unique, descriptive name that is meaningful to the entire organization. Choose names that are descriptive enough to be self-explanatory.
  2. Good Example: “Vehicle Maintenance”.

  3. Create a table name that accurately, clearly, and unambiguously identifies the subject of the table.
  4. Bad Example: “Pieces”, Good Example: “Engine Components” and “Body Components”.

  5. Use the minimum number of words necessary to convey the subject of the table.
  6. Bad Example: “TD_1″ or “Multi-Use Vehicle Equipment”, Good Example: “Equipment”.

  7. Do not use words that convey physical characteristics.
  8. Bad Example: “File”, “Table”, “Record”; Good Example: “Patient Record”.

  9. Do not use acronyms and abbreviations.
  10. Bad Example: “SC”; Good Example: “Security Codes”.

  11. Do not use proper names and other words that will unduly restrict the data that can be entered into the table.
  12. Bad Example: “Southwest Region Employees”.

  13. Do not use names that implicitly or explicitly identify more than one subject.
  14. Bad Example: “Facility/Building”.

  15. Use the plural form of the name. Table represents a collection of similar objects or events. By following this rule it’s easy to differentiate between table names and field names.
  16. Bad Example: “Home Phone”; Good Example: “Home Phones”.

>> Part 3


The basic principles of relational database design. Part 1

Friday, July 11th, 2008

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

An important aspect of most every business is record keeping. In our information society, this has become an important aspect of business, and much of the world’s computing power is dedicated to maintaining and using databases.

Databases of all kinds pervade almost every business. All kinds of data, from emails and contact information to financial data and records of sales, are stored in some form of a database. The quest is on for meaningful storage of less-structured information, such as subject knowledge.

The most important steps of the relational database design:

  1. Define mission statement and mission objectives for the database. The mission statement defines the purpose of the database. The mission objectives define the tasks that are to be performed by users against the data in the database.
  2. Analyze the current database. You identify the data requirements of the organization by reviewing the way data is currently collected and presented and by conducting interviews with users and management to determine how they use the database on a daily basis.
  3. Create the data structures. You establish tables by identifying the subjects that will be tracked by the database. Next you assign each table fields that best characterize its subject, and you designate a Primary key as well. Then you establish field specifications for every field in the table.
  4. Determine and establish table relationship. You’ll identify relationships that exist between the tables in the database and then establish the logical connection for each relationship using Primary keys and Foreign keys, or linking tables. Finally, you’ll set the various characteristics for each relationship.
  5. Determine and define Business Rules. Next you conduct interviews with users and management to identify constraints on the data in the database based on the way the organization views and uses its data. These constraints are then declared as Business Rules, which will serve to establish various levels of data integrity.
  6. Determine and establish Views. Users and management are interviewed to identify the various ways they look at the data in the database. After these various perspectives have been identified, you establish them as Views. Each View is defined using the appropriate table or tables, and certain Views use criteria that limit the records that they display.
  7. Review data integrity. This phase involves four steps. First, you review each table to ensure that it meets proper design criteria. Second, you review and check all field specifications. Third, you test the validity of each relationship. Fourth, you go over and confirm the business rules.

>> Part 2


HTML, CSS, PHP, and More Cheat Sheets

Thursday, July 10th, 2008

A great collection of cheat sheets for HTML, CSS, PHP, search engines, and more. Excellents helpers. Wish I’d had this list a long time ago.

Cheat Sheets Link