Transcript
1 |
Page
1.
Organization Overview
Organization
Techno Campus
is a leading organization in North India which offers different verticals
–
Software
Development, Network Consultancy, Training and Recruitment all under one roof. It is also engaged
with the IT industry in building the competency levels of the IT workforce across all levels on a wide
technology stack.
In 15th year of operations now and the oldest one in the region, is an
Authorized Training Partners
of
“
Microsoft, Sun Microsystems, Oracle, Red Hat & EC-
Council”
committed to provide us with
the quality training on the latest cutting edge technologies under the expert guidance of Experienced and
Certified Subject Matter Experts. Where Every Industrial Training Student Works on Real Time
Projects
under Project Managers in a Real Time Environment.
Location
Techno Campus, SCF - 8, New Jawahar Nagar Market, Backside Heat 7 Near APJ College, Jalandhar,
PB - 144001
Specialization Under
Oracle 10G Database Administrator Technology.
External Guide
Mr. Balraj singh (Oracle Cerified Professional) ,Techno Campus
Mr. Neeraj , Techno Campus
2 |
Page
Technology Learnt
Technology name
:
Oracle 10g database Administrator Technology
This course is first step towards success as an Oracle professional, designed to give a firm
foundation in basic database administration. In this I learnt how to install and maintain an
Oracle database. And I gain a conceptual understanding of the Oracle database architecture and
how its components work and interact with one another and also learn how to create an
operational database and properly manage the various structures in an effective and efficient
manner including performance monitoring, database security, user management, and
backup/recovery techniques. The lesson topics was reinforced with structured hands-on practices.
It helps to prepare me for the corresponding Oracle Certified Associate.it counts towards the Hands-on
course requirement for the Oracle Database 10g Administrator Certification.
This course covers creating indexes and constraints, and altering objects. Additionally, I learnt how to
create and query tables. In order to query and manipulate data within the database, and some advanced
features of SQL.
This course consists of “ Oracle 10g Database: SQL Fundamentals I “For this course,
the main development tool used is Oracle SQL. SQL*Plus is available as an optional development tool.
This is appropriate for a audience. There are minor Difference between 10g and 11g features in SQL.
Oracle Database is an
object-relational database management system
produced and marketed by
Oracle
Corporation
. “
Oracle Database 10
g”
is the first database designed for grid computing, the most
flexible and cost-effective way to manage enterprise information. It cut costs of management while
providing the highest possible quality of service.
Oracle 10g is a major re-write of the Oracle kernel
from Oracle 9i.
Learn to-
Prerequisites :
Working knowledge of SQL
Data processing
3 |
Page
Actual applications
This is data retrieving language that is used by most databases i.e. Oracle and MySql. There are quite a
number of reasons for the use of SQL. One of the most common reasons why most web developers’ use
this language is because it is easy to use and a standard query language that can be used for both
database and web pages. Because of the fact that SQL is can be used to manipulate data in all ways, it
can be used to retrieve, create, store or even update records.
Use
The new Oracle Database 10g is focused on simplifying the management of the storage used for holding
database information. The database information includes the database files, as well as other information
such as database configuration information, backup and log archives, etc.
, the system administrator, and
the storage administrator to plan physical storage layout to be used for housing the various pieces of
information making up the mission critical databases.
Objectives:
After complting ,we would be able to do following :
1.
Install Oracle Database 10g and configure a database
2.
Display data from multiple tables
3.
Create or remove administer user accounts
4.
Controling user through DCL.
5.
Manage the Oracle instance
6.
Manage the Database and maintain it.
7.
Create tables to store data.
8.
Utilize views to display data.
9.
Learn to use Data Defination Language(DDL) statement to create and manage tables
10.
Retrive rows and columns data from tables with Select statement
11.
Displaying data from multiple tables
12.
Write multiple-column sub-queries.
13.
Solving the queries
14.
Run data manipulation(DML) statements to update or delete data in Oracle Database 10g
Oracle 10g Modules:
SQL * Plus
PL/SQL
Query processing
4 |
Page
Topics under “
Oracle Database 10g: Administration Workshop I
”
Introduction to Oracle 10g (Database Architecture)
5 |
Page
Installing the Oracle Database Software
installation
6 |
Page
Â
7 |
Page
Control User Access
Privileges
Retrieve Data using the SQL SELECT Statement
are used in a WHERE clause
8 |
Page
Learn to Restrict and Sort Data
that are used in a WHERE clause
statement
9 |
Page
Aggregate Data Using the Group Functions
10 |
Page
Display Data From Multiple Tables Using Join
Rightmost Join
11 |
Page
Â
Leftmost Joins
12 |
Page
Use Sub-queries to Solve Queries
-queries can solve
-queries
-queries
single-row and multiple-row sub-queries
Data Manipulation Statements
13 |
Page
changes with the COMMIT and ROLLBACK statements
Use of DDL Statements to Create and Manage Tables
14 |
Page
Decipher how constraints
Retrieve Data Using Sub-queries
-Column Subqueries
Management of Schema Objects
15 |
Page
Creating sessions and Managing options
Using Spool for Creation of sessions
Setline
16 |
Page
Student Management Database
The student management system is an improved Student Management System. It can handle all
details about a student. The details include lecture, subject details, student personal details e.t.c.
In case of current system they need a lot of time, manpower e.t.c here almost all work is
computerized. So the accuracy is maintained. Student management system is managed by an
administrator. It is the job of the administrator to insert update and monitor the whole.
Problem Definition:
Today all the work, from the time of student admissions to student management is done manually
through ink and paper, which is very slow and consuming much efforts and time. Maintaining the
records in written documents is very informal and full of problems.
Student Management Database is an approach to Design a Computerized Automated Student Database,
where all the useful student records can be kept and viewed from time to time, we can also update the
database and can get the information of particular student and academics through queries. It is an
efficient way to speed up the work and easy way to use system.
Demand on the client and the network
Does not perform table locking automatically
Not fault tolerant in the case of client failure
Do not have file based transaction logging
17 |
Page
Purpose:
1).
Student Management supports the student admission , registration process, the maintenance of
student personal, academic and fee related data.
2). Database maintained by the
system usually contains the student’s personal,
academic and its fee
related information. It focuses on storing and processing (insertion, updates) by using following
DDL, DML, DCL and DQL queries.
3). Generates student information in various tables .
4). Generate Student’s Academic Detail Report.
5). Generate
Student’s Personal Detail Report.
6). Generate Student’s Fee Status Report.
7). It stores faculties details , their salaries and the subjects they taught.
8). It also Stores Students subjects, merit list provided by University
Scope:
It’s the back
-end which will work for institute conducting professional courses like MCA/MBA under a
governing university.
Student Management Database Manages student (personal, academic, fees) details. Students are
admitted through their merit and promoted to the higher class as per their marks secured in the
examinations.
Activities like Updating, Creations done in the system by the System Operator will be maintained in the
form of tables for auditing and maintaining the integrity of the system.
Project Requirements:
Automate manual paper work done at the time of student’s admission (fee
deposition) in the
institute.
s paper work.
.
Software Required:
Operating System: Microsoft Windows XP / Windows 7
Back End tools: Oracle10g, SQL Server
18 |
Page
Hardware Required:
CPU: Pentium VI processor
RAM: 512 MB
HDD: 40 GB
DEVICES: Keyboard, Monitor, Mouse, Printer
Implementation
My first goal is to create the tables for Student Database
The following tables would be created under Student Database:-
Student table
Subject table
Course table
Marks table
Lecturer table
Salary table
Subject_ join_ course table
Department table
Tables with specific Attribute and entities
1.
STUDENT TABLE
.
Student_ id - student id is a proper identification of students in the table students.
19 |
Page
It’s also the “primary key” for database table STUDENT
Student_ name - it is the name of the student
Student_ gender - it is the sex of the student (i.e. Male or Female)
Student_ age - it indicates the present age of the student
Student_ status - it indicates whether the student is a hostler or day scholar
Student_ phno - it indicates the student contact phone number.
Student_ year - it is the year from which student is registering in the campus
Student_ sem - it indicates the present semester for the student.
Student_ dept - it indicates the department for which the student registered in.
Class_ id -
it works as the “foreign key” for the class table.
1.1 Command for Creating Student Table:
1.2
Viewing student table :
20 |
Page
2.
SUBJECT TABLE
Subject_ id - It uniquely identifies the Course within the table through its specific id.
It works as the “Primary key” for the table Subject. And also works as “foreign
ke
y” for table lecturer
Subject_ name - it indicates the name given to a particular course.
2.1 Command for Creating Subject Table:
2.2 Viewing Subject table :
21 |
Page
3.
LECTURER TABLE
Lecturer_ id - an unique id for each
lecturer and also “primary key” for table LECTURER
Lecturer_ name - the name of each lecturer
Lecturer_ age - the age of every lecturer
Lecturer_ gender -
it’s the gender of each lecturer (i.e. male or female)
Lecturer_ email - i
t’s an email id of lecturers
Lecturer_degn -
it’s a designation given to each lecturers
Sub_ id -
it’s the id of subjects name which is taught by specific lecturer
And also the “foreign key” for LECTURER table which refer the SUBJECT
Lecturer _salary - the salary for each lecture
3
.1 Command for Creating Lecturer Table:
3.2 Viewing Lecturer Table
22 |
Page
4.
CLASS TABLE
Class_ id - it works as the primary key for the table CLASS and it is an unique id for
each class
Class_ name -
it’s the name for each class
4.1 Command for creating Class table
4
.2 Viewing Class Table
5.
DEPARTMENT TABLE
Dept_ id -
it’s an id for each department.
Dept_ name -
it’s the name for each dept.
5.1 Creating Department table
23 |
Page
6.
LECTURER _JOIN_CLASS(JOIN2) TABLE
Lecturer_id - id of the lecturer , it is a foreign key thatreference lecturer_id of table lecturer
Class id - id of class , it is foreign key that reference class_id of table class
6.1 Creating Table Join2
6.2
Viewing table join2
24 |
Page
7.
CLASS_ JOIN_ SUBJECT (JOIN1) TABLE
Class_ id - Id of the class, it is the foreign key that references class_id of class table
Subject_ id - id of subject, it is a foreign key that references subject_id of subject table
7.1 Creating table Join1
25 |
Page
7.2
Viewing table join2
8.
MARKS TABLE
Student_id - Specific Id of student
Student_name - Name of student
Sub1 Marks - marks of subject 1.
Sub2 Marks - marks of subject 2.
Sub3 Marks - marks of subject 3.
Grade - Grade (i.e A,B,C,D,E,F)
8.1 Creating Marks Table
8.2 Viewing Marks Table
26 |
Page
Reason for choosing this training
This training will help me for the corresponding Oracle Certified Associate exam. It counts towards the
hands-on course requirement for the Oracle Database 10g Administrator Certification.
The course is designed to give me a firm foundation in basic database administration. Expert
instructors helped me how to install, create and maintain an Oracle database.
It develop the key fundamental skills necessary to be an Oracle DBA. They design and create a server
using the Optimal Flexible Architecture (OFA), configure logical and physical structures, set up
database and user security, add and administer users, and monitor and tune main server areas. I learn
creates , Modification , Managing Databases by the end of this class.
27 |
Page
Learning Outcomes
I learn how to create an operational database and properly manage the various structures efficiently. The
training reinforced with structured, hands-on practices that solidify my understanding. Ensure fast,
reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and
deliver a higher quality of service by enabling smooth and rapid consolidation within a Datacenter.
learned to:
1.
Install the Database.
Installation of Oracle Database 10g Express Edition is extremely easy and can be done in less than
15 minutes.
Oracle Database 10g Express Edition can be installed on both Linux and Windows platforms
2.
Modification and Manage Database Objects
28 |
Page
Can easily modify tables using the Sql Alter statement. You may need to change the table structure
due to any of the following reasons:
omitted a column.
if column definition needs to be changed.
if there is a need to remove columns.
The ALTER TABLE statement is used to:
and
modify existing column
and Manage constraints
3.
Administer users and manage data.
a. Log in as SYSTEM
b. Select Administration>Database Users>Create User
c. Input user information, check the DBA role for user and click Create
4.
Join data between databases
a.
Join through Outer join
Left outer join
Right outer join
Full outer join
b.
Self join
c.
equijoin
29 |
Page
5.
Apply constraints and Build views
PRIMARY KEY
It ensures that no column that is part of the primary key can contain a null value. A null value is a
value that does not exist. For example, in the DEPENDENTS table, the column ID is the primary
key.
The FOREIGN KEY constraint designates a column or a combination of columns as a foreign
key. It establishes a relationship between a primary key or a unique key in the same table or
different table. A foreign key enforces that the value within the column matches the value in the
relationship column.
The UNIQUE constraint requires that no two rows of a table can have duplicate values in a
specified column or a set of columns. A table can have more than one unique key.
Composite key
If the UNIQUE constraint comprises more than one column, then the group of columns is called a
composite key
6.
user management
a.
creating users
b.
granting privileges
c.
revoking privileges
