7. Database design

In this post, I am going to talk about how database design is very important for any applications.

Database is the backbone for any application.  I am not going to write much theory here and do explain what are the necessary things to remember when we do the database design.

This below diagram describes the overall data modelling steps involved when we do the database design.

Please keep in mind the following database concepts and corresponding usage when database design or working on data base.

Concepts and description:

RDBMS (Relational Database Management System) :

Contains the tables and enforces relationships between the tables.

RDBMS Table Properties:  

1. Values are atomic.
2. Each Column must have a unique name
3. The sequence of rows and columns are insignificant.
4. Each column must specify same type of values

Normalization & different normalization forms:

The process of organizing data to minimize the redundancy is called normalization.

1^st Normal Form: Eliminate repeating groups. Define set of attributes for each table and set the primary key for the each table.

2^nd Normal Form: Eliminate redundant data.

3^rd Normal Form: Eliminate columns not dependent on the key.

4^th Norma Form:  Isolate independent multiple relationships.

Boyce- Codd Normal Form: If there are non-trivial dependencies between candidate key attributes, than separate them out into distinct tables.

5^th Normal Form: isolate semantically related multiple relationships.

Note:

1. First 3 normal forms should be necessarily implemented.
2. Too much normalization is not good for applications.
3. There are other normalizations namely Optimal and Domain-Key normal forms

De-normalization:

De-normalization is the process of attempting to optimizing performance of the database by adding redundant data.

Primary and Unique key:

Primary key is the unique identifier of the each row. Primary key prevents duplicate values. Primary key does not allow NULL values.

Unique + Not NULL = Primary Key

Unique key enforces the uniqueness of the values in the set of columns. Unique key allow on NULL value.

Candidate key:

The key which is become primary key is called candidate key.

Foreign key:

Foreign key enforces the referential integrity.  Foreign key in one table points to primary key or unique on another table.

ACID Database characteristics:

ACID: Atomicity, Consistency, Isolation, Durability. For more information, please refer http://en.wikipedia.org/wiki/ACID

Store Procedure and advantages:

Store procedure is the group of SQL statements that have been previously created and stored in the SQL Server. This is pre-compiled.

Advantages:

1. Reduce the network traffic.
2. More secure and prevents the SQL injection.
3. Speed and Modularization.

Trigger:

Trigger is the SQL procedure or SQL code which fire the action (When INSERT OR DELTE OR UPDATE on database). For more information, please refer http://en.wikipedia.org/wiki/Database_trigger

View:

View is the virtual tables and does not occupy any space. Views can be used for securing the columns fields. Please refer http://en.wikipedia.org/wiki/View_(database) for more information.

Index:

Index is used for fast data retrieval. Please refer http://en.wikipedia.org/wiki/Database_index for more information.

Cursor:

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis. Please refer the http://en.wikipedia.org/wiki/Cursor_(databases) for more information.

SQL Types:

Refer below diagram.

What is the difference between UNION and UNION ALL?

UNION:

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL:

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE:

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE cannot be rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table

DELETE:

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE can be rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

What is difference between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

SQL Joins:

Please refer the below diagram

Please refer http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ for more information about joins.

Linked Server:

Linked Server option enables to execute the commands against the OLE DB data sources on remote servers.

Locking:

Locking is a method used to protect records that will be accessed by multiple users and its prevents the concurrency errors.

There are two types of locking 1. Optimistic 2. Pessimistic

In pessimistic locking, when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking.

In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days in browser based application it is very common and having pessimistic locking is not a practical solution.

Please refer

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

http://www.dotnetfunda.com/articles/article1030-6-ways-of-doing-locking-in-net-pessimistic-and-optimistic.aspx

http://msdn.microsoft.com/en-us/library/777e5ebh.aspx

http://blog.sqlauthority.com/

http://blog.extremexperts.com

for more about locking, how you prevent concurrency problems and other useful information.

What is the difference between having and where clause?

1. Where clause cannot be used with aggregates, but the having clause can.
2. Having specifies a search condition for a group or an aggregate function used in SELECT statement.

Filtered index:

It uses a filter predicate to index a portion of rows in the table. Please refer http://technet.microsoft.com/en-us/library/cc280372.aspx for more information about filtered index.

What is the difference between varchar & nvarchar?

1. Nvarchar can store Unicode characters but varchar does not store the Unicode characters.
2. Varchar is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as 16 bits
3. Use nvarchar when if wants to store the text in different languages.
4. Varchar is better performance than nvarchar

Can we do unit test for SQL server functions, store procedures, triggers…etc.?

Yes. Please refer http://msdn.microsoft.com/en-us/library/aa833169.aspx

http://blogs.msdn.com/b/atverma/archive/2010/07/28/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010.aspx

for more information.

Please keep in mind the below notes when working on database.

Notes:

1. Use necessary data type needed for each column. For example, if you are not going to store the different language text for that column then you no need nvarchar. In this case, use varchar.
2. Use normalization
3. Write unit test for your store procedures, triggers, functions and evaluate the execution time for each and every query.
4. Use SQL Server performance tuning dashboard
5. Use http://www.opensourcetesting.org/performance.php for health check and performance issues.
6. Use SQL profiler for trouble shooting the performance issues.
7. Use SQL Server Best Practices Analyser tool for checking installation, configurations…etc.
8. Use Microsoft BI tools such as SSIS, SSRS, and SSAS if needed and use them appropriately.
9. Please prepare the checklist for your DB development and activities and use them whenever you deploy your DB package on production.
10. Please refer

http://msdn.microsoft.com/en-us/library/ff648235.aspx

http://msdn.microsoft.com/en-au/library/ms159689(v=sql.100).aspx

I hope you enjoy this post. I am thinking of writing another blog about Microsoft BI tools such as SSIS, SSRS, and SSAS if I get the time.

Topics Summary

Hey All,

I am going to write few blog posts about how to become an Architect. Please follow up me and contact me if you have any questions or would like to participate with me for building the Architect boat which will take you to the destination name called "Architect".

I am going to put together with all the topics which help you to become an Architect plus various domain (such as banking, telecom, digital media, CMS, Software Services, Retail...etc) knowledge experience.

Now days, anyone can build the any system/applications if you give time to build. But the beauty is how efficiently, systematically, how you streamline the process for the whole your software 

system/applications. This is very important and tricky part too.

Can we say here good (here good means efficiently, systematically, streamline the process) experienced programmer is an Architect.

We always ask to our self about WWW (here WWW means When to use, Where to use, Why to use, can we use something else and vice versa) when we design/build the systems, then we will have efficient answer for everything.

The learning following topics will help to become an Architect. Please click the below links to know more about the particular topic.

1. Coding standards & practices
2. OOAD

3. Design patterns

4. UML

5. Security

6. R & D for up to date technologies

7. Database design

8. Feeding the people technically

9. Testing framework

10. Documentation skills

11. Communication skill

12. Doing Certifications

13. SOA & Cloud Fundamentals

14. Building SOA & Cloud based services

15. Networking skills up to Architect level

16. Troubleshooting the problems

17. Lesson learned from your experience

The above topics are given as a pie chart. Please start learning from topic 1 to 17 and you become an Architect when the circle complete.

Architect Topics

Are you ready to start? If you says yes then please read the subsequent posts.

I am going to describe each topic in details in the subsequent posts.

Thank you  for visiting.

4. UML

In this post, I am going to explain more about UML and its significants.

As we know most of the techies are don’t use UML much and they don’t know exact usability of the UML. Generally techies are straightaway start writing coding & they spend most of the time related in terms of technologies. So they don’t know where the UML is fit for them until they use to realize.

In short form, UML diagrams gives you in the simplified modelling language which is equal to what you going explain in many numbers of lines on the document or so on.

For example, let’s say you are going to explain to someone (to your stakeholders) about your project and your project is called online banking system. For this, you need to write huge document which contains so much of lines and take so much of time to explain to someone. But simple UML diagram save you everything.

Let’s see how it is? The following UML use case diagram will tell you overall what the system does.

What is UML?

UML stands for Unified Modelling language. This is not a programming language and it’s a modelling language.

Where UML comes from and what is the history of the UML?

Please visit www.omg.org to know more details about UML including version details and change history.

What are the different types of UML?

In UML 2.x, there are 14 types of UML diagrams and below picture shows all types which from http://en.wikipedia.org/wiki/Unified_Modeling_Language

Next, our question is do we need to use all the types of diagrams for our project?

No. Its depends upon of the project and project domain type. For Information technology (IT) projects we don’t use Timing Diagram but for the manufacturing project, Timing Diagram is essentials.

What are the most commonly used UML diagrams in IT?

1. Use Case
2. Sequence
3. Class
4. Object
5. Activity
6. State Machine
7. Component
8. Package
9. Deployment

What are the UML tools are available on the market?

There are many tools are available on the market.  Please visit http://en.wikipedia.org/wiki/List_of_Unified_Modeling_Language_tools to know more about the all tools.

Which UML tool can we use for our projects?

It’s all depends upon your taste. Recent Microsoft Visual Studio, Enterprise Architect (from Sparx Systems) and Visual Paradigm (from http://www.visual-paradigm.com) are comes with modelling project which is collaboration with source version controls systems such as TFS, SVN…etc. and more integrated with other project development tools. This is very useful for our development. Let’s say for example, some people like to buy things from Woolworths and some people from Coles but the product (say Dairy Milk) is the same.

Any books or online materials are useful for learning UML?

There are many books and information are available on the internet. If you use VS for modelling then I would suggest visiting MSDN site for more information http://msdn.microsoft.com/en-us/library/57b85fsc.aspx and I also suggest the book called UML Distilled: A Brief Guide to the Standard Object Modeling Language (3rd Edition) by Martin Fowler.

Is there any other diagrams which useful for our team?

Yes. There are other few more diagrams namely

1. Layer
2. Network
3. Customize diagram which can be useful for all other purposes.

Up to here, we know little bit about UML and UML tools…etc. I would suggest lets learn first about UML notations so that we know where we can use exactly. I am not going to explain about all the notations however, I do explain the necessary notations which we need it for our modelling. I am explain with the following details for most commonly used UML diagrams

1. Diagram name
2. Notations
3. When to use
4. Who will be using
5. Example

Let’s start with Use Case diagram.

Use Case Diagram:

1. Diagram name: Use Case. Use Case diagram describes what system does and its does shows the overall functionalities of the systems.

• 2.       Notations:

3.       When to use:  Initial phase of the project such as requirements gathering & describes the functionalities of the system.

4.       Who will be using: Technical and non-technical people.

5.       Example:

 6.       Notes:

There are two types of actors in Use Case and they are Primary and Secondary actors.

1. Primary Actor: Who are the active participants and they are initiate the Use Case
2. Secondary Actor:   Who are the passively participants.
3. <Include>>:  One Use Case used by other Use Case similar like one function called by another function.
4. <Extend>>:  Extending to another Use Case but some situations not necessarily to be extended.

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/UseCase.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/95/6362_drawinguseca.html

http://knowhow.visual-paradigm.com/uml/10-use-case-diagram-tips/ for more details about other notations and how to use them.

Sequence Diagram:

1. Diagram name: Sequence. Sequence diagram shows the interaction between objects over specific period of the time. For each and every Use Case diagram we can draw the sequence diagram for that.

2.       Notations:

 

3.       When to use:  we can use the sequence diagrams when we want to look at the behaviour of the many objects with in a single Use Case.

4.       Who will be using: Technical and non-technical people.

5.       Example:

6.       Notes

Please note that messages are the important when we use the Sequence diagram and Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Sequence.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/265/7025_drawingseque.html

for more details about messages and how to use them.

Class Diagram:

1.       Diagram name: Class. Class diagram provides an overview of the classes, objects and relationship between them. We can say class diagrams are prototype model of the target systems. Each and every Class diagram contains 3 compartments namely Class name, Attributes, Methods. 

• + -- used to define public properties/methods
• # -- used to define protected properties/methods
• -   -- used to define private properties/methods
• ~ -- used to define the package

Example:

The following 3 relationships are the very important when we draw the class diagrams.

Association: – Relation between two classes.

Composition: – A type of association represents a part of whole relationship. One part dies and other part will dies in the relationship. So simply, one cannot exist without another.

Aggregation: - A type of association represents a part of whole relationship. However one part lives without other part in the relationship.

Example:

Here, Company cannot exist without groups. This relation is called Composition and the symbol is filled diamond. We can say this relation is a strong relationship.

Employee leaves the company and Groups still exists. This relation is called Aggregation and symbol is diamond. We can say this relation is a part of relationship and it’s not strong relationship.

Multiplicity: Represents the multiple associations or one class links to many other classes.

0..1	No instances, or one instance (optional, may)
1	Exactly one instance
0..* or *	Zero or more instances
1..*	One or more instances (at least one)

Example:

Here, Customer has zero or more orders.

Generalization & Specialization: Defines the parent and child relationships.

Example:

Abstract Class: Abstract class are represented by with in {abstract}  as shown in the below picture.

2         Notations:

3         When to use:  we can use the class diagrams when we want to represents the relationship between classes and objects.

4         Who will be using: Technical people.

5         Example:

6         Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Class.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/299/7190_drawingclass.html

for more details about other notations and how to use them.

Object Diagram:

1. Diagram name: Object. Object diagram shows the how classes are looks when object are created for that class. It’s similar like class diagram and the same notations can be used in the object diagram and used to illustrate an instance of a class at a particular point in time.

2.  Notations:

3.       When to use:  we can use the object diagrams when we want to create the instances of the classes and it does show how the class looks.

4.       Who will be using: Technical people.

5.       Example:

6.       Notes:

Please note that its similar like class diagram and top of that it does shows the instance of the classes. Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Object.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/299/7191_drawingobjec.html

for more details about object diagrams and how to use them.

Activity Diagram:

1. Diagram name: Activity. Activity diagram used to captures the complicated flows in a system. Main notations are Start, Activity and End.

Start - activity is denoted by a dark circle.

End - activity is denoted by a dark circle inside a white circle.

Activities - denoted by simple oval rectangles.

2.       Notations:

3.       When to use:  we can use the Activity diagrams to shows the various flows with in a systems and mainly this diagram used whenever we have complicated flows in our project.

4.       Who will be using: Technical and non-technical people.

5.       Example:

6.       Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Activity.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/200/6713_drawingactiv.html for more details about Activity diagrams and how to use them.

State Machine or State Chart Diagram:

1. Diagram name: State Machine. State diagram shows the object undergoing process and shows the dynamic behaviour of the object.

For example, ATM machine is best examples for this diagram. The states are when we withdraw money from ATM machine.

What is the difference between Activity and State Diagram?

State diagram shows status of the object undergoing process. It gives a clear picture of the changes in the object's state in each and every process.

For example, withdraw money from ATM machine

Card object states: Checking, Approving, Rejecting

Activity diagram is a shows the flow of activity of a process.

For example, withdraw money from ATM machine

Withdraw activities: Insert Card, Enter PIN, Check PIN, Enter amount, Check balance, withdraw money, remove card.

 2.       Notations:

3.       When to use:  we can use the State diagrams to shows the various status of the object undergoing the process and gives you clear picture of the list of possibilities of the status.

4.       Who will be using: Technical people

5.       Example:

6.       Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/State.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/200/6714_drawingstate.html

for more details about State diagrams and how to use them.

Component Diagram:

• 1. Diagram name: Component. Component diagram shows the different software components to be used and how they connected each other in your project.

2.       Notations:

3.       When to use:  we can use the Component diagrams to shows the various software components and how they are interconnected in our project.

4.       Who will be using: Technical people

5.       Example:

6.       Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Component.html

http://www.visual-paradigm.com/support/documents/vpumluserguide/94/334/7292_drawingcompo.html

for more details about Component diagrams and how to use the them.

Package Diagram:

1. Diagram name: Package. Package diagram is used to specify the logical groups of classes and its related components. Technically, Package diagram maps each and every library of our project which developer in ASP.NET MVC or Java etc.

 Stereo types are mainly use in Package diagrams.

What is the stereo type and what are the commonly used stereo types in UML?

Stereotypes are a way to define various entity components in UML.

The commonly used stereo types in UML are

<<Interface>>: To represents an interface.

<<Web services>>: To represents a web service

<<Executable>>: To represents a software component which can be executable.

<<Library>>:  To represents the library

<<Database>>: To represents the database of the application.

<<ODBC>>: To represents the database connectivity.

2.       Notations:

3.       When to use:  we can use the Component diagrams to shows the various software components and how they are interconnected in our project.

4.       Who will be using: Technical people  & deployment engineer.

5.       Example:

6.       Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/product/vpuml/tutorials/packagediagram.jsp

http://www.visual-paradigm.com/VPGallery/diagrams/Package.html

for more details about Package diagrams and how to use them.

Deployment Diagram:

1.       Diagram name: Deployment. Deployment diagram shows overall view of the how software and hardware nodes in our application. Deployment diagrams will helpful when we have so many assemblies which need to be deploying which servers…etc. This diagram mainly used by deployment managers and engineers.

 2.       Notations:

3.       When to use:  we can use the Deployment diagrams for deploying which package on which server…etc. and shows that changes of single assembly affect the how many systems and where need to deploy to make sure everything is correct and its does helpful for the outage notification…etc.

4.       Who will be using: Technical people, deployment managers and engineers.

5.       Example:

6.       Notes:

Please visit the below URL’s

http://www.visual-paradigm.com/VPGallery/diagrams/Deployment.html

http://www.visual-paradigm.com/product/vpuml/provides/architecturalmodeling.jsp

for more details about Deployment diagrams and how to use them.

I hope you enjoy reading this post.

Conclusions:

1. Please try to use relevant UML diagrams which needs for your project development and it does save you lot of time and leads the project to be successful.
2. We can use other diagrams such as Layer and Network diagrams for our future developers and application supporters. Recent Microsoft Development Studio come with modelling project and it is collaboration tool with many futures. Please try to use them and get more benefits for your project.
3. Now, we are expert in UML.