CS 2214: Database Implementation and
Administration
Lecture 1: Introduction to Oracle Database
Introduction
● Oracle is a relational database product which is used to store data
permanently in secondary storage.
● Oracle is Object Relational Database Management System (ORDBMS).
● To operate Oracle database, the following language is used:
○ SQL (Only maintain the database).
○ PL/SQL (Procedural Language) - to control the server
○ Dynamic SQL
All organization stores some type of data
Terminologies
● Data
○ It is a collection of raw facts.
● Information
○ Meaningful or processed data.
● Data Store
○ A place where data and information are stored
○ Examples are:
■ Books & Papers
■ Flat Files
■ Database
Flat Files Vs Databases
● Flat files is a traditional mechanism which is used to store data and
information in secondary storage devices.
● Disadvantages:
○ Data retrieval
○ Data redundancy
○ Data Integrity
○ Dat Security
○ Data Indexing
Data Retrieval - Flat File
To retrieve (Fetch) data from flat files, an application program must be developed
in HLL whereas it is possible to retrieve data from databases using SQL language.
Every application program in flat file mechanism, maintain its own file separate
from other application.
Data Retrieval - Database
When DBMS software is installed, the
software automatically creates some
spaces, i.e user interface and it can be used
to interact with the database directly or
through the application program.
Data Redundancy - Flat Files
● In flat files sometimes multiple copies of the same data is stored in different
locations. This is also called duplicate or redundant data.
● In flat file mechanism, modifying this data in one location does not affect in
another location, and this may result in inconsistent of the data.
● Databases automatically maintain consistency of data through ACID
properties.
● Normalization is also used to reduce redundancy.
Data Integrity
Integrity means to maintain proper data. It is difficult to achieve this in flat files as
application programs are to be used.
While it is possible in databases with the use of constraints and triggers.
Data Security
● Data in flat files can not be secured as flat files does not provide security
mechanism.
● Databases provide role-based security. A user can access certain information
or data based on their roles and privileges.
Data Indexing
● To retrieve data fastly, indexing mechanism can be used in databases
whereas the mechanism is not provided with flat files.
● To overcome the above mentioned problems, a specialized software was
proposed to store and manage data efficiently. The software is called the
Database Management System (DBMS).
What is a Database?
● It is an organized collection of interrelated data used by an application
program within an organization.
● Once the data is stored in the database, with the help of DBMS, a number of
users can simultaneously access the data.
● Database have two types of structures:
○ Logical: Is not visible in Operating System and is having database objects such as tables,
views, indexes,
○ Physical:Handled by database administrators only, and is visible in operating system.
DBMS Architecture
● American National Standard Institute (ANSI) has established 3 level
architecture of DBMS.
●
● The objective of this architecture is to separate users view of the database
from the way it is physically stored.
●
● The 3 levels are:
○ Conceptual level
○ External level
○ Internal level
DBMS Architecture
● The 3 level architecture provides
data independence.
● Upper levels are unaffected by
changes in the lower level.
Source: BeginnersBook.com
Conceptual Level
● Provides logical structure of the database.
● Most visible part of the database.
● It does not describe how the data is physically stored within the database, but
it defines what type of data can be stored in database by specifying data type
and data type size. It also defines constraints to specify what can not be
stored in the database.
● It also defines relationship between tables, using referential integrity
constraint foreign key.
● Conceptual views are designed by either developers or DBA
External level
● This provides separate views to the group of users for providing security of
the database.
○ External level DB Admin create views from the tables and then assign those views to a
number of users.Users will thus be allowed to access part of the data from the conceptual
level.
Internal level
● This defines how the data is physically stored within the database.
● It is handled by the DBAs.
Data Models
● Data models define how data is represented at the conceptual level.
● Three data models have been used:
○ Hierarchical Data model
○ Network Data Model
○ Relational Data Model
Hierarchical Data Model
● Data is organized in a “TREE” like structure.
● This is also called as parent-child hierarchy.
● Data is represented in the format of records.
● It is implemented based on one-to-many relationship. It has more duplicate
data due to this restriction.
●
Hierarchical Data Model
● Whenever a table column does not have a duplicate data entry, it is called a
Primary.
● Master table has the main transaction, child table has repeated data.
● You can not delete from a master table a column that exists in the child table.
● For this on delete cascade rule is used.
● Two rules are mandatory:
○ First delete child table data, then possible to delete master table
○ To insert data in the child table, it must be available in the master table.
Network Data Model
● Introduced in 1970 by CODASYL(Conference on Data System Language).
● It is implemented based on MANY-TO-MANY relationships.
● Data is also represented in the format of records.
Relational Data Model
● In 1970, E.F codd introduced this model.
● Data is stored in 2 dimensional tables.
● Relational data model mainly consist of 3 components:
○ Collection of database objects
○ Set of operators
○ Integrity rules
● Examples of relational data model products are:
● Oracle, SQL Server, db2, mySQL,Sybase, Sqlite
ORACLE
● Today’s Task:
○ Visit https://en.wikipedia.org/wiki/Oracle_Database for a bit of History on Oracle versions.
○ Enroll yourself in LMS course http://elearning.suza.ac.tz/course/view.php?id=642 using
enrollment key suza2023.
○ Install Oracle 19c in your machine
■ The link for step by step installation: https://alekciss.com/install-oracle-database-19c-on-windows/
