GST Billing Software

header image

INTRODUCTION to SQL

Erachana Line

1.1 Database definition

Data is nothing but information; a collection of data is called as database. It's an organised collection of data in the form of schemas, tables, queries, reports, views and other objects.

As multi-media capabilities are becoming common to computers of different sizes, the databases are also going multi-media. Modern DBMS permits storage and retrieval of data types such as business graphs, spreadsheets, art clippings (clip arts), pictures, sound, video, etc. These new data types are termed as 'objects'.

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

1.2 Database Types

The different database types are:

Centralised database

Users from different locations can access this database from a remote location at the central database, that store entire information and application programs at a central computing facility for processing. The application programs pick up the appropriate data from the database based on the transactions sent by the communications controller for processing the transaction.

Data validation and verification is carried out by the application programs at the central computer centre, and a registration number is allotted by the application programs located at the central facility. The local area office keeps on recording it and hardly does any processing.

Operational database

This is more of a basic form of data that contain information relating to the operations of an enterprise. Generally, such databases are organised on functional lines such as marketing, production, employees, etc.

End user database

End user is the user of a software, application or a product. This is a shared database which is shared by users and is meant for use by the end users, just like managers at different levels. They may not be concerned about the individual transactions as found in operational databases. This database is more about the summary of the information.

Commercial database

This is a database that contains information which external users may require. However, they will not be able to afford maintaining such huge database by themselves. It's a paid service to the user as the databases are subject specific. The access to commercial database can be given through commercial links.

Some of the database service providers also offer databases on CD-ROMs and the updated versions of the databases are made available periodically. The databases on CD-ROMs have the advantage of reduced cost of communication.

Personal database

The personal databases are maintained, generally, on personal computers. They contain information that is meant for use only among a limited number of users, generally working in the same department.

Distributed database

These databases have contributions from the common databases as well as the data captured from the local operations. The data remains distributed at various sites in the organisation. As the sites are linked to each other with the help of communication links, the entire collection of data at all the sites constitutes the logical database of the organisation.

RDBMS

A relational database management system (RDBMS) is a collection of programs and capabilities that enable IT teams and others to create, update, administer and otherwise interact with a relational database . Most commercial RDBMSes use Structured Query Language to access the database, although SQL was invented after the initial development of the relational model and is not necessary for its use.

1.3 Tables, Rows and Columns

A table is a collection of related data held in a structured format within a database . It consists of columns , and rows .

In the context of a relational database , a row—also called a tuple —represents a single, implicitly structured data item in a table . In simple terms, a database table can be thought of as consisting of rows and columns . Each row in a table represents a set of related data, and every row in the table has the same structure.

In a relational database , a column is a set of data values of a particular simple type, one value for each row of the database. A column may contain text values, numbers, or even pointers to files in the operating system. [2] Some relational database systems allow columns to contain more complex data types; whole documents, images or even video clips are examples. [3] A column can also be called an attribute. Each row would provide a data value for each column and would then be understood as a single structured data value.

Table

Column 1

Column 2

Row 1

Row 1, Column 1

Row 1, Column 2

Row 2

Row 2, Column 1

Row 2, Column 2

Row 3

Row 3, Column 1

Row 3, Column 2


1.4 Naming Conventions

• The main goal of adopting a naming convention for database objects is so that you and others can easily identify the type and purpose of all objects contained in the database.

• Use only letters or underscores (try to avoid numbers - and limit the use of underscores to meet standards for Constraints).

• Use a letter as the first character of the name. (don't start names with underscores or numbers).

• Limit the use of abbreviations. (can lead to misinterpretation of names).

• Limit the use of acronyms (some acronyms have more than one meaning e.g. "ASP")

• Make the name readable (they shouldn't sound funny when read aloud).

• Avoid using spaces in names even if the system allows it.

• Table names should be singular, for example, "Customer" instead of "Customers".

• In some cases, your tables might be sharing a schema/database with other tables that are not related in any way. In this case, it is sometimes a good idea to prefix your table names with some characters that group your tables together.

• For table names, underscores should not be used.