Database Management System (DBMS) [ SEE COMPUTER SCIENCE 2079 ]

 SEE COMPUTER SCIENCE 2079                                                Database Management System (DBMS)            

 

Database

A database is an organized collection of data that is designed to efficiently store, manage, and retrieve information.

E.g. Dictionary, Marks Ledger, Telephone Directory etc.

 

DBMS (Database Management System)
A DBMS is a software that manages databases, allowing users to store, access, and manipulate data in an organized and secure way.
E.g.: MS-Access, Oracle, Fox pro, Dbase etc.

RDBMS (Relational Database Management System)
RDBMS is a type of DBMS that uses a relational model to organize and manage data, allowing users to easily retrieve and manipulate it.
E.g.: MySQL, Oracle, MS Access etc.

Advantages of DBMS

a)     It controls data redundancy which means duplication of data.

b)     It allows multiple users to access the same data simultaneously.

c)     Large volume of data can be stored and updated easily.

d)     It provides high security of data as well as maintains accurate database.

 

MS-Access

Microsoft Access is a relational database management system (RDBMS) that allows users to store, organize, and manage large amounts of data.

 

Features of MS-Access

a)     It provides the flexible ways to add, edit, delete and display the related data.

b)     Queries help to view, change and analyse the data indifferent ways.

c)     Forms are used for viewing and editing the information.

d)     Reports are used for summarizing and printing the data.

 

Objects of MS-Access

·       Tables: Used to store data in a structured format with columns and rows.

·       Queries: Used to retrieve and manipulate data from one or more tables based on specified criteria.

·       Forms: Used to create user-friendly interfaces for entering and viewing data in tables.

·       Reports: Used to generate formatted and organized views of data from tables or queries.

 

Computerized Database vs Non-Computerized Database

Computerized Database	Non-Computerized Database
It can store large volume of data. It is very fast to find a specific record.	It is limited by physical storage available.
The database can be kept secure by use of passwords.	The only security would be locking up the records.
We can search data very easily.	It can take a while to manually search through all of the records.
Data can be sorted into ascending or descending order on multiple criteria.	Difficult to sort data on more than one criteria.

 

 

 

Data

·       Data refers to any collection of raw facts, figures, or other information that can be processed or analyzed.

·       Data measured in bits and bytes.

·       Data never depends on information.

·       E.g.: Ram, student, 20, etc.

 

Information

·       Information is processed or organized data that has meaning and context for its users.

·       Information is measured in meaningful units like time, quantity, etc.

·       Information is dependent on data

·       E.g. “Ram is a student. He is 20 years old.”, is information that gives a complete sense.

 

Table.

Table is a database object that stores a collection of related data organized in rows and columns, where each row represents a record and each column represents a field or attribute of that record.
The different ways to create table are:

·       Table Design View

·       Table Wizard

Importance of table

·       Tables store and organize data efficiently, reducing data redundancy and inconsistencies.

·       Tables enable the use of data validation rules to ensure data accuracy and completeness.

·       Tables are essential for creating forms, queries, and reports that extract useful insights and information from data.

·       Tables provide security features to restrict access to sensitive data and ensure data privacy.

 

Form

Form is a database object that provides a graphical user interface for users to view, enter, and edit data from one or more tables, queries, or other data sources.

Methods to create a form are:
Form Design View

·       Form Wizard

Importance of form

·       Forms provide an easy-to-use interface for data entry and manipulation, improving data accuracy and completeness.

·       Forms can include validation rules to ensure data quality and prevent errors.

·       Forms enable users to customize the look and feel of data input screens.

·       Forms can be linked to other objects, such as tables or queries, to simplify data management.

 

Query

Query is a request for information from a database, used to retrieve and display data based on specified criteria, such as filtering, sorting, or calculating values.
Methods to create a query are:
Query Design View

Query Wizard

 

The different types of query are:
i) Select query
ii) Action query
In action query, we have update query, append query, delete query and make-table query.

 

Advantages of query

·       Queries enable users to retrieve specific data from tables or other data sources, based on specified criteria.

·       Queries allow users to filter, sort, and group data in meaningful ways.

·       Queries enable users to perform calculations and aggregate functions on data to extract useful insights.

·       Queries can be used to create reports and forms that extract meaningful information from data.

 

Select query

A select query is a type of query used to retrieve and display data from one or more tables or queries, based on specified criteria or selection rules, such as filtering or sorting.

Action query

An action query is a type of query used to perform a specific action on a set of records, such as adding, deleting, updating, or appending records in a table or query based on specified criteria or selection rules.

 

Update query

An update query is a type of action query used to modify or update existing data in a table or query based on specified criteria, such as changing values in a field or updating multiple records at once.

 

Append query

An append query is a type of action query used to add records from one or more tables or queries to another table, based on specified criteria or selection rules.

 

Delete query

A delete query is a type of action query that removes specific records or all records from a table.

 

Make table query

A make-table query is a type of query used to create a new table based on the results of a select query.

 

Report

Report is a formatted presentation of data from one or more tables or queries, designed for printing or sharing.

The data sources for report are table and query.
The methods of creating report are:
Methods to create a form are:
Report Design View

Report Wizard

Why is Report created?

Reports are created in MS Access to present data from one or more tables in a formatted and organized manner, allowing users to easily view and analyze the information.

 

Importance of report

·       Reports provide a formatted presentation of data that is easy to read and understand.

·       Reports enable users to summarize, analyze, and visualize data in meaningful ways.

·       Reports can be exported or printed for sharing with others.

·       Reports can be based on tables, queries, or other reports, providing flexibility and ease of use.

Data type  

Data type is an attribute for a field that determines the type of data that can be stored in that field.

 

·       Text: Used for storing alphanumeric text values such as names, addresses, and descriptions.

·       Memo: Used for storing large blocks of text or lengthy descriptions.

·       Number: Used for storing numeric data such as integers, decimals, or currency values.

·       Auto number: Unique value generated by Access for each new record. (4 bytes)

·       Date/Time: Used for storing dates and times. (8 bytes)

·       Yes/No: Used for storing boolean (true/false) values. (1 bit)

·       Attachment: Used for storing files, such as documents or images, within the database.

·       Hyperlink: Used for storing URLs or links to other documents or web pages.

·       Lookup: Used for storing a value from a predefined list of values.

·       OLE Object: Used for storing objects from other applications, such as Excel spreadsheets or Word documents. (1 GB)

 

What happens when we enter text in a numeric field?
Ans: an error message is usually displayed, indicating that the data type does not match the expected format.

Text

Text data type is used for storing alphanumeric text values with a maximum size of 255 characters.

The default size of text field is 50.

Memo

Memo data type is used for storing large blocks of text or lengthy descriptions up to 65,536 characters.

 

Field properties

·       Field Name: The name of the field, which should be unique within the table.

·       Field Size: The maximum size of the data that can be stored in the field.

·       Format: The format for displaying the data in the field, such as currency, date/time, or text.

·       Input Mask: A pattern that controls the format of the data as it is entered into the field, such as a phone number or ZIP code.

·       Validation Rule: A rule that specifies the valid values or ranges of values that can be entered in the field.

·       Validation Text: A message that appears if a user enters invalid data in the field.

·       Default Value: The default value that is entered into the field if no other value is specified.

·       Indexed: Specifies whether the field is indexed for faster searching and sorting.

·       AutoNumber: Specifies whether the field automatically generates a unique number for each record.

·       Caption: Specifies a descriptive label for the field, which can be displayed in forms and reports instead of the field name. It can contain up to 2048 characters.

Relationship

Relationship is a connection between two tables that is established using a common field or fields, such as a primary key and a foreign key. Its types are:
i)One to one relationship
ii)One to many relationship
iii)Many to many relationship

Record

Record is complete set of information. Record is a complete set of information that contains data values for all the fields in a table, represented by a row in the table. E.g.: Ram Chabahil 4468790

Field

Field is a small unit of information. Field is a column in a table that stores specific information, such as a name, date, or number. E.g.: Name, Address, Telephone

Sorting
Sorting refers to the process of organizing data in a specific order (ascending or descending) based on one or more fields in a table, query, or report.  Sorted data is easier to handle than unsorted data.

·       The advantages of sorting are:
Sorting helps to organize data and make it easier to find and retrieve specific information.

·       Sorting can save time and improve efficiency by allowing users to quickly access the data they need.

 

Filtering

Filtering is an option that selects the required records and displays the result.

 

Primary key

A primary key is a field or combination of fields in a table that uniquely identifies each record, and is used to establish relationships between tables and enforce data integrity.

 

Uses of Primary key
i) To reduce and control duplication of record in a table.
ii) To set the relationship between tables.

 

Composite key

The group of primary key that consists of two or more attributes is called composite key.

 

Foreign Key

A foreign key in MS Access is a field that establishes a relationship between two tables by referencing the primary key of another table.

 

Data Redundancy

Data redundancy is the duplication of data within a database.

Data redundancy can be controlled by normalizing the database, eliminating duplicate data and storing it in separate tables, and using foreign keys to establish relationships between the tables.

 

While designing table structure which data types are suitable to store information about teacher’s name, address, salary and date of birth.
Teacher’s name - Text
Address – Text
Salary – Currency
Date of birth – Date/Time

Identify a record, field and value from the following table structure. [SQE 2074]

Roll        Name               Marks

1             Kopila                 87

2             Rabin Rana         56  

Ans: Record = 1  Kopila  87, 2     Prabin Rana    56

Field = Roll, Name, Marks

Value = 1, 2, Kopila, Prabin Rana, 87, 56

List the field name from the following table structure.

Symbol No.	Name	Marks
00100200Q	Surya Gurung	85
00100201R	Birendra Sharma	91

Field names are : Symbol No., Name and Marks

 

1.     Which data type is used to store date of birth of an employee in MS Access?

2.     Which data type of MS Access is used for data that can store only one of two possible values?

3.     Which data type is also known as logical data type in MS-Access?

4.     Which data type consumes the least storage space?

5.     Which of the following data type is appropriate to store gender of person in terms M or F?

6.     Which data type of MS-ACCESS is used to store photo, video?

7.     Which data type used for lengthy text and numbers?

8.     What is the default extension of MS-Access?

9.     What is the memory space consumed by a currency data type?

10.  What is the memory space consumed by  auto number data type?

11.  What is the memory space consumed by date/time data type?

12.  What is the field size of logical field data type?

13.  What is the maximum size of text field in MS-Access?

14.  What is the default size of text data type in MS Access?

15.  What is the default data type of MS-Access?

16.  Which  data type is appropriate to store salary

17.  Which key uniquely identifies each record?

18.  Which view is to used to modify a table structure in Ms-Access?

19.  Which object of MS-Access is used to retrieve date from the table?

20.  Which query changes value in main table?

21.  Which query is used to view the data stored in database?

22.  Which object of MS-Access that is used to generate hard copy of records?

23.  Which object of MS-Access that is used to store  records in MS Access?

24.  List any four components of database management.

25.  Name any four objects of Ms-Access.

26.  Name any four data types of Ms Access

27.  Write any two examples of database

28.  Write any two examples of DBMS.

29.  In how many ways we can create a table?

30.  List the types of query.

 

31.  What is database?

32.  Define DBMS.

33.  Define RDBMS.

34.  Define MS Access.

35.  List any two advantages of DBMS.

36.  What are validation text and validation rule in MS-Access?

37.  What is table in MS Access?

38.  Why table is called primary object of Ms -Access?

39.  What is query?

40.  List any two advantages of query.

41.  What is action query?

42.  Define form.

43.  Write the importance of form.

44.  What is report?

45.  Mention the uses of report.

46.  Why is report created?

47.  What is primary key?

48.  List any two of its advantages of primary key.

49.  What is data redundancy? How can it be reduced in database?

50.  Differentiate between Primary key and foreign with example.

51.  What is data sorting?

52.  List any two advantages of sorting.

53.  Differentiate between fields and records with example

1.     While designing table structure which data types are suitable to store information about teacher’s name, address, salary and date of birth.

2.     Identify a record, field and value from the following table structure.

Roll        Name               Marks

1             Kopila                 87

2             Rabin Rana         56  

 

Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will spend its whole life believing that it is stupid.” ― Albert Einstein