Database

In The Section We discuss All the Queries Related to DataBase

:><:Types Of Database:<>:

There are two main types of database; flat-file and relational. Which is the best one to use for a particular job will depend on factors such as the type and the amount of data to be processed; not to mention how frequently it will be used.

1-Flat-File

The flat-file style of database are ideal for small amounts of data that needs to be human readable or edited by hand. Essentially all they are made up of is a set of strings in one or more files that can be parsed to get the information they store; great for storing simple lists and data values, but can get complicated when you try to replicate more complex data structures. That's not to say that it is impossible to store complex data in a flat-file database; just that doing so can be more costly in time and processing power compared to a relational database. The methods used for storing the more complex data types, are also likely to render the file unreadable and un-editable to anyone looking after the database.
The typical flat-file database is split up using a common delimiter. If the data is simple enough, this could be a comma, but more complex strings are usually split up using tabs, new lines or a combination of characters not likely to be found in the record itself.
Database Management (DBM)
The Database Management Layer allows script programmers to store information as a pair of strings; a key, which is used to find the associated value. Essentially, a DBM adds more functionality and better sortation during storage to the binary flat-files that it uses. There are several versions of DBMs available, but the most popular is the Berkley Database Manager; also known as the Berkley DB.
The Berkley DB is an improvement over normal flat-files, as it provides a way for programmers to use the database without having to worry about how the data is stored or how to retrieve the values. Retrieval of data using the Berkley DB is often much faster than from a flat-file, with the time savings being made by storing data in a way that speeds up the locating of a specific key-value pair.
Creating, editing and deleting data when using the Berkley DB is actually quite simple; once the database has been tied to the script you just use and manipulate the variables as normal. The problem of file locking that plagues flat-file databases is still apparent when using DBM, so you should still take care when planning scripts that utilize it.

2-Relational

The relational databases such as MySQL, Microsoft SQL Server and Oracle, have a much more logical structure in the way that it stores data. Tables can be used to represent real world objects, with each field acting like an attribute. For example, a table called books could have the columns title, author and ISBN, which describe the details of each book where each row in the table is a new book.
The "relation" comes from the fact that the tables can be linked to each other, for example the author of a book could be cross-referenced with the authors table (assuming there was one) to provide more information about the author. These kind of relations can be quite complex in nature, and would be hard to replicate in the standard flat-file format.
One major advantage of the relational model is that, if a database is designed efficiently, there should be no duplication of any data; helping to maintain database integrity. This can also represent a huge saving in file size, which is important when dealing with large volumes of data. Having said that, joining large tables to each other to get the data required for a query can be quite heavy on the processor; so in some cases, particularly when data is read only, it can be beneficial to have some duplicate data in a relational database.

Database Comparisons

In most cases, you would want your database to support various types of relations; such databases, particularly if designed correctly, can dramatically improve the speed of data retrieval as well as being easier to maintain. Ideally, you will want to avoid the replication of data within a database to keep a high level of integrity, otherwise changes to one field will have to be made manually to those that are related.
While several flat-files can be combined in such a way as to be able to emulate some of the behaviours of a relational database, it can prove to be slower in practice. A single connection to a relational database can access all the tables within that database; whereas a flat file implementation of the same data would result in a new file open operation for each table.
All the sorting for flat-file databases need to be done at the script level. Relational databases have functions that can sort and filter the data so the results that are sent to the script are pretty much what you need to work with. It is often quicker to sort the results before they are returned to the script than to have them sorted via a script, few scripting languages are designed to filter data effectively and so the more functions a database supports, the less work a script has to do.
If you are only working with a small amount of data that is rarely updated then a full blown relational database solution can be considered overkill. Flat-file databases are not as scaleable as the relational model, so if you are looking for a suitable database for more frequent and heavy use then a relational database is probably more suitable.

                                                                                                                                       *************************

Basic SQL statements: DDL and DML

In the first part of this tutorial, you’ve seen some of the SQL statements that you need to start building a database. This page gives you a review of those and adds several more that you haven’t seen yet.

• SQL statements are divided into two major categories: data definition language (DDL) and data manipulation language (DML). Both of these categories contain far more statements than we can present here, and each of the statements is far more complex than we show in this introduction. If you want to master this material, we strongly recommend that you find a SQL reference for your own database software as a supplement to these pages.

Data definition language:

DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

• The create table statement does exactly that:
CREATE TABLE <table name> (
<attribute name 1> <data type 1>,
...
<attribute name n> <data type n>);

The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.
• The alter table statement may be used as you have seen to specify primary and foreign key constraints, as well as to make other modifications to the table structure. Key constraints may also be specified in the

CREATE TABLE statement.
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);

You get to specify the constraint name. Get used to following a convention of tablename_pk (for example, Customers_pk), so you can remember what you did later. The attribute list contains the one or more attributes that form this PK; if more than one, the names are separated by commas.
• The foreign key constraint is a bit more complicated, since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to in the parent table.

ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>)
REFERENCES <parent table name> (<attribute list>);

Name the constraint in the form childtable_parenttable_fk (for example, Orders_Customers_fk). If there is more than one attribute in the FK, all of them must be included (with commas between) in both the FK attribute list and the REFERENCES (parent table) attribute list.
You need a separate foreign key definition for each relationship in which this table is the child.
• If you totally mess things up and want to start over, you can always get rid of any object you’ve created with a drop statement. The syntax is different for tables and constraints.

DROP TABLE <table name>;
ALTER TABLE <table name>
DROP CONSTRAINT <constraint name>;

This is where consistent constraint naming comes in handy, so you can just remember the PK or FK name rather than remembering the syntax for looking up the names in another table. The DROP TABLE statement gets rid of its own PK constraint, but won’t work until you separately drop any FK constraints (or child tables) that refer to this one. It also gets rid of all data that was contained in the table—and it doesn't even ask you if you really want to do this!
• All of the information about objects in your schema is contained, not surprisingly, in a set of tables that is called the data dictionary. There are hundreds of these tables most database systems, but all of them will allow you to see information about your own tables, in many cases with a graphical interface. How you do this is entirely system-dependent.

Data manipulation language:

DML statements are used to work with the data in tables. When you are connected to most multi-user databases (whether in a client program or by a connection from a Web page script), you are in effect working with a private copy of your tables that can’t be seen by anyone else until you are finished (or tell the system that you are finished). You have already seen the SELECT statement; it is considered to be part of DML even though it just retreives data rather than modifying it.

• The insert statement is used, obviously, to add new rows to a table.
INSERT INTO <table name>
VALUES (<value 1>, ... <value n>);

The comma-delimited list of values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format 'yyyy-mm-dd' (for example, '2012-03-11').

Yes, you will need a separate INSERT statement for every row.

• The update statement is used to change values that are already in a table.
UPDATE <table name>
SET <attribute> = <expression>
WHERE <condition>;

The update expression can be a constant, any computed value, or even the result of a SELECT statement that returns a single row and a single column. If the WHERE clause is omitted, then the specified attribute is set to the same value in every row of the table (which is usually not what you want to do). You can also set multiple attribute values at the same time with a comma-delimited list of attribute=expression pairs.

• The delete statement does just that, for rows in a table.
DELETE FROM <table name>
WHERE <condition>;

If the WHERE clause is omitted, then every row of the table is deleted (which again is usually not what you want to do)—and again, you will not get a “do you really want to do this?” message.
• If you are using a large multi-user system, you may need to make your DML changes visible to the rest of the users of the database. Although this might be done automatically when you log out, you could also just type:
COMMIT;
• If you’ve messed up your changes in this type of system, and want to restore your private copy of the database to the way it was before you started (this only works if you haven’t already typed COMMIT), just type:
ROLLBACK;
Although single-user systems don’t support commit and rollback statements, they are used in large systems to control transactions, which are sequences of changes to the database. Transactions are frequently covered in more advanced courses...

                                                     ****************************