Entity Relationship Model (ER Model)

Read this Oracle Tutorial Point article to know benefits and key components of Entity Relationship (ER) Model in oracle database. In an effective system data is divided into describe categories or entities. An Entity Relationship(ER) Model is an illustration of multiple entities in a business and the relationships between them. It is built during the analysis phase of the System Development Life Cycle (SDLC). Entity Relationship(ER) Model separates the information required & the business from the activities performed.

Entity Relationship(ER)-Model Benefits:

1. ER-Model documents information for the organization in a clear, precise format.
2. ER-Model provides a clear picture of the scope of the information requirement.
3. ER-Model provides an easily understandable pictorial map for the database design.
4. ER-Model offers an effective framework for integrating several applications.

Key Components in Entity Relationship(ER)-Model:

1. ENTITY:
   It's a thing of significance about which the information needs to be known.
2. ATTRIBUTES:
   It's something that describe or qualifies an entity. Each attribute might be mandatory or optional.

Relational Database Terminology:
Tuple or Row:
It represents all data required for a specific instance in an entity. Every row is an entity is uniquely identified by declaring it as UNIQUE or PRIMARY KEY. The order of the rows isn't significant, while retrieving the data.

Attribute or Column:
It represents one kind of data in a table. The column order isn't significant when storing the data.

Field:
It can be found at the intersection of a row and a column. A field will have only one value or may not have a value at all. The absence of value in Oracle is described as NULL.

Relating Multiple Tables:
Each and every table contains data that are describes exactly only one entity per table. Data of multiple entities is stored in multiple tables. RDBMS enables the data in one table is related to another table by using the foreign keys. Foreign Key is a column or a group of columns that refer to a Primary Key in the same table or another table.

Relational Database Properties:
It shouldn't specify the access route to the tables and shouldn't reveal the physical arrange. The database is accessed by using Structured Query Language(SQL). The language is a collection of set of operations.

Structured Query Language(SQL) Statements:
SQL(Oracle) complies with industry accepted standards. The multiple categories into which the SQL statements falls are,

1. Data Retrieval Statements
   
   • SELECT
2. Data Control Language Statements (DCL)
   
   • GRANT, REVOKE
3. Data Manipulation Language Statements (DML)
   
   • INSERT, UPDATE, DELETE, MERGE
4. Data Definition Language Statements (DDL)
   
   • CREATE, ALTER, DROP, RENAME, TRUNCATE
5. Transaction Control Language Statements (TCL)
   
   • COMMIT, ROLLBACK, SAVEPOINT

About the SQL Buffer:
All commands of SQL are typed at the SQL prompt. Only one(1) SQL statement is managed in the SQL Buffer. The current SQL statement replaces the previous SQL statement within the Buffer. The SQL statement will be divided into different lines within the SQL Buffer. Only one line that is, the current line can be active at a time in the SQL Buffer. At SQL prompt, editing is possible only in the current SQL Buffer line.

Each statement of SQL ought to be terminated using semicolon. One SQL statement can contains only one. To run the previous (old) or current SQL statement in the Buffer type ‘/’ at SQL prompt. To open the SQL Editor type ed at SQL prompt.

Create and Manage Tables:
Database Objects:
Oracle database can contain multiple data structures. The different Oracle Database objects are,

1. TABLE: Table is used to store data, Basic Unit.
2. VIEW: View is logically represents subsets of data from one or more(additional) tables.
3. SEQUENCE: Sequence is used to generate Primary Key values.
4. INDEX: It used to improve the performance of some queries.
5. SYNONYM: Synonym is used to give alternate names to objects.

Tables in Oracle:
Table can be created at any time, even when users are using the database. No need to specify size of the table. The structure of the table can be modified online.

Table Creation Rules to remember:

1. The user must have permission or CREATE TABLE command and storage area.
2. The table name should starts with a letter within the range of 1 – 30 characters long.
3. Names can contain: 0-9, A-Z, a-z,  _,$, #
4. Names can't be duplicated for another object in the same Oracle server.
5. Name can't be Oracle server reserved word.
6. Names aren't case sensitive

Create Table Statement:
Syntax:

SQL> CREATE TABLE <TABLE_NAME>
(
Column_Name1 <Data type> (Width),
Column_Name2 <Data type> (Width),
     --
     --
Column_Namen <Data type> (Width)
);