Page 1 :
UNIT 3 Database Management system, Class-10, Database is a collection of information or data in an organized manner. As a student, you maintain, your daily work in your notebook or use any application on the computer to record your daily, activities. Such a collection of information is termed as a database., With the arrival of computers, maintaining database became easier. Computerized database, systems, commonly known as Database Management System (DBMS), also made the processes of, retrieving and updating data from the databases much easier and faster., Commonly used database applications or software are, Oracle, MySQL, Open office base, Microsoft access, Microsoft SQL SERVER, 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. 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., Advantages of Database, • Reduces 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. This is, prevented in a database as there is a single database and any change in it is reflected immediately., Because of this, there is no chance of encountering duplicate data., • Sharing of Data In a database: - the users of the database can share the data among themselves., There are various levels of authorisation to access the data, and consequently the data can only be, shared based with the authorized users. Many remote users can also access the database, simultaneously and share the data between themselves., • 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., • 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

Page 2 :
RDBMS:- A relational database is a collective set of multiple data sets organized by tables, records, and columns. Relational database establishes a well-defined relationship between database tables., RDBMS contains various objects such as tables, forms and reports, Database objects of OpenOffice Base are as follows:, Tables: A table is used to store data in an organized form. In tables, data is stored in the form of, rows and columns, where rows are known as records and columns are known as fields. A table is, used to store the related information., Forms: A form is a database object which presents the information of a table in an easily readable, format. You can use forms to enter, edit or display records of the table., Queries: A query is the request sent to a database to retrieve specific data from a table. When you, want to search records in a database, you can use query., Reports: A report refers to a database object that displays the summarized data in an attractive, manner., Session 2: Understanding a table, Data in a database is stored in multiple tables. A table presents data in a tabular format, i.e., in rows, and columns. The data of a table is related to a single entity., For example, a table named Student would contain information or data about the students of a, particular school. It would include information, such as the names of the students, their roll, numbers, their addresses and their contact numbers,, STUD_NAME, , STUD_ROLL NO, , STUD_ADDRESS, , Rahul, Manoj, Rakesh, , CX01, CX02, CX03, , Laxminagar, Preet Vihar, Karol bagh, , STUD_CONTACT, number, 8978458945, 987858945, 7898456512, , Deciding Table Content and Properties, Before creating a table, you need to decide what kind of structure it is going to have. The table, structure depends on the type of information you want the table to contain. You need to have the, basic knowledge of the following table components while designing the structure of a table:, , •, •, •, •, •, , Field name, Field type (Data type), Field length, Field properties, Relationship, , Field Name, In a table, a field name serves as a label that specifies the type of information contained in a, particular field. For example, a field named Address in a table would store information related to the, addresses of people. Similarly, a field named Emp_ID would contain the IDs of the employees of a, particular organization.

Page 3 :
Field Type or Data Type, When you create a table in Base, you need to assign data types for each field in a table. These data, types specify the type of data that each field will hold, i.e., whether the field contains textual or, numerical data. In Base, data types can be categorized into the following types., •, •, , •, •, •, , •, •, •, •, , Text data types : Allow you to store textual data in fields. The maximum length of all text, type fields is 2 GB. The Text data types are as follows:, Text (fix) [CHAR], •, Text [VARCHAR, Text [VARCHAR_IGNORECASE], • Memo [LONGVARCHAR], Date and Time data types: Allow you to store information related to date and time. The Date, and Time data types are as follows:, Date [DATE], Time [TIME], Date/Time [TIMESTAMP], Numeral data types: Allow you to store numeric data. Numeral data types are further, categorized into two types, namely Whole-Number data types and Fractional data types., The Whole-Number data types are as follows:, Tiny Integer [TINYINT], Small Integer [SMALLINT], Integer [INT], BigInt [BIGINT], , The Fractional data types are as follows:, •, •, •, •, •, , Float [FLOAT], Real [REAL], Double [DOUBLE], Number [NUMERIC], Decimal [DECIMAL], , Special data types: Allow you to store special type of information, such as Yes/No data. The special, data types are as follows:, •, •, •, , Yes/No [BOOLEAN], Image [LONGVARBINARY], Binary [VARBINARY], , Field Length:-In a database, field length is the number of characters allowed in a field. You, can specify the field length to set the size of a field in a table. For example ,if you specify 2 in, a field length, it means it stores values from 10 to 99., Field properties, Auto Value: Specifies whether the entries are auto incremental values or not. This property, is especially used for Primary Key column., Entry required: Specifies whether the entry of data in the selected field is mandatory or not., For example, in case you set the value as Yes in the field property of a selected field, it, means that you must enter data in the field. On the other hand, if the value is set as No, you, need not enter data in the field. In other words, you can skip this field.

Page 4 :
Length: Specifies the field length, i.e., the size of the field., Decimal places: Specifies the number of decimal places allowed in the field., Default value: Specifies the value that is added automatically in a field. You can change this, value with a value of your own., Format example: Allows you to control the appearance of data in a table. You can use the, built-in formats available in Base or define your own formats., Relationship, A relationship is a link between two or more tables. You can connect two fields from two, different tables with the help of the relationship. You can create relationships between, tables to bring together related information. The relationships between tables are essential, for creating a form, report, or query that uses information from more than one table in a, database., One-To-One Relationship: Implies that a single record from the first table can be linked to, only a single record in the second table and vice versa. In this relationship, one record of a, first table is linked to exactly one record in another table., One-To-Many Relationship: Implies that a single record from the first table can be linked to, multiple records in the second table while a single record in the second table relates to only, a single record in the first table., Many-To-Many Relationship: Implies that multiple records from the first table can be linked, to multiple records in the second table and vice versa., Different Types of key, Primary key-: A primary key is a key that is used to uniquely identify the data in column of a table., ➢ It states that the column to which the primary key is applied cannot have a duplicate, entries., ➢ Each value in the column must have a unique value., Foreign Key:- Foreign key refers to key in a table which is reference of primary key in, another table., Composite Key:- Composite key refers to a key that is a combination of more then one key and, used for uniquely identifying the records in a table, , 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., 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 tables.

Page 5 :
• Deleting records from a primary key table if there are any matching related records available in, associated tables., , Creating query in Open office base:In OpenOffice Base, a query allows you to extract data from single or multiple tables as well as, summarize and filter it. The table from which a query retrieves the data is called a record source, In OpenOffice Base, you can create a query by using, ➢ Design view, Wizard, and the SQL view, Handling queries using a SQL commands, 1 .SELECT: This command is used to retrieve data from one or more tables in a database. The syntax, is as follows:, SELECT * FROM Table Name;, The syntax implies that the user needs to start with the SELECT keyword for retrieving the data, the, asterisk (*) sign is used to retrieve all records, and the keyword FROM is used to specify the table to, which the columns belong, For example consider the car_model table, COMPANY, Toyota, Honda, Chevrolet, BMW, , COUNTRY, Japan, Japan, USA, Germany, , MODEL, Camry, Accord, Beat, BMW3, , Select query for this table is, SELECT*FROM car_model;, If we want to retrieve country, SELECT COUNTRY FROM car_model;, SELECT command is used with various clause like, ➢ WHERE AND ORDERD BY, FOR EXAMPLE:SELECT*FROM car_model WHERE “COUNTRY”=’Japan’;, SELECT*FROM car_model ORDER BY” COUNTRY” ASC;, ASC is used for ascending order and DESC is used for descending order, , COLOUR, Silver, Grey, Green, Black

Page 6 :
2. CREATE : The CREATE command is used to create table in database. The syntax is, CREATE TABLE “Table Name”(“Table field” Data Type, ”Table_ Field” Data Type);, For Example, CREATE TABLE “Book_Details”(“Book_ID” INTEGER ,”Book_Name” VARCHAR(50),”Department”, VARCHAR(50));, 3.INSERT :- The command is used to add a new in an existing table., SYNTAX IS, INSERT INTO “car_model”(“Company”,”Country”,”Model”, “Color”), VALUES(‘Audi’,’Germany’,’A4’,’White’);, Result will be:COMPANY, Toyota, Honda, Chevrolet, BMW, Audi, , COUNTRY, Japan, Japan, USA, Germany, Germany, , MODEL, Camry, Accord, Beat, BMW3, A4, , COLOUR, Silver, Grey, Green, Black, White, , 4. UPDATE :- This command is used to update the existing record in a table, SYNTAX isUPDATE ”Table_Name” SET[“Coloumn_Name”=’Value’,”Coloumn_Name”=’Value’], WHERE[Condition];, For example:UPDATE “car_model” SET “Color”=’White’ WHERE “Company”=’BMW’;, Result will be:COMPANY, Toyota, Honda, Chevrolet, BMW, Audi, , COUNTRY, Japan, Japan, USA, Germany, Germany, , MODEL, Camry, Accord, Beat, BMW3, A4, , COLOUR, Silver, Grey, Green, White, White

Page 7 :
5 DELETE :- This command is used to delete one or more records from the table in a database., SYNTAX is:DELETE FROM “Table_Name” WHERE[Condition];, For example, DELETE FROM”car_model” WHERE “Company”=’Chevrolet’;, Result will be:COMPANY, Toyota, Honda, BMW, Audi, , COUNTRY, Japan, Japan, Germany, Germany, , MODEL, Camry, Accord, BMW3, A4, , COLOUR, Silver, Grey, White, White, , 6. DROP :- This command is used to delete the table from the database, Syntax is :DROP TABLE “Table_Name”;, If you want to delete car_model from database, DROP TABLE “car_model”;, Table car_model is deleted from database., Wildcards, A wildcard is a symbol used to replace or represent one or more characters. A wildcards refers to a, specific character that can be used to return/filter/generate results from a given query., Some examples of wildcard characters used in queries are as follows:, 1, 2, 3, 4, 5, 6, , Asterisk (*), Question mark (?), Square brackets [], Exclamation mark (!), Hash tag (#), Underscore (_)