SQL Queries on Branch, Customer, Loan, borrower, account & depositor

 

Q) Consider following database schema, Write the queries using SQL

branch (branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)

loan (loan_number, branch_name, amount)

borrower (customer_name, loan_number)

account (account_number, branch_name, balance)

depositor (customer_name, account_number)

  

-- a)        Find all customers who have both a loan and an account (eliminate duplicates if exists)

SELECT distinct customer_name from borrower

WHERE customer_name IN (SELECT customer_name FROM depositor );

 

-- b)        Find all customers who have a loan or an account or both (eliminate duplicates if exists)

SELECT customer_name FROM borrower

UNION 

SELECT customer_name FROM depositor;

 

-- c) Find all customers who have a loan but not an account (eliminate duplicates if exists)

SELECT DISTINCT customer_name FROM borrower

WHERE customer_name NOT IN (SELECT customer_name FROM depositor );

 

-- d)        Find all customer who have a loan or an account or both

SELECT customer_name FROM borrower

UNION ALL

SELECT customer_name FROM depositor;

 

-- e)        Find all customer who have both a loan and an account

SELECT customer_name FROM borrower

WHERE customer_name IN (SELECT customer_name FROM depositor);

----Demonstration

use databasename;

create table BRANCH (

branch_name varchar(20) primary key,

branch_city varchar(20),

assets int

);

create table CUSTOMER (

customer_name varchar(20) primary key,

customer_street varchar(20), 

customer_city varchar(20)

);

create table ACCOUNT (

account_number int primary key, 

branch_name varchar(20), 

amount int,

foreign key (branch_name) references branch(branch_name)

);

create table LOAN (

loan_number int primary key,

branch_name varchar(20), 

amount int,

foreign key (branch_name) references branch(branch_name)

);

create table DEPOSITOR (

customer_name varchar(20),

account_number int,

primary key(customer_name, account_number),

foreign key (customer_name) references customer(customer_name),

foreign key (account_number) references account(account_number)

);

create table BORROWER (

customer_name varchar(20),

loan_number int,

primary key(customer_name, loan_number),

foreign key (customer_name) references customer(customer_name),

foreign key (loan_number) references loan(loan_number)

);

create table EMPLOYEE (

employee_name varchar(20), 

branch_name varchar(20), 

salary int,

primary key(employee_name, branch_name),

foreign key (branch_name) references branch(branch_name)

);

INSERT INTO BRANCH VALUES 

("JP Nagar","Bangalore",1000),

("Charchgate","Mumbai",1500),

("Red Hill","Hyderabad",2000),

("Jaya Nagar","Bangalore",5000);

 

INSERT INTO CUSTOMER VALUES

("c1","s1","Bangalore"),

("c2","s2","Hyderabad"),

("c3","s3","Mumbai"),

("c4","s4","Kolkatta");

INSERT INTO ACCOUNT VALUES

(101,"JP NAGAR",5000),

(102,"Red Hill",3000),

(103,"Charchgate",2000);

INSERT INTO LOAN VALUES

(1,"JP NAGAR",3000),

(2,"Charchgate",1000),

(3,"Red Hill",5000),

(4,"Jaya Nagar",6000);

INSERT INTO DEPOSITOR VALUES

("c1",101),

("c3",103);

INSERT INTO BORROWER VALUES

("c1",1),

("c4",4);

-- Q) Consider following database schema, Write the queries using SQL

-- branch (branch_name, branch_city, assets)

-- customer (customer_name, customer_street, customer_city)

-- loan (loan_number, branch_name, amount)

-- borrower (customer_name, loan_number)

-- account (account_number, branch_name, balance)

-- depositor (customer_name, account_number)

-- a) Find all customers who have both a loan and an account (eliminate duplicates if exists)

SELECT distinct customer_name from borrower 

WHERE customer_name IN (SELECT customer_name FROM depositor );

-- b) Find all customers who have a loan or an account or both (eliminate duplicates if exists)

SELECT customer_name FROM borrower 

UNION  

SELECT customer_name FROM depositor;

-- c) Find all customers who have a loan but not an account (eliminate duplicates if exists)

SELECT DISTINCT customer_name FROM borrower 

WHERE customer_name NOT IN (SELECT customer_name FROM depositor );

-- d) Find all customer who have a loan or an account or both

SELECT customer_name FROM borrower 

UNION ALL 

SELECT customer_name FROM depositor;

-- e) Find all customer who have both a loan and an account

SELECT customer_name FROM borrower 

WHERE customer_name IN (SELECT customer_name FROM depositor );

Table and Type Inheritance in DBMS (Oracle)

 

What is inheritance?

Inheritance enables you to share attributes between objects such that a subclass inherits attributes from its parent class. 

Inheritance is the process that allows a type or a table to acquire the properties of another type or table. The type or table that inherits the properties is called the subtype or subtable. The type or table whose properties are inherited is called the supertype or supertable. Inheritance allows for incremental modification so that a type or table can inherit a general set of properties and add specific properties. You can use inheritance to make modifications only to the extent that the modifications do not alter the inherited supertypes or supertables.

Type inheritance

Type inheritance applies to named row types only. You can use inheritance to a group named row types into a type hierarchy in which each subtype inherits the representation (data fields) and the behaviour of the supertype under which it is defined. A type hierarchy provides the following advantages:

• It encourages modular implementation of your data model.
• It ensures consistent reuse of schema components.
• It ensures that no data fields are accidentally left out.

Table inheritance

Only tables that are defined on named row types support table inheritance. Table inheritance is the property that allows a table to inherit the behaviour (constraints, storage options, triggers) from the supertable above it in the table hierarchy. A table hierarchy is a relationship that you can define among tables in which subtables inherit the behavior of supertables. A table inheritance provides the following advantages:

• It encourages modular implementation of your data model.
• It ensures consistent reuse of schema components.
• It allows you to construct queries whose scope can be some or all of the tables in the table hierarchy.

In a table hierarchy, a subtable automatically inherits the following properties from its supertable:

All constraint definitions (primary key, unique, and referential constraints)

• Storage option
• All triggers
• Indexes
• Access method

Demonstration of Type and Table Inheritance in Oracle

--Creating Type 

CREATE TYPE flName AS OBJECT (

          first_name VARCHAR2(50),

           last_name VARCHAR2(50)

        )NOT FINAL;

CREATE TYPE Address AS OBJECT (

          street VARCHAR2(50),

          city    VARCHAR2(50),

          state  VARCHAR2(50),

          country VARCHAR2(50)         

       ) NOT FINAL;

CREATE TYPE person AS OBJECT (

          p_name flName,

          p_address  Address,

          tel_number NUMBER(15)

         )NOT FINAL;

CREATE TYPE student UNDER person (

    student_id NUMBER(10),

    year NUMBER(1)

);

CREATE TYPE employee UNDER person (

    emp_id NUMBER(10),

    Salary NUMBER(10)  

);

--Creating Table

CREATE TABLE person_t OF person;

CREATE TABLE  student_t OF student;

CREATE TABLE employee_t OF employee;

--Describing the Structure of student_t table

DESC student_t;

--Inserting Values in student_t table

INSERT INTO student_t VALUES(FLNAME('Antosh','Dyade'),Address('Aurad','Bidar','Kar','India'),9999999,101,1);

--Structured Select Query on student_t table

SELECT t.p_name.first_name,t.p_name.last_name,

t.p_address.street,t.p_address.city,t.p_address.state,t.p_address.country,

t.tel_number,t.year FROM student_t t;

--Update Query on student_table 

update student_t t set t.p_address.state='Karnataka' where t.p_name.first_name='Antosh';

--Delete Query on student_table

DELETE from student_t t where t.p_name.first_name='Antosh';

--Dropping all the types and tables created above.
--First, drop the tables
--Then drop the type and make sure that while dropping the TYPE it should not hold/opened by any other TYPE or TABLE. 

DROP TABLE employee_t;

DROP TABLE student_t;

DROP TABLE person_t;

DROP type employee;

DROP TYPE student;

DROP TYPE person;

DROP type Address;

Drop type flName;

Domain Relational Calculus

• Domain Relational Calculus uses domain variables that take on values from an attributes domain, rather than for an entire tuple. ​
• Formal Definition​
• An expression in the domain relational calculus is of the form :​
• {<x1, x2, …..., xn> | P(x1, x2, …..., xn)}​
• Where ​
• x1, x2, …..., xn represent domain variables.​
• P formula composed of atoms. ​
• An atom in the domain relational calculus has one of the following forms:​
• <x1, x2, …..., xn> ϵ r, where r is a relation on n attributes and x1, x2, …..., xn are domain variables or domain constants.​
• X Θ y, where x and y are domain variables and Θ is a comparison operator (<, ≤, >, ≥, = , ≠) Attributes x and y have domains that can be compared by  Θ ​
• x Θ c, where x is a domain variable, Θ  is a comparison operator, and c is a constant in the domain of the attribute for which x and a domain variables. ​

Examples based on following relation.

Example1: Find the loan number, branch name and amount for loans of over 100000

Answer: =>      { < l, b, a > | < l, b, a > ϵ loan ᴧ a > 100000 }​

Example2: Find the name of all customer who have a loan from the Mumbai branch

Answer: =>  { < c, a > ∃ l ((<c, l > ϵ borrower ᴧ ∃ b(<l, b, a >  ϵ loan ᴧ b = "Mumbai") }​

 

Tuple Relational Calculus

• The tuple relational algebra is a nonprocedural query language. It describes the desired information without giving a specific procedure or obtaining that information.​
• A query in the tuple relational calculus is expressed as ​
• { t | P(t) }​
• That is, it is the set of all tuples t such that predicate P is true for t.​
• In tuple relational calculus ​
• t[A] - denotes the value of tuple on attribute A.​
• t ε  r –denotes tuple t in relation r. ​

Examples based on the following table

Example 1: Find all tuples from the depositor relation

Answer: { t | t ϵ depositor }​

Example 2: Find the loan_no, branch_name and amount for loans of over 100000.

Answer: { t | t ϵ loan ᴧ [amount] > 100000 }​

Modification of the Database in Relational Algebra

• Different operations that modify the contents of the database are​
• Delete​
• Insert​
• Update​
• We express database modification by using the assignment operation.​

Deletion

• The deletion operation removes the selected tuples from the database using delete values of any particular attribute.​
• In relational algebra a deletion is expressed by​
•  r ← r - E​
•  r is a relation​
• E is a relational algebra query​

Example​

Delete 'Ramesh' Record from the database​

• employees ← employees - σemp_name='Ramesh'(employees)

• Delete all employee working in IT department​
• employees ← employees - σdepartment='IT'(employees)

Insertion

• The relational algebra expresses an insertion by ​
• r ← r Ս E​
• r is a relation​
• E is relational algebra expression​

Examples​

• Insert Ramesh Record in employee relation​
• employees ← employees Ս {('E004','Ramesh', 12000)}​

Updating

• Sometimes, we wish to change a value in a tuple without changing all values in the tuple. We can use generalized projection operation to do this:​

• r  ← ∏f1,f2,f3...fn(r)

• Where fi the ith attribute to be updated has expression involving constants and attributes of r, that gives new value for the attribute. ​

• Example​
• ∏emp_id, salary*1.05(employees)