Relational Databases, Normalization, and SQL

Relational Databases, Normalization, and SQL A database is an application that can store and retrieve data very rapidly. The relational bit refers to how the data is stored in the database and how it is organized. When we talk about a database, we mean a relational database, in fact, an RDBMS: Relational Database Management System. In a relational database, all data is stored in tables. These have the same structure repeated in each row (like a spreadsheet) and it is the relations between the tables that make it a relational table. Before relational databases were invented (in the 1970s), other types of database such as hierarchical databases were used. However relational databases have been very successful for companies like Oracle, IBM, and Microsoft. The open source world also has RDBMS. Commercial Databases OracleIBM DB 2Microsoft SQL Server Ingres. The first commercial RDBMS. Free/Open Source Databases MySQLPostgresSQLSQLite Strictly these are not relational databases but RDBMS. They provide security, encryption, user access and can process SQL queries. Who Was Ted Codd? Codd was a computer scientist who devised the laws of normalization in 1970. This was a mathematical way of describing the properties of a relational database using tables. He came up with 12 laws that describe what a relational database and an RDBMS does and several laws of normalization that describe the properties of relational data. Only data that had been normalized could be considered relational. What Is Normalization? Consider a spreadsheet of client records that is to be put into a relational database. Some clients have the same information, say different branches of the same company with the same billing address. In a spreadsheet, this address is on multiple rows. In turning the spreadsheet into a table, all the clients text addresses must be moved into another table and each assigned a unique ID- say the values 0,1,2. These values are stored in the main client table so all rows use the ID, not the text. A SQL statement can extract the text for a given ID. What Is a Table? Think of it as being like a rectangular spreadsheet made up of rows and columns. Each column specifies the type of data stored (numbers, strings or binary data - such as images). Unlike a spreadsheet where the user is free to have different data on each row, in a database table, every row can only contain the types of data that were specified. In C and C, this is like an array of structs, where one struct holds the data for one row. For more information see Normalizing a database in the Database Design part of databases.about.com. What Are the Different Ways of Storing Data in a Database? There are two ways: Via a Database Server.Via a Database File. Using a database file is the older method, more suited to desktop applications. E.G. Microsoft Access, though that is being phased out in favor of Microsoft SQL Server. SQLite is an excellent public domain database written in C that holds data in one file. There are wrappers for C, C, C# and other languages. A database server is a server application running locally or on a networked PC. Most of the big databases are server based. These take more administration but are usually faster and more robust. How Does an Application Communicate With Database Servers? Generally, these require the following details. IP or Domain name of the server. If it is the on the same PC as you, use 127.0.0.1 or localhost as the dns name. Server Port For MySQL this is usually 3306, 1433 for Microsoft SQL Server.User Name and PasswordName of the Database There are many client applications that can talk to a database server. Microsoft SQL Server has Enterprise Manager to create databases, set security, run maintenance jobs, queries and of course design and modify database tables. What Is SQL?: SQL is short for Structured Query Language and is a simple language that provides instructions for building and modifying the structure of databases and for modifying the data stored in the tables. The main commands used to modify and retrieve data are: Select - Fetches data. Insert - Inserts one or more rows of data. Update - Modifies existing row(s) of data Delete - Deletes rows of data. There are several ANSI/ISO standards such as ANSI 92, one of the most popular. This defines a minimum subset of supported statements. Most compiler vendors support these standards. Conclusion Any nontrivial application can use a database and a SQL-based database is a good place to start. Once you have mastered the configuration and administering of the database then you have to learn SQL to make it work well. The speed at which a database can retrieve data is astonishing and modern RDBMS are complex and highly optimized applications. Open source databases like MySQL are fast approaching the power and usability of the commercial rivals and drive many databases on websites. How to Connect to a Database in Windows using ADO Programmatically, there are various APIs that provide access to database servers. Under Windows, these include ODBC and Microsoft ADO. [h3[Using ADO So long as there is a provider- software that interfaces a database to ADO, then the database can be accessed. Windows from 2000 has this built in. Try the following. It should work on Windows XP, and on Windows 2000 if youve ever installed MDAC. If you havent and want to try this, visit Microsoft.com, do a search for MDAC Download and download any version, 2.6 or higher. Create an empty file called test.udl. Right click in Windows Explorer on the file and do open with, you should see Microsoft Data Access - OLE DB Core Services. This dialog lets you connect to any database with an installed provider, even excel spreadsheets! Select the first tab (Provider) as opens by default at the the Connection tab. Select a provider then click Next. The data source name shows the different types of device available. After filling in username and password, click the Test Connection button. After you press the ok button, you can open the test.udl with file with Wordpad. It should contain text like this. [oledb] ; Everything after this line is an OLE DB initstring ProviderSQLOLEDB.1;Persist Security InfoFalse;User IDsa;Initial Catalogdhbtest;Data Source127.0.0.1 The third line is the important one, it contains the configuration details. If your database has a password, it will be shown here, so this is not a secure method! This string can be built into applications that use ADO and will let them connect to the specified database. Using ODBC ODBC (Open Database Connectivity) provides an API based interface to databases. There are ODBC drivers available for just about every database in existence. However, ODBC provides another layer of communication between an application and the database and this can cause performance penalties.