ESSENCE Pee VIIl, Relational Operation (Relational Algebra) >> Recent trends and major issues Relational algebra is a set of operations for processing relations in a relational database and has the characteristic that all operands of each operation are relations, and the result of the operation is also relations. Basic relational algebra operations are usually divided into set operations and relation operations, Exact understanding of relational algebra operations is requred to use the relational database efficiently using a language such as SQL. >> Learning objectives | Tobe able to explain the concept of relational algebra 2.Tobe able to utlize set operations and relation operations, 3. Tobe able to utlize extended relational algebra operations. >» Keywords + Relational operation, relational algebra, relational calculus, set operation, relational operation, join, extended relational algebra + Preview for practical business Most relational databases used by the information systems of enterprises and organizations use SQL. as the standard query language. if an SQL statement is writen without understanding relational algebra, ich the theoretical basso SQL, the SQL statement cannot rete the eat tended bythe user, | ‘and may degrade performance. Therefore, users shoud fully understand relational algebra before writing 2 more optimized SQ statement | 100 TOPCIT ESSENCEDatabase ) 01 Understanding the Concept of Relational Algebra Relational algebras largely divided into relational algebra and relational calculus, Unke relational analyss, which is 2 non-procedural nguage that defines desired information, a relation operation has the characteristic ofa procedural language because it specifies the order of operations to be performed in order to obtah desired information. In partiular, as relational algebra becomes the theoretical basis for SQL, the standard query language of relational ‘databases, the concept and principle of relational algebra nead to be cleadly understaod, Therefore, this module introduces set operation and relation operation, which constitute the basic relational algebra, and the extended relational algebra operation 02 Set Operation and Relation Operation Relational algebra is a set of operations for processing relation in a relational database and all operands of each ‘operation and the result ofthe operation is relations, Basic relational algebra operations are usually cvided into set ‘operations and relation operations. A) Set operation Set operators inde Union, Intersect, Difference, and Cartesian Product ‘Table 36> Set operators ‘Doperstor | @Sen {@ Description as 1p | *Abiaty operation to create retin tht compose of tries, which Belong i> mof tha one relation ameng to relations that can be merged ™ Abrary operton to create arlton tht is composed of tues, which belong tboth Leaieats A of the two rations that can be merge. * Abbrary operton to create arelton that is composed of tuples, which exst hone pice | “reben out natin te other hen the e two restos the ca be mend ae | Abiraty aeration to creat arebton thet composed ofa combratin of tues Soe which canbe confired wth two reatons. 8) Relation operation Rebaton operator nude Sect, Projet, Join, and Deion “Tale 37> Relation operators Oper | Sen Descipton "Arar opeationin which speci tuple whch satis the contin & separstecrom a Select a | reeton + Aharon subset taken frm one elation, || = Aunay operation hvhicha speci atrbute deced ard separated om eaton Project * Avert suse state for an eatin M2 Database 101 m2ESSENCE Operator | Sin Descipton * Anoperation to create anew ration that composed of tunis, whichis crested by combining the tuples of two restos that meet the on conto in two or more elation. + Theta join: A jon operaton wth general jon condns (= 5, >, 27) Join bo | + Equi: on that uses the equal condtion (=) ony, The canon atibute used hthejoin anger in unica + Natural pn jon that uses the eal corto =) only. The commen attrbute used nthe join appear ony once without duplicates. cf “,_ | + Anoperation that separates and projects the tuple of R where al tuples of the dation S exit, ed hen thane ae twa restore Prd 03 Extended Relational Algebra Operations Useful operations can be defined by extending the basic relational algebra operations, Extended relational algebra ‘operations such as outer join, semi join, outer union etc. are generally used, ‘Table 38> Extended rlatioal algebra operations Operator Outer jin Outer union 102. TOPCIT ESSENCE Sen Deserpton ~ A operation that performs natural on or the result of prectig the ration after the operator wth jon atrbutes on tothe elation before the operat, *Tthas the seme effect as projecting theres of prfoming natural jn on two relations using the atributeof the relation before the operstor “Inths operation i tuple corespandng to tiple none lation doesnot exist inthe other relation nt excued, but the others charged to Nl tuple ta incude iti the rest. + Al tuples ofthe two join rations ae inched nthe result elation ~ An operation that creates a uion by expanding the saquence umber oF two relations that cannot be compietaly mergedDatabase Pee IX. Relational Database Language (SQL) >> Recent trends and major issues Even though the types of databases have been diverstfied recently, most of the information systems used by corporations and organizations are still running on relational databases. Therefore, software developers should be familar with relational database language and query processing methods and should be able to use them profcietly in order to process data efficiently SQL was developed by BM researchers Donald D, Chamberlain and F Raymond Boyce in the early 1970s, SQL began with the name of SEQUEL (Structured Engich Query Language) and was devised to manipulate and receive the data stored in System R, which was the semitelational database management system of IBM Recently interest in acquiring SQlL-related certifications has been increasing because the importance ‘of SQL has been highlighted. >» Learning objectives |. Tobe able to utiizea Data Definition Language (DDL). 2. Tobe able to utilize a Data Control Language (DCL), 3.To be able to utize the basic operation, group function, and advanced join of a Data Manipulation ‘Language (DML). >> Keywords + SQL, Data Definition Languege (DDL), Data Control Language (DCL), Data Manipulation Language (OML) M2 Database 103 7ESSENCE + Preview for practical business Since most of the recent information systems store deta ina database, an adequate database processing lenguege is needed to process values by linking 2 program to a database In particular, 2s more than 90% of the databases adopted by 2 profct are mostly relational databases inthe majonty of cases, developers should be familiar with the relational database language. So how well should developers familiarize themselves with the relational database Language? Objects such as tables and views are manzged by the data architect (DA) or the database administrator (DBA) in some Large-scale projects; however, developers manage everything from table creation/changeidelete to data processing in most projects, Therefor, itis desirable for the developer to know all ofthe data definition language (ODL), data control lanauage (0CL), and data manipulation language (DML) of the relational database, in order to peform those tasks smacthly Thats, rational database languages are one ofthe key database build and operation areas that developers should know well and itis recommended for developers to leam those anguages to such a level that they can use the syntax quite frely 104. TOPCIT ESSENCEDatabase ) 01 Types of Relational Database Languages ‘A)DDL, DCL, DML ‘The relational database language processes the structure and value stored in a relational database and can be Cassified into the Data Defnition Language (DDL), Data Control Language (DCL), and Data Manipdation Language (OM), “Table 39> Types of relational database languages 7a cls besten 2 ea CEATE 2 etn | AMG ees tite deier sania cage, | ATER (Biaoeon Salona active he lea toe oor rove = ‘igage tons aces dina datas aswel peom dna GRANT By | Sy ey nd concer on eve (Ce comrel Stine cnt back whecorceseransctns, a seated | CAMA Se reaston coe args LBA SELECT we From (x Atrige ht seca egstes cletes pds te weds hecabse | wee Manputon | serrations weet Language) UPDATE. oaetE B) Changes in SQL and the characteristics of SQL3 (© Changes in SQL ‘SQL was developed by IM researchers Donald D, Chamberain and F Raymond Boyce inthe early 1970s, andits standards have ben changed peroicaly by the ANS, 2s folows. Changes in SOL Yer] _Name ‘As Crarscwrtcs 1986 | SO. SQL86 Fist SQL standard ‘Adcpted a mar revson (SOS07S) FPS 122 ie 2 | Soe elsionaldatabece , (Object-oriented detabase 1999 | $0.3 | S0L1999 | cso the muon expression recurve query rear ete goes | sau | SOROS | Melted heres, Windom unctons, standardised sequences, nd a 501-2005 | column that has an automaticaly gererated vue Inprogress | SOLNY Consist access to the metadstareaisty @ Characteristics of SQL3 The object-oriented characteristics oF SQL3 can be seen as the common charecterstics of the abject relational ews, M2 Database 105 m2ESSENCE aes cist S03 Cancers corre eae _| Keane ea pes ncrdterlenesan anasteneeed pean Chere Caracloretes, | SuPports the user-defined type, obec, object identi, erence type function, ae method. Newly introduced concepts suchas the recurve ery. (Other aditional | Proves an act database function tat supports a trgger functions Supports the clentsererervronment improves oouty and vow update uncon. 02 Data Definition Language (DDL) ‘A date definition language is used to describe the detabase schema in such a way that computers cn understand it Specialy, t manages the creation. change, and deletion ofa table structure, The main statements of the data Role ofthe data contro language Tem oles Data secur rotect data rom unauthorized uses, Integrity Maintain te accuracy of data Recovery Cope wth system fare ‘Concurrency control rable multe users to access (us) a database atthe same time BB) Types of data control languages GRANT ‘This statement is used to grant privieges related to an object to a user « Example: When granting the prviege of searching the customer table to user ABC. ~> GRANT SELECT ON customer TO ABC REVOKE ‘This statement is used to cancel a users privileges related to an object. + Example’ When withdrawing the prviege of searching the customer table from user ABC. > REVOKE SELECT ON customer FROM ABC @DENY This statement is used to deny the use’ privilege related to an object, both GRANT and DENY are assigned at the same time, DENY takes precedence + Example: When denying the user ABC's priviege of searching the customer tab ~> DENY SELECT ON customer TO ABC: @commT “Ths statement is used to complete a transaction and confirm a data change. ROLLBACK This statement is used to cancel a transaction and restore data to their orignal state before the change. M2 Database 107 m2ESSENCE 04 Data Manipulation Language (DML) ‘A data menipuiation languege is used to input, modty delete, and search deta in a database. This section introduces ‘the data manigviaton language by subdividing it into basic operations, aggregate operations, and advanced join ‘operations A) Basic DML operations DML basic operations consist of statements such as INSERT, UPDATE, DELETE, SELECT, FROM, and WHERE, and are used for input, modifiction, deletion, and search operetions, Among them, FROM and WHERE are used together with the INSERT, UPDATE, DELETE, and SELECT statements, FROM is used to designate the target table and WHERE is used to set the conditions. ‘Table 43> Basic DML operations Satemet seLECT ‘Descrption and evamples of usage Used to search data valbes storedina table ‘SELECT calurnrame 1, column name2 FROM table came WHERE condcona cause: nseer Used to input values in al or some cols ofa table (Entering all cours) INSERT INTO table rame VALLES (nput vale 1, np valu 2, input vale 3), (emtang same cates) INSERT INTO table rame (colurn name |,colurn name 3) VALUES (rg value I, put vale 3) (Entering rom an exsting table) INSERT INTO table rame SELECT column ame 1. colunnname2 FROM tblerame WHERE condcona dauce: UPDATE Used to change the data stoedina table UPDATE is performed forall rows contend causes rok speed UPDATE table name SET column name |= input vale |. colamn name 2 WHERE condition cause: rout value DELETE Used to celete the data stored ina table DELETE s performed forall ows fa condenal cause isnot speed DELETE FROM table name WHERE conditional aus: B) Aggregate DML operations ‘Aggregate operations can be performed by using the AVE, SUM, and COUNT statements of the DML. The GROUP BY clause is specified after the FROM clause and the WHERE clause in the SQL statement. This cause is used to group data and to obtain statistical information on each group item. Aggregate operations are generally used with ‘the GROUP BY clause. However if an entre table is group, an aggregate operation can be performed without the ‘GROUP BY cause On the other hand, the HAVING dause can be used to set the condition forthe GROUP BY clause In adation, the DISTINCT option can be used to consider the same value as the same data, 108 TOPCIT ESSENCEDatabase ) ‘Table 44> Aggregate DML operations See ‘Descrption and orarples of Usage Used to output the ner ofrows, COUNT ‘SELECT COUNT() FROM table name Used to output the total exclude the NULL value sum ‘SELECT column name | SUM (caumnname 2) AS alas FROM table name (GROUPBY cole rame 1 sed to output the average exci the NULL vale. ave SELECT ealurn name 1 AVG (crn name 2) ales ROM table namie GROWPBY col ram | Example of calculation of no, of employees by department/total employee salary/average salary from the [Employee] table below. Department | Employee umber | Name | Ageedarnua salary] Jb ttle | Contact ifernetin Finance naa Hong Giiong, 6000 TTeamieader | O10-00X%XXX Legal affairs 04889 | Lee Monanona | 5400 | Assistant manager | O10-2000000 HR (1122 | MaengSaseong [5700 | General manager] O10-X1XX%X00 Generalafiars | 07876 Ha Wongi 7700 |General manager | 0103000000, SSCLECT depertment,COUNT(*) ASrumbe of employees, SUM (egred annua ery) AS tte sear, AVG (@greed annual slay) AS everage salary [Figue 39] Uttzation of aggregate operations ©) Advanced DML join operations DN (Nested Loop) Join NL Join performs join operations in a similar way to the nested loop statement used in programming. The table ‘outside the loops called the “preceding table” or the “outer table and the table inside the logp is called the “following table" or the ‘inner table’ NL Join first extracts the rows that meet the conditions of the preceding table and performs a join operation while reading the folowing table. This operation is repeated as many times as the number of rows that meets the Conditions of the preceding table, As the join operation of the folowing table is repeated as many times as the numberof rows that meet the conditions ofthe preceding tbl, itis recommended to select a table with fever result rows as the preceding table in consideration of performance, I is advantageous to NL Join to have a narrow processing range because data ae accessed randomly, @ Sot Merge Jor ‘Sort Merge cin performs a join operation by sorting data based on the join column. Sort Merge Jon is advantageous when processing 2 wide range of data as it mainly reads data using the scan method, unike NL Join, However, M2 Database 109 m2ESSENCE perfomance deteriorates if all sorting operations cannot be performed in the memory due to there being too many data to be sorted @Hash Jin Hash Join performs a join operation using the hashing technique. The hash functions executed using the join column (of the table to perform a join operation, The jin operation is performed while checking whether the data with the same hash value actualy have the same valve. Hash Join was introduced as an attematve for solving the rendom= access problem of NL Join and sorting the workload of Sort Marge Join, 110 TOPCIT ESSENCE