Monthly Archives: August 2013

Build Relational Databases in Seven Steps


The following is an ordered list of steps you can follow to create a good database design for your web application.

Step 1- Create a charter for the database

First keep in your mind why you need to create a database and what you are expecting it to do. Database designers sometimes fall into trouble when.

1. They assume that the data exists for its own sake

2. They assume that the database exists for the sake of the Information Technology department.

Both the above are wrong, and the database exists for its users. Before you design your database, find out why the users of your application need the database and what the users expect to accomplish from it. You can think of this purpose statement as a mission statement or a charter for the database.

The more complicated and complex your application is, the more detailed your Charter will be. But always limit your charter to the key points that identify the unique capabilities that the user expects from the database.

The most vital part of this step is to examine how the data is currently being stored and to uncover the weaknesses. If the data is currently being stored in an Excel spreadsheet or is stored in paper form in file cabinets, carefully examine them to see what kinds of data are included in them.

Step 2 – Make a list and double check it

Once you finish Step 1, and you are done with your Charter, start listing the major tables of the database you are going to create.

When you have come up with a rough list of tables and the data items, always keep in mind them as entities that are like real-world objects that the database needs to keep track of.

Step 3 – Add Keys

For every database you create, there should be a column or combination of columns that uniquely identifies each row in a table. This column or combination of columns is also known as the Primary Key of the table.

In this step you will have to figure out the primary and foreign keys for each table design and add appropriate ID fields to overcome the problem stated above.

Step 4 – Normalize the Database

The next step is the Normalization process. Normalization refers to the process of eliminating redundant information and other problems in the database design. In order to normalize the current database design, you need to identify the problems in the design and correct them, often by creating additional tables.

Five different levels of normalization exist, known as the five normal forms.

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Fourth Normal Form (4NF)

5. Fifth Normal Form (5NF)

The following are the corrections that need to be handled:

1. Maintain Consistency – use same field names for fields used as primary keys and foreign keys, and change all tables to plural names.

2. Data Redundancy

First Normal Form – 1NF

A table is in the 1NF when each table is free of repeating data.

Second Normal Form – 2NF

The 2NF only applies to composite a key table, that is if the table contains a primary key made up of two or more table columns. In order to be in the 2NF, every column in the table must depend on the entire primary key, not only a part of it.

Third Normal Form – 3NF

A table is in the 3NF form when the table is in 2NF and every column in the table depends on the entire primary key and none of the other fields depend on each other.

Step 5 – Denormalize the Database

When you come across a situation where you ben the normalization rules a bit, the database will operate more efficiently. For instance having data redundancy in the database can increase the performance of your database. This process of adding data redundancy to increase the efficiency and performance of your database is called denormalization.

Step 6 – Use legal SQL names

If you look back at the data-design process, all the names used were self-descriptive an it was easy to remember what it does exactly and what table and column it represents. It is a good database design practice to avoid SQL dialects with spaces between words like “Product ID” where you can either name it as “ProductID” or “Product_ID”.

Always follow these rules when you choose names for your database:

1. No special characters, other than $, #, and _.

2. No spaces.

3. No more than 128 characters – Keep the names as short and descriptive as possible. Even though 128 character are allowed choose names that are 15 characters or lesser.

Step 7 – Draw an ERD

The best way to represent your data model is by drawing an Entity-Relationship Diagram also known as an ERD. The following is a sample ERD for the database.


Source link

Access VBA Programming – How to Use SQL and VBA in Forms to Retrieve Data


If you have been using Access for a while, you may be familiar with SQL. SQL (Structured Query Language) is the language used to manipulate the records in your database. It is the language behind the queries and the recordsets. In fact, when you built a query in Access, you actually use a SQL statement.

You can also use SQL statements in VBA (Visual Basic for Applications) programming language. VBA and SQL work very well together and are powerful tools to use in your Access applications. With them, you can add more functionality to your programs.

Sometimes it is useful to limit the number of records that are displayed on a form, based on certain criteria. You may also want to sort the information on a column.

How can you accomplish this?

First, you open a form in design view. Then bind a query to this form. Let ‘s say you have a query named qryCustomers, which contains the names of your customers and the names of the cities where they reside.

Next, you define which records from this query will be selected when the user clicks an option on the form. For example, if the user can choose from three options, you write a Select SQL statement with three different Where and Order by clauses to retrieve the data.

In addition, you put an option group with three option buttons on the form. Every option will select another set of records to be displayed on the form.

Finally, assign a name to the option group control. Right-click the control and from the menu choose Build Event. Then from the Builder menu, choose Code Builder. This opens the VBA editor. Select the After Update event. Now put the SQL statements in the code module.

First, define two variables to compose a SQL string.

Dim strQuery as string

Dim strWhere as string

Next, put a select statement in a string to retrieve all the records from your query.

strQuery = “Select * From qryCustomers ”

Now write a Where clause for every option. For example, you want to view only the customers based on a city and sorted by name.

Select Case Me!OptionGroupName

Case 1

strWhere = “Where City = ‘New York’ Order by Name”

Case 2

strWhere = “Where City = ‘Washington’ Order by Name”

Case 3

strWhere = “Where City = ‘Atlanta’ Order by Name”

End Select

Finally, bind the string to the form.

Me.Recordsource = strQuery & strWhere


When the form is open and the user selects an option, the after update event of this control is triggered and the record source of the form is changed. The form is reloaded with the resulting details, based on the option that was clicked.


Source link

How to Reset SA Password in SQL Server 2000


Microsoft SQL Server 2000 is a full-featured relational database management system that offers a variety of administrative tools to ease the burdens of database development, maintenance and administration. It operates in one of two authentication modes: Windows Authentication Mode (the default) or Mixed Mode.

When you install the SQL Server 2000, you can log in the server with Windows Authentication and SQL Server Authentication if you select mixed mode authentication. If you select Windows Authentication, you cannot assign a SA password during installation. However, you should set the password after installation.

It is very easy for one to set SA password in SQL 2000. Just follow the below steps:

* 1. Expand a server group, and then expand a server.

* 2. Expand Security, and then click Logins.

* 3. In the details pane, right-click SA, and then click Properties.

* 4. In the Password box, type the new password.

The type of client connections determines the choice of security mode. If all of the incoming clients support trusted connections, you can use Windows Authentication Mode. If some clients do not support trusted connections, you can select Mixed Mode.

If you forget the SA password, you will need to use MS SQL Server Password Unlocker to reset the password for SQL 2000. MS SQL Server Password Unlocker is easy yet powerful SQL password reset software for you to reset SQL password, including reset SA password and other user passwords. It provides you an easy and safe way to reset SQL password for Microsoft SQL Server 2000/2005/2008 in a minute. No need for you to reinstall SQL on your PC. The file in master.mdf format is enough for you to recover SA password SQL 2000.

Steps to reset SQL 2000 password for SA as follows:

* 1. Download and install MS SQL Server Password Unlocker.

* 2. Click the application icon to run it.

* 3. Click Open to import your master.mdf file. All the user names of your MS SQL will be displayed.

* 4. Select SA account, click Change password button to reset the password.

* 5. Type a new password, and then click OK to finish your operation.

Note: This method to reset SA password in SQL 2000 is usually applied when you cannot login SQL Server 2000 by Windows Authentication wile you forget system administrator login password.

More information about SA password:

According to Microsoft, System administrator (SA) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although SA is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use SA only when there is no other way to log in to an instance of Microsoft SQL Server.


Source link