DATABASE MANAGEMENT SYSTEM

(OOBase - TABLES, FORMS AND REPORTS)

Database – A database is an organized collection of data.

For example: In a stationary shop detailed records of the materials available in the shop is
database. Similarly, if in a computerized system, we need to maintain several files, we
would use database programs such as Microsoft Access, OpenOffice.org Base, and
MySQL. These database programs are used to organize the data as per our needs in the
computer system.

Database Management System (DBMS) - A database management system is a

software package with computer programs that controls the creation, maintenance and
use of a database. A DBMS allows different user application programs to concurrently
access the same database. Some of the DBMSs are Oracle, IBM DB2, Microsoft SQL
server, Microsoft Access, PostgreSQL, MySQL, FoxPro and SQLite.

Advantages of Database
Reduces Data Redundancy: no chance of encountering duplicatedata
Sharing of Data: the users of the database can share the dataamong themselves.
Data Integrity: Data integrity means that the data is accurate andconsistent in the
database.
Data Security: Only authorised users are allowed to access the database and their
identity is authenticated using a username andpassword.
Privacy: The privacy rule in a database states that only the authorized users can access
a database according to its privacyconstraints.
Backup and Recovery: Database Management Systemautomatically takes care of
backup and recovery.
Data Consistency Data Consistency means there should be multiple mismatching
copies of the same data.

Data can be organized into two types:

Flat File: Data is stored in a single table. Usually suitable for less amount of data.
Relational: 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.
Database Servers – Database servers are dedicated computers that hold the actual
databases and run only the DBMS and related software.
Databases 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 Client-server model.

1
RDBMS: A relational database management system (RDBMS) is a database management
system that is based on the relational model. In the relational model of a database,
all data is represented in terms of tuples (rows), grouped into relations (tables). A
database organized in terms the relational model is a relational database.

Database Concepts: Database contains objects that are used for storing and managing
information.
1. Item: Item is about which information is stored in thedatabase.
2. Field: Each question that we ask about our item is a Field.
3. Record: Record is a set of information (made up of fields)stored in your
database about one of the items.
4. Value: Value is the actual text or numerical amount or datethat you put in
while adding information to your database.
For example, Database: Employee
Emp_Code Emp_Name Emp_Address Emp_Designation Emp_ContactNo Emp_Salary

E001 ABC Meerut Manager 9876543210 50000

Field: Emp_Code, Emp_Name, Emp_Address, Emp_Designation,Emp_ContactNo,

Emp_Salary
Record:
E001 ABC Meerut Manager 987654321 50000

Value: E001, ABC, Meerut, Manager, 9876543210, 50000

5. Key Field: Key Field is a value in a Field that uniquely identifies the
record. Eg. E001 which is unique to every employee.

Q How data is organized in a RDBMS?

Ans: In RDBMS, data is organized in the form of inter- linked tables.

TABLE: A table is a set of data elements that is organized using a model of

vertical columns and horizontal rows. Each row is identified by a unique key index
or the key field.

2
 COLUMNS OR FIELD: A column is a set of data values of a particular simple type,
one for each row of the table. For eg. Emp_Code, Emp_Name, etc.

ROWS OR RECORDS OR TUPLES: A row represents a single, data items in a table.

Each row in a table represents a set of related data, and every row in the table has
the same structure.

DATA TYPES: Data types are used to identify the type of datawe are going to
store in the database.

Categories of data types: Data types can be broadly classifiedinto five

categories:-
1. Numeric Types
2. Alphanumeric Types
3. Binary Types
4. Date Time
5. Other variable Types

NUMERIC TYPES: They are used for describing numeric valueslike mobile
number, age, etc.
The different types of numeric data types available are-
1. Boolean (Yes / No) 6. Numeric
2. TinyInt (Tiny Integer) 7. Decimal
3. SmallInt (Small Integer) 8. Real
4. Integer 9. Float
5. BigInt (Big Integer) 10. Double

ALPHANUMERIC TYPES:
The list of different data types available in alphanumeric typesare:
1. LongVarChar (Memo) (Long Text)
2. Char (Text-fix) (Small Text)
3. VarChar (Text) (Text of specified Length)
4. VarChar_IgnoreCase (Text) (Comparisions are not casesensitive)

BINARY TYPES:
Binary types are used for storing data in binary formats. It can be used for storing
photos, music files or (in general file of any format) etc.
The list of different datatypes available in Binary types are:
1. LongVarBinary (Image)
2. Binary (Binary fix)
3. VarBinary (Binary)

3
DATE TIME:
Date time data types are used for describing date and time values for the field used in
the table of a database. It can be used for storing information such as date of birth,
date of admission etc.
The list of different data types available in Date Time type are:
1. Date (Stores month, day and year information)
2. Time (Store hour , minute and second information)
3. Timestamp (Stores date and time information)

PRIMARY KEY: A primary key is a unique value that identifies a row in a table. These
keys are also indexed in the database, making it faster for the database to search a
record.

FOREIGN KEY: 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.

CREATING DATABASE USING OPENOFFICE BASE

 To open OpenOffice, click Start>Programs>OpenOffice.org 4 >OpenOffice.org.

4
 Select the option database to open the base application.
 Click Start>Programs>OpenOffice.org 4>OpenOffice.org Base.

 You can create a new database by selecting the option Create a new database.
 You can also open an existing database file that you have already created by selecting the
option Open an existing database file.
 Click Next.

5
 Click Finish. The Save As dialog box appears as shown below.

 Specify a name for the database in the File name: field and click Save. A window similar
to the one displayed below.
 Click Finish. The Save As dialog box appears.

 Specify a name for the database in the File name: field and click Save.

6
Steps To Create A Table Using Table Wizard
Tables are the basic building blocks of a database. You store the data in the database in
the form of tables.

There are different ways to create a table:

1. Create table in Design View
2. Use Wizard to Create Table

Use Wizard To Create Table:

To create a table:
 Click on Tables > Use Wizard to Create Table

7
 Click the Select Fields > Choose Category > Select the table > Click on Next Button.

 Select the fields as per the requirements and click on > button to add the selected
column or select to remove the fields from the Selected Fields Box.
 After selectingthe fields click on Next Button.

8
  The window to set the data types will open, By default all the fields will have
Text[VARCHAR] data type which can be changed and other properties can be
selected as per the requirement.
 Click on Next.

 After setting the properties, the window to set the primary will open. Select the
column to be set as Primary Key.
 Click on Next.

9
  The window to rename the table will open. A user can either use the same table name or
can change it.
 The option to insert the data immediately will be selected by default. Click on Finish to
insert the data in the table.

Creating table using Design View

 Click on Create Table in Design View… option. Table Design window appears.

 Specify the field name and data type of the field to be created, by selecting the
appropriate type available under Field type drop down list.
 Specify the field name and the data type for each field name.
 After specifying the field name and the datatype, save the table by clicking on
File > Save option.
10
 Specify the table name. The default name is Table1. Click OK.

 A dialog box appears, where you are asked to set a primary key for the table created.
You can select the appropriate option to set the primary key or leave the table without
a primary key.

11
Data Types:
Datatypes are used to identify which type of data (value) we are going to store in the
database.
Fields themselves can be of different types depending on the data they contain. Data types
in OpenOffice base are broadly classified into five categories listed below.
• Numeric Types
• Alphanumeric Types
• Binary Types
• Date time
• Other Variable types

Numeric Types:
Numeric data types are used for describing numeric values for the field used in the table
of a database.
The different types of numeric data types available are listed here.

12
Alphanumeric Types:

Binary Types:
Binary data types are used for storing data in binary formats. Binary data types in a
database can be using for storing photos, music files, etc. In general, files of any format
can be stored using the binary data type.
The different types of binary data types availableare listed here.

DATE TIME Types:

Date time data types are used for describing date and time values for the field used in the
table of a database. Date time data types in a database can be used for storing information
such as date of birth, date of admission, date of product sale, etc.
The different types of date time data types available are listed here.

13
Inserting Data in the Table
To insert the data in the table, follow the steps:
 Select the table > Double click on it.
 The table will open in Datasheet View in which data new data can be inserted and
existing data can be updated or removed.

Editing Records In The Table

 To edit the data either click on edit icon or double on the data in the cell of
a tableand modifications can be done.

14
Deleting Records From The Table

 To remove the data from the table, follow the steps:

 Select the data > right click on selected data > select the Delete option.

Field Properties
To set the field properties:
Select the table > Right click > Select Edit > Table Design View window will open.
Some commonly used properties are:
AutoValue – If set to yes then field will get the auto numeric values.
Length – By default length of the field is 10 but the size of the field can be
set to maximum length.
Default Value – A default value can be set for a field if user don’t provide any value
while entering the values in the table.
Format example – This property helps to set the format of the data entered in the
field.
Entry Required – if set to yes then it will be must to insert the value in the
field which means that field cannot be left blank.

FORMS
A form provides the user a systematic way of storing information into the
database. It is an interface in a user specified layout that lets users to view,
enter, and change data directly in database objects such as tables.
15
Creating Form Using Wizard
 To create a form, Click on Forms option located under Database section.

Steps To Create Form Using Wizard

 Click Use Wizard to Create Form… option. The Form Wizard dialog box
appears.

 You can select fields to be sent onto the form by selecting the field name and
clicking > button. You can select individual fields in a database or all fields in a
database.To use all the fields in the table in a form, click the >> button.
 Click Next.

16
 You see the Set up a sub form step dialog box of the wizard.

 You can select the option Add Subform, if you need to insert the contents in the table
in a separate form. Click Next.

 Now you need to arrange selected fields in a form. You can use different styles
from the list displayed.

 Once you have selected a style, click Next.

17
 A dialog box appears wherein you can select the data entry model. Click Next.

 You should see a dialog box wherein you can specify the styles to be used in the
form. Click Next.

18
 You see a dialog box where you can specify the name of the form. Click Finish.

 A form window appears. The records in the table will be displayed automatically
within the form created.

19
Enter Data from Form
 You can add new records to the table using the form by clicking the symbol.

Reports
A report helps to display the data in a summarized manner. It is used to generate the
overall work outcome in a clear format. You can create reports in the database.

Creating Reports using wizard

To create reports in Base, the table must be selected from which data can be displayed in a
format as required.

Steps to create Report:

 Click on Reports section under Database in the OpenOffice base application.

 Now we can generate the report for the table created earlier.
 Click on Use Wizard to Create Report… option available under Tasks.

20
 You have to select all the table fields by selecting the >> button

 You can redefine the label of the fields in the reports or else you can set the default
name. Click Next.

21
 You can define grouping for the fields of the table. Click Next.

 To sort the field variables in the report, select the appropriate field and sorting method.
Click Next.

22
 You can select the layout of the report by selecting the appropriate option available
under the Layout of data list and you can also select the orientation of the report.
Click Next.

 You can define a name for the report.

 Click Finish.

23