Friday, 9 January 2015

Essential Database Concepts - A Quick Overview

Database and Database Management System

We interact with many databases in our daily lives to get some information. For example, a phone book is a database of names and phone numbers, and an email list is a database of customer names and email addresses. A database can simply be defined as a collection of individual named objects (such as tables) to organize data. File cabinets used in an organization that carry folders and name tags are examples of paper database. From technology viewpoint, this kind of organized information handling is performed by special computer software, called database management system (DBMS). And just as file cabinets come in many different colors and sizes, each DBMS available today has its own characteristics. A good understanding of these characteristics will help you make better use of your DBMS. 


Database, Schemas, and tables


·         A database is a container that holds various schemas (sales, hr, finance, etc). A schema (e.g. Sales) is a collection of  individual named objects, such as tables, indexes, views, triggers and so on.
·         Related data is organized and stored in tables such as Products, Orders, Customers etc. A table is similar to a spreadsheet, containing rows and columns.
·         Data is stored under relevant columns in a table. For example, all order numbers are stored in the Orders table under the Order_ID column.  


Database Tables and Relationship

A relational database organizes data in tables under individual schemas. Each table comprises columns and rows. Columns report different categories (headings) of data, and rows contain the actual vales for each column. Relationship among database tables is formed with the help of Primary, Composite, and Foreign keys. The following figure illustrates an example of a related database containing two tables.



A related database with two tables


Primary Key

A primary key is a column or a set of columns in a database table that uniquely identifies each record in that table. In order to keep data integrity, every table must have a primary key. A primary key cannot be NULL and must not allow duplicates. In the above figure, the PRODUCT_ID column in the Products table is a primary key because it holds a unique value for each product. Besides unique identification of records, values in the primary key are used to create relationship with other database tables.

Foreign Key

You create relationship among database tables using matching columns. The above figure displays how PRODUCT_ID 1 and PRODUCT_ID 2 in the Products table relate to ORDER_ID 1000 in the Orders table. A primary key column in another table which creates a relationship between the two tables is called a foreign key. PRODUCT_ID is a foreign key in the Orders table. The foreign key value must exist in the table where it is a primary key. For instance, if you try to add a new order for PRODUCT_ID 4, the insert process will fail because there is no primary record for PRODUCT_ID 4 in the Products table.

Composite Key

It is a set of columns in a table combined together to form a unique primary key. As you can see in the above figure, the first two records in the Orders table carry 1000 for both records, so the ORDER_ID value is not unique for these records. However, combining ORDER_ID and PRODUCT_ID columns will create a unique primary key for the Orders table, which is called a composite key.

Data Type

Each column in a table has an associated data type which specifies what type of data the column can contain. For example, if the column were to contain a number (quantity of items in an order), the data type would be a numeric data type. If the column were to contain dates or text the appropriate data type would be used to store data accordingly. You select relevant data types to also restrict the type of data that can be stored in a column (for instance, to prevent recording of alphabetical or special characters into a numeric column). 

Data Integrity

By implementing the following four integrity constraint types, you ensure that your database is in a correct and consistent state.
·         Entity: This constraint type defines a primary key which should not be NULL and must contain a unique value.
·         Referential: It relates to foreign keys, which must match a primary key in another table, or be NULL.
·         Column: Values in the column must adhere to the defined data type. For example, a numeric column must not contain any alphabet.
·         User-defined: This includes compliance of data with the defined business rules. For example, customers’ credit limit should be less than or equal to 5000. It is implemented using the CHECK constraint.  

What is SQL?

SQL is a command language that you use to interact with databases. It provides you with a simple and efficient way to read and write data from and to a database. It is used in two different ways: embedded or interactively. In the former case, you embed SQL commands in a program created in a different programming platform (such as Java). In the later scenario, you enter SQL commands using your keyboard on a SQL command prompt or in a GUI software (such as SQL Developer), to get your desired information on your screen. This book is intended to deal with the later scenario where you'll be taught how to communicate with databases interactively.

Normally, the SQL language is divided into the following four command categories:

·         Data Definition Language (DDL)
·         Data Manipulation Language (DML)
·         Data Control Language (DCL)
·         Data Query Language (DQL) 

Data Definition Language (DDL)

The SQL data definition commands allow you to create, modify, and delete objects of a database.  Typical database objects include tables, views, procedures, users, triggers and so on. Almost all SQL data definition commands start with one of the following three keywords:

CREATE: To add new database objects such as tables, users etc.
ALTER: To modify the structure of an existing database object
DROP: To delete a database object

Data Manipulation Language (DML)

This category of SQL commands allows you to change the contents of your database. For this purpose, SQL offers three basic data manipulation commands:

INSERT: To add new rows (records) into a table
UPDATE: To modify column values of existing rows
DELETE: To delete rows from a table

Data Control Language (DCL)

Data control commands are used to control access to different database objects (tables, views and so on). Data control commands include GRANT and REVOKE. 

Data Query Language (DQL)

This category has just one command, but is the most significant one: SELECT. It is the sole command in SQL which is used to retrieve (query) data from a database.




No comments:

Post a Comment