यहाँ उल्लेखित ट्याब मेनुहरुमा क्लिक गरी हेर्नुहोस् । Click on the tab menus mentioned here.

NEB Important Questions for Computer Science XII

Unit-1- Database Management System – 8 Marks

1. What is Database and DBMS? List out the advantages and disadvantages of DBMS.
2. Explain the different models of DBMS with advantages and disadvantages.
3. Write differentiate between centralized and distributed database system.
4. Who is DBA? What are the major responsibilities of DBA?
5. What is normalization? Explain normalization process with examples.
6. Define the following terms. a) Data Dictionary  b) Primary Key           c) Relationship         d) DML         
7. e) SQL f) Data Integrity g) DDL                        h) Data Security  
8. i) Database System

Unit 1 – Database Management System (DBMS)

Data:

Data is defined as the raw facts and figures. It could be any numbers, pictures, sound, alphabets or any combination of it. Which do not provide clear meaning. Examples, 101, Hari, Bharatpur etc.

1. Primary Data: Facts and figures newly collected. Examples, observation data, questionnaire data, surveys data etc.
2. Secondary data: Facts and figures already collected. Examples, Financial statements, customer list, sales report, census report etc.

Data Processing:

Data processing is the mechanism of converting unprocessed data into meaningful result or information.

Information:

When data are processed using a database program or software, they are converted to the meaningful result, called information. Information provides answers to “who”, “what”, “where”, and “when” questions. Examples, Hari lives in Bharatpur-11, Chitwan.

Data Processing

Difference between Data and Information

Flat File/ File based system:

• It is traditional way to keeping records of any organization in a manual filing system. It means to used to keep records in file based or flat file system non computerized.
• A flat file system is a system of flies in which every file in the system must have a different name.

Limitation of file based/ Flat file system

1. Duplication of data ( Data Redundancy)
2. Inconsistent data.

• Program Data dependence.

1. Poor data control.
2. Limited data sharing.
3. Security problems.

• Incompatible file formats.
• Fixed queries

Database System:

A database system consists of a collection of interrelated data and a set of application programs to access, update and manage the data.

Database:

It is organized form of record about some person, organization or something store under certain media.

It is a collection of related information about a subject organized in a useful manner that provides a base or foundation for procedure, such as retrieving information, drawing conclusion and make decision.

Advantage of database over flat file or file based system

1. Reduction of data redundancies
2. Shared data
3. Data independent
4. Improved integrity
5. Efficient data access
6. Multiple user interface
7. Improved security
8. Improved backup and recovery
9. Supports for concurrent transactions
10. Unforeseen queries can be answered

File based system Vs Electronic Database System

DBMS:

Database Management System is software that manages the data stored in a database. This is a collection of software which is used to store data, records, process them and obtain desired information. Since, data are very important to the end users, we must have a good way of managing data.

A DBMS is a collection of programs that manages the database structure and controls access to the data stored in the database. The DBMS make it possible to share the data in the database among multiple applications or users. The DBMS stands between the database and the user.

Examples: MS-Access, Oracle, FoxPro, dBase, SQL server, MySQL, Delphi, Sybase, etc.

Why to Use DBMS?

1. To develop software application in less time.
2. Data independence and efficient use of data.
3. For uniform data administration.
4. For data integrity and security.
5. For concurrent access to data, and data recovery from crashes.
6. To use user friendly declarative query language.

Some major database System activities are (Functions of DBMS)

1. Adding new file to the database
2. Inserting data into the database
3. Retrieving/viewing data from the database
4. Updating data in existing database file
5. Deleting data from the database file
6. Removing files from the database

Advantages of DBMS (Features /Objectives of DBMS)

1. Sharing data
2. Reduced data redundancy
3. Data backup and recovery
4. Inconsistency avoided
5. Data integrity
6. Data security
7. Data independence
8. Multiple user interfaces
9. Process complex query

Disadvantages of DBMS

1. Expensive
2. Changing Technology
3. Needs Technical Training
4. Backup is needed

Field/ Attribute:

A field is a piece of information about an element. A field is represented by a column. Every field has got a title called the field title.

Record (Tuple):

A record is information about an element such as a person, student, an employee, client, etc. A record can have much information in different heading or titles.

Table:

A table is the arrangements of rows and columns. Each table must have unique name and must be simple. It is the place where data and information are stored.

Objects:

Database Objects are the essential tools of relational database. These database objects helps to store, view, edit and manipulate the data and information stored in database.

It can be used to hold and manipulate the data. Some of the examples of database objects are view, sequence, indexes, form, query report etc.

• Table: Basic unit of storage; composed rows and columns
• View: Logically represents subsets of data from one or more tables
• Sequence: Generates primary key values
• Index: Improves the performance of some queries
• Synonym: Alternative name for an object

Some Basic Terms used in Database

• Schema: A schema is the structure of database which defines name of tables, data fields with data types, relationships and constraints.
• Instance: It defines data values in a record.
• Entity: An entity is a thing or object in the real world that is different from other objects.
• Attribute: Attribute is properties possessed by an entity or relationship.
• Index: It is used to create indexes in database. It helps searching and sorting operation faster and improves the performances of queries.
• Query: It is the object of DBMS which is mainly used to extract and upgrade the necessary records that are present in the database.
• Form: It is object of database which is mainly used for data entry. It is easy to add, modify and delete the records in form.
• Report: Report are the printed output that is created from table or query. We can’t add, modify and delete the records in report.

Keys of DBMS:

Key is a field that uniquely identifies the records, tables or data. Key in a table allows us to establish the relation between multiple tables. Keys are also useful for finding the unique records or combination of records from a large database tables.

• Primary Key: A primary key is one or more columns in a table used to uniquely identify each row in the table. Primary key cannot contain Null value.

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record. A table cannot have more than one primary key.

A primary key’s main features are:

• It must contain a unique value under the field.
• It cannot contain null values.
• Every row must have a primary key value.
• Foreign Key: Foreign keys represent relationships between tables. A foreign key is a column whose values are derived from the primary key of some other table.
• Candidate Key: If a relational schema has more than one key, that is called a candidate key. All the keys which satisfy the condition of primary key can be candidate key. There can be any number of candidate keys that can be used in place of the primary key if required.
• Alternate Key/ Secondary Key: Alternative keys are those candidate keys which are not the primary key. There can be only one primary key for a table. Therefore all the remaining candidate keys are known as alternative.
• Compound Key: It has two or more attributes that allow you to uniquely recognize specific record. It is possible that each column may not be unique by itself within the database.

Table1

                                                                                                                                  Relationship

                                                             Table2

SQL (Structured Query Language):

It is an international standard database query language for accessing and managing data in the database.

Features of SQL

• It is a non-procedural Language.
• It is English like language.
• It can process a single record as well as sets of records at a time.
• It is a data sub-language consisting of three built in language: DDL, DML, DCL etc.
• It insulates the user from the underlying structure and algorithm.
• It has the facilities for defining tables, views, security, integrity, transaction control etc.

1. DDL (Data Definition Language):DDL is used by the database designers and programmers to specify the content and structure of the table. It is used to define the physical characteristics of records. It includes commands that manipulate the structure of objects such as views, tables, and indexes, etc.

» CREATE: Create is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

» DROP: Drop is used to delete objects from the database.

» ALTER: Alter is used to alter the structure of the database.

» TRUNCATE: Truncate is used to remove all records from a table, including all spaces allocated for the records are removed.

» COMMENT: Comment is used to add comments to the data dictionary.

» RENAME: Rename is used to rename an object existing in the database.

2. DML (Data Manipulation Language):DML is related with manipulation of records such as retrieval, sorting, display and deletion of records of data. It helps user to use query and display reports of the table. So it provides technique for processing the database.

» SELECT: It is used to retrieve data from a database.

» INSERT: It is used to insert data into a table.

» UPDATE: it is used to update an existing data in table.

» DELETE: It is used to delete record from table.

3. DCL (Data Control Language): DCL provides additional features for security of table or database. It includes commands for controlling data and access to the database. Examples of these commands are commit, Rollback, Grant

• GRAND: It gives user’s access privileges to database.
• REVOKE: It is used withdraw users’ access privileges given by using the GRANT command.

Database Model:

A Database model defines the logical design and structure of a database and are used to show how data will be stored, accessed and updated in a Database Management System. It refers to the layout of a database and helps in designing a database. The various types of database models are

Different database models

1. Hierarchical database model: this is one of the oldest types of database models. In this model data is represented in the form of records. Each record has multiple fields. All records are arranged in database as tree like structure. The relationship between the records is called parent child relationship in which any child record relates to only a single parent type record.

Advantages

• It is the easiest model.
• Searching is fast and easy if parent is known.
• It is very efficient in handling one to one and one to many relationships.

Disadvantages

• It is old and outdated database model.
• It does not support many to many relationships.
• It increases redundancy because same data is to be repeated in different places.

1. Network database model: It replaced hierarchical network database model due to some limitations on the model. Suppose, if an employee relates to two departments, then the hierarchical database model cannot able to arrange records in proper place. So network, database model was emerged to arranged non-hierarchical database. The structure of database is more like graph rather than tree structure. A network database model is a database model that allows multiple records to be linked the same owner file. The network model allows each child to have multiple parents.

Advantages

• It accepts many to many relationships, so it is more flexible.
• The searching is faster because of multidirectional pointer.
• The network model is simple and easy to design.
• It reduces the redundancy.

Disadvantages

• It is difficult to handle the relationship in complex programs.
• There is less security because of sharing data.
• It increases the processing overhead due to the complex relationship.

1. Relational database model: in this model, the data is organized into tables which contain multiple rows and columns. These tables are called relations. A row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, it is generally referred to the mathematical term relation, from which the relational database model derives its name.

We notice from below table, here each student has a unique roll number and has marks of subject. Here Roll makes relation between these two tables.

Advantages

• The breaking of complex database table into simple database table becomes possible.
• Database processing is faster than other model.
• There is very less redundancy.
• The integrity rules can easily be implemented.

Disadvantages

• It is more complex than other models.
• There are too many rules because of complex relationships.
• It needs more powerful computers and data storage devices.

4. Object oriented database model: In the object-oriented model, both data and their relationships are contained in a single structure known as an object. An Object-Oriented Model reflects a very different way to define and use entities. An object includes information about relationships between the facts within the object, as well as information about its relationships with other objects. An objects include data, various types of relationships, and operational procedures, the object becomes self-contained, thus making the object-at least potentially-a basic building block for autonomous structures.

Advantages

• Semantic content is added.
• Visual representation includes semantic content.
• Inheritance promotes data integrity.

Disadvantages

• Slow development of standards caused vendors to supply their own enhancements, thus eliminating a widely accepted standard.
• It is a complex navigational system.
• There is a steep learning curve.
• High system overhead slows transactions.

5.     Entity Relationship Diagram:

The diagrammatic representation of entities attributes and relationship is called E-R diagram. The E-R diagram is an overall logical structure of a database that can be expressed graphically. It was developed to facilitate database design. It is graphical representation of database.

Components of E-R Diagram

Entity                         Attribute                     Link                  Relationship

• Entity: An entity is defined as anything about which data to be collected and stored.
• Relationships: Relationships describes associations among data. Most relationships describes associations between two entities.
• Attribute: Attribute describes particular characteristics of the entity.

Fig. E-R diagram for relation between college and principal

Relationship and its types:

A relationship is an association among several entities and represents meaningful dependencies between them. It is represented by diamond. There are 3 types of relationship:

1. One to one
2. One to many

• Many to many

1. One to one: if one record of an entity is related with only one record of another entity then such type of relationship is called one to one relationship.
   • College————-Principal
   • Bank —————-Manager
   • Driver—————-Car

2. One to many: If one instance of one entity is related with many instances of other entity then it is called the one to many relationship.

• College——————students
• Bank ——————— Employers

3. Many to many: If many instances of the one entity are related with many instances of another entity then it is called many to many relationship.

• Teachers —————–students
• Books ——————–Readers
• Employers ————–Customers

Concept of Normalization

Normalization is a database design process in which complex database table is broken down into simple separate tables. It makes data model more flexible and easier to maintain.

• Database Normalization is a technique of organizing the data in the database. It is a systematic approach of decomposing the tables to eliminate data redundancy and inconsistency. The data is said to be redundant if there is duplicate or repeated data in the table.
• Normalization divides the larger table into the smaller table and links them using relationship. It increase clarity in organizing data in the database.

For example:

Below table shown is our database without normalized. Here in table we can see that for the large records of this table, there would be multiple data row of same values especially in the country and city column. So, we can normalize the table by splitting it into two tables where one table only stores the location area of each person name and could be referenced by some unique id. Say Area code.

The above table can be normalized in two tables as below:

Advantages of Normalization

• It reduces the data redundancy.
• It improves faster sorting and index creation.
• It improves the performances of the database system.
• It simplifies the structures of table.
• It avoids the loss of information.

Disadvantages of Normalization

• It is complex to design due to the relationship between tables.
• It requires more CPU cycles.
• It requires large memory.
• It requires more joins to get the result.
• Maintenance overhead.

Types of Normalization

• 1NF (First Normal Form):

A table is said to be in first normal form if it has atomic values. There shouldn’t be any repeating groups of attribute in the table.  First normal form sets the very basic rules for an organized database.

• The data field should be a single (atomic) valued attribute/ columns.
• It eliminates duplicates rows and columns from the same table.
• It minimizes the data redundancy in the database table.

• 2NF (Second Normal Form):

A table is said to be in 2NF if it is a First normal form and it doesn’t have the partial dependency. Second normal form further addresses the concept of removing duplicate data.

• It should be in the first normal form.
• It should not have partial Dependency.
• It identifies data dependencies.
• Non key attributes are functionally depends on key attribute (primary key).

• 3NF (Third Normal Form):

Third normal form goes one large step further.

• It should be in the second normal form.
• It removes transitive dependencies in a table.
• All non-primary key attribute must dependent on primary key attribute or attribute.

In Details Normalization with Examples

Un-normalized:

A table is said to be un-normalized when there is repetition of data in a table. In un-normalized table records are not atomic. Let’s take an example of un-normalized table.

Un-normalized table:             Table No.1

1. 1NF (First Normal Form):

A table is said to be in first normal form if it has atomic values. There shouldn’t be any repeating groups of attribute in the table. Following are the main rules for table to be in 1NF:

• Table should have single (atomic) valued attributes/columns.
• Values stored in columns should be of same domain.
• Columns name should not be repeated in table.
• The order of column names doesn’t matter.

The table given above in un-normalized data meets the three requirements among four to be in first normal form. In the subject column more than one subject are stored in a single column for two students. But, each column must contain atomic value to be in first normal form. And the problem is solved in the table given below:

Example of 1NF for above table No.1

                                                                    Table No.2

Though, some values are repeated but all columns are atomic for each record /row.

1. 2NF (Second Normal Form):

A table is said to be in 2NF if it is in First normal form and it doesn’t have the partial dependency. i.e. each attributes should functionally depend on primary key. Rules for 2 NF:

• A table should be in first normal form.
• There must not be partial dependency.
• Partial dependency exists when any attribute of a table depends on only one part of a composite primary key (primary key combining more than one field) and not on the complete primary key.
• To remove partial dependency, a table can be divided and attributes creating partial dependency are removed in some other tables.

Situation of Dependency:

Let’s take an example of table student with student_id, name, address and age as its columns.

Here student_id is the primary key which can identify each records uniquely and can be used to fetch any row of data from this table.  

Here we can get name, address and age of the student easily from their student_id. Which means each column can be fetched using primary key. So, all needed is student_id and every other column depends on it or can be fetched using it.

This is called dependency or functional dependency. And this kind of dependency must be in table to be in second normal form.

Situation for partial dependency:

In above table a single filed student_id uniquely identifies the all the records of the table. But in some cases combination of two or more columns or fields makes the primary key. Where more than one field acts as primary key. Lets create a table named subject with fields subject_id and subjectname.

Above we have two tables: student and subject for storing student’s and subject’s information. Now, let’s make a table named Mark storing student’s mark in respective subjects with subject teacher.

Note: the above table is not in 2^nd normal form.

In above table student_id is used to get student’s information where as subject_id is used to get subject name. The combination of student_id and subject_id is the primary in above table. It is because if we want to get mark of student with id 15 then we cannot get because we don’t know which subject. Here we have to give sudent_id and subject_id to uniquely identify any row.

Is there a partial dependency in above table? Obviously, yes. In the given table Mark column name teacher is only dependent on the subject, for math there is Bishnu  for science Umesh and so on. But the primary key is the combination of student_id and subject_id, teacher’s name depend only on subject, i.e. subject_id not on the student id. 

This situation is known as partial dependency, where an attribute/ column in table depends on only a part of primary key not on the whole key.

Removing the partial dependency:

Above table can be normalized in second normal form by removing teacher’s name from the Mark table adding it to Subject table.

Subject:

Mark:

Summary:

• For table to be in second normal form, it should be in first normal form.
• Partial dependency exists, when non primary key attribute depends only on a part of primary key instead of complete primary key.
• Partial dependency can be removed by breaking a table and removing attributes causing partial dependency.

1. 3NF (Third Normal Form):

A table is said to be in third normal form, if it is second normal form and it doesn’t have any transitive dependency in primary key. The elements that are not dependent on primary key are removed. Transitive dependency occurs in table when a non-primary key attribute depends upon another non primary key attribute. All non-primary key attribute must dependent on primary key attribute or attributes.

Transitive Dependency?

Transitive Dependency occurs when a non-primary key attribute depends upon another non primary key attribute instead of primary key attribute or primary key.

For instance:

In the above table Mark, lets add some more information such as Exam_name and Full_mark.

In above table, student_id and subject_id are the primary key. The column exam_name depends on both student_id and subject_id.  But, the Full_marks depends on the Exam_name. The first term exam might have 500 full mark but the second term may have 300 or others. Here exam_name is neither primary key nor the part of primary key still, Full_mark depends on it. So, here full_mark which is non-primary key attribute depends on another nonprimary key attribute known as Exam_name. This situation is known as transitive dependency.

Removing Transitive Dependency:

Again, table should be broken into small individual tables to remove it. So we need to remove those fields which are creating transitive dependency. Which looks like.

Score Table:

Exam Table:

Benefits of removing transitive dependency:

• Amount of data duplication is reduced.
• Data integrity is achieved.

Note: Normalization does not eliminate data redundancy. Instead, it reduces the redundancy.

Example of normalization:

Un-normalized Table:

First Normal Form:

Second Normal Form:

Lets take a table employee having more than one department.

Here the non-primary key attribute salary dependent on the employee id only. Here Employee id and department are the candidate key. This violates the rule that “no non primary attribute is dependent on the part of primary key or on the subset of candidate key.”

To make table in 2NF we can break it as:

Third Normal Form:

Here, Employee Id is the primary key and all other are non-primary key attributes. The non- primary key attribute HoD is dependent on non-primary key attribute Department. Here, transitive dependency occurs. To remove it we can decompose table as:

Centralized database system Vs. Distributed database system:

Centralized database system:

• The database system where data and information are stored in the centralized server or centralized database system.
• The data stored in database are accessed from different locations through several applications. The information (data) is stored at a centralized location and the users from different locations can access this data.
• This type of database contains application procedures that help the users to access the data even from a remote location.

Distributed database system:

• Distributed database doesn’t store all data and information in the single but store on various sites or places, which are connected by the help of communication, links which helps them to access the distributed data easily.
• In distributed database various portions of a database are stored in multiple different locations along with the application procedures which are replicated and distributed among various points in a network.

Advantages:

• It decreases risk of data manipulation. i.e. manipulation of data will not affect the core data.
• Data consistency is maintained as it manages data in a central repository.
• It provides better data quality, which enables organizations to establish data standards.
• It is less costly as fewer vendors are required to handle the data sets.

Disadvantages:

• The size of centralized database is large which increases the response time of fetching data.
• It is difficult to update the centralized database.
• If sever gets damaged entire data will be lost.

Advantages:

• The system can be expanded by including new computers and connecting them to the distributed system.
• Distributed database is more reliable than centralized database.
• The performance and service are better.
• Large numbers of users are supported.
• One server failure will not affect the entire data set.

Disadvantages:

• It is difficult to administrate and manage the database
• It is expensive to set up.
• This database has high risk of hacking and data theft.

Different between centralize and distributed database system

Data dictionary:

A data dictionary is a file which contains meta-data that is data about data. It also called information system catalogue. It keeps all the data information about the database system such as location, size of the database, tables, records, fields, user information, recovery system, etc.

Data integrity:

Data integrity referees to the validity or consistency of data in database. It ensures that the data should be accurate and consistent.

Mainly there are 3 types of data integrity constraints used in the database system. They are as:

1. 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.
2. 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.
3. 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.

Data Security:

Data security is protection of data in database system against unauthorized access, modification, failure, losses or destruction. The authorized access means only right people can get the right access to the right data.

The SQL statement for creating, dropping, and altering database and table

XAMPP provides a GUI environment to perform any operations on the database. However, it also provides an option to use SQL statements to perform any operations SQL statements are used in the SQL menu in phpMyAdmin. The SQL statements used in XAMPP also work well with most of the databases.

Creating a database:

Syntax: CREATE DATABASE databasename;

Example: CREATE DATABASE School;

Dropping the database (deleting the database):

Syntax DROP DATABASE databasename:

Example: DROP DATABASE School:

Creating a table:

Syntax CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype….)

Example: CREATE TABLE Students (StudentID int, FName varchar(255), LName varchar(255), Address varchar(255), Class varchar(255));

Altering table adding, deleting, or modifying columns in an existing table

Adding column Syntax:

Synatx: ALTER TABLE table name

ADD column_name datatype

Example:         ALTER TABLE Students ADD Email varchar(255));

Deleting column

 Syntax:

ALTER TABLE table name

DROP COLUMN column_name;

Example: ALTER TABLE Students

DROP COLUMN Email

Modifying column (changing the data type of a column in a table)

Sytnax:

ALTER TABLE table name MODIFY COLUMN column_name datatype:

Example: ALTER TABLE Students

MODIFY COLUMN Class int

Deleting table

DROP TABLE table_name,

SQL statement to insert, select, update and delete data

Syntax Example: DROP TABLE Students:

Inserting data

Syntax

INSERT INTO table name (column1, column2 column3,…) Example: INSERT INTO Students (StudentID, FName, LName, Address, Class)

VALUES (valuel, value2, value3.);

VALUES (‘101’, ‘Ram’, Sharma’, Pokhara’, 7);

Selecting data iselecting data from a database);

Syntax SELECT column1, FROM table name column2.

Example: SELECT * FROM Students; (This will select all the columns from the table Students] SELECT FNAME. LNAME FROM Students: [This will select only the First Name and Last

Name from the table Students.]

Selecting data using conditions

Syntax SELECT column1, column2….

FROM table_name

WHERE condition;

Web References:

• https://www.javatpoint.com
• https://www.w3schools.com
• https://www.tutorialspoint.com
• https://www.google.com
• https://www.wikipedia.org