Contents aa Preartea Cau sche Syston Pee parse ce i ition 5 a peared San Rony Rae Gocunteie done een (eats BSS rac aay Bec Pokemon ees Prats Resse) tec aed Arete Soe et GE cctioaciren el Eaten) Retiree Senses Re Eee one) etd Pemacerecuet eal preva net) po Pemesore Peter tenet Lesmceri percent Cee eet ct eter cad Piraeraeeeaieeid fond car Ped pee Baicaderca peorietastoaty pt Wii} Data Base Management System See Database Concepts 8.1, DATA AND INFORMATION ‘output as information, and calculation to be performed on data is the processes. This is explained as fol ‘Some examples of information Since computer is a calculating device, it is known as processor. The input is known as data, — Processor | >} Information Input Output Figure 8.1 The same concept can be explained in kitchen. Do you like mango shake ? Let us see how 1n-be prepared Mango + Milk Outpu Shake, FGput Figure 8.2 In the example mango and milk are inputs and are fed to mixer to obtain the mango shake. DEFINITION» Data comprises of numbers, alphabets or any other form like pictures, sound etc It is collected from various sources but not organized. Such data cannot be used directly to make meaningful decisions. Examples of data are Sugam scored 704 marks out of 800. jas was born on 14th February, 2019. When the data has been worked upon to give it more meaning, it is called as Information. + Ojas is 17 years old Defini fo be useful to decision makers, information must be delivered to the right person, at the Danish scored more marks than Sugam did. Mn : Information is the data that has been organized and processed. right time, in the right place. It must be accurate, timely, complete, concise and relevant. If itis not mecting the above requirements, itis of litele value Data item or field data elements. The} A set of char are also kno represented as Roll_No, actets which are used together to represent a specific wn as field. e.g, Roll number of a student may be Record : Collection of fields is called a Record. c.g. The class record for a student contains such data fields as Roll_No., Name, Matks etc 2u1212 VINESH Information Technology (Vocational) Code : 402 Class— E + Collection of records is called a fileeg. A class file might consist of the student marks record for a class, eg, File : Student RollNo Name Marks 2001 Sugam 94 ‘2002 Danish 69 In the above table + Roll_No + Name + Marks are the fields. The above table have two records and these two records constitute file Student. The difference between Data and Information are as follows Sno Data Information 1 Data is raw, unorganized facts that need co Information is the data that has been organized and processed. be processed. 2. Data is an individual unit which contains Information is the product and group of data which collectively raw material and doesn’t carry any meaning. carry a logical meaning. 3. Ie doesn't depend on Information. Ie relies on data. 4 Measured in bits and bytes Measured in meaningful units like time, quantity, etc 8.2. CONCEPTS OF DATABASE Database is the collection of cerrelated data e.g. Consider the names, telephone numbers and addresses of people you know. This is a collection of related data and hence database e.g. Consider the names, telephone numbers and addresses of people you know. This is a collection of related data and hence database. Ik is managed by an individual (or group) called the Database Administrator (DBA), who is responsible for designing. creating and maintaining the database to satisfy the needs of users. All access to the database is controlled by a sophisticated software package called Database Management Systems (DBMS). DBMS is the collection of i errelated data and a set of procedures to access that data. It has programs to set the storage structures, load the data, acepe data requests from programs and user, format rettieved data so that ie appears in a form the Program or user expects, hide data that particular user should not have access to, accept and perform updates, allow concurent use of the data without having Users interface with each other, and perform backup and recovery procedures automatically. A DBMS is a general purpose software system that facilitates the process of defining, constructing and manipulating database fr various applications. + Defining a database involves specifying the datatypes, structures and constraints for the data to be stored in the | database. Constructing the database is the process of storing the data itself or on some storage medium that is controlled bY DBMS. ea] + Manipulating 2 database includes such functions as querying the database co retrieve specific data, updating database to reflect changes and generating reports from the dataGBH Date Base Management System FEATURES OF DATABASE SYSTEM ‘The main features of the database are given below : 1, Self-Deseribing nature of a Database System : A fundamental feature of the database approach is that the database system contains not only that database itself but also a complete definition or description of the database structure and constraints. 2, Insulation between Programs and Data : In database the structure of data files is stored in the database catalog separately from the access programs. This is called program-data independence. 3. Support of Multiple Views of the Data : A database typically has many users, each of whom may require a different view of the database. A view may be a subset of the database or it may contain virtual data that is de -d from the atabase 4, The integration and sharing of data minimizes the duplication of data. 5. Integration of data results in a considerable saving of storage space and data storage costs. G. Fewer application programs need to be developed for obtaining various reports due to independence of programs and data. 7. The query language facility helps non-programmi without the help of any programmer. 8. Faster preparation of information to support non-recurring tasks and changing conditions is possible. 9. Updation of data becomes easier due to integration of data 10, Large database maintenance, 8.4. CONCEPTS OF RELATIONAL DATABASE persons to access the database for information as needed RDBMS stand for Relational Database Management System. A relational database system consists of a collection of tables, each of which is assigned a unique name ie. A relational model represents the database asa collection of relations. Each a table with relation resembles a table of values. Each row in the table represents a collection of related data values. A rel columns and rows. Consider the customer table as shown below = — , caRDINauiry Ba e caeuta ee Aon = a 5 ss Yenkesh <— $$ rrcree ——_———> Table 8.1: The Customer Relation ‘Age and City. Attribute is the named column of a relation. The column headers are Ichas three column headers : Name, called anvibutes ue. Name, Age and City are atcibutes. The row of a table is called as tuple, In the customer elation of table, 8:1 there are seven tuples. A tuple isa row in a table, The degece of a relation is the number of attributes it contains.VINESH Information Technology (Vocational) Code : 402 Class 214 «three, Some of the more popular relational database manage yo The customer table has three attributes or depres include + Open Office Base + Microsoft Access + Microsoft SQL Server + MySQL + Oracle DATABASE SERVER caer a computer thats dedicated to database storage and retrieval tis a computer sytem chat pro ee aoe cecrvcled to accessing and retrieving data from a database, The database server holds the Brats Management System (DBMS) andthe databases. Upon requests from the ‘client machines, it searches the database for selected records and passes them back over the network. J CLIENT APPLICATION { DATABASE TIER | TIER W TIER i] | \ | oe aon i— cient Computer | i Figure 8.3. Database Server Database servers are used in client-server environment. They work as follows : +The client is a front end computer with which user interacts ie. issues commands or queries + Database servers are back end computers where data is stored and these computers receive request from front end computers + Based on that request the database server will search the database a ean the dats ind send requested information back to front end Many companies utilize a database server for storage, User nang tT canes es age. Users can access the data by executing a query using 2 vet KEYS Ic is important to be able to specify how row: i Pe eae ce ae ry rows ina rcation are distinguished, Conceptual, rows ate distin from ont ina relion ae allowed ro have exactly the iference among dhe mast be expressed in terms oftheir abuts New distinguish rows from each other, Keys the sme value for all attributes. A key allows us to identify a set of attributes that other, y Ip uniquely identify relationships and thus distinguish relationship fom © 1, Primary Key : Within a given rel cy : Within a given relation, a set of one or mote attributes that are unique within che ration and his able to uniquely identify that tuple (T! primary hey isa set nay arate {ws ofthe cable are called a tuple) is sad to be primary key of he ae ibutes that can uniquely identify tuples within the relation. Every relation does have a primary key. Ci E p ry eh ona the Customer table 8.2. The EID is the primary key for coset values in the relation, The non-primary key relation. The primary key is non-redundant ie. it oes at et 2. Candidate hey 2 Occasionally ee oat ey ateibutes of a table canbe refered ro as non-key abuts unique enifcnion proper All auibte combina eth He more than ons seb Pe pes they are candidates for the p ee rei scaton tar can eve a A leciex ome of them as primary key fr thereon, ne SME OHO OF More cana keys the darabo singMani it BB] vate Base Management System ai EID Name Address City 1043210 Suresh M.G. Road Bangaluru 1051461 Amit 421, VikasPuri Bangaluru | 1043102 Sudhir B-VIII-601, Rohini Delhi 1064210 Akram Hussain 412, Goal Market Caleutea 1113325, Rakesh Vyas 121, Model Town Ahmedabad { "1072164 Venktesh 612, Rohtas Nagar Chennai ~ 1011061 Sugam 6666,Sector 47D Chandigarh ‘Table 8.2. Customer Table 3. Super key : A Super key is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set. e.g.EID attribute of the table Customer is sufficient to distinguish one customer entity from another. The EID is a super key. Similarly, the combination of Name and EID is not a super key for the table Customer, 4. Alternate key : In case of two or more candidate keys, only one of them serves as the primary key. The rest of them are alternates only. A candidate key that is not the primary key is called an Alternate key i.e. Any attribute that is a candidate for the primary key, but is not the primary key, is called an alternate key. In the Customer table, Name is the alternate key. 5. Foreign key : A foreign key is used to represent the relationship berween two tables. A forcign key is a non-key attribute (or 1 group of non-key attributes) whose value is derived from the primary key of another table. A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table. Attributes Employee, Ecode ‘Name Sex Address Salary E143 Ojas M 451, Sector 45, 55000 (Chandigarh E268 Sugam M 349, Sector 4, 67000 ge Punchkula i E512 Namya F 612, Model Town, 51000 Jalandhar 329 Rahat F 121, Model Town, 45000 | Amritsar Primary Key 7 + Degree > Department Dno Dname DO3 ELE DOs CE Dil IT DIZ CE aa Pname Peode Plocation _Ecode Product X | P126 Chandigarh E143 Product Y P69 Ludhiana E268, Product Z P22 Amricsar E512 Product A P246 Jalandhar 5329 ES Figure 8.4. Primary key and foreign key216 VINESH Information Technology (Vocational) Code : 402 Class_y B 8.7, ADVANTAGES OF A DATABASE SYSTEM is can often lead to con: 1. Minimal Redundancy : In a file system, each application has its own private files. with resultant wastage in storage space. For example, a personnel application artment information for employees, These ty erable redundancy (duplication) in stored data, and an education records application may each own a file containing dep: files can be integrated, and redundancy can be eliminated. ; ; 2. Inconsistency can be avoided : In case the system is not aware of the duplication (ive. the redundancy is not controlled), there will be occasions when the two entries will not agree (i.e. when only one record is updated). At such times, the in an inconsistent state is capable of supplying incorrect or database is said to be inconsistent. A database tha conflicting information. 3. Sharing of data : It means not only that existing applications can share the data in the database, but also that new applicat applications may be satisfied without having to create any new stored files. ns can be developed to operate against that same stored data. In other words, the data requirements of new 4, Standard can be enforced : With central control of the database, the DBA can ensure that all applicable standards are followed in the representation of the data such as format of data items, conventions on data names, documentation standards etc. which will result in uniformity of the entire database as well as its usage. 5. Search cap: needed ata terminal, the search will have to be fast and the capability to search a database quickly with different search : The user of database may ask a wide variety of the questions about the data. Ifa quick answer is criteria is highly dependent on the physical data organization. 6. Integrity : By integrity, we mean unification of several files into one file. When a database contains data employed by a number of different users, it is important that the data items and associations between data items may not be destroyed. Hardware failure and various types of accidents will occur occasionally. The storage of data and its updating and insertion procedures, ae ‘must be such that the system can recover from these circumstances without harm to the dita integration reduces redundancy and also eliminates inconsistency. Privacy and Security : Data security refers to protection of data against accidents or intentional disclosure to unauthorized Persons. Privacy refers to the rights of individuals and organizations to determine for themselves when, how and t0 what extent information about them is to be transmitted to other. 8.8. DISADVANTAGES OF DATABASE SYSTEM ee SYSTEM DBMS software is very costly. 2. High Hardware Cost : Additional memory and processin "8 power may be required to run the DBMS, resulting in the need to upgrade the hardware. High Conversion Cost : The organization that was using file system needs to load files into the database which may a difficult and time consuming process, 4, Higher Programming Cost : Because a DBMS is a com resulting in extra payment for hire and expertise, 5. Backup and Recovery are more plex ool with many features, it requires experienced program icult : This is because of f increased complexit latabases are of® processed by several usets concurrently. iene Pista Because] cua STRUCTURE OF DATABASE SYSTEM ‘management system,Management Sys BB vate Base gement System xi Database Access Language Figure 8.5. Database Management System ‘The database management system have five components : Data, Hardware, Software, Users and Procedures. a Figure 8.6, Database Management System Components 1, Data : Data refers to collection of related information, combined to be stored at a central place, which is the basis of any activity in the organization. It is that component, for whose management, the DBMS works, It is the most tion and its activity. important and essential component and is the core of any org: Hardware : The hardware components of the system consist of + The secondary storage devices such as magnetic tapes or disks, DVD ROM, USB devices which are used to hold large volumes of data, together with associated I/O devices, device controller, I/O channels and so on. + The processor and associated main memory that ate used co support the execution of the database system software. 3. Software : Berween the physical databases itself ie, the data as physically stored and the users of the system, is a layer of software, known as Database Manager or Database Management System (DBMS). All requests for access to database ate handled by the DBMS. Access to DBMS is done by Data Access Language. Database Access Language is a simple language designed to write commands to access, insert, update and delete data stored in any database. Users so that ¢ his component includes all type of users, who derive the benefit of getting their work done by the DBMS, xy have the required data and information with them for decision making.VINESH Information Technolog) ent system. This includes neral instructions to use database a een oa 5. Procedures :Procedures refer 1 af DBMS sofa, on i in to setup and install a DBMS, to logi generating reports etc. 8.10, RELATIONAL DATABASE MANAGEMENT. yy (Vocational) Code : 402 Ciass_y r 218 SYSTEM (RDBMS) is called Relational Data Base Management System i ent System. Tei RDBMS stands for Relational Dea Mangement 5 See agpi ander Ios senate siden oles a a ater pees of data in che database. Often, dara ina tlational databases pang to identify and access data in el 2 oo is asi i ie. A relational model represents th i i hich is assigned a unique nam: ; ' Consider the customer table as shown below : database as a collection of relations. Each relation r related data values, A relation is a table with columns and rows. ATTRIBUTES Name Age City Suresh 25 Bangalore Amit 29 Bangolore ‘Sudhir 24 New delhi CABDINALIIN) Akram Husain: 32 Calcutta Rakesh Vyas 30 Ahmadabad Chandigarh ane ECR ee Table 8.3 : The Customer Relation Ithas three column headers : Name, Age and a sit y Attribute is the named column of a relation. The column heade called attributes ie. Name, Age and City are attributes, lation ‘The row of a table is called as tuple. In the customer 1 y : able is called as tuple. In the customer ra" table8.3, there are seven tuples. A tuple is a row in a table. The degree of a relation is the number of attributes it on™ The customer table has three atributes or degree three, hhe features of RDBMS are as follows : RDBMS stores data in the form of tables, Numerous users can acces it together whic managed by a single user Virtual tables are available for Storing the data " “ws kn order to exclusively find out the rows, the primary key i ed ‘The data are always saved in tows and columns me To retrieve the information the indexes are used Columns are beng shared between tbls using the ke ing the keysBh vote Bese Management System —_ Some of the more popular relational database management systems include : Libre Office Base, Open Office Base, Microsoft Access. Microsoft SQL Server, MySQL, Oracle etc. The difference between DBMS and RDBMS is as follows Sno. DBMS DBMS stores data as a file. In DBMS system, stored data in either a navigational ot hierarchical form. DBMS supports single user only, Te computer networks and the system hard disks. ‘Low software and hardware needed, DBMS does not support the integrity constants. The the program for managing the databases on the integrity constants are not imposed at the file level. RDBMS Data is stored in the form of tables. RDBMS uses a tabular structure where the headers are the column names and the rows contain corresponding values Te supports multiple users. Ie's the database systems which are used for maintaining the relationships among the tables. Higher hardware and software needed. RDBMS supports the integrity constraints at the schema level. Values beyond a defined range cannot be stored into the patticular RDMBS column. 8. DBMS system mainly deals with small quantity ofdata. RDBMS is designed co handle a large amount of data 9. DBMS does not support client-server architecture RDBMS supports client-server architecture. 10. Data fetching is slower for the complex and large Data fetching is rapid because of its relational approach. amount of data, Data redundancy is common in this model. Keys and indexes do not allow data redundancy. 12. No relationship berween data Data is stored in the form of tables which are related to each other with the help of foreign keys. 13. There is no security. Multiple levels of security. Log files are created at operating system, command and object level. 14. Data elements need to accessed individually. Data can be easly accessed using SQL query. Multiple data clements can be accessed at the same time. 15. Examples of DBMS are a file system, XML, Windows Example of RDBMS is MySQL, Oracle, SQL Server, te. Registry, ete ep Create and Edit Tables using Wizard and SQL Commands DATABASE OBJECTS BALI ‘The fundamental concept underlying data columns of data. Each table represents a single entity, are as follows Table ‘The most important component of any database is the « bases is that data is stored in tables. ‘Tables are comprised of rows and such a8 a person or product. The various data storage elements table. This is where the actual data stored. * A table is a collection of records that can be divided into fields. * Each field holds a single piece of information about the record in wi hich it residesfe VINESH Information Technology ( Vocational) Code : 402 Classy b + Inthe table, rows are called as records and the columns are fields. (easil [pull ol Figure 8.7. Student Table In the above student table, there are six rows and four columns. Therefore, the table student have four fields : ROLLNO, NAME, MARKS, GRADE. and six records. 8.11.2. Queries Ina database table, you use a query to search, view and modify the data that exists in the table, You can also use quetisto modify the structure of the tables or to access data that is external to the database. The typical query is used to return dat that meets specific criteria. Figure below obtains the merit list for the students in descending order of marks. Figure 8.8. A query that returns the marks in descending order 8.11.3. Forms One of the most powerful features of database is the capabilis edie and 0% your data. After a form is created, it looks ability to create forms that can be used to enter, ai ae rH looks and operates like a Windows application. Figure 8.9. shows 2 form for heBf vste Base Management System ms {B eoream esonttom: Gorter tn Onubnetom 6 (60 Yee beet Fem TH Tesh dow Be B-Bue Bas 7aoete Ti Figure 8.9. Form 8.11.4, Reports Reports organize or summarize your data so you can print it or view it on screen. You often use reports when you want 0 analyze your data or present your data to others.You can send reports to a printer or you can export the report to any number of formats. Reports like forms, use an underlying data source, ether a table or a query ro provide the actual data. The report’ design dictates how the data will be presented when the report is printed, previewed or exported. Figure below shows the report of student table. Merit List ROLLNO NAME CLASS aa nn aT” a 108 UnesiRevrre 0 108 SueznBorth 0 101 Sige cra 0 | 10 owen 0 | 10 Rett 10 | 107 Wiosdtinen 0 105 Ant Sreme 0 reormucteaT—viebeemay Figure 8.10. Report =sa VINESH Information Technology (Vocational) Code : 402 G,, : y fr Ih “The relation between form, table, query and reportare #5 fo Basic Database Object Database Application Figure 8.11. Relation between form, table, query and report 8.12, DATA TYPES Data types are rules that define what data may be stored in a column and how thar data is actually stored. Data ypes ae used for several reasons : 1. Data types enable us to restrict the type of data that can be stored in a column. column only accepts numeric values. 2. Data types allow for more efficient storage, internally. Numbers and date time values can be stored in a more condensed format than text strings The various data types available ate as follows : 1. Numeric 2. String 3. Date and Time 1, Numeric Data types ‘or example, a numeric data ype 4, Boolean. Numeric data types store numbers. Databases support several numeric data types, each with a different range of numbers that can be stored in it. Numeric data types in a database can be used for storing information such as mobile number, roll number, year of school admission, true or false statements, statistical values etc. Table below lists the fequett used numeric data types. Data ype Description TINYINT Integer value from 0 to 255 SMALLINT Integer value from 2" to 25 INT Integer value from -2°" to 231 BIGINT Integer value from -2 to 26 DECIMAL Floating poine values REAL Decimal value from -2!° gq 2102 BOOLEAN Boolean value 1 oF 0 /Yes or No DOUBLE Double-precision floating poine values. FLOAT = Single-precision floating poine values. Numeric Data typesData Base Management System aaall 2, String Data types ‘The most commonly used data types are string datatypes. These stoe strings : ‘wo types of string data type that we can us Data type _ CHAR __ Fhxed-lengeh scting from 1 to 255 chars long VARCHAR Stores up to the specified lengeh LONGVARCHAR Stores up to the max length or number indicated by user String Data types names, addresses etc. There are basically ixed-lengeh strings and variable-length strings. Description 3. Date and Time Data types ‘The various data types for the storage of date and time values are as follows : Data type Description DATE Date from 1000-01-01 to 9999-12-31 in the format YYYY-MM-DD. DATETIMEA combination of DATE and TIME. TIMESTAMP Functionally equivalent to DATETIME (but with a smaller range). TIME ‘Time in the format HH:MMSSS. Date and Time Data types 4. Binary Data types Binary datatypes are used to store all sorts of data (even binary information), such as graphic images, multimedia, and word processor documents. Datatype Description Binary Stores any array of bytes VARBINARY Stores any array of bytes, Binary Data types 8.13. MANIPULATING DATA In a database to define the structure of the data and to manipulate data certain commands are used. These are 1. Data Definition Language (DDL) 2. Data Manipulation Language (DML) 1. Data Definition Language (DDL). Ic consists of commands to create objects such as tables, views, indexes ete. in the database. CREATE, ALTER, DROP, RENAME, TRUNCATE are commands of DDL language which helps in defining objects. The result of compilation of DDL statements is a set of tables that is stored in a special file called Data Dictionary. The storage structure and access methods used by database system ate specified by a set of definitions in a special ype DDL called daa storage and definition language. 2, Data Manipulation Language. Ie is used for query, insertion, deletion and updation of information stored in database. Data Manipulation Language (DML) consists of| VINESH Information Technology (Vocational) Code : 402 cia 2 224 . . + Rettieving Data (SELECT COMMAND) means getting information out of 2 database. ‘+ Inserting Data (INSERT Command) means inserting new information in database ie. inserting tows in be option to move the field cowards Selected Fields. Click Next after selecting fields for the table “ae Wears a ny Stews 7 Selec fed for our tae = Ths mamd eps yout te stir tie Arctic cgay m2 Zserypedtomas niente nu wn oc sam 3 Set primary key a crete | gy © Brine: | Figure 8.25, Field selection in wizard 6. Set field types and format window appear on the screen. Specify the data type of all the fields as shown below and click Next EMPLOYEE TABLE EMPLOYEEID INTEGER FIRSTNAME ‘TEXT(VARCHAR) LASTNAME TEXT(VARCHAR) CITY TEXT(VARCHAR) SALARY DECIMAL ‘Stews Set fed types and formats | sett Sacto — | stp bey {ane Fete | ces = ae | bay gat fw E nee a | ti Paar ene fapVINESH Information Technology (Vocational) Code : 402 Class, = ary’ key in a table that is fa a i window af the screen. Primary key is a key in a table that is unique for each record STE eee tmployee Identification numbet, Aadhar number, mobile number ec 4 relational database must always have one and only one primary key. In the EMPLOYEE table Employee the primary key because its unique and no two employees can have same EmployeelD. Select Use an existing field as primary key option and specify Employee as primary key. Click Next fsvewins ‘| ‘Steps ‘Set primary key ey lon ch cn FE ae in Le oe pce IY a ov ear aaa yell ee Te ] Se veaiane ‘Without a primary key, it will not be possible to enter data into this table. I coma = ee O Astomatically adda primary key | © Use an existing field as a primary key ‘me TITS] Cato xe © Define primary key 253 combination of several fields ‘Aniabl fields rian hey field Employee) a] cay =| Figure 8.27. Specitying Primary Key 8. Create Table window appears on the screen. Give name to the table and select Insert data immediately to add data to the Employce table Son Grote ttle 1 Select fie What do you nant name your ble 2.Set ype an formats Enployed 2.5 pay one Cengeions Youhare entered al th eformation neded to crest your table | Wihat do you want todo next? © mat titi Oey hate eign (© Create form based on this tableei Data Base Management System a9 9. Entered data will appears as follows on the screen. Click Save to save the data and close the window. ‘© Employee - Employee - OpenOffice Base: Table Data View ee x File Edit View Insert Tools Window Help 7 @ SMO t wie y vie Hl [__] EmployeeiO | FirstName | LastName City Salary I 1101 heaA ARORA CHANDIGARH |150000 e| 1102 [ARORA |BANGALORE 95000 = [_]1103 maar TARORA (DELHI (175000 | [1104 (SONIA |KALRA JALANDHAR 135000 1705 SUGAM ARORA |AMRITSAR —_|145000 | 31106 Org [ARORA HYDERABAD 65000 | - | Record of 6 cH] Figure 8.29. Employee Table with data 8.18. CREATING TABLE USING SQL COMMANDS (DDL COMMANDS) Tables can also be created using SQL (Structured Query Language) commands. The CREATE TABLE command is used t0 create a new table in a database. The syntax is CREATE TABLE “table_name” ( “column” datatype(size), “column2” datatype(size), “column3” datatype(size), In the table Table name Specifies the name of the table Column Specifies the names of the columns of the table. Datatype Specifies the type of data the column can hold (eg. varchar, integer, date, etc) Size Specifies the size of the data we can store in a particular column, For example, if for a column fe specify the data_type as int and size as 10 then this column can store an integer number of maximum 10 digits. : ; Somple 8.1. Write an SQL query to create a table with following details : Field ‘Type : Rollno Numeric(12) Primary Key Name ‘Varchar(30) Tee Float e Chiy Varchar(30)— 34 VINESH Information Technology (Vocational) Code : 402 Class_x a a i ble in the datab: 1, Create a new database studentl. After creating database, next step is to create table in the database. The steps in creating table are as follows 2. Click Tools>SQL option et Tt Yoke Figure 8.30. Creating table with SQL statements 3. Execute SQL statement window appears as shown below. Write SQL query under command to execute section Execute SOL Statement SQL command — ‘Command to execute | Bvevious commands Leis ees rn = statusJ data Base Management System 4, 5. Click Execute option. Write the foll ing SQL statements CREATE TABLE “STUDENT” ( “ROLLNO” NUMERIC(12) PRIMARY KEY, “NAME” VARCHAR(30), “FEE” FLOAT(12), “CITY” VARCHAR(30) ’ Figure 8.32. Writing SQL statements created. Close the Execute SQL statement window. ‘Command Successfully Executed appears on the scr feen as shown below and table is Figure 8.33. Executing SQL statements 235VINESH Information Technology (Vocational) Code : 402 Classy 236 in reopen it, Click at Tables option and able SeudeOtl appear 2 shoyy Base and again reo 6, Close the Open Office below Sse Edt Yew Jruet Toots Window Help Pe ie Ol SCL dab engine Figure 8.34. Example 8.2. Write an SQL query to create a table with following details : Field Type Emp_no Char(4) Primary Key Name Varchar(25) Salary Float Department Varchar(25) Ans. The SQL statement is as follows CREATE TABLE “EMPLOYEE2” EMP_NO” CHAR(4) PRIMARY KEY, “NAME” VARCHAR(25), SALARY” FLOAT, “DEPARTMENT” VARCHAR(25) op Perform Operations on Table el DATA IN THE TABLE The steps to insert data into a table arc as follows Bohr she die in Open ae ee 8.19, INSERTINGff) Date Base Management System Bsus act “Opec tase - o x ee x Bee Bis Of Bose y Oe) tana ane Ee) = aS a | & mone mel fe == xi se) | ae aay Figure 8.35 2. Double click the table name . Table Data View window appears on the screen as shown below © STUDENTI- student -OpenOice Base: Table Data View - Oo x file Edt View Inet Tools Window Help & ibie- ROLNO | NAME | FEE | cv | set Figure 8.36. Table Data View window 2371238 3. Enter the data ata is inserted. 4. Glick Save-tn save the dar and clos: the window. Data is Information Technology (Vocational) Code anc) “© STUDENT - student - Openofce Base: Table Data View File Edit View Insert Tools Window Help nase vi til ‘ay ‘PONCHRULA [BANGALORE BANGALORE | [Mua Veco E ot Figure 8.37. Inserting data into table 8.20. INSERTING DATA IN THE TABLE USING SQL STATEMENT my The other way to insert data into table is using SQL statement. The SQL statement to insert data into table is INERT INTO. The syntax is as follows INSERT INTO “Table_name” (“Field1”, “Field2”, “Field3”... VALUES ( ‘Valuel’, ‘Value2’, ‘Values’, In the table Table_name Specifies the name of the table Field Specifies the names of the columns of the table. Values Specifies the data to be entered in the table Trample 83. Write SQL query to insert data into student table Field ‘Type Rollno Numeric(12) Primary Key Nate Varchar(30) Fee Float : hs ay) ‘Varchar(30) 1 Open the database student] After 6 Pening the datal The steps in inserting data i ae imo student] table are as follows the Dase, next step is to open student] table int weData Ba: Management System 2. Click Tools®SQL option Figure 8.38. Inserting data into table using SQL statements Execute SQL statement window appears as shown below. Write SQL query under command to execute section 4 Write the following SQL. statements INSERT INTO “STUDENTI VALUES ('1206’, ‘AMAN’, ‘17500’, ‘NOIDA’; [= $01 Statement st command ‘ommandtomecste ice eo 00007 "0, NOON 239VINESH Information Technology (Vocational) Code : 402 Class—x i 5. Click Execute option. Command Successfully Executed appears on the screen as shown below and data is inserted. Close the Execute SQL statement window. 240 Figure 8.40. Inserting Data into table 8.21. EDITING AND DELETING RECORDS IN THE TABLE Once a table has been created, records can be entered into it. Editing is used for updating data in the table where as delete is used for deleting records in the table. The steps in editing records in table are as follows 1 Open the database studentl. After opening the database, next step is co open student] table in the database 2 Double click the table name and open the table in data view as shown below ‘© STUDENT? - student - OpenOvfice dase Table Data View le Eat Yew Inset Tool: Window Help [BANGALORE (way 21500 MUMBA ‘ANANIA, 22500 (CHENNAI ‘aun 17500 NOIDA a PeMeremagean oeData Base Management System 3. Select the record that you want to edit. 4, Edit the record and click save. EDITING RECORDS USING SQL STATEMENTS The editing of records using SQL statements is as follows Srample 8:1, Write an SQL query to update name of Aman to Amandeep in Student! table. Ans: 241 1. Open the database studentl. After opening the database, next step is to open studentl table in the database. The steps in editing data into student! table are as follows 2. Click ToolsSQL option Execure SQL starement window appears. Write SQL query under command to execute section 4, Write the following SQL statements UPDATE “STUDENT1” SET “NAME” = ‘AMANDEEP" WHERE — “ROLLNO"= *1206'; 5. Click Execute Option. Command Successfully Executed appears on the screen as shown below and data is updated. Close the Execute SQL statement window. Execute SAL Statement Brevious commands Status ——— (1: Command successfully executed. Figure 8.42. Updating Data into table 8.23. DELETING RECORDS ~23. DELETING RECORDS The steps in deleting records in table are as follows 1. Open the database studentl. After opening the database, next step is to open student] table in the database 2. Double click the table name and open the table in data view