DBMS Fundamentals

Database Management System

DBMS is Database Management System which is used to control and manipulate data in the forms of tables and relations, mostly used and trusted system by nearly all organisations across the globe.

Full pdf: Database Management System

Video Lecture on Database Management System:

for using and practicing the database SQL that is structured query language is the best solution used.

DATABASE:

A database is a collection of Data (Information). Examples of databases, which we use in our daily life, is an Attendance Register, Telephone Directory, Muster Rule.

Database Management System(DBMS): A database management system is a collection of programs written to manage a database. That is, it acts as a interface between user and database.

RDBMS:

A Database Management System based on Relational Data Model is known as Relational Database Management System (RDBMS).

Relational Data Model was developed by Dr. E.F. CODD. He developed the relational data model by taking the concept from Relational Algebra in June - 1970.

Relational Data Model has some 12 Rules which are named after Codd as Codd Rules. According to Codd a package can be called as RDBMS only if it satisfies the Codd Rules.

ORACLE

Oracle is an Object-Relational Database Management System. It is the leading RDBMS vendor worldwide. Nearly half of RDBMS worldwide market is owned by Oracle.

ORACLE DATABASE

Every Oracle Database Contains Logical and Physical Structures. Logical Structures are tablespaces, Schema objects, extents and segments. Physical Structures are Datafiles, Redo Log Files, Control File.

A database is divided into logical storage units called tablespaces, which group related logical structures together. Each Tablespace in turn consists of one are more datafiles.

Oracle database

In relational database system all the information is stored in form of tables. A table consists of rows and columns

Table described

All the tables and other objects in Oracle are stored in tablespace logically, but physically they are stored in the datafiles associated with the tablespace.

Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database's redo log. A redo log is made up of redo entries (also called redo records).

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, the changes can be obtained from the redo log so work is never lost.

Every Oracle database has a control file. A control file contains the database name and locations of all datafiles and redo log files.

Every Oracle database also has a Parameter File. Parameter file contains the name of the Database, Memory Settings and Location of Control file.

Capabilities of SQL SELECT Statements:

A SELECT statement retrieves information from the database. With a SELECT statement, you can use the following capabilities:

•Projection: Select the columns in a table that are returned by a query. Select as few or as many of the columns as required.

•Selection: Select the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.

•Joining: Bring together data that is stored in different tables by specifying the link between them. SQL joins are covered in more detail in the lesson titled “Displaying Data from Multiple Tables.”

Basic SELECT Statement:

In its simplest form, a SELECT statement must include the following:

A SELECT clause, which specifies the columns to be displayed A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause

In the syntax:

SELECT is a list of one or more columns

* selects all columns

DISTINCT suppresses duplicates

column|expression selects the named column or the expression

alias gives the selected columns different headings

FROM table specifies the table containing the columns

Note: Throughout this course, the words keyword, clause, and statement are used as follows:

A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.

A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, and so on is a clause.

A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement.

Selecting All Columns

You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example in the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department.

You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table:

SELECT department_id, department_name, manager_id, location_id FROM departments;

Note: In SQL Developer, you can enter your SQL statement in a SQL Worksheet and click the “Execute Statement” icon or press [F9] to execute the statement. The output displayed in the Results tabbed page appears as shown in the slide.

Selecting Specific Columns

You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas. The example in the slide displays all the department numbers and location numbers from the DEPARTMENTS table.

In the SELECT clause, specify the columns that you want in the order in which you want them to appear in the output. For example, to display location before department number (from left to right), you use the following statement:

SELECT location_id, department_id

FROM departments;

Writing SQL Statements:

By using the following simple rules and guidelines, you can construct valid statements that are both easy to read and edit:

SQL statements are not case-sensitive (unless indicated).

SQL statements can be entered on one or many lines.

Keywords cannot be split across lines or abbreviated.

Clauses are usually placed on separate lines for readability and ease of editing.

Indents should be used to make code more readable.

Keywords typically are entered in uppercase; all other words, such as table names and columns names are entered in lowercase.

Executing SQL Statements:

In SQL Developer, click the Run Script icon or press [F5] to run the command or commands in the SQL Worksheet. You can also click the Execute Statement icon or press [F9] to run a SQL statement in the SQL Worksheet. The Execute Statement icon executes the statement at the mouse pointer in the Enter SQL Statement box while the Run Script icon executes all the statements in the Enter SQL Statement box.

The Execute Statement icon displays the output of the query on the Results tabbed page while the Run Script icon emulates the SQL*Plus display and shows the output on the Script Output tabbed page.

In SQL*Plus, terminate the SQL statement with a semicolon, and then press [Enter] to run the command.

Column Heading Defaults:

In SQL Developer, column headings are displayed in uppercase and are left-aligned.

SELECT last_name, hire_date, salary

FROM employees;

A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.

Search This Blog

NotableGyan | Helpful Tech Information to Note !