Unit 3: Database Management System

 Unit 3: Database Management System

SESSION 1: APPRECIATE THE CONCEPT OF DATABASE MANAGEMENT SYSTEM

 

Q1. What is a database?

Ans. A database is an organized collection of data.

 Q2. What is DBMS?

Ans. DBMS stands for Database Management System. It is software that controls the creation, maintenance, and use of a database.

 Q3. Name two DBMS Software.

Ans. Two DBMS Software are :

1 MySQL

2. Oracle

3. Microsoft SQL Server

4. Microsoft Access.

 Q4. What is the difference between a Flat file and a Relational?

Flat File: Data is stored in a single table. It is suitable for less amount of data.

Relational: Data is stored in multiple tables. It is suitable for large amounts of data.

 Q5. What do you mean by database servers?

Ans. Database servers are dedicated computers that hold the actual databases and run only the DBMS and related software.

 Q6. Write four advantages of a database.

Ans. Four advantages of a database are

1 It reduces Data Redundancy

2. It allows sharing of data.

3. It helps to maintain data integrity.

4. It provides Data Security.

 Q7. What do you mean by Data Redundancy?

Ans. Duplication of data in a database is known as data redundancy.

Q8. What do you mean by Data Integrity?

Ans. Data integrity means that the data is accurate and consistent in the database.

 Q9. What is Data Consistency?

Ans. Data Consistency means there should be multiple mismatching copies of the same data in a database.

 Q10. Write two features of the database.

Ans. Two features of the database are :

1. A database can have one or many tables.

2. Every table in a database has a primary key field which ensures 100% unique values in the database.

  Q11. What do you mean by RDBMS?

Ans. RDBMS stands for Relational Database Management System. When data is to be stored, maintained, and retrieved from multiple tables then special database software is required known as RDBMS.

 Q12. What is Primary Key?

Ans. A field that is unique for each and every record in a table is called the primary key.

 Q13. What do you mean by Composite Primary key?

Ans. When a primary key constraint is applied on one or more columns then it is known as Composite Primary Key.

 Q14. What is Foreign Key?

Ans. A Foreign Key is a field in one table that refers to the primary key of another table. It is used to link two tables.

 Q15. Answer the following questions on the basis of the following table:

Table: Student

Stud_id	Stud_name	Class	Fees
1	Aman	IX	3000
2	Anil	X	3200

Table: Teacher

T_id	T_name	T_Sal	Stud_id
3210	Mr. Kumar	60000	1
3211	Mr. Sethi	70000	2

1. Identify the primary key in the Student and Teacher table.
2. Identify the foreign key in the Teacher table.

Ans. 1. Primary Key in Student table: Stud_id

The primary key in the Teacher table is: T_id

2. Foreign key in the Teacher table is: Stud_id

=====================================================

SESSION 2: CREATE AND EDIT TABLES USING WIZARD AND SQL COMMANDS

 

Q1. What do you mean by RDBMS?

Ans. RDBMS stands for Relational Database Management System. It is a database management system that is based on a relational model.

 Q2. Define the following terms :

1 Table 2. Fields 3. Records

Ans.

Table: A table is a set of data elements that are organized in vertical columns and horizontal rows.

Fields: A field is a set of data values of a particular simple type, one for each row of the table.

Records: A row also called a Record or Tuple represents a single, data item in a table.

 Q3. What are the two ways of creating a table in OpenOffice Base?

Ans. Two ways of creating a table in OpenOffice Base are :

a. Create a table in design view

b. Use Wizard to create a table.

 Q4. What do you mean by Data type? Give an example of two data types used in OpenOffice Base.

Ans. Datatypes are used to identify which type of data (value) we are going to store in the database.

Q5. Identify the data type in which we can store the decimal value :

a. Boolean b. TinyInt c. Decimal d. Real e. Double f. Smallint

Ans. Data types in which we can store the decimal value are :

c. Decimal

d. Real

e. Double

 Q6. What is the difference between Char and Varchar data types?

Ans. Char is a fixed-length data type and Varchar is a variable-length data type.

 Q7. Write the data type suitable for the following fields.

a. DateofBirth b. Name c. Rollno d. Class e. Email f. City

Ans.

a. DateofBirth – Date

b. Name – Char or Varchar

c. Rollno – TinyInt / Smallint / Integer

d. Class – Char / Varchar

e. Email – Char or Varchar

f. City – Char or Varchar

Q8. Write four data types available in Alphanumeric Data types.

Ans. Four data types are available in Alphanumeric Data types :

1. LongVarchar
2. Char
3. Varchar
4. Varchar_Ignorecase

 Q9. Data in a (RDBMS) is organized in the form of __________________.

Ans. Tables.

 Q10. In RDBMS table is also known as ________________

Ans. Relation

 Q11. Write four data types that are used to store numerical value.

Ans. Four data types that are used to store numerical values are

1. Smallint
2. Bigint
3. Integer
4. Float

 Q12. Write four appropriate field names and their data types to store records of students.

Ans. Four appropriate field names and their data types to store records of students are :

1 Name —————— Char / Varchar

2. Admno —————- Integer

3. Fees ——————–Float/Double

4. DOB ——————-Date

 Q13. Write four appropriate field names and their data types to store the record of the book.

Ans. Four appropriate field names and their data types to store records of the book are :

a. Author_name ————- Char / Varchar

b. Price ———————– Float / Decimal / Double

c. Class ———————– Char / Varchar

d. Subject ———————Char / Varchar

 Q14. What is the default data type of a field while creating the table in the design view?

Ans. Varchar

 Q15. _______________ are the basic building blocks of a database.

Ans. Tables

 Q16. Write the expanded form of SQL

Ans. SQL: Structured Query Language

 Q17. A table has __________________ (horizontal / Vertical) row and ____________________ columns (horizontal / Vertical).

Ans. Horizontal, Vertical

 Q18. OpenOffice Base is an open source _________________ (DBMS / RDBMS )

Ans. RDBMS

 Q19. ________________ (Database / Table ) wizard helps you to create a new database.

Ans. Database

 Q20. Multiple tables can be created in a database. (T / F)

Ans. True

=====================================================

SESSION 3: PERFORM OPERATIONS ON THE TABLE

 

Q1. What do you mean by Datasheet View?

Ans. A view in which new data can be inserted and inserted data can be modified or removed in a table is called a datasheet view

 Q2. What do you mean by design view?

Ans. A view in which we can change the structure of the table is called a design view.

 Q3. Write steps to open the table in the design view.

Ans. The steps to open the table in design view are :

1. Open the Open Office Base
2. Right-click on the table that we need to open.
3. Select the option Edit from the popup menu.
4. The required table will open in the design view

 Q4. What do you mean by Field Property in Base?

Ans. Those properties which determine the characteristics and behavior of fields are called field properties.

 Q5. Name two properties of Numeric type data.

Ans. Two properties of Numeric type data are :

1. Auto Value
2. Length

 Q6. Name two properties of Character type data.

Ans. Two properties of Character type data are :

1. Entry Required
2. Length

 Q7. What is the default length of the Integer data type?

Ans. The default length of the Integer data type is 10.

 Q8. What is the default length of the Varchar data type?

Ans. The default length of the Varchar data type is 50.

 Q9. Define the following field properties in reference to Open Office Base.

1. Default Value 2. Format 3. Auto Value

Ans.

1. Default Value: A value that automatically appears when we enter a new record.

2. Format: This property helps to set the format of the data entered in the field.

3. Auto Value: This is the property of the Numeric Field type. If this property is set to Yes then the numeric field will get an auto numeric value.

 Q10. What do you mean by Sorting?

Ans. Sorting means to arrange the data in either ascending or descending order.

 Q11. What is Referential Integrity?

Ans. Referential Integrity is used to maintain the accuracy and consistency of data in relationships.

  Q12. What do you mean by the relationship between tables?

Ans. A relationship refers to an association or connection between two or more tables.

 Q13. How many types of relationships can be created between tables? Name them.

Ans. Three types of relationships can be created between tables.

1. One to One
2. One to Many or Many to One
3. Many to Many

 Q14. Relationship option is available in _____________ menu.

Ans. Tool

 Q15. Write two advantages of the relationship created between the two tables.

Ans. Two advantages of the relationship created between the two tables are :

1. It reduces data entry errors.
2. It helps to summarize data from related tables

=====================================================

SESSION 4: RETRIEVE DATA USING QUERY

 

Q1. What do you mean by query in Open Office Base?

Ans. A query is a request to collect specific information from a table or combination of tables.

 Q2. In how many ways you can create a query in Open Office Base?

Ans. We can create queries in Open Office Base in three ways which are :

1. Create a query in the Design view
2. Create a query using Wizard
3. Create query in SQL view

 Q3. Name the Query language which is used in Base?

Ans. SQL

 Q4. Which command is used to retrieve data from the table?

Ans. Select command is used to retrieve data from the table.

Q5. Name two categories of SQL Commands.

Ans. Two categories of SQL Commands are :

1. DDL
2. DML

 Q6. Differentiate between DDL and DML Commands.

Ans. DDL: It stands for Data Definition Language; Example: Create, Alter, Drop

DML: It stands for Data Manipulation Language; Example: Insert, Update, Delete

 Q7. Identify the DML Commands from the following :

1. Create
2. Alter
3. Insert
4. Delete

Ans. Insert and Delete are DML Commands

 Q8. __________ is the most commonly used Data Manipulation Language(DML) command.

Ans. Select

 Q9. Name two clauses that can be used with Select Command.

Ans. Two clauses that can be used with Select Command are :

1. Where Clause
2. Order By Clause

 Q10. Write the Select command to display all the records of table “book”.

Ans. Select * from book;

 Q11. Write the shortcut to execute the query in “Create query in SQL view” of Base.

Ans. F5

 Q12. What is the purpose of the Where clause in Select Command?

Ans. Where Clause is used to retrieve specific records from the table.

 Q13. What is the purpose of Order by clause in Select Command?

Ans. Order by clause is used to arrange the records in ascending or descending order.

 Q14. Write the queries for the following table: Emp

Emp_id	Ename	Salary
1	Suman	20000
2	Sanjay	32000
3	Ravi	30000

a. Display the salary of all the employees after incrementing by Rs 1000.

Ans. Select Salary +1000 from Emp;

 b. Display the Employee id and salary of all the employees after decreasing by Rs 500.

Ans. Select Emp_id, Salary – 500 from Emp;

 c. Display the Name and salary of all the employees after incrementing it as thrice the amount of the present salary.

Ans. Select Ename, Salary * 3 from Emp;

 d. Display the Employee id, Name, and salary of all the employees after decrementing it as half the amount of the present salary.

Ans. Select Emp_id, Ename, Salary/2 from Emp;

 e. Display the Employee id and Name of all the employees.

Ans. Select Emp_id, Ename from Emp;

 Q15. Write the queries for the following table: Student

Admno	Name	Class	House
1001	Sonam	9	Blue
1002	Ravi	10	Yellow
1003	Poonam	10	Green

a. Display the entire table

Ans. Select * from Student

 b. Display the list of students whose house color is blue.

Ans. Select * from Student where House = “Blue”

 c. Display the admission number of students whose house color is Yellow.

Ans. Select Admno from Student where House = “Yellow”

 d. To view records in ascending order of Admission Number.

Ans. Select * from Student order by Admno Asc;

 e. Display the records of Class 10 Students.

Ans. Select * from students where Class = 10;

 f. Display the class of ‘Ravi’

Ans. Select Class from Student where Name = ‘Ravi’

 g. Insert the given record: 1004, “Aman”, 11, “Blue”

Ans. Insert into Student values( 1004, “Aman”, 11, “Blue”)

Q16. Which command is used for the following task in the database?

1. To insert a new record
2. To modify the existing data.
3. To delete a record
4. To display record

Ans.

1. Insert
2. Update
3. Delete
4. Select

 Q17. Write the queries for the following table: Item

Item no	Iname	Price	Qty
12	Pen	10	17
13	Eraser	5	15
14	Notebook	15	20

a. Write a query to insert a new record of the following details

15, “Pencil”, 20, 10      Ans. Insert into Item values(15, “Pencil”, 20, 10)

 b. Write a query to display detail of items whose quantity is more than 10.

Ans. Select * from Item where Qty > 10

 c. Write a query to change the quantity of Item number 13 to 25.

Ans. Update Item set Qty = 25 where Itemno = 13

 d. Display the total amount of each item. The amount must be calculated as the price multiplied by the quantity of each item

Ans. Select Price * Qty from Item.

 e. Display the name of an item whose price is 10.

Ans. Select Iname from Item where price = 10

 f. Display all the records in ascending order of price.

Ans. Select * from Item order by Price asc.

 g. Identify the Primary key from the table Item. 

Ans. Item no

 h. Write the suitable data type of field “Iname”. Ans. Char or Varchar

 i. Write a query to increase the price of all items by Rs2.

Ans. Update Item set Price = Price + 2;

 j. Write a query to decrease the price of all items by Rs2 whose price is less than 20.

Ans. Update Item set Price = Price – 2 where Price < 20;

 Q18. By default, data is arranged in _________ order using the ORDER BY clause.

Ans. Ascending Order

 Q19. Which clause is used for the following:

a. To display specific records. b. To display records in a particular order.

Ans.

1. Where Clause
2. Order by Clause

=====================================================

SESSION 5: CREATE FORMS AND REPORTS USING WIZARD

 

Q1. What is Form in OpenOffice Base?

Ans. A form provides the user a systematic way of storing information in the database.

 Q2. Name the interface which lets users view, enter, and change data directly in the database.

Ans. Form

 Q3. Write two ways of creating a form in Open Office Base.

Ans. Two ways of creating forms in Open Office Base are :

a. Create form in design view

b. Use Wizard to create a form

 Q4. Can we enter the data in a table using forms?

Ans. Yes

Q5. What do you mean by Report in Open Office Base?

Ans. A report helps to display the data in a summarized manner. It is used to generate the overall work outcome in a clear format.

 Q6. Differentiate between Forms and Reports.

Ans. 

Form: It is a way of storing data in a database; Values entered in the form can be modified

Report: It is a way to display data in printed form; Values in the report can not be modified.

Q7. Name the two types of reports created in OpenOffice Base.

Ans. Two types of reports created in OpenOffice Base are:

a. Static Report

b. Dynamic Report

Q8. What do you mean by static report?

Ans. A report which does not show any change if we make any changes in the data of the table.

 Q9. What do you mean by dynamic report?

Ans. A report which shows the corresponding changes which we make in the data of the table.

 Q10. Can we enter records by report?

Ans. No