TT TTT TT Aewances SQL: SQL Data Types and Schemas, Integrity Constraints: vtherksation, Erbeddled SQL, Dynamic SQL, Functions and Procedural Constructs, Recursive Queries, Advanced SQL Features. Relational Database Design: Features of Good Relational Desisn, ‘Atomic Domains and First Normal Form, Functional-Dependency Theory, Decomposition using Functional Dependencies. PATE Apvanceo SQL 3.1.1 Sql Data Types And Schemas Ql. Explain about SQL data types. Aus i SQL data types can be broadly divided into following categories, (imp.) 1, Numeric data types such as int, tinyint, bigint, float, real etc Date and Time data types such as Date, Time, Datetime etc. Character and String data types such as char, varchar, text etc. nvarchar, ntext etc. ary etc. Binary data types such as binary, var 2. 3. 4, Unicode character string data types, for example nchar, 5. 6. es clob, blob, xml, cursor, table ete. .. Miscellaneous data typ! ayint, smallint int, bigint, decimal, numeric, float, har, Varchar 3 Character/String——* Varehar (max). a — |” Nehar Nvarcha Unicode Character/String ‘NVarchar (max), NText) . ~ Binary, Varbinary, Binary \Varbinary (max), image’ Samal th CantonMCA LYEAR I SEMESre, 1. Numeric Data Types DATA FROM TO TYPE bigint 79.223,372.036 854,775,808 9,223,372,036 854,775,807 int -2,147,483,648 2.147.483,647 smallint 32,768 32,767 tinyint 0 255 bit 0 1 decimal -10*38 41° 1038-1 numeric “10°38 41 10*38-1 money 7922,337,203,685,477.5808 ++922,337,208,685,477.5807 smallmoney | -214,748 3648 +214,748,3647 SSS is Sse DATA TYPE FROM To Float “1.79 + 308 L.79E + 308 Real -3.40E + 38 3.40E + 38, DATA TYPE FROM TO datetime dan 1, 1753 Dec 31, 9999 smalldatetime Jan 1, 1900 dun 6, 2079 date Stores a date like June 30, 199] time Stores a time of day like 12:30 PM, Note : Here, datetime has 3.33, milliseconds accuracy where as. smalidatetime has 1 minute accuracy. 3. Character Strings Data ‘Types i) char — Maximum length of 8,000 characters,( Fixed length non-Unicode characters) il) varchar — Maximum of 8,000 characters. (Variable-length non-Unicode data). iti) varchar(max) — Maximum length of 2E + 31 characters, Variable-ler.gth non-Unicode data (SQL Server 2005 on!y). iv) text — Vanable-length non-Unicode data with a maximum length of 2,147,483,647 characters. Rahul Publications ee. ee en a eeyoneh (Fixed length Unicode) Characters (Variable length Unicode) Of 2E + 31 characters {SQL Server 2005 only).( Variable 073,741, ii) nvarchar(max) — 1, Ll length Unicode) en aah iv) text — Maximum length of 1 5, Binary Data Types varbinary(max) — Binary data) iv) Image — Maximum length of 2.1 6. Misc Data Types i) sql_variant — Stores y, 47,483,647 bytes. ( Variable length Binary Data) ah i ome wes of various SQL Server-supported data types, except text, ntext, and ii) ase Stores a database-wide unique number that gets updated every time a row gets update ii) uniqueldentifier — Stores a globally unique ident ifier (GUID) iv) xml — Stores XML data. You can store xml instances ina column or a vaviable ¥) cursor — Reference to a cursor object vi)__ table — Stores a result set for later processing Q2. What is a Schema in SQL? Explain the advantages of schema, Aus: (imp. A Schema in SQL is a collection of database objects associated with a database. The username of a database is called a Schema owner. Schema always belong to a single database wherens a databace ean have single ‘or multiple schemas. Also, itis also very similar to separate ‘Tamespaces or containers, which stores database objects. It includes various database objects including your tables, views, procedures, index, etc. Advantages of using Schema > You can.apply security permissions for separating and protecting database objects based on user access rights. je ‘be managed within a database. Schemas play an important > Alogical of database objects can ged w 2 men allowing the database objects to be organized into these logical groups > The schema also helps in situations where the database object name is the same, But these objects schema fall under different logical groups. > Asingle schema can be used in multiple databases. > "The schema also helps in adding security. / _ ei > Ithelps in manipulating and accessing the objects which otherwise is a complex mi inON MCA YEAR II SEMESTER > You can also transfer the ownership of'several schemas. > The objects created in the database can be moved among schemas. ‘These were few advantages, now the next topic is the method to create a schema. Syntax: CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name] (DEFAULT CHARACTER SET char_set_name] [PATH schema_name, ...]] ‘Name schema_name' "| sThe name of a schema to be created. If this is omitted, the user_name is used as the schema name. AUTHORIZATION. _ | Identifies the user who is the owner ofthe schema, If not ‘owner_name | mentioned the current user is set as the owner. DEFAULT. ©. “CHARACTER SET Specify the default character set, used id for al abject crate in | Examples : 4 Example-1; As a user with authority, create schema called STUDENT with the user STUDENT as, the owner. SQL Code: (CREATE SCHEMA STUDENT AUTHORIZATION STUDENT Alter Schema “The ALTER SCHEMA statement is used fo rename a schema or fo specify a new owner, the nes’ ‘owner must be a pre-existing user on the database ‘Syntax: ALTER SCHEMA schema_name [RENAME TO new_schema_ name] [OWNER TO new_user_! name] 90. Rahul Publications eu il Sima hCnSeto,po ee schema_name The name of an existing schema. new_schema_name “The new name of the schema. new_owner prop Schema Destroy a schema. Syntax DROP SCHEMA 3.1.2 Integrity Constraints Q3. Explain about various types of integrity constraints. Aus? ° >» Rel > They ensure the correctness of data in the database. Jational constraints are the restrictions imposed on the database contents and operations. Types of Constraints in DBMS fferent types of relational constraints- In DBMS, there are following 5 dif 4. Domain constraint Tuple Uniqueness constraint Key constraint Entity Integrity constraint . Referential Integrity constraint 1, Domain Constraint 2 Per .es the domain or set of values for an attribute. > Domain constraint defin he attribute must be the atomic value from its domain. > Itspecfies that the value taken by # Example Consider the following Student table ‘STUID Name |_sTutp | $001 Alshay = | — $002 Abhishek 21 | so | Ot $003 Shashank 20 004 Rahul A Rahul Publications Samal sth CantonPa MCA | YEAR II SEMESTER Here, value ‘A’ is not allowed since only integer values can be taken by the age attribute, 2, Tuple Uniqueness Constraint ‘Tuple Uniqueness constraint specifies that all the tuples must be necessarily unique in any relation, Example-01: Consider the following Student table sTUID | Name Age S001 Akshay 20 002 Abhishek 21 S003 Shashank 20 $004 Rahul 20 STUID Name Age $001 Akshay 20 ‘S001 Akshay 20 8003 Shashank 20 E 004 Rahul 20 i > Allthe values of primary key must be unique. > The value of primary key must not be null. Example Consider the following Student table STU_ID Name Age S001 Akshay 20 . 001 ‘Abhishek 21 $008 Shashank 20 S004 Rahul 20 Rahul Publicati Samal th Connuit DATABASE MANAGEMENT SYSTES tion d i 5 Thisrelation does not satisfy the key constraint as here al the values of primary Key are not uniae Entity Integrity Constraint 4. > Entity integrity const value ian ty constraint specifies that no attibute of primary key must contain a null value it » _ Thisis because the presence of null value in the primary key violates the uniqueness property. Example Consider the following Student table sTUID Name ‘Age $001 Akshay 20 S002 Abhishek 21 S003 Shashank | 20 Robo | 20 an This relation does not satisfy the entity integrity constraint as here the primary key contains @ NULL value. 5. Referential Integrity Constraint > This constraint is enforced when a foreign key references the primary key of a relation. > Itspecifies that all the values taken by the foreign key must either be available in the relation of the primary key or be null. Example Consider the following two relations- ‘Student’ and ‘Department’. Here, relation ‘Student’ references the relation ‘Department’. (DezLne Dest.name) student (SLID. Mame O#p0.79) (Raterenelng Relation) t (Referenced Reation ) Student sTUID Name Dept_no 001 Akshay p10 $002 ‘Abhishek p10 003 Shashank Dll 5004 Rahul Di4 Rahul Publications Samal th ConSatnnMCA LYEAR II SEMESTER Department Dept_no Dept_name p10 ‘ASET Du ALS biz ASFL. Dis ASHS authorization. (a) Resource Authorization > Authorization to access any system resource E.g: Sharing of database, Printers. (b) Alternation Authorization > Authorization to add attributes or delete attributes from relations, (©) Drop Authorization > Authorization to drop a relation. Database Administrator > The main authority of databse system is databse administrator(DBA) Rahul Publications {a} Saal th Connunit DATABASE MANAGEMENT SYSTEMS ‘The SQL standard specifies mod owner of, rie ma oF DBA of scheme, lifcation to the schema can be done only by the database The DBA may authorize new users, restructure the database and so on. Itis analogous to that of superuser or operatar for operating systems. 3.1.4 Embedded SQL Q5. Write about Embedded SQL program development. fue: (imp.) There are some special embedded SQL statements which are used to retrieve the data into the rogram. There is a special SQL precompiler that accepts the combined source code with other programming fools and converts them into an executable program, Concepts for Embedding the SQL Statements There are some techniques to embed SQL statements in the programming languages. 1. The programming language in which the SQL statements are embedded is called the host langt2ey- The SQL statements and host language statements make the source program which is fed toa SQL precompiler for processing the SQL statements. 2, The host programming languages variables can be referenced in the embedded which allows values calculated by the programs to be used by SQL statements. SQL statements, 3, There are some special program variables which are used to assign null values to database columns. These program variables support the retrieval of null values from the database. Embedded SQL Program Development ce the embedded SQL is a mixture of SQL and programming language, so it cannot be fed directly to a general purpose programming language compiler. Actually the program execution is a multi- step which is as follows. 1. Fisst, the embedded SQL source code is fed to the SQL precompiler. The precompiler scans the program and processes the embedded SQL statements present in the code, There can be different precompilers for different type of programming languages. 2. After processing the source code, the precompiler produces 2 files asits output. The first file contains the source program without embedded SQL statements and the second file contains all the embedded SQL statements used in the program, 3, The first file prodicéd by precompiler (that contains the source program) is fed to the compiler for the host programming language (like C compiler). The compiler processes the source code and produces object code as its output. 4, Nowthe linker takes the object modules produced by the compiler and link them with various library routines and produces an executable program. 5. The database request modules, produced by the precompiler (in steps) are submitted to a special BIND program, The BIND program examines the SQL statements, parse them, validates them, optimizes them and finally produces an application plan for each statement. The result is a combined pplication plan forthe enfre program, that represents a DBMS-executable version of is embedded SOL statements. The BIND program stores the plan in the database, usually assigning it the name of the application program that has created it. 95 ‘Rahul Publications Samal th ConanMCA LYEAR Il SEMESTER Increase the salary of teacher by 10% who are B.Te update teacher set salary=1:1*salary where qualification="B. Tech’; ‘The embedded SQL program for above written SQL statement will be: main() { exec sql include sqlea; exec sql declare table teacher (tid char(6) not null,tname char(20),sex char(1),ag¢ ‘number(3) qualification char(7),salary number{7),city varchar(15)); ‘MDisplay a message to user print{(“updating teacher salary who are B.Techn"); Rating ———————(@}—__ Saal th ConScnnyr OO =< wt DATABASE MANAGEMENT SYSTEMS Ilthis code executes the SQL statement exec sql update teacher set sala printf(update done”); exit(); 1*salary where qualificatio } xplanation Although the above shown program is very easy to understand, still we would like to discuss some ery basic features of embedded SQL. . 1, The embedded SQL statement can be uriten in any case (lower or upper). Although we should follow the convention of that programming language in which we are embedding the SQL statements. Fore.g., COBOL and FORTRAN ate written in upper case so, the SQL statements are also written in upper case, while in C, the SQL statements are written in lower case’as shown in above program. 2, Each embedded SQL statement begins with an introducer which indicates that it is a SQL statement. For most of the programming language EXEC SQL is used as an introducer. 3, Each embedded SQL statement ends with a terminator. There can be different terminators for different programming languages. For example, there is END EXEC for COBOL and a semicolon (;) for C. 4, The DECLARE TABLE statement is used to declare a table. With the use of DECLARE TABLE statement our program specifies the column and data type explicitly. 5, When we type SQL statement, we may make an error. This error is displayed by the interactive SQL program and we are prompted to type a new statement. ‘There can be two types of errors: compile time and runtime. 3.1.5 Dynamic SQL Q6. What is Dynamic SQL? Explain. Aas: ‘There are several limitations in static SQL. Although using the host variables (host variables allows us toinput values for search condition at run time), we ¢an achieve a litile bit dynamicness, for e.g., exec sql select tname, sex from teacher where salary > :sal; Here the salary will be asked on run time. But getting colurnn name or table asked at nun time not Possible with embedded SQL. For having such a feature we need dynamic SQL. Dynamic SQL Concepts > Indynamic SQL, the SQL statements are not hard coded in the programming language. The text of the SQL statement is asked at the run time to the user. Indynamic SQL, the SQL statements that are to be executed are not known until runtime, so DBMS can't get prepared for executing the statements in advanced. > When the program is executed, the DBMS takes the text of SQL statements to execute the statements that are ctecated in auch a mariner called statement sting, Once DBMS receives the text, it goes through a five steps execution as illustrated below. 7 Rahul Publications | Samal th CanSatnnHEAR See, q sa Statement Binary form of SQL statement Execute application plan The program constructs a asa statement as a string of text in one of its diate areas (cael » buffer). é ‘The program eset the SQL satements fo the DBMS with the EXECUTE IMMEDIATE satemert Procedures & Functions “A procedures ot function is a group or set of SQL and PL/SQL statements that perform @ specific task.” A function and procedure is a named PL/SQL Block which is similar . The major difference between a procedure and a function is, a-function must always return a value, but a procedure may or may not return a value. A. Procedures : Aprocudure isa named PL/SQL block which performs one or more specific task. This is similar t0 a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed '0 the procedure. ueeesy Rahul Publications : Samal th Conanyr yvit = DATABASE MANAGEMENT SYSTEMS The body consists of declaration section, execution section and exception section simiy IS © : is named for general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it i peated usage. We can pass parameters to procedures in three ways : Parameters Description IN type ‘These types of parameters are used to send values to stored procedures, OUT type “These types of parameters are used to get values from stored procedures. This is similar to a return type in functions, IN OUT wpe “These types of parameters are used to send values and get values from stored procedures. A proceditre may or may not return any value. Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name ( {IN, OUT, IN OUT} ,...) Is Declaration section ; BEGIN Execution section EXCEPTION Exception section END 1S- marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/ SQL Blocks. The code between IS and BEGIN forms the Declaration section, 5 The syntax within. the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code. How to execute a Procedure? There are two ways to execute a procedure : > From the SQL prompt : EXECUTE [or EXEC] procedure_name; > Within another procedure - simply use the procedure name : procedure_name; Example: Create table named emp have CREATE OR REPLACE PROCE! AS BEGIN two column id and salary with number datatype. DURE pl(id IN NUMBER, sal IN NUMBER) 99 Rahul Publications Samal th CaranSe MCA ‘ J YEAR I SEMESt, INSERT INTO emp VALUES(id, sal}; DBMD_OUTPUT.PUT_LINE(‘VALUE INSERTED. ); END; y B. - Functions: A function isanamed PL/SQL Blockwhich issimilartoa procedure. The major difference bere. ® Procedure and a function is, a function must always return a value, but a procedure may or r not return a value. Syntax: is of the datatype > Ina PL/SQL Statements like, : dbms_output.put_linelemp_details func); This line displays the value returned by the function . Example: create or replace function getsal (no IN number) return number is sal number(5); begin select salary into sal from emp where id: return sal; end; / @ Rahul Publications Samal th ConanENT SYSTEMS wt p DATABASE MANAGEM! 17 Recursive Queries 4 M gxplain about the concept of see queries (imp) jive queries are used to query hi 2 special nies Z table expressions to enzb! ‘assume the following hierarchical defi cqoate table category ( id integer not null primary key, name varchar(100) not null, parent_category integer references category ik With the following data: id | name a at ee 1 | Root Node | (nul) 2 | Software to 3 | Hardware 1a 4 | Notebooks 1.3 5 | Phones ris 6 | Applications pie: 7 | Database Software |? 8 | Relational DBMS 1 7 9 | Tools 7 10 | Commandiine tools | ® 11 | GUI Tools {19 12, Android Phones | 5 13 | iPhone 1 5 14 | Windows Phones 1 5 To (recursively) query all sub-categories for one parent, the following query can be used: with recursive cat_tree 2s ( select id, name, parent_category from category where name = ‘union all Rahul Publications $$$ Samal th Canton «Database Software’ — this defines the start of the recursionMCA select child id, child.name, child. parent_category from category as child join otis arent on parent id = child.parent_category — the self join to the CTE builds the recursion ) select * from cat_tree; ‘The above query will return the following result: BIN(18) 10010 + It converts a value to a binary string SELECT BINARY “GeeksforGeeks”; Output: GeeksforGeeks Ranut Publications * Saal th ConnPP OG —————eeeeEee “Il we DATABASE MANAGEMI ENT SYSTEMS COALESCE(: It returns the first non-null expression in alist ‘Syntax: ; T COAI SELECT COALESCE(NULL,NULL, 'GeeksforGeeks’ NULL,’Geoks'); Output: COALESCE(NULL,NULL, 'GeeksforGeeks' NULL Geeks’) GeeksforGeeks CONNECTION_ID(): It returns the unique connection ID for the current connection. ‘Syntax: SELECT CONNECTION _ID(); Output: CONNECTION_1D0 CURRENT_USER(): It returns the user name and host name for the MySQL account used by the server to authenticate the current client. Syntax: 8 SELECT CURRENT.USER(); Output: CURRENT_USERO root@localhost DATABASE(): It returns the name of the default database. Syntax: SELECT DATABASE(); Output: DATABASE() IFO: It returns one value if 2 condition is TRUE, o another value if a condition is FALSE. Syntax: ‘Rahul Publications Samal th CantLYEAR Il SEMEs, MCA SELECT IF(200<500, “YES”, “NO”}; Output: 1F(200<500, "YES*,"NO") 8. LAST_INSERT_ID(): Itreturns the first AUTO_INCREMENT value that was set by the most INSERT or UPDATE statement. ‘Syntax: ed NULLIF(145, 115) 10. SESSION_USER(: It returns the user name and host name for the current MySQL user. Syntax: SELECT SESSION_USER(); Output: SESSION_USER() teot@localhost Rahul Publications Saal th Connrt a DATABASE MANAGEMENT SYSTEMS 3YSTEM_USI : u. § |_USER(): It returns the user name and host name for the current MySQL user Syntax: SELECT SYSTEM_USER(); Output: ‘SYSTEM_USER() root@localhost 12, USER(: It returns the user name and host name for the current MySQL. user Syntax: SELECT USER(); Output: USERO 2 13, VERSION(;: It returns the version of the MySQL database: ‘Syntax: SELECT VERSION(); Output: VERSIONO 8.0.11 3.2.1 Features Of Good Relational Design 10, Explain withan example , how to design a good database. Consider the following example: classroom|building, room number, capacity) department(dept name, building, budget) course(course id, title, dept name, credits) instructor(ID, name, dept name, salary) section(course id, Sec Id, semester, yar, building, room number, time slot id) teaches(ID, course id, sec id, semester, year) student(ID, name, dept name, tot cred) -(405 Uy ‘Rahul Publications Samal th Caranmca LYEAR II SEMES takes(ID, course id, sec id, semester, year, grade) ‘advisor(s ID, i 1D) {time slot(time slot id, day, start time, end time) Prereq(course id, prereq id) Fig. : Schema for the university database. () Design Alternative: Larger Schemas Now, let us explore features of this relational database design as well as some alternatives, Suppose that instead of having the schemas instructor and department, we have the schema: inst dept (ID, name, salary, dept name, building, budget) sae : onsistent. In our original -exactl salary | deptname | building | Einstein] 95000 ] Physics Watson Wu 90000 | Finance’ | Painter El Said 60000 | History Painter Katz 75000 | Comp. Sci. | Taylor Kim 80000 | Elec. Eng. | Taylor Crick 72000 | Biology Watson Srinivasan | 65000 | Comp. Taylor Califieri_ | 62000 | History | Painter Brandt 92000 | Comp. Sci. | Taylor Mozart 40000 | Music Packard Gold 87000 | Physics Watson Singh 80000 | Finance | Painter Fig, : The inst dept table. (ii) Design Alternative: Smaller Schemas * dept name"!budget ‘Rahal Publications (08 }____ = —__— Rahul Publications Samal th CanSatnne ae unite — DATABASE MANAGE! MENT SYSTEMS «inst dept 4 the problem of the Inst d lepartment may need 2 to be repeated. Glven such A ttle, We now have sufficient information to recognl ema, Because dept name cannot be the primary key for Inst dept (because a ds fevero! (uples In the relation on wchema inet dept), the amount of a budgetmay have Observations such as these and the rules (lunctional dependencies in particu themallow the database designer to recognize situations where a schema ought to be spl into two or more schemas, It 1s not hard to see that the right way to decompose Inst de instructor and department as in the original design ppl is into schemas employee (ID, name, street, elly, salary) into the following two schemas: employee! (ID, name) employee? (name, street, city, salary) ample, lel us dssume two employees, both named Kim, work at the university and have the Asan following tuples in the relation on schema employee in the original desigt (57766, Kim, Main, Perryridge, 75000) (98776, Kim, North, Hampton, 67000) more [ore] 57746 | kim | Main 199776 | kim | Nonth ely “aay | Hampton | ‘67000 employe iy kim | Main | Pereyridge | 75000 Kim | Novth | Hampton | 67000 57766 | Kim ‘99776 | Kine (wy aly 57iv6| Kin | Man | Pore | 75000 $7706\ im | North | Manptor | 67000 $a776| im | Main | Dargie | 75000 gu7r6| Kim | Non | Hamm | 7000 Fig. : Loss of Information via a bad decomposition Figure shows these tuples, the resulting tuples using the schemas resulting from the decomposition, and the result if we attempted to regenerate the original tuples using a natural join, 3.2.2 Atomic Domains and First Normal Form Q11. Write about atomic domain and first normal form. Ans ; ‘Atomic in rational database mean that cannot be divided further. Atomic Domain is also known as Factorization Domain, An atomic domain is an integral domain. Atomic domains are different from unique factorization domains. Rahul Publications Samal th ConanMCA TABLE_PRODUCT [ProasetiO | Color] Pree 1 ted, green | 1599 2 vel 2388 3 oreen 1750 4 yellow pe | 980 5 red 2388 This table is not in first normal form because the [Color] column can contain multiple value ° example, the first row includes values “red” and “green.” To bring this table to first normal form, we split the table into two tables and now we 2 resulting tables: Rahul Publications Ce}ema UNIT = Il DATABASE MANAGEMENT SYSTEMS TABLE_PRODUCT_PRICE TABLE_PRODUCT_COLOR Product 1D Price Product ID 1 1599 2 23.99 3 17.50 4 9.99 5 2900] Now first normal form is satisfied, as the columns on each table all hold just one value. 3.2.3 Functional-dependency Theory Q12. What is a Functional Dependency? Explain. Mess (Imp.) Functional Dependency (FD) determines the relation of one attribute to another attribute in a database management system (DBMS) system. Functional dependency helps you to maintain the quality of data in the database. A functional dependency is denoted by an arrow "!, The functional dependency of X on Y is represented by X "! Y. Functional Dependency plays a vital role to find the difference between good and bad database design. Example: Employee number Employee Name Salary City 1 Dana 50000 San Francisco 2 Francis 38000 London 3 Andrew 25000 Tokyo In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc, By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number. Rules of Functional Dependencies Below given are the Three most important rules for Functional Dependency: > Reflexive rule ~, IfX isa set of attributes and Y is_subset_of X, then X holds a value of ¥ > Augmentation rule: When x -> y holds, and c is attribute set, then ac -> be also holds, That is adding attributes which do not change the basic dependencies, > Transitivity rule: This rule is very much similar to the transitive rule in algebra ifx-> y holds and y-> zholds, then x -> z also holds. X -> y is called as functionally that determines y. ‘Types of Functional Dependencies {i) Multivalued dependency: 400) Samal th Canton1 MCA YEAR I, (i) Tyivial functional dependency: i) Non-trivial functional dependency: s (iv) Transitive dependency: (ii) Multivalued dependency in DBMS ‘Multivalued dependency occurs in the situation where there are multiple independent attributes in a single table. A multivalued dependency is a complete constraint between two sets in a relation. It requires that certain tuples be present in a relation. Example: Ml hg of tis | ‘For example: Consider this table with two columns Emp id and Emp name. {Emp_id, Emp_name} {Emp_id,Emp_name}. Rahul Publications > Emp_id is a trval functional dependency as Emp_idis a b* Samal th ConanUNIT = Ill DATABASE MANAGEMENT SYSTEMS (it) Non trivial functional dependency in DBMS Functional dependency which where Bisnot a subset of A. Inn man NOW! a a nontrivial dependency occurs when A->-B holds true araronial epersnses ‘ationship, ifattribute Bis not a subset of attribute A, then it is considered Company CEO Age Microsoft Satya Nadella 51 Google Sundar Piehai 46 Apple Tim Cook. 57 Example: c - ; & — > (CEO) (if we know the Company, we knows the CEO name} ut is not a subset of Company, and hence it's non-trivial functional dependency. (iv) Transitive Dependency: A transitive is a : | oo fanctional dapendct ee of functional dependency which happens when tis indirectly formed by two Example: Company CEO ‘Age Microsoft Satya Nadella 51 ‘ Google ‘Sundar Pichai 46 Alibaba Jack Ma 54 {Company} -> {CEO} (if we know the compay, we know its CEO's name) {CEO } -> {Age} If we know the CEO, we know the Age Therefore according to the rule of rule of transitive dependency: { Company} -> {Age} should hold, that makes sense because if we know the company name, we can know his age. 3.2.4 Decomposition using Functional Dependencies Q13. Explain , how to use decomposition in functional dependency. Aus: Decomposition is the reverse of the Union rule, If you have a table that appears to contain two entities that are determined by the same PK, consider breaking them up into two tables. This rule states that ifX determines Y and Z, then X determines Y and X determines Z separately, > We can avoid update anomalies by decomposition of the original relation. > The relation scema R is decomposed into relation schemas: > {RI,R2,R3,.....Rn} in such a way that Ri UR2 U... URn =R Gan ity) Rahal Publications Samal th Conan| YEAR Wy, " Mek ioe anscd Ener Example: city, Assets, Cusomer_name, Loan nun. = (Branch_name, | tion Lending_schema = (Brat into folowing two relat ' The Lending schema is decomposed , Branch_cily, Assets, Cusomer nar) | namé | 2) Branch.cusiomer shen © Coes Loan_number, Amount) | 2) Customer Joan. schema = (Cusomer nan” = Customer Name rer es EE voice [Downtown Brooklyn ae smith Redwood Palo Alto, * Tackson p00 Horseneck 1 | Peryridge Jones Mianws Horseneek s Northtown ye 7 Pte’ SA Jones 2000 ‘smith on | S Tones a1 10000 jos Ls 9000 Saith 2000 | Perryridge — Horseneck Iackson 1500 | Mianus -Horseneck So00p Jones La 900 { Mianus —Horseneck 80000, Jones lar 1900 E Northtown Rye 37000 Hayes Lao 00 The relation Branch_customer {B4" Customer_loan 1, If the relation Branch_customer and Customer_loan is compared with the original lending rl we observe that: > Every tuple in lending relation is in Branch_customer and Customer_loan relation. » The relation Branch_cust~mer and Customer_loan contain following additional tuples (Downtown, Brooklyn, 90000,Jones,L-11,9000) > — (Mianus,Horseneck,80000,Jones,L-17,1000) Rahul Publications Samal th Canton v—_— rr ar l ow DATABASE MANAGEMENT SYSTEMS g, Consider the query: y “Find all the branche: > ee ranches that have made a loan in an amount less than Rs. 1000." > result of this query using the lending relation is : Mianus ‘The result of the same query using the relation Branch_customer and Customer_loan is: Downtown and Mianus. From the above two s cases we can say that the decomposition of lending relation into Branch_customer ar - nd Customer Joan results in bad database design. It results in loss of information and repetition of information of lending relation into scomposition. 4, Because of loss of information, we call the decomposit Branch_customer and Customer_loan a lossy decomposition or a lossy-join de: Desirable Properties of Decomposition Decomposition help us eliminate redundancy, root of data anomalies. ‘There are two important properties associated with decomposition: 1. Lossless join 2. Dependency Preservation 1. Lossless Join This property ensures that any instance o the original eatin can be identifies from the corresponding instances in the smaller relation. > Let Rbe a relational schema, andlet F bea set of form a decomposition of R. composition is a lossless join decomposition of R if functional dependencies on R. Let R1 and R2 + least one of the following functional - This des dependencies is in F+ RIOR2 > RI R1OR2 — R2 In other word, if B1 GR2 forms a su join decomposition. Example Let R(A, perkey of ether RL or R2, the decomposition of Fis a lossless: B, C) and F = (AB). Check the decomposition of Rinto R1(A, B) and R2{A, C). RIO R2 is A which is comman attribute. A > Bis he FD in E (augmentation re) A — AB which is relation R1. Thus, RIO R2 is satisfies. Hence, above decompositio 1 is lossless-join decomposition.MCA Let R(A, B,C) and F=(A-»B). Check the decomposition of R into RA(A, B) and Rayp, R1O R2 is B which is comman attribute. But B is not a superkey of either R1 or R2. Hence this decomposition is not a lossless-join decomposition. 2. Decomposition preservatiom This property ensures that a constraint on the original relation can be maintained by simp! some constraint on each of the smaller relation. > — Given a relational schema R and set of fuctional dependencies associated with it is F (BoB, CC, “leo: In R2 the following dependencies hold: F2"= set of nontrivial dependencies hold on RI and R2: Fo = > F'=FI'UF2' = (A+B, BC, trivial dependencies) > InF'all the original dependencies occur, so this decomposition preserves dependencies Samal th CanSatnn