CBSE Class 10 Computer Science Chapter 3 Database Management System Notes

Database Concept

A database is a collection of logically related information/data, which is available for one or more users organised in a way, so that it can be easily accessed, managed and updated. It is actually a place, where related piece of information is stored and various operations can be performed on it by the user. A database is basically a computer based record/data/information keeping system. Data is raw, unorganised facts and entities relevant to the user need to be processed such as a digital representation of text, numbers, graphical images or sound. The data are stored in such a way that, they are independent of the programs used by the people for accessing the data.
e.g. Consider the names, telephone numbers and addresses of the relatives, etc. You may have recorded this data in an indexed address book or you may have stored it on a hard drive, using application software such as Microsoft Access, OpenOffice.org BASE etc.

Database can be created with the help of following structures

Character → Field → Record → File → Database
                 Database Structure

Need for a Database

The need for a database arose in the early 1960s in response to the traditional file processing system. In the file processing system, the data is stored in the form of files and a number of application programs are written by programmers to add, modify, delete and retrieve data to and from appropriate files.
However, the file processing system has a number of problems, which are as follows
- Some information may be duplicate in several files.
- The file processing system lacks the insulation between program and data.
- Handling new queries is difficult, since it requires change in the existing application programs or requires a new application program.
- Unable to maintain data standards and does not provide data sharing.
- In this system, all the integrity rules need to be explicitly programmed in all application programs, which are using that particular data item.
- This system also lacks security features.

To overcome these problems, database system was designed.

Components of a Database

A database consists of several components. Each component plays an important role in the database system environment.
The major components of database are as follows Data It is raw numbers, characters or facts represented by value. Most of the organisations generate, store and process large amount of data. The data acts as a bridge between the hardware and the software. Data may be of different types such as User data, Metadata and Application Metadata.

Software
It is a set of programs that lies between the stored data and the users of database. It is used to control and manage the overall computerised database. It uses different types of software such as MySQL, Oracle, etc.

Hardware
It is the physical aspect of computer, telecommunication and database, which consists of the secondary storage devices such as magnetic discs, optical discs, etc., on which data is stored.

Users
It is the person, who needs information from the database to carry out its primary business responsibilities. The various types of users which can access the database system are as follows
(i) Database Administrator (DBA) A person, who is responsible for managing or establishing policies for the maintenance and handling the overall database management system is called DBA.
(ii) Application Programmer A person, who writes application programs in programming languages to interact and manipulate the database are called application programmer.
(iii) End-user A person, who interacts with the database system to perform different operations on the database like inserting, deleting, etc., through menus or forms is called end-user.

Features of a Database

Features of a database to let you manage your data are as follows Tables
It is the building block of any relational database model, where all the actual data is defined and entered. A database consists of many tables. Tables (relations) consist of cells at the intersection of records (rows) and fields (columns). Different types of operations are done on the tables such as sorting, filtering, retrieving and editing of data. It is also known as a file.

(i) Fields or Columns (Data item) It is an area (within the record), reserved for a specific piece of data. It is the individual sub-component of one record. It contains set of characters. e.g. Customer number, customer name, street address, city, state, phone number, current address, date of birth, etc. Field in a table is also known as column or attribute.
(ii) Records or Rows or Tuples It is the collection of data items of all the fields (information) pertaining to one entity or a complete unit of information, i.e. a person, company, transition, etc. Record of a table is also known as row, entity or tuple.

Queries
It is an inquiry into the database using the SELECT statement. These statements give you filtered data according to your conditions and specifications indicating the fields, records and summaries which a user wants to fetch from a database.
It allows you to extract information from the database based on the conditions that you define in query. MS-Access 2007 supports the database object query.

Forms
In a database, a form is a window or a screen that contains numerous fields or spaces to enter data. Forms can be used to view and edit your data. It is an interface in user specified layout.
e.g. A user can create a data entry form that looks exactly like a paper form. People generally prefer to enter data into a well-designed form, rather than a table.

Reports
When you want to print those records which are fetched from your database, design a report. It is an effective way to present data in a printed format. It allows you to represent data retrieved from one or more tables, so that it can be analysed.

Database Server
The term database server may refer to both hardware and software used to run a database, according to the context. As software, a database server is the back end portion of a database application, following the traditional client server model.

Database Management System (DBMS)

It is a collection of programs that enables users to create, maintain database and control all the access to the database.It is a computer based record keeping system.
The primary goal of the DBMS is to provide an environment that is convenient and efficient for user to retrieve and store information. It acts as an interface between the application program and the data stored in the database.

DBMS is a software package that manages database. e.g. MySQL, INGRES, MS-Access, etc.
DBMS is actually a tool that is used to perform any kind of operation on data in database. It also maintains data consistency in case of multiple users. The purpose of a DBMS is to bridge the gap between information and data. Some basic processes that are supported by a DBMS are as follows

- Specification of data types, structures and constraints to be considered in an application.
- Storing the data itself into persistent storage.
- Manipulation of the database.
- Querying the database to retrieve desired information.
- Updating the content of the database.

A short list of database applications would include:
- Inventory - Payroll
- Membership - Orders
- Shipping - Reservation
- Invoicing - Accounting
- Security - Catalogues
- Mailing - Medical records

Relational Database Management System (RDBMS)

RDBMS is a type of DBMS that stores data in the form of relations (tables). Relational databases are powerful, so they require few assumptions about how data is related or how it will be extracted from the databases.
An important feature of relational database system is that a single database can be spread across several tables. Base, Oracle, DB2, SAP, Sybase, ASE, Informix, Access, etc., are the examples of RDBMS.

Working of a Database

Database is created to operate large quantities of information by input, store, retrieve and manage the information. It is a centralised location which provides an easy way to access the data by several users. It does not keep the separate copies of a particular data file still a number of users can access the same data at the same time.

As the diagram shows, DBMS works as an interface between the user and the centralised database. First, a request or a query is forwarded to a DBMS which works (i.e. a searching process is started on the centralised database) on the received query with the available data and if the result is obtained, it is forwarded to the user.
If the output does not completely fulfill the requirements of the user, then a rollback (again search) is done and again search process is performed until the desired output is obtained.

Advantages of a Database/DBMS

The centralised nature of database system provides several advantages, which overcome the limitations of the conventional file processing system. These advantages are as follows

(i) Reduce Data Redundancy Redundancy means ‘duplication of data’. This eliminates the replication of data item in different files, extra processing required to face the data item from a large database. This also ensures data consistency and saves the storage space.
(ii) Enforcing Data Integrity It means that, the data contained in the database is accurate and consistent. Integrity constraints or consistency rules can be applied to database, so that the correct data can be entered into the database.
(iii) Data Sharing The data stored in the database can be shared among multiple users or application programs.
(iv) Data Security The DBMS ensures that the access of database is done only through an authorised user.
(v) Ease of Application Development The application programmer needs to develop the application programs according to the user’s needs.
(vi) Backup and Recovery The DBMS provides backup and recovery sub-system that is responsible to recover data from hardware and software failures.
(vii) Multiple Views of Data A view may be the subset of database. Various users may have different views of the database itself.
(viii) Enforced Standards It can ensure that, all the data follow the applicable standards.
(ix) Data Independence System data descriptions are independent from the application programs.

Disadvantages of a Database/DBMS

There are many advantages of database, but database also have some minor disadvantages. 

These disadvantages are as follows
(i) Cost of Hardware and Software Through the use of a database system, new costs are generated due to additional hardware and software requirements.
(ii) Complexity A database system creates additional complexity and requirements.
(iii) Database Failures If database is corrupted due to power failure or it is corrupted on the storage media, then our valuable data may be lost or the system will stop working.
(iv) Lower Efficiency A database system is a multi-user software, which is less efficient.

- Data sharing refers to the process of sharing single piece of data among different users.
- Multiple mismatching copies of the same data is known as data inconsistency. If a field value is stored in two places in the database, then storage space is wasted and changing the data in one place will not cause data inconsistency.
- Data redundancy leads to data inconsistency

Data Integrity

Data Integrity ensures the accuracy, reliability and consistency of the data during any operation.

Each type of data integrity are as follows
(i) Entity Integrity It defines the primary key of a table.Entity integrity rule on a column does not allow duplicate and null values.
(ii) Domain Integrity It defines the type, range and format of data allowed in a column. Domain integrity states that all values in a column must be of same type.
(iii) Referential Integrity It defines the foreign key concepts. Referential integrity ensures that data in related tables remains accurate and consistent before and after changes.
(iv) User Defined Integrity If there is some business requirements which do not fit any above data integrity then user can create own integrity, which is called user defined integrity.

Key Fields

The key is defined as the column or the set of columns of the database table which is used to identify each record uniquely in a relation. If a table has id, name and address as the column names, then each one is known as the key for that table. The key field is a unique identifier for each record. e.g. In Student table, you could use a combination of the LastName and FirstName (or perhaps LastName, FirstName to ensure you to identify each student uniquely) as a key field.

Types of Key Fields

The following are the types of key fields available in the DBMS system

Primary Key

A field or a set of fields that uniquely identify each record in a table is known as a primary key. Each relation has atleast one column for which each row that must have a unique value. Only one column attribute can be defined as a primary key for each table.

A primary key must possess the following properties
- It does not allow null values.
- It has a unique index.
- It allows numbers and text both.
e.g. In the student’s table, StudentId works as a primary key because it contains Ids which are unique for each student.
Note Data cannot be primary key.

Candidate Key

The set of all attributes which can uniquely identify each tuple of a relation are known as candidate keys. Each table may have one or more candidate keys and one of them will become the primary key. The candidate key of a relation is always a minimal key. e.g. Column StudentId and the combination of FirstName and LastName work as the candidate keys for the student table.
A candidate key must possess the following properties
- For each row, the value of the key must uniquely identify that row.
- No attribute in the key can be discarded without destroying the property of unique identification.

Alternate Key

From the set of candidate keys after selecting one of the keys as a primary key, all other remaining keys are known as alternate keys.
e.g. From the candidate keys (StudentId, combination of FirstName and LastName), if StudentId is chosen as a primary key, then the combination of FirstName and LastName columns work as alternate keys.

Foreign Key

A field of a table (relation) that references the primary key of another table is referred to as foreign key. The relationship between two tables is established with the help of foreign key. A table may have multiple foreign keys and each foreign key can have a different referenced table. Foreign keys play an essential role in database design, when tables are broken apart, then foreign keys make it possible for them to be reconstructed.
e.g. CourseId column of student table (reference table) works as a foreign key as well as a primary key for course table (referenced table).

Data Storage

A data type is a data storage format that can contain a specific type or range of values. The fields within a database often require a specific type of data to be input. e.g. A school’s record for a table student may use a character data type for the students first name and last name. The student’s date of admission and date of birth would be stored in a date format, while his or her marks in each subject may be stored as a numeric.
In MS-Access, data types can be categorised into the following types

Data Types 

Text 
Description- Allows to store text or combination of text and numbers, as well as numbers that don’t require calculations such as phone numbers. Also, it is a default data type.

Memo
Description- Allows to store long blocks of text that use text formatting.

Number
Description- Holds numeric values which are used for calculations and zip code. It includes various types such as Byte, Integer, Long Integer, (Single, Double) Replication ID and Decimal.

Date/Time
Description- Allows to store date and time value for the year 100 to 9999.

Currency
Description- Allows to store monetary values that can be used in calculations. Accurate upto 15 digits on LHS and 4 digits on RHS of decimal point.

AutoNumber
Description- Allows to store numbers that are automatically generated for each record. It increases the number automatically when you add records.

Yes/No
Description- Allows boolean value. (i.e. one of two possible values)

OLE Object
Description- OLE is an acronym for Object Linking and Embedding. It can store objects such as a video clip, a picture, word document or any other binary data.

Hyperlink
Description- Allows to store hyperlinks such as E-mail addresses.

Attachment
Description- Allows to store files such as digital photos. Multiple files can be attached per record.

Lookup Wizard…
Description- Lets you type a list of options, which can be chosen from a drop down list.

Field Length (Field Size)

It refers to the maximum number of characters that a field can contain. Each character requires one byte for its storage.

Field length is of two types which are as follows

(i) Fixed Length Field It is a type of field length in which the number of characters you enter in a field is fixed. These are present in Format option in Data Type Formatting group (in Datasheet tab) such as Currency, Euro, Per cent, etc.

(ii) Variable Length Field In this type of field length, the number of characters is not fixed. Actually, the number of characters of the data entered in the field decide the field length.
The field length or field size of each data type are as follows

Data Type    Field Length or Field Size       Data Type Field        Length or Field Size
Text               0-255 characters                        Memo                         0-65,536 characters
Number         1, 2, 4, 8 or 16 bytes                 Date/Time                   8 bytes
Currency        8 bytes                                      AutoNumber               4 or 16 bytes
Yes/No          1 bit (0 or 1)                               OLE Object                 Upto 2 GB
Hyperlink       Each part contains                    Lookup Wizard...         4 bytes
                      upto 2048 characters

Numeric Data Types

It allows the database server to store numbers such as integers and real numbers in a column. e.g. Age of the students, numbers obtained in subjects, etc.

FLOAT (N, D) A small number with floating decimal point. It cannot be unsigned. Its size is 4 bytes. Here, N represents the total number of digits (including decimals) and ‘D’ represents the number of decimals.
DOUBLE (N, D) A large number with floating decimal point. It cannot be unsigned. Its size is 8 bytes.
DECIMAL (N, D) It cannot be unsigned. The maximum number of digits may be specified in the N parameter. The maximum number of digits to the right of the decimal point is specified in the D parameter.

String/Text Data Types

It allows the database server to store string values such as name of the Students, Address, etc.

Date-and-Time-Data-Types

It-allows-the-database-server-to-store-a-date-using-the-fields-YEAR,-MONTH-and-DAY-in-the-format YYYY-MM-DD.-e.g.-Date of-admission,-Date-of-birth,-etc.

Manipulating Data

In a database, structure and manipulation of data are done by some commands. For this, we can use SQL commands. SQL (Structured Query Language) commands are the instructions used to communicate with the database to perform specific task that work with data.
SQL commands can be used not only for searching the database but also to perform various other functions like, create tables, add data to tables, modify data, drop the tables, set permissions for users and many more.

Data Definition Language (DDL)

DDL is used to define the structure of your tables and other objects in the database. In DBMS, it is used to specify a database schema as a set of definitions (expressed in DDL). In SQL, the Data Definition Language allows you to create, alter and destroy database objects. Basically, a data definition language is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemes, tables, indexes, etc. This term is also known as data description language in some contexts, as it describes the fields and records in a database table.

Data definition language consists of various commands that lets you to perform some specified tasks as follows
(i) CREATE Uses to create objects in the database.
(ii) ALTER Uses to alter the structure of the database table. This command can add up additional columns, drop existing columns and even change the data type of columns involved in a database table.
(iii) DROP Uses to delete objects from the database.
(iv) TRUNCATE Uses to remove all records from a table.
(v) RENAME Uses to rename an object.

Data Manipulation Language (DML)

DML provides various commands used to access and manipulate data in existing database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data.
DML is mostly incorporated in SQL database. The basic goal of DML is to provide efficient human interaction with the system.

The DMLs are of two types

Procedural DMLs These require a user to specify what data is needed and how to get it.
Non-Procedural DMLs These require a user to specify what data is needed without specifying how to get it.
Various data manipulation language commands are as follows
(i) SELECT Used to retrieve data from a database.
(ii) INSERT Used to insert data into a table.
(iii) UPDATE Used to update existing data within a table.
(iv) DELETE Used to delete all records from a table, the space of the records remains.
(v) LOCK TABLE Used to control concurrency.
A query language is a portion of a DML involving information retrieval only. The terms DML and query language are often used synonymously.

Differences between DDL and DML
DDL
• DDL is the abbreviation of Data Definition Language.
• It is used to create and modify the structure of database objects in database.
• DDL commands allow us to perform tasks related to data definition.
• For example, CREATE, ALTER and DROP commands.
DML
• DML is the abbreviation of Data Manipulation Language.
• It is used to retrieve, store, modify, delete, insert and update data in database.
• DML commands are used to manipulate data.
• For example, SELECT, UPDATE and INSERT commands.

Transaction Control Language (TCL)

TCL is playing an important role in SQL. TCL commands are used to manage transactions in database. These are also used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. A transaction is a single unit of work.

Each individual statement is a transaction. If a transaction is successful, all of the data modifications made during the transaction is committed and became a permanent part of the database. If a transaction encounters an error and must be cancelled or rolled back, then all of the data modifications are erased. To manage all these operations, transaction control language commands are used.

Various transaction control commands are as follows
(i) COMMIT Used to save the work done.
(ii) SAVEPOINT Used to identify a point in a transaction to which you can later rollback.
(iii) ROLLBACK Used to restore database to original, since the last COMMIT.
(iv) SET TRANSACTION It establishes properties for the current transactions.

Data Control Language (DCL)

DCL commands are used to assign security levels in database which involves multiple user setups. They are used to grant defined role and access privileges to the users.

There are two kinds of user in the schema
(i) Users They work with the data, but cannot change the structure of the schema. They write data manipulation language.
(ii) Admin They can change the structure of the schema and control access to the schema objects.
They write data definition language.
Basically, the DCL component of the SQL language is used to create privileges that allow to users access and manipulation of the database.

Two types of DCL commands are
(i) GRANT Used to give user’s access privileges to database.
(ii) REVOKE Used to withdraw access privileges given with the GRANT command.

Creating a Database Object

To create a new blank database, you need to perform the steps which are as follows
- Start OpenOffice Base, then getting started with its starting page will appear.
(v) If a mistake is made in the order as listed above, click on the field name that is in the wrong order to highlight it. Use the Up or Down arrow on the right side of the Selected Fields list to move the field name to the correct position. Click Next.

- Select create a new database under what do you want to do? and click on Next button

- Save and proceed window will appear. Click on Finish button.
- Now, SaveAs dialog box will appear where you can save your database with appropriate name. Click on Save button.

- Base creates the database with its features as Tables,Queries, Forms and Reports.

Creating Database Tables

To work with tables, click the Tables icon in the Database list. The three tasks that you can perform on a table are in the Task list

Creating Tables in Design View

Design View is a more advanced method for creating a new table. It allows you to directly enter information about each field in the table.
To create a table in design view, follow the given steps
- Open the Database.
- Select Create Table in Design View under Tasks. It will open a table as shown below

Note The field entry area is used for entering field name, field type and description. This description is optional and the field properties pane is used for entering more details for each field, i.e. field size, validation rule etc. The table needs to be opened in Design View to access the field properties.
Steps to add a new field are as follows
Step 1 Click on the first cell in the Field Name column and type the field name.
Step 2 Press Enter. The neighboring cell in the Field Type column is selected. To select the data type, click the drop-down arrow to the right of the Field Type field and select an alternative data type.

Step 3 Press Enter. A cell in the Description column will be selected. Enter a description, if required.
Step 4 Press Enter and repeat the above process for other fields.

Steps to set the field properties are as follows
Step 1 Click once on a Field Name for which you want to set the field property.
Step 2 The Field Properties will appear at the bottom of the screen as shown in the following figure

Make Changes in a Design View
The Design View of a table can be modified by the following changes

Insert a Field
Steps to insert a field in a table are as follows
Step 1 Select the field, before which you want to insert a new field.
Step 2 Right click at the selected field and click at the Insert Rows option from the context menu.
Step 3 A new field is added to the table design thus, you can enter field name, data type and description.

Delete a Field
Steps to delete a field from a table are as follows
Step 1 Select the field that you want to delete.
Step 2 Right click at the selected field and choose the option Delete from context menu.

Rename a Field
You can change a field name by placing the cursor on the field, double click on it and type the new name.

Naming and Saving of a Table
Steps to save a table are as follows
Step 1 To save the table, click the Save button at the top of the screen or click the File button and select Save. The Save As dialog box will appear as shown in following figure

Step 2 Type the name that you want to give to your table in Table Name: text box.
Step 3 Click OK.

Entering/Removing Record into/from a Table

Once a table has been created, the field and its properties are defined, you can start to enter the records. This is done in a Datasheet View. If you create table in Design View, you need to switch to the Datasheet View to enter records.

Insert a Record

Steps to insert a record in a Datasheet View are as follows
Step 1 When you create a table, a new blank record automatically appears in the second row of the table or If you enter data in the last record, a new blank record will automatically appear at the end of the table.
Step 2 Type data into the fields.
Step 3 When you have finished adding records in the datasheet, save it and close it.

Delete an Existing Record

Steps to delete an existing record are as follows
Step 1 Select the row which you want to delete.
Step 2 Right click on the row and select the Delete from context menu.

Using the Wizard to Create a Table

To create a table using wizard, follow the below steps
- Open the database
- Select Use Wizard to Create Table under Tasks. It will open the Table Wizard as shown below

Step 1 : Select fields
You have a choice of two categories of suggested tables: Business and Personal. Each category contains its own suggested tables from which to choose. Each table has a list of available fields. We will use the DVD-Collection Sample table in the Personal category to select the fields we need.

(i) Category Select Personal. The Sample Tables drop down list changes to a list of personal sample tables.
(ii) Sample Tables Select DVD-Collection. The Available fields window changes to a list of available fields for this table.
(iii) Selected Fields Using the > button, move these fields from the Available fields window to the Selected fields window in this order: CollectionID, MovieTitle, Actor, PurchasePrice, Rating, Notes Photo and Director.
(iv) Selected fields from another sample table. Click Business as the Category. Select Employees from the dropdown list of sample tables. Use the > button to move the Photo field from the Available fields window to the Selected fields window. It will be at the bottom of the list directly below the Director field.
(v) If a mistake is made in the order as listed above, click on the field name that is in the wrong order to highlight it. Use the Up or Down arrow on the right side of the Selected Fields list to move the field name to the correct position. Click Next.

Step 2 : Set field types and formats
In this step you give the fields their properties. When you click a field, the information on the right changes. You can then make changes to meet your needs. Click each field, one at a time and make the changes listed below.

CollectionID Change AutoValue from No to Yes.

- MovieTitle Default

(a) Entry required If all of your music is in albums, change Entry required to Yes. Otherwise, leave Entry required as No.
(b) Length Unless you have an album title that exceeds 100 characters in length counting the spaces, do not change the length.
- Actor Use the Default setting. And since music has authors, set Entry Required to Yes.
- PurchasePrice Length: default setting. Entry required should be Yes.
- Rating Only change the Entry Required setting: from No to Yes.
- Notes No changes are required.
- Director Use the default settings.
- Photo Use the default settings.
When you have finished, click Next.
Step 3: Set primary key

- Create a primary key should be checked.
- Select option Use an existing field as a primary key.
- In the Fieldname drop down list, select CollectionID.
- Check Auto value, if it is not already checked.
- Click Next.

Step 4 : Create the table
- If desired, rename the table at this point. If you rename it, make the name meaningful to you.
- Leave the option Insert data immediately checked.
- Click Finish to complete the table wizard. Close the window created by the table wizard.

Creating a Table by Copying an Existing Table

If you have a large collection of music, you might want to create a table for each type of music you have. Rather than creating each table from the wizard, you can make a copy of the original table. Each table can be named according to the type of music contained in it. Possible names could include Classical, Pop, Country and Western, and Rock, among others.
- Click on the Tables icon in the Database pane to see the existing tables.
- Right-click on the DVD-Collection table icon. Select Copy from the context menu.
- Move the mouse pointer below this table, right-click and select Paste from the context menu. The Copy table window opens.
- Change the table name to Pop and click Next.
- Click the >> button to move all the Fields from the left window to the right window and click Next.
- Since all the Fields already have the proper File Type formatting, no changes should be needed. Click Create. The new table is created.

Opening an Existing Table

Existing table can be opened by following steps

Step 1 Find a table in the All Base Objects list (the right hand window).
Step 2 Right click on a table and select Open. This view represents the data in a table.

Renaming and Deleting a Table

Steps to rename a table are as follows
Step 1 Find a table in the All Base Objects list (the left hand window).
Step 2 Right click on a table and select Rename from context menu that appears.
Step 3 Now, type a new name for the table.
Step 4 Press Enter from the keyboard.

Steps to delete a table are as follows
Step 1 Find a table in the All Base Objects list.
Step 2 Right click on a table and select Delete.
Step 3 Now, OpenOffice Base will display the prompt message to confirm that you want to delete the table or not.
Step 4 Click on Yes button to delete a table with its contents.

Building Forms
Forms are used for entering, modifying and viewing records you likely have had to fill out forms on many occasions, like when visiting a doctor’s clinic, applying for a job or registering for school. When you enter information in a form in Base, the data goes exactly where the database designer wants it to go: in one or more related tables.
There are two ways to create forms in Base

Using the Wizard to Create a Form

In the main database window, click the Form icon. Double click Use Wizard to Create Form to open the wizard.
Follow the below steps to create a form using wizard

Step 1 : Field selection
- Under Tables or queries, select DVD-Collection as the table. Available fields lists the fields for the DVD-Collection table.
- Click the right double arrow to move all of these fields to the Fields in the form list. Click Next.

Step 2: Set up a subform
Since, we have not already created a relationship between the Table1 and DVD-Collection tables. If no relationship had been defined, this would be done in step 4.
- Click the box labeled Add Subform.
- Click the radio button labeled Subform based on manual selection of fields.
- Click Next.

Step 3 : Add subform fields
This step is exactly the same as step 1. The only difference is that not all of the fields will be used in the subform.
- Select Table1 under Tables or queries.
- Use the >> button to move all the fields to the right.
- Click the ID field to highlight it.
- Use the < button to move the ID to the left.
- Click Next.

Step 4 : Get joined fields
This step is for tables or queries for which no relationship has been defined. Since we want to list all expenses by the day they occur in both the form and subform, we will join the Date fields of these two tables
- Select CollectionID from the First joined subform field dropdown list. This is not the Primary key for the Table1 table, but it is known as a Foreign key.
- Select CollectionID from the First joined main form field dropdown list. This is the Primary key for the DVD-Collection table. Click Next.

Step 5 : Arrange controls
- Arrangement of the main form: Click Columnar - Labels on top. The labels will be placed above their field.
- Arrangement of the subform: Click As Data Sheet. (The labels are column headings and the field entries are in spreadsheet format.)
Click Next.

Step 6 : Set data entry
Unless you have a need for any of these entries to be checked, accept the default settings. Click Next.

Step 7 : Apply styles
- Select the color you want in the Apply Styles list.
- Select the Field border you want.
- Click Next.

Step 8: Set name
- Enter the name for the form. In this case, it is DVD.
- Click the circle in front of Modify the form if you want to modify the form otherwise select first option.
- Click Next. The form opens in Edit mode, if you select Modify the form.

2. Creating forms in Design View

To create a form using design view, follow below steps
- In the main database window, click the Form icon.Double click Create Form in Design View.
- It will open the base form where you can design your form with the help of form controls which are given in left side of form window.

Create and Manage Queries

Queries are the basis of power in a database. It is a way to get specific information from the database. They give us the ability to ask questions, record the questions for later and to take actions on the answers. There are two methods for creating a query in OpenOffice Base. These are as follows Using the Wizard to Create a Query
In the main database window , click the Queries icon in the Databases section, then in the Tasks section, click Use Wizard to Create Query.
The Query Wizard window opens. Now follow the below steps

Step 1 : Field selection

- Select the DVD-Collection table from the dropdown list of tables.
- Select fields from the DVD-Collection table in the Available fields list.
(a) Click MovieTitle, and use the > button to move it to the Fields in Query list.
(b) Move the Actor and PurchasePrice fields in the same manner.
(c) Use the up arrow to change the order of the fields.
(d) Click Next.
 

Step 2 : Sorting order
Up to four fields can be used to sort the information of our query.

- Click the first Sort by dropdown list.
(a) Click DVD-Collection.Actor to select it.
(b) If you want the actors to be listed in alphabetical order (a-z), select Ascending on the right. If you want the artist listed in reverse order (z-a), select Descending on the right.
- Click the second Sort by dropdown list.
(a) Click DVD-Collection.MovieTitle.
(b) Select Ascending or Descending according to the order you want.
- Repeat this process for DVD-Collection. Purchase Price.
- Click Next.

Step 3 : Search conditions
The search conditions available are listed below. They allow us to compare the name we entered with the names of the actor in our database and decide whether to include a particular actor in our query or not.
- Since we are only searching for one thing, we will use the default setting of Match all of the following.
- We are looking for a particular actor, so select is equal to.
- Enter the name of the actor in the Value box. Click Next.

Step 4 : Detail or summary
We want simple information, so the default setting:
Detailed query is what we want. Click Next.

Step 5 : Aliases
We want the default settings. Click Next.

Step 6 : Overview
- Name the query.
- To the right of this are two choices. Select Display Query.
- Click Finish.

Using the Design View to Create a Query

OpenOffice Base provides you the functionality to create a query by using a Design View.
Steps to create a query through a Design View are as follows
Step 1 In the main database window , click the Queries icon in the Databases section, then in the Tasks section,click Create Query in Design View.
Step 2 The Add Table or Query dialog box will appear as shown in the following figure

Step 3 Select a table in the Tables tab and click the Add button to add a table.
Step 4 The selected table is being displayed as shown in the following figure. You can click the Close button to close the Add Table or query dialog box.

Step 5 Edit the Field row and other rows.
Step 6 Save the query by using shortcut Ctrl+ S key. The SaveAs dialog box will appear, enter the name of your query in Query Name text field and then,click OK button.

MySQL Command Basics

MySQL database is a way of organizing a group of tables and table stores the data in the form of rows and columns. To create a bunch of different tables that share a common theme, you would group them into one database to make the management process easier. So, for manipulating data, we need to know about MySQL commands, which are described below.

CREATE Command

Create a database Creating database is an easier task. You need to just type the name of the database in a CREATE DATABASE command.
Syntax
CREATE DATABASE [IF NOT EXISTS]<database_name>;
CREATE DATABASE command will create an empty database with the specified name and would not contain any table.
IF NOT EXISTS is an optional part of this statement which
prevents you from an error if there exists a database with the
given name in the database catalogue.
For example, mysql>CREATE DATABASE BOOK;
Output Query OK, 1 row affected <0.01 sec>
Tables are created using CREATE TABLE command.
Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
------
);
Here,
CREATE TABLE defines a new table.
table_name defines the name of a table.
column_name defines the name of a column.
data_type specifies that which type of data can be contained in a particular column.
For example,
mysql > CREATE TABLE STUDENT (Student_Code integer,Student_Name char(20), Sex char(1), Grade char(2),Total_Marks decimal);

Output
Query OK, 0 row affected <0.06 sec>
Some rules for creating tables are as follows
- The table and column names must start with a letter and can be followed by letters, numbers, or underscores.
- Table or column names not to exceed a total of 30 characters in length and not use any SQL reserved keywords as names for tables or column names (such as ‘select’, ‘create’, ‘insert’, etc).
- It is important to make sure that you are using an open parenthesis before the beginning of a table definition and a closing parenthesis after the end of the last column definition.
- Separate each column definition with a comma (,).
- All SQL statements should end with a semi-colon (;).

INSERT Command
The INSERT command is used to add a single record or multiple records into a table.
Syntax
INSERT INTO <table_name>(col_1, col_2, col_3,.., col_n)
VALUES(value_1, value_2,... value_n);
Here,
table_name defines the name of a table where data will be inserted.
col_1, col_2, col_3,..., col_n are the columns of the current table.
value_1, value_2,... value_n are the data values against each column.
For example, The following information exists in the table

STUDENT

To add a new row into the STUDENT table use the INSERT command as follow mysql>INSERT INTO STUDENT(Roll_No, Name, Subject)
VALUES(106, ‘Ajay’, ‘Science’);
Now, the table STUDENT will look like as follows

SELECT Command
The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table.
Syntax
SELECT column_list
FROM table_name;
Selecting Specific Columns
To select any specific column or information from the table, we use the following command:
Syntax
SELECT<column_name1>,[<column_name2>,…,<column
_nameN>] FROM <table_name>;
Example To display the column Emp_Code and Emp_Salary from table COMPANY

mysql>SELECT Emp_Code, Emp_Salary FROM COMPANY;
Output

Selecting All Columns
To select all the columns of a table or entire table, we can use an asterisk (*) symbol in place of column_name list.
Syntax
SELECT * FROM <table_name>;
Example To display all the columns of table COMPANY.
mysql>SELECT * FROM COMPANY;

Output
It will display the complete table COMPANY.
UPDATE Command
The UPDATE command is used to update a single record or multiple record in a table. The UPDATE command is used to modify the existing rows in a table.
Syntax
UPDATE<table_name > SET <column1> = <value1>,<column2> = <value2> ,.....
WHERE <condition>;
Example To update Emp_Salary with 28000 of those employees whose Emp_Code is 100.
mysql> UPDATE COMPANY SET Emp_Salary=28000
WHERE Emp_Code=100;
The above query will update the Emp_Salary by 28000
whose Emp_Code is 100.

DELETE Command

To discard unwanted data from a database, the DELETE command is used. The DELETE command uses a WHERE clause.
If you don’t use a WHERE clause, all rows in the table will be deleted.
Syntax
DELETE FROM <table_name> WHERE <condition>;
Example To delete the record of employee Puneet from the table COMPANY.
mysql>DELETE FROM COMPANY
WHERE Emp_Name ='Puneet';
The above query will delete the record of Puneet from table COMPANY
Delete All Rows To delete all rows in a table without deleting the table structure, the following command is used.
Syntax DELETE FROM<table–name>;
Example To delete all rows from table
COMPANY.
mysql>DELETE FROM COMPANY;
The above query will delete all data of table COMPANY.

Design Report

Report offers you the ability to present your data in print.Reports are useful because they allow you to present components of your database in easy to read format.
To create a new report, follow the below steps
- Click the Reports icon in the Database list.
- In the Tasks list, click Use Wizard to Create Report.
The Report Wizard window opens.

Step 1 : Field selection
- Select Table: DVD-Collection in the Tables or Queries dropdown list.
- Use the > to move these fields from the Available fields list to the Fields in report list.
Click Next.

Step 2: Labeling fields
- Click the field label you want to change and make your changes as you would in any text box.
- Click Next.

Step 3 : Grouping
Since we are grouping by the date, use the > button to move the Date field to the Groupings list. Click Next.

Step 4 : Sort options

We do not want to do any additional sorting. Click Next.

Step 5 : Choose layout
We will be using the default settings for the layout. Click Next.

Step 6 : Create report
- Label the report: DVD Report
- Select Static report.
- Click Finish.

Printing a Report

To print a report, follow the given steps
(i) You can print the report by clicking on Print command from File tab or press Ctrl + P from keyboard.
(ii) Print dialog box will appear on the screen.
(iii) Here, you can set your printer and click on Print button.

CBSE Class 10 Computer Science Chapter 3 Database Management System Multiple Choice Questions

Question. Databases have the ability to
(a) store a large amount of data in a structured format, easy update, sort query, production of reports
(b) spell check, perform calculations, library of mathematical functions, replication
(c) rotate images, copy and paste, fill scale
(d) None of the above
Answer. A

Question. Which of the following is not an example of database?
(a) Cross knot game
(b) Employee payroll management
(c) Numeric calculator
(d) Customer management system
Answer. C

Question. Database is a combination of
(a) hardware and software
(b) hardware and operating system
(c) software and operating system
(d) utility programs
Answer. A

Question. Operations performed on a databases are controlled by
(a) user
(b) hardware
(c) DBMS
(d) RDBMS
Answer. A

Question. Which of the following is not a component of a database?
(a) tables
(b) queries
(c) forms
(d) formula bar
Answer. D

Question. All the information about a thing or a person is known as a
(a) database
(b) file
(c) field
(d) record
Answer. D 

Question. Out of the following, which one is the most appropriate data field in context of employee table, if only one of these is required?
(a) Age in years
(b) Data of birth
(c) Age in days
(d) Age in months
Answer. B

Question. Which of the following is not the main building block of a database?
(a) Lists
(b) Queries
(c) Reports
(d) Forms
Answer. A

Question. Which of the following best describes a form?
(a) Form enables people to enter or view data in a database easily.
(b) Form summarises and prints data.
(c) A form filters data from a database based on a criteria
(d) All of the above
Answer. A

Question. DBMS is a program that controls the creation, maintenance and use of database. Here, DBMS referred to
(a) Digital Base Management System
(b) Data Build Management System
(c) Database Management System
(d) Database Management Service
Answer. C

Question. Computer based record keeping system is known as
(a) Data Manipulation System
(b) Computerised Data System
(c) Computerised Record Keeping System
(d) DBMS
Answer. D

Question. RDBMS provides relational operators to manipulate the data. Here, RDBMS refers to
(a) Record Database Management System
(b) Relational Database Management System
(c) Reference Database Management System
(d) None of the above
Answer. B

Question. A database that contains tables linked by common fields is called a
(a) Centralised database
(b) Flat file database
(c) Relational database
(d) None of the above
Answer. C

Question. Duplication of data is known as
(a) data security
(b) data incomplete
(c) data redundancy
(d) None of the above
Answer. C

Question. Key field is a unique identifier for each record. It is defined in the form of
(a) rows
(b) columns
(c) tree
(d) query
Answer. B

Question. Which of the following fields will not make a suitable primary key?
(a) A customer’s account number
(b) A data field
(c) An auto number field
(d) A student’s admission number
Answer. B

Question. When you define a field for a table, which of the following parameters do access always consider optional?
(a) Field Name
(b) Data Type
(c) Field Size
(d) Description
Answer. D

Question. The design of the database is known as
(a) attribute
(b) database scheme
(c) abstraction
(d) database oriented
Answer. B

Question. A relational database is a collection of
(a) attributes
(b) tables
(c) records
(d) fields
Answer. B

Question. A tuple in RDBMS is referred to ……… of a table.
(a) record
(b) field
(c) table
(d) key
Answer. A

Question. …………… refers to the attribute that can uniquely identify tuples within the relation.
(a) Foreign key
(b) Consolidate key
(c) Alternate key
(d) Primary key
Answer. D

Question. Which of the following is an attribute whose value is derived from the primary key of some other table?
(a) Primary key
(b) Foreign key
(c) Alternate key
(d) None of these
Answer. B 

Question. Which of the following is not a data type?
(a) Picture/Graphic
(b) Date/Time
(c) Text
(d) Number
Answer. A

Question. The default data type for a field is
(a) number
(b) auto number
(c) currency
(d) text
Answer. D

Question. What is the use of Memo data type?
(a) To add table
(b) To store objects created in other programs
(c) For long text entries
(d) For short text entries
Answer. C

Question. What data type should be chosen for a zipcode field in a table?
(a) Text
(b) Number
(c) Memo
(d) All of these
Answer. B

Question. You create a table in MS-Access. You decided to create two fields RollNo and Date_of_Birth, what will be the data type of Date_of_Birth column?
(a) Number
(b) Text
(c) Yes/No
(d) Date/Time
Answer. D

CBSE Class 10 Computer Science Chapter 3 Database Management System Case Based MCQs

Direction Read the case and answer the following questions.

1. Mr. Amar Agarwal is the owner of his parental firm Agarwal & Sons which deals in the wholesale business of spices. He wants to maintain records of spices available in their shop and generate bills when someone purchases any spices from the shop. They want to create a database to keep track of all the spices in the shop and the spices purchased by customers.

Question. Which is the correct SQL statement to create the table?
(a) CREATE TABLE spice(scode integer, sname char(25), price decimal, stock integer)
(b) CREATE spice(scode integer, sname char(25), price decimal, stock integer)
(c) CREATE spice(scode , sname , price, stock)
(d) SELECT TABLE spice(scode integer, sname char(25), price decimal, stock integer)
Answer. A

Question. To add records in the table which of the following SQL statement is used?
(a) ADD
(b) SELECT
(c) INSERT
(d) INSERT INTO
Answer. D

Question. To add a new column exp_date in the existing table the statement is
(a) ALTER TABLE spice ADD (exp_date date);
(b) UPDATE TABLE spice ADD (exp_date date);
(c) MODIFY spice ADD (exp_date date);
(d) ALTER TABLE spice MODIFY (exp_date date);
Answer. A

Question. To view the table structure of the above created table the command is
(a) SELECT spice
(b) DESC spice
(c) DESCRIBE spice
(d) Both (b) and (c)
Answer. D

Question. To create an index on name of spices in the existing table, the command is
(a) ALTER TABLE spice ADD INDEX spice_idx (sname)
(b) CREATE INDEX spice_idx ON spice (sname)
(c) CREATE TABLE spice (INDEX spice_idx (sname))
(d) Both (a) and (b)
Answer. D 

2. Consider the table STUDENT with following details.

Question. Command to select all Commerce students from the table STUDENT.
(a) SELECT * FROM STUDENT;
(b) SELECT Stream FROM STUDENT WHERE Stream= ‘Commerce’;
(c) SELECT * FROM STUDENT WHERE Stream = ‘Commerce’;
(d) SELECT Name FROM STUDENT WHERE Stream = ‘Commerce’;
Answer. C

Question. View all records other than ‘12A’ class.
(a) SELECT DISTINCT Class FROM STUDENT;
(b) SELECT Class FROM STUDENT WHERE (Class NOT = ‘Science’);
(c) SELECT * FROM STUDENT WHERE Class NOT IN (‘Science’);
(d) None of the above
Answer. D

Question. Modify the marks of Kanika as 85.5.
(a) ALTER TABLE STUDENT SET Marks = 85.5 WHERE Name = ‘Kanika’;
(b) UPDATE STUDENT SET Marks=85.5 WHERE Name = Kanika’;
(c) UPDATE TABLE STUDENT SET Marks =85.5 WHERE Name= ‘Kanika’;
(d) ALTER STUDENT SET Marks=85.5 WHERE Name = ‘Kanika’;
Answer. B

Question. Command to delete all records that belongs to ‘Science’ stream .
(a) DELETE FROM STUDENT WHERE Stream= ‘Science’;
(b) DROP FROM STUDENT WHERE Stream= ‘Science’;
(c) DROP TABLE STUDENT WHERE Stream= ‘Science’;
Answer. A

Question. None of the above
(v) Add a record of new student ‘Rashmi’ of ‘Humanities’ stream in class ‘12B’ and her marks is ‘70.9’.
(a) INSERT INTO STUDENT VALUES (‘Rashmi’, ‘Humanities’, ‘12B’, 70.9);
(b) INSERT INTO STUDENT VALUES (7,‘Rashmi’, ‘Humanities’, ‘12B’, 70.9);
(c) INSERT INTO STUDENT VALUES (‘Rashmi’, ‘Humanities’, 70.9, ‘12B’);
(d) INSERT INTO STUDENT VALUES (7,‘Rashmi’, ‘Humanities’, 70.9, ‘12B’);
Answer. D

CBSE Class 10 Computer Science Chapter 3 Database Management System Short Answer Type Questions

Question. What is database? Give an example. What does DBMS stand for?
Answer. A collection of related information organised as tables is known as database e.g. INGRES, MySQL etc.DBMS stands for DataBase Management System. It is a computer-based record keeping system.

Question. What is the difference between ‘Rows’ and ‘Columns’ in a table?
Answer. In a table, rows are called records and columns are termed a fields. A row stores complete information of a record whereas column stores only similar data values for all records.

Question. What is field in database? Give an example.
Answer. A field is an area, reserved for a specific piece of data. It is also known as attribute. e.g. Customer Name.

Question. Define forms and what is the need of using them?
Answer. A form is awindow or screen that contains numerous fields or spaces to enter data. Forms can be used to view and edit your data. It is an interface in user specified layout.

Question. What does RDBMS stand for?
Answer. RDBMS stands for Relational Database Management System. It is a type of DBMS that stores data in the form of relations (tables).

Question. How is data organized in a RDBMS?
Answer. A relational database is a type of database. It uses a structure that allows us to identify and access datain relation to another piece of data in the database. Data in a relational database is organized into tables.

Question. Write the purpose of DBMS.
Answer. DBMS is used to store logically related information at a centralised location. It facilitates data sharing among all the applications requiring it.

Question. Write any two uses of database management system.
Answer. The two uses of database management system are as follows
(i) DBMS is used to store data at a centralised location.
(ii) It is used to minimise data redundancy and data inconsistency.

Question. Write any two advantages of using database.
Answer. The two advantages of using database are as follows
(i) It can ensure data security.
(ii) It reduces the data redundancy.

Question. Give any two disadvantages of the database.
Answer. The two disadiantages of the database are as follows
(i) A database system creates additional complexity and requirements.
(ii) A database system is a multi-user software, which is less efficient.

Question. A table named School (containing data of students of the whole school) is created, where each record consists of several fields including AdmissionNo (Admission Number), RollNo (Roll Number), Name. Which field out of these three should be set as the primary key and why?
Answer. AdmissionNo should be set as primary key because admission numbers are unique for each and every students of the school, which is not possible in the case with RollNo and Name.

Question. Why Memo data type is preferred over Text data type for a field?
Answer. When the length of the field is more than 255 characters. Text data type is not capable to store the project description because its length cannot be more than 255 characters so, Memo data type is preferred over Text data type.

Question. What happens when text is entered in a Number type field?
Answer. When we enter text in a Number field and press Enter or press Tab key, it displays a message that ‘‘The value you entered does not match the Number data type in this column.’’

Question. List datatypes available in Numeric data type.
Answer. Datatypes available in numeric data type are TINYINT,SMALLCINT, MEDIUMINT, INT and BIGINT.

Question. Write one example of data field for which you would set the Required property to Yes.
Answer. In a table, when we declare a field as a primary key, then the field’s Required property must be set to yes because in a primary key field, we need to enter data always.

Question. What is the purpose of Default Value field property?
Answer. If there is a situation when you want to enter same value for all records. Then, to avoid typing the same thing many times, you can set as a Default Value property.

Question. Damini is a programmer in an institute and is asked to handle the records containing information of students. Suggest any 5 fields name and their data type of students database.
Answer.
Field Name     Data Type
RollNo               Number
Name                Text
Class                 Text
Section              Text
Gender              Text

Question. Create a table of Student based on the following table instance.
Column Name    Data Type    Length
ID                        integer
Name                  varchar           15
Stream __Id        integer
Answer. CREATE TABLE STUDENT (ID Integer, Name varchar (15), Stream_Id Integer);

Question. Write a SQL command to create the table BANK whose structure is given below.
Table : BANK
Field Name       Datatype    Size     Constraint
ID__Number       integer         10      Primary key
Name                 varchar        20
B_date               date
Address             varchar        50
Answer. The SQL command to create a table as per given structure is as follows
Mysql> CREATE TABLE BANK (ID__Number integer
(10) PRIMARY KEY, Name varchar (20), B__date
Date, Address varchar (50));

Question. Insert some information into a table COLLEGE, whose structure is given below.
ROLL_NO NAME CLASS BRANCH
Answer. (i) Mysql>INSERT INTO COLLEGE (ROLL_NO,
NAME, CLASS, BRANCH) VALUES (2,
‘VIKAS’,12, ‘SCIENCE’);
(ii) Mysql>INSERT INTO COLLEGE (ROLL_NO,
NAME, CLASS, BRANCH) VALUES (3, ‘RAJ’, 10,
‘SCIENCE’);

Question. What is the value of Entry Required field?
Answer. The value of this property can be Yes or No. If entry required is Yes, the field cannot be absent i.e. should be necessarily present with a value.

Question. What is table? Also, define Candidate Key.
Answer. A table consists of a number of rows and columns. Each record contains values for the attributes. A candidate key is the smallest subset of the super key for which there does not exist a proper subset that is super key. Any candidate key can be choosen to uniquely identify the records, it is called primary key.

Question. What is Data Control Language?
Answer. Data Control Language is used to create roles, permissions, and referential integrity as well it is used to control access to the database by securing it. These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Question. What is Data Transaction Control Language?
Answer. Transaction control commands manage changes made by DML commands. These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

CBSE Class 10 Computer Science Chapter 3 Database Management System Long Answer Type Questions

Question. Define Database Management System. Write two advantages of using database management system for school.
Answer. Database Management System (DBMS) is a collection of programs that enable users to create, maintain database and control all the access to the database. The primary goal of the DBMS is to provide an environment that is both convenient and efficient for user to retrieve and store information. The advantages of using DBMS for school are as follows
(i) In school, DBMS is used to store the data about students, teachers and any other related things at a centralised location.
(ii) It provides security to the personal information of the school, stored in it.

Question. Distinguish between a record and a field in a table with an example.
Answer. Distinguish between a record and a field in a table are as follows
Record
• It is a collection of data items which represent a complete unit of information about a thing or a person.
• A record refers to a row in the table.
• Record is also known as tuple.
• e.g. If Employee is a table, then entire information of an employee is called a record.
Field
• It is an area with in the record reserved for a specific piece of data.
• A field refers to a column in the table.
• Field is also known as attribute.
• e.g. If Employee is a table, then empld, empName, department, Salary are the fields.

Question. Write one example of each field, for which you would use
(i) Text data type (ii) Memo data type
Answer. (i) Text data type It allows to store text or combination of text and numbers as well as numbers that don’t require calculations such as phone number. This data type allows maximum 255 characters to store. e.g. If Employee is a table and Emp_No, Name and Description are fields, then name will be a Text field. Because, Name is a character entry field.
(ii) Memo data type It allows long blocks of text that uses text formatting. e.g. In the Employee table, the field Description will be of Memo data type, because the length of description of employee may be large.

Question. Define the following terms
(i) INSERT Command (ii) SELECT Command
(iii) DELETE Command (iv) DCL Command
(v) Template
Answer. (i) INSERT Command It is used to add a single record or multiple records into a table.
Syntax
INSERT INTO <table_name> (col1, col2.....)
VALUES (val1, val2);
(ii) SELECT Command It is used to query or retrieve
data from a table in the database.
Syntax
SELECT column_list FROM table_name;
(iii) DELETE Command To discard unwanted data from
a database, the delete command is used.
Syntax
DELETE FROM <table_name> WHERE
<condition>;
(iv) DCL Command DCL commands are used to assign security levels in database which involves multiple user setups. They are used to grant defined role and access privileges to the users.
(v) Template It is a complete tracking application with predefined tables, forms , reports, queries, macros and relationship.

Question. Write SQL queries for the questions from (i) to (v) on the basis of table class

(i) Select all the non-medical stream students from the class table.
(ii) List the names that have grade A sorted by stipend.
(iii) Arrange the records of class name wise.
(iv) List the records whose grade is B or C.
(v) Insert the new row with the following data. (06, 'Jack’, 2800, 'Humanities’, 98, ‘A’)
Answer. (i) mysql> SELECT * FROM Class WHERE Subject= ‘Non-medical’;
(ii) mysql> SELECT Name FROM Class WHERE Grade=‘A’ ORDER BY Stipend:
(iii) mysql> SELECT * FROM Class ORDER BY Name;
(iv) mysql> SELECT * FROM Class WHERE Grade IN (‘B’, ‘C’);
(v) mysql> INSERT INTO Class VALUES (06, ‘Jack’,2800, ‘Humanities’, 98, ‘A’);

Question. Write SQL commands for the questions from (i) to (v) on the basis of table SHOP.
Table : SHOP

(i) Display all products whose quantity in between 100 and 400.
(ii) Display data for all products sorted by their quantity
(iii) To list S_Name, P_Name, Cost for all the products whose quantity is less than 300.
(iv) To display S_NO, P_Name, S_Name, Qty in descending order of quantity from the SHOP table.
(v) Give S_Name for products whose name starts with ‘B’:
Answer. (i) mysql> SELECT * FROM SHOP WHERE Qty
BETWEEN 100 and 400;
(ii) mysql> SELECT * FROM SHOP ORDER BY Qty;
(iii) mysql> SELECT S_Name, P_Name, Cost FROM SHOP WHERE Qty <300;
(iv) mysql> SELECT S_NO, P_Name, S_Name, Qty FROM SHOP ORDER BY Qty DESC;
(v) mysql> SELECT S_Name FROM SHOP WHERE P_Name LIKE 'B%';

Question. Write SQL commands for the questions from (i) to (v) on the basis of table MASTER (contains details of employees)
Table : MASTER

(i) Write a command to update the salary of the employee to 40000, whose SNo is 3.
(ii) Write a query to add a column Date_of_ Joining to the table MASTER.
(iii) Select Age, Department of those employees whose salary is greater than 12000.
(iv) List all data of table MASTER.
(v) Write a query to change the data type of a column Name to varchar with size 35.
Answer. (i) mysql>UPDATE MASTER SET Salary= 40000 WHERE SNo =3;
(ii) mysql >ALTER TABLE MASTER ADD Date_of _ Joining date;
(iii) mysql>SELECT Age, Department FROM MASTER WHERE Salary>12000;
(iv) mysql>SELECT * FROM MASTER;
(v) mysql>ALTER TABLE MASTER MODIFY NAME VARCHAR (35);

Question. The director of a company uses a database to store data about job title. This is a part of the database given below

(i) How many records are there in this part of the database?
(ii) How many fields are there in this part of the database?
(iii)What is the job title of the employee with Payroll Number M421?
(iv)Which department has maximum employees and what are their Payroll Numbers?
Answer. (i) 6 (ii) 6 (iii) Secretary
(iv) Finance department and their Payroll Numbers are A621 and M502.

Question. Consider the following database.

Answer the following questions.
(i) Write the name of the field that contains numeric data.
(ii) Identify the primary key field in the database.
(iii) Identify the field type of the DateofSale field.
(iv) Identify the names of the fields that contain textual data.
(v) The given table contains how many fields and records?
Answer. (i) QtySold and Amount
(ii) ProductCode
(iii) Date/Time data type
(iv) ProductCode, ProductName and CustomerName