Thursday 4 November 2010

Normalisation

2NF

When a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.
Multiple candidate keys occur in the following table:

Electric Toothbrush Models
Manufacturer
Model
Model Full Name
Manufacturer Country
Forte
X-Prime
Forte X-Prime
Italy
Forte
Ultraclean
Forte Ultraclean
Italy
Dent-o-Fresh
EZbrush
Dent-o-Fresh EZBrush
USA
Kobayashi
ST-60
Kobayashi ST-60
Japan
Hoch
Toothmaster
Hoch Toothmaster
Germany
Hoch
X-Prime
Hoch X-Prime
Germany

Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:

Electric Toothbrush Manufacturers
Manufacturer
Manufacturer Country
Forte
Italy
Dent-o-Fresh
USA
Kobayashi
Japan
Hoch
Germany

Electric Toothbrush Models
Manufacturer
Model
Model Full Name
Forte
X-Prime
Forte X-Prime
Forte
Ultraclean
Forte Ultraclean
Dent-o-Fresh
EZbrush
Dent-o-Fresh EZBrush
Kobayashi
ST-60
Kobayashi ST-60
Hoch
Toothmaster
Hoch Toothmaster
Hoch
X-Prime
Hoch X-Prime

 

BCNF

Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF.
Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.

A relation is in BCNF, if and only if, every determinant is a candidate key.


4NF

Multivalued Dependency
If the column headings in a relational database table are divided into three disjoint groupings X, Y, and Z, then, in the context of a particular row, we can refer to the data beneath each group of headings as x, y, and z respectively. A multivalued dependency X-->>Y signifies that if we choose any x actually occurring in the table (call this choice xc), and compile a list of all the xcyz combinations that occur in the table, we will find that xc is associated with the same y entries regardless of z.

A trivial multivalued dependency X-->>Y is one where either Y is a subset of X, or X and Y together form the whole set of attributes of the relation.

A functional dependency is a special case of multivalued dependency. In a functional dependency X-->Y, every x determines exactly one y, never more than one.


Consider this example of a database of teaching courses, the books recommended for the course, and the lecturers who will be teaching the course:

Teaching database
Course
Book
Lecturer
AHA
Silberschatz
John D
AHA
Nederpelt
William M
AHA
Silberschatz
William M
AHA
Nederpelt
John D
AHA
Silberschatz
Christian G
AHA
Nederpelt
Christian G
OSO
Silberschatz
John D
OSO
Silberschatz
William M

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.
Put formally, there are two multivalued dependencies in this relation: {course}-->>{book} and equivalently {course}-->>{lecturer}.


Example
For an example see the ‘Example’ section on http://en.wikipedia.org/wiki/Fourth_normal_form


My Normalisation Guide

1NF
All attributes must be atomic.
All instances of an entity must contain the same number of values.
All instances of an entity must be different from one another.
2NF
Only relevant when a composite candidate key (primary key or other candidate key) exists in the entity.
The entity must be in 1NF.
All attributes must be a fact about the entire key.
3NF
The entity must be in 2NF.
Non-key attributes cannot describe other non-key attributes.
BCNF
Only in rare cases 3NF entity does not satisfy BCNF (3NF table with two or more overlapping candidate keys may or may not be in BCNF).
Every determinant is a key.
If a table has no non-key attributes then it satisfy all previous normal forms up to BCNF.
4NF
The entity must be in BCNF.
Either every multivalued dependency X -->> Y is trivial or for every nontrivial multivalued dependency X -->> Y, X is a superkey (or in other words, no attribute can have multiple values issue in the entity).
5NF
The entity must be in 4NF.
Try to breakdown any ternary relationship into binary relationships.

Friday 15 October 2010

Dynamic Data Features

How to add Dynamic Data to an existing site

1.       Copy ‘DynamicData’ folder, site.css, and site.master files.
2.       Add references to ‘System.ComponentModel.DataAnnotations’, ‘System.Web.Routing’, and ‘System.Web.DynamicData’.
3.  Modify global.asax file
protected void Application_Start(object sender, EventArgs e)
        {
            // Create an instance of the data model.
            MetaModel DefaultModel = new MetaModel();
            // Register the data model.
            DefaultModel.RegisterContext(typeof(
              AdventureWorksLT_DataEntities),
              new ContextConfiguration() { ScaffoldAllTables = false });

            // Create the routes.     
            RouteTable.Routes.Add(new
                DynamicDataRoute("{table}/{action}.aspx")
                {
                    Constraints = new RouteValueDictionary(new
                    {
                        action = "List|Details|Edit|Insert"
                    }),
                    Model = DefaultModel
                });
        }


Adding Dynamic Behaviour to Controls

1.       For GridView and DetailsView that uses LinqDataSource or EntityDataSource:
use ‘DynamicField
<asp:DynamicField DataField="OrderQty" HeaderText="Order Quantity" />For sample code see ‘addDDBehaviourToGridViewAndDetailsVew.aspx’
2.       For ListView and FormView that uses LinqDataSource or EntityDataSource:
use ‘DynamicControl
<asp:DynamicControl ID="DynamicControl1" runat="server" DataField="FirstName" Mode="Edit" /> For sample code see ‘addDDBehaviourToListViewAndFormView.aspx’

Action Links

-          Data-bound control table row action links
use ‘DynamicHyperLink’ control
<asp:TemplateField>
    <ItemTemplate>                             
        <asp:DynamicHyperLink runat="server" ID="EditHyperLink"
            Action="Edit" Text="Edit"/>
    </ItemTemplate>
</asp:TemplateField>

-          Unbound action links
<asp:DynamicHyperLink ID="InsertHyperLink" runat="server"
    Action="Insert" ContextTypeName="DynamicDataFeatures.AdventureWorksLT_DataEntities"  
    TableName="ProductModels">Insert new item
</asp:DynamicHyperLink>
<asp:DynamicHyperLink ID="DynamicHyperLink2" runat="server" Action="Edit"
    ContextTypeName="DynamicDataFeatures.AdventureWorksLT_DataEntities"
    TableName="ProductModels" ProductModelID="1">Edit item</asp:DynamicHyperLink>

For example, see ‘ActionLinks.aspx’


Customising Data Field Appearance and Behaviour

1.       Add custom field template files inside ‘FieldTemplates’ folder.
Add CustomFieldTemplateName.ascx for viewing and CustomFieldTemplateName_Edit.ascx for editing.
2.       Modify the markup and display functionality.
We may need to overwrite the ‘OnDataBinding’ method as well.
See ‘MyCustomFieldTemplate.ascx’ for more details.
3.  Refer to the custom field template in the databound row control by adding ‘UIHint
<asp:DynamicField DataField="OrderQty" HeaderText="Order Quantity" UIHint="MyCustomFieldTemplate" />
See ‘customDataField.aspx’ for more details.
To associate with an entity that will affect the rendering in the whole project, create a partial class for that entity then use ‘UIHint’ attribute on its meta data class.

Customising a Table Layout using Custom Page Template

1.       In the ‘DynamicData\CustomPages’ folder, create a subfolder with the name of the entity
2.       Copy an existing page template from the ‘DynamicData\PageTemplates’ folder to the subfolder created
3.       Change the copied files namespace if necessary
4.       Modify the new template
See ‘CustomPages/Products’ folder for more details.

Customising a Table Layout using Entity Template
If it’s only for editing details, edit, and insert views, we can use Entity Template.
1.       Add new ascx file with the entity name as the filename in the ‘EntityTemplates’ folder
2.       In the class file change the base class from ‘UserControl’ to ‘EntityTemplateUserControl
3.       Modify the new template file
See ‘Addresses.ascx’ inside ‘EntityTemplates’ folder.


Customising Entity Model Class

1.       Create a partial class for the entity
2.       Add references to System.Web.DynamicData and System.ComponentModel.DataAnnotations with ‘Using’ keyword
3.       Create a class that will act as the associated metadata class for the entity partial class
4.       Add ‘MetadataTypeAttribute’ attribute to the entity partial class definition with the class name that has just been created
[MetadataType(typeof(CustomerMetaData))]
public partial class Customer { }
5.       Add a property accessor for each data field that we want to provide attributes for. For any other fields that we want them to be displayed as default, ignore specifying them.
Sample:
[MetadataType(typeof(SalesOrderDetailMetadata))]
public partial class SalesOrderDetail
{
    public partial class SalesOrderDetailMetadata
    {
        [UIHint("MyCustomFieldTemplate")]
        public object OrderQty;

        [DataType(DataType.Date)]
        public object ModifiedDate { getset; }
    }
}

Some useful attributes:
-           [Required]
-           [Range(X, Y)]
-           [DataType(DataType.DataType)]
-           [ScaffoldColumn(true/false)]
-           [DisplayName(String)]
-           [RegularExpression(ExpressionErrorMessage = String)]
-           [ScaffoldTable(true)] – This attribute is used for an entity partial class, not a data field.



Modifying Field Template to Use Customised Data Attributes

For this example, see ‘Customer.cs’ and ‘FieldTemplates/Text.ascx.cs