Introduction to Relational Model Flashcards
What are various attribute types in the Relational Model?
- 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
What is a Super Key?
What is a Candidate Key?
What is a Primary Key?
What is a Surrogate Key?
What is a Composite Key?
What is a Secondary Key?
What is a Foreign Key?
What is a Compound Key?
What is Procedural Programming? vs Declarative Programming?
What is the domain of an attribute?
What are atomic attributes?
What is “Select” Relational Operation ?
What is “Project” Relational Operation ?
What is “Union” Relational Operation ?
What is “Difference” Relational Operation ?
What is “Intersection” Relational Operation ?
What is “Cartesian Product” Relational Operation ?
What is “Natural Join” Relational Operation ?
What are aggregate operations?
◦ SUM
◦ AVG
◦ MAX
◦ MIN
What are the basic properties of relations?
Relation is a set, so, no ordering, no duplicates.
What is a composition of relational operations?
How to create a table in SQL?
create table instructor (
ID char(5),
name varchar(20)
dept name varchar(20)
salary numeric(8, 2));
How to order the display of tuples in SQL?
How to select Number of Tuples in Output?
How to deal with Duplicates in SQL?
Select distinct - displays unique values
Select all - displays all values including duplicates
How to implement Union Set Operation in SQL?
How to implement Intersect Set Operation in SQL?
How to implement Difference Set Operation in SQL?
How to deal with “NULL” values?
What are some of the frequently used aggregate functions in SQL?
How to group using an aggregate attribute in SQL?
How to set conditions of an aggregating attribute in SQL?
What are Instances in databases?
The actual data present in the database at that point of time.
What are Turing Complete Languages? Is SQL turing complete?
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
What are Integrity Constraints of an attribute?
Primary Key, Unique, Not Null, Auto Increment, Foreign Key etc are some of the integrity contraints of an attribute
how to check set membership in SQL?
Using “in” query
select * from student in (‘CSE’);
What are the string operations possible in SQL
1) Like - with % wildcard for multiple characters and _ wildcard for single character
How to sort the output of a query in SQL?
using “order by “ clause.. we could set it to “ASC” or “DESC” or one option per atribute
How to create temporary /permanent relations in a database that hides certain information from users
Create view v as <query>
one view could be dependent on the other.That is, composition is possible.</query>
What are transactions?
How to evaluate a predicate in SQL
Using “Check” clause..
check(semester in (‘Fall’,’Winter’,’Spring’,’Summer’)
What is referential integrity? how is it ensured in SQL
Foreign Key..
on delete_cascade;
on update cascade;
Alternative actions to cascade: no action, set null, set default.
How to create an index in SQL?
Create idx_student_id on Student(Student_id)
How to create user defined data types in SQL
create type Dollars as numeric(12,2) final
How to create domains in SQL
create domain person_name char(20) not null.
Allows us to specify integrity constraints unlike user defined data types.
What are some of large object Types
- BLOB
- CLOB
What are forms of authorization on parts of the database?
- Read
- Write
- Update
- Delete
What are forms of auhorization to modify the database schema
- Index
- Resource
- Alteration
- Drop
How to grant authorization in SQL
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.
How to cancel authorization in SQL?
revoke <privilege>
on <relation>
from <user></user></relation></privilege>
How to create roles in SQL
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
How to grant privelege for created foreign keys?
grant reference (dept_name) on department to Madhan
How are functions defined in SQL?
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.
How to loop in SQL
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;
Branching Statements in SQL
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;
exception handling in SQL
declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
……
signal out_of_classroom_seats
…….
end
What are external language routines
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’
