Database management system
Q.1) What is databae ?
Ans) A database is an organized collection of structured information, or data, typically stored
electronically in a computer system . It is computer based
record keeping system. A database is usually controlled by a database management system (DBMS)
Q.2) What do you understand by Data inconsistency ?
Ans) Multiple mismatching copies of same data is known as data inconsistency.
It means that different files contain different information about a particular object or person . This can
cause unreliable and meaningless information . Data
Redundancy leads to data inconsistency. i.e. when data redundancy is not controlled, it may be possible
that the two entries about the same data do not
Agree , at such times the database is said to be inconsistent which provides incorrect and conflicting
information .
Q.3) What is meant by Data redundancy ?
Ans) It is defined as the redundancy means duplicate data and it is also stated that
the same parts of data exist in multiple locations into the database. This condition
is known as data redundancy .
Q.4) Write the key features of a DBMS ?
Ans) The key featutres of DBMS are
Minimum duplication and redundancy
Sharing of data
Databases enforce standards
Provides high level of security
Saves storage space and cost
Anyone can work on it
Large database maintenance
Permanent storage of data
Q.5) Write the basic features of OOo base .
Ans) Basic features of OOo base are Tables,Queries,Forms and reports .
Q.6) How do you interpret ‘field’ in context of a DBMS ?
Ans)A field is a smallest unit of named data . It may consist of any number of
bits or bytes. A field item represents one type of information . For example a
table may contains many fields such as Empno, Ename, Job, Mgr, Sale etc.
such that each field contain one type of information as name field will contain
only person name of each record .
Q.7) What is a primary key ? what is an alternate key ?
Ans) A primary key is field that uniquely identifies records in a table .
The key that have the unique values for each record but are not selected
as the primary keys are called the alternate key.
Q.8) What is the need of setting relationships between tables ?
Ans) A relationship between tables is an important aspect of a god relational database which helps to
established a link between two table based on common
field or we can say that , it establishes a connection between a pair of tables that
are logically related to each other . It helps to refine table structures and minimize
redundant data . It is the mechanism that enables you to fetch data from multiple
tables simultaneously .
Q.9) What is a DBMS ? What importance does it have for an enterprise ?
Ans) A database management system (DBMS) is a software system that is
basically a computer based record keeping system used to create and manage
databases . The DBMS provides users and programmers with a systematic way
to create, retrieve , update and manage date .
A database contains information about one particular enterprise . It manages data in more effectively
and efficiently. For an enterprise , it maintains any information that may be necessary to the decision
making processes involved
in the management of that organization .
Q.10) What are advantages of DBMS over normal file system ?
Ans) Advantages of DBMS over normal file system :
It controls data redundancy
It facilitate sharing of data
It facilitate data concurrency
It facilitate data searching
It maintains data integrity
It facilitate data security
Q.11) What forms, queries and reports in base ?
Ans) A form is an interface in user specified layout has lets users view,enter, change data directly in the
table.
A query is a statement that gives you filtered data obtained from one or more
Underlying base tables, according to your conditions and specifications.
A report is an effective way to present data in a printed format . It is a formal,
Presentable printed document has lists data in a formatted manner.
Q.12) What are the different steps involved in designing a database ?
Ans) Steps involved in designing a database are :
1. Determine the purpose of database
2. Determine the tables you need .
3. Determine the fields you need .
4. Identify the field or fields with unique values in each record .
5. Determine the relationships between tables .
6. Refine your design .
7. Enter data and create other database objects .
Q.13) Name some popular DBMSs .
Ans) Some popular DBMS are :
1. Mysql
2. MS Access
3. Oracle
4. Postgre SQL
5. dBase
6. Foxpro
7. MS SQL Server
8. Ooo Base etc.
9. Libre office base
Q.14) How is a relational database different from a flat database ?
Ans) When all the data is stored in one file then such a database is called flat database. A spreadsheet is
an example of flat database.
When the data is stored in multiple tables that are linked via common fields then
such a database is called relational database and software handling relational
Database is called RDBMS.
Q.15) Name different types of keys in a table .
Ans) Different types of keys in a table are : Primary key, Candidate key,
Alternate key, Foreign key, Composite primary key etc .
Q.16) What is a foreign key ?
Ans) A non-key field of a table that refers to the primary key of another table .
Q.17) What is a composite primary key ?
Ans) A primary key having a combination of more than one field is a composite primary key .
Q.18) How is a foreign key different from a primary key ?
Ans) A foreign key is a field or fields that refers to a primary key of another table while primary key is a
field in a table that uniquely identifies each record in a table .
Foreign key is a non-key attribute that allows duplicate values whereas primary key is key field that
does not allow duplicate values.
Q.19 ) How can a BASE database be created ?
Ans) BASE database can be created in two ways :
a) Through table wizard
b) From scratch
Q.20) What is the need of designing a tables ?
Ans) A table is a data structure of a database that organize information into rows and columns . It can
be used to store and display data in a structured format . Information stored in a table structure can be
easily access, update, store and delete .
Q.21) How can a table be created in BASE ?
Ans) Table can be created in BASE in three ways :
a. Using table wizard
b. Through design view
c. Using SQL statements
Q.22) What does a table wizard do ?
Ans) Table wizard is one of the way to create table in BASE database in easy way.
It provides a sequence of dialog box or series of well define steps to create table . Each dialog box
contains number of options to select according to the desire structure of table to store records . It helps
to select desire fields and their data types , applying primary key and other field properties required for
defining table structure
Q.23) Can you set the primary key of a table through the table wizard ?
Ans) Yes, we can set the primary key of a table through the table wizard .
Q.24) How are field types Boolean and Tinyint different from one another ?
Ans) The basic difference between Boolean and tinyint(1) is only in the naming convention. If we say
that we need true or false values then Boolean comes to our mind, instead of tinyint(1). These data
types are synonyms. It is up to us which data type we want to use- values can be 1 and 0 or true and
false
Q.25) Write the difference between INT and Tinyint data types .
Ans) Both TINYINT and INT are exact numeric data types, used for storing integer data. Below table lists
out the major difference between TINYINT and INT Data Types
TINYINT INT
Storage Size 1 byte 4 bytes
Minimum Value 0 -2,147,483,648 (-2^31)
2,147,483,647 (2^31-1)
Maximum Value 255
SESSION -3 CREATING / EDITING TABLES IN DESIGN VIEW
Q.26)WHAT DO YOU MEAN BY DEFAULT VALUE OF A FIELD IN BASE AND HOW CAN IT BE SET ?
ANS) THE DEFAULT VALUE IS AUTOMATICALLY ENTERED IN A FIELD OR CONTROL WHEN A NEW RECORD
IS CREATED.
IT CAN BE SET BY FOLLOWING:
SUPPOSE IN STUDENT TABLE , WE MIGHT SET THE DEFAULT VALUE FOR HOUSE AS “MY HOUSE “. WHEN
USERS ADD RECORDS TO THE TABLE ,THEY CAN EITHER ACCEPT THIS VALUE OR ENTER THE NAME OF A
DIFFERENT HOUSE .
Q.27) WHAT ARE THE DIFFERENT DATE/TIME FORMATS AVAILABLE IN BASE ?
ANS) THE DIFFERENT DATE/TIME FORMATS AVAILABLE IN BASE ARE
Name Description Format
Date Stores month, day and year information 1/1/99 to 1/1/9999
Time Stores hour, minute and second info Seconds since 1/1/1970
Timestamp Stores date and time information
Q.28) DEFINE THE AUTO NUMBER DATA TYPE .
ANS) AutoNumber is a type of data used in tables to generate an automatically incremented numeric
counter. It may be used to create an identity column which uniquely identifies each record of a table.
Only one AutoNumber is allowed in each table.
Q.29) WHAT HAPPENS WHEN TEXT IS ENTERED IN A NUMBER TYPE FIELD ?
ANS) When we enter text in a Number field and press Enter or press Tab key BASE displays a message
that “The value you entered does not match the Number data type in this column.
Q.30) WHAT IS THE DEFAULT EXTENSION OF BASE DATABASE ?
ANS) The default extension of Base Database file is . odb.
Q.31) HOW DO WE SET A PRIMARY KEY IN BASE ?
ANS) To set the primary key:
. Open the database that you want to modify.
. In the Navigation Pane, right click the table in which you want to set the primary key and, on the
shortcut menu, click Design View. ...
. Select the field or fields that you want to use as the primary key
Q.32) WRITE ONE EXAMPLE EACH OF FIELDS FOR WHICH YOU WOULD USE (!) TEXT DATA TYPE AND (!!)
MEMO DATA TYPE .
ANS) (!) 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.
(!!) 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.
Q.33) HOW NUMBER AND DATE TYPE FIELDS ARE DIFFERENT IN BASE ? GIVE ANY ONE DIFFERENCE .
ANS) NUMBER data type contains numeric values, while DATE/TIME type field contains date and time
values.
NUMBER DATA TYPE WE CAN ENTER NUMERIC VALUES WHEREAS DATE/TIME FIELD WE CAN ENTER
DATE/TIME WITH SPECIFIC FORMAT. EXAMPLE YYYY-DD-MM .
Q.34) WHICH FIELD PROPERTY SPECIFIES WHETHER THE FIELD CAN BE LEFT EMPTY WHEN ENTERING
DATA INTO A RECORD ?
ANS) REQUIRED PROPERTY .
SESSION -4 PERFORMING OPERATIONS ON TABLES
Q.35) WHAT ARE THE OPERATIONS YOU NORMALLY DO WHILE EDITING DATA IN A TABLE ?
ANS) THE OPERATIONS WE NORMALLY DO WHILE EDITING DATA IN A TABLE ARE TO CHANGE FIELD
NAME , FIELD PROPERTY,FIELD TYPES ETC.
Q.36) WHAT IS SORTING ?
ANS) ARRANGING DATA IN THE ORDER OF SOME FIELD(S) VALUES WITH SIMILAR PROPERTIES .
Q.37) WHAT ARE TWO WAYS OF SORTING DATA ?
ANS ) TWO WAYS OF SORTING DATA ARE ASCENDING AND DESCENDING ORDER .
Q.38) WHAT IS THE ROLE AND IMPORTANCE OF RELATIONSHIP IN A DATABASE?
ANS) A relationship, in the context of databases, is a situation that exists between two relational
database tables when one table has a foreign key that references the primary key of the other table.
Relationships allow relational databases to split and store data in different tables, while linking
disparate data items.
Q.39) WHAT IS REFERENTIAL INTEGRITY ?
ANS) Referential integrity refers to the relationship between tables. Because each table in a database
must have a primary key, this primary key can appear in other tables because of its relationship to data
within those tables. When a primary key from one table appears in another table, it is called a foreign
key .
Q.40) WHAT ARE THE CONDITIONS FOR SETTING REFERENTIAL INTEGRITY IN A DATABASE ?
ANS) Referential integrity requires that a foreign key must have a matching primary key or it must be
null. This constraint is specified between two tables (parent and child); it maintains the correspondence
between rows in these tables. It means the reference from a row in one table to another table must be
valid.
Q.41) WHEN REFERENTIAL INTEGRITY IS ENFORCED ,WHAT ARE THE RULES THAT USERS MUST OBSERVE
?
ANS) SAME AS ANSWER NO 39 .
Q.42) GIVE SOME EXAMPLES OF DIFFERENT TYPES OF RELATIONSHIPS IN A DATABASE .
ANS) There are three types of relationships between the data you are likely to encounter at this stage in
the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you
need to examine the data and have an understanding of what business rules apply to the data and
tables.
SESSION -5 QUERIES IN BASE
Q.43) WHAT ARE QUERIES ?
ANS ) A query can either be a request for data results from your database or for action on the data, or
for both. A query can give you an answer to a simple question, perform calculations, combine data from
different tables, add, change, or delete data from a database.
Q.44) WHAT ARE THE ADVANTAGES OF QUERIES ?
ANS ) ADVANTAGES OF QUERIES ARE AS FOLLOWS :
View data only from the fields you are interested in viewing. When you open a table, you see all the
fields. ...
Combine data from several data sources. A table usually only displays data that it stores. ...
Use expressions as fields. ...
View records that meet criteria that you specify.
Q.45) HOW DO YOU CREATE QUERIES IN BASE ?
. ANS) Click the queries Tab.
. Click on Query Design.
. Click on the tables and then ADD, one at a time.
. Click and drag fields from each table into the query.
. Click on RUN
Q.46) WHAT IS GROUP FIELD?
ANS) A Group field is a way to group together one or more fields on a page for visual and practical
purposes. Visually, a group helps to tie similar fields together into a common category. Grouped fields
also provide organization and structure to the form.
Q.47) WHAT ARE DIFFERENT TYPES OF QUERIES ?
ANS ) It is commonly accepted that there are three different types of search queries: Navigational
search queries. Informational search queries. Transactional search queries
Q.48) WHAT IS A SUMMARY QUERY ?
ANS ) Summary queries (also called Group-By queries) are used to summarise the contents of a table.
You mark a query as a Summary Query by selecting the Summary Query option.
Q.49) WHAT IS AGGREGATE FUNCTION ? WHAT IS ITS OTHER NAME ?
ANS) A basic aggregate function returns a single value by aggregating multiple rows of data .
Other name of aggregate function is set functions.
Q.50) NAME SOME SUMMARY FUNCTIONS AND THEIR USAGE.
ANS ) NAME OF SOME SUMMARY FUNCTIONS AND THEIR USAGE ARE AS FOLLOWS :
COUNT : TO COUNT NUMBER OF RECORDS IN A GROUP OF RECORDS .
SUM : TO CALCULATE TOTAL OF A FIELD’S VALUES FOR A GROUP OF RECORDS .
AVERAGE : TO CALCULATE AVERAGE OF A FIELD’S VALUES FOR A GROUP OF RECORDS .
MINIMUM : TO CALCULATE MINIMUM VALUE OF A FIELD’S VALUES FOR A GROUP OF RECORDS .
MAXIMUM : TO CALCULATE MAXIMUM VALUE OF A FIELD’S VALUES FOR A GROUP OF RECORDS .
SESSION -6 STRUCTURED QUERY LANGUAGE
Q.51) HOW ARE SQL COMMANDS CLASSIFIED ?
ANS ) SQL COMMANDS ARE CLASSIFIED INTO FOLLOWING CATEGORIES :
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language(DCL)
Transaction Control Language(TCL)
Data Query Language (DQL)
Q. 52) DIFFERENTIATE BETWEEN DDL AND DML COMMANDS .
ANS ) DDL stands for Data Definition Language. DML stands for Data Manipulation Language. DDL
statements are used to create database, schema, constraints, users, tables etc. DML statement is used
to insert, update or delete the records .
Q.53 ) WHAT IS THE USE OF UPDATE STATEMENT IN SQL ? HOW IS IT DIFFERENT FROM ALTER
STATEMENT ?
ANS) UPDATE Command is used to update existing records in a database.
THE DIFFERENT BETWEEN UPDATE AND ALTER COMMAND ARE AS FOLLOWS :
UPDATE ALTER
1. UPDATE Command is a Data ALTER COMMAND IS DATA
Manipulation Language (DML). DEFINITION LANGUGE (DDL)
2.UPDATE COMMANND WILL
WORK ON THE DATA LEVEL . ALTER COMMAND WILL WORK
THE ACTION OF STRUCTURE LEVEL
AND NOT ON THE DATA LEVEL.
3. UPDATE COMMAND USED TO UPDATE ALTER COMMAND USED TO ADD ,
EXISTING RECORD IN A DATABASE DELETE , MODIFY RECORDS IN A
DATABASE .
SESSION -7 FORM WIZARD
Q.54 ) WHAT IS A FORM ?
ANS) In a database context, a form is a window or screen that contains numerous fields, or spaces to
enter data. Each field holds a field label so that any user who views the form gets an idea of its contents.
A form is more user friendly than generating queries to create tables and insert data into fields.
Q.55) WHAT ARE THE USES OF FORMS ?
ANS ) A form in a database object that you can use to create a user interface for a database application.
A form is one that is directly connected to a data source such as a table or query, and can be used to
enter, edit, or display data from that data source.
SESSION -8 REPORT WIZARD
Q.56) WHAT ARE REPORTS ?
ANS) REPORT IS THE FORMATTED RESULT OF DATA COMING FROM DATABASE OBJECTS LIKE TABLES OR
QUERIES. REPORTS ARE VERY USEFUL TOOLS FOR DECISION-MAKING AND ANALYSIS .
Q.57) HOW ARE REPORTS USEFUL ?
ANS) REPORTS WILL PROVIDE IMPORTANT DETAIL THAT CAN BE USED TO HELP DEVELOP FUTURE FORECASTS, MARKETING
PLANS, GUIDE BUDGET PLANNING AND IMPROVE DECISION-MAKING. MANAGERS ALSO USE BUSINESS REPORTS TO TRACK
PROGRESS AND GROWTH, IDENTIFY TRENDS OR ANY IRREGULARITIES THAT MAY NEED FURTHER INVESTIGATION .
SQL COMMANDS OR QUERY
Employee_ info
Employeeid Employee Emergency Phone no. Address City Country
Name Contact
name.
01 Shanya Abhinay 9889765112 Maharajgung KTM Nepal
02 Anay Soumya 9854321090 Teku KTM Nepal
03 Preeti Rohan 9851100676 Balkhu KTM Nepal
Create :- Create table Employee_infoDrop :- To
delete
Syntax:- Drop database employee:- Complete information present inthe database will be
lost.
Drop table tablename:- Complete information present in the table willbe lost.
Truncate:- Truncate table employee_info:- Your information will belost, but not the
table.
Alter: - This statement or command is used to add,delete,modifycolumns in an
existing table .
Syntax:- Alter table employee_info add bloodgroup varchar(255)
DDL :-(Data definition language)
It is a standard for commands that define the different structures in adatabase. DDL
statements create , modify and remove database objects such as tables, indexes and users
.
Common DDL statements are :-
1. Create
2. Alter
3. Drop
DML:- (Data manipulation language )Common
DML statements are:-
1. Insert :- Used for insertion of new information into the database
2. Select :- Used for retrieval of information from the database .
3. Delete :- Used for deletion of information in the database.
4. Update :- Used for modification of information in the database .
Example
Insert :- This statement is used to insert new records into the table.
Insert :- Insert into employee_info
Values (‘02’, ‘anay’ , ‘soumya’, ‘9854321090’, ‘teku’, ‘KTM’,
‘Nepal’);
Select :- This statement is used to select data from a database ordisplay the data .
Select Employeeid, EmployeeName, from Employee_info(*) is used to
select all from the table
Select * from Employee_info ;
Delete :- This statement is used to delete the existing records in atable.
Delete from Employee_info Where
EmployeeName=’preeti’;
Update:- This statement is used to modify the records already presentin the table.
Update Employee_info
Set EmployeeName =’Aahana’, city=’KTM’, where Employeeid=1;
Student
Rollno Name Class Phone
101 Ajay 10 9887654321
102 Tarun 9 9867543211
103 Ravi 8 9851003456
104 Vicky 9 9841567843
105 Navin 10 9876543210
Write queries to insert a new record
(107,aayush,8,9810305678)
1.Ans) Insert into student (Rollno,Name,Class,Phone)Values
(106,aayush,8,9810305678);
Update the record modify name = aman where rollno=1032.Ans)Update
student set name=” aman” where Rollno=103;
Delete the record 106
3.Ans) Delete from student where rollno=106;
Display all records from student4.Ans)
Select * from student ;
Display rollno, name from student 5.Ans)
Select rollno, name from student;
Display name from student where class = 10 6.Ans) Select
name from student where class =10;
Display all records in ascending order
by name .
7.Ans) Select * from student order by
name asc;
Student
Rollno Integer
Name Char(25)
Class Integer
Phone Varchar(10)
Write a SQL query to create above table student
THE END
