Oracle Date Format

The Oracle database stores dates in an internal numeric format, representing the century, year, month, day,
hours, minutes, and seconds.
The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January
1, 4712 B.C. and A.D. December 31, 9999.
In the example in the slide, the HIRE_DATE for the employee Gietz is displayed in the default format DDMON-
RR. However, dates are not stored in the database in this format. All the components of the date and
time are stored. So, although a HIRE_DATE such as 07-JUN-94 is displayed as day, month, and year, there
is also time and century information associated with it. The complete data might be June 7th, 1994 5:10:43
p.m.
This data is stored internally as follows:
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
Centuries and the Year 2000
The Oracle Server is year 2000 compliant. When a record with a date column is inserted into a table, the
century information is picked up from the SYSDATE function. However, when the date column is
displayed on the screen, the century component is not displayed by default.
The DATE data type always stores year information as a four-digit number internally, two digits for the
century and two digits for the year. For example, the Oracle database stores the year as 1996 or 2001, and
not just as 96 or 01.


Date Functions
Date functions operate on Oracle dates. All date functions return a value of DATE data type except
MONTHS_BETWEEN, which returns a numeric value.
• MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2.
The result can be positive or negative. If date1 is later than date2, the result is positive; if date1
is earlier than date2, the result is negative. The noninteger part of the result represents a portion of
the month.
• ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an
integer and can be negative.
• NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char')
following date. The value of char may be a number representing a day or a character string.
• LAST_DAY(date): Finds the date of the last day of the month that contains date.
• ROUND(date[,'fmt']): Returns date rounded to the unit specified by the format model fmt.
If the format model fmt is omitted, date is rounded to the nearest day.
• TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit
specified by the format model fmt. If the format model fmt is omitted, date is truncated to the
nearest day.
This list is a subset of the available date functions. The format models are covered later in this lesson.
Examples of format models are month and year.

Creating and Managing Tables

Objectives
After completing this lesson, you should be able to
do the following:
• Describe the main database objects
• Create tables
• Describe the data types that can be used when
specifying column definition
• Alter table definitions
• Drop, rename, and truncate tables

Database Objects
Object Description
Table Basic unit of storage; composed of rows
and columns
View Logically represents subsets of data from
one or more tables
Sequence Numeric value generator
Index Improves the performance of some queries
Synonym Gives alternative names to objects

Database Objects
An Oracle database can contain multiple data structures. Each structure should be outlined in the database
design so that it can be created during the build stage of database development.
• Table: Stores data
• View: Subset of data from one or more tables
• Sequence: Numeric value generator
• Index: Improves the performance of some queries
• Synonym: Gives alternative names to objects
Oracle9i Table Structures
• Tables can be created at any time, even while users are using the database.
• You do not need to specify the size of any table. The size is ultimately defined by the amount of space
allocated to the database as a whole. It is important, however, to estimate how much space a table will use
over time.
• Table structure can be modified online.

Naming Rules
Table names and column names:
• Must begin with a letter
• Must be 1 to 30 characters long
• Must contain only A–Z, a–z, 0–9, _, $, and #
• Must not duplicate the name of another object
owned by the same user
• Must not be an Oracle Server reserved word

Naming Rules
Name database tables and columns according to the standard rules for naming any Oracle database object:
• Table names and column names must begin with a letter and be 1–30 characters long.
• Names must contain only the characters A–Z, a–z, 0–9, _ (underscore), $, and # (legal characters, but
their use is discouraged).
• Names must not duplicate the name of another object owned by the same Oracle Server user.
• Names must not be an Oracle Server reserved word.
Naming Guidelines
Use descriptive names for tables and other database objects.
Note: Names are case insensitive. For example, EMPLOYEES is treated as the same name as eMPloyees
or eMpLOYEES.

The CREATE TABLE Statement
• You must have:
– CREATE TABLE privilege
– A storage area
• You specify:
– Table name
– Column name, column data type, and column size
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);

Oracle Instance

Oracle Instance
An Oracle Instance consists of the System Global Area (SGA) memory structure and the
background processes used to manage a database. An instance is identified by using methods
specific to each operating system. The instance can open and use only one database at a time.

Oracle Server

Oracle Server
The database server is the key to information management. In general, a server must reliably
manage a large amount of data in a multiuser environment so that many users can
concurrently access the same data. All this must be accomplished while delivering high
performance. A database server must also prevent unauthorized access and provide efficient
solutions for failure recovery.

Tasks of a DBA

Tasks of a Database Administrator :
Database administrators are responsible for maintaining the Oracle server so that the servercan process user requests. An understanding of the Oracle architecture is necessary tomaintain it effectively. This course will be focusing on outlining the Oracle architecture,and other administrator tasks such as: planning and creating databases, managing databaseavailability, managing memory, physical and logical structures, and managing users andprivileges.Database administrator tasks covered in other courses:The following tasks are discussed in other courses:• Backup and recovery in Oracle9i Database Administration Fundamentals II• Network administration in Oracle9i Database Administration Fundamentals II• Database tuning in Oracle9i Database Performance Tuning

Oracle9i Enterprise Edition

What is the Oracle9i Enterprise Edition?
Oracle9i Enterprise Edition is an object-relational database that is scalable and easily
manageable. The administration of the basic enterprise edition is discussed in this course.
However, the following options provide additional functionality.
• Partitioning: Provides facilities for implementing large, scalable applications. Enables
control over tables and indexes at a lower level of granularity than is possible with the
basic enterprise edition
• Real Application Clusters: Improves the scalability and availability of a database by
allowing multiple copies of the Oracle software to access a single database
• Oracle Enterprise Manager Packs: Built on top of the Oracle Enterprise Manager.
Oracle Enterprise Manager Diagnostics, Tuning, and Change Management Packs are
add-ons that provide DBAs with a set of tools for advanced diagnostics, monitoring,
tuning, and change management of Oracle environments.
• Advanced Security: Provides client–server, server–server network security using
encryption and data integrity checking, and supports enhanced user authentication
services, using third-party security services.

Relational Database

Definition of a Relational Database
A relational database uses relations or two-dimensional tables to store information.
For example, you might want to store information about all the employees in your company. In a relational
database, you create several tables to store different pieces of information about your employees, such as an
employee table, a department table, and a salary table.

-------------
Relational Database Management System
Oracle provides a flexible RDBMS called Oracle9i. Using its features, you can store and manage data with
all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to
store and execute program units. Oracle9i also supports Java and XML. The Oracle server offers the
options of retrieving data based on optimization techniques. It includes security features that control how a
database is accessed and used. Other features include consistency and protection of data through locking
mechanisms.
The Oracle9i server is an object-relational database management system that provides an open,
comprehensive, and integrated approach to information management. An Oracle server consists of an
Oracle database and an Oracle server instance. Every time a database is started, a system global area (SGA)
is allocated, and Oracle background processes are started. The system global area is an area of memory
used for database information shared by the database users. The combination of the background processes
and memory buffers is called an Oracle instance.
-------------
Data Models
Models are a cornerstone of design. Engineers build a model of a car to work out any details before putting
it into production. In the same manner, system designers develop models to explore ideas and improve the
understanding of the database design.
Purpose of Models
Models help communicate the concepts in people’s minds. They can be used to do the following:
• Communicate
• Categorize
• Describe
• Specify
• Investigate
• Evolve
• Analyze
• Imitate
The objective is to produce a model that fits a multitude of these uses, can be understood by an end user,
and contains sufficient detail for a developer to build a database system.

--------------

ER Modeling
In an effective system, data is divided into discrete categories or entities. An entity relationship (ER) model
is an illustration of various entities in a business and the rel ationships between them. An ER model is
derived from business specifications or narratives and built during the analysis phase of the system
development life cycle. ER models separate the information required by a business from the activities
performed within a business. Although businesses can change thei r activities, the type of information tends
to remain constant. Therefore, the data structures also tend to be constant.
Benefits of ER Modeling
• Documents information for the organization in a clear, precise format
• Provides a clear picture of the scope of the information requirement
• Provides an easily understood pictorial map for the database design
• Offers an effective framework for integrating multiple applications
Key Components
• Entity: A thing of significance about which information needs to be known. Examples are
departments, employees, and orders.
• Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the
attributes would be the employee number, name, job title, hire date, department number, and so on.
Each of the attributes is either required or optional. This state is called optionality.
• Relationship: A named association between entities showing optionality and degree. Examples are
employees and departments, and orders and items.


Entities
To represent an entity in a model, use the following conventions:
• Soft box with any dimensions
• Singular, unique entity name
• Entity name in uppercase
• Optional synonym names in uppercase within parentheses: ( )
Attributes
To represent an attribute in a model, use the following conventions:
• Use singular names in lowercase.
• Tag mandatory attributes, or values that must be known, with an asterisk: *.
• Tag optional attributes, or values that may be known, with the letter o.
Relationships
Symbol Description
Dashed line Optional element indicating “may be”
Solid line Mandatory element indicating “must be”
Crow’s foot Degree element indicating “one or more”
Single line Degree element indicating “one and only one”

Relationships
Each direction of the relationship contains:
• A label, for example, taught by or assigned to
• An optionality, either must be or may be
• A degree, either one and only one or one or more
Note: The term cardinality is a synonym for the term degree.
Each source entity {may be must be} relationship name {one and only one one or more} destination
entity.
Note: The convention is to read clockwise.
Unique Identifiers
A unique identifier (UID) is any combination of attributes or relationships, or both, that serves to distinguish
occurrences of an entity. Each entity occurrence must be uniquely identifiable.
• Tag each attribute that is part of the UID with a number symbol: #
• Tag secondary UIDs with a number sign in parentheses: (#)
---------------

Relating Multiple Tables
Each table contains data that describes exactly one entity. For example, the EMPLOYEES table contains
information about employees. Categories of data are listed across the top of each table, and individual cases are
listed below. Using a table format, you can readily visualize, understand, and use information.
Because data about different entities is stored in different tables, you may need to combine two or more tables
to answer a particular question. For example, you may want to know the location of the department where an
employee works. In this scenario, you need information from the EMPLOYEES table (which contains data about
employees) and the DEPARTMENTS table (which contains information about departments). With an RDBMS
you can relate the data in one table to the data in another by using the foreign keys. A foreign key is a column
or a set of columns that refer to a primary key in the same table or another table.
You can use the ability to relate data in one table to data in another to organize information in separate,
manageable units. Employee data can be kept logically distinct from department data by storing it in a separate
table.
Guidelines for Primary Keys and Foreign Keys
• You can not use duplicate values 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 value must match an existing primary key value or unique key value, or else be null.
• A foreign key must reference either a primary key or unique key column.