KEY
A KEY is a value used to uniquely identify a
record in a table. A KEY could be a single column or combination of multiple
columns.
Primary key
A primary is a single column values used to
uniquely identify a database record.
It has following attributes
- A primary
key cannot be NULL
- A primary
key value must be unique
- The
primary key values can not be changed
The primary key must be given a
value when a new record is inserted.
Foreign Key
Foreign Key references primary
key of another Table! It helps connect Tables
- A foreign
key can have a different name from its primary key
- It ensures
rows in one table have corresponding rows in another
- Unlike
Primary key they do not have to be unique. Most often they aren’t.
- Foreign
keys can be null even though primary keys can not
Candidate key
A candidate key is a combination of
attributes that can be uniquely used to identify a database record without any
extraneous data.
- Each table may have one or more candidate keys.
- One of these candidate keys is selected as the table
primary key.
Database
Dependencies/Functional Dependencies
A dependency occurs in a database
when information stored in the same database table uniquely determines other
information stored in the same table. You can also describe this as a
relationship where knowing the values of one attribute (or a set of attributes)
is enough to tell you the value of another attribute (or set of attributes) in
the same table.
Saying that there is a dependency between attributes in a table is the same as
saying that there is a functional dependency between those attributes. If there
is a dependency in a database such that attribute B is dependent upon attribute
A, you would write this as
“A->B”.
For example, in a table listing employee characteristics including Social
Security Number (SSN) and name, it can be said that name is dependent upon SSN
(or SSN -> name) because an employee's name can be uniquely determined from
their SSN. However, the reverse statement (name -> SSN) is not true because
more than one employee can have the same name but different SSNs.
Trivial Functional Dependencies
A trivial functional
dependency occurs when you describe a functional dependency of an attribute
on a collection of attributes that includes the original attribute. For
example, “{A, B} -> B” is a trivial functional dependency, as is “{name,
SSN} -> SSN”. This type of functional dependency is called trivial because
it can be derived from common sense. It is obvious that if you already know the
value of B, then the value of B can be uniquely determined by that knowledge.
Full Functional Dependencies
A full functional dependency
occurs when you already meet the requirements for a functional dependency and
the set of attributes on the left side of the functional dependency statement
cannot be reduced any farther. For example, “{SSN, age} -> name” is a
functional dependency, but it is not a full functional dependency because you
can remove age from the left side of the statement without impacting the
dependency relationship.
Transitive Dependencies
Transitive dependencies
occur when there is an indirect relationship that causes a functional
dependency. For example, ”A -> C” is a transitive dependency when it is true
only because both “A -> B” and “B -> C” are true.
Multivalued Dependencies
Multivalued dependencies
occur when the presence of one or more rows in a table implies the presence of
one or more other rows in that same table. For example, imagine a car company
that manufactures many models of car, but always makes both red and blue colors
of each model. If you have a table that contains the model name, color and year
of each car the company manufactures, there is a multivalued dependency in that
table. If there is a row for a certain model name and year in blue, there must
also be a similar row corresponding to the red version of that same car.
Importance of Dependencies
Database dependencies are
important to understand because they provide the basic building blocks used in database
normalization. For example:
- For a
table to be in second normal form (2NF), there must be no case of a
non-prime attribute in the table that is functionally dependendent upon a
subset of a candidate key.
- For a
table to be in third normal form (3NF), every non-prime attribute must
have a non-transitive functional dependency on every candidate key.
- For a
table to be in Boyce-Codd Normal Form (BCNF), every functional dependency
(other than trivial dependencies) must be on a super key.
- For a
table to be in fourth normal form (4NF), it must have no multivalued
dependencies.
The Data Definition Language (DDL)
Definition: The Data
Definition Language (DDL) is one of two major components of the Structured
Query Language (SQL). It is used to alter the structure of tables within a
relational database. Some of the major commands comprising DML are CREATE TABLE,
DROP TABLE and CREATE INDEX.
CREATE TABLE statement
A commonly used CREATE command is the CREATE
TABLE command. The typical usage is:
CREATE
[TEMPORARY] TABLE [table name] ([column definitions]) [table
parameters].
For example, the command to create a table named employees
with a few sample columns would be:
CREATE TABLE employees
(
id INTEGER PRIMARY KEY,
first_name VARCHAR(50) NULL,
last_name VARCHAR(75) NOT NULL,
dateofbirth DATE NULL
);
DROP statements
Drop - To destroy an existing database,
table, index, or view.
DROP objecttype
objectname.
For example, the command to drop a table named employees
would be:
DROP TABLE employees;
The DROP statement is distinct from the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself. For example, a DELETE statement might delete some (or
all) data from a table while leaving the table itself in the database, whereas
a DROP statement would
remove the entire table from the database.
ALTER statements
Alter - To modify an existing database
object.
ALTER objecttype
objectname parameters.
For example, the command to add (then remove) a
column named bubbles for an existing table named sink would be:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sinks DROP COLUMN
bubbles;
Referential integrity
statements
Finally, another kind of DDL sentence in SQL is
one used to define referential integrity relationships, usually implemented as primary
key and foreign key tags in some columns of the tables.
These two statements can be included inside a CREATE TABLE or an ALTER TABLE sentence.
The Data Manipulation Language (DML)
The Data Manipulation Language
(DML) is one of two major components of the Structured Query Language (SQL). It
is used to insert, retrieve and modify data stored within relational databases.
The major commands comprising DML are SELECT, INSERT, DELETE and UPDATE.
VIEWS
A view is a logical
representation of another table or combination of tables. A view derives its
data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or
might be views themselves. All operations performed on a view actually affect
the base table of the view. You can use views in almost the same way as tables.
You can query, update, insert into, and delete from views, just as you can
standard tables.
Views can provide a different representation
(such as subsets or supersets) of the data that resides within other tables and
views. Views are very powerful because they allow you to tailor the
presentation of data to different types of users.
There are two primary reasons to
provide users with access to data through views rather than providing them with
direct access to database tables:
- Views provide
simple, granular security. You can use a view to limit the data that a
user is allowed to see in a table. For example, if you have an employees
table and wish to provide some users with access to the records of
full-time employees, you can create a view that contains only those
records. This is much easier than the alternative (creating and
maintaining a shadow table) and ensures the integrity of the data.
- Views
simplify the user experience. Views hide complex details of your
database tables from end users who do not need to see them. If a user
dumps the contents of a view, they won’t see the table columns that aren’t
selected by the view and they might not understand. This protects them
from the confusion caused by poorly named columns, unique identifiers and table
keys.
Creating a View
Creating a view is quite
straightforward: you simply need to create a query that contains the
restrictions you wish to enforce and place it inside the CREATE VIEW command.
Here’s the syntax:
CREATE VIEW viewname AS
<query>
For example, if you wish to
create the full-time employees view you would issue the following command:
CREATE VIEW fulltime AS
SELECT first_name, last_name, employee_id
FROM employees
WHERE status='FT'
Modifying a View
Changing the contents of a view
uses the exact same syntax as the creation of a view, but you use the ALTER
VIEW command instead of the CREATE VIEW command. For example, if you wanted to
add a restriction to the fulltime view that adds the employee’s telephone
number to the results, you would issue the following command:
ALTER VIEW fulltime AS
SELECT first_name, last_name, employee_id, telephone
FROM employees
WHERE status='FT'
Deleting a View
It’s simple to remove a view from
a database using the DROP VIEW command. For example, if you wish to delete the
full-time employees view, you would use the following command:
DROP VIEW fulltime
INDEXES
An index is a database feature
used for locating data quickly within a table. Indexes are defined by selecting
a set of commonly searched attribute(s) on a table and using the appropriate
platform-specific mechanism to create an index.
Examples:
Personnel information may be
store in a Human Resource department's employee table. Clerks find that they
often search the table for employees by last name but get slow query responses.
Defining an index on the table consisting of the last name attribute would
speed up these queries.
Synonyms
A synonym is an alternative name
for objects such as tables, views, sequences, stored procedures, and other database
objects.
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
The or replace phrase allows you
to recreate the synonym (if it already exists) without having to issue a DROP
synonym command.
The public phrase means that the
synonym is a public synonym and is accessible to all users. Remember though
that the user must first have the appropriate privileges to the object to use
the synonym.
The schema phrase is the
appropriate schema. If this phrase is omitted, Oracle assumes that you are
referring to your own schema.
The object_name phrase is the
name of the object for which you are creating the synonym. It can be one of the
following:
- table
- view
- sequence
- stored
procedure
- function
- package
- materialized
view
- java class
schema object
- user-defined
object
- synonym
For Example
create public synonym suppliers
for app.suppliers;
This first example demonstrates how to create a
synonym called suppliers. Now, users of other schemas can reference
the table called suppliers without having to prefix the table name
with the schema named app. For example:
select * from suppliers;
If this synonym already existed and you wanted to
redefine it, you could always use the or replace phrase as
follows:
create or replace public synonym suppliers
for app.suppliers;
Dropping a synonym
It is also possible to drop a synonym. The syntax
for dropping a synonym is:
drop [public] synonym [schema .] synonym_name [force];
The public phrase allows you to
drop a public synonym. If you have specified public, then you
don't specify a schema.
The force phrase will force
Oracle to drop the synonym even if it has dependencies. It is probably not a
good idea to use the force phrase as it can cause invalidation
of Oracle objects.
For Example
drop public synonym suppliers;
This drop statement would drop the synonym called
suppliers that we defined earlier.
Data
Dictionary
The
data
dictionary is where the RDBMS stores the logical and
physical structure of the database; e.g. the objects
in
the database, the structure of your tables, where the data files are, which
extents are used by which segments, what constraints exist, and so on.
Sequences
A sequence is a database object that generates
numbers in sequential order. Applications most often use these numbers when
they require a unique value in a table such as primary key values. Some
database management systems use an "auto number" concept or
"auto increment" setting on numeric column types. Both the auto
numbering columns and sequences provide a unique number in sequence used for a
unique identifier. The following list describes the characteristics of
sequences:
·
Sequences
are available to all users of the database
·
Sequences
are created using SQL statements (see below)
·
Sequences
have a minimum and maximum value (the defaults are minimum=0 and maximum=263-1);
they can be dropped, but not reset
·
Once a
sequence returns a value, the sequence can never return that same value
·
While
sequence values are not tied to any particular table, a sequence is usually
used to generate values for only one table
·
Sequences
increment by an amount specified when created (the default is 1)
Creating a Sequence
To create sequences, execute a CREATE SEQUENCE statement
in the same way as an UPDATE or INSERT statement. The sequence information is
stored in a data dictionary file in the same location as the rest of the data
dictionary files for the database. If the data dictionary file does not exist,
the SQL engine creates the file when it creates the first sequence. In legacy
dictionaries, the new file name is SEQUENCE.DD. The format of this file remains
proprietary and subject to change, so do not depend on the record layout or
format of the data. In Journaled Filesystem databases, this information is also
proprietary and subject to change.
The format for a CREATE
SEQUENCE statement is as follows:
CREATE SEQUENCE sequence_name
[INCREMENT BY #]
[START WITH #]
[MAXVALUE # | NOMAXVALUE]
[MINVALUE # | NOMINVALUE]
[CYCLE | NOCYCLE]
Variable
|
Description
|
INCREMENT BY
|
The increment value. This can be a positive or
negative number.
|
START WITH
|
The start value for the sequence.
|
MAXVALUE
|
The maximum value that the sequence can
generate. If specifying NOMAXVALUE,
the maximum value is 263-1.
|
MINVALUE
|
The minimum value that the sequence can
generate. If specifying NOMINVALUE,
the minimum value is -263.
|
CYCLE
|
Specify CYCLE
to indicate that when the maximum value is reached the sequence starts over
again at the start value. Specify NOCYCLE
to generate an error upon reaching the maximum value.
|
Dropping a Sequence
To drop a sequence, execute a DROP SEQUENCE
statement. Use this function when a sequence is no longer useful, or to reset a
sequence to an older number. To reset a sequence, first drop the sequence and
then recreate it.
Drop a sequence following this format:
DROP SEQUENCE my_sequence
Using a Sequence
Use sequences when an application requires a
unique identifier. INSERT
statements, and occasionally UPDATE
statements, are the most common places to use sequences. Two
"functions" are available on sequences:
NEXTVAL: Returns the next value from the
sequence.
CURVAL: Returns the value from the last
call to NEXTVAL by the current
user during the current connection. For example, if User A calls NEXTVAL and it returns 124, and User B
immediately calls NEXTVAL
getting 125, User A will get 124 when calling CURVAL,
while User B will get 125 while calling CURVAL.
It is important to understand the connection between the sequence value and a
particular connection to the database. The user cannot call CURVAL until making
a call to NEXTVAL at least once on the connection. CURVAL returns the current
value returned from the sequence on the current connection, not the current
value of the sequence.
Examples
To create the sequence:
CREATE SEQUENCE customer_seq
INCREMENT BY 1 START WITH 100
To use the sequence to enter a record into the
database:
INSERT INTO customer (cust_num,
name, address)
VALUES (customer_seq.NEXTVAL, 'John
Doe', '123 Main St.')
To find the value just entered into the database:
SELECT customer_seq.CURVAL AS
LAST_CUST_NUM
