Cursors in oracle

Implicit cursors

Explicit cursors

Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

Attribute Description
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example:
We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.
If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;
Working with an explicit cursor involves four steps:

Declaring the cursor for initializing in the memory

Opening the cursor for allocating memory

Fetching the cursor for retrieving data

Closing the cursor to release allocated memory

Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS
   SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;
Example:
Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota  
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Oracle Hints

select /*+ index(customer cust_primary_key_idx) */ * from customer;


/*+ ALL_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput .


/*+ CHOOSE */

Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement


/*+ FIRST_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). It will also force the optimizer to make use of index, if available. There are other versions of FIRST_ROWS hints. This hint is useful in an OLTP environment when the user cannot wait till the last row is fetched. This is mainly used in Java lookup screens. If there are some calculations then this hint should not be used.


Test your PL/SQL knowledge, Which code runs faster?
/*+ RULE */

Explicitly chooses rule-based optimization for a statement block


/*+ AND_EQUAL(table index) */

Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes


/*+ CLUSTER(table) */

Explicitly chooses a cluster scan to access the specified table


/*+ FULL(table) */

Explicitly chooses a full table scan for the specified table


/*+ HASH(table) */

Explicitly chooses a hash scan to access the specified table


/*+ HASH_AJ(table) */

Transforms a NOT IN sub query into a hash anti join to access the specified table


/*+ HASH_SJ (table) */

Transforms a NOT IN sub query into a hash anti-join to access the specified table


/*+ INDEX(table index) */

Explicitly chooses an index scan for the specified table


/*+ INDEX_ASC(table index) */

Explicitly chooses an ascending-range index scan for the specified table


/*+ INDEX_COMBINE(table index) */

If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.


/*+ INDEX_DESC(table index) */

Explicitly chooses a descending-range index scan for the specified table


/*+ ROWID(table) */

Explicitly chooses a table scan by ROWID for the specified table


/*+ USE_CONCAT */

Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the

UNION ALL set operator


/*+ ORDERED */

Causes Oracle to join tables in the order in which they appear in the FROM clause


/*+ STAR */

Forces the large table to be joined using a nested-loop join on the index


/*+ DRIVING_SITE (table) */

Forces query execution to be done at a different site from that selected by Oracle


/*+ USE_HASH (table) */

Causes Oracle to join each specified table with another row source with a hash join


/*+ USE_MERGE (table) */

Causes Oracle to join each specified table with another row source with a sort-merge join


/*+ USE_NL (table) */

Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table


/*+ APPEND */ , /*+ NOAPPEND */

Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.


/*+ NOPARALLEL(table) */

Disables parallel scanning of a table, even if the table was created with a PARALLEL clause


/*+ PARALLEL(table, instances) */

This allows you to specify the desired number of concurrent slave processes that can be used for the operation. DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION PARALLEL DML to enter this mode.)


/*+ PARALLEL_INDEX */

Allows you to parallelize fast full index scan for partitioned and non-partitioned indexes that have the PARALLEL attribute


/*+ NOPARALLEL_INDEX */

Overrides a PARALLEL attribute setting on an index


/*+ CACHE */

Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed


/*+ NOCACHE */

Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed


/*+ MERGE (table) */

Causes Oracle to evaluate complex views or sub queries before the surrounding query


/*+ NO_MERGE (table) */

Causes Oracle not to merge mergeable views

oracle pl sql developer roles and responsibilities

Created new procedures, functions, triggers, packages, and SQL*Loader scripts, and modified existing codes, tables, views, and SQL*Loader scripts
Incorporated business rules and constraints and created new tables for all modules.
Worked on claims processing reports on investigation and payment.
Generated claims report weekly, monthly and yearly.
Worked on reports to give parallel comparison of different-2 fiscal year of claims.
Fine tuned long running claims reports.
Developed expense reports to provide claims from accessing different-2 tables.
Wrote heavy stored procedures using dynamic SQL to populate data into temp tables from fact and dimensional tables for reporting purpose.
Tuned SQL queries to improve response time.
Wrote UNIX shell scripts to run SQL scripts daily.
Fine tuned SQL queries using hints for maximum efficiency and performance.
Developed Claims reports that would produce Excel outputs for users.
Developed a customer data warehouse to understand customer behavior to various Claims related programs and identify critical
customers in support of improved decision-making across the organization
Extensively worked in performance tuning of programs and processes.
Worked closely with Administrator for proper backup and recovery plans.
Monitored performance and changed performance requirements through application of Database tuning and performance optimization techniques.
Worked under tight schedules for successful development and implementation.

List available listeners on a server:

$ ps -ef | grep listener | grep -v grep
  oracle 23879    1  0   Feb 24 ?  33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit
  oracle 27939    1  0 05:44:02 ?  0:00  /8.1.7/bin/tnslsnr listener_db2 -inherit
  oracle 23536    1  0   Feb 12 ?  4:19  /8.1.7/bin/tnslsnr listener_db3 -inherit
  oracle 28891    1  0   Mar 03 ?  0:01  /8.1.7/bin/tnslsnr listener_db4 -inherit

Difference Between MyISAM And InnoDB - MySQL

MyISAM Advantage
1. Faster than InnoDB for search results
2. support Full-text indexing
3. When use "select" statement, gives faster results as compare to Innodb

InnoDB Advantage
1. Support for transactions (ACID: Atomicity, Consistency, Isolation, Durability).
2. Better "Row-level locking"
3. Foreign key constraints
4. Repair better incase of table corruption
5. Support Rollback abilities
6. Automatic crash recovery

MyISAM Disadvantage
1. Cascading delete/update not support
2. NOt support ACID compliance
3. When we use indexing, insert/update/delete become bit slow
4. Not Support Row Locking


InnoDB Disadvantage
1. Not support full text indexing (<5.6 mysql version)
2. Can't compressed
3. Search Slow as compare to MyISAM

Change table engine to MyISAM
ALTER TABLE `honnikery` ENGINE = MYISAM

Change table engine to innodb
ALTER TABLE `honnikery` ENGINE = innodb

MySQL Interview Question and Answers

1. What’s MySQL?
  MySQL the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers, that builds its business by providing services around the MySQL database management system.

2. Why use the MySQL Database Server?
  The MySQL Database Server is very fast, reliable, and easy to use. it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything.

3. What is the technical features of MySQL Server?
  The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

4. What are the column comparisons operators?
  The = , ‹›, ‹=, ‹, ›=, ›,‹‹,››, ‹=›, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements.

5. How do you get the number of rows affected by query?
  SELECT COUNT (user_id) FROM users;

6. What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
You can only use the comparison operators = and ‹=›.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.

7. How do you return the a hundred books starting from 25th?
  SELECT book_title FROM books LIMIT 25, 100;

8. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
  SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8).

9. What is the default port for MySQL Server?
  The default port is 3306.

10. How would you select all the users, whose phone number is null?
  SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

11. What are ENUMs used for in MySQL?
  You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ’January’, ’February’, ’March’,); INSERT months VALUES (’April’).

12. What are the advantages of Mysql comparing with oracle?
  MySql is Open source, which can be available any time. Provides Gui with Command Prompt. Supports the administration using MySQL Admin,MySQL Query Browser.Oracle is best database ever in Software development.

13. What is the difference between CHAR_LENGTH and LENGTH?
  The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

14. How are ENUMs and SETs represented internally?
  As unique integers representing the powers of two, due to storage optimizations.

15. How do you change a password for an existing user via mysqladmin?
  mysqladmin -u root -p password "newpassword"

16. If the value in the column is repeatable, how do you find out the unique values?
  SELECT DISTINCT user_firstname FROM users;

17. Explain the difference between FLOAT, DOUBLE and REAL?
  FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

18. How do you get the current version of mysql?
  SELECT VERSION();

19. Is Mysql query has LETTERCASE?
  No.
Ex :
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;

20. What is the LIKE?
  A LIKE pattern match, which succeeds only if the pattern matches the entire value.

21. Differentiate the LIKE and REGEXP operators?
  SELECT * FROM pet WHERE name REGEXP "^b";
  SELECT * FROM pet WHERE name LIKE "%b";

22. What are the String types are available for a column?
  The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

23. What is the REGEXP?
  A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested.

24. What is the difference between CHAR AND VARCHAR?
  The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare when you create the table.
The length can be any value between 1 and 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

25. How quoting and escaping work in SELECT QUERY?
  SELECT ‘hello’, ‘“hello”’,‘““hello””’, ‘hel‘‘lo’, ‘\‘hello’.

26. What is the difference between BLOB AND TEXT?
  A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

27. How we get Sum of column?
SELECT SUM(*) FROM [table name];

28. How do you get current user in mysql?
  SELECT USER();

29. How would you change a table to InnoDB?
  ALTER TABLE name_file ENGINE innodb;

30. How do you concatenate strings in MySQL?
  CONCAT (string1, string2, string3)

31. what is difference between primary key and candidate key?
  Primary Key
- are used to uniquely identify each row of the table. A table can have only one primary Key.
Candidate Key
- primary key is a candidate key. There is no difference. By common convention one candidate key is designated as a “primary” one and that key is used for any foreign key references.

32. How do you get the month from a timestamp?
  SELECT MONTH(january_timestamp) from tablename;

33. What do % and _ mean inside LIKE statement?
  % corresponds to 0 or more characters, _ is exactly one character.

34. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
  999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

35. How do you get the current date in Mysql?
  SELECT CURRENT_DATE();

36. What is the difference between mysql_fetch_array and mysql_fetch_object?
  mysql_fetch_array(): - returns a result row as a associated array, regular array from database.
mysql_fetch_object: - returns a result row as object from database.

37. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
  SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS();

38. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)?
  It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

39. How do you display the list of database in mysql?
  SHOW DATABASES;

40. How do you display the structure of the table?
  DESCRIBE table_name;

41. How do you find out which auto increment was assigned on the last insert?
  SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

42. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
  On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

43. How many drivers in Mysql?
There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are
PHP Driver
ODBC Driver
JDBC Driver
ado.net5.mxj
CAPI1PHP DRIVER
PERL DRIVER
PYTHON DRIVER
RUBY DRIVER
C WRAPPER

44. How do you run batch mode in mysql?
  mysql < batch-file >;
mysql < batch-file > mysql.out

45. What Storage Engines do you use in MySQL?
  Storage engines used to be called table types.
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employs different storage mechanisms, indexing facilities, locking levels and ultimately provides a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

46. Where MyISAM table is stored?
Each MyISAM table is stored on disk in three files.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension

47. Define Primary key?
  MYSQL allows only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself.A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.

MySQL Training MySQL Development and Administration

Work with any database
Use MySQL Workbench to work with a database and run SQL statements
Retrieve, add, update, and delete data from a database
Work with summary queries, subqueries, data types, and functions
Use MySQL Workbench to create an EER (enhanced entity-relationship) model for their database
Design and implement their own databases
Use MySQL to create stored procedures, functions, triggers, and events
Manage transactions and locking
Perform core administrative tasks, including monitoring, configuring, logging, and adding/removing users
Secure, back up, and restore a database
MySQL Training Outline

Introduction
An Introduction to Relational Databases and SQL
An Introduction to Client/Server Systems
An Introduction to the Relational Database Model
An Introduction to SQL and SQL-Based Systems
The SQL Statements
How to Use SQL From An Application Program
MySQL Workbench and Other Development Tools
An Introduction to MySQL Workbench
Using Workbench to Run SQL Statements
Using the Reference Manual
Command Line Client
How to Retrieve Data From A Single Table
An Introduction to the SELECT Statement
The SELECT Clause
The WHERE Clause
The ORDER BY Clause
The LIMIT Clause
How to Retrieve Data From Two Or More Tables
Inner Joins
Outer Joins
Other Skills For Working With Joins
How to Work With Unions
How to Insert, Update, and Delete Data
Creating Test Tables
Inserting New Rows
Updating Existing Rows
Deleting Existing Rows
Coding Summary Queries
Aggregate Functions
How to Group and Summarize Data
Coding Subqueries
An Introduction to Subqueries
How to Code Subqueries In the WHERE Clause
How to Code Subqueries In Other Clauses
Working With Complex Queries
The Data Types
Converting Data
Working With Data Types
How to Use Functions
String Data
Numeric Data
Date/Time Data
Other Functions You Should Know About
How to Design A Database
Design A Data Structure
Normalize A Data Structure
Using MySQL Workbench For Database Design
Creating Databases, Tables, and Indexes
Databases
Tables
Indexes
A Script That Creates A Database
How to Use MySQL Workbench
Character Sets and Collations
Storage Engines
How to Create Views
An Introduction to Views
How to Work With Views
Language Skills For Writing Stored Programs
An Introduction to Stored Programs
How to Write Procedural Code
Transactions and Locking
How to Work With Transactions
How to Work With Concurrency and Locking
How to Create Stored Procedures and Functions
Coding Stored Procedures
Coding Stored Functions
How to Use Workbench With Procedures and Functions
How to Create Triggers and Events
An Introduction to Database Administration
Database Administration Concepts
Monitoring the Server
Configuring the Server
Working With Logging
How to Secure a Database
An Introduction to User Accounts
Working With Users and Privileges
How to Use MySQL Workbench
How to Backup and Restore a Database
Strategies For Backing Up and Restoring a Database
How to Back Up a Database
Restoring a Database
Importing and Exporting Data
How to Check and Repair Tables
Conclusion

MySQL Training Topics.

Introduction
An Introduction to Relational Databases and SQL
An Introduction to Client/Server Systems
An Introduction to the Relational Database Model
An Introduction to SQL and SQL-Based Systems
The SQL Statements
How to Use SQL From An Application Program
MySQL Workbench and Other Development Tools
An Introduction to MySQL Workbench
Using Workbench to Run SQL Statements
Using the Reference Manual
Command Line Client
How to Retrieve Data From A Single Table
An Introduction to the SELECT Statement
The SELECT Clause
The WHERE Clause
The ORDER BY Clause
The LIMIT Clause
How to Retrieve Data From Two Or More Tables
Inner Joins
Outer Joins
Other Skills For Working With Joins
How to Work With Unions
How to Insert, Update, and Delete Data
Creating Test Tables
Inserting New Rows
Updating Existing Rows
Deleting Existing Rows
Coding Summary Queries
Aggregate Functions
How to Group and Summarize Data
Coding Subqueries
An Introduction to Subqueries
How to Code Subqueries In the WHERE Clause
How to Code Subqueries In Other Clauses
Working With Complex Queries
The Data Types
Converting Data
Working With Data Types
How to Use Functions
String Data
Numeric Data
Date/Time Data
Other Functions You Should Know About
How to Design A Database
Design A Data Structure
Normalize A Data Structure
Using MySQL Workbench For Database Design
Creating Databases, Tables, and Indexes
Databases
Tables
Indexes
A Script That Creates A Database
How to Use MySQL Workbench
Character Sets and Collations
Storage Engines
How to Create Views
An Introduction to Views
How to Work With Views
Language Skills For Writing Stored Programs
An Introduction to Stored Programs
How to Write Procedural Code
Transactions and Locking
How to Work With Transactions
How to Work With Concurrency and Locking
How to Create Stored Procedures and Functions
Coding Stored Procedures
Coding Stored Functions
How to Use Workbench With Procedures and Functions
How to Create Triggers and Events
Conclusion

Oracle 11g PL/SQL Training Topics.

Oracle 11g PL/SQL Training

1. Introduction to Oracle PL/SQL
About PL/SQL
The Environment
Block Structure
Block Types
Create an Anonymous Block
Execute an Anonymous Block



2. Declaring PL/SQL Variables
Handling Variables in PL/SQL – Declaration
Scalar Data Types
Declaring Scalar Variables



3. Writing Executable Statements
SQL Functions in PL/SQL
Using Sequences in PL/SQL Expressions
Data Type Conversion – Implicit
Data Type Conversion – Explicit
Nested Blocks
Variable Scope and Visibility
Operators in PL/SQL



4. Interacting with the Oracle Database Server
SQL Statements in PL/SQL
SELECT Statements in blocks
Retrieving Data - Example I
Retrieving Data - Example II
Inserting Data: Example
Updating Data: Example
Deleting Data: Example
SQL Cursor
SQL Cursor Attributes for Implicit Cursors



5. Writing Control Structures
Objectives
Controlling Flow of Execution
IF Statement
IF Statement – 1st Form
IF Statement – 2nd Form
IF Statement – 3rd Form
Iterative Control: LOOP Statements
Basic Loops
WHILE Loops
FOR Loops
FOR Loops: Example
CONTINUE Statement


6. Working with Composite Data Types
Composite Data Types
PL/SQL Records
%ROWTYPE Attribute
%ROWTYPE Attribute: Example
DMLs using %ROWTYPE – INSERT
DMLs using %ROWTYPE – UPDATE
INDEX BY Tables or Associative Arrays
Creating an INDEX BY Table


7. Using Explicit Cursors
Cursors
Controlling Explicit Cursors in block
Explicit cursor –
Cursor Attributes
Cursors and Records
Cursor Attributes
Cursor FOR Loops




8. Handling Exceptions
Example of an Exception Raise
Example of an Exception Handling
Handling Exceptions
PRAGMA EXCEPTION_INIT –
Functions for Trapping Exceptions
Functions for Trapping Exceptions
Trapping User-Defined Exceptions
RAISE_APPLICATION_ERROR Procedure



9. Creating Procedures
Subprograms
Subprogram Types
What Are Procedures?
Parameters and Parameter Modes
Comparing the Parameter Modes
Formal and Actual Parameters
IN Parameter Mode: Example
Executing the procedures with IN mode
Using the OUT Mode: Example
Executing the procedures with OUT mode
Using the DEFAULT Option for the Parameters
Calling Procedures
Removing Procedures: DROP command
Viewing Procedure in the Data Dictionary



10. Creating Functions
Stored Functions
Procedures vs Functions
Creating Functions: Overview
Invoking a Stored Function in SQL*Plus
Invoking a Stored Function in PL/SQL
Using a Function in a SQL Expression
Restrictions When Calling Functions
from SQL Expressions
Removing Functions: DROP command
Functions Using Data Dictionary Views



11. Creating Packages
PL/SQL Packages: Overview
Components of a Package
Package: Syntax
Packages: Example
Visibility of Package component
Calling the Package Subprograms
Creating Packages without Body: Example
Dropping Packages: DROP Statement
Viewing Packages Using the Data Dictionary



12. Creating Triggers
Triggers
Trigger Types
DML Trigger Timing and Event
Statement-Level Triggers vs. Row-Level Triggers
Example of a DML Statement Trigger
Example of DML statement trigger contd.
Creating a DML Row Trigger
Using OLD and NEW Qualifiers: Example
INSTEAD OF Triggers
Creating an INSTEAD OF Trigger: Example
Trigger management: Enable, Disable, Drop
Viewing Trigger Information

MySQL DBA Responsibilities

 DBA Responsibilities

Server startup/shutdown
Mastering the mysqladmin administrative client
Using the mysql interactive client
User account maintenance
Log file maintenance
Database backup/copying
Hardware tuning
Multiple server setups
Software updates and upgrades
File system security
Server security
Repair and maintenance
Crash recovery
Preventive maintenance
Understanding the mysqld server daemon
Performance analysis
Obtaining and Installing MySQL

Choosing what else to install (e.g. Apache, Perl +modules, PHP)
Which version of MySQL (stable, developer, source, binary)
Creating a user acccount for the mysql user and group
Download and unpack a distribution
Compile source code and install (or rpm)
Initialize the data directory and grant tables with mysql_install_db
Starting the server
Installing Perl DBI support
Installing PHP
Installing Apache
Obtaining and installing the samp_db sample database
The MySQL Data Directory

deciding/finding the Data Directory’s location
Structure of the Data Directory
How mysqld provides access to data
Running multiple servers on a single Data Directory
Database representation
Table representation (form, data and index files)
OS constraints on DB and table names
Data Directory structure and performance, resources, security
MySQL status files (.pid, .err, .log, etc)
Relocating Data Directory contents
Starting Up and Shutting Down the MySQL Server

Securing a new MySQL installlation
Running mysqld as an unprivileged user
Methods of starting the server
Invoking mysqld directly
Invoking safe_mysqld
Invoking mysql.server
Specifying startup options
Checking tables at startup
Shutting down the server
Regaining control of the server if you can’t connect
Managing MySQL User Accounts

Creating new users and granting privileges
Determining who can connect from where
Who should have what privileges?
Administrator privileges
Revoking privileges
Removing users
Maintaining MySQL Log Files

The general log
The update log
Rotating logs
Backing up logs
Backing Up, Copying, and Recovering MySQL Databases

Methods: mysqldump vs. direct copying
Backup policies
Scheduled cycles
Update logging
Consistent and comprehensible file-naming
Backing up the backup files
Off-site / off-system backups
Backing up an entire database with mysqldump
Compressed backup files
Backing up individual tables
Using mysqldump to transfer databases to another server
mysqldump options (flush-logs, lock-tables, quick, opt)
Direct copying methods
Database replication (live and off-line copying)
Recovering an entire database
Recovering grant tables
Recovering from mysqldump vs. tar/cpio files
Using update logs to replay post-backup queries
Editing update logs to avoid replaying erroneous queries
Recovering individual tables
Tuning the MySQL Server

Default parameters
The mysqladmin variables command
Setting variables (command line and options file)
Commonly used variables in performance tuning
back_log
delayed_queue_size
flush_time
key_buffer_size
max_allowed_packet
max_connections
table_cache
Erroneous use of record_buffer and sort_buffer
Running Multiple MySQL Servers

For test purposes
To overcome OS limits on per-process file descriptors
Separate servers for individual customers (e.g. ISPs)
Configuring and installing separate servers
Procedures for starting up multiple servers
Updating MySQL

Stable vs. development releases
Updates for both streams
Using the “Change Notes”
Bug fixing vs. new features
Dependencies on the MySQL C client library (PHP, Apache, Perl DBD::mysql)
MySQL Security

Assessing risks and threats
Internal security: data and directory access
Access to database files and log files
Securing both read and write access
Filesystem permissions
External security: network access
Structure and content of the MySQL Grant Tables
user, db, host, tables_priv, columns_priv
Grant table scope fields/columns
Grant table privilege columns
Database and table privileges: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE
Administrative privileges: FILE, GRANT, PROCESS, RELOAD, SHUTDOWN
Server control over client access: matching grant table entries to client connection requests and queries
Scope column values: Host, User, Password, Db, Table_name, Column_name
Query access verification
Scope column mmatching order
Grant table risks: the FILE and ALTER privileges
Setting up users without GRANT
The anonymous user and sort order
MySQL Database Maintenance and Repair

Checking and repairing tables
Invoking myisamchk and isamchk
Extended checks
Standard table repair
Table repair with missing/damaged index or table description
Avoid server-checking interaction, without shutdowns

Top Queries based on ADDM recommendations in oracle.

select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and  a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc)



User Last Login
*****************

select username, count(*) "SESSIONS", trunc(last_call_et/3600) "IDLE_HOURS", module
from v$session
group by username, trunc(last_call_et/3600), module
order by 4, 3, 1;
select osuser, program, count(program) "Active Count"
from v$session
where program not like '%$program%'
and (status = 'ACTIVE'
or last_call_et < 900)
group by osuser, program





Max Cursors
***********
select max(a.value) as highest_open_cur, p.value as max_open_cur
from
v$sesstat a, v$statname b, v$parameter p
where
a.statistic# = b.statistic#
and
b.name = 'opened cursors current'
and
p.name= 'open_cursors'
group
by p.value;

Step by Step RMAN Recovery Catalog Creation and Configuration in Windows Oracle

SQL> select name,log_mode from V$database;

NAME      LOG_MODE
--------- ------------
ORCL      ARCHIVELOG



SQL> select name from V$datafile;

NAME
--------------------------------------------------------------------------------

E:\APP\HONNIKERY\ORADATA\ORCL\SYSTEM01.DBF
E:\APP\HONNIKERY\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\HONNIKERY\ORADATA\ORCL\UNDOTBS01.DBF
E:\APP\HONNIKERY\ORADATA\ORCL\USERS01.DBF
E:\APP\HONNIKERY\ORADATA\ORCL\EXAMPLE01.DBF


SQL> create tablespace rman datafile 'E:\APP\HONNIKERY\ORADATA\ORCL\rman.dbf'
  2  size 1000m  reuse autoextend on ;

Tablespace created.


SQL> create user rman identified by rman default tablespace
  2  rman temporary tablespace temp;

User created.



SQL> grant connect,resource,recovery_catalog_owner to rman;

Grant succeeded.

SQL>

MySQL Interview Questions & Answers

1. What is MySQL?

MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)


2. What are the technical features of MySQL?

MySQL database software is a client or server system which includes

Multithreaded SQL server supporting various client programs and libraries
Different backend
Wide range of application programming interfaces and
Administrative tools.
3. Why MySQL is used?

MySQL database server is reliable, fast and very easy to use.  This software can be downloaded as freeware and can be downloaded from the internet.

4. What are Heap tables?

HEAP tables are present in memory and they are used for high speed storage on temporary

basis.

• BLOB or TEXT fields are not allowed

• Only comparison operators can be used =, <,>, = >,=<

• AUTO_INCREMENT is not supported by HEAP tables

• Indexes should be NOT NULL

5. What is the default port for MySQL Server?

The default port for MySQL server is 3306.

6.  What are the advantages of MySQL when compared with Oracle?

MySQL is open source software which is available at any time and has no cost involved.
MySQL is portable
GUI with command prompt.
Administration is supported using MySQL Query Browser
7. Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE:

• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.

• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

 8. Differentiate CHAR_LENGTH and LENGTH?

CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

9. How to represent ENUMs and SETs internally?

ENUMs and SETs are used to represent powers of two because of storage optimizations.

10. What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values and that can be used during table creation.


Create table size(name ENUM('Small', 'Medium','Large');
1
Create table size(name ENUM('Small', 'Medium','Large');


11. Define REGEXP?

REGEXP is a pattern match in which  matches pattern anywhere in the search value.

12. Difference between CHAR and VARCHAR?

Following are the differences between CHAR and VARCHAR:

CHAR and VARCHAR types differ in storage and retrieval
CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
 13. Give string types available for column?

The string types are:

SET
BLOB
ENUM
CHAR
TEXT
VARCHAR
14. How to get current MySQL version?


SELECT VERSION ();
1
SELECT VERSION ();
is used to get the current version of MySQL.

 15. What storage engines are used in MySQL?

Storage engines are called table types and data is stored in files using various techniques.

Technique involves:

Storage mechanism
Locking levels
Indexing
Capabilities and functions.
16. What are the drivers in MySQL?

Following are the drivers available in MySQL:

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxj
17. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

18. What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

19. How do you login to MySql using Unix shell?

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

20. What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

21. How do you control the max size of a HEAP table?

Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

22. What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption.

23. What are federated tables?

Federated tables which allow access to the tables located on other databases on other servers.

24. What, if a table has one column defined as TIMESTAMP?

Timestamp field gets the current timestamp whenever the row gets altered.

25. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

26. How can we find out which auto increment was assigned on Last insert?

LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.

27. How can you see all indexes defined for a table?

Indexes are defined for the table by:



SHOW INDEX FROM <tablename>;

28. What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

29. How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

30. What are the column comparisons operators?

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

 31. How can we get the number of rows affected by query?

Number of rows can be obtained by


SELECT COUNT (user_id) FROM users;
1
SELECT COUNT (user_id) FROM users;
32.  Is Mysql query is case sensitive?

No.


SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
1
2
3
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;


All these examples are same. It is not case sensitive.

33. What is the difference between the LIKE and REGEXP operators?

LIKE and REGEXP operators are used to express with ^ and %.


SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";


34. What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –

TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types

TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.

The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

35. What is the difference between mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object:

mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.

mysql_fetch_object –  Returns a result row as object from database.

36. How can we run batch mode in mysql?

Following commands are used to run in batch mode:


mysql ;
mysql mysql.out
1
2
mysql ;
mysql mysql.out


37. Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats:

The ‘.frm’ file stores the table definition
The data file has a ‘.MYD’ (MYData) extension
The index file has a ‘.MYI’ (MYIndex) extension
38. What are the different tables present in MySQL?

Total 5 types of tables are present:

MyISAM
Heap
Merge
INNO DB
ISAM
MyISAM is the default storage engine as of MySQL .

39. What is ISAM?

ISAM  is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

 40. What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

41. How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.


SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
42. How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

43. How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:


SELECT * FROM
LIMIT 0,50;
1
2
SELECT * FROM
LIMIT 0,50;


44. How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

45. What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds.

CURRENT_DATE() shows current year,month and date only.

46. What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement:

DATABASE
EVENT
FUNCTION
INDEX
PROCEDURE
TABLE
TRIGGER
USER
VIEW
47. How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE
48. What are the nonstandard string types?

Following are Non-Standard string types:

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
49. What are all the Common SQL Function?

CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) – Formats the number X to D significant digits.

CURRDATE(), CURRTIME() – Returns the current date or time.

NOW() – Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.

DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) – Determines the difference between two times.

FROMDAYS(INT) – Converts an integer number of days into a date value.

50. Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.

MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.

MySQL database naming conventions.

1. Overview

This document lists out the Database standards recommendations to be followed by developers for Database design in MYSQL Technologies.

            MySQL 5.5.27 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.27 is recommended for use on production systems.

2. Database design

·      Database configuration to include :

DBMS                         : Mysql 5.5

Data base Engine    : InnoDB

Collation                    : utf8_general_ci

·      If a data item has more than 2 data values to be made available for selection by end users, Create a Parameter table to store the different data values for the said data item.

·      Data width for data items with numeric data type for storage of Amount data should be (12,2)

·      Data width for data items with Text data type should be 50 or 100 instead of big & small.

·      All Tables need to include a field with constraint defined as Primary key with Auto increment setup. This field will be referred as Record ID with data type int and data width of 8 digits. Whenever there is a relationship between two Tables, this field should be made used as Foreign key.

·      HTML file to be made used to store error/warning  messages along with unique message ID.

·         Data type for each primary key column should be “ int”.

·         Use same variable type for similar fields across tables.

·         Use VARCHAR instead of CHAR. Because more space and performance effective

e.g., 

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte

·         For fixed length column as code use CHAR instead of VARCHAR

·         Specify data type and its size as per Functional Requirements.

·         All table should include the below 6 columns :

lld_rec_status	to identify active ( 1 ) & inactive ( 0 ) records
lld_createdby	To store login id of the user who has created the record.
lld_createddt	To store database server date on which the record was created.
lld_modifiedby	To store login id of the user who has modified the record.
lld_modifieddt	To store database server date on which the record was modified.
lld_ipaddress	To store ipaddress of the desktop which was made used by user.

3.  Naming Standards:

·      Database name should be same as Project name (for e.g., crm).

·      Schema name should be same as Database name (for e.g., crm)

·      All tables should be prefixed with Project Name and related module ( for e.g., roles to store roles details in crm Project ).

·      All master data table should suffix with table name _m.( for e.g., parameter_m)

·      Should not make use of spaces or any special characters in table name (Alpha number + Underscore are the only things allowed).

·      Column names should be prefixed by respective table name .

·      Column name should relate to the data being stored in the respective field .

·      Should not make use of spaces or any special characters to be used in column names (Alpha number + Underscore are the only things allowed).

·      Primary key column name should be “record_id” and prefixed with table name.( for e.g., ofo_rec_id).

·      Primary Key Field name to be prefixed with table name. (for e.g., ofo_rec_id ) with Primary key constraint.

·      Give names to all field level constraints.(for e.g., ofo_pk_rec_id, reg_fk_ofo_rec_id)

4. Normalization and Referential Integrity:

·      Use single column primary key in a table as much as possible.

·      Use unique constraints where ever required.

·      Use Referential integrity at Child Tables when ever required.

·      Avoid ON DELETE CASCADE

·      On-Line Transaction Processing (OLTP) should be at least 3NF.

(3 Normal Form: Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed)

·      Evaluate every one-to-many relationship as a potential many-to-many relationship

5. General

• We should include primary keys, foreign keys and simple indexes to improve performance.
• Only use Unicode data types in very special circumstances.
•  Identify the critical tables and ensure minimum number of required records are available at implementation.
• Event, trigger, procedure should be prefixed with “project name_event_”, “projectname_trigger_”, project name_“proc_” followed by respective name