Tables are the structure where data is stored in the database.
In most cases, there is no way for the database vendor to know ahead of
time what your data storage needs are, then tables in the database makes itself. Many database tools allow you to create tables
automatically,but always tables are the container of all the data, it
is important to include the CREATE TABLE syntax in this tutorial.
A D V E R T I S E M E N T
Tables are divided into rows and columns.
Each row represents one piece of data, and each column
represent a component of piece of data.Example, if we have a
table for recording customer information, then the columns may include
information such as First Name, Last Name, birth Date, Address, City, Country,
and so on. As a result, when we specify a table, we include the column headers
and the data types for that particular column.
Data comes in a variety of forms. It could be
an integer (such as 1), a real number (such as 0.55),
a date/time expression (such as '2000-JAN-25 03:22:22'),a string (such as 'sql'), or even in binary
format. When we specify a table, we need to specify the data type associated
with each column (i.e., we will specify that 'First Name' is of type char(40) -
meaning it is a string with 40 characters). One thing to note is that different
relational databases allow for different data types.
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
Create Index Statement
Index are used to retrieve data from tables by quicker way. Let's use an example
it is much quicker for us to go to the index
section at the end of the book, locate which pages contain information that we want
and then go to these pages directly. Going to the index first saves us
time and is by far a more efficient method for locating the information we need.
This principle applies for retrieving data from a database table. Without an
index, the database system reads through the entire table (this process is
called a 'table scan') to locate the desired information. With the proper index
the database system can then first go through the index to find out
where to retrieve the data, and then go to these locations directly to get the
needed data. This is much faster.
Therefore, it is often desirable to create indexes on tables. An index can cover
one or more columns.
Syntax
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
Example
CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)
Primary Key
A primary key is used to uniquely identify each row in a table. It can either
be part of the actual record itself , or it can be an artificial field (one that
has nothing to do with the actual record). A primary key consist of one or
more fields on a table. When multiple fields are used as a primary key then it is
called composite key.
Primary keys can be specified either when the table is created
or by changing the existing table structure with alter command.
A foreign key is a field that points to the primary key of
another table. The purpose of the foreign key is to ensure referential integrity
means only values that are supposed to appear in the
database are permitted.
For example, say we have two tables, a CUSTOMER table that includes all customer
data, and an ORDERS table that includes all customer orders. The constraint here
is that all orders must be associated with a customer that is already in the
CUSTOMER table. In this case, we will place a foreign key on the ORDERS table
and have it relate to the primary key of the CUSTOMER table. By this way, we can
ensure that all orders in the ORDERS table are related to a customer in the
CUSTOMER table. In other words, the ORDERS table cannot contain information on a
customer that is not in the CUSTOMER table.
Both Table is given below:-
Table CUSTOMERS
column name
characteristic
SID
Primary Key
Last_Name
First_Name
Table ORDERS
column name
characteristic
Order_ID
Primary Key
Order_Date
Customer_SID
Foreign Key
Amount
In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
