Computer Class 11 GSEB Solutions Chapter 9 Introduction to Database Management System

Students frequently turn to Computer Class 11 GSEB Solutions and GSEB Computer Textbook Solutions Class 11 Chapter 9 Introduction to Database Management System for practice and self-assessment.

GSEB Computer Textbook Solutions Class 11 Chapter 9 Introduction to Database Management System

Question 1.
Explain the terms Data and Information with example.
Answer:
Data and Information

• All kinds of facts, figures and details related to people, places, things or events is known as Data.
• Data may be in any form.
• Data can be written, oral, computerized or non-computerized.
• Data in not much useful in its original form.
• Data must be processed in a proper way to make it useful and meaningful.
• Data, being useful for decision-making process, is considered as an important asset of any business.
• Information can be described as the required result obtained from processing of the Data.
• Information, in other words is “Processed Data”
• Information is important as it forms the foundation for Decision Making.
• Correct and relevant Information helps in taking timely decisions.
• On the other side, taking decisions based on incomplete and incorrect information may lead to bad effect on work.
• Suppose you and your team in a school want to send birthday wishes to each student of the school. To do this, one will require a collection of birthdays like 10th September 1998. The Dates collected are the Data. And if today is 10th September, you will have to search for all the names of students whose birthday falls on this date. Therefore the collection of names in your final list will now act as information.

Question 2.
Explain the need of information.
Answer:
Information System

• In the current state of affairs with the arrival and reach of digital technologies, almost all data is stored and managed electronically.
• Day to day transactions in small, medium and large enterprises are recorded in systems termed as information systems.
• Users of information system are growing multifold.
• Users expect flexibility in accessing data irrespective of the places they are at and irrespective of where the data is stored.
• Organizations now being global, information systems are not now limited to storage and maintenance of enterprise transactions which were understood as replacement of registers/files maintained in organization.
• Search engines, reservation systems, online banking, online tax filing, stock trading are examples of systems used by millions of users simultaneously.
• All of us directly or indirectly act as users of one or other information systems.
• Doctors, engineers, shopping store owners, broking agents, educators and students, none of the entities
  have remained untouched.
• Information systems today has also become a strong helping hand in taking decisions.
• To take best possible decision, everyone depends on the accuracy of information available.
• Information is the output generated through processing of raw data.
• Below given details we will be able to learn on the two important terms: Data and Information.

Question 3.
Define the terms database, data, model and Database Management System.
Answer:
Database and Data Model

• We all use different types of databases in our daily lives.
• We maintain a diary containing addresses and phone numbers.
• A school maintains details of all the students enrolled or even an attendance record of every student.
• To maintain a record of all the citizens of the country, the Indian government is issuing unique identity
  numbers to each citizen under Adhar project.
• Passport office maintains details of the issued passports.
• Doctors maintain details of the medical history for each patient.
• The railway department maintains the details of all the passengers.
• Database is a collection of related data items stored in an organized manner.
  • The words “related” and “organized are very important in the definition of database.
  • Logical arrangement of things always makes it easier for searching as and when required. For eg: 4
    1. Arranging things in one’s room.
    2. Instructions to keep one’s belongings in a proper place.
    3. Packing of school bags.
    4. A book shelf being given a label for identification like Science, GK etc., which makes it easier to locate a book later.
    5. Arranging one’s clothes systematically in a wardrobe.
  • However, the place allotted for each item would be of a different structure. Like a store place of jewellery or utensils or books has different structure.
  • So, this means, that depending on the things to be stored, structure of shelves has to be designed.
  • Similarly, databases are designed according to certain rules.
  • The underlying structure of database is known as data model.
  • Data model describes way of storing and retrieving data.
  • Here are different data models like hierarchical data model, network data model and relational data
    model.

Database Management Systems

• To work with computerized database, one has to first define and create a database.
• To keep the database ready with latest data, few of the following tasks are to be performed regularly.
  1. Adding data into the database as and when transaction takes place.
  2. Editing the content of the database with the corrections required as and when noticed.
  3. Removing the unnecessary data from the database, if any.
  4. Organizing the database in a proper manner.
  5. Retrieval of data as per user requirement.
  6. Securing data stored in database.
  7. Maintaining backup of database.
  8. Allowing multiple users to access database simultaneously.
  9. Allowing data to be stored in multiple languages.
  10. Allowing data to be stored at multiple locations.
• All the above tasks related to the database handling is known as Database Management.
• For all this, special software is required.
• Such software is known as Database Management Systems (DBMS).
• DBMS is a system designed for efficient storage, maintenance and retrieval of data.
• DBMS software makes database practical and more usable.
• A database is useless if there is no DBMS associated with it for accessing its data.
• Oracle, Ingress, DB2, SQL server, MYSQL, MS Access, Base etc., are the names of some popular DBMS software available in the market.
• OpenOffice Base is a Database which is an open source database and is a part of the Open Office suite. Base is based on a Relational data model.

Question 4.
Define Table, Record and Field with example and diagram.
Answer:
Base is a collection of related data objects known as Tables, Queries, Reports and Application Modules. Each of these data objects are explained below in detail.
(1) Table :

• Table is the basic unit for storing data in database designed for using Base.
• Tables are organized in the form of columns and rows.
• Database designer has to create column titles and decide entities in database.
• Entities are real world objects about which information is to be stored in database.
• For example, “Employee” will be the first entity while creating database design for “Employee Attendance System”.
• These details are stored in the form of rows.
• Each entity generally has collection of attributes associated to it. For example, each employee would have attributes like first name, last name, address, date of birth, contact number etc.
• Attributes of an entity are represented in the form of columns.
• Following figure shows how data is represented in relational model.

• In the above table, attributes of an entity are also termed as fields.
• In the Employee Table, First Name, Last Name, Address, City, Birth date, Joining Date, Contact number and Designation are fields.
• Collection of all these fields forms a record.
• Therefore, <<‘Anya’, ‘Gangele’, ’15-shub bunglows, SG road’, ‘Ahmedabad’, ’12-10-1978′, ‘ 15-1-2012’, ‘99988870’, ‘Relationship Manager’ >> is a record.

Question 5.
What are logical field names and physical field names ?
Answer:
Step-2 :

• Create attributes or fields of the four tables.
• Attributes (Fields) can be defined as characteristics of an entity.
• We will store details of suppliers, employees, customers and products as shown in following tables.
  (Supplier)

Attributes	Description
Supplier Name	Name of supplier or the name of the company
Address line 1	Address from where products are dispatched
Address line 2	Street and / or area
City	City from where products are dispatched
Pine ode	Pincode of area within a city
Contact Person	Person to be contacted.
Email id	E-mail address of conact person
Contact Number	Contact number of office

Attributes for Supplier entity

(Employee)

Attributes	Description
Firstname	Name of the Employee
Lastname	Surname of the Employee
Address Line 1	Address of the Employee
Address Line 2	Street and / or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail number of Employee
Mobile Number	Mobile number of Employee
Birthdate	Birthdate of Employee
Joining date	Joining date of Employee
Designation	Current designation of an employee

Attributes for Employee entity

(Customer)

Attributes	Description
Firstname	Name of the Employee
Customer First Name	Name of customer
Customer Last Name	Surname of customer
Address Line 1	Address of customer
Address Line 2	Street or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail address of customer
Mobile number	Mobile number of customer
CardHolder	Whether customer is member of Modern Electronic Store

Attributes for Customer entity

(Product)

Attributes	Description
Product Category	Category of Product like ipad, mobile, smartphone, etc.
Model Name	Name of the Model like Samsung Galaxy III
Supplier Name	Name of the Supplier
Reorder Level	Stock level that triggers purchase order
Selling Price	Current selling price of the product
Quantity in Hand	Stock available
Product Image	Image of Product
Camera Pixel Size	Mega pixels of Camera
Processor	Processor name
OS Support	Name of Operating System supported by Model
Touch Screen	Touch Screen Feature available or not
Wi-fi Enables	Whether the phone is wi-fi enabled
Memory slot	Capacity of Initial slot of memory
FM Support	Whether FM support is support is available or not.
Display Size	Display size in inches
HDD Capacity	Hard disk capacity
Weight	Weigh of the item

Attributes for Product entity

Logical Names and Physical Names of Attributes :

• Logical names are used at the time of designing the data model,
• Logical names do no translate so simply into the names that database software will allow us to use.
• Base allows using a filed as “First Name” but the database applications would only accept “first_name”.
• This limitation is dire to the way particular database software has been designed.
• The name actually used in the internal structure of our tables is called a “physical” name.
• The physical name will always have to confirm to the conventions imposed by the software used.
• Some conventions mentioned below are used to name attributes which help in porting of database design to other database software.
  1. Start all attributes names with a letter.
  2. For subsequent characters use either letters, numbers or underscore character.
  3. Do not use a space between words; instead separate them with the underscore
  4. Do not use special characters except underscore.
  5. Use abbreviations, if required, to keep the length of attribute names short.
• In the sample applications, “camel back” notations have been used.
• Camel back means each word in field name starts with capital letter.
• Example, FirstName, LastName, AddressLinel, etc.

Question 6.
Write down instruction to be followed while deciding a field name.
Answer:
Step-2 :

• Create attributes or fields of the four tables.
• Attributes (Fields) can be defined as characteristics of an entity.
• We will store details of suppliers, employees, customers and products as shown in following tables.
  (Supplier)

Attributes	Description
Supplier Name	Name of supplier or the name of the company
Address line 1	Address from where products are dispatched
Address line 2	Street and / or area
City	City from where products are dispatched
Pine ode	Pincode of area within a city
Contact Person	Person to be contacted.
Email id	E-mail address of conact person
Contact Number	Contact number of office

Attributes for Supplier entity

(Employee)

Attributes	Description
Firstname	Name of the Employee
Lastname	Surname of the Employee
Address Line 1	Address of the Employee
Address Line 2	Street and / or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail number of Employee
Mobile Number	Mobile number of Employee
Birthdate	Birthdate of Employee
Joining date	Joining date of Employee
Designation	Current designation of an employee

Attributes for Employee entity

(Customer)

Attributes	Description
Firstname	Name of the Employee
Customer First Name	Name of customer
Customer Last Name	Surname of customer
Address Line 1	Address of customer
Address Line 2	Street or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail address of customer
Mobile number	Mobile number of customer
CardHolder	Whether customer is member of Modern Electronic Store

Attributes for Customer entity

(Product)

Attributes	Description
Product Category	Category of Product like ipad, mobile, smartphone, etc.
Model Name	Name of the Model like Samsung Galaxy III
Supplier Name	Name of the Supplier
Reorder Level	Stock level that triggers purchase order
Selling Price	Current selling price of the product
Quantity in Hand	Stock available
Product Image	Image of Product
Camera Pixel Size	Mega pixels of Camera
Processor	Processor name
OS Support	Name of Operating System supported by Model
Touch Screen	Touch Screen Feature available or not
Wi-fi Enables	Whether the phone is wi-fi enabled
Memory slot	Capacity of Initial slot of memory
FM Support	Whether FM support is support is available or not.
Display Size	Display size in inches
HDD Capacity	Hard disk capacity
Weight	Weigh of the item

Attributes for Product entity

Logical Names and Physical Names of Attributes :

• Logical names are used at the time of designing the data model,
• Logical names do no translate so simply into the names that database software will allow us to use.
• Base allows using a filed as “First Name” but the database applications would only accept “first_name”.
• This limitation is dire to the way particular database software has been designed.
• The name actually used in the internal structure of our tables is called a “physical” name.
• The physical name will always have to confirm to the conventions imposed by the software used.
• Some conventions mentioned below are used to name attributes which help in porting of database design to other database software.
  1. Start all attributes names with a letter.
  2. For subsequent characters use either letters, numbers or underscore character.
  3. Do not use a space between words; instead separate them with the underscore
  4. Do not use special characters except underscore.
  5. Use abbreviations, if required, to keep the length of attribute names short.
• In the sample applications, “camel back” notations have been used.
• Camel back means each word in field name starts with capital letter.
• Example, FirstName, LastName, AddressLinel, etc.

Question 7.
Write down the steps to design the database.
Answer:
Creating a Sample Database for an Application

• We will now create a small application for a hypothetical Modern electronic Store.
• This store sells devices like Mobiles, Camera, Handy Cams, Laptops, Netbooks, ipads, Smartphones and accessories related to them.
• All the products are procured from various suppliers.
• Store has a physical shop as well as a web shop.
• Thus customers can either purchase items while walking in the shop or place orders online.
• Sales persons are paid special incentives for converting leads to customers.
• The name of this application can be Store Management System.

Step-1 :

• We will store all data pertaining products, suppliers, employees and customers.
• Entities can be defined as any person, thing, place or an object about which we store the data in the database.
• For each entity a separate able has to be designed.
• Thus the tables to be created will be four – Product, Supplier, Employee and Customer.

Step-2 :

• Create attributes or fields of the four tables.
• Attributes (Fields) can be defined as characteristics of an entity.
• We will store details of suppliers, employees, customers and products as shown in following tables.
  (Supplier)

Attributes	Description
Supplier Name	Name of supplier or the name of the company
Address line 1	Address from where products are dispatched
Address line 2	Street and / or area
City	City from where products are dispatched
Pine ode	Pincode of area within a city
Contact Person	Person to be contacted.
Email id	E-mail address of conact person
Contact Number	Contact number of office

Attributes for Supplier entity

(Employee)

Attributes	Description
Firstname	Name of the Employee
Lastname	Surname of the Employee
Address Line 1	Address of the Employee
Address Line 2	Street and / or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail number of Employee
Mobile Number	Mobile number of Employee
Birthdate	Birthdate of Employee
Joining date	Joining date of Employee
Designation	Current designation of an employee

Attributes for Employee entity

(Customer)

Attributes	Description
Firstname	Name of the Employee
Customer First Name	Name of customer
Customer Last Name	Surname of customer
Address Line 1	Address of customer
Address Line 2	Street or area
City	City
Pincode	Pincode of area of above city
Email id	E-mail address of customer
Mobile number	Mobile number of customer
CardHolder	Whether customer is member of Modern Electronic Store

Attributes for Customer entity

(Product)

Attributes	Description
Product Category	Category of Product like ipad, mobile, smartphone, etc.
Model Name	Name of the Model like Samsung Galaxy III
Supplier Name	Name of the Supplier
Reorder Level	Stock level that triggers purchase order
Selling Price	Current selling price of the product
Quantity in Hand	Stock available
Product Image	Image of Product
Camera Pixel Size	Mega pixels of Camera
Processor	Processor name
OS Support	Name of Operating System supported by Model
Touch Screen	Touch Screen Feature available or not
Wi-fi Enables	Whether the phone is wi-fi enabled
Memory slot	Capacity of Initial slot of memory
FM Support	Whether FM support is support is available or not.
Display Size	Display size in inches
HDD Capacity	Hard disk capacity
Weight	Weigh of the item

Attributes for Product entity

Logical Names and Physical Names of Attributes :

• Logical names are used at the time of designing the data model,
• Logical names do no translate so simply into the names that database software will allow us to use.
• Base allows using a filed as “First Name” but the database applications would only accept “first_name”.
• This limitation is dire to the way particular database software has been designed.
• The name actually used in the internal structure of our tables is called a “physical” name.
• The physical name will always have to confirm to the conventions imposed by the software used.
• Some conventions mentioned below are used to name attributes which help in porting of database design to other database software.
  1. Start all attributes names with a letter.
  2. For subsequent characters use either letters, numbers or underscore character.
  3. Do not use a space between words; instead separate them with the underscore
  4. Do not use special characters except underscore.
  5. Use abbreviations, if required, to keep the length of attribute names short.
• In the sample applications, “camel back” notations have been used.
• Camel back means each word in field name starts with capital letter.
• Example, FirstName, LastName, AddressLinel, etc.

Step 3 :

• Decision of what kind of data need to be entered in each attribute, one has to decide on the data type of each field while designing the table.
• It is compulsory to define the data type of each field.
• Data type is a way to define storage structure of the field.
• When building tables in Base, there is a support of a drop-down menu options asking to choose the kind of attributes one wants to store.
• Following tables shows sample records of the created four sample tables.

Question 8.
What is the difference between Text (fix) [CHAR] and Text [VARCHAR] ? Explain giving suitable example.
Answer:
Data types available in Base can be divided into three categories, namely, Alphanumeric type, Calendar type, Binary type.

(1) Alphanumeric Type :

• This type consists of letters, numbers as well as special characters. This can be seen in following table.
• For example, Addresses contain alphabets, special characters as well as numbers. The appropriate data type to be assigned to this field would be alphanumeric type. Text (Varchar) can be selected as data type for Address field.
• The number of bytes to be allocated to Varchar data type depends on the number of characters entered by the user.
• For example, if an address contains 100 characters, 100 bytes would be reserved and if it contains 50 characters only then 50 bytes would be reserved.

• Character data type has different nature. It is used when we are aware of exact number of characters to be entered in the field.
• For example, we can use it in case of license number, passport number as they have fixed number of characters.
• The character data type though has a limitation on number of characters that can be used.
• To store descriptive data which has more than 255 characters, then Memo data type is used.
• This data type allows to store text data up to 64,000 characters.
• Text (Var Char) Ignore case is efficient when the fields are used for searching and it is unsure that whether user will enter searching text in lower case or upper case.
• For example, First Name or Last Name can be stored in Text (Var Char) Ignore case.
• Students have to note that computers have different ways of storing alphanumeric characters.
• For example, there is the ASCII code that needs only one byte to store a character.
• This limits the number of possible characters that can be stored to only 256.
• It falls short for character sets like Gujarati or Hindi characters.
• Unicode standards (which uses more bytes per character) has been now developed which allows for larger number of characters.
• Base stores alphanumeric characters using UTF-8, which is a code that is compatible with both ASCII and Unicode.
• Base uses one or more bytes for each character according to internal calculations.

Numeric Type :

• This data type is used to store numerical information like marks of a student, salary of an
  employee, quantity on hand or balance in the bank account of a person.
• Numeric data type attributes are described by the number of bytes they use and whether they are signed or unsigned.
• These two factors determine the range of possible values they can hold.
• Base offers several types of numeric data attributes, both signed and unsigned that use different amount of bytes.
• A Boolean number consumes the least memory.
• A Boolean number uses one bit and it can be stored as YES/NO type of data.
• A Floating point number stores numbers that have decimal places like 1.715249.
• Floating points numbers are the most memory consuming numbers.
• Floating point numbers are the only ones that can perform divisions with good accuracy.
• Following tables list different numeric data types along with the number of bits/bytes it uses and its
  range.

(2) Calendar Type :

• Calendar data type is used to store calendar information like year, month, day, hour, minute, second and fraction of a second.
• Date type attribute allows storing year, month and day as it is stored in the system through which data is entered.
• Time type attribute stores time of the day: hour, minute and second.
• Timestamp type attribute is used for recording both time and day of an event.
• Table lists name and description of the Calendar data type.

Name	Description
Date	Stores month, day and year information
Time	Stores hour, minute and second information
Timestamp	Stores date and time information

Binary Type Attributes: Used for Storing Date and Time

(3) Binary Type :

• The Binary type attributes can store any information that comes as a long string of zeros and ones.
• Examples are Digitized Images and Sounds.
• Using Base one can store photos of products or employees and sound snippets or voice messages.
• Following table lists the details of the binary data types.

Name	Data Type	Max Length	Description
Image	LongVar Binary	2 GB	Stores an array of bytes(images, sounds, etc)
Binary	Var Binary	2 GB	Sores any array of bytes
Binary(fix)	Binary	2 GB	Stores any array of Bytes

Binary Type Attributes: Used for Storing Images and Sounds

After understanding the data types the user can assign the data types according to the requirement of input data.

Following tables shows data types assigned to each field of Supplier, Product, Employee and Customer Tables.

(Supplier)

Field Name	Data Type
Supplier Name	Text[VARCHAR_IGNORECASE]
Address Line1	Text[VARCHAR_IGNORECASE]
Address Line2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Textfix[CHAR]
ContactPerson Name	Text[VARCHAR_IGNORECASE]
Emailld	Text[VARCHAR]
ContactNumber	Integer

(Employee)

Field Name	Data Type
First Name	Text[VARCHAR_IGNORECASE]
Last Name	Text[VARCHAR_IGNORECASE]
AddressLine1	Text[VARCHAR_IGNORECASE]
AddressLine2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Textfix[CHAR]
Emailld	Text[VARCHAR]
MobileNumber	Text[VARCHAR]
Birth Date	Date
JoiningDate	Date
Designation	Text[VARCHAR_IGNORECASE]

Data Types Assigned to Fields of Supplier and Employee Table

(Product)

Field Name	Data Type
ProductCategory	Text[VARCHAR_IGNORECASE]
ModelName	Text[VARCHAR_IGNORECASE]
SupplierName	Text[VARCHAR_IGNORECASE]
ReorderLevel	Smalllnt
SellingPrice	Integer
QuantityonHand	Smalllnt
Productlmage	Text[VARCHAR]
CameraPixelSize	Integer
Processor	T extfVARCHAR_IGNORECASE]
OSSupport	Text[VARCHAR_IGNORECASE]
TouchScreen	Boolean
Wi-fiEnabled	Boolean
MemorySlotlnGB	Smalllnt
FMSupport	Boolean
DisplaySizelnlnches	Smalllnt
HDDCapacity	Smalllnt
WeighttlnGrams	Smalllnt

(Customer)

Field Name	Data Type
CustomerFname	Text[VARCHAR_IGNORECASE]
CustomerLname	Text[VARCHAR_IGNORECASE]
AddressLine1	Text[VARCHAR_IGNORECASE]
AddressLine2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Text[CHAR]
Emailld	Text[VARCHAR_IGNORECASE
MobileNumber	Text[VARCHAR]
CarHolder	Boolean

Data Types Assigned to Fields of Product and Customer Table
Note : A field containing only numbers and is not being used for calculations or sorting, it is better to use Text data type. Hence Pincode and Mobile number have been assigned Text data type.

• When Base sorts numbers in a Text field, it reads the numbers from left to right instead of right to left.
• While sorting Base will read the right most digit (7 in case of 457) and sorts accordingly.

Step 4 :

• After all the tables have been assigned their respective fields, attributes and data types a Key field has to be decided.
• We have to understand the purpose of the model name given to each product manufactured by a company.
• It helps to distinguish a product from another.
• While buying a product from a shop there is no confusion.
• For example, if we are searching the Employee Table, there may be more than one employee with same names.
• Thus each table should contain at least one Key field which can identify each record of a table uniquely.
• Therefore there is a need to add this new field as an attribute in the Employee table.
• In terms of database or combination of fields capable of identifying each record uniquely is known as Primary key.
• In the Product table there is no value in HDD capacity of Smart phone.
• There could be two reasons for this, either we are not aware of the capacity or that particular attribute may or may not contain any value.
• Such types of values are known as Null value.
• A null value means unknown or missing value.
• Primary key values cannot contain Duplicate values or Null values.
• Above table shows Supplier, Product, Employee and Customer tables.
• The Supplier Table does not have any field that contains a unique value.
• We need to add an extra field Scode in the Supplier table so that each supplier record can be uniquely identified.
• The structure of scode should be decided to make it unique.
• For example the code will have ‘S’ as the first character and the other three characters would represent a number.
• Suppliers of Modern Electronic Store can be identified as S001, S002, S003 and so on.
• Similarly, an attribute Pcode can be added in Product Table, Ecode in Employee Table and Ccode in Customer Table.
• The Model Name of the Product Table can also act as Primary key because it is unique in every product. But the model name consists of variable characters with more than 15 characters for each Product. So if we designate only 8 characters for the product code, then 9999999 products can be identified uniquely (first character would be P).
• Primary key of a table is also used to establish relationships with multiple tables in the database. Defining Primary key is a must for each table.
• A table cannot have more than one primary key.
• Combination of more than one field can serve as primary key. A primary key so designed is known as Composite Primary key.

Question 9.
Define Key field.
Answer:
The field which is capable of identifying each record uniquely is known as keyfied. It will not allow any field remain empty (null value). Its value can never be repeated.

Question 10.
What is primary key ? Explain giving a suitable example.
Answer:
Step 4 :

• After all the tables have been assigned their respective fields, attributes and data types a Key field has to be decided.
• We have to understand the purpose of the model name given to each product manufactured by a company.
• It helps to distinguish a product from another.
• While buying a product from a shop there is no confusion.
• For example, if we are searching the Employee Table, there may be more than one employee with same names.
• Thus each table should contain at least one Key field which can identify each record of a table uniquely.
• Therefore there is a need to add this new field as an attribute in the Employee table.
• In terms of database or combination of fields capable of identifying each record uniquely is known as Primary key.
• In the Product table there is no value in HDD capacity of Smart phone.
• There could be two reasons for this, either we are not aware of the capacity or that particular attribute may or may not contain any value.
• Such types of values are known as Null value.
• A null value means unknown or missing value.
• Primary key values cannot contain Duplicate values or Null values.
• Above table shows Supplier, Product, Employee and Customer tables.
• The Supplier Table does not have any field that contains a unique value.
• We need to add an extra field Scode in the Supplier table so that each supplier record can be uniquely identified.
• The structure of scode should be decided to make it unique.
• For example the code will have ‘S’ as the first character and the other three characters would represent a number.
• Suppliers of Modern Electronic Store can be identified as S001, S002, S003 and so on.
• Similarly, an attribute Pcode can be added in Product Table, Ecode in Employee Table and Ccode in Customer Table.
• The Model Name of the Product Table can also act as Primary key because it is unique in every product. But the model name consists of variable characters with more than 15 characters for each Product. So if we designate only 8 characters for the product code, then 9999999 products can be identified uniquely (first character would be P).
• Primary key of a table is also used to establish relationships with multiple tables in the database. Defining Primary key is a must for each table.
• A table cannot have more than one primary key.
• Combination of more than one field can serve as primary key. A primary key so designed is known as Composite Primary key.

Question 11.
What are the properties of primary key ?
Answer:
Step 4 :

• After all the tables have been assigned their respective fields, attributes and data types a Key field has to be decided.
• We have to understand the purpose of the model name given to each product manufactured by a company.
• It helps to distinguish a product from another.
• While buying a product from a shop there is no confusion.
• For example, if we are searching the Employee Table, there may be more than one employee with same names.
• Thus each table should contain at least one Key field which can identify each record of a table uniquely.
• Therefore there is a need to add this new field as an attribute in the Employee table.
• In terms of database or combination of fields capable of identifying each record uniquely is known as Primary key.
• In the Product table there is no value in HDD capacity of Smart phone.
• There could be two reasons for this, either we are not aware of the capacity or that particular attribute may or may not contain any value.
• Such types of values are known as Null value.
• A null value means unknown or missing value.
• Primary key values cannot contain Duplicate values or Null values.
• Above table shows Supplier, Product, Employee and Customer tables.
• The Supplier Table does not have any field that contains a unique value.
• We need to add an extra field Scode in the Supplier table so that each supplier record can be uniquely identified.
• The structure of scode should be decided to make it unique.
• For example the code will have ‘S’ as the first character and the other three characters would represent a number.
• Suppliers of Modern Electronic Store can be identified as S001, S002, S003 and so on.
• Similarly, an attribute Pcode can be added in Product Table, Ecode in Employee Table and Ccode in Customer Table.
• The Model Name of the Product Table can also act as Primary key because it is unique in every product. But the model name consists of variable characters with more than 15 characters for each Product. So if we designate only 8 characters for the product code, then 9999999 products can be identified uniquely (first character would be P).
• Primary key of a table is also used to establish relationships with multiple tables in the database. Defining Primary key is a must for each table.
• A table cannot have more than one primary key.
• Combination of more than one field can serve as primary key. A primary key so designed is known as Composite Primary key.

Question 12.
What is Data Type ? List and exaplain the data type available in Base.
Answer:
Data Types Available in Base
Data types available in Base can be divided into three categories, namely, Alphanumeric type, Calendar type, Binary type.

(1) Alphanumeric Type :

• This type consists of letters, numbers as well as special characters. This can be seen in following table.
• For example, Addresses contain alphabets, special characters as well as numbers. The appropriate data type to be assigned to this field would be alphanumeric type. Text (Varchar) can be selected as data type for Address field.
• The number of bytes to be allocated to Varchar data type depends on the number of characters entered by the user.
• For example, if an address contains 100 characters, 100 bytes would be reserved and if it contains 50 characters only then 50 bytes would be reserved.

• Character data type has different nature. It is used when we are aware of exact number of characters to be entered in the field.
• For example, we can use it in case of license number, passport number as they have fixed number of characters.
• The character data type though has a limitation on number of characters that can be used.
• To store descriptive data which has more than 255 characters, then Memo data type is used.
• This data type allows to store text data up to 64,000 characters.
• Text (Var Char) Ignore case is efficient when the fields are used for searching and it is unsure that whether user will enter searching text in lower case or upper case.
• For example, First Name or Last Name can be stored in Text (Var Char) Ignore case.
• Students have to note that computers have different ways of storing alphanumeric characters.
• For example, there is the ASCII code that needs only one byte to store a character.
• This limits the number of possible characters that can be stored to only 256.
• It falls short for character sets like Gujarati or Hindi characters.
• Unicode standards (which uses more bytes per character) has been now developed which allows for larger number of characters.
• Base stores alphanumeric characters using UTF-8, which is a code that is compatible with both ASCII and Unicode.
• Base uses one or more bytes for each character according to internal calculations.

Numeric Type :

• This data type is used to store numerical information like marks of a student, salary of an
  employee, quantity on hand or balance in the bank account of a person.
• Numeric data type attributes are described by the number of bytes they use and whether they are signed or unsigned.
• These two factors determine the range of possible values they can hold.
• Base offers several types of numeric data attributes, both signed and unsigned that use different amount of bytes.
• A Boolean number consumes the least memory.
• A Boolean number uses one bit and it can be stored as YES/NO type of data.
• A Floating point number stores numbers that have decimal places like 1.715249.
• Floating points numbers are the most memory consuming numbers.
• Floating point numbers are the only ones that can perform divisions with good accuracy.
• Following tables list different numeric data types along with the number of bits/bytes it uses and its
  range.

(2) Calendar Type :

• Calendar data type is used to store calendar information like year, month, day, hour, minute, second and fraction of a second.
• Date type attribute allows storing year, month and day as it is stored in the system through which data is entered.
• Time type attribute stores time of the day: hour, minute and second.
• Timestamp type attribute is used for recording both time and day of an event.
• Table lists name and description of the Calendar data type.

Name	Description
Date	Stores month, day and year information
Time	Stores hour, minute and second information
Timestamp	Stores date and time information

Binary Type Attributes: Used for Storing Date and Time

(3) Binary Type :

• The Binary type attributes can store any information that comes as a long string of zeros and ones.
• Examples are Digitized Images and Sounds.
• Using Base one can store photos of products or employees and sound snippets or voice messages.
• Following table lists the details of the binary data types.

Name	Data Type	Max Length	Description
Image	LongVar Binary	2 GB	Stores an array of bytes(images, sounds, etc)
Binary	Var Binary	2 GB	Sores any array of bytes
Binary(fix)	Binary	2 GB	Stores any array of Bytes

Binary Type Attributes: Used for Storing Images and Sounds

After understanding the data types the user can assign the data types according to the requirement of input data.

Following tables shows data types assigned to each field of Supplier, Product, Employee and Customer Tables.

(Supplier)

Field Name	Data Type
Supplier Name	Text[VARCHAR_IGNORECASE]
Address Line1	Text[VARCHAR_IGNORECASE]
Address Line2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Textfix[CHAR]
ContactPerson Name	Text[VARCHAR_IGNORECASE]
Emailld	Text[VARCHAR]
ContactNumber	Integer

(Employee)

Field Name	Data Type
First Name	Text[VARCHAR_IGNORECASE]
Last Name	Text[VARCHAR_IGNORECASE]
AddressLine1	Text[VARCHAR_IGNORECASE]
AddressLine2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Textfix[CHAR]
Emailld	Text[VARCHAR]
MobileNumber	Text[VARCHAR]
Birth Date	Date
JoiningDate	Date
Designation	Text[VARCHAR_IGNORECASE]

Data Types Assigned to Fields of Supplier and Employee Table

(Product)

Field Name	Data Type
ProductCategory	Text[VARCHAR_IGNORECASE]
ModelName	Text[VARCHAR_IGNORECASE]
SupplierName	Text[VARCHAR_IGNORECASE]
ReorderLevel	Smalllnt
SellingPrice	Integer
QuantityonHand	Smalllnt
Productlmage	Text[VARCHAR]
CameraPixelSize	Integer
Processor	T extfVARCHAR_IGNORECASE]
OSSupport	Text[VARCHAR_IGNORECASE]
TouchScreen	Boolean
Wi-fiEnabled	Boolean
MemorySlotlnGB	Smalllnt
FMSupport	Boolean
DisplaySizelnlnches	Smalllnt
HDDCapacity	Smalllnt
WeighttlnGrams	Smalllnt

(Customer)

Field Name	Data Type
CustomerFname	Text[VARCHAR_IGNORECASE]
CustomerLname	Text[VARCHAR_IGNORECASE]
AddressLine1	Text[VARCHAR_IGNORECASE]
AddressLine2	Text[VARCHAR_IGNORECASE]
City	Text[VARCHAR_IGNORECASE]
Pincode	Text[CHAR]
Emailld	Text[VARCHAR_IGNORECASE
MobileNumber	Text[VARCHAR]
CarHolder	Boolean

Data Types Assigned to Fields of Product and Customer Table
Note : A field containing only numbers and is not being used for calculations or sorting, it is better to use Text data type. Hence Pincode and Mobile number have been assigned Text data type.

• When Base sorts numbers in a Text field, it reads the numbers from left to right instead of right to left.
• While sorting Base will read the right most digit (7 in case of 457) and sorts accordingly.

Step 4 :

• After all the tables have been assigned their respective fields, attributes and data types a Key field has to be decided.
• We have to understand the purpose of the model name given to each product manufactured by a company.
• It helps to distinguish a product from another.
• While buying a product from a shop there is no confusion.
• For example, if we are searching the Employee Table, there may be more than one employee with same names.
• Thus each table should contain at least one Key field which can identify each record of a table uniquely.
• Therefore there is a need to add this new field as an attribute in the Employee table.
• In terms of database or combination of fields capable of identifying each record uniquely is known as Primary key.
• In the Product table there is no value in HDD capacity of Smart phone.
• There could be two reasons for this, either we are not aware of the capacity or that particular attribute may or may not contain any value.
• Such types of values are known as Null value.
• A null value means unknown or missing value.
• Primary key values cannot contain Duplicate values or Null values.
• Above table shows Supplier, Product, Employee and Customer tables.
• The Supplier Table does not have any field that contains a unique value.
• We need to add an extra field Scode in the Supplier table so that each supplier record can be uniquely identified.
• The structure of scode should be decided to make it unique.
• For example the code will have ‘S’ as the first character and the other three characters would represent a number.
• Suppliers of Modern Electronic Store can be identified as S001, S002, S003 and so on.
• Similarly, an attribute Pcode can be added in Product Table, Ecode in Employee Table and Ccode in Customer Table.
• The Model Name of the Product Table can also act as Primary key because it is unique in every product. But the model name consists of variable characters with more than 15 characters for each Product. So if we designate only 8 characters for the product code, then 9999999 products can be identified uniquely (first character would be P).
• Primary key of a table is also used to establish relationships with multiple tables in the database. Defining Primary key is a must for each table.
• A table cannot have more than one primary key.
• Combination of more than one field can serve as primary key. A primary key so designed is known as Composite Primary key.

Question 13.
When should one use memo data type ?
Answer:
(1) Alphanumeric Type :

• This type consists of letters, numbers as well as special characters. This can be seen in following table.
• For example, Addresses contain alphabets, special characters as well as numbers. The appropriate data type to be assigned to this field would be alphanumeric type. Text (Varchar) can be selected as data type for Address field.
• The number of bytes to be allocated to Varchar data type depends on the number of characters entered by the user.
• For example, if an address contains 100 characters, 100 bytes would be reserved and if it contains 50 characters only then 50 bytes would be reserved.

• Character data type has different nature. It is used when we are aware of exact number of characters to be entered in the field.
• For example, we can use it in case of license number, passport number as they have fixed number of characters.
• The character data type though has a limitation on number of characters that can be used.
• To store descriptive data which has more than 255 characters, then Memo data type is used.
• This data type allows to store text data up to 64,000 characters.
• Text (Var Char) Ignore case is efficient when the fields are used for searching and it is unsure that whether user will enter searching text in lower case or upper case.
• For example, First Name or Last Name can be stored in Text (Var Char) Ignore case.
• Students have to note that computers have different ways of storing alphanumeric characters.
• For example, there is the ASCII code that needs only one byte to store a character.
• This limits the number of possible characters that can be stored to only 256.
• It falls short for character sets like Gujarati or Hindi characters.
• Unicode standards (which uses more bytes per character) has been now developed which allows for larger number of characters.
• Base stores alphanumeric characters using UTF-8, which is a code that is compatible with both ASCII and Unicode.
• Base uses one or more bytes for each character according to internal calculations.

Numeric Type :

• This data type is used to store numerical information like marks of a student, salary of an
  employee, quantity on hand or balance in the bank account of a person.
• Numeric data type attributes are described by the number of bytes they use and whether they are signed or unsigned.
• These two factors determine the range of possible values they can hold.
• Base offers several types of numeric data attributes, both signed and unsigned that use different amount of bytes.
• A Boolean number consumes the least memory.
• A Boolean number uses one bit and it can be stored as YES/NO type of data.
• A Floating point number stores numbers that have decimal places like 1.715249.
• Floating points numbers are the most memory consuming numbers.
• Floating point numbers are the only ones that can perform divisions with good accuracy.
• Following tables list different numeric data types along with the number of bits/bytes it uses and its
  range.

Question 14.
List the objects available in Base.
Answer:
There are four object available in Base :
(1) Tables,
(2) Queries,
(3) Forms,
(4) Reports

Question 15.
What is Null value ? Explain giving a suitable example.
Answer:
The value which is absent or unknown is known as Null Value. The attribute which is not holding any of the value is the null value. E.g. if we do not know a friend’s birthdate, we might keep that cell empty during data entry which is called null value.

Question 16.
Choose the most appropriate option from those given below:

(1) DBMS stands for
(A) Database Management System
(B) Database Migration System
(C) Data Management System
(D) Direct Base Management System
Answer:
(A) Database Management System

(2) Which of following is a term used for “Raw facts or figures ?”
(A) Information
(B) Variable
(C) Data
(D) Field
Answer:
(C) Data

(3) Which of the following best describes customer in Database ?
(A) Relationship
(B) Attribute
(C) Entity
(D) Data
Answer:
(D) Data

(4) Processed data is known as
(A) fact
(B) prepared data
(C) information
(D) decision
Answer:
(C) information

(5) Which one of the following is not a database ?
(A) MySQL
(B) Base
(C) SQL Server
(D) Spreadsheet
Answer:
(D) Spreadsheet

(6) Which of the following is not an Open source DBMS ?
(A) MySQL
(B) Ingress
(C) Base
(D) Oracle
Answer:
(D) Oracle

(7) The alphabate R in RDBMS stands for which of the following ?
(A) Rotational
(B) Relational
(C) Random
(D) Ring
Answer:
(B) Relational

(8) What is the name of the database available as part of Open Office Suite ?
(A) OfficeDB
(B) Base
(C) OpenDB
(D) Access
Answer:
(B) Base

(9) Which of the following is the default extension of Base Database file ?
(A) .bdf
(B) .odf
(C) .odb
(D) .dbf
Answer:
(C) .odb

(10) Which of the following is not an object maintained by Base ?
(A) Tables
(B) Queries
(C) Charts
(D) Forms
Answer:
(C) Charts

(11) Which data type cannot be used to store numbers ?
(A) Decimal
(B) Integer
(C) Text
(D) Date
Answer:
(D) Date

(12) Which data type is used to store image in Base database ?
(A) Binary
(B) Photo
(C) Long
(D) Huge
Answer:
(A) Binary

(13) Which of the following is false statement ?
(A) Primary key cannot contain null values.
(B) Primary key cannot contain duplicate values.
(C) Primary key can be combination of more than one field.
(D) Primary key is always numeric field.
Answer:
(D) Primary key is always numeric field.

Computer Class 11 GSEB Notes Chapter 9 Introduction to Database Management System

Introduction to Base

• Base is a relational Database Management System (RDBMS).
• It is very easy and efficient to access and manage.
• Base has a versatile way to organize information and store it in tables.
• Any Relational Model is the organization of data into collections of two-dimensional tables called “relations”.
• Relational Model is designed by E.F. Codd and is based on the theory of sets and relations of mathematics.
• A DBMS is based on relational model called RDBMS.
• Relational Model is the most successful data model.
• It is very successful because of its simplicity.
• It is easy to understand the data relationship and very easy to manipulate.

(2) Forms :

• Form is an object which allows entering the data in the table and editing or deleting existing data in the table.
• It consists of format, style and widgets like radio button, list boxes which help in providing easy and systematic way to populate table with data.

(3) Queries :

• A question asked within the database environment is known as Query.
• For example, how many Android based mobiles were sold in July in current year ?
• Query displays subset of data contained in various tables of a database.

(4) Reports :

• The output of Query in Base is displayed in the form of rows and columns.
• The presentation of information in an organized and readable format as per the user requirement is known as Reports.
• Various complex reports can be generated that can help in taking decisions by management in organizations.

Opening Base

• Select Applications → Office → OpenOffice.org 3.2 Base.
• Figure displays the opening screen in Base.

• Earlier created databases can be opened by choosing an option Open an existing database file.
• But here as we have not created any database earlier, we shall choose the option Create a new database.
• The screen will be displayed as shown in following figure.

• Observe the above figure.
• Select the option “No, do not register the database”.
• Also select the check box with option “Create tables using the table wizard”.
• Then click the Finish button.
• The Save As dialog box shown in following figure will now open.

• In the textbox with label File name, where text New Database is visible, type Modern Electronic Store.
• Choose appropriate location to save the file.
• Base will automatically assign .odb extention to the database file.
• Click Save Button and Table Wizard as shown in following figure will be displayed.

• The Table wizard of Base software consists of readymade tables divided into two categories, viz., Business and Personal.
• Business Category consists of sample tables like Tasks, Assets, Contacts, Expenses, Invoices etc.
• Select Business Category and click on drop down list available under Sample tables list.
• Select Supplier. List of fields as shown in following figure will be populated.

• Click on the button to select all the fields that are visible under Available fields list.
• Alternatively one can hold the control key and select only the desired fields one by one.
• Once the selection is done, click on the button.
• But right click on the button so that all the fields are part of the table.
• Click on the Finish button, the suppliers table will be opened in Data View.
• Opening table in Data View allows user to enter records in the table. Following figure shows data view of Supplier table.

• Base opens each table in a separate window.
• Close the Suppliers Data View window.
• Open ModernElectronicStore.odb window.
• The main Database window will be shown as in following figure.

• In the left pane, Database objects viz., Tables, Queries, Forms and Reports can be seen.
• The Tables object icon would be a default selection the Suppliers table is visible under the label Tables.
• Right click on the Suppliers table icon.
• Choose Edit option from the available options as in following figure.

• Suppliers table is opened in Design View as shown in following figure.
• The fields of the table can be edited, updated, or deleted.

• In this view defining each field is associated with four terms as stated below.
  1. Field Name
  2. Field Type : It allows assigning a data type in each field.
  3. Description : To enter description for each of the field, though it is not compulsory to write description for each field of the table. The description helps the user to understand the purpose of each field.
  4. Field Properties : It is used to control and validate that is to be entered. For each data type selected, a list of field properties related to it is displayed. It also has some default selection. All the field properties can be changed according to the requirement.
• Click on the drop down menu arrow in front of Address field in Field Type column, data types as
  shown in above figure will be displayed.
• Choose Text [VARCHAR_IGNORE CASE] data type.
• The Address filed can be renamed to AddressLinel and update its data type.
• In a similar manner, the Products, Employees and Customer table can also be created and modified as per requirement.

Deleting Objects

• To delete any object created in Base, right-click the object icon and click Delete option.
• Delete all the tables including Suppliers table created through the wizard.
• The required tables can be recreated as per the specifications using Design View.
• While creating tables using design view, add Scode, Pcode, Ecode and Ccode field in the Supplier, Product, Employee and Customer table respectively.

Creating Table Using Design View

• When a table is created using wizard, certain changes have to be done to the pre-designed tables, so that it can be used based on the requirement.
• Tables provided by Base usually do not suffice the requirements.
• Therefore, creating tables using Design View is more beneficial.
• In the main Database Window, under Tasks pane, there are two options available for creation of table – Create Table in Design View and Use Wizard to Create Table …. as shown in following figure.

• Click on the option Create Table in Design View and Table Design Window will be opened.
• Table Design View as shown in following figure is grid structure with three columns, field Name, field
  Type and Description and Field Properties pane at the bottom half of the window.

• Create a supplier table discussed again. Type name of the field and select data type corresponding to each field.
• Add Scode as the first field.

Set Primary Key
To designate a field as Primary key, right click on the Scode field to open a sub-menu as shown in figure. Select the Primary key option and a symbol which looks like a key will be displayed in the first column against the chosen field.

Save Table

• Save the created table structure. Click on the Save button(–) visible in above figure.
• The Save dialog box as seen in following figure will be displayed.
• Type the name of the table and click on OK button.

• • Similarly create the tables Product, Employee and Customer.
  • The tables created by us till now are known as master tables.
  • These tables store data that has transactions like, customer purchasing a mobile phone from Modern Electronic Store or details of purchase made by Mobile Electronic Store from its supplier Sony India.
  • Separate tables should be designed to store transactions and relate them with master tables.