IToverview.com - Easy To Learn Tutorials!

IToverview.com

Tutorials


Introduction to Databases

The Database section will explain about different databases from Developer's view. For a beginner or a middle level developer, this tutorial will be very useful. With few databases (example: oracle), we have explained how to download, install, configure, create databases, users, and working on database objects.

RDBMS acronym stands for Relational Database Management Systems. Codd created rules for RDBMS and several companies have created databases based on Codd's rules. The popular RDBMS are Oracle, Sybase, Informix, SQL Server, Teradata, DB2, MYSQL etc. Some are open source databases and some are proprietary based databases. Each company has developed the software for different platforms like UNIX, LINUX, WINDOWS, MAC etc. Each database has different versions and editions. These databases are sold to companies (companies who use the database) and companies hire DBAs and Programmers for managing the databases.

Database

Database is all about Storing, retrieving and managing of data. Data is always stored in the form of tables. Senior Data Modelers or Data Architects design the logical data model. Data Modeler or Architects or DBAs convert this logical data model to a physical data model. Based on the needs, DBAs generate scripts from the physical data model, design and create the database. Developers write code (programs) for storing and retrieval of data. Note: DBAs also do developer work.

Database User

A person who uses the database. Different types of privileges (read, update) will be given to the user. For example, user in BA (Business Analysis), development, testing and production will have different privileges. Sometimes privileges are grouped and a new object (IN ORACLE, it is called as role) is created. These roles are granted to the users.

SQL (Structured Query Language) - It contains

  • Data Definition:
    • You will create objects (tables, column, procedure, function etc.)
    • You will alter the objects (adding/removing columns, adding/removing indexes) etc.
    • You will be dropping the objects.
  • Data Manipulation:
    • Insert: You will be inserting the data
    • Select: You will be retrieving the data
    • Delete: You will be deleting the data
    • Update: You will be updating the data
    • Commit: You will be permanently saving the data
    • Undo Changes: You will undo all the changes based on some scenarios.
  • Privileges Allocation: you will allocate privileges to different users.

Programs:

In SDLC (Software Development cycle), during development phase, developers will do programming by creating procedures, or functions or packages. The intention to create procedure, function, and package is to avoid repetitive work in many situations. Procedures or functions or packages will be called from the front end (dot net, java etc) or from the reporting (BO, Cognos etc.) or from the back end (database) also. Standalone programs are also written, which will not be repetitively used.

What databases contain:

  • Tables: Each table has one or more columns and data is stored in those columns. Datatypes are associated with columns.
  • DataType: Type (character, numeric, data) of the data to be stored. Examples: Character (Name), Numeric (Salary), Date (Birth Date) etc.
  • Record: When you read the data horizontally (from left to right), it is called as row or record.
  • Column: When you read the record from top to bottom, it is called as column.
  • NULL: No Values are stored in that column. NULL is not zero, since zero is also a value.
  • NOT NULL: When not null constraint is created on a column, it should have value.
  • Primary Key Constraint: When primary key constraint is created on a column, data in that column will be always unique and it will not allow null values. When primary key is imposed on several columns, it is called as composite primary key.
  • Unique Constraint: When unique constraint is created on a column, data in that column will be always unique and will allow null values.
  • Foreign Key Constraint: When a foreign key is created on a column, data in that column will allow null values and will always refer to values in ANOTHER primary key column. Another column may be from the same table or from a different table. When foreign key constraint is created on multiple columns, it is called as composite foreign key.
  • Primary/Foreign Key Relation: When data from a foreign key column reference a value in a primary key column it is called as primary foreign key relationship.
  • Self-Referential Integrity: When data is referred between columns (primary key and foreign key column of the same table), it is called as self-referential integrity.
  • ON Delete Cascade: When a record in a parent table is deleted, corresponding records that reference in the child table will be also deleted.
  • Sequence: Used for generating number automatically.
  • Index: Used for fastest retrieval of data. Index, Unique Index, Composite index are different types of indexes.
  • Functions & Joins: With the use of several built in functions (arithmetic functions, character functions, date functions, conversion function, mathematical functions etc), and JOINS (equal join, inner, outer join) it becomes easier to get the correct result.
  • Query/SubQueries: It is used for retrieving data from the database. In a select statement, queries, sub queries, correlated sub queries are different types of queries used to retrieve data. Select statements are can executed on the columns with filter (Where clause with a conditions) or without filter.
  • Object Examples: Tables, Columns, Procedure, Functions, Triggers, Sequences, Views, Synonyms are called as objects.
  • Object Metadata: A table has a name, column and datatype. An index has a name and the type of index also. All constraints have names also. Column, datatype are called as metadata.
  • System Tables: When you want to delete some indexes or constraints, you need the name of those indexes or constraints and you can that information from system tables. You can query metadata information of tables from system tables. We will explain it later.
  • Performance Tuning: You have parameters or objects to tune the data, which will retrieve data in a faster manner.
  • Instance: It is a collection of MEMORY and PROCESSES.
  • Backup: If the data is backed up, with commands or Backup Tools we can do recovery of the data. Cold Backup and Standard Backups are commonly used.


Steps to Download and Install Oracle Database 12c  Next