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.

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.

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)

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.

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)}

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 key⊆Candidate Key⊆Super Key.A key (primary, candidate or super) is the property of the entire relation, rather than of the individual tuples

The fundamentals Operation of Relational Algebra :

- 1.select(unary)

- project(unary)

- union(binary)

- set-difference(binary)

- cartesian product(binary)

- rename(binary)

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_number}amount^{(loan)}

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)}

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)

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)}

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.

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)

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

(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)

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.