Advantages of Database Management Systems

DBMSs have the following advantages: Data Independece, Efficiency, Data Integrity, Centralized Administration and Reduced Application Development Time

PostgreSQL integer and floating point division

PostgreSQL integer and floating point division

SQL Temporary Tables

How to use SQL Temporary Tables

SQLite division: integer and floating point division

SQLite integer and floating point division

How to Backup a PostgreSQL Database

PostgreSQL provides the utility program pg_dump to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. pg_dump dbname > outfile pg_dump can be used from the Linux console. For example, if you want to backup a database called ‘my_database’ you run, from the console: pg_dump my_database > my_database.sql

How to Describe and Store Data in a Database

A data model is a collection of descriptions of the data and it’s relationships that hides details of the storage implementation. A database management system such as PostgreSQL or MS SQL allows users to define data models. The relational data model is a particular type of data model. A more abstract data model is the semantic data model. It is usually used to develop an initial description of the data in a real world scenario, like a business or any other real world organization.

The Relational Data Model

In the relational data model, a relation (table) can be thought as a set of records, like a table that contains information about employees in a company. A record (row or tuple) represents a simgle item in a relation. Every row in the same relation has the same structure. For example, if, in the employees table, we can have a row that describes the employe named ‘Mike Smith’. A field (column) represents a characteristic or variable of an entity described.

PostgreSQL Character Types: CHAR, VARCHAR and TEXT

There are three PostgreSQL character types: char: fixed length. varchar: variable length with optional limit. text: variable length without limit. char and varchar need an argument n to specify the characters length or limit. char(n) The char(n) type stores fixed length strings. If the string you are adding is shorter than n, spaces are added to make up the difference. For example, if ‘n’ is 7 and the string is house, which has 5 characters, the following value will be added: house .

Correation in SQL

The correlation is a statistical measure that shows how closely related are two sets of values. The most used correlation measure used is the Pearson’s Correlation Coefficient. The Pearson’s Correlation Coefficient gives: a value of 1 for a perfect correation. a value of 0 for no correlation. a value of -1 for a perfect negative correlation Examples of Positive and Negative Correlation: Positive Correlation: marketing expenditure and sales, price of a competitor product and sales.