|
|
|
Database concepts for dummies
SURE database sounds cool and you have heard this word at least a hundred times by now. It’s another thing that you don’t understand the ABCs of it, and are scared to let others on to this embarrassing fact. Fret not, you are not alone. And the good news is you can easily find out about databases from millions of article on W3. Some basic concepts are presented here to get you started on the road to discovery.
A database is a collection of data organized in a manner that allows access, retrieval, and use of that data. In a manual database, you might record data on paper and store it in a filing cabinet. In a computerized database, the computer stores the data in an electronic format on a storage medium such as a floppy disk or hard disk.
Database software, also called DataBase Management System (DBMS), is software that allows you to create, access and manage a database. Using database software you can add, change and delete data in the database; sort and retrieve data from the database; and create forms and reports using the data in the database.
Types: Some of the commonly used database types are: operational, analytical, end-user, hypermedia, multidimensional and external.
Structures and models: There are a number of data models or database structures - in each different type of structure, the basic way in which records relate to one another is different. The model explained below, which uses two-dimensional tables to organize data is known as the Relational Model. Hierarchical and Network models are the older ones and are hardly used nowadays. A fourth model called the Object-oriented database structure is a newly emerging technology. However, its widespread use is constrained because of the financial cost of switching over from the Relational model which is used almost everywhere.
Basics
Relational model: With most popular personal computer database software packages, a database consists of a collection of tables or relations, organized in rows and columns. A record is a row in a table that contains information about a given person, product or event. A field is a column in a table that contains a specific piece of information within a record.
To make it even simpler, here is the hierarchy:
— Eight bits make up a byte, logically known as character; for instance “a”.
— A collection of characters make up a field, such as “Age.”
— A logical collection of related fields make up a record; such as eName, eAge, eAddress, ePhone (Where the “e” stands for employee whose name, age and address are being stored)
— A logical collection of records make up a file or table; for example EmployeeMaster (the table containing an employee’s personal or contact details)
— A logical collection of a number of interrelated tables make up a database; for instance, employee database (which contains other tables containing information about departments, salaries and benefits along with employees’ personal information)
So far so good? Ok, so lets move on.
Organization: Before creating a database, you should perform some preliminary tasks. Make a list of the data items you want to organize. Each of these data items will become a field in the database. To identify the different fields (data item), assign each field a unique name that is short yet descriptive. For instance, the field name for an item identification number could be ItemID.
One you determine the fields and field names, you must decide the field size and data type for each field. The field size is the maximum number of characters that a particular field can contain. The description field, for instance, may be defined as 25 characters in length, counting in space. Now if you try to insert a description for a particular item the length of which is greater than 25 characters, the database won’t accept it even if you threaten it with murder! Good news is that if you under-estimated the filed size at the time of creating the tables (right now we are in the process of creating a table); you can change it later on. The data type specifies the kind of data a field can contain and how the field is used. For instance, if you are creating the eAge field, you would define its data type as numeric, age is never alphabetic, ever heard of anyone s years old? Common data types include the following:
Text: It includes letters, numbers or special characters. (Yes, a number is text!)
Numeric: These are numbers only.
Currency: Obviously, units of money, such as dollar and cent amounts.
Data: This category include month, day and year information.
Memo: Includes lengthy text entries
Completing these steps provides a general description of the records and fields in a table, including the number of fields, field names, field sizes, and data types. These items collectively are known as the table structure.
Data entry: After you create a table structure, the next step is to enter individual records into a table, called populating the table. The database software allows you to create a data entry form, through which you can enter or modify records using the keyboard. As you are entering the data, the database software checks, or validates, the data. Validation is the process of comparing the data to a set of rules or values to determine if the data is correct. For instance, a field with a numeric data type restricts a user to only numbers into the field. Validation is important because it helps to ensure that the data entered into the database is error free.
Another way to enter data into a database is to import data from an existing file. For example, you can import data from a spreadsheet file into a database.
Data manipulation: Once the records are in the database, you can use the database software to manipulate the data to generate information. You can sort, or organize a set of records in a particular order, such as alphabetical or by date.
You can also retrieve information from the database by running a query, a request for specific data in the database. You can specify which data the query should retrieve by identifying criteria, which are restrictions the data must meet. For example, suppose you wanted to generate a list of all items that have a selling price less than Rs15. You could set up a query to list the Supplier Code, Name ItemID, Description, Units on hand, Cost, and Selling Price for all records that meet the criteria. Then you can sort the list by supplier name and instruct the database software to print or store the results of the query.
There is one important distinction that you must always be aware of. Database and Database Management System (DBMS) are two different things. Database contains the actual data whereas the DBMS is the software used to create, edit, modify or delete a database.
Language of database
At the backend of all relational database management systems is Structured Query Language (SQL). Three subsets of SQL are:
Data Definition Language (DDL): For creating database structure or table structures
Data Manipulation Language (DML): For manipulating the data in a database
Data Control Language (DCL): For assigning access privileges to tables.
The syntax of the most commonly needed SQL statements is given below for reference.
Data Definition Language: DDL includes statements for:
1. Creating a database:
CREATE SCHEMA
CREATE DOMAIN
CREATE TABLE
CREATE VIEW
2. Modifying the database structure:
ALTER DOMAIN
ALTER TABLE
3. Deleting a database:
DROP SCHEMA
DROP DOMAIN
DROP TABLE
DROP VIEW
Create a table using DDL:
CREATE TABLE table_name
(Column_name data_type [NULL | NOT NULL] [,…])
To delete a table from the database run the following DROP statement:
DROP TABLE table_name
Data Manipulating Language (DML): DML statements include:
— SELECT to query the database for data
— INSERT to insert data into a table
— UPDATE to modify or update data in a table
— DELETE to delete data from a table
The basic syntax of SELECT statement is:
SELECT *|data_field_list FROM table_name, {table_name2, ...} WHERE condition.
However, there are numerous variations of this statement.
Run the following INSERT statement for adding data for the first time into a table which has been created using the CREATE statement
INSERT INTO table_name[(column_list)]
VALUES [data_value_list]
Run the following UPDATE statement for updating existing data in a table
UPDATE table_name
SET [column_name =data_value column_name2= data_value2, … ]
Run the following delete statement for delete data from a table
DELETE FROM table_name
WHERE search_condition
SQL provides GRANT and REVOKE statements to allow security to be setup on the tables in a database
Syntax of grant statement:
GRANT {privilege_list | ALL PRIVILEGES}
ON object_name
TO {authorization_id_list | PUBLIC}
[WITH GRANT OPTION]
Granted privileges can be retracted using the revoke statement, the syntax of which is as follows:
REVOKE [GRANT OPTION FOR] {privilege_list | ALL PRIVILEGES}
ON object_name
FROM {authorization_id_list | PUBLIC}
I hope you have gained enough arsenals for your repository to enter the database war zone. Now using this knowledge as a launching pad you can traverse your way to becoming a great database champion. All the best!
The writer
|
|
|
|
