Regarding a New Dummies Guide on Databases

I have authored a brand new Dummies' Guide on Database Systems, titled, "The Dummies' Guide to Database Systems: An Assembly of Information" meant for anybody interested in databases. That is anyone can master the concepts of Databases, not only undergraduate students in Computer Science and/or Engineering but anybody, you name it.

That is because majority of the chapters have been written with that purpose in mind i.e, in an easy to understand manner. It is my pleasure to be able to write a book like this which should not only be educational and informative but also an enjoyable and productive read at the same time. So what are you waiting for? Simply click the link below and grab a free complimentary copy:

http://www.free-ebooks.net/ebook/The-Dummies-Guide-to-Database-Systems-An-Assembly-of-Information

You will love to read it and have an enjoyable read. Do share the above link with friends and colleagues.

All the best,

Rosina S Khan

(Alias Pinky Islam)

SQL Continued VI

Consider the following table schemas and then go over SQL queries and their corresponding expressions in response to the queries that follow:

Customer:

customer_id | customer_name | customer_street | customer_city

Branch

branch_name | branch_city | assets

Account:

account_number | type | balance | branch_name

                                                             fk

Deposit:

customer_id | account_number

Loan:

loan_number | branch_name | amount

                                 fk

Borrow:

customer_id | loan_number

Payment:

pay_no | loan_number | date | amount

                       fk

Employee:

employee_id | name | designation | contact_no

Serves:

customer_id | employee_id | date

Let us consider the following relations on loan and borrow from the banking example:

Table 1: Loan relation

loan_number	branch_name	Amount
L-170	Kakrail	3000
L-230	Motijheel	4000
L-260	Dhanmondi	1700

                                 

Table 2: Borrow relation

customer_name	loan_number
Kamal	L-170
Tinku	L-230
Jones	L-155

We start with a simple example of inner joins:

loan inner join borrow on loan.loan_number = borrow.loan_number

The expression computes the normal join of loan and borrow relations with the join condition being loan.loan_number = borrow.loan_number

The result of the above join is shown below:

Table 3: The result of loan inner join on loan.loan_number = borrow.loan_number

 

loan_number	branch_name	amount	customer_name	loan_number
L-170	Kakrail	3000	Kamal	L-170
L-230	Motijheel	4000	Tinku	L-230

We may rename the result relation of a join and the attributes of the result relation by using an ‘as’ clause as shown below:

loan inner join borrow on loan.loan_number = borrow.loan_number as lb(loan_number, branch, amount, cust, cust_loan_num)

We rename the second occurrence of loan_number to cust_loan_num. The ordering of the attributes in the result of the join is important for the renaming.

Now we consider an example of left outer-join operation:

Loan left outer join borrow on loan.loan_number = borrow.loan_number

The following table shows the result of the above left outer join.

Table 4: The result of loan left outer join borrow on loan.loan_number = borrow.loan_number

loan_number	branch_name	amount	customer_name	loan_number
L-170	Kakrail	3000	Kamal	L-170
L-230	Motijheel	4000	Tinku	L-230
L-260	Dhanmondi	1700	NULL	NULL

In the resultant relation, the tuples (L-170, Kakrail, 3000) and (L-230, Motijheel, 4000) from loan join with tuples from borrow and appear in the result of the inner join and hence in the result of the left outer join. On the other hand the tuple(L-260, Dhanmondi, 1700, NULL, NULL) is present in the result of the left outer join.

Finally we consider an example of natural-join operation:

loan natural inner join borrow

This expression computes the natural join of the two relations. The only attribute name common to loan and borrow is loan_number and it appears once in the result unlike the result of the join with the ‘on’ condition.

The right outer join is symmetric to the left outer join. Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls and are added to the result of the right outer join.

Here is an example of combining the natural-join condition with the right outer join type:

loan natural right outer join borrow

The result of the above erxpression is shown below:

Table 5: The result of loan natural right outer join borrow 

loan_number	branch_name	amount	customer_name
L-170	Kakrail	3000	Kamal
L-230	Motijheel	4000	Tinku
L-155	NULL	NULL	Jones

Now we consider a full outer join operation. For example the following table shows the result of full outer join expression.

loan full outer join borrow using (loan_number)

Table 6: The result of loan full outer join borrow using (loan_number)

loan_number	branch_name	amount	customer_name
L-170	Kakrail	3000	Kamal
L-230	Motijheel	4000	Tinku
L-260	Dhanmodi	1700	NULL
L-155	NULL	NULL	Jones

As another example, we can write the query, “Find all customers who have either an account or loan but not both at the bank” with natural full outer join as:

SELECT customer_name FROM (deposit natural full outer join borrow)

WHERE account_number is NULL OR loan_number is NULL

 

 

 

 

 

 

 

 

SQL Expressions Continued V

Consider the following table schemas and then go over SQL queries and their corresponding expressions in response to the queries that follow:

Student:

matNr | sName

Professor:

pname | psalary

Class:

classNr | room | day | pname

                                     fk

Takes:

matNr | classNr | grade

TA:

matNr | classNr | hours | tasalary

Schema updates

Adding a column:

ALTER TABLE tablename

ADD column_name data_type

Example:

ALTER TABLE Student

Add Birthday Date

·         NOT NULL cannot be specified here

Dropping a column:

ALTER TABLE tablename

DROP columnname CASCADE|RESTRICT

DROP requires appending CASCADE or RESTRICT

With CASCADE, all references and views that refer to this column will automatically be deleted with the drop. With RESTRICT, the dropping is executed only if there are no such references to this column.

Example:

ALTER TABLE Student

DROP Birthday CASCADE

Definition of a Domain

A domain is essentially a data type with optional constraints (restrictions on the allowed set of values).

CREATE DOMAIN domainname AS type

[CHECK (condition)]

Examples:

CREATE DOMAIN MatNr AS CHAR(6);

CREATE DOMAIN GenderType AS CHAR

CHECK (VALUE IN (‘M’, ‘F’));

The second example allows to define restrictions on the values in the desired domain GenderType.

Creation and Deletion of Views

 CREATE VIEW view_name { (column name, ……)} AS

SELECT_statement

DROP VIEW view_name;

Example:

CREATE VIEW CSE303_Students (name, matNo) AS

SELECT sName, matNr

FROM Student, Takes

WHERE Student.matNr = Takes.matNr

And ClassNr = ‘CSE303’;

Normally, views are not stored like base tables but are generated just in time when a query accesses the view. Some systems allow storing views for performance reasons.

Referring to the Bank Enterprise, here are some sample SQL queries and corresponding SQL expressions:

Customer:

customer_id | customer_name | customer_street | customer_city

Branch

branch_name | branch_city | assets

Account:

account_number | type | balance | branch_name

                                                             fk

Deposit:

customer_id | account_number

Loan:

loan_number | branch_name | amount

                                 fk

Borrow:

customer_id | loan_number

Payment:

pay_no | loan_number | date | amount

                       fk

Employee:

employee_id | name | designation | contact_no

Serves:

customer_id | employee_id | date

The Rename Operation

 Q For all customers who have a loan from the bank, find their names, loan numbers and loan amount.

While we can write the SQL expression for the above as

SELECT customer_name, loan_number, amount FROM borrow, loan WHERE borrow.loan_number=loan.loan_number

We can write the above expression using a rename operation as:

Select customer_name, T.loan_number, S.amount

From borrow AS T, loan as S

WHERE T.loan_number =  S.loan_number

UNION & UNION ALL

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

We can write the expressiom for the above query using a normal UNION operation as:

(SELECT customer_name FROM deposit

UNION

(SELECT customer_name FROM borrow)

If we want to retain all duplicates, we must write UNION ALL in place of UNION as:

(SELECT customer_name FROM deposit)

           UNION ALL

(SELECT customer_name FROM borrow)

 

INTERSECT  &  INTERSECT ALL

Q Find all customers who have both a loan and an account at the bank.

(SELECT DISTINCT customer_name FROM deposit)

                      INTERSECT

(SELECT DISTINCT customer_name FROM borrow)

If we want to retain duplicates, we write:

(SELECT DISTINCT customer_name FROM deposit)

                      INTERSECT ALL

(SELECT DISTINCT customer_name FROM borrow)