1 2 3 4 5 > (Next)

Overview of Relational Databases, SQL and PL/SQL

Discuss the Theoretical and Physical Aspects of a Relational database

E.F. Codd Fathered the concept of the Relational Datamodel. A relational model organizes DATA into TABLES and only TABLES. Tables are the lingua franca of the relational model.

/-------------/        /------------------/     /------------/
/  Relational /--------/ Named Attributes /-----/   Tuples   /
/     Tables  /        /    (Columns or   /     /  (Rows or  / 
/-------------/        /       Fields )   /     /   Records) / 
                       /------------------/     /------------/

	 

A row and column intersection is called a "cell" The columns are placeholders, having data types such as character or integer.The rows themselves are the data.
A relational table must meet the following criteria:
1. The data stored in the cells must be atomic. Each cell can only hold one piece of data.When a cell contains more than one piece of information this is known as information coding
2. Data stored under columns must be of the same data type
3. Each row is unique (No duplicate rows)
4. Columns have no order in them
5. Rows have no order in them
6. Columns have a unique name
7. Two fundamental integrity rules: entity integrity rule :States that the primary key cannot be totally or partially empty. referential integrity rule : States that the foreign key must either be null or match currently existing value of the primary key that it references.

CODDS TWELVE RULES

1. The information Rule :Information is to be represented as data stored in cells.
2. The guaranteed access rule : Each data item must be accesible by a comination of table name + primary key of the row + column name .
3. NULLS must be used in a consistent manner: Nulls should be simply missing data and have no values.
4. An active, online data dictonary should be stored as relational tables and accesible through the regular data access language.
5. The data access language must provide all means of access and be the only means of access, except possibly for low level routines : If you can access a table thorugh a utility other than SQL it might violate this rule.
6. All views that can be updatable should be updatable: If you can join three tables as the basis of a view, but cannot update the view directly it violates this rule.
7. There must be set-level inserts, updates and deletes :
8. Physical data independence : An application cannot depend on a physical restructuring. If a file is moved to another disk it should not break the application.
9. Logical Data Independance : An application should not depend on a logical restructuring. If a file is split in two it should not break the application.
10. Integrity Dependance : Integrity rules should be stored in the data dictonary.
11. Distribution Independance : A database should continue to work properly even if distributed.
12. The nonsubversion rule: If low-level access is permitted it should not bypass security or integrity rules.

The major objective of physical design is to eliminate or at least minimize contention . Follow these rules to avoid contention :
1. Separate Tables and Indexes
2. Place large Tables and Indexes on disks of their own
3. Place frequently joined tables on separate disks, or cluster them.
4. Place infrequently joined tables on the same disks if necessary (if your short on disks)
5. Separate the RDBMS software from tables and indexes.
6. Separate the Data Dictonary from tables and indexes.
7. Separate the (undo) rollback logs and redo logs onto their own disks if possible.
8. Use RAID 1 for undo or redo logs
9. Use RAID 3 or 5 for Table Data.
10. Use RAID 0 for indexes.

Describe the Oracle Implementation the RDBMS and ORDBMS.

Oracle is entry-level SQL-92 complaint.It Offers SQL*Plus as an interactive SQL command interpreter which can run noninteractive scripts, and a procedural programming language known as PL/SQL ( modeled after ADA and Pascal ) (stored procedures and triggers are written in PL/SQL )

Oracle offers pre-compilers ( for embedded SQL) and a 3GL function called OCI ( Oracle Call Interface).



One of the major reasons to want an RDBMS with an Object-orientated interface (OORDBMS) is so that object orientated programs can communicate directly with the object-portion of the RDBMS instead of having to handle the object-relational mapping (composition-decompostion) dynamically within the code. Normally this situation was resolved through constructs such as cursors and software such as precompilers (Pro*C) this is known as impedance mismatch .

Oracle helps alleviate the problem of object-orientated developement and RDBMS back-end situation , with the following built-in object-orientated capabilities:

1. Relationships as Datatypes
2. Inheritance
3. Collections as Datatypes, including nesting (containers)
4. User-defined (extensible) datatypes
5. Improved large objects (LOBs)

Oracle extended the already complex RDBMS with the following:

1. Object Types: Records or classes
2. Object Views :
3. Object Language: Extensions to the Oracle SQL and PL/SQL
4. Object APIs : Objects supported through Oracle precompilers PL/SQL, OCI.
5. Object Portability : Through the object type translator (OTT) which can port for example an Oracle8 object type to a C++ class.

Despite these advancements Oracle does not support multiple inheritance, polymorphism, or constraints on object attributes (such as referential integrity).The Oracle8 Open Type System (OTS) is a repositary for all object types, as well as external object types from other languages or systems.

Within OTS, there is a datatype hierarchy that has as its foundation the built-in Oracle8 datatypes (VARCHAR2,NUMBER,etc).Also user defined datatypes can be built on any of the built-in datatypes plus previously user-defined datatypes. When creating user-defined datatypes these can be used :

1. As a column of a relational table
2. As an attribute within another object type.
3. As part of an object view of relational tables.
4. As the basis for an object table.
5. As the basis for PL/SQL variables.

Extended Oracle SQL manages the object types with the commands:

 CREATE TYPE , ALTER TYPE , DROP TYPE , GRANT/REVOKE TYPE  

Describe the use and benefits of PL/SQL

PL/SQL is a Procedural Language extension to Oracle's version of ANSI standard SQL. SQL is non-procedural language , the programmer only describes what work to perform. How to perform the work is left to the "Oracle Optimizer", in contrast PL/SQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next.

PL/SQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL. This results in a robust, powerful language suited for designing complex applications.

1 2 3 4 5 > (Next)