CSE SOLUTION SITE



Database Management System Question List | Solve

What is Database Management System?.

Database Management System :


A database management system(DBMS) is a collection of interrelated data and a set of programs to access those data.The collection of data, usually reffered to as the database, contains information relevant to an enterprise.


What is Key?

Key :


The tuples within a given relation is distinguished in terms of its attributes.That is the value of the attributes of a tuple must be much be such that they can uniquely identofy the tuple.


Write down the Except Operation(Minus)

The Except Operation(Minus):


Find all customers having a loan, an account or both at the bank

(select customer_name from depositor)

minus
(select customer_name from borrower)

the intersect operation automatically eliminates duplicates.If we want to retain all duplicates :(oracle does not support)

(select customer_name from depositor)

minus all

(select customer_name from borrower)


What is Candidate Key?

Candidate Key :


The superkey for which no proper subsets of superkey, is a candidate key. So the minimul superkey are called keys.It is possible that several distinct sets of attributes could serve as a candidate key.

  • In customer_schema - {customer_name, customer_street}, {customer_id} are candidate keys.{customer_id, customer_name} is not a candidate key because customer_id itself a superkey.


Write down the Union Opeation?

The Union Opeation :


  • This is binary operation and denoted by ∪

  • Union must be taken compitable relation

  • union clause will eliminate duplicate values.



    • For a union operation r∪s to be valid , we require that two conditions holds:

    • (a) The relation r and s must be of the same arity.That means, they must have the same number of attributes

    • (b) The domains of attributes of r ith and the ith attributes of s must be the same, for all i.



  • Example :Find all customers of the bank who have both a loan and an account ∏customer_name(borrower) ∪ ∏customer_name(depositor)


What is Primary Key?

Primary Key :


The primary key is to denoted a candidate key that is chosen by the database designers as the principal means of identifying tuples within a relation.

  • In customer_schema -{customer_id}

  • So, Primary keyCandidate KeySuper Key.A key (primary, candidate or super) is the property of the entire relation, rather than of the individual tuples


Write down the fundamentals Operation of Relational Algebra

The fundamentals Operation of Relational Algebra :


  • 1.select(unary)

  • project(unary)

  • union(binary)

  • set-difference(binary)

  • cartesian product(binary)

  • rename(binary)


Write down the Project Operation

The Project Operation :


  • This is a bunary operation that returns its argument relation with certain attribute/attributes left out (∩)

  • Since relation is a set, any duplicate rows are eliminated

  • This is denoted by uppercase Greek letter pi (∏) and we list those attributes those we wish to appear in the result as a subscript of (∏).The argument relation follows in parenthesis.

  • Example :Find all loan numbers and the amount of the loan ∏loan_numberamount(loan)

Write down the Set Difference Operation

The Set Difference Operation :


  • This is binary operation

  • Allows us to find tuples that a one relation and are not in another.The expression- r-s produces a relation containing those tuples in r but not in s

  • set difference must also be taken between compitable relations.So the said two conditions are also applicable here.

  • Example :Find all customers of the bank who have both a loan and an account ∏customer_name(depositor) - ∏customer_name(borrower)


Write down the Additional Relational Algebra Operation

The Additional Relational Algebra Operation :


  • The fundamental operations of relational algebra are sufficient to express any relational algebra query but some common queries are lengthy to express

  • Additional operations do not add any power to the algebra, but simplfy common queries.


    • 1.set intersection(binary)

    • 2.natural join(binary)

    • division(binary)

    • assignment(unary)


Write down the Set Intersect Operation

The Set Intersect Operation :


  • This is a binary operation and denoted by (∩)

  • It must be taken between compitable relations

  • Can be rewritten replacing th intersection operation with a pair of set-difference operation : r∩s = r-(r-s) or r∩s=s-(s-r)

  • Example :Find all customers of the bank who have both a loan and an account ∏customer_name(borrower) ∩ ∏customer_name(depositor)

Write down the Set Operations

The Set Operations :


  • The SQL operations union, intersect, and except operate an relations and correspond to the relational-algebra operations U, ∩ and -,

  • Like union, intersection, and set difference in relational algebra, the relations participating in the operations must be compitable; that is, they must have the same set of attributes.


Write down the Union Operation

The Union Operation :


Find all customers having a loan, an account or both at the bank

(select customer_name from depositor)

union

(select customer_name from borrower)

unlike the select clause, the union operation automatically eliminates duplicates.If we want to retain all duplicates :

(select customer_name from depositor)

union all

(select customer_name from borrower)


Write down the Intersect Operation

The Intersect Operation :
Find all customers having a loan, an account or both at the bank

(select customer_name from depositor)

intersect

(select customer_name from borrower)

the intersect operation automatically eliminates duplicates.If we want to retain all duplicates :

(select customer_name from depositor)

intersect all

(select customer_name from borrower)


What is Integraty Constraints?

Integraty Constraints :


Integrity constraints ensure that that changes made to the database by authorized users do not result in a loss of data consistency.thus, integrity constraints quard against accidental damage to the database.

© Copyright & reserved CSE Solve