Que2.1. What’s relational model? Explain with illustration. Answer 1. A relational model is a collection of abstract tools for describing data, data connections, data semantics and thickness constraints. 2. It’s the primary data model for marketable data processing operations. 3. The relational model uses collection of tables to represent both data and the connections among those data. 4. Each table has multiple columns and each column has a unique name. Que2.2. Explain constraints and its types. Answer 1. A constraint is a rule that’s used for optimization purposes. 2. Constraints apply limits to the data or type of data that can be fitted / streamlined deleted from a table. 3. The whole purpose of constraints is to maintain the data integrity during an update/ delete/ insert into a table. Types of constraints 1. NOT NULL NOT NULL constraint makes sure that a column doesn’t hold NULL value. ii. When we don’t give value for a particular column while fitting a record into a table, it takes NULL value by dereliction. iii. By specifying NULL constraint, we make sure that a particular column can not have NULL values. 2. UNIQUE UNIQUE constraint enforces a column or set of columns to have unique values. , it means that particular column If a column has a unique constraint. can not have indistinguishable values in a table. 3. dereliction i. The dereliction constraint provides a dereliction value to a column when there is no value handed while fitting a record into a table. 4. CHECK i. This constraint is used for specifying range of values for a particular column of a table. ii. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range. 5. crucial constraints Primary crucial Primary crucial uniquely identifies each record in a table. b. It must have unique values and can not contain null. ii. Foreign crucial Foreign keys are the columns of a table that points to the primary key of another table. b. They act as a extract between tables. 6. sphere constraints i. Each table has certain set of columns and each column allows a same type of data, grounded on its data type. ii. The column doesn’t accept values of any other data type Que2.3. Explain integrity constraints. Answer 1. Integrity constraints give a way of icing that changes made to the database by authorized druggies don’t affect in a loss of data thickness. 2. A form of integrity constraint with ER models is crucial affirmations certain attributes form a seeker key for the reality set. form of a relationship mapping cardinalities 1- 1, 1- numerous and numerous- numerous. 3. An integrity constraint can be any arbitrary predicate applied to the database. 4. Integrity constraints are used to insure delicacy and thickness of data in a relational database. Que2.5. What’s relational algebra? bandy its introductory operations. Answer 1. The relational algebra is a procedural query language. 2. It consists of a set of operations that take one or two relations as input and produces a new relation as a result. 3. The operations in the relational algebra are select, design, union, set difference, cartesian product and brand. introductory relational algebra operations are as follows 1. Select operation a. The select operation selects tuples that satisfies a given predicate. Select operation is denoted by sigma(). c. The predicate appears as a subscript to. d. The argument relation is in gap after the. 2. design operation a. The design operation is a unary operation that returns its argument relation with certain attributes left out. b. In design operation duplicate rows are excluded. protuberance is denoted by pi(). 3. Set difference operation a. The set difference operation denoted by allows us to find tuples that are in one relation but aren’t in another. b. The expression r s produces a relation containing those tuples in r but not ins. 4. Cartesian product operation a. The cartesian product operation, denoted by a cross( ×), allows us to combine information from any two relations. The cartesian product of relations r1 and r2 is written as r1 × r2. 5. Brand operation a. The rename driver is denoted by rho(). Given a relational algebra expression E, x( E) returns the result of expression E under the namex. c. The rename operation can be used to brand a relation r to get the same relation under a new name. d. The rename operation can be used to gain a new relation with new names given to the original attributes of original relation as xA1, xA2,., xAn( E) Que2.7. What are the fresh operations in relational algebra? Answer The fresh operations of relational algebra are 1. Set crossroad operation Set crossroad is denoted by, and returns a relation that contains tuples that are in both of its argument relations. The set crossroad operation is written as r s = r –( r – s) 2. Natural join operation a. The natural join is a double operation that allows us to combine certain selections and a cartesian product into one operation. It is denoted by the join symbol. b. The natural join operation forms a cartesian product of its two arguments, performs a selection forcing equivalency on those attributes that appear in both relation schemas and eventually removes indistinguishable attributes. Database Management System 2 – 9 A( CS/ IT- Sem- 5) 3. Division operation 1. In division operation, division driver is denoted by the symbol E(). 2. The relation r ÷ s is a relation on schema R – S. A tuple t is in r ÷ s if and only if both of two conditions hold t is in R – S( r). b. For every tuple ts in s, there’s a tuple tr in r satisfying both of the following tr( S) = ts( S) ii. tr( R – S) = t 3. The division operation can be written in terms of abecedarian operation as follows r ÷ s = R – S( r) – R – S(( R – S( r) × s) – R – S, S( r)) 4. Assignment operation The assignment operation, denoted by, works like assignment in a programming language. Que2.8. Give the following queries in the relational algebra using the relational schema pupil( id, name) enrolled( id, law) subject( law, speaker) i. What are the names of scholars enrolled in cs3020? ii. Which subjects is Hector taking? iii. Who teaches cs1500? iv. Who teaches cs1500 or cs3020? v. Who teaches at least two different subjects? vi. What are the names of scholars in cs1500 or cs307? vii. What are the names of scholars in both cs 1500 and cs1200? AKTU 2019- 20, Marks 07 Answer name( law = cs3020( pupil enrolledin)) ii. law( name = Hector( pupil enrolledin)) iii. speaker( law = cs1500( subject)) iv. speaker( law = cs1500 law = cs3020( subject)) v. For this query we’ve to relate subject to itself. To disambiguate the relation, we will call the subject relation R andS. speaker(R.lecture = S.lecturerR.code<>S.code( R S)) vi. name( law = cs1500( pupil enrolledin))( name( law = cs307( pupil enrolledin))) vii. name( law = cs1500( pupil enrolledin)) name( law = cs1200( pupil enrolledin)) Que2.9. What’s relational math? Describe its important characteristics. Explain tuple and sphere math. OR What’s tuple relational math and sphere relational math? Answer 1. Relational math is anon-procedural query language. 2. Relational math is a query system where queries are expressed as formulas conforming of a number of variables and an expression involving these variables. 3. In a relational math, there’s no description of how to estimate a query. Important characteristics of relational math 1. The relational math is used to measure the picky power of relational languages. 2. Relational math is grounded on predicate math. 3. In relational math, stoner isn’t concerned with the procedure to gain the results. 4. In relational math, affair is available without knowing the system about its reclamation. Tuple Relational Calculus( TRC) 1. The TRC is anon-procedural query language. 2. It describes the asked information without giving a specific procedure for carrying that information. 3. A query in TRC is expressed as t| P( t)} This is, that it’s the set of all tuples t similar that predicate P is true fort. The memorandum t( A) is used to denote the value of tuple t on trait A and t r is used to denote that tuple t is in relationr. 4. A tuple variable is said to be a free variable unless it’s quantified by a or. 5. Formulae are erected using the tittles and the following rules a. An snippet is a formula. , also so are ¬ P1 and( P1), If P1 is a formula. , also so are P1 P2, P1 P2 and If P2 and P1 are formulae. P1 P2. , and r is a If P1( s) is a formula containing a free tuple variables. relation, also s r( P1( s)) and s r( P1( s)) are also formulae. Domain Relational Calculus( DRC) 1. DRC uses sphere variables that take on values from an attributes sphere, rather than values for an entire tuple. 2. An expression in the DRC is of the form < x1, x2,., xn>| P( x1, x2,, xn)} where x1, x2,, xn represent sphere variable. P represents a formula compose of tittles. 3. An snippet in DRC has one of the following forms < x1, x2,., xn> r, where r is a relation on n attributes and x1, x2,., xn are sphere variables or sphere constant. x y, where x and y are sphere variable and is a comparison driver(<,, = ,,>,). The attributes x and y must have the sphere that can be compared. x c, where x is a sphere variable, is a comparison driver and c is a constant in the sphere of the trait for which x is a sphere variable. 4. Following are the rules to make up the formula a. An snippet is a formula. b. If P1 is a formula also so is P1. , also so are P1 P2, P1 P2 and If P1 and P2 are formula. P1 P2. , where x is a sphere variable, also If P1( x) is a formula inx. x( P1( x)) and x( P1( x)) are also formulae.
