Mysql Databases used for web programming

An open source relational database management system, developed by MySQL AB, a subsidiary of Sun Microsystems (which has recently been bought by Oracle, the makers of Oracle Database).

It is used to store information. MySQL can store many types of data from something as tiny as a single character to as large as complete files or graphics. Although it can be accessed by most programing languages, it is often coupled with PHP because they work together with ease.

Information stored in a MySQL database hosted on a web server can be accessed from anywhere in the world with a computer. This makes it a good way to store information that needs the ability to change over time, but also needs to be accessed over the net. Some examples that can utilize MySQL are a web message board or a customer's shipping status.

It is also used in conjunction with scripting languages such as JSP and PHP, which runs on Linux and Windows among other platforms.

A freeware database based upon SQL standards. Known for reliability and speed, but lacking in the functionality and scalability of a bigger database system like Oracle.

Foremost recommend MySQL to customers who require a simple database solution, perhaps with a web-based interface, as an inexpensive alternative to an Oracle solution. We have built several web sites with MySQL back-ends.

HostingPalace has the innovative webhosting technology to implement in the web hosting domestic market. The web hosting Panel provided by HostingPalace as control panel of your domain is one among the best in the current market. It is your domain control panel, from where you manage all aspects of your domain and its contents. The domain control panel provided by web hosting company has been designed with the intention of making it easier for an individual to even act as a domain registrar possessing an authority to register a domain for self or for its clients and every now and then on the basis of requirement he can modify the web hosting account with every new update hence the domain resellers can benefit from such technology with the authority to register domain for its clients. It has become more user-friendly and more reliable.

When you access your web hosting account, everything you need is available right there in hosting panel or domain control panel itself.

The main tools available within your panels let you do the basic domain and webspace administration required to keep your website in order. You can set or reset your login details, ftp details and email accounts from web hosting control panel. You can access and maintain all your databases from your web hosting control panel as well, review basic statistics of your website, check your bandwidth use, check which scripts are supported, block certain IP addresses(depends on the web hosting package terms) from accessing your website, check for and clear up viruses, make a backup of your entire site, and other general maintenance actions, or grooming, of your domain.

If your web hosting plan allows it, you can actually set up different domains within your single account and control them all through your hosting or domain control panel.

Within your webhosting panel, you will more often than not find a handy little extra applications called file manager. It is what made easier for a client to deploy website files in webspace without taking the help of ftp account.Inbuilt feature of webhosting panel helps doing so, and this brilliant tool really comes as a handy element for hosting resellers who in this case every now and then need not have to memorize or search for ftp login information for different domains of its clients . It is not restricted to limited upload or download. One can deploy unlimited files in its webspace using the feature however restricted to limited upload in some online software at one time due to unavailability to browse for unlimited files. Bandwidth doesnt get much affected with such move and unlimited upload and download can easily be taken into process(incase the package has unlimited webspace and bandwidth facility).

Some hosting panel has the feature of adding java applications separately to its webspace package. As java is an important and widely used application most of the web hosting companies will make it sure the compatible features to enable the java application resides in the online software or control panel.

Many web hosting companies have added online shopping application in its webspace packages which helps clients to add up the application to its website at ease rather swaying the process of manipulation and editing. These comes free nowadays with web hosting packages. Most of the web hosting companies have included such applications for free in its web hosting package to let their client get benefited for hosting web applications.

HostingPalace has started providing free search engine submission for its 2 years old clients.Based on the demand of our clients which they are looking to get for free along with their web hosting i.e webspace packages HostingPalace has stated implementing this technique to help our clients to submit their website URL for free in over 8 lakh search engines.

Relational databases, such as MySQL, are so called as they rely on a series of relationships to connect data stored in different tables. Joining tables based on a common ID number. This ID number was stored as the uniquely valued field (Primary Key) in one table (for example in the artists table), but was also present in another table (the cds table) acting as a reference (Foreign Key) that allowed data to be retrieved from both tables when the values matched. For ease of understanding we used unique numbers, but a key can have any value provided it conforms to a few rules.

When were we were looking at the physical database in the other workshops we used certain terms such as TABLE, COLUMN, ROW and FIELD.

Relation/ Entity - These are the same as a table.

Attributes - These are similar to columns in that they describe the type of data stored.

Domain - This is values within the same attribute (a collection of fields that exist in the same column).

Tuple - This is a record similar to a row.

The Primary Key is frequently used to identify a specific row (although other keys may exist) and all the other values are dependent on that value to be meaningfully identified. A primary key is usually one attribute (column) in a relation (table), but can be a combination of attributes (Composite Key). If we consider the following attributes some cds may have.

The obvious Primary Key for a CDs table would be the Catalogue No. as each cd has already been given a unique identifier. But consider if we knew for a FACT that each artist could only release one CD per year

By adding the Year attribute we could also use the combination of Artist and Year to identify the row, as together they form a Composite Key. Thus we have two potential or Candidate Keys. We would probably still choose the Catalogue No. as it is simpler to use one column. Consider a Radio station wishing to record how many times a day they played a CD. The combination of Catalogue No. and the date would be sufficient to identify that record in a new Play History table

In this situation the Catalogue No. number is a Primary Key in the CDs table but only part of the Composite Key in the Play History table as the date is required to form the whole key. An alternative could have been to create a new unique single-attribute Primary Key for the table. The catalogue number would become the Foreign Key in the Play History table, i.e. a Foreign Key is a attribute (column) in a table that refers to a Primary Key in another table.

NULLs are used in databases to signify that no value exists, but are not really values themselves. Instead they are just an indictor that a particular field has no value. Some argue this is better that leaving a blank value or having a default value. I personally see little value in a blank field, but default values (such as inserting the current date if no date is offered) can be useful. The interesting point about NULLs is that no Primary Key can contain a NULL and thus it's useful when comparing Candidate Keys to see if one could potentially contain a NULL, thus disqualifying that key.

This is another important concept and describes the relationship that columns have to the Primary Key. If the value of a column can be determined by knowing the value of the Primary Key and no other, then that column is said to be functionally dependent. Or more simply, if we know the value of Primary Key we can find out the value of any other dependent column. This dependency is often written as follows (where A is the Primary Key and B is the dependent column

When describing tables there is a way of expressing their structure that we will follow here. This includes the name of the relation, the attributes within the relation and which attributes are key. The format of this is:

RELATION (attribute one, attribute two, attribute three etc)

NOTE: The key attribute is underlined to signify its status and the relation is always UPPERCASE.

For example consider how we might express the CDs table above:

CDS (Catalogue No, Artist, Year, Title)

There are several stages of normalisation that a database structure can be subject to, each with rules that constrain the database further and each creating what is called a Normal Form.

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5FN)

s the database goes through each stage of normalisation it is said to be in the normal form of that stage, i.e. my database is 2NF and I need it to be 3NF. We are not going to take a detailed look at all of these Normal Forms as BCNF, 4NF and 5NF are probably overkill for small to medium databases, with the first 3 normal forms usually being sufficient. There is also one other stage - that of the Un-normalised Normal Form (UNF) which is the starting point for Normalisation. To begin our examination we must first create this Un-normalised data.

For a relation (table) to conform be 1NF, it must obey the following rules:

To ensure that all attributes (columns) are atomic - what this means is that any single field should only have a value for ONE thing. For example in a 'cars' database a field in a car may have a value 'Blue Ford Focus'. This field is not atomic as it contains more than one piece of information (colour, manufacturer and model). To demostrate the problem with this, try to imagine constructing a query to select all Ford cars. This would be difficult as the value 'Ford' is trapped in the middle of the field. We must therefore split this column into three (colour, manufacturer, model).

There must be no repeating groups. This is perhaps most easily understood as there cannot be repeating columns (that which contain the same type of information). For example a cars field may contain a list of Ford cars (Focus, Mondeo, Puma). As we know we must also make this field atomic, but splitting the column into three (cars1, cars2, cars3) to hold these values isn't an option this time as that would create three domains that contain similar data. In practice this would make querying the databases difficult as we would have to create a statement to match potential values in 3 columns (or four, or five or a hundred if there was a list of a hundred). Splitting the field so that each value was in a separate row would require the other values in the rows to be repeated. The solution is to change the structure of the database by extracting the repeating groups into a new relation.

If we have a look at the remaining non-atomic attributes we can see that they are similar informational attributes about a CD. In order to make these attributes atomic we cannot repeat the trick of creating additional columns as each repeated column would hold values of a similar type.

If we split each original field into 3 we can see the value 'Christina Aguilera' appears twice, once in Artist_Two and once in Artist_Three. If we were searching to see who had borrowed CDs by Christina we'd have to create a query to search ALL the artist fields. Also if someone borrowed a 4th CD as part of a transaction we would have to create a fourth Artist column and so on. The other reason that repeating groups are not allowed to be split into columns is that we MUST know the number of columns required in order to create the database.

The second normal form has one rule that must be followed:

All non-key attributes must be dependant ON THE WHOLE KEY and not just one attribute of the key. This obviously only applies to Composite Keys and means that attributes in the relation that only contain information (and have no role in the structure of the database) must be functionally dependent on all parts of the Key. Another way of thinking about this is that if there are some attributes that are not dependent on all parts of the Key this means that they are in the wrong relation.

To achieve Third Normal Form no attribute must be dependent on a non-key attribute. This means that every informational attribute must be DIRECTLY dependent on the Primary Key and not on another column. If we again look at an imagined cars database, a customer table contains information such as address, city, postcode and also a column called shipping cost. The value of shipping cost changes in relation to where the car should be delivered, and thus is not directly dependent on the customer, but the city. Thus we would need to create another separate relation to hold the information about cities and shipping costs.

As mentioned above there are more Normal Forms that we could use to normalise our database further. We are not going to do this, because in practice the anomalies that they are designed to remove are rare. We will however have a brief look at BCNF so that there is at least some understanding of that norm should you come across it. If you are confused or struggling with the above, it would perhaps be worth skipping past this section until you are more confident about your understanding of the first three Normal Forms.

This often referred to as a strong 3NF and states that each determinant must be a Candidate Key. In a relation there may be more than just the Primary Key from which we can derive the values of other attributes, i.e. from another attribute or combination of attributes. These are known as Candidate Keys and in 3NF it is possible to have only some of the attributes functionally dependent on them. In BCNF all attributes must depend on all Candidate Keys.

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Commit

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Rollback

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

all developers must know specifially the relationships that should be there and work from the parent table down through all the child tables when doing inserts or updates and work from the child tables up through the parent tables when doing deletes. Of course the problem is that not every developer will be aware of all the tables involved in constraints aren't set. And if you have layers of relationships, you need to go allthe way to the bottom of the chain to do a delete.

If you use an ORM, I think you can define relationships there? Not sure never used one but I think you can. Worth looking into at any rate.

If you aren't using an ORM and you can't define referential integrity, at least store the relationships in a tables somewhere so the developers can look up what tables would be affected.

Another approach if you can't define referential integrity specfically would be to enforce it through triggers.

edited to expand on triggers: If you create an instead of trigger (are these available in my sql?) on the parent table you can mimic the behavior of a cascading delete by specifying the tables to delete from before doing the actual delete (this how we used to do it in the old days before we had cascading deletes). You can also specify the tables to update if the primary key changes in an update (hopefully your design has a key that won't change, but not if you used any natural keys). An insert trigger on any child tables would see if the key field value existed in the parent table and then reject it if it did not.

The relationships between columns located in different tables are usually described through the use of keys.

As you can see we have a PRIMARY KEY in each table. The Primary key serves as a mechanism to refer to other fields within the same row. In this case, the Primary key is used to identify a relationship between a row under consideration and the person whose name is located inside the 'names' table. We use the AUTO_INCREMENT statement only for the 'names' table as we need to use the generated contact_id number in all the other tables for identification of the rows.

This type of table design where one table establishes a relationship with several other tables is known as a 'one to many' relationship.
In a 'many to many' relationship we could have several Auto Incremented Primary Keys in various tables with several inter-relationships.

Since version 3.23.23, Full Text Indexing and Searching has been introduced into MySQL. FULLTEXT indexes can be created from VARCHAR and TEXT columns. FULLTEXT searches are performed with the MATCH function. The MATCH function matches a natural language query on a text collection and from each row in a table it returns relevance.The resultant rows are organized in order of relevance.

Full Text searches are a very powerful way to search through text. But is not ideal for small tables of text and may produce inconsistent results. Ideally it works with large collections of textual data.

Well, databases do tend to get large at some or the other. And here arises the issue of database optimization. Queries are going to take longer and longer as the database grows and certain things can be done to speed things up.

The easiest method is that of 'clustering'. Suppose you do a certain kind of query often, it would be faster if the database contents were arranged in a in the same way data was requested. To keep the tables in a sorted order you need a clustering index. Some databases keep stuff sorted automatically.

These are a kind of 'lookup' tables of sorts. For each column that may be of interest to you, you can create an ordered index.
It needs to be noted that again these kinds of optimization techniques produce a system load in terms of creating a new index each time the data is re-arranged.

Replication is the term given to the process where databases synchronize with each other. In this process one database updates it's own data with respect to another or with reference to certain criteria for updates specified by the programmer. Replication can be used under various circumstances. Examples may be : safety and backup, to provide a closer location to the database for certain users.

In an RDBMS, when several people access the same data or if a server dies in the middle of an update, there has to be a mechanism to protect the integrity of the data. Such a mechanism is called a Transaction. A transaction groups a set of database actions into a single instantaneous event. This event can either succeed or fail. i.e .either get the job done or fail.

The definition of a transaction can be provided by an Acronym called 'ACID'.

If an action consists of multiple steps - it's still considered as one operation.

The database exists in a valid and accurate operating state before and after a transaction.

Processes within one transaction are independent and cannot interfere with that in others.

Changes affected by a transaction are permanent.

To enable transactions a mechanism called 'Logging' needs to be introduced. Logging involves a DBMS writing details on the tables, columns and results of a particular transaction, both before and after, onto a log file. This log file is used in the process of recovery. Now to protect a certain database resource (ex. a table) from being used and written onto simulatneously several techniques are used. One of them is 'Locking' another is to put a 'time stamp' onto an action. In the case of Locking, to complete an action, the DBMS would need to acquire locks on all resources needed to complete the action. The locks are released only when the transaction is completed.

Now if there were say a large numbers of tables involved in a particular action, say 50, all 50 tables would be locked till a transaction is completed.

To improve things a bit, there is another technique used called 2 Phase Locking or 2PL. In this method of locking, locks are acquired only when needed but are released only when the transaction is completed.

This is done to make sure that that altered data can be safely restored if the transaction fails for any reason.

This technique can also result in problems such as "deadlocks".

In this case - 2 processes requiring the same resources lock each other up by preventing the other to complete an action. Options here are to abort one, or let the programmer handle it.

MySQL implements transactions by implementing the Berkeley DB libraries into its own code. So it's the source version you'd want here for MySQL installation. Read the MySQL manual on implementing this.

A view allows you to assign the result of a query to a new private table. This table is given the name used in your VIEW query.
Although MySQL does not support views yet a sample SQL VIEW construct statement would look like:

CREATE VIEW TESTVIEW AS SELECT * FROM names;

A trigger is a pre-programmed notification that performs a set of actions that may be commonly required. Triggers can be programmed to execute certain actions before or after an event occurs. Triggers are very useful as they they increase efficiency and accuracy in performing operations on databases and also are increase productivity by reducing the time for application development. Triggers however do carry a price in terms of processing overhead.

When the capabilities of a database are integrated with object programming language capababilities, the resulting product is an ODBMS. Database objects appear as programming objects in an ODBMS. Using an ODBMS offers several advantages. The ones that can be most readily appreciated are:

When you use an ODBMS, you're using data the way you store it. You will use less code as you're not dependent on an intermediary like SQL or ODBC. When this happens you can create highly complex data structures through your programming language.

When data is stored the way you'd like it to be stored (i.e. natively) there is a massive performance increase as no to-and-fro translation is required.

--
http://www.co5.in/