Database

Database is a collection of data and it is arranged in such a way that the data can be easily accessed, managed and updated. The most common form of database today is relational database. It is in tabular form, which means data is stored in tables that can be split into rows and columns.

Good database design ensures that your database is easy to maintain. You store data in tables and each table contains data about only one subject, such as customers. Therefore, you update a particular piece of data, such as an address, in just one place and that change automatically appears throughout the database.

The first step in designing a database is to determine its purpose and how it's to be used:

• Talk to people who will use the database. Brainstorm about the questions you and they would like the database to answer.
  
• Sketch out the reports you'd like the database to produce.
  
• Gather the forms you currently use to record your data.

A well-designed database usually contains different types of queries that show the information you need. Figure-7.1 displays a table form from Microsoft Access database.

Figure-7.1

In given above example you can see, each row defines a person and each person has a unique ID. Access calls this ID column an AutoNumber column. You define your customer ID Column into a primary Key, as each ID can appear only once in the entire ID column. All records together make up the table.

Kind of Databases

A list of common and popular database system are many but we will be covering Microsoft Access as it can be installed quickly and it’s relatively cheap, makes it an ideal tool for database design and prototyping.

Microsoft Access is a user-friendly database with which you can store and retrieve data but it has some limitation too. The limit of Access is reached sooner. You can define 255 user Id in practice.

Besides Access we many other database available in market like Microsoft SQL Server, MySql, Oracle etc.

Microsoft SQL Server: SQL Server features make it a popular database system and supporting advanced features like views, stored procedures and transactions. It’s a expensive product and costs big.

MySql: It’s a very popular database and easy to install. It can be run on different operating systems like windows, Linux and Mac OS X. It has also advanced features like transactions and stored procedures.

Oracle: Oracle Database delivers record-breaking performance and scalability on Windows, Linux, and UNIX servers and provides fast ROI by moving from a single server to Grid Computing without changing a single line of code.

Oracle Database delivers better results by automating administrative tasks, providing industry-leading security and regulatory compliance features, and enabling the highest availability with Real Application Clusters. With a range of editions and lower operating cost versus IBM DB2 and Microsoft SQL Server, it's the ideal choice for large enterprises and small and medium businesses alike.

Protecting Your Database

You need to perform quite a lot of steps to protect your database. If you keep your database with in the scope of your web site, a malicious user can easily download your sensitive information. Suppose you have database named Company.mdb generated in Microsoft Access and it is located in a root directory of your site, can easily be downloaded by visiting to www.Yourdomain.com/Database/Company.mdb.

Naturally you would like your site to stay away from such things.

You can overcome this situation by placing the database outside the root directory and restricting access to the database.

C:\inetpub\wwwroot is the root folder of a web site and this location is the default location. So, if you place your file under the wwwroot directory, one can easily download the contents. So it’s advisable that you keep your Company.mdb file in another folder for e.g., c:\Database. The database can no longer be downloaded but still you can access. With this, it has become a tedious task to manage your files as your files are locating in two different positions.

You can restrict access to the database by allowing authorized users to download the database. This practice is possible only if your web server and file system support certain features like you need to run your Windows NT 4Server, Windows 2000 professional, Windows 2000 Server, Windows Xp professional or Window Server 2003.

Establishing Relationship between Dreamweaver and Database

When you are using Database in your site creation then Dreamweaver doesn’t know what database you are using. Dreamweaver stores all this information in a Connection String. When you work with ASP in Dreamweaver, you have two methods available for connection i.e DSN and DSN-less Connection Strings. DSN stands for Data source Name, refers to either a file or registry setting that defines the option for a connection.

Creating a DSN

1. To Create a DSN, open the Data Sources from Administrative Tools in the Control Panel in windows. Press File DSN tab as shown in Figure-7.2.

   
   Figure-7.2

2. Click Add and Create new data Source box will appear on the screen as shown in Figure-7.3. Select Microsoft ACCESS Driver *.mdb from list of Drivers. Click Next. This Wizard will take you through the step to create a DSN.

   
   Figure-7.3

3. Type a name of your file for creating DSN as shown in next Figure-7.4. You can click browser to change the location of your file.

   
   Figure-7.4

   
   When you are done, Click next and Finish.
   
4. Click the Select button and then select your database folder in ODBC Microsoft Access Setup dialog box as shown in given Figure-7.5.

   
   Figure-7.5

   
   Click OK to close the ODBC Microsoft Access Set up dialog Box as well ODBC Data Source Administrator dialog box.
   
5. Open your file in Notepad. Your path will be c:\ program Files\Common Files\ ODBC\ Data Sources\Company.dsn. You will find the given code as shown in Figure-7.6.

   
   Figure-7.6

   For establishing a connection, you can simply refer to DSN by prefixing DSN name with the Keyword dsn=. Your complete connection will be like this …..
   dsn= Company.mdb