Page 1 :
PREPARED BY VIKRAM RAJ VICKY IT TEACHER DAV PUBLIC SCHOOL, MALIGAHT, , CLASS 10, , IT CODE 402 (Part – B ), , UNIT-3 : Relational Database Management System (RDBMS), Important Notes to score 50/50 in Theory Paper., , 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, , in, , a, , computerized, , system, we need to maintain several files, we would used 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
Page 2 :
SQLite., , Advantages of Database, Reduces Data Redundancy : no chance of encountering duplicate, data, Sharing of Data : the users of the database can share the data, among themselves, Data Integrity : Data integrity means that the data is accurate and, consistent in the database, Data Security : Only authorised users are allowed to access the, database and their identity is authenticated using a username and, password, Privacy : The privacy rule in a database states that only the, authorized users can access a database according to its privacy, constraints, Backup and Recovery : Database Management System, automatically takes care of backup and recovery., , Data Consistency : Data Consistency means there should be, multiple mismatching copies of the same data.
Page 3 :
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., , RDBMS:- A relational database management system (RDBMS) is a, database management system that is based on the relational, model. In, , the, , relational, , model, , of, , represented in terms of tuples (rows),, , a, , database,, grouped, , all, , into, , data, , is, , 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.
Page 4 :
1. Item : - Item is about which information is stored in the, database., 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 date, that you put in while adding information to your database., , For example, Database : Employee, Emp_Code, , Emp_Name, , Emp_Address Emp_Designat Emp_ContactNo, io, , E001, , ABC, , Meerut, , Manager, , Emp_Salary, , 9876543210 Rs.5000, 0, , Item : Employee, Field : Emp_Code , Emp_Name , Emp_Address , Emp_Designation ,, Emp_ContactNo , Emp_Salary, Record :, E001, , ABC, , Meerut, , Manager 987654321 Rs., 50,000, , Value : E001 , ABC , Meerut , Manager , 9876543210 , Rs. 50,000, 5. Key Field :- Key Field is a value in a Field that uniquely
Page 5 :
identifies the record. Eg. E001 which is unique to every employee., , Important Question :- 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., , 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 , Emp_Address 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 :- Datatypes are used to identify the type of data, we are going to store in the database.
Page 6 :
Categories of data types:- Data types can be broadly classified, into 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 values, like mobile number, age, etc., The different types of numeric data types available are1. 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 datatypes available in alphanumeric types, are
Page 7 :
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 case, , sensitive), , 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), , 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.
Page 8 :
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., , Note:- The “one” side of a relation is always the parent, and, provides the PK(Primary Key) Attributes to be copied. The “many”, side of a relation is always the child, into, , which, , the, , FK(Foreign, , Key) attributes are copied., Memorize it : one, parent, PK (Primary Key) ; many, child , FK, (Foreign Key), , There are two types of languages:-
Page 9 :
1. DDL (Data Definition Language), 2. DML (Data Manipulation Language), , DATA DEFINITION / DESCRIPTION LANGUAGE:- It is a, standard for commands that define the different structures in a, database. DDL statements create,modify and remove database, objects such as tables, indexes and users., , Common DDL Statements are:-, , 1. Create :- Used to create database objects., 2. Alter :- Used to modify database objects., 3. Drop :- Used to delete database objects., , DATA MANIPULATION LANGUAGE:- It is a standard for, commands that enables users to access and manipulate data in a, database., , Common DML Statements are:1. SELECT :- Used for retrieval of information from the database., 2. INSERT :- Used for insertion of new information into the, database.
Page 10 :
3. DELETE :- Used for deletion of information in the database., 4. UPDATE :- Used for modification of information in the database., , Types of DML:-, , 1. Procedural:- The user specifies what data is needed and how to, get it., 2. Non Procedural :- The user only specifies what data is needed., , Note:- A popular data manipulation language is SQL (Structured, Query Language.), , In this article on SQL Commands, I am going to consider the, below database as an example, to show you how to write, commands., Employee_Info, , Emergen PhoneNu, oyeeI eeNam cy, mber, ContactNa, D, e, me, Empl Employ, , 01, , Address, , City, , Count, ry, , Shanay, , Abhin, , 98987656, , Oberoi, , a, , ay, , 12, , Street 23, , Mumbai India
Page 11 :
Marathalli, 02, , 03, , Anay, , Preeti, , Soum, , 94321567, , House No, , Delhi, , India, , ya, , 83, , 23, , Roha, , 97642345, , Queens, , Bangalo, , India, , n, , 19, , Road 45, , re, , CREATE : To create Table, CREATE TABLE Employee_Info, , DROP : To Delete, DROP DATABASE Employee (complete information, present in the database will be lost), , DROP TABLE TableName, , (complete information present, , in the table will be lost), , TRUNCATE Table Employee_Info (your information will be, lost, but not the table)
Page 12 :
ALTER : This statement is used to add, delete, modify columns in, an existing table, ALTER TABLE, Employee_Info ADD, BloodGroup, varchar(255);., , INSERT : This statement is used to insert new records into the, table., , INSERT INTO Employee_Info, VALUES ('02', 'Anay','Soumya', '9432156783', ' Marathalli, House No 23', 'Delhi', 'India');, , UPDATE : This statement is used to modify the records already, present in the table, , UPDATE Employee_Info, SET EmployeeName = 'Aahana', City= 'Ahmedabad' WHERE, EmployeeID = 1;
Page 13 :
DELETE : This statement is used to delete the existing records in, a table, DELETE FROM Employee_Info WHERE EmployeeName='Preeti';, , SELECT : This statement is used to select data from a database, and the data returned is stored in a result table, called the resultset., SELECT EmployeeID, EmployeeName FROM Employee_Info;, (*) is used to select all from the table SELECT * FROM, Employee_Info;, , ****
