Database Design and
Programming
Tahaluf Training Center 2021
Tahaluf Training Centre 05 Jul 2021
Chapter 01
1 Overview of Database
2 Introduction of Oracle Database
3 Development Life Cycle SDLC
4 Overview of Database Design
5 Database Design Phases
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
Built-in data types
Oracle Database provides a collection of built in data types:
1. VARCHAR2(size [BYTE | CHAR]): Variable-length character string
having maximum length size characters or bytes.
Example:
CREATE TABLE Var2_Example (
VARCHAR2_byte VARCHAR2(1 BYTE),
VARCHAR2_char VARCHAR2(1 CHAR)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
2. NVARCHAR2(size): Variable-length Unicode character string
having maximum length size characters.
Example:
CREATE TABLE Var2_SizeExample (
VAR2 VARCHAR2(100)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
3. NUMBER [ (p [, s]) ]: Number having precision p and scale s. The
scale s can range from -84 to 127. The precision p can range from 1 to
38.
Example:
CREATE TABLE Number_Example (
NumberExample NUMBER(8,2)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
4. FLOAT [(p)]: A subtype of the NUMBER data type having precision p.
A FLOAT value is represented internally as NUMBER.
Example:
CREATE TABLE Float_Example (
FloatExample FLOAT(5)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
5. LONG: Character data of variable length up to 2 gigabytes, or 231 -1
bytes.
Example:
CREATE TABLE Long_Example (
LongExample LONG
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
6. DATE: Valid date range from January 1, 4712 BC, to December 31,
9999 AD.
Example:
CREATE TABLE Date_Example (
DateExample DATE
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
7. BINARY_FLOAT: 32-bit floating point number. This data type requires
4 bytes.
Example:
CREATE TABLE Binary_Example (
BinaryExample BINARY_FLOAT
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
8. BINARY_DOUBLE: 64-bit floating point number. This data type requires
8 bytes.
Example:
CREATE TABLE BinaryD_Example (
BinaryDExample BINARY_DOUBLE
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
9. TIMESTAMP [(fractional_seconds_precision)]: Year, month, and day
values of date, as well as hour, minute, and second values of time, where
fractional_seconds_precision is the number of digits in the fractional part
of the SECOND datetime field. Accepted values of
fractional_seconds_precision are 0 to 9.
Example:
CREATE TABLE TimeStamp_Example (
TimeStampExample TIMESTAMP
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
10. TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME
ZONE: All values of TIMESTAMP WITH TIME ZONE, with the following
exceptions:
Data is normalized to the database time zone when it is stored in the
database.
Example:
CREATE TABLE TimeStampWithLocalZone_Example (
TimeStampWithLocalZoneExample TIMESTAMP WITH LOCAL
TIME ZONE
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
11. RAW(size): Raw binary data of length size bytes.
Example:
CREATE TABLE RawExample (
RawExample RAW(10)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
12. LONG RAW: Raw binary data of variable length up to 2 gigabytes.
Example:
CREATE TABLE LRawExample (
LRawExample LONG RAW
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
13. ROWID: Base 64 string representing the unique address of a row in its
table.
Example:
CREATE TABLE RowIdExample (
Row_Id ROWID,
Name VARCHAR(20)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
14. UROWID [(size)]: Base 64 string representing the logical address of a
row of an index-organized table.
Example:
CREATE TABLE URowIdExample (
URow_Id ROWID,
Name VARCHAR(20)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
15. CHAR [(size [BYTE | CHAR])]: Fixed-length character data of length
size bytes or characters.
Example:
CREATE TABLE CharExample (
Name CHAR(3 CHAR)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
16. NCHAR[(size)]: Fixed-length character data of length size characters.
Example:
CREATE TABLE NCharExample (
Name NCHAR(3)
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
17. CLOB: A character large object containing single-byte or multibyte
characters.
Example:
CREATE TABLE ClobExample (
Name NCHAR(3),
Note CLOB
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
18. NCLOB: A character large object containing Unicode characters.
Example:
CREATE TABLE NClobExample (
Name NCHAR(3),
Note NCLOB
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
19. BLOB: A binary large object.
Example:
CREATE TABLE BlobExample (
Image BLOB
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
20. BFILE: Contains a locator to a large binary file stored outside the
database.
Example:
CREATE TABLE BfileExample (
Document BFILE
);
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
What is Schema Diagram?
Schema diagram contains attributes and entities.
It only display the database design and does not display the actual data of
the database.
Schema can be a single table or more than one table which is related.
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
Draw Schema Diagram
Example
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
Exercise
Draw Schema Diagram for the following scenario:
In a hospital System, there are many doctors and other employees work.
Doctors treat patients and provide them with care and medicines. There is
a room for every patient entering the hospital.
Tahaluf Training Centre 05 Jul 2021
Database Design Phases
What is Class Diagram?
A class diagram used in modeling and designing software to describe
classes and relationships.
Class diagrams used to model a software in a high level of abstraction.
Tahaluf Training Centre 05 Jul 2021
References
[1]. https://www.studytonight.com/dbms/overview-of-dbms.php
[2]. https://strolve.com/what-is-software-development-life-cycle-sdlc/
[3]. https://ecomputernotes.com/database-system/rdbms/phases-of-design-
methodology
[4]. https://www.oracle-dba-online.com/introduction_to_oracle.htm
[5]. https://www.oracletutorial.com/getting-started/what-is-oracle-database/
[6]. https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021
Tahaluf Training Centre 05 Jul 2021
