Introduction to Relational Model

Question 1

What are various attribute types in the Relational Model?

Answer 1

• char(n). Fixed length character string, with user-specified length n
• varchar(n). Variable length character strings, with user-specified maximum length n
• int. Integer (a finite subset of the integers that is machine-dependent)
• smallint(n). Small integer (a machine-dependent subset of the integer domain type)
• numeric(p, d). Fixed point number, with user-specified precision of p digits, with d
  digits to the right of decimal point. (ex., numeric(3, 1), allows 44.5 to be stores
  exactly, but not 444.5 or 0.32)
• real, double precision. Floating point and double-precision floating point numbers,
  with machine-dependent precision
• float(n). Floating point number, with user-specified precision of at least n digits

Question 2

What is a Super Key?

Question 3

What is a Candidate Key?

Question 4

What is a Primary Key?

Question 5

What is a Surrogate Key?

Question 6

What is a Composite Key?

Question 7

What is a Secondary Key?

Question 8

What is a Foreign Key?

Question 9

What is a Compound Key?

Question 10

What is Procedural Programming? vs Declarative Programming?

Question 11

What is the domain of an attribute?

Question 12

What are atomic attributes?

Question 13

What is “Select” Relational Operation ?

Question 14

What is “Project” Relational Operation ?

Question 15

What is “Union” Relational Operation ?

Question 16

What is “Difference” Relational Operation ?

Question 17

What is “Intersection” Relational Operation ?

Question 18

What is “Cartesian Product” Relational Operation ?

Question 19

What is “Natural Join” Relational Operation ?

Question 20

What are aggregate operations?

Answer 20

◦ SUM
◦ AVG
◦ MAX
◦ MIN

Question 21

What are the basic properties of relations?

Answer 21

Relation is a set, so, no ordering, no duplicates.

Question 22

What is a composition of relational operations?

Question 23

How to create a table in SQL?

Answer 23

create table instructor (
ID char(5),
name varchar(20)
dept name varchar(20)
salary numeric(8, 2));

Question 24

How to order the display of tuples in SQL?

Question 25

How to select Number of Tuples in Output?

Question 26

How to deal with Duplicates in SQL?

Answer 26

Select distinct - displays unique values
Select all - displays all values including duplicates

Question 27

How to implement Union Set Operation in SQL?

Question 28

How to implement Intersect Set Operation in SQL?

Question 29

How to implement Difference Set Operation in SQL?

Question 30

How to deal with “NULL” values?

Question 31

What are some of the frequently used aggregate functions in SQL?

Question 32

How to group using an aggregate attribute in SQL?

Question 33

How to set conditions of an aggregating attribute in SQL?

Question 34

What are Instances in databases?

Answer 34

The actual data present in the database at that point of time.

Question 35

What are Turing Complete Languages? Is SQL turing complete?

Answer 35

A programming language is considered Turing complete if it can express any computation that a Turing machine can perform. This means that the language provides the necessary constructs and features to implement any algorithm or computation, even if it might not be practical to do so.

SQL is NOT Turning Complete Language

Question 36

What are Integrity Constraints of an attribute?

Answer 36

Primary Key, Unique, Not Null, Auto Increment, Foreign Key etc are some of the integrity contraints of an attribute

Question 37

how to check set membership in SQL?

Answer 37

Using “in” query

select * from student in (‘CSE’);

Question 38

What are the string operations possible in SQL

Answer 38

1) Like - with % wildcard for multiple characters and _ wildcard for single character

Question 39

How to sort the output of a query in SQL?

Answer 39

using “order by “ clause.. we could set it to “ASC” or “DESC” or one option per atribute

Question 40

How to create temporary /permanent relations in a database that hides certain information from users

Answer 40

Create view v as <query>
one view could be dependent on the other.That is, composition is possible.</query>

Question 41

What are transactions?

Question 42

How to evaluate a predicate in SQL

Answer 42

Using “Check” clause..

check(semester in (‘Fall’,’Winter’,’Spring’,’Summer’)

Question 43

What is referential integrity? how is it ensured in SQL

Answer 43

Foreign Key..

on delete_cascade;
on update cascade;

Alternative actions to cascade: no action, set null, set default.

Question 44

How to create an index in SQL?

Answer 44

Create idx_student_id on Student(Student_id)

Question 45

How to create user defined data types in SQL

Answer 45

create type Dollars as numeric(12,2) final

Question 46

How to create domains in SQL

Answer 46

create domain person_name char(20) not null.

Allows us to specify integrity constraints unlike user defined data types.

Question 47

What are some of large object Types

Answer 47

1. BLOB
2. CLOB

Question 48

What are forms of authorization on parts of the database?

Answer 48

1. Read
2. Write
3. Update
4. Delete

Question 49

What are forms of auhorization to modify the database schema

Answer 49

1. Index
2. Resource
3. Alteration
4. Drop

Question 50

How to grant authorization in SQL

Answer 50

grant <privilege>
on <relation>
to <user></user></relation></privilege>

where
previleges could be select, insert, update,delete, all privileges.

user list could be user id, role or public.

Question 51

How to cancel authorization in SQL?

Answer 51

revoke <privilege>
on <relation>
from <user></user></relation></privilege>

Question 52

How to create roles in SQL

Answer 52

create role <role_name>
grant <role_name> to Madhan</role_name></role_name>

we could chain the roles
create role <role2>
grant role_name to role2</role2>

Previleges could also be transferred.
grant select on dept to Madhan with grant option

revoke select on dept from Amit cascade

Question 53

How to grant privelege for created foreign keys?

Answer 53

grant reference (dept_name) on department to Madhan

Question 54

How are functions defined in SQL?

Answer 54

create function dept_count(dept_name varchar(20)) returns integer
begin
declare d_count integger;
select count(*) into d_count
from instructor
whare instructor.dept_name
=dept_name
return d_count;
end

Functions could return a table itself.

Question 55

How to loop in SQL

Answer 55

while loop:
while <boolean> do
sequence of statements
end while;</boolean>

repeat loop:
repeat
sequence of statements
until <boolean>
end repeat</boolean>

for loop:
for r as select budget from department
do
set n=n+r.budge
end for;

Question 56

Branching Statements in SQL

Answer 56

if-then-else :

if <bool> then
sequence of statements;
elseif <bool> then
sequence of statements;
else
sequence of statements
end if;</bool></bool>

case:

case
when sql-expr =value1 then
sequence of statemetns;
when sql-expr=value2 then
sequence of statemetns;
else
sequence of statemetns;
end case;

Question 57

exception handling in SQL

Answer 57

declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
……
signal out_of_classroom_seats
…….
end

Question 58

What are external language routines

Answer 58

SQL allows the definition of functions/ procedures in an imperative programming language like Java, C, C# etc which can be invoked from SQL queries.
create procedure dept _coutn_proc(in dept_name varchar(20),out count integer)
Language C
external name ‘/usr/avi/bin/dept_count_proc/

create function dept_count(dept_name varchar(20)) returns integer
language C
external name ‘/usr/avi/bin/dept_count’