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