Ms Access : Creating Database Tables

Option 1: Programatically

The following is an example of creating a new table. Note that we are specifying the name of the table, the name of each column, and the data type of each column. More parameters can be added to this example if your requirements are more specific.

CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
DateCreated dateTime
)

Option 2: User Interface

Database management systems usually have a "Design View" for creating tables. Design view enables you to create the names of each column, specify the type of data that can go into each column, as well as specifying any other restrictions you'd like to enforce. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date.

More parameters can be added against each column if you require them. For example, you could specify a default value to be used (in case the field has been left blank by the user).

When you create a table via the user interface (or design view), depending on which database system you use, you should see something like this:

Once you've created your table in "design view", you can switch to "datasheet view" to see the resulting table. You should see something like this:

OK, so this is a blank table - it doesn't have any data yet. What we have is a table that contains the columns required before we can enter any data.

So, now that we have a blank table, let's look at how to add data.

Ms Access : About Database Tables

Database tables will most likely be the area you'll become most familiar with after working with databases for a while. Now, before we go ahead and start adding tables to our new database, let's have a look at what a database table actually is.
What is a Table?

In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns.

In the following example, the second row is highlighted in black:



In the next example, the second column is highlighted in black. This column has been given a name of "FirstName":



A row contains each record in the table, and the column is responsible for defining the type of data that goes into each cell. Therefore, if we need to add a new person to our table, we would create a new row with the person's details.

Ms Access : Creating a Database

With database management systems, many tasks can be done either via programatically or a user interface. Creating databases is no exception.
Option 1: Programatically

Many database administrators (DBAs) use Structured Query Language (SQL) to perform many of their database tasks. To enter SQL, you need to open an interface that allows you to enter your code. For example, if you use SQL Server, you would normally use Query Analyzer.

The following example is the basic code for creating a new database. Parameters can be added to this example if your requirements are more specific.
CREATE DATABASE MyDatabase

Note: This example assumes you know how to use your database system to run scripts like this. If you don't you, will probably find it easier to use the user interface method (below).
Option 2: User Interface

Most database systems make it very easy to create a database via a user interface. Generally, it's just a matter of selecting an option from a menu, then providing a name for your database.

The following examples demonstrate how to create a database in Microsoft Access.
From the "File" menu, click on "New Database":



Choose "Blank Database". (MS Access also gives you the ability to choose from a template, but we'll just use a blank database here):



Choose a location to save the database:



Your New Database
Once you've completed the above tasks, you should see a blank database, like this:



We know this database is blank because it doesn't have any tables. If it did, you would see these tables in the middle pane of the table tab. Now that we have our blank database, we can start adding some tables.

MySql : Manage Data

Once we have collected data in the database the links may get old and need updating or the links just may no longer work. So we need to be able to update and/or delete the data from the database.

There are two SQL statements which handle each of these, and they are coincidently named UPDATE and DELETE.

The format for the UPDATE statment is:
UPDATE (table) SET
(column1) = (value),
(column2) = (value),
...
WHERE (column) = (value)
The last column = value line does not have a comma after it. Also it is very important to use the WHERE clause to specify which data record you want to update. If the where clause is left out it will update all records with the specified values. This is where the primary key id becomes useful, the unique primary key is used to specify which record you want to update.

The DELETE statement format is:
DELETE FROM (table)
WHERE (column) = (value)
This will delete the complete record(s), multiple rows can be delete at once depending on the WHERE clause. Again the WHERE clause is crucial, if it is left off it will delete all records from the database.

A good example of how to manage links is at any of the portal personal pages, such as my.excite.com, or my.yahoo.com. These sites display the links entered in, and you then click to edit or delete the one you want. To do this you first need to use a SELECT statment to display the available links. The code is the same as before:
$category = "Local Docs";

$SQL = " SELECT * FROM links ";
$SQL = $SQL . " WHERE category = '$category' ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }
?>
One difference is displaying the links. We do not need the links "live" but instead want to be able to edit them by clicking either "edit" or "delete". We will need the primary key id of the link we wish to edit or delete. The primary key can be passed in via the URL and the get method by adding on ?id=(number) at the end of the URL.

The delete code is shown below, I will leave the edit page as an exercise for you to do. The edit page is only slightly more complex because you need another form to display the data to edit. This can be processed the same way as the insert script. What I usually do is copy over the insert form and modify that, it is a good base for my edit page.

Code to display the links on the screen:
echo ("

\n");
while ($row = mysql_fetch_array($retid)) {
$sitename = $row["sitename"];
$id = $row["id"];

echo ("");
echo ("\n");
echo ("");
echo ("");
echo ("");
}
echo ("

$category
$sitename	Edit	Delete

");
?>

When the "Edit" link is clicked it will go to the "manageedit.php" page, it will also pass along the id of the link to edit. This page will display the data in a form, when that form is submitted it will update the information.

When the "Delete" link is clicked it will submit to the same page (manage.php) and pass along two variables. The first variable is the id of the record to delete, the second variable is "task" which is set to "del". This is done so we can catch that variable and know we are performing a delete action when we load the page. The following code, which is placed in the top of the script, shows how:
if ($task=="del") {

$SQL = " DELETE FROM links ";
$SQL = $SQL . " WHERE id = $id ";
mysql_db_query($db, $SQL, $cid);

}

MySql : View Data from Database

Now we want to retrieve the data from the database and display them as links on the page. The SQL statement used to get data from a database is SELECT. The format is:
SELECT (columns) FROM (table)
WHERE (exclusive criteria)

For our database we want so select all columns, so a '*' is used instead of listing out each column. We also only want to select a specific category of links, let's say "Local Docs" is the cateogry we want. So our SELECT SQL statement would be:
SELECT * FROM links
WHERE category = 'Local Docs'

Single quotes specify a string value in the WHERE clause, if we were using a column which was a number value no quotes would be needed. The WHERE part is optional, if you want to select everything from the database you can leave off the WHERE portion. Review the SQL Tutorial for more examples of what can be done with SELECT statements and WHERE clauses.

The script to display links out of the database starts with the usually code to initialize the database connection.
$usr = "--username--";
$pwd = "--password--";
$db = "linksdb";
$host = "localhost";

$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n"); }
?>

The next part of the script is setting up and executing the SQL statement. This should look familiar to the previous pages. I set the category as a variable at the beginning so the code can be copied and pasted for other category selects and only one change is needed.
$category = "Local Docs";

$SQL = " SELECT * FROM links ";
$SQL = $SQL . " WHERE category = '$category' ";
$retid = mysql_db_query($db, $SQL, $cid);
if (!$retid) { echo( mysql_error()); }

The SELECT statement returns rows of data from the database. We need to loop through that data and display the information we want, which will be our links. The command to grab a row is:
$row = mysql_fetch_array($retid);

This sets $row as an array holding one record from the database, with the column names as the "keys" for the array. So to retrieve the siteurl value from that array you would use:
$siteurl = $row["siteurl"];

When the mysql_fetch_array command is called next, it moves to the next data record returned by the SELECT. If there are no more rows of data the command returns false. So to loop through all rows of data we can setup a while statement with the mysql_fetch_array in it. So here is the code to loop through the data and display it to the screen:
while ($row = mysql_fetch_array($retid)) {
$siteurl = $row["siteurl"];
$sitename = $row["sitename"];

echo ("
$sitename
\n");
}
echo ("");

?>

You can download the complete script of viewing data from clik the title of this posting

MySql : Insert Data

We need to get some data into the database. To do so we use an HTML form to collect the data, and then insert it into the database using PHP. Both these functions can be done on the same page.

The form can submit to the same page it is on. This page checks if the POST method is used. If so, insert into the database. I find it easier to have the form and database insert operation on the same page. Makes one less file to keep track of.

Here is the HTML for the form:

Category:
Site Name:
Site URL:
Description:

The above HTML snippet has the table elements removed because they are for display purpose only. The HTML for the form below is displayed using a table.



This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.

This form gathers the information we want to insert into the database and submits back to the same page it is on. We now need the PHP code to process this information. The first block of code is setting up the connection to the database. This is the same as used on the create table page.

?>?>?>

The second part is run only when the request method on the page is a POST. This is when the form is submitted back to the page. We need to create our SQL statement for the insert. The SQL format for an INSERT is:
INSERT INTO -tablename-
(column_name1, column_name2, ...) VALUES
(data1, data2, ... )

Where the first column name matches to the first data in each of the sets. Remember PHP variables are automatically initialized with the form field names submitted to the page. So the PHP code to submit the query and check for errors is:
?>?>?>

Escaping Data
When creating SQL statements, string values are delimited using apostrophes (see above code). So what happens when there is an apostrophe in the data you are trying to insert? A SQL error will occur if, for example, the description variable included an apostrophe. Because you do not know what the user will type in, you must assume they are entering all sorts of bad data.

To insert an apostrophe into the database using SQL you need to "double-up" the apostrophes. That is, put two apostrophes in the text where you want just one. For example, to insert the phrase "what's up?" into a database, the SQL code looks like:
INSERT INTO mytable (phrases) VALUES ('what''s up?')

In PHP there is a string function which allows you to do just this on variables quite easily: str_replace This function replaces one value with another in a string. So before you insert data in the database you should replace all single apostrophes with double-apostrophes. For the example variable, the PHP code is:
$description = str_replace("'","''",$description);

Note: This does not insert two apostrophes into the database, just one. So when you pull the data out of the database, it will contain only single apostrophes.

For complte script clik the title of posting

MySql : Create Database

We know what data we want and what we want to with that data. Now we need to create the database to store the data. The first thing to do is create an empty database to hold our table. For this tutorial, I name the database linksdb. In MySQL you use the following command from the command line to create the new database.

mysqladmin -p create linksdb

The "-p" flag is used so it will prompt you for your password to MySQL. Most setups require a username and password. Read the MySQL doumentation on how to setup the username and password using the mysqladmin tool. It is fairly straight forward, however you may need root access.

Test connecting to the database using the mysql client. On the command line:

mysql -p linksdb

You should get a mysql> prompt if it connects to the database. Else, it returns an error saying Unknown database 'linksdb'


Creating the Database Table
Next we need to create the table in the database. Tables are created using SQL statements, and can be created using the mysql client tool or PHP. The nice thing about using a PHP script to create the table is you can save the script to use later. If something goes wrong, you can recreate the tables. Or, you can use it to refer to the database schema.

The SQL command to create a table is:
CREATE TABLE tablename (
column1 column1type,
column2 column2type,
etc.... )

The table we want to create is:
Table Name: links

Columns: id (integer - primary link key)

sitename (50 characters)

siteurl (75 characters)

description (text field - lots of text)

category (50 characters)


So the SQL code to create the categories table is:
CREATE TABLE links (
id INT NOT NULL AUTO_INCREMENT,
sitename VARCHAR(50),
siteurl VARCHAR(75),
description TEXT,
category VARCHAR(50),
PRIMARY KEY(id) );

The id column is the primary key for this table. In order to be a primary key the column can not be null (NOT NULL). I also set it to automatically increment the number (AUTO_INCREMENT) so when each record is added the id will increase by one. The last line specifies that the id column will be the primary key for this table. A primary key is a unique number for that specific record or row of data.

The sitename, siteurl, and category column each are specifed as a VARCHAR or variable character field each with their respective maximum length. A VARCHAR field holds characters from 0 up to its maximum specified length. Most databases VARCHAR limit is 255 characters. If you have a field which may require more characters you should use a TEXT field, which is what is used for the description field. A text field can hold lots of data, usually dependent on the database.

The most common data types are INT, VARCHAR, TEXT and DATETIME. Look in the MySQL documentation about creating databases for information about other available datatypes in MySQL, and more specifics the data types it supports.


Executing SQL in PHP
The code to execute a SQL statement in PHP has 3 steps:
Connect to database
Create statement
Execute statement

1.The command to connect to the database is:

$cid = mysql_connect($host,$usr,$pwd);

Where $host, $usr, and $pwd are previously specified. Host refers to the machine running MySQL and the username and password to connect to that MySQL machine. This command returns a connection id, which is used for to identify this connection in later queries.

2.Creating the SQL statement simply consists of assigning the SQL statement such as the one above to a string. I usually have it run over multiple lines concatanating the string together as I go, this makes it easier to read.

3.The command to actual send the database the SQL command is:

$result = mysql_db_query($db,"$SQL",$cid);

Where $db is the database to query, $SQL is the SQL statement, and $cid is the connection id created above. This returns a 1 if executed correctly, and undefined or false if an error occurred.

Putting all of this together, and adding a little error detection and displaying of the errors gives us the following script, which you can download and load on to your web server running PHP. Note: Opening the file through the web server will execute the script and create the database tables.

Download: PHP Script to create tables (create_table.phps)
Note: You must edit the username and password at the top of this script to reflect your system.


Problems: The most common problem when running this script is connecting to the database. Make sure you replace the username and password variables with your username and password setup to connect to your MySQL server. You can trouble shoot connection problems using the mysql client from the shell, use mysql -? for help.

MySql : Getting Started

Getting started with a database means first figuring out what you want to do. Our sample project in this tutorial is building a database which manages links to sites. I want to be able to add, edit and remove links in the database, as well as display links from the database. I want the links to be categorized so I can display groups of links, instead of all links at once.

I want to record the following data for each link in the database:
Name of site
URL of site
Description of site
Link Category

Each of these will become one column in a table in the database. If you are not familiar with databases, a table can be thought of somewhat as a spreadsheet in Excel. The columns in a spreadsheet relate to the columns in a database. Each row in the spreadsheet would be considered a record (or row) in the database. There also can be multiple spreadsheets in a single Excel file, as there can be multiple tables in one database.

This application could use two tables, one table to store all of the categories and another table to store all the links, with a link back to the categories table. Because this is a beginner tutorial, I keep it simple by using just one table to store the info.

Advanced Topic
Two tables is the better way to create this application. The second table would store all the category names seperately with a unique key. The unique key is just a number for each category, usually starting at 1 for the first category, 2 for the next, and so on. A record in the links table, which are the individual links, would also store this category number with the link. This number is then used to refer back to the category table to get the category name when needed. It is a little more complicated but it is a much better way to create the database.

The benefits to using multiple tables is you get faster queries, and it is easier to manage the data. Some examples of the benefits of multiple tables:
Faster query when selecting the list of all categories because you are querying a smaller set of data.
Faster queries when selecting links by category, because the it is faster to select, group and sort numbers much faster then strings.
Easier to manage because you can change the category names seperately from the links, since they only store the numbers as reference to the category name.
Easier to enter data by category because you can pull the category name from the table and display it in a select form element instead of requiring the use to type it in.

Using multiple tables (or databases) and relating their data to each other using keys is referred to as relational databases. Read more about relational database design from the related links below.

RDBMS : Architecture of a Database Management System

The architecture of a database management system can be broadly divided into three levels :

a. External level
b. Conceptual level
c. Internal level



The External Level
This is the highest level, one that is closest to the user. It is also called the user view. The user view is different from the way data is stored in the database. This view describes only a part of the actual database. Because each user is not concerned with the entire database, only the part that is relevant to the user is visible. For example, end users and application programmers get different external views.

For example, an instructor will view the database as a collection of students and courses offered by the university. An administrator will view the database as a collection of records on the stock of course material provided by the university. The instructor is concerned with only a portion of database that is relevant to the instructor and the administrator is concerned with only the portion of database that is relevant to the administrator. These portions of database, which are viewed, by the instructor and administrator are reffered as their user’s view or external view.

Each user uses a language to carry out database operations. The application programmer uses either a conventional third-generation language, such as COBOL or C, or a fourth-generation language specific to the DBMS, such as visual FoxPro or MS Access.

The end user uses a query language to access data from the database. A query language is a combination of three subordinate language :
 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control Language (DCL)

The data definition language defines and declares the database object, while the data manipulation language performs operations on these objects. The data control language is used to control the user’s access to database objects.

The Conceptual Level
This level comes between the external and the internal levels. The conceptual level represents the entire database as a whole, and is used by the DBA. This level is the view of the data “as it really is”. The user’s view of the data is constrained by the language that they are using. At the conceptual level, the data is viewed without any of these constraints.

The Internal Level
This level deals with the physical storage of data, and is the lowest level of the architecture. The internal level describes the physical sequence of the stored records.
Following is an example of the three levels :

External
Cout << “Emp#” << Employee_Code;
Cout << “Dept#” << DepartmentEmployee_Code;
Cout << “Salary#” << Salary;

Conceptual
Employee
Employee_Code Character 6
Department_Code Character 4
Salary Numeric 5

Internal
Stored Employee Length=18
Prefix Type=Byte(6), offset=0
Emp# type=byte(6), offset=6, index empx
Dept# type=byte(6), offset=12,
Salary type=byte(6), offset=6

Mappings
Mapping determines the correspondence between one level an another. There are two levels of mapping involved in this architecture. One is between the external and the conceptual levels, while the other is between the conceptual and the internal levels. The external-conceptual mapping determines the correspondence between the conceptual anad the user views. This specifies how a user views the conceptual data. The conceptual-internal mapping determines the correspondence between the conceptual and internal views. It specifies how the conceptual data is stored.

The first step in designing a databse is to define the conceptual level. The conceptual level is then mapped to the external level. Each user view and the requirement is taken into consideration. Next, the conceptual-internal mapping is done. The way data is stored is derived from the conceptual level. Ths three-level architecture of a DBMS helps achieve data independence.

RDBMS : Functional Components of a Database Management System

A database management system comparises many modules each dealing with a specific responsibility of the overall system. The functional components of a database management system are :

a. Database Manager
The database manager is the central software component of a DBMS. It is responsible for converting users queries to appropriate system calls. It maintains the consistency and integrity of the database and enforces data security. It also synchronizes the simultaneous operations performed by concurrent users. Apart from these, it also handles backup and recovery operations.

b. File Manager
The file manager services all requests for data. It identifies the block containing the requested record. The block is then requested from the disk manager. The file manager selects the requested record from the block and transmits it to the database manager. The file manager is also responsible for managing storage space and structures.

c. Disk Manager
The disk manager performs all the physical input and output. It interacts with the file manager and does read or write on the storage device as requested by the file manager. It views all data as row data. The operations performed by the disk manager are :
 Retrieves blocks of data
 Replaces blocks of data
 Removes blocks of data

RDBMS : Recovery From Deadlock

Once it is determined that a deadlock exitx, the system needs to recover from the deadlock. For this, one or more transactions are rolled back to break the deadlock. While performing the roll back operation, the following issues need to be addressed :

a. Selection of a victim : in the situation of a deadlock, you first need to determine the transaction (or transaction) that should be rolled back to break the deadlock. Such a transaction is called the victim transaction. The transaction that will lead to minimum loss, in terms of cost, should be chosen for rollback. The following factors determine the cost of a rollback :
 How much execution has the transaction completed and for how much more time the transaction will execute to complete its task?
 How many data items were used by the transaction?
 How many more data items does the transaction need to complete?
 How many transaction will be included in the rollback?

b. Rollback : after determining the transaction to be rolled back, you need to determine how far the transaction is to be rolled back. The easlest answer to this problem is to do a total rollback, which means that the transaction will be aborted and restarted. However, it is better to roll back the transaction only till the point where the deadlock can be broken. This method requires the DBMS to maintain information about all current transaction.

c. Starvation : when the selection of a victim is based on cost factors, it might happen ihat the same transaction is selected as a victim every time a deadlock occurs. Due to this, the transaction might not be able to complete its task. Such a situation is called starvation. To avoid starvation, you need to ensure that a transaction is picked as a victim for only a fixed number of times. To ensure this, you can select a victim based on the number of rollbacks along with the cost factor.

RDBMS : Benefit of Database Management System

A major advantage that the database approach has over the conventional approach is that a database management system provides centralized of data.

Following are some of the benefits of the database approach :

a. Redudancy is reduced : in the database approach, applications do not have to maintain their own data file. The same course data file is used by the course scheduling application, the instructor scheduling application, and the semester planning application.

b. Inconsistency is avoided : because redundancy is reduced, inconsistency is also avoided. Consider a situation where ten students have enrolled for a course. This information is stored in the student data file. The other applications will use the same student file and therefore, no inconsistency can occur. If this information is recorded in more than one place, inconsistency can occur, as changes made in one data file may not get reflected in another data file.

c. Data is shared : while the existing applications can share data in the database, new applications can also be developed that will use the same database.

d. Standards are enforced : with centralized control of data, the DBA can ensure that standards are maintained in stored data formats. This is particularly useful for data interchange, or migration of data between two systems.

e. Security restrictions are applied : the DBA eensures that only authorized persons have access to the database. The DBA defines the security checks to be carried out. Differenet checks can be applied to different operations on the same data. For instance, a person may have access to query a file, but may not have the right to delete or update that file.

f. Integrity is maintained : inconsistency between two entries can lead to integrity problems. Even without redundancy, the database can still be inconsistent. For example, a student enrolls in 10 courses when the maximum number of courses a student can enroll is seven. Or, a student enrolls in a course that is not being offered in that semester. Such problems can be avoided in a DBMS by establishing certain integrity checks that are performed with any update operation.

RDBMS : Introduction of DBMS

A database is a collection of records. One of the major tasks in a computer system is to store and manage data. To handle this task, you need a specialized computer software known as a database management system (DBMS). Database management systems are design to maintain large volumes of data. Management of data involves :

a. Defining structures for data storage
b. Providing mechanism for data manipulation
c. Providing data security against unauthorized access

Database management system are now available on a wide range of computer, from desktops to mainframes. The size and power of the computer determine the system facilities, such as security and storage.

A single-user system allows only one person to access a database at any given time. In a multi-user system, several users can access a database simultaneously.

With the increasing power of desktop computer, multi-user database systems are now available that support a small group of users to connect these desktop computers and access data concurrently. These database systems can be scaled up to support hundreds or thousands of users, depending on the configuration of the hardware on which the database system is running.

The main objectives of any DBMS are to :

a. Provide an efficient and convenient environment that is used to store data in, and retrieve data from a database.
b. Manage information about users who interact with the DBMS and the activities that these users can perform on the data.