DATA BASE

Q. Define relational database schema, domain, relation, tuple, Cartesian product, query language, procedural language, and non – procedural language.
Ans: Relational database schema: A relational database schema is a listing showing relation names, attribute names, key attributes and foreign keys.
Attribute: The column header of the table is known as attribute.
Domain: For each attribute, there is a set of permitted values called the domain of that attribute.
Relation: A relation is a two dimensional table containing rows and columns of data.
Tuple: The row of a table is known as tuple of that table.
Query language: A query language is a language in which a user requests information from the database. These languages are usually on a level higher than that of a standard programming language. Query languages can be categorized as either procedural or nonprocedural.
Procedural language: In a procedural language, the user instructs the system, to perform a sequence of operations on the database to compute the desired result.
Non – procedural language: In a nonprocedural language, the user describes the desired information without giving a specific procedure for obtaining that procedure.
Q. What are the fundamental in the relational algebra?
Ans: The fundamental of relational algebra operations are –
The Select operation: The select operation selects tuples that satisfy a given predicate. The lower case Greek letter sigma (σ) to denote selection. The predicate appears as a subscript to σ. The argument relation is in parentheses after the σ. To select tuples of the loan relation where the branch is “Perryridge” we write,
σ_(branch_name=""Perryridge\"" ) (loan)
For comparisons, =, ≠, <, ≤, >, ≥ operators are used in the selection predicate. Furthermore, and (Λ), or (V), and not (¬) operators are used to combine several predicates into a larger predicate. Thus, to find those tuples pertaining to loans or more than $1200 made by the Perryridge branch, we write –
σ_(branch_name =""Perryridge\" " ∧ amount >1200) (loan)
The Project operation: The project operation is a unary operation that returns its argument relation, with certain attributes left out. Projection is denoted by uppercase Greek letter pi (Π). Those attribute that are appear in the result as a subscript to Π. The argument relation follows in parentheses. The query to list all loan numbers and the amount of the loan as –
Π_(loan_number ,amount ) (loan)
The Union operation: The union operation is used to combine two queries from where same type of output will come out. Union is denoted by ∪. As for example, the query will be for the statement all customer_name who have an account and loan in the bank –
Π_(customer_name) (borrower)∪Π_(customer_name) (depositor)
The Set – Difference operation: The set difference operation, denoted by –, allows to find tuples that are in one relation but are not in another. The expression r – s produces a relation containing those tuples in r but not in s. for example, all customers of the bank who have an account but not a loan by writing –
Π_(customer_name) (depositor)-Π_(customer_name) (borrower)
The Cartesian – Product operation: The Cartesian – product operation, denoted by a cross (×), allows to combine information from any two relations. The Cartesian product of relations r_1 and r_2 as r_1 ×〖 r〗_2. For example, the relation schema for r=borrower×loan is
(█(borrower.customer_name,borrower.loan_number,loan.loan_number,loan.branch_name,@ loan.amount))
The Rename operation: The rename operator, denoted by the lowercase Greek letter rho (ρ). Given a relation algebra expression E, the expression –
ρ_x (E)
returns the result of expression E under the name x.
A second form of the rename operation is as follows. Assume that a relational algebra expression E has arity n. Then, the expression –
ρ_x(A_1,A_2,……,A_n ) (E)
For the example, the query to find the largest account balance in the bank can be written as:
Π_balance (account)-Π_(account.balance(σ_(account.balance < d.balance) (account×ρ_d (account) ) ) )