Friday, January 29, 2016

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)

Thursday, January 21, 2016

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

 

 

 

 

 

 

 

 


Thursday, January 14, 2016

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)