![]()
CHAPTER 1
Database Systems Architecture
Introduction
This chapter explains the need for database management systems over the file management system. A database management system is a collection of interrelated data. One of the main advantages of using a database system is that the organization of data can be exercised, such as, via the DBA (database administrator), a software used for centralized management and control over the data.
SQL (structured query language) allows us to write a query and then execute on the client server machine where SQL is installed on server side, and the queries are fired from client machine.
The most common use of relational database management system (RDBMS) is to implement simple CRUD operation which allows to perform create, read, update, and delete tasks. Relational model allows two or more than two entities connected to each other. We will learn about the advantages of relational model by the end of this chapter, and various terms used in relational model in the later part of this book.
Structure
- Introduction
- Purpose of database system
- Network architecture
- Views of data
- Database system architecture
- Data models
- Conclusion
- Questions
Objectives
After studying this unit, you should be able to:
- Understand the need for database management system (DBMS)
- Discuss the use of DBMS over file system
- Know the data models, like hierarchical, network, and relational models
- Identify the relational model along with the advantages
Data storage is very important as it protects and retrieves the required data whenever a kit is needed. Data is a key asset for businesses, so the need for data storage is important. A set of programs are used to access the data.
A DBMS is a collection of interrelated data. It provides users and programmers with a systematic way to create, retrieve, update, and manage data. These are systems that can be used to manage transactional databases, such as HR systems, banking systems, payroll systems, and so on.
Database is a collection of inter-related data which helps in efficient retrieval, insertion, and deletion of data from database and organizes the data in the form of tables, views, schemas, reports, and so on. For example, an examination management system which organizes the data on students, subjects, semesters information and the paper details. Database contains information about a particular enterprise. It provides an environment that is both convenient and efficient to use.
Database management system
A DBMS is a software system that allows to access data situated in databases. The objective of DBMS is to provide a convenient and effective method of defining, storing, and retrieving the information contained in the database.
The number of database applications which are available are as follows:
- Banking: Customers, accounts, loan, transactions
- Airlines: Reservations, schedules
- Universities: Registration, grades
- Sales: Customers, products, purchases
- Manufacturing: Production, inventory, orders, supply chain
- Human resources: Employee records, salaries, tax deductions
Customer Table:
| CustNum | CustName | Address |
| 1 | A | Mumbai |
| 2 | B | Delhi |
| 3 | C | Jaipur |
Table 1.1: Customer File
Orders Table:
| OrderNum | OrderDate | Amount |
| 101 | 01-01-2009 | 12340 |
| 102 | 02-02-2009 | 20075 |
| 103 | 13-06-2009 | 9740 |
Table 1.2: Orders File
Although, this seems to be a working solution, the information does not store the relation regarding which order belongs to which customer, and how many orders are placed by each customer. For doing this, one would be duplicating all the information of customers into orders table or vice versa.
For example, the excel sheet or data file would look like the following table 1.3:
| OrderNum | OrderDate | Amount | CustNum | CustName | Addr |
| 101 | 01-01-2009 | 12340 | 1 | A | Mumbai |
| 102 | 02-02-2009 | 20075 | 2 | B | Delhi |
| 103 | 13-06-2009 | 9740 | 1 | A | Mumbai |
Table 1.3: Cust_orders File
As we can see in the preceding table, the data duplication, where custnum is repeated, and if the same customer gives many orders, then the customersā data would be repeated that many times.
This redundancy would create the inconsistencies, which is an important aspect in keeping data properly.
There are a few other advantages, which are as follows:
- If the customer with custnum 1 shifts to Indore, then this change should be done not only in customerās file but in all the records of
Orders file. This can be a difficult task, since otherwise data will be inconsistent. CustNum & OrderNum identify a particular customer or order. While giving these numbers, there are chances that the same number is given to another customer or order. There are chances that a user may delete the customerās record from the customerās file when his/her orders are pending.- If the same file is accessed by many users ā the administrative office and the proprietor ā then the proprietor as well as office person can modify it. It is difficult to set read/write/modify permissions for owner and read permissions for clerk.
- One more important point is to check whether the values entered under columns, such as
order_amt < 0 or customer address may be entered as blank too.
Now, we will try to understand the limitations of the file system by looking at the following points:
- Difficulty in accessing data: Suppose one wants to find the data of those orders which were placed on 1st January 2009, and the order amount>10000 in such cases. A conventional file processing environment does not allow the needed data to be retrieved in a convenient and efficient manner. This can be done easily by using databaseās management system.
- Atomicity of updates: Failures may leave database in an inconsistent state with partial updates carried out. For example, transfer of funds from one account to another should either complete or not happen at all.
- Simultaneous (concurrent) access by multiple users: Uncontrolled concurrent accesses can lead to inconsistencies. For example, the orders placed by the proprietor and/or the administrative staff; both would not come to know which order was placed at what time, and how many items were there in balance or in store.
- Supervision and regulations: On data it can be difficult since many application programs should be coordinated.
- Security problems: Enforcing security constraints could be difficult in an ad-hoc manner.
Advantages of DBMS
One of the main advantages of using a database system is that the organization can exercise, via the DBA, centralized management and regulations over the data. The database administrator is the focus of the centralized control.
The advantages of the database management system are as follows:
- Reduction of redundancies: Centralized control of the data by the DBA avoids unnecessary duplication of data. DBA reduces the total amount of data storage required. In this manner, the redundancies that exist are controlled and the system ensures that these multiple copies are consistent.
- Reduction in keeping number of files: When the same data is stored in a number of files, it brings in data duplication. In such cases, if the data is modified at one place, the data would be copied in each of the files. This happens as follows:
- Storage space gets wasted.
- Processing time may be wasted as more data is to be handled.
- Inconsistencies may be created.
- Shared data: Sharing of data is allowed by any number of application programs or users.
- Integrity: Data integrity means that the data contained in the database is both accurate and consistent. Data values being entered are checked to ensure that they fall within a specified range, and are of the correct format.
- Security: Data is very important for an organization and it should be kept confidential. Any unauthorized person must not access such confidential data. The DBA, who has the ultimate responsibility to take care of the data in DBMS, can ensure the...
