DATABASE:
A database is an organized collection of information.
DATABASE MANAGEMENT SYSTEM (DBMS):
To manage databases we need DBMS. A DBMS is a program that
stores, retrieves, and modifies data in the databases on request.
RELATIONAL DASTABASE MANAGEMENT SYSYTEM:
A Relational Database is a collection of relations of two dimensional
tables.
TERMINOLOGIES USED IN RDBMS:
1) Table:
A Table is the basic storage structure of an RDBMS. A table stores all the data necessary about something in the real world.
Example:
Employees.
2) Single Row or Tuple:
Representing all data required for a particular employee. Each row in
a table may be identified by a Primary Key, which allows no duplicate
rows.
3) Column or Attribute:
Usually refers to a characteristic of an entity.
4) Primary Key:
A Field which uniquely identifies a row.
5) Foreign Key:
A Foreign Key is a column that identifies how tables relate to each
other. A foreign key refers to a Primary Key in another table.
RELATING MULTIPLE TABLES.
1.Each row of data in a table is uniquely identified by a Primary Key.
2.You can logically relate data from multiple tables using foreign keys.
Example:
EMP may have empno,ename,job,deptno where empno is a primary
key and deptno is a foreign key.
DEPT may have deptno,dname,loc where deptno is a primary key.
GUIDELINES FOR PRIMARY AND FOREIGN KEYS: No duplicate values are allowed in a primary key.
Primary keys generally cannot be changed.
Foreign keys are based on data values and are purely logical, not
physical pointers.
A foreign key must match an existing primary key or else be null.
RELATIONAL DATABASE PROPERTIES:
A relational database
can be accessed and modified by executing Structured Query Language (SQL).
Contains a collection of tables with no physical pointer
Use a set of operators for partitioning and combining relations e.g
where clause, orderby clause.
Common Data types used in Databases:
DATATYPE SIZE TYPE OF DATA HOLD
CHAR 255characters Character
VARCHAR2 > 2000 characters Character
DATE DD-MON-YY Date and time
LONG 65,535 characters Binary Data.
NUMBER 9.99 * 10124 Integer, Float
Common Data types used in MS Access:
DATATYPE TYPE OF DATA HOLD
TEXT Character
MEMO Character
DATE/TIME Date & Time
NUMBER Integer, Long Integer , Decimal etc.
CURRENCY Dollar , Euro etc.
SQL (Structured Query Language):
ANSI SQL is divided into five different sections.
1) Data Retrieval:
Select.
2) Data Manipulation Language (DML):
Insert, Update, Delete , Merge
3) Data Definition Language (DDL):
Create, Alter, Drop, Rename, Truncate.
4) Data Control Language (DCL):
Grant, Revoke.
5) Transaction Control:
Commit, Rollback, Savepoint.
PL/SQL:
PL/SQL is an extension to SQL with design features of programming
language.
Data manipulation and query statements of SQL are included within
procedural units of code.
ORACLE 9I
Introduction to Oracle 9i SQL
Oracle9i Features:
Oracle offers a comprehensive high-performance infrastructure for e-business. It is called Oracle9i.
Oracle9i can support up to 512 Peta Bytes (1024 Tera Bytes) of data.
Oracle9i includes everything needed to develop, deploy, and manage Internet applications.
Benefits include:
Scalability from departments to enterprise e-business sites
Robust reliable, available, secure architecture
One development model, easy deployment options
Leverage an organization’s current skillset throughout the Oracle
platform (including SQL, PL/SQL, Java, and XML)
One management interface for all applications
Industry standard technologies, no proprietary lock-in
There are two products, Oracle9i Application Server and Oracle9iDatabase, that provide a complete and simple infrastructure for Internet applications.
RELATIONAL AND OBJECT RELATIONAL DATABASE MANAGEMENT SYSTEM:
Relational Model and object relational model
User - defined data types and objects
Fully compatable with relational database
Support of multimedia and large objects
High quality database server features
System Development Life Cycle
From concept to production, you can develop a database by using the
system development life cycle, which contains multiple stages of
development.
This top-down, systematic approach to database development
transforms business information requirements into an operational
database.
Strategy and Analysis:
Study and analyze the business requirements.
Interview users and managers to identify the information
requirements.
Incorporate the enterprise and application mission statements as well
as any future system specifications.
Build models of the system.
Transfer the business narrative into a graphical representation of
business information needs and rules. Confirm and refine the model
with the analysts and experts.
Design
the database based on the model developed in the strategy and
analysis phase.
Build and Document
Build the prototype system.
Write and execute the commands to create the tables and supporting
objects for the database.
Develop user documentation, Help text, and operations manuals to
support the use and operation of the system.
Transition
Refine the prototype.
Move an application into production with user acceptance testing,
conversion of existing data, and parallel operations. Make any
modifications required.
Production
Roll out the system to the users.
Operate the production system. Monitor its performance, and enhance
and refine the system.
