Unit 3 Reletional Database Management Systems || Solved Exercise || Class-10 || IT 402

 

INFORMATION TECHNOLOGY-402

CLASS-X

SESSION-2020-21

UNIT-3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC)

Book Exercise

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

Extra Questions :

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 simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. For example, cFirstName, or cLastName are fields in a row.	A row also called a Record or Tuple represents a single, data item in a table. In simple terms, a database table can be visualized as consisting of rows and columns or fields. Each row in a table represents a set of related data, and every row in 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.