UNIT-II-
The SQL language has several parts:
_ data - definitition language
_ Data manipulation language
_ View definition
_ Transaction control
_ Embedded SQL
_ Integrity
_ Authorization
2. What are the categories of SQL command?
SQL commands are divided in to the following categories:
1. data - definitition language
2. data manipulation language
3. Data Query language
4. data control language
5. data administration statements
6. transaction control statements
3. What are the three classes of SQL expression?
SQL expression consists of three clauses:
_ Select
_ From
_ where
4. Give the general form of SQL query?
Select A1, A2…………., An
From R1, R2……………, Rm
Where P
5. What is the use of rename operation?
Rename operation is used to rename both relations and a attributes.
It uses the as clause, taking the form:
Old-name as new-name
6. Define tuple variable?
Tuple variables are used for comparing two tuples in the same relation. The tuple
variables are defined in the from clause by way of the as clause.
7. List the string operations supported by SQL?
1) Pattern matching Operation
2) Concatenation
3) Extracting character strings
4) Converting between uppercase and lower case letters.
8. List the set operations of SQL?
1) Union
2) Intersect operation
3) The except operation
9. What is the use of Union and intersection operation?
Union: The result of this operation includes all tuples that are either in r1 or in r2
or in both r1 and r2.Duplicate tuples are automatically eliminated.
Intersection: The result of this relation includes all tuples that are in both r1 and
r2.
10. What are aggregate functions? And list the aggregate functions supported by
SQL?
Aggregate functions are functions that take a collection of values as input and
return a single value.
Aggregate functions supported by SQL are
_ Average: avg
_ Minimum: min
_ Maximum: max
_ Total: sum
_ Count: count
11. What is the use of group by clause?
Group by clause is used to apply aggregate functions to a set of tuples.The
attributes given in the group by clause are used to form groups.Tuples with the
same value on all attributes in the group by clause are placed in one group.
12. What is the use of sub queries?
A sub query is a select-from-where expression that is nested with in another
query. A common use of sub queries is to perform tests for set membership, make
setcomparisions, and determine set cardinality.
13. What is view in SQL? How is it defined?
Any relation that is not part of the logical model, but is made visible to a user as a
virtual relation is called a view.
We define view in SQL by using the create view command. The form of the
create view command is
Create view v as <query expression>
14. What is the use of with clause in SQL?
The with clause provides a way of defining a temporary view whose definition is
available only to the query in which the with clause occurs.
15. List the table modification commands in SQL?
_ Deletion
_ Insertion
_ Updates
_ Update of a view
16. List out the statements associated with a database transaction?
_ Commit work
_ Rollback work
17. What is transaction?
Transaction is a unit of program execution that accesses and possibly updated
various data items.
18. List the SQL domain Types?
SQL supports the following domain types.
1) Char(n) 2) varchar(n) 3) int 4) numeric(p,d)
5) float(n) 6) date.
19. What is the use of integrity constraints?
Integrity constraints ensure that changes made to the database by authorized users
do not result in a loss of data consistency. Thus integrity constraints guard against
accidental damage to the database.
20. Mention the 2 forms of integrity constraints in ER model?
_ Key declarations
_ Form of a relationship
21. What is trigger?
Triggers are statements that are executed automatically by the system as the side
effect of a modification to the database.
22. What are domain constraints?
A domain is a set of values that may be assigned to an attribute .all values that
appear in a column of a relation must be taken from the same domain.
23. What are referential integrity constraints?
A value that appears in one relation for a given set of attributes also appears for a
certain set of attributes in another relation.
24. What is assertion? Mention the forms available.
An assertion is a predicate expressing a condition that we wish the database
always to satisfy.
_ Domain integrity constraints.
_ Referential integrity constraints
25. Give the syntax of assertion?
Create assertion <assertion name>check<predicate>
26. What is the need for triggers?
Triggers are useful mechanisms for alerting humans or for starting certain tasks
automatically when certain conditions are met.
27. List the requirements needed to design a trigger.
The requirements are
_ Specifying when a trigger is to be executed.
_ Specify the actions to be taken when the trigger executes.
28. Give the forms of triggers?
_ The triggering event can be insert or delete.
_ For updated the trigger can specify columns.
_ The referencing old row as clause
_ The referencing new row as clause
_ The triggers can be initiated before the event or after the event.
29. What does database security refer to?
Database security refers to the protection from unauthorized access and malicious
destruction or alteration.
30. List some security violations (or) name any forms of malicious access.
_ Unauthorized reading of data
_ Unauthorized modification of data
_ Unauthorized destruction of data.
31. List the types of authorization.
_ Read authorization
_ Write authorization
_ Update authorization
_ Drop authorization
32. What is authorization graph?
Passing of authorization from one user to another can be represented by an
authorization graph.
33. List out various user authorization to modify the database schema.
_ Index authorization
_ Resource authorization
_ Alteration authorization
_ Drop authorization
34. What are audit trails?
An audit trail is a log of all changes to the database along with information such
as which user performed the change and when the change was performed.
35. Mention the various levels in security measures.
_ Database system
_ Operating system
_ Network
_ Physical
_ human
36. Name the various privileges in SQL?
_ Delete
_ Select
_ Insert
_ update
37. Mention the various user privileges.
_ All privileges directly granted to the user or role.
_ All privileges granted to roles that have been granted to the user or role.
38. Give the limitations of SQL authorization.
_ The code for checking authorization becomes intermixed with the rest of the
application code.
_ Implementing authorization through application code rather than specifying it
declaratively in SQL makes it hard to ensure the absence of loopholes.
39. Give some encryption techniques?
_ DES
_ AES
_ Public key encryption
40. What does authentication refer?
Authentication refers to the task of verifying the identity of a person.
41. List some authentication techniques.
_ Challenge response scheme
_ Digital signatures
_ Nonrepudiation
42. Define Boyce codd normal form
A relation schema R is in BCNF with respect to a set F of functional
dependencies if, for all functional dependencies in F
+
of the form. _->_, where _
43. List the disadvantages of relational database system
_ Repetition of data
_ Inability to represent certain information.
44. What is first normal form?
The domain of attribute must include only atomic (simple, indivisible) values.
45. What is meant by functional dependencies?
Consider a relation schema R and _ C R and _ C R. The functional dependency _
_ _ holds on relational schema R if in any legal relation r(R), for all pairs of
tuples t1 and t2 in r such that t1 [_] =t1 [_], and also t1 [_] =t2 [_].
46. What are the uses of functional dependencies?
_ To test relations to see whether they are legal under a given set of functional
dependencies.
_ To specify constraints on the set of legal relations.
47. Explain trivial dependency?
Functional dependency of the form _ _ _ is trivial if _ C _. Trivial functional
dependencies are satisfied by all the relations.
48. What are axioms?
Axioms or rules of inference provide a simpler technique for reasoning about
functional dependencies.
49. What is meant by computing the closure of a set of functional dependency?
The closure of F denoted by F+ is the set of functional dependencies logically
implied by F.
50. What is meant by normalization of data?
It is a process of analyzing the given relation schemas based on their Functional
Dependencies (FDs) and primary key to achieve the properties
_ Minimizing redundancy
_ Minimizing insertion, deletion and updating anomalies.
51. Define canonical cover?
A canonical cover Fc for F is a set of dependencies such that F logically implies
all dependencies in FC and Fc logically implies all dependencies in F. Fc must
have the following properties.
52. List the properties of canonical cover.
Fc must have the following properties.
_ No functional dependency in Fc contains an extraneous attribute.
_ Each left side of a functional dependency in Fc is unique.
53. Explain the desirable properties of decomposition.
_ Lossless-join decomposition
_ Dependency preservation
_ Repetition of information
54. What is 2NF?
2.) 16 Marks
• Set membership
• Set comparison
• Test for empty relations
2. Explain embedded SQL and dynamic SQL in detail
• Explanation
3. Explain the integrity constraints supported by SQL
• Domain constraints
• Referential integrity
4. Explain triggers with example.
• Need for triggers
• Explanations
• When not to use triggers
5. Explain security and authorization and Security in SQL
• Security violations
• Authorization
• Privileges
• Roles
6. Explain the different normal forms in detail
• 1 normal form
• 2 nd normal form
• 3 rd normal form
• 4 th normal form
• BCNF
Unit
II
1.) 2 Marks
1. What are the parts of SQL language?1.) 2 Marks
The SQL language has several parts:
_ data - definitition language
_ Data manipulation language
_ View definition
_ Transaction control
_ Embedded SQL
_ Integrity
_ Authorization
2. What are the categories of SQL command?
SQL commands are divided in to the following categories:
1. data - definitition language
2. data manipulation language
3. Data Query language
4. data control language
5. data administration statements
6. transaction control statements
3. What are the three classes of SQL expression?
SQL expression consists of three clauses:
_ Select
_ From
_ where
4. Give the general form of SQL query?
Select A1, A2…………., An
From R1, R2……………, Rm
Where P
5. What is the use of rename operation?
Rename operation is used to rename both relations and a attributes.
It uses the as clause, taking the form:
Old-name as new-name
6. Define tuple variable?
Tuple variables are used for comparing two tuples in the same relation. The tuple
variables are defined in the from clause by way of the as clause.
7. List the string operations supported by SQL?
1) Pattern matching Operation
2) Concatenation
3) Extracting character strings
4) Converting between uppercase and lower case letters.
8. List the set operations of SQL?
1) Union
2) Intersect operation
3) The except operation
9. What is the use of Union and intersection operation?
Union: The result of this operation includes all tuples that are either in r1 or in r2
or in both r1 and r2.Duplicate tuples are automatically eliminated.
Intersection: The result of this relation includes all tuples that are in both r1 and
r2.
10. What are aggregate functions? And list the aggregate functions supported by
SQL?
Aggregate functions are functions that take a collection of values as input and
return a single value.
Aggregate functions supported by SQL are
_ Average: avg
_ Minimum: min
_ Maximum: max
_ Total: sum
_ Count: count
11. What is the use of group by clause?
Group by clause is used to apply aggregate functions to a set of tuples.The
attributes given in the group by clause are used to form groups.Tuples with the
same value on all attributes in the group by clause are placed in one group.
12. What is the use of sub queries?
A sub query is a select-from-where expression that is nested with in another
query. A common use of sub queries is to perform tests for set membership, make
setcomparisions, and determine set cardinality.
13. What is view in SQL? How is it defined?
Any relation that is not part of the logical model, but is made visible to a user as a
virtual relation is called a view.
We define view in SQL by using the create view command. The form of the
create view command is
Create view v as <query expression>
14. What is the use of with clause in SQL?
The with clause provides a way of defining a temporary view whose definition is
available only to the query in which the with clause occurs.
15. List the table modification commands in SQL?
_ Deletion
_ Insertion
_ Updates
_ Update of a view
16. List out the statements associated with a database transaction?
_ Commit work
_ Rollback work
17. What is transaction?
Transaction is a unit of program execution that accesses and possibly updated
various data items.
18. List the SQL domain Types?
SQL supports the following domain types.
1) Char(n) 2) varchar(n) 3) int 4) numeric(p,d)
5) float(n) 6) date.
19. What is the use of integrity constraints?
Integrity constraints ensure that changes made to the database by authorized users
do not result in a loss of data consistency. Thus integrity constraints guard against
accidental damage to the database.
20. Mention the 2 forms of integrity constraints in ER model?
_ Key declarations
_ Form of a relationship
21. What is trigger?
Triggers are statements that are executed automatically by the system as the side
effect of a modification to the database.
22. What are domain constraints?
A domain is a set of values that may be assigned to an attribute .all values that
appear in a column of a relation must be taken from the same domain.
23. What are referential integrity constraints?
A value that appears in one relation for a given set of attributes also appears for a
certain set of attributes in another relation.
24. What is assertion? Mention the forms available.
An assertion is a predicate expressing a condition that we wish the database
always to satisfy.
_ Domain integrity constraints.
_ Referential integrity constraints
25. Give the syntax of assertion?
Create assertion <assertion name>check<predicate>
26. What is the need for triggers?
Triggers are useful mechanisms for alerting humans or for starting certain tasks
automatically when certain conditions are met.
27. List the requirements needed to design a trigger.
The requirements are
_ Specifying when a trigger is to be executed.
_ Specify the actions to be taken when the trigger executes.
28. Give the forms of triggers?
_ The triggering event can be insert or delete.
_ For updated the trigger can specify columns.
_ The referencing old row as clause
_ The referencing new row as clause
_ The triggers can be initiated before the event or after the event.
29. What does database security refer to?
Database security refers to the protection from unauthorized access and malicious
destruction or alteration.
30. List some security violations (or) name any forms of malicious access.
_ Unauthorized reading of data
_ Unauthorized modification of data
_ Unauthorized destruction of data.
31. List the types of authorization.
_ Read authorization
_ Write authorization
_ Update authorization
_ Drop authorization
32. What is authorization graph?
Passing of authorization from one user to another can be represented by an
authorization graph.
33. List out various user authorization to modify the database schema.
_ Index authorization
_ Resource authorization
_ Alteration authorization
_ Drop authorization
34. What are audit trails?
An audit trail is a log of all changes to the database along with information such
as which user performed the change and when the change was performed.
35. Mention the various levels in security measures.
_ Database system
_ Operating system
_ Network
_ Physical
_ human
36. Name the various privileges in SQL?
_ Delete
_ Select
_ Insert
_ update
37. Mention the various user privileges.
_ All privileges directly granted to the user or role.
_ All privileges granted to roles that have been granted to the user or role.
38. Give the limitations of SQL authorization.
_ The code for checking authorization becomes intermixed with the rest of the
application code.
_ Implementing authorization through application code rather than specifying it
declaratively in SQL makes it hard to ensure the absence of loopholes.
39. Give some encryption techniques?
_ DES
_ AES
_ Public key encryption
40. What does authentication refer?
Authentication refers to the task of verifying the identity of a person.
41. List some authentication techniques.
_ Challenge response scheme
_ Digital signatures
_ Nonrepudiation
42. Define Boyce codd normal form
A relation schema R is in BCNF with respect to a set F of functional
dependencies if, for all functional dependencies in F
+
of the form. _->_, where _
43. List the disadvantages of relational database system
_ Repetition of data
_ Inability to represent certain information.
44. What is first normal form?
The domain of attribute must include only atomic (simple, indivisible) values.
45. What is meant by functional dependencies?
Consider a relation schema R and _ C R and _ C R. The functional dependency _
_ _ holds on relational schema R if in any legal relation r(R), for all pairs of
tuples t1 and t2 in r such that t1 [_] =t1 [_], and also t1 [_] =t2 [_].
46. What are the uses of functional dependencies?
_ To test relations to see whether they are legal under a given set of functional
dependencies.
_ To specify constraints on the set of legal relations.
47. Explain trivial dependency?
Functional dependency of the form _ _ _ is trivial if _ C _. Trivial functional
dependencies are satisfied by all the relations.
48. What are axioms?
Axioms or rules of inference provide a simpler technique for reasoning about
functional dependencies.
49. What is meant by computing the closure of a set of functional dependency?
The closure of F denoted by F+ is the set of functional dependencies logically
implied by F.
50. What is meant by normalization of data?
It is a process of analyzing the given relation schemas based on their Functional
Dependencies (FDs) and primary key to achieve the properties
_ Minimizing redundancy
_ Minimizing insertion, deletion and updating anomalies.
51. Define canonical cover?
A canonical cover Fc for F is a set of dependencies such that F logically implies
all dependencies in FC and Fc logically implies all dependencies in F. Fc must
have the following properties.
52. List the properties of canonical cover.
Fc must have the following properties.
_ No functional dependency in Fc contains an extraneous attribute.
_ Each left side of a functional dependency in Fc is unique.
53. Explain the desirable properties of decomposition.
_ Lossless-join decomposition
_ Dependency preservation
_ Repetition of information
54. What is 2NF?
2.) 16 Marks
• Set membership
• Set comparison
• Test for empty relations
2. Explain embedded SQL and dynamic SQL in detail
• Explanation
3. Explain the integrity constraints supported by SQL
• Domain constraints
• Referential integrity
4. Explain triggers with example.
• Need for triggers
• Explanations
• When not to use triggers
5. Explain security and authorization and Security in SQL
• Security violations
• Authorization
• Privileges
• Roles
6. Explain the different normal forms in detail
• 1 normal form
• 2 nd normal form
• 3 rd normal form
• 4 th normal form
• BCNF