Grade:XII (Concept of database)
Concept of Database
Information is the backbone of any organization. It is the most critical resources of the organization. Different methods and techniques have been adopted to keep up to date information. Before the invention of computer, people used to keep records in the paper. With the development of computer, methods of processing, techniques of keeping data and information have been completely changed because computer can store a large amount of data in a small place of a disk which is more secured and very easy to retrieve.
Concept of Database
Information is the backbone of any organization. It is the most critical resources of the organization. Different methods and techniques have been adopted to keep up to date information. Before the invention of computer, people used to keep records in the paper. With the development of computer, methods of processing, techniques of keeping data and information have been completely changed because computer can store a large amount of data in a small place of a disk which is more secured and very easy to retrieve.
Data: Data
is raw facts which are composed of alphabets, digits and other symbols. It may
or may not give any sense. For example: 1, Rubesh, 43, 11,etc.
Information: When data are processed using a database program or software, and then converted to the meaningful result, called information. It is an organized collection of related data. It gives complete sense.
Information: When data are processed using a database program or software, and then converted to the meaningful result, called information. It is an organized collection of related data. It gives complete sense.
Field: A
field is the property or attributes of a table. Example: SN, NAME, ROLLNO,
CLASS are fields.
Record: A
record is the collection of interrelated fields.
SN NAME ROLLNO
1 RAMESH 23
here, 1, ramesh and 23 are records.
Table : A table is the arrangement of rows and columns. Each table must have unique name and must be simple. It is the backbone of the database as it is used as a source.
Database: Database is a collection of interrelated data of objects or entities stored in tabular form. Database gives very useful information for an organization during data manipulation and decision making. It provides a base or foundation for managing large volume of data in well organized manner. For example: Phone diary, Result sheet, Dictionary, Address book.
Flat file system/ file processing
A file processing system is traditional way of storing data electronically. A flat file stores data in a single stream of bytes that can grow up as large as necessary until the disk is full. Despite of simplicity, a flat file system has significant limitations. It is extremely difficult for processing the complex request using query to the file.
Limitation of Flat file system
- The same information stores in different places of computer memory so duplication of data caused data redundancy.
- Difficulty in representing data from the user's view.
- Special computer program have to be written to retrieve data from each independent file.
- Data security is one of the major problems of flat file processing system
- It is difficult for processing complex queries.
SN NAME ROLLNO
1 RAMESH 23
here, 1, ramesh and 23 are records.
Table : A table is the arrangement of rows and columns. Each table must have unique name and must be simple. It is the backbone of the database as it is used as a source.
Database: Database is a collection of interrelated data of objects or entities stored in tabular form. Database gives very useful information for an organization during data manipulation and decision making. It provides a base or foundation for managing large volume of data in well organized manner. For example: Phone diary, Result sheet, Dictionary, Address book.
Flat file system/ file processing
A file processing system is traditional way of storing data electronically. A flat file stores data in a single stream of bytes that can grow up as large as necessary until the disk is full. Despite of simplicity, a flat file system has significant limitations. It is extremely difficult for processing the complex request using query to the file.
Limitation of Flat file system
- The same information stores in different places of computer memory so duplication of data caused data redundancy.
- Difficulty in representing data from the user's view.
- Special computer program have to be written to retrieve data from each independent file.
- Data security is one of the major problems of flat file processing system
- It is difficult for processing complex queries.
Database Management System
A database management system is computerized records keeping system. It is software that defines, manipulates and manages the database. It allows accessing the files, updating the records and retrieving data as requested. Examples: FoxPro, Dbase, Sybase, MS Access, My SQL server, Oracle, etc.
Advantages
1. Sharing data; Data stored in a database can be shared. It refers to the capacity that makes data simultaneously accessible by many users without any interference.
2. Reduced data redundancy: The same data may be duplicated at many times or places, is called data redundancy. DBMS reduces such type of duplication of data from database.
3. Data Security: In database system, an unauthorized person cannot access data from database. Although various departments may share data in database, access to specific information that can be limited to selected users.
4. Data backup and recovery : DBMS provides lookup facilities to store data future use. If any files or data lost in any computer, it is possible to restore them from database server.
5. Multiple user interfaces : DBMS provides variety of interfaces for various user. It provides query language interface, forms and command interfaces so that users can easily access with database.
Objectives of DBMS
Mass Storage
DBMS can store a lot of data in it. So for all the big firms, DBMS is really ideal technology to use. It can store thousands of records in it and one can fetch all that data whenever it is needed.
Removes Duplicity
If you have lots of data then data duplicity will occur for sure at any instance. DBMS guarantee it that there will be no data duplicity among all the records. While storing new records, DBMS makes sure that same data was not inserted before.
Multiple Users Access
No one handles the whole database alone. There are lots of users who are able to access database. So this situation may happen that two or more users are accessing database. They can change whatever they want, at that time DBMS makes it sure that they can work concurrently.
Data Protection
Information such as bank details, employee’s salary details and sale purchase details should always be kept secured. Also all the companies need their data secured from unauthorized use. DBMS gives a master level security to their data. No one can alter or modify the information without the privilege of using that data.
Data Back up and recovery
Sometimes database failure occurs so there is no option like one can say that all the data has been lost. There should be a backup of database so that on database failure it can be recovered. DBMS has the ability to backup and recover all the data in database.
Integrity
Integrity means your data is authentic and consistent. DBMS has various validity checks that make your data completely accurate and consistence.
Disadvantages of Database Management System (DBMS):
Although there are many advantages but the DBMS may also have some minor disadvantages. These are:
1. Cost of Hardware & 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, DBMS software is also Very costly.
2. Cost of Data Conversion:
When a computer file-based system is replaced with a database system, the data stored into data file must be converted to database files. It is difficult and time consuming method to convert data of data files into database. You have to hire DBA (or database designer) and system designer along with application programmers; alternatively, you have to take the services of some software houses. So a lot of money has to be paid for developing database and related software.
3. Cost of Staff Training:
Most DBMSs are often complex systems so the training for users to use the DBMS is required. Training is required at all levels, including programming, application development, and database administration. The organization has to pay a lot of amount on the training of staff to run the DBMS.
4. Appointing Technical Staff:
The trained technical persons such as database administrator and application programmers etc are required to handle the DBMS. You have to pay handsome salaries to these persons. Therefore, the system cost increases.
5. Database Failures:
In most of the organizations, 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.
A database management system is computerized records keeping system. It is software that defines, manipulates and manages the database. It allows accessing the files, updating the records and retrieving data as requested. Examples: FoxPro, Dbase, Sybase, MS Access, My SQL server, Oracle, etc.
Advantages
1. Sharing data; Data stored in a database can be shared. It refers to the capacity that makes data simultaneously accessible by many users without any interference.
2. Reduced data redundancy: The same data may be duplicated at many times or places, is called data redundancy. DBMS reduces such type of duplication of data from database.
3. Data Security: In database system, an unauthorized person cannot access data from database. Although various departments may share data in database, access to specific information that can be limited to selected users.
4. Data backup and recovery : DBMS provides lookup facilities to store data future use. If any files or data lost in any computer, it is possible to restore them from database server.
5. Multiple user interfaces : DBMS provides variety of interfaces for various user. It provides query language interface, forms and command interfaces so that users can easily access with database.
Objectives of DBMS
Mass Storage
DBMS can store a lot of data in it. So for all the big firms, DBMS is really ideal technology to use. It can store thousands of records in it and one can fetch all that data whenever it is needed.
Removes Duplicity
If you have lots of data then data duplicity will occur for sure at any instance. DBMS guarantee it that there will be no data duplicity among all the records. While storing new records, DBMS makes sure that same data was not inserted before.
Multiple Users Access
No one handles the whole database alone. There are lots of users who are able to access database. So this situation may happen that two or more users are accessing database. They can change whatever they want, at that time DBMS makes it sure that they can work concurrently.
Data Protection
Information such as bank details, employee’s salary details and sale purchase details should always be kept secured. Also all the companies need their data secured from unauthorized use. DBMS gives a master level security to their data. No one can alter or modify the information without the privilege of using that data.
Data Back up and recovery
Sometimes database failure occurs so there is no option like one can say that all the data has been lost. There should be a backup of database so that on database failure it can be recovered. DBMS has the ability to backup and recover all the data in database.
Integrity
Integrity means your data is authentic and consistent. DBMS has various validity checks that make your data completely accurate and consistence.
Disadvantages of Database Management System (DBMS):
Although there are many advantages but the DBMS may also have some minor disadvantages. These are:
1. Cost of Hardware & 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, DBMS software is also Very costly.
2. Cost of Data Conversion:
When a computer file-based system is replaced with a database system, the data stored into data file must be converted to database files. It is difficult and time consuming method to convert data of data files into database. You have to hire DBA (or database designer) and system designer along with application programmers; alternatively, you have to take the services of some software houses. So a lot of money has to be paid for developing database and related software.
3. Cost of Staff Training:
Most DBMSs are often complex systems so the training for users to use the DBMS is required. Training is required at all levels, including programming, application development, and database administration. The organization has to pay a lot of amount on the training of staff to run the DBMS.
4. Appointing Technical Staff:
The trained technical persons such as database administrator and application programmers etc are required to handle the DBMS. You have to pay handsome salaries to these persons. Therefore, the system cost increases.
5. Database Failures:
In most of the organizations, 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.
Database Model
A database model is the structure or format of a database, described in a formal language supported by the database management system, in other words, a "database model" is the application of a data model when used. in conjunction with a database management system. A database model is a buyout theory or specification describing how a database is structured and used.
Some common models are:
a. Hierarchical model
b. Network model
c. Relational model
d. Entity relationship
Hierarchical database model
Hierarchical database model is a structure of data organized in a tree-like model using parent / child-like relationships, and there will not be too many relationships. In a hierarchical database, an entity type can be either a parent or a child; Under each individual entity there are more multiple entities. Entity types are related to each other using tree mapping, which is represented by one parent with many child relationships. There are also attributes of a specific data recorded under an entity.
fig: use book
A database model is the structure or format of a database, described in a formal language supported by the database management system, in other words, a "database model" is the application of a data model when used. in conjunction with a database management system. A database model is a buyout theory or specification describing how a database is structured and used.
Some common models are:
a. Hierarchical model
b. Network model
c. Relational model
d. Entity relationship
Hierarchical database model
Hierarchical database model is a structure of data organized in a tree-like model using parent / child-like relationships, and there will not be too many relationships. In a hierarchical database, an entity type can be either a parent or a child; Under each individual entity there are more multiple entities. Entity types are related to each other using tree mapping, which is represented by one parent with many child relationships. There are also attributes of a specific data recorded under an entity.
fig: use book
Network Model
A network database model is a database model that allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches have member information linked to the owner, which is the bottom of the tree. The multiple linkages that allow this information to be made are very flexible to the network database model. In addition, the relationship that the information has in the network database model is defined as many relationships because one owner file can be linked to many member files and vice versa.
fig: use book
A network database model is a database model that allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches have member information linked to the owner, which is the bottom of the tree. The multiple linkages that allow this information to be made are very flexible to the network database model. In addition, the relationship that the information has in the network database model is defined as many relationships because one owner file can be linked to many member files and vice versa.
fig: use book
Relational model
A model in a database system basically defines the structure or organization of data and a set of operations on that data. The Relational Model is a simple model in which the database is represented as a collection of "Relations", where each relation is represented by a two dimensional table. Thus, because of its simplicity it is most commonly used.
fig: use book
A model in a database system basically defines the structure or organization of data and a set of operations on that data. The Relational Model is a simple model in which the database is represented as a collection of "Relations", where each relation is represented by a two dimensional table. Thus, because of its simplicity it is most commonly used.
fig: use book
The ENTITY RELATIONSHIP (ER) MODEL
Entity relationship model is a high-level conceptual data model. It allows us to describe the data associated with a real-world enterprise in terms of objects and their relationships. It is widely used to develop an initial design of a database. It provides a set of useful concepts that make it convenient for a developer to move from a basic set of information to a detailed and precise description of information that can be easily implemented in a database system. It is a collection of entities, relationships and attributes.
fig: use book
Entity relationship model is a high-level conceptual data model. It allows us to describe the data associated with a real-world enterprise in terms of objects and their relationships. It is widely used to develop an initial design of a database. It provides a set of useful concepts that make it convenient for a developer to move from a basic set of information to a detailed and precise description of information that can be easily implemented in a database system. It is a collection of entities, relationships and attributes.
fig: use book
Ø Different between centralize and distributed
database system
Centralized database
system
|
Distributed database
system
|
Simple type
|
Complex type
|
Located on
particular location
|
Located in many
geographical locations.
|
Consists of only one
server
|
Contains servers in
several locations
|
Suitable for small
organizations
|
Suitable for large
organizations
|
Less chance of data
lost
|
More chances of data
hacking, lost
|
Maintenance is easy
and security is high
|
Maintenance is not
easy and security is low
|
Failure of system
makes whole system down
|
Failure of one
server does not make the whole system down
|
There is no feature
of load balancing
|
There is feature of
load balancing
|
Data traffic rate is
high
|
Data traffic rate is
low
|
Cost of centralized
database system is low
|
Cost of distributed
database system is high
|
Ø DBA and responsibilities of DBA (Database Administrator)
DBA is the most
responsible person in an organization with sound knowledge of DBMS. He/she is
the overall administrator of the system. He/she has the maximum amount of
privileges (permission to access the database) for accessing the database,
settings up system and defining the role of the employees which use the system.
Responsibilities of DBA:
- DBA defines data security,
schemas, forms, reports, relationships and user privileges.
- DBA has responsibility to
install. Monitor and upgrade database server.
- DBA provides different
facilities for data retrieving and making reports as required.
- DBA has responsibility to
maintain database security, backup-recovery strategy, and documentation of
data recovery.
- DBA supervises all the
activities in the system: addition, modification and deletion data from
the database.
Ø What do you mean by data integrity? Explain its
types.
Data integrity is the overall completeness, accuracy and
consistency of data. This can be indicated by the absence of alteration between
two instances or between two updates of a data record, meaning data is intact
and unchanged. Data integrity is usually imposed during the database design
phase through the use of standard procedures and rules.
Different types of data
integrity
Mainly there are three
types of data integrity which are:
- Domain integrity constraints:
it defines a set range of data values for given specific data field. And
also determines whether null values are allowed or not in the data field.
- Entity integrity constraints:
it specify that all rows in a table have a unique identifier, known as the
primary key value and it never be null i.e. blank.
- Referential integrity
constrains: it exists in a relationship between the two tables in a
database. It ensures that the relationship between the primary keys in the
master table and foreign key in child table are always maintained.
v What
is normalization? Why is it needed? Explain normalization process with examples.
Ans:
Normalization is the process of breaking down a complex and large table or
relation into smaller multiple tables. It is used to reduce repetition of data,
identify dependencies between the data and make the database more flexible and
easier to use.
Normalization
is needed to:
a. Reduce data redundancy and wastage of storage.
b. Remove inconsistency in the database.
c. Simplify the structure of tables and make the
database operation easier.
d. Avoid loss of data from the database.
e. Improve the performance of the system.
Normalization process use different normal
forms. Some of the normal forms are:
i.
First normal form ii. Second normal form and
iii. Third normal form
Each normal form defines particular rule. A
table or a relation will be in a particular normal form if it satisfies the
prescribed rule for that normal form.
i. First normal form
A
table or a relation is said to be in 1NF, if it is atomic. i.e. it should not
contain any repeating group in any row or column of the table. Each cell of the
table should contain only one value.
Unmoral form table
STID
|
STNAME
|
STCLASS
|
STSEC
|
STADD
|
STTELNO
|
SUBJECTS
|
MARKS
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
ENGLISH
MATH
COMPUTER
|
50
70
75
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
ENGLISH
NEPALI
ACCOUNT
|
60
65
70
|
First normal form table
STID
|
STNAME
|
STCLASS
|
STSEC
|
STADD
|
STTELNO
|
SUBJECT
|
MARKS
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
ENGLISH
|
50
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
MATH
|
70
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
COMPUTER
|
75
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
ENGLISH
|
60
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
NEPALI
|
65
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
ACCOUNT
|
70
|
ii.
Second normal form
A table or a relation is said to be in 2NF, if it is in 1NF and each non
key attribute is functionally dependent on the entire primary key. The purpose
of 2NF is to eliminate partial key dependencies. To convert the table in 1NF to
2NF, the non key attributes which are not functionally dependent on the primary
key are decomposed into separate table.
Table name: ST info
STID
|
STNAME
|
STCLASS
|
STSECTION
|
STADD
|
STTELENO
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
Table name: St Marks
STID
|
SUBJECTS
|
MARKS
|
1001
|
ENGLISH
|
50
|
1001
|
MATHS
|
70
|
1001
|
COMPUTER
|
75
|
2031
|
ENGLISH
|
60
|
2031
|
NEPALI
|
65
|
2031
|
ACCOUNT
|
70
|
iii. Third normal form
A table of a relation is said to be in 3NF, if it is in 2NF and
transitive dependencies doesn’t exist.
Every non key attribute should not be transitively dependent on the primary
key. To convert the table in 2NF into 3NF, the non key attributes which are
transitively dependent on the primary key must be decomposed into separate
table.
Table name: ST info
STID
|
STNAME
|
STCLASS
|
STSECTION
|
STADD
|
STTELENO
|
1001
|
RAM
|
XI
|
A
|
PATHARI
|
9843098343
|
2031
|
GITA
|
XII
|
B
|
URLABARI
|
9842345633
|
Table name: St Marks
STID
|
CLASS
|
SUBJECTS
|
1001
|
XI
|
ENGLISH
|
1001
|
XI
|
MATHS
|
1001
|
XI
|
COMPUTER
|
2031
|
XII
|
ENGLISH
|
2031
|
XII
|
NEPALI
|
2031
|
XII
|
ACCOUNT
|
TABLE NAME: ST MARKS
STID
|
CLASS
|
MARKS
|
1001
|
XI
|
50
|
1001
|
XI
|
70
|
1001
|
XI
|
75
|
2031
|
XII
|
60
|
2031
|
XII
|
65
|
2031
|
XII
|
70
|
What do you mean by data Security? Explain.
- Data security is protection of data in database system against unauthorized access, modification, failure, losses or destruction. There are different causes of database physical destruction such as fire, water, heat, dust, power failure, theft, etc. In order to protect from such physical damages or accidental loss, database is stored in different location and followed different security measures. They are as:
• Making physical prevention by using stabilizer and UPS to supply a regular power through which we can prevent hardware and software from high electricity voltage and irregular supply.
• Using strong password protection system, making log in the system to prevent data from unauthorized access to the database.
• By keeping the system under lock or safe place.
Database Administrator (DBA)
DBA is the most responsible person in an organization with sound knowledge of DBMS. He/she is the overall administrator of the system. He/she has the maximum amount of privileges (permission to access the database) for accessing the database, settings up system and defining the role of the employees which uses in the system.
Responsibilities of DBA:
a. DBA defines data security, schemas, forms, reports, relationships and user privileges.
b. DBA has responsibility to install. Monitor and upgrade database server.
c. DBA provides different facilities for data retrieving and making reports as required.
d. DBA has responsibility to maintain database security, backup-recovery strategy, and documentation of data recovery.
e. DBA supervises all the activities in the system: addition, modification and deletion data from the database.
Qualities of DBA
• DBA must have sound knowledge about database system.
• DBA should have depth knowledge of OS in which database server is running.
• DBA should have sound knowledge of network architecture and database design.
• DBA must be familiar with the database server and the modern database package.
Types of Database language
A DBMS must provide appropriate languages and interfaces for each category of users to express database queries and updates. Database Languages are used to create and maintain database on computer. There are large numbers of database languages like Oracle, MySQL, MS Access, dBase, FoxPro etc. SQL statements commonly used in Oracle and MS Access can be categorized as data definition language (DDL), data control language (DCL) and data manipulation language (DML).
Data Definition Language (DDL)
It is a language that allows the users to define data and their relationship to other types of data. It is mainly used to create files, databases, data dictionary and tables within databases.
Data Manipulation Language (DML)
It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. The part of DML that involves data retrieval is called a query language.
Data Control Language (DCL)
DCL statements control access to data and the database using statements such as GRANT and REVOKE. A privilege can either be granted to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command.
Define metadata and data dictionary
A metadata (also called the data dictionary) is the data about the data. It is the self describing nature of the database that provides program-data independence. It is also called as the System Catalog. It holds the following information about each data element in the databases.
The data dictionary contains the bookkeeping information about the database so that it can manage the data. It does not contain the information of the actual data of the database. Without the presence of a data dictionary, a database management system cannot access the data from the database. Database administrators handle the data dictionary, and users don’t interact with it.
