Basics of database system
Database-database is a collection of interrelated data i.e. it is composed of a collection of files that are linked in such a way that information from one of the files may be combined with information from other files so that user may receive the exact information needed.
For example School database organises the data about students, teachers, and admin staff etc. which helps in efficient retrieval, insertion and deletion of data from it.
Basic building block of a database is data. The input to the database is known as data and output as information.
Data and information
Data- data is a raw and unorganised fact that required to be processed to make it meaningful. i.e. data is the collection of raw facts and figures.
E.g. 1, Sam, 15
Information- information is data formatted in a manner that allows it to be utilised by human beings in some significant way i.e. processed data is called information.
E.g.
Database elements
Table- in a database table is composed of records and Fields that hold data. Tables are also called datasheets. Each table in a database holds data about a different but, related subject. Below is example of database table student
Record- data is stored in records. A record is composed of fields and contains all the data about one particular person, company or item in a database. Records appear as rows in the database table. A record for Roll_No 2 is highlighted in table Student
Field- A field is part of a record and contains a single piece of data for the subject of the record. In the database table illustrated in, each record contains 3 fields Roll_No, Name, Age. Fields appear as columns in a database table. Data from the Name field for 3 records is highlighted in the table Student below:
The main purpose of database management system is to manage the data. Consider a school that keeps the data of students, teachers, courses, books etc. To manage this data we need to store this data somewhere, where we can add new data, delete unused data, update outdated data, retrieve data. To perform these operations on data we need a database management system that allows us to store the data in such a way so that all these operations can be performed on the data efficiently.
We can use database management system for:
- Creation of a database
- Retrieval of information from the database
- Updating the database
- Managing a database
So we can say that database management system is a collection of interrelated data and set of programs to store and access those data in an easy and effective manner. Some examples of database management systems are MySQL, Oracle etc.
DBMS allows user the following tasks:
- Data definition- it helps in creation, modification and removal of definition that define the organisation of data in database
- Data updation- it helps in insertion, modification and deletion of the actual data in the database
- Data retrieval- it helps in retrieval of data from the database which can be used by applications for various purposes
- User administration- it helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, and recovering information corrupted by an unexpected failure
Application where we can use database management system are:
- Telecom- there is a database to keep track of the information regarding calls made, network uses, customer details etc. Without the database system it is hard to maintain that huge amount of data that keeps updating every miliseconds.
- Industry- where it is a manufacturing unit, warehouse or distribution centre each one needs a database to keep the records of ins and outs.
- Banking system- for storing customer information, tracking day-to-day credit and debit transactions, generating bank statement etc. All this work has been done with the help of database management systems
- Sales- to store customer information, production information and invoice details
- Airline- to travel through airlines we make early reservation, this reservation information along with flight schedule is stored in database
- Education sector- database systems are frequently used in schools and colleges to store and retrieve the data regarding student details, staff details, course details, exam details etc.
- Online shopping- online shopping websites such as Amazon, Flipkart etc. These sites stores the product information, addresses and preferences, credit details and provide the relevant list of products based on customers query all these involves a database management system
Components of Database system
A database system is composed of following components
A database system is composed of following components
- Data
- Hardware
- Software
- Database access language
- Users
which co-ordinate with each other to form an effective database system
- Data- data is that resource, for which DBMS is designed. the motive behind the creation of DBMS is to store and utilize data. In a typical database the user saved data and meta data is stored by the DBMS to better understand the data. (Metadata is data about the data. This is information stored by the DBMS to better understand the data stored in it. For example when we store our name in a database the DBMS will store what is the size of the name, is it stored as related data to some other data or is it independent, all this information is called metadata)
- Hardware- when we say hardware we mean computer hard disk, input/output channels for data and any other physical components involved.
- Software- this is the main component, as this is the program which controls everything. The DBMS software is more like a wrapper around the physical database which provides us with an easy-to-use interface to store, access and update data.
- Database access language- data base access language is a simple language designed to write commands to access, insert, update and delete data stored in any database.
- Users- users are those persons who need the information from the database to carry out their primary business responsibilities.
The various types of users which can access the database are:
- Database administrator (DBA)
- Database designers
- End users
- Application programmers
RDBMS (Relational Database Management System)
RDBMS stands for relational database management system. An RDBMS is a DBMS designed specially for Relational databases. Relational database management system is an advanced version of a DBMS. It came into existence during 1970's and it allows the organization to access data more efficiently then DBMS.
Relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easy to locate and access specific values within the database. It is relational because values within each table are related to each other.
RDBMS is a software system which is used to store only data which need to be stored in the form of tables. In this kind of system data is managed and stored in rows(tuples) and columns(attributes).
RDBMS is a powerful data management system and is widely used across the world. Examples of RDBMS is MySQL, Oracle, SQL server etc.
Traditional file processing systems- it is totally computer-based system where all the information is stored in different computer files. Also traditional files system stores data in a manner that all the departments of an organisation have their own set of files that creates data redundancy(duplication).
But this system is good only for small organisation having small number of items. In this traditional file system, each file is independent of other file. To illustrate traditional file processing system definition let us take an example of school where student record for examination is stored in other file and his library record is stored in different file that creates many duplicate values like roll number, name and father name etc.
Characteristics of traditional file processing system
- It stores data of an organisation in group of files
- files carrying data are independent of each other
- COBOL, C, C++ programming languages were used to design the files
- each file contains data for some specific area or department like library, student fees, and student examinations.
- It is less flexible and has many limitations
- It is very difficult to maintain the file processing system
- Any change in one file affects all the files that creates burden on the programmer
- File in traditional file processing system are called flat files
Over all traditional file processing system is good in many cases in compared to manual (non computer-based system) but still it has many limitations.
Problems in traditional file oriented approach
- Data redundancy- data redundancy refers to the duplication of data, let's say we are managing the data of a school where a student is enrolled for two courses, the same student details in such case will be stored twice which will take more storage then needed. Data redundancy often leads to higher storage cost and poor access time.
- Data inconsistency- data inconsistency leads to data inconsistency, let's take the same example that we take above, a student is enrolled for two courses and we have student address stored twice, now student request to change his address, if the address is changed at one place and not on all the records then this can lead to data inconsistency.
- Data isolation- because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.
- Dependency on application programs- changing files would lead to change in application programs
- Atomicity issue- atomicity of a transaction refers to “all or nothing”. which means either all the operations in a transaction executes or none. For example let's say A transfers 100 rupees to B’s account. This transaction consists of multiple operations such as debit 100 rupees from A’s account, credit 100 rupees to B’s account. Like any other device a computer system can fail, let's say it fails after the first operation, then in that case A’s account would have been debited by 100 rupees but the amount was not credited to B’s account. In such a case the rollback of operation should occur to maintain the atomicity of the transaction. It is difficult to achieve atomicity in file processing systems
- Difficult data access- user should know the exact location of file to access data, so the process is very cumbersome and tedious. If user wants to search student hostel allotment number of student from 10000 unsorted students records how difficult it can be.
- Unauthorised access- file system may lead to an unauthorised access to data if a student get access to file having his marks he can change it in unauthorised way.
- No concurrent access- the access of same data by multiple users at same time is known as concurrency. File system does not allow concurrency as data can be accessed by only one user at a time.
- No backup and recovery- file system does not incorporate any backup and recovery of data if file is lost or corrupted
- Data security- data should be secured from unauthorised access, for example a student in a school not be able to see the payroll details of the teachers, such kind of security constraints are difficult to apply in file processing systems.
- Limited data sharing- there is limited data sharing possibilities with the traditional file system each application has its own private files and users have little choice to share the data outside their on applications.
Advantages of DBMS
There are several advantages of relational database management system. Few of them are as follows:
- Minimal Redundancy- unlike traditional file system storage, data redundancy in DBMS is very less or not present. Data redundancy occurs when the same data are stored unnecessarily at different places.
- Data inconsistency can be avoided- in traditional file system storage, the changes made by one user in one application does not update the change in other application and then it leads to data inconsistency. While this is not the case with DBMS systems.
- Searching capability- searching and retrieving of data is very easy in DBMS systems. The need to write separate programs for each of the search is eliminated as in the case within a traditional files based approach.
- Data sharing- data sharing is the primary advantage of DBMS. DBMS systems allows user and applications to share data with multiple applications and users.
- Data security- DBMS systems provide strong framework to protect data privacy and security. DBMS ensure that only authorized users have access to data and there is a mechanism to define access privileges in DBMS.
- Flexibility- DBMS systems are more flexible than file processing systems.
- Data integration- data integration is a process of combining the data residing at different locations and present the user with a unified view of data. DBMS system allow data integration with much feasibility.
- Easy access to data- A database system manages data in such a way so that the data is easily accessible with fast response times.
- Concurrent access to data- data can be accessed concurrently by different users at same time in DBMS.
- Data backup and recovery- this is another advantage of DBMS as it provide as strong framework for data backup. Users are not required to backup their data periodically and manually.
- Improved decision making- better managed data and improved data access make it possible to generate better quality information on which better decisions are based.
Disadvantages of DBMS
- DBMS requires high initial investment for hardware, software and trained staff
- In order to convert our data into database management system will need to spend a lot which adds on to the coast of the database management system
- In order to work with the database management system a team of technical staff is required who understand DBMS
- DBMS requires disk storage for the data and sometimes we need to purchase extra space to store our data
- Database systems are complex to understand
- DBMS does not give a good performance as its speed is very slow
- Backup and recovery are more difficult. This is because of increased complexity and because databases are often processed by several users concurrently
Three level architecture of DBMS
DBMS is collection of interrelated data and set of procedures to access the data. Database systems are made up of complex data structures. The main purpose of DBMS is to provide users with an abstract view of the data. To ease the user interaction with database the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction.
There are mainly three levels of data abstraction
- Internal or physical level
- Conceptual or logical level
- External or view level
Conceptual level- it is also called logical level. The whole design of the database such as relationship among the data, schema of the data etc. are described in this level. Database constraints and security are also implemented in this level of architecture. This level is maintained by DBA (database administrator)
External level- it is also called view level. The reason this level is called 'view' is because several users can view their desired data from this level which is internally fetched from database with the help of conceptual and internal level. In this level user just interact with system with the help of GUI (graphical user interface) and enter the details at the screen they are not aware of how the data is stored and what data is stored such details are hidden from them.
The database administrator (DBA)
Database administrator (DBA) is the person which makes the strategic and policy decisions regarding the data of the enterprise, and who provide the necessary technical support for implementing these decisions. Therefore DBA is responsible for overall control of system at a technical level in database environment. 
The primary resource is the database itself and the secondary resource is the DBMS and related software administering these resources is the responsibility of the database administrator DBA.
DBA responsibilities includes designing, implementing and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees.
The role of the DBA is very important and is defined by the following functions:
- Schema definition- the DBA define the logical schema of the database. A schema refers to the overall logical structure of the database. According to this schema database will be developed to store required data for an organisation.
- Storage structure and access method definition- the DBA decides how the data is to be represented in the stored database.
- Assisting application programmers- the DBA provides assistance to application programmers to develop application programs.
- Physical organisation modification- the DBA modifies the physical organisation of the database to reflect the changing needs of the organisation or to improve performance.
- Approving data access- the DBA determine which user needs access to which part of the database. According to this various types of authorisations are granted to different users.
- Liaising with users- the DBA needs to interact continuously with the users to understand the data in the system and its use. The DBA figures out which client needs access to which part of the database.
- Monitoring performance- the DBA monitors performance of the system. The DBA ensures that better performance is maintained by making changes in physical or logical schema if required.
- Backup and recovery- database should not be lost or damaged. The DBA ensures this periodically by backing up the database on magnetic tapes or remote servers. In case of failure such as virus attacks database is recovered from this backup.
SQL data types
Date types are rules that define what data may be stored in a column and how that data is actually stored. Data types are used for several reasons:
- Data types enables us to restrict the type of data that can be stored in a column. For example, a numeric data type column only accepts numeric values.
- Data type specifies storage efficiently and instructs how it stores data internally in memory.
- Data types allow to specify field type and size to implement optimization of database. For example do not define field 10 characters wide, if need only 2 characters wide field.
- Data type allow alternate sorting orders
In SQL there are three main data types:
- Numeric
- Date and time
- String
Numeric data type- numeric data types are normally used to store numeric data like age, marks, salary, price etc.
Numeric data types are:
- Int- used for number without decimal
- Decimal(m, d)- comedy used for floating/real numbers.
Here m denotes the total length of number and d is number of decimal digits
Date and time-
- Date- used to store date in YYYY-MM-DD format
- Time- used to store time in HH:MM:SS format
String data type- string data types are normally used to store names, addresses, descriptions or any value that contains letters and numbers including binary data like image or audio files.
String data types are:
- char(n)- used to store a fixed length string. Here n denotes maximum number of characters
- varchar(n)- used to store a variable length string. Here n denotes maximum number of characters
Difference between char and varchar datatypes
| Char Datatype | Varchar Datatype |
|---|---|
| It specifies a fixed length character string | It specifies a variable length character string |
| When a column is given datatype as char(n), then MySQL, ensures that all values stored in that column have this length that is n bytes. If a value is shorter than this length n then blanks are added but the size of value remains n bytes | When a column is given datatype as varchar(n), then the maximum size a value in this column can have is n bytes. Each value that is stored in this column store exactly as we specify it that is no blanks are added if the length is shorter than maximum length n. |
SQL statements
Introduction to SQL
SQL stands for structured query language, which is a standardized language for interacting with RDBMS (relational database management system). SQL is a database query language used for storing and managing data in RDBMS. SQL was the first commercial language introduced for E.F. Codd's relational model of database. Today almost all RDBMS (MySQL, Oracle, Informix, Sybase, MS-Access) use SQL as the standard database query language.
Characteristics of SQL
- SQL stand for structured query language. It is used for storing and managing data in relational database management system (RDBMS)
- All the RDBMS like MySQL, Informix, Oracle, MS-Access and SQL server use SQL as their standard database language.
- SQL allows user to query the database in a number of ways, using English like statements.
- SQL is used to perform C.R.U.D (Create, retrieve, update and delete) operations on relational databases.
- SQL can also perform administrative task on database such as database security, backup, user management etc.
- We can create database and tables inside database using SQL.
- SQL is not case sensitive. Generally, keywords of SQL are written in upper case.
- Using the SQL statements, we can perform most of the actions in a database.