Railways Computers Science Database Concepts Database Concepts

 

Database Concepts

 

A database is a collection of logically related information in an organised way so that it can be easily accessed, managed and updated. Some other operations can also be performed on database such as adding, updating and deleting data. A database could be simple as a single text file with a list of names or it could be complex as a large bunch of text files including some data.

 

Fundamentals of Database

For defining database, two terms, which are used frequently with database, should be known.

1. Data These are raw and unorganised facts that need to be processed such as digital representation of text, numbers, graphical images or sound, e.g. a student's test score is one piece of data.
2. Information When data is processed, organised, structured or presented in a given context to make it useful or meaningful, it is called information, e.g. the class's average score is the information that can be concluded from the given data.

 

Types of Database

Databases are of three types, namely as follows

1. Network Database In this type of database, data is represented as collection of records and relationships among data are represented as links.
2. Hierarchical Database In this type of database, data is organised in the form of tree with nodes. Nodes are connected via links.
3. Relational Database This database is also known as structured database in which data is stored in the form of tables. Where, columns define the type of data stored in the table and rows define the information about the data.

 

Components of a Database

A database consists of several different components. Each component listed, is called an object. Within the file, you can divide your data into separate storage containers called tables view, add and update table data by using online forms; find and retrieve the data that you want by using queries and analyse or print data in a specific layout by using reports.

Database components are described below

1. Tables These are the building blocks or relation of any relational database model where all the actual data is defined and entered. Different types of operation are done on the tables such as storing, filtering, retrieving and editing of data. Tables consist of cells at the intersection of records (rows) and fields (columns), -which are describe below

(i) Field It is an area (within the record). reserved for a specific piece of data. e.g. customer number, customer name, street address, city, state, phone number, current address, etc. Field of a table is also known as column.

(ii) Record It is the collection of data items of all the fields pertaining to one entity, i.e. a person, company, transition, etc. Record of a table is also known as row or a tuple and the number of records in a relation is called the cardinality of that relation.

2. Queries These are basically questions based on the data available in a database. A query consists of specifications indicating which fields, records, and summaries a user wants to fetch from a database. Queries allow you to extract data based on the criteria that you define.
3. Forms Although you can enter and modify data in datasheet view of tables but you neither control the user's action very well nor you can do much to facilitate the data-entry process. To overcome this problem, forms are introduced. Like tables, forms can be used to view and edit your data. However, forms are typically used to view the data in an underlying table one record at a time. e.g. a user can create a data entry form that looks exactly like a paper form. People generally prefer to enter data into a well-designed form, rather than a table,
4. Reports When you want to print those records which are fetched from your database, design a report. Access even has a wizard to help produce mailing labels.

 

Database Management System (DBMS)

A DBMS is a collection of interrelated data and a set of programs to retrieve data from a database. It is an organised collection of data viewed as a whole, instead of a group of separate unrelated files. The primary goal of DBMS is to provide an environment that is both convenient and efficient for user to store and retrieve database information, e.g. MySQL, Oracle, FoxPro, dBASE, SyBase MS-Access. The purpose of database management system is to bridge the gap between information and data.

The basic processes that are supported by DBMS are as follows

1. Specification of data types, structures and constraints to be considered in an application.
2. Storing the data.
3. Manipulation of the database.
4. Querying the database to retrieve desired information.
5. Updating the content of the database.

 

Architecture of DBMS

The architecture of DBMS is divided into three levels are as follows

(i) Internal Level It is the lowest level of data abstraction that deals with the physical representation of the database on the computer. It is also known as physical level. It defines how the data. are actually stored and organised on the storage medium.

 

(ii) Conceptual Level It is the overall view of the database and includes all the information that is going to be represented in the database.

It describes what type of data is stored in the database, the relationship among the data without effecting to the physical level. It is also known as logical level.

(iii) External Level This is the highest level of data abstraction which describes the interaction between the user and the system.

It permits the users to access data in a way that is customised according to their needs, so that the same data can be seen by different users in different ways, at the same time. It is also known as view level.

 

Advantages of DBMS

There are following advantages of DBMS

(i) Reduction in Data Redundancy The duplication of data refers to data redundancy. DBMS cannot make separate copies of the same data. All the data is kept at a place and different applications refer to data from centrally controlled system.

(ii) Better Interaction with Users In DBMS, the availability of upto-date information improves the data to be access or respond as per user requests.

(iii) Improvement in Data Security DBMS can allow the means of access to the database through the authorised channels. To ensure security, DBMS provides security tools. i.e. username and. password.

(iv) Maintenance of Data Integrity Data integrity ensures that the data of database is accurate. In DBMS; data is centralised and used by many users at a time, it is essential to enforce integrity controls.

(v) Ease of Application Development The application programmer needs to develop the application programs according to the user's need. The other issues like concurrent access, security, data integrity, etc, are handled by database itself. This makes the application development an easier task.

(vi) Backup and Recovery The DBMS provides backup and recovery subsystem that is responsible to recover data from hardware and software failures.

 

 

Disadvantages of DBMS

As there are many advantages, DBMS also have some minor disadvantages.

These disadvantages are listed here

(i) Cost of Hardware and Software A processor with high speed of data processing and memory of large size is required to run the DBMS software. It means that you have to upgrade the hardware used for file based system. Similarly database software is also very costly.

(ii) Complexity The provision of the functionality that is expected from a good DBMS makes the DBMS an extremely complex piece of software. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organisation.

(iii) Cost of Staff Training Mostly DBMS are often complex systems so the training for user to use the database is required. The organisation has to pay a lot of amount for the training of staff to run the DBMS.

(iv) Appointing Technical Staff The trained technical persons such as database administrator, application programmers, etc are required to handle the database. You have to pay a lot of amount to these persons. Therefore, the system cost increases.

(v) Database Failure In most of the organisations, all data is integrated into a single database. If database is corrupted due to power failure or it is corrupted on the storage media, then our valuable data may be lost or whole system stops.

 

Applications of DBMS

Some applications of DBMS are as follows

(i) Banking For customer information, accounts, loans and other banking transactions.

(ii) Reservation For reservation and schedule information.

(iii) Universities For student information, course registration, grades, etc.

(iv) Credit Card Transaction For purchase of credit cards and generation of monthly statements.

(v) Telecommunication For keeping records of calls made, generating monthly bill, etc.

(vi) Finance For storing information about holdings, sales and purchase of financial statements.

(vii) Sales For customer, product and purchase information.

 

Relational Database

In a relational database, data is stored in different tables with relationships to each other. These tables communicate and share information, which facilitates data search ability, organisation and reporting. In the case of relational database, a Relational Database Management System (RDBMS) performs these tasks. An important feature of this database system is that a single database can be spread across several tables.

e.g. Base, Oracle, DB2, SAP SyBase, Informix, etc.

 

Terms Related to Database

Various terms related to relational database are as follows

(i) Relation It is a table with columns and rows which represent is the data items and relationships among them. Relation have three important properties a name, cardinality and a degree.

These properties help us to further define and describe relations

(a) Name The first property of a relation is its name, which is represented by the tide or the entity identifier.

(b) Cardinality The second property of a relation is its cardinality, which refers to the number of tuples (rows)     in a relation.

(c) Degree The third property of a relation is its degree, which refers to the number of attributes (columns) in each tuple.

(ii) Domain It is a collection of all possible values from which the values for a given column or an attribute is drawn. A domain is said to be atomic if elements are considered to be indivisible units.

(iii) Attributes The heading columns of a table are known as attributes. Each attribute of a table has a distinct name.

(iv) Tuples The rows in a relation are also known as tuples. Each row or tuple has a set of permitted values for each attribute.

 

Keys

Key is one of the important concepts of database. A key is defined as the column or set of columns in a table that is used to identify either row of data in a table or establish relationship with another table.

It is also referred as super key, arranging the records either in ascending or descending order.

If a table has id, name and address as the column names then each one is known as the key for that table. The keys are also used to uniquely identify each record in the database table.

 

Types of Keys

There are mainly four types of keys which are described below

(i) Primary Key It is a set of one or more attributes that can uniquely identify tuples within the relation. It identifies unique records within a table. The primary key should be chosen in such a way, i.e. its value must not be changed. There should not be duplicacy in the record of primary key. Primary key can be atomic or composite. The field chosen as primary key, cannot accept null value.

(ii) Candidate Key The set of all attributes which can uniquely identify each tuple of a relation, are known as candidate keys. Each table may have one or more candidate keys and one of them will become the primary key. The candidate key of a relation is always a minimal key.

(iii) Alternate Key From the set of candidate keys after selecting one of the keys as primary key, all other remaining keys are known as alternate keys.

(iv) Foreign Key It is a non-key attribute whose value is derived from the primary key of the same or some another table. The relationship between two tables is established with the help of foreign key.

A table may have multiple foreign keys and each foreign key can have a different referenced table. Foreign keys play an essential role in database design, when tables are broken apart then foreign keys make it possible for them to be

reconstructed.

 

Tit-Bits

• EF Codd represented 12 rules for Relational Database Management System (RDBMS) in 1970.
• Schema It is a logical structure of the database.
• Instances These are the actual data contained in the database at a particular point of time.
• Data Mining It combines efficient implementation techniques that enable them to be used in extremely large database.

 

Database Languages

There are various types of database languages

(i) Data Definition Language (DDL) DDL is used to define structure of your tables and other objects in database. In DBMS, it is used to specify- a. database schema as a set of definitions.

(ii) Data Manipulation Language (DML) DML provides various commands used to access and manipulate data in existing database. This manipulation involve inserting data into data base tables, retrieving existing data, deleting data from existing tables and modifying existing data.

(iii) Data Control Language (DCL) DCL commands are used to assign security levels in database which involves multiple user setups. They are used to grant defined role and access privilages to the users.

 

Entity- Relationship Model (E-R Model)

It represents the entities contained in the database. It is a diagrammatically representation of entities and relationship between them- It is also known as E-R diagram. Some terms related to E-R model are described ahead

 

Entity

It is an object that has its existence in the real world. It includes all those things about which the data are collected. "Entities are represented in rectangles." e.g. customer buys goods, it means customer and goods are entities.

 

Attributes

It describes the characteristics or properties of entity. In tables, attributes are represented by columns. Attributes are drawn in elliptical shapes e.g. ITEM entity may contain code and price.

 

Entity Set

It is a set of entities of the same type that share that same properties or attributes, e.g. Students is an entity set of all student entities in the database.

Entity set is of two types as follow

(a) Strong entity set It has a primary key or can be easily distinguishable each attributes.

(b) Weak entity set It does not posses sufficient attributes to form a primary key.

 

Relationship

It is an association among several entities. A relationship describes how two or more entities are related to each other. It is represented by diamond shape.

 

 

Relationship can be divided into three parts

(a) One to one

(b) Many to one

(c) One to many