5.2 INTRODUCTION TO DATABASE
A Database Management System (DBMS) is formally defined as:
A database management system (DBMS) is a collection of programs which enables users to create and maintain a database. The DBMS is hence a generalpurpose software system that facilitates the processes of defining, constructing and manipulating databases for various applications.
Before DBMS came into existence traditional file approach were used for data handling. Let us see basic differences between file approach and DBMS.
5.2.1 File System vs. DBMS
Database is the term which may be a little new to you, but understand data as the collection of some values and when data becomes useful, it becomes information. Now question arises how you will save data? You will save in computer memory, but how and in what form? You will save the data in memory; the answer is in the form of files. The answer is, you will save the data in memory and in form of files. Data storing can be done manually also. But in case of large amount of data, this task can become difficult and could be full of errors. So, to avoid all these difficulties computers are used. You can say that move on to a computer from traditional filing approach or use computerized system for data handling good for the organization which requires a large amount of data handling.
5.2.2 Traditional File System or File Oriented Approach
In the traditional file processing approach, generally each business application was designed to use one or more specialized data files containing only specific types of data records and saved in different files. Taking a very simple example of your daily expense, how much money you are spending and how much you are saving can be kept in a different file. Monthly expense would be kept in different file. An organization may keep yearly inventory report and production department related information in different files. This system may be better and reliable than the manual handling of the system now let us see how many types of files are there in the system. In order to save the relevant information in management systems the relevant file.
Generally, there are three kinds of files: Master File, Transactional, Work File and Database File.
Master File: A file containing static information or the information which does not change frequently. For example employee personal details like his name, address, spouse name etc.
Transaction File: A file in which the data is updated after every regular interval of time. For example in sales file how much sales has happened are updated every week or on daily basis.
Work File: This is a temporary file which helps in sorting and merging of records from one file to another. Where a record is form by combining multiple related fields. For example a record named Account will have fields: name, address, account no. account type etc
Database File: It is a suite of programs which typically manages large structured sets of determined data, offering ad hoc query facilities to many users. They are widely used in business applications. A database management system (DBMS) can be an extremely complex set of software programs which controls the organization, storage and retrieval of data (fields, records and files) in a database. For example, bank accounting system.
Now after, seeing at different files you should be able to categorize the data and can save it in the respective files. A database management system helps in storing related data, performing queries on data, and updating of data. Let us see some advantages of DBMS.
DBMS has many advantages such as:
1) Reduction in data redundancy: It means that DBMS helps in removing duplicate data from the database. If any type of duplicate entry arises, then there is man (DBA, database administrator) who can Centrally control the data and arrange data in non duplicate forms on the basis of Primary Key . now what is a Primary Key ? It is a unique field and has non duplicate information. For example, Roll Number is always unique and different for every student which is not repeated. So, Roll Number is a primary key to store the student data uniquely. You will notice that Redundancy leads to several problems:
i) Duplication of Efforts.
ii) Save the storage space / memory where data is saved.
2) Provides security: Restrict users to access all the information of the database by providing passwords and rights. There are also various coding techniques to maintain the security of important and crucial information from unauthorized users and accidents.
3) Data can be shared among various users: In a database system, data can be easily shared by different users. For example, student data can be share by teacher department subject wise, Fee department, library etc.
4) Maintenance of data integrity: Data Integration can be understood by the fact that the data contained in the database should be accurate and consistent.
5) Data Integrity : Since a large number of users can access database together at the same time. So, to maintain the same data/information correct every time and at every place during any operation is called data integrity. For example, someone wants to make change in the address of the student Ashish record in the Fee Department and at the same time if Librarian is also sending some letter to Ashish’s address. The updating of address should be done at both the places (Fee Department and Library Department) to avoid inconsistency and such operations maintain data integrity.
5.2.3 Types of DBMS
Take example of an excel sheet having columns and rows where you have to save data and also you can apply various operations such as modification, updation in the sheet. These operations are very essential component of a DBMS.
In a database, how data is stored, how data is searched for answering a query plays very important role. On the basis of representation of data, DBMS has been broadly divided into four types. The division had been done in order to reduce the response time of a query and to save memory.
Now, we will discuss about types of databases. These categories are made on the basis of the ways in which data are stored in the database:
- · Sequential (“Flat File”)
- · Hierarchical
- · Network
- · Relational
In business organizations or large applications, generally two types of data models are used in DBMS: (i) relational data model (ii) object data model (it is not covered here). But still many legacy applications are still running on hierarchical and network model.
Broadly Classification of DBMS is done on the basis on various parameters like:
a) Number of users of system whether single user or multi user system.
b) General purpose or special purpose database.
c) How data is stored and can be accessed.
Now let us discuss these different categories of DBMS one by one.
Sequential Files
In this approach, data is stored in a single file (flat file). You must have worked in Microsoft Excel Spread sheet files; spread sheets are sequential in nature and act as flat files.
Flat File: A flat file can be defined as a text file in which you can organize the data in your own format and it is not linked with any other file. In this the data is processed in the way the data is stored.
If you want to keep track of your money or finances on weekly basis. The amount you have spent in purchasing items. Let us say you want to note down 6 items you purchased this week and five items next week then you will find that you are typing shop name and address over and over again which is a waste of time and memory management systems space of your computer. Similarly in sequential file system, same data is stored again and again. This is one of the major disadvantages of sequential file.
Assume that you are storing the following information again and again:
1. Item no.
2. Description of an item
3. Price
4. Quantity
5. Shop name
6. Address of shop
7. Date of purchase
If you use a flat sequential file, you have to store the same information repeatedly.
A flat sequential file, you can see, as a file having information exactly in the format in which it is generated without proper structuring.
This system is good for very small data storage. If only few records and some tables are required then in that case a flat file will usually serve the purpose. It does not maintain any relationship between data stored and does not avoid redundancy of data in the file.
For example, we can take the table below which keeps the information about the items sold in a general store, here you will find some repetition of the data in Table 1:
a) It is simple and easy to implement.b) It can be used for small databases.c) It is used where no links between data or files are required
a) Duplication of data Entriesb) Since the access mode is sequential or in a single order, the system gets slower for large databases.c) The file is hard to recover if it is corrupted or system crashes.
Foreign Key: The Foreign Key is the key of a table which is used for referencing the other table for example, in a school library if Librarian wants to see issued book to a particular student, he/she will refer that with the roll no. of that student so, in this case in the Library table RollNo of a student is the Foreign Key which is the Primary key in the STUDENT table. Here, BookNo is the Primary Key for LIBRARY table and RollNo is the Foreign Key which is the Primary Key in the STUDENT Table.
There are Various Constraints in Database Systems. The constraints mean various checks on the database which are used to validate the data in the table. These are some constrains :
a) KEY Constraint
b) NOT NULL constraint
c) Semantic Constraint
d) Referential Integrity Constraint
Key Constraint:While designing any database, you must specify one attribute or combinations of attributes which must be unique. That unique key becomes the Primary Key to be used to identify tuples or records. For example RollNo is the primary key in the STUDENT table and BOOK table BookNo is the primary key.
NOT NULL Constraint: In this validation, it says that no Primary key should have NULL value. For example, every student must possess a roll no.
Semantic Constraint: It expresses the general restriction on the data or defines some boundary condition on the required attribute. For example, a student can be issued two books at a time.
Referential Constraint: This check requires that a data of an attribute which is referred to should actually exists as an entity in some table. DBMS keeps a check on the referential integrity when a referenced value is inserted or changed.
For example, the LIBRARY table, RollNo 12 (student name : Prateek) must exist in STUDENT table when referred to by the LIBRARY table.
Now let us look into another type of DBMS which is a Hierarchical DBMS.
Hierarchical Databases Model
In this kind of databases, data are stored in the form of a hierarchy. Hierarchy database is like a tree structure which has one root and many branches. It uses the concept of parent child relationship. In this type of database a single file may have many relationships with many files.
This system is very advance compare to a flat file (sequential file system), where in a business has to deal with several files which are hierarchically related to each other.
Let us take an example of Customer and Supplier System. A Store name ABC which is a store of several departments, each department is having a storekeeper, number of staff, and their address. Each department is having several products and each product is having product number, product description and price. Also, the available quantity of different products are stored in the database. Further, information about suppliers of different products is stored in which the database.
This example of Store can be shown in a tree like structure as given below:
In the Figure Tree (T1): A department is having various products and here table Department is acting as a parent because it has various products (Table Product which is acting as a child). Further in fig T2, Product table has many Suppliers and in II part it has shown that a Supplier (Supplier table with attributes Supplier_name, Supplier_Street and Supplier_City) delivers/supplies various Orders.(Order Table with attributes Order Number and Order_description)
1) Hierarchical model is simple to construct.2) This type of database is generally used in daily life organizations where data is organized in hierarchically organized domains, component assemblies in manufacturing, personnel organization in companies.3) Accessing of data is faster in hierarchical database compared to sequential database.
a) It eliminates unnecessary duplication of data with associated errors and costs .b) It uses concept of a data definition language, data manipulation languagec) It uses concept of m:n linkages or relationships , which means that an owner record can have many member records. Similarly a member record can have several owners. But a hierarchical model allows only 1:n
Customer: Customer No, Customer name, address, City.Order : Order No, Order Description, Supplier no, Order DateSupplier : Supplier No, Supplier Name, Address, Contact No.Product : Product No, ProductDescription,ProductPrice
A Customer record can have many Orders and Orders record can have many Products. Product record can be owned by both Order and Product records both.
In the figure below, Supplier table shows a relation link with the Order Table . A Supplier record can delivery many Orders.
Here, boxes are record types and lines are links. In figure 2, Supplier has to supply/deliver the Order. Many Suppliers can deliver/supply many Orders. This is the relationlink between them.
You can summarize the Network Model by greater flexibility links between records of the same type are not allowed while a record can be owned by several records of different types .
Relational Model
In this model of DBMS, data is stored in twodimensional table (rows, and columns), this is a model relation between two tables exists with something in common or some data which these two tables share. Each column of a table is referred to as an attribute and the values which it may contain is called domain of values.
This is model was proposed by IBM researcher E.F. Codd in 1970. It is more of a concept than a data structure. Its internal architecture varies substantially from one RDBMS to another. Relational Database model use relational concept of mathematics in a table.
- · You can understand Relational Model where
- · Each row is a record or tuple
- · Each column is an attribute
For example, details of a shop where a Product (productno, product description, price, quantity), and its Supplier details (supplier no, supplier name, productionno) are stored in two tables. Let us say these are named as Product and Supplier.
Now if you observe in the figure 3 below, table Supplier and table Order are having attribute Supplier_no in common and they (Supplier and Order) are related with each other due to this common attribute.
PK = Primary Key
FK = Foreign Key
− Supplier_No is the primary key for supplier table as it uniquely identifies the record.
− Order _No is the primary key in the Order Table since it uniquely identifies the record.
− Supplier_No is the foreign key for the Order Table which comes from supplier
Table and used for Refreshing the Record
A relational database is a finite set of relational schemas. A relational scheme is defined as the relational model which represents data as twodimensional tables called relations.
The contents of a table are rarely static thus the addition or deletion of a row is required. Some advantages of relational model is:
1) Mostly used by the most popular type of DBMS in use.
2) It has various integrity rules which ensure data consistency.
3) Easy to use and handle data.
Remember that a "relation" is a table of records, not a linkage between records.
The degree of a relation is the number of attributes in the table, For example if a table (relation) is having four columns then the degree of that table is four.
1 attribute is a unary relationfor example, the wife has one husband.
2 attributes is a binary relationfor example, two employee works in two departments.
N attributes is an nary relation for example many relationship is SUPPLY between supplier and Project. Where each relationship associates three entities, Supplier, Part and Project whenever Supplier supplies part to Project.
Comments
Post a Comment