Database Software Applications

A database software application is a software which is developed to accomplish specific tasks. The important task of a database application is to accept and save data into a database and retrieve the same when required. Retrieving in whichever format and to a device of choice.
three layers of a database application and their deployment

To know how to develop a components based or 3 tier application, read Database programming using visual basic, c# (c sharp) sql server

A database software application can be developed as a single user desktop application, a multi user network version or a web application. A multi user application works on a LAN (local area network) and a web application works on a distant server and accessed by a browser. Web services in many ways blur the distinction between windows application and web application and this because windows applications can also access web for services.

Invariably, a database software application includes an RDBMS like Oracle, SQL Server or SYBASE. The RDBMS is the core part of a database software application and holds all data. RDBMS enable manipulation of information stored in a Database, using SQL Statements.

Whenever we discuss database software application, we need to mention RAD software tools which stands for Rapid Application Development tools. A RAD tool enables quick development of database software applications by providing drag and drop functionality to build database software. C#, VB, dBASE (in its present avatar) and Delphi can be thought of as good examples of RAD tools. The evolution of a database software application is interesting and is useful for developers. From being single user desktop applications, database software applications are now sophisticated software which run on a network or networks. It has become also necessary to partition applications depending on the responsibilities. This necessity has resulted in two tier, three tier, n-tier applications. The partitioning of the software into layers is the first step in designing a successful database software application. Any database software application has a minimum of three layers. One is the Business Layer, Data Layer and Presentation Layer.

Database refers to the records kept or maintained with an intention to use it at a later date. Ledgers, sales, payments, or any other non monetary records also can be called as a database. A phone book is also a database usually organized alphabetically by surname. In computing, a database is defined as a structured collection of records or data. Using query language, data can be retrieved which can be used to make decisions. DBMS is the computer program or software package used to administer, query a database and also import /export to and from different data formats.



Examples of DBMS: Relational databases : SQL Server or Oracle, Desktop databases : MS Access or Lotus access.

Why use a Database ?

-Storage
-Accessibility
-Organization
-Manipulation

The core concept of a database is that of a collection of records. For a given database, there is a structural description of the type of records of data held in that database. This description is also known as a schema. The schema describes the objects that are presented in the database, and the relationships among them. There are two types of databases widely deployed, relational and hierarchical.

We access SQL Server database by using front-end application or any application capable of communicating with the driver that can talk to the database.

Hierarchical Databases

In a hierarchical databases, data is organized into a tree-like structure with a root element at the top. The root element has one or more nodes, and the nodes themselves have nodes. Example is Windows Explorer file manager, which relies on a tree-like structure for representing your disk and files. The Windows Explorer is more like a hierarchical DBMS with multiple databases. These databases are very fast and not good for complex relationships such as Accounting system and Inventory system. IBM produces a hierarchical database system called the Information Management System (IMS). IMS has tools to set up and implement hierarchical database.

Relational Databases

The relational model was introduced by E. F. Codd in 1970 as a way to make database management systems. It is a mathematical model defined in terms of predicate logic and set theory.

Three key terms are used extensively in relational database models: relations, attributes, and Entities.

Entities

An entity is an object with a distinct set of properties that is easily identified. Entities are the building blocks of a database. Some Examples of entities are student, course and grade.

Attributes

An attribute is a property of an entity that differentiates it from other entities and provides information about the entity. For example, the attributes of the entity Student are Student Name, Student Id and Course Id.

Relationships

A relationship is a crucial part of the design of a database. It is used to establish a connection between a pair of logically related entities. It is an association between entities. Separate entities can have relationships with each other. For example, if students study various courses, the entities are student and course, while the relationship between them is studies.

The best way to identify the entities is to find the nouns that represent the information in the application. For example, in the FinAccounting system, we need to track Transactions for Accounts.

The basic data structure of the relational model is the table, where information about a particular entity is represented in columns and rows or tuples. We map the entities and relationships on to tables.

We can picture a table as a grid: each row corresponds to a different account, but all students have the same structure. Columns (also known as domains) define the structure of the table, and each column represents the attribute of the entity stored in the table. We can think of attributes as terms that describe the tables. All these columns use terms that describe the Student. Each student is stored in a different row. As we add or remove students from the table, the number of rows change, but the number of columns remain the same. The columns determine the information we store about each student. This representation of columns in a table is also referred to as the table's schema.

Keys

Keys are important in relational database design. These are used as record identifiers. Enforcing data integrity ensures that the data in the database is valid and correct. Keys are also critical in the creation of indexes, which facilitate fast retrieval of data from large tables. Any column can be a key, or multiple columns can be grouped together into a compound key. It is not necessary to define all the keys in advance; a column can be used as a key even if it was not originally intended to be one. Keys play an important role in maintaining data integrity. The various types of keys that have been identified are :

Candidate key
Primary key
Alternate key
Composite key
Foreign key


Candidate key

It is important to have an attribute in a table that uniquely identifies a row. An attribute or set of attributes that uniquely identifies a row is called a Candidate key. This attribute has values that are unique. A candidate key can also be referred to as a Surrogate key.

Primary key

The candidate key that you choose to identify each row uniquely is called the primary key.

Alternate key

A Candidate key that is not chosen as a primary key is an Alternate key. It is important that you should understand that a primary key is the only sure way to identify the rows of a table. Hence, an alternate key may have the value NULL. A NULL value is not to be permitted in a Primary key since it would be difficult to uniquely identify rows containing NULL values.

Composite key

In certain tables, a single attribute cannot be used to identify rows uniquely and a combination of two or more attributes is used as a primary key. Such keys are called Composite keys.

Foreign key

When a Primary key of one table appears as an attribute in another table, it is called the Foreign key in the second table. A Foreign key is used to relate two tables.

We can use the following conventions to represent the leys in the table structure

Primary Key - PK
Alternate key- AK
Foreign key -FK

Relational operations

Users using programs retrieve data from a database by sending a query that is written in SQL. With RAD tools like VB.Net and C#, SQL is now embedded into the tools which provide the User Interface.

The success of relational databases is that it allows programmers to write queries that were not anticipated earlier. Due to this feature, relational databases are used by multiple applications in ways the original designers did not foresee.

In response to a query, the database returns a result set, which is a list of rows. The simplest query is to return all the rows from a table. In the day-to-day programming practice rows are filtered to return just the answer wanted.

There are many variations to returning data. The common variation is the Join. When we combine data from multiple tables we call it a join.

There are many more relational operations in addition to join. These include project, restrict, union a way of combining two tables with similar structures, difference, which lists the rows in one table that are not found in the other, intersect which lists the rows found in both tables, and product mentioned above, which combines each row of one table with each row of the other.

Depending on which other sources you consult, there are a number of other operators - many of which can be defined in terms of those listed above. These include semi-join, outer operators such as outer join and outer union, and various forms of division.