UNIT-3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC)

SESSION 1: APPRECIATE CONCEPT OF DATABASE MANAGEMENT SYSTEM

Fill in the blanks:

1. A database is an organized collection of data.
2. A _Microsoft Access, / OpenOffice.org Base,/ MySQL _is a software package that can be used
for creating and managing databases.
3. A _Microsoft Access / OpenOffice.org Base is a database management system that is based on
the relational model.
4.Three popular DBMS software are Oracle, IBM DB2, Microsoft SQL Server, Microsoft Access,
PostgreSQL, MySQL, FoxPro, and SQLite.
5. A Primary Key (PK) is a unique value that identifies a row in a table.
6. Composite Key is a combination of _one or more columns.

Short Answer Questions

Q1) What does DBMS stands for?

Ans: Database Management System (DBMS).

Define : A database management system is a software package with computer programs that
controls the creation, maintenance, and use of a database. It allows organizations to
conveniently develop databases for various applications. A database is an integrated collection
of data records, files, and other objects. A DBMS allows different user application programs
to concurrently access the same database.

Q2) What does RDBMS stands for?

Ans:Relational Database Management System (RDBMS)

Define : A relational database is a type of database. It uses a structure that allows us to

identify and access data in relation to another piece of data in the database. Often, data in a
relational database is organized into tables.

Q3 ) How is data organized in a RDBMS?

Ans: Data is stored in multiple tables and the tables are linked using a common field. Relational is
suitable for medium to large amount of data.

Q4) State the relationship and difference between a primary and foreign key?

Ans: Primary key uniquely identify a record in the table. Foreign key is a field in the table that
is primary key in another table. By default, Primary key is clustered index and data in the database
table is physically organized in the sequence of clustered index.

A foreign key is a column or a set of columns in one table that references the primary key columns in
another table. The primary key is defined as a column (or set of columns) where each value is unique
and identifies a single row of the table
Define :

1 : Flat File:

Data is stored in a single table. Usually suitable for less amount of data.

2 : Relational File :

Data is stored in multiple tables and the tables are linked using a common field. Relational is suitable
for medium to large amount of data.

3 : Database Servers :

Database servers are dedicated computers that hold the actual databases and run only the DBMS and
related software. Typically databases available on the database servers are accessed through
command line or graphic user interface tools referred to as Frontends; database servers are referred
to as Back-ends. Such type of data access is referred to as a client-server model.

4: Data Redundancy :

The database management systems contain multiple files that are to be stored in many different
locations in a system or even across multiple systems. Because of this, there were sometimes
multiple copies of the same file which lead to data redundancy.

5: Data Integrity :

Data integrity means that the data is accurate and consistent in the database. Data Integrity is very
important as there are multiple databases in a DBMS. All of these databases contain data that is
visible to multiple users. So it is necessary to ensure that the data is correct and consistent in all the
databases and for all the users.

6: Data Security

Data Security is an important concept in a database. Only authorised users should be allowed to
access the database and their identity should be authenticated using a username and password.
Unauthorised users should not be allowed to access the database under any circumstances as it
violates the integrity constraints.

7: Data Consistency

Data consistency is ensured in a database because there is no data redundancy. Data Consistency
means there should be multiple mismatching copies of the same data. All data appears consistently
across the database and must be same for all the users viewing the database. Moreover, any changes
made to the database are immediately reflected to all the users and there is no data inconsistency.

8: Primary Key (PK).

A primary key is a unique value that identifies a row in a table. For example, ClientID is the primary
key in the Client table. Primary Keys are also indexed in the database, making it faster for the
database to search for a record.

9: Composite Primary Key

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

10: Foreign Key :

The referred field ClientID which occurs in the Sales table is called the Foreign key (FK). Hence,
the foreign key identifies a column or set of columns in one (referencing) table that refers to a
column or set of columns in another (referenced) table.

SESSION2:CREATE AND EDIT TABLES USING WIZARD & SQL COMMANDS

Fill in the blanks:

1. A table is a set of data elements that is organized using a model of vertical columns(which are
identified by their name) and horizontal rows.

2. A Column or Field or Attribute is a set of data values of a particular type, one for each row of
the table.

3. A Row or Record or Tuple represents a single, data item in a table.

4. Datatypes are used to identify which type of data we are going to store in the database.

5. There are Two (1Create table in Design View , 2. Use Wizard to Create Table ) ways to create
a table.

6. Field properties can be set in both the _design view_ and _datasheet view_ .

Short Answer Questions

Q1) In how many ways tables can be created in Base?

Ans:Two Ways :

1 Create table in Design View

2. Use Wizard to Create Table

Q2) Why are data types used in DBMS /RDBMS?

Ans:Data types in OpenOffice base are broadly classified into five categories listed below.

• Numeric Types
• Alphanumeric Types

• Binary Types

• Date time

• Other Variable types

Q3) List datatypes available in Numeric Datatype?

Ans: The different types of numeric data types available are listed here.

Q4) List datatypes available in Alphanumeric Datatype?

Ans:

Q5) Define the structure of a table.

Ans: A table is a set of data elements (values) that is organized using a model of vertical
columns(which are identified by their name) and horizontal rows. A table has a defined
number of columns, but can have any number of rows. Each row is identified by the values
appearing in a particular column identified as a unique key index or the key field.
Q6) Differentiate between Tuples and Attributes of a table.

Ans:
Columns or Fields or Attributes: Rows or Records or Tuples:
A column is a set of data values of a particular A row also called a Record or Tuple represents a
simple type, one for each row of the table. single, data item in a table. In simple terms, a
database table can be visualized as consisting of
The columns provide the structure according to rows and columns or fields. Each row in a table
which the rows are composed. For example, represents a set of related data, and every row in
cFirstName, or cLastName are fields in a row. the table has the same structure.

Q7) Name different Binary data types.

Ans: The different types of binary data types available are listed here.

SESSION 3: PERFORM OPERATIONS ON TABLE

Fill in the blanks:

1. The types of languages used for creating and manipulating the data in the Database
are __Data Definition Language. __ & _Data Manipulation Language .

2. A _Data Definition Language (DDL) is a standard for commands that define the
different structures in a database.

3. A data manipulation language (DML) is a language that enables users to access and
manipulate data in a database.

4. A query language is a part of DML involving information retrieval only.

5.A popular data manipulation language is Structured Query Language (SQL) .

6. Entity, attribute and relationships are the basic building blocks of a database.

7. There are three specific types of relationships that can exist between a pair
of tables: one-to-one, one-to-many, and many-to-many.
Short Answer Questions:

Q1) What is the file extension for databases created using OpenOffice.Org Base?

Ans: “ odb” is the file extension for the databases which are created
using the OpenOffice org Base.

Q2) List any three file formats that can be managed using OpenOffice.Org Base?

Ans:Three file formats that can be managed using OpenOffice.org base are:

*.odt – This file format is used by OpenOffice.org Writer application for creating
documents. This is the counterpart of .doc extension of MS Word.

*.ods – This file format is used by OpenOffice.org Calc application for creating
spreadsheets. This is the counterpart of .xls extension of MS Excel.

*.odp – This file format is used by OpenOffice.org Presentation application for creating
presentations. This is the counterpart of .ppt extension of MS Powerpoint.

Q3) How many types of relationships can be created in Base? Explain each of the
them.

Ans: There are three types of relationships:

1. One-to-one: Both tables can have only one record on either side of the
relationship. Each primary key value relates to only one (or no) record in the
related table.
In this relationship, both the tables must have primary key columns. Example: In
the given tables EMP and DEPT, EMP_ID in EMP table and DEPT_ID in DEPT
table are the primary keys.
2. One-to-many: The primary key table contains only one record that relates to
none, one, or many records in the related table.
In this relationship, one of the table must have primary key column.

It signifies that one column of primary key table is associated with all the columns
of associated table. Example: In the given tables EMP and DEPT, EMP_ID in EMP
table is the primary key.

3 Many-to-many: Each record in both tables can relate to any number of records (or
no records) in the other table. In this relationship, no table has the primary key
column. It signifies that all the columns of primary key table are associated with
all the columns of associated table.

Example: In the given tables EMP and DEPT, there is no primary key.
Q4) What do you mean by Sorting? In how many ways it can be done?

Ans: Sorting Data : Sorting means to arrange the data in either ascending order of
descending order. Select the column(s) then click on sort buttons. The data will be
displayed accordingly.

Data sorting is any process that involves arranging the data into some meaningful order
to make it easier to understand, analyze or visualize. When working with research data,
sorting is a common method used for visualizing data in a form that makes it easier to
comprehend the story the data is telling.

Q5) Explain Referential Integrity with the help of an example.

Ans: Referential integrity is used to maintain accuracy and consistency of data in

a relationship.

In Base, data can be linked between two or more tables with the help of primary
key and foreign key constraints. Referential integrity helps to avoid:

 Adding records to a related table if there is no associated record available in the

primary key table.  Changing values in a primary if any dependent records are
present in associated table(s).

 Deleting records from a primary key table if there are any matching related
records available in associated table(s).

SESSION 4: RETRIEVE DATA USING QUERY

Fill in the blanks

1. A form helps the user to systematically store information in the database.

2. A form enables users to view, enter, and change data directly in database objects
such as tables.

3. SELECT statement retrieves zero or more rows from one or more database tables or
database views.

4. By default, data is arranged in ascending order. order using ORDER BY clause.

5. UPDATE statement is used for modifying records in a database.

6. DELETE statement is used to remove one or more records in a Database.

Short Answer Questions:

Q1) Name DML commands.

Ans: commands of DML are:

 SELECT – retrieve data from the a database.

 INSERT – insert data into a table.
 UPDATE – updates existing data within a table.
 DELETE – deletes all records from a table, the space for the records remain.
 MERGE – UPSERT operation (insert or update)
 CALL – call a PL/SQL or Java subprogram.
 LOCK TABLE – control concurrency

Q2) What is the purpose of using queries?

Ans: A query 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.

Q3) Which clause of Select statement helps to display specific data?

Ans:The SELECT statement has many optional clauses: FROM specifies which table to get
the data. WHERE specifies which rows to retrieve. GROUP BY groups rows sharing a property
so that an aggregate function can be applied to each group.

Q4) Differentiate between Where and Orderby clause of SQL statements.

Ans: In group by clause, the tuples are grouped based on the similarity between the
attribute values of tuples. Whereas in order by clause, the result-set is sorted based on
ascending or descending order.

Q5) State the purpose of Update Command with the help of an example.

Ans: An SQL UPDATE statement changes the data of one or more records in a table. Either
all the rows can be updated, or a subset may be chosen using a condition. The UPDATE
statement has the following form: UPDATE table_name SET column_name = value [,
column_name = value ...]

SESSION 5: CREATE FORMS AND REPORTS USING WIZARD

Fill in the blanks:

1. To create a form you need to select FORMS_ option available under Database section.

2. A QUERY is helps to collect specific information from the pool of data in the database.

3. data dashboard is used to display the display the summary of data.

4. Graphical user interfaces (GUIs) are the interfaces with which the user interacts.

5. Data from multiple tables can be stored in database. .

Short Answer Questions:

Q1) Why there is a need to create Forms?

Ans: Forms collect information and add to your Contacts database. Either a new lead is
created, or if it's a lead or customer that already exists, any new information that is collected will
be added to the existing contact information. Form information is stored in Contacts and can be
used in your marketing efforts.

Q2) What is the purpose of creating Reports?

Ans: Reports enable you to format your data in an attractive and informative layout for printing
or viewing on screen. Reports are often used to present a big-picture overview, highlighting
main facts and trends. The data in a preview or in a printed report is static.

Q3) What are the prerequisites to create a Form and Reports?

Ans: Forms allow you to both add data to tables and view data that already exists.
Reports present data from tables and also from queries, which then search for and
analyze data within these same tables.

Q4) Differentiate between Forms and Reports.

Ans: Forms are Input to the information system and Reports are output from the
system. Form gathers information for essentially one record of the database. That is, information
about one person or object. On the other hand, Reports can represent information, gathered
from more than one file.

Q5) Can a form displays data from queries?

Ans: A form is a database object that you can use to enter, edit, or display data from a table
or a query. You can use forms to control access to data, such as which fields of data are
displayed. For example, certain users may not need to see all of the fields in a table.

Q6) In how many ways Forms and Reports can be created in a database?

Ans: In three different ways we can create a database.

1. A blank database

2. Create a database by using template

3. Create a database without using template

Explanation:

1. A blank database

This is a better option that we can create a new database with our own
design requirements and we can accommodate our existing data with new database.

2. A database by using template :

It contains a variety of templates and we can use when we need and it includes table,
queries, forms, reports and macros. A templates are used to perform a different
tasks such as manage contacts, track issues and keep records.

3. A database without using template:

If you do not have interest by using templates, you can create a new database
with tables, forms, quires, reports and other objects.

Note : Revise all the SQL Query Commands questions done in class and given in book.