UGC NET

This post consists of 43 questions from Relational Database Design and SQL from previous years UGC NET papers. this will help you to understand the pattern of questions comes under this section. Generally 5 out 50 questions comes from Relational Database Design and SQL. New syllabus issued by the NTA is given in this post. This post also highlight the questions from each years. Try to solve the questions.

Best of luck for your NTA UGC NET  preparation.

Relational Database Design and SQL

E-R Diagrams And Their Transformation To Relational Design

When an ER Model is conceptualized into diagrammatical form it is capable of providing a good overview of an entity-relationship which is easier to understand. ER diagrams can be mapped to relational schema, that is, it is possible to create relational schema using ER diagram.

There are many methods and algorithms available to us which can help one to convert an ER Diagram into am Relational Schema. Some of the process and algorithm are automated while the others are manual.

 

NORMALIZATION

The technique of organizing any required data in database to avoid duplication, insertion anomaly, update anomaly & deletion anomaly is termed as NORMALIZATION thus we can say that Database Normalization is a technique of organizing the data in the database.

Normalization is said to be a more systematic approach for decomposing tables to eliminate redundancy of data and also helps one to avoid undesirable situations like Insertion, Update and Deletion Anomalies.

Normalization thus can also be termed as a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

Normalization is used for mainly two purposes:-

Eliminating redundant (useless) data.

Ensuring data dependencies make sense i.e data is logically stored

Normalization Rule

Normalization rule are divided into following normal form.

• First Normal Form INF
• Second Normal Form 2NF
• Third Normal Form 3NF
• BCNF
• 4NF

INF-

INF rule also known as the First Normal Form states that no two Rows belonging to same data must contain same group of information which means that each set of column must have a unique value so that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that makes it unique.

 2NF

2nf also known as the Second Normal Form states that there should not exist partial dependency of any column on primary key in a data. In simpler words we can say that if in a table there is concatenated primary key then every column of the table that is not a part of primary key should depend on the entire concatenated key for its existence. where as if any column that exists but depends on only one part of concatenated key then the table fails the 2NF normal form.

 3NF

3nf known as the Third Normal form states that every non-prime attribute of table must be dependent on a primary key, or it can also be stated as, that there should be no such case in which  a non-prime attribute is determined by another non-prime attribute. 3nf aims at removal of transitive functional dependency from the table and also the table must be in Second Normal form.

BCNF

The BCNF short for the “Boyce and Cod Normal Form” is said to be an extended version of the Third Normal form. BCNF is designed to deal with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be present in BCNF because of which use of bcnf is prioritized over 3nf. For a table to be in BCNF, following conditions must be satisfied:

R must be in 3rd Normal Form

and, for each functional dependency ( X -> Y ), X should be a super Key.

 

 4NF

Fourth normal form known as 4NF is a normal form used in database normalization. The 4nf was introduced by Ronald Fagin in 1977. 4NF is the next level of normalization after Boyce–Cod normal form (BCNF). Unlike  the second, third, and Boyce–Cod normal forms that are concerned with functional dependencies, the 4NF is concerned with a more general type of dependency known as a multivalued dependency. A Table is in 4NF if and only if it fulfills the criterion that:-

For every one of its non-trivial multivalued dependencies X \two head right arrow Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

 

 

 

LIMITATIONS OF 4NF AND BCNF.

• Database must be already achieved to 3NF to take it to BCNF, but database must be in 3NF and BCNF, to reach 4NF.
• In fourth normal form, there are no multi-valued dependencies of the tables, but in BCNF, there can be multi-valued dependency data in the tables.

 

SQL

 

Data Definition Language ( DDL )

 

Data Definition Language (DDL) is a standard for commands that define the different structures in a database. There are different types of  DDL statements that are used to create, modify, and remove database objects such as tables, indexes, and users.

Common DDL statements used are

 

• CREATE

The create statement is used to create either a new database or a table.

• Syntax For Creating A New Database:-

CREATE DATABASE database_name;

Since we know that a table in a database is a combination of rows and columns, the CREATE command is used to create table by naming the required columns along with their description ie the datatype & size of the column

• Syntax For Creating A New Table:-

CREATE TABLE table_name

(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
….
);

 ALTER

Alter command is used to do modifications on the existing columns of a table the alter command is used to perform tasks such as addition, deletion or modification on the columns of the existing tables.

• Syntax to add a column in a table using alter statement:-

ALTER TABLE table_name
ADD column_name datatype (size);

• Syntax to delete a column in a table using ALTER statement

ALTER TABLE table_name
DROP column_name datatype;

• Syntax to change the data type of a column in a table

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

 DROP

In orderto delete/remove or truncate an index, table or entire database in mysql one can make use of the DROP statement.

• Syntax to delete and index in a table:-

DROP INDEX table_name.index_name;

• Syntax to delete a table:-

DROP TABLE table_name;

• Syntax to delete a database:-

DROP DATABASE database_name;

Drop command is used to just delete the table from the database but if one wants to delete not only the table but also the data within the table then use of TRUNCATE command would be considered appropriate.

• Syntax for TRUNCATE:-

TRUNCATE TABLE table_name;

 

DATA MANIPULATION LANGUAGE ( DML )

 

A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for performing tasks such as selecting, inserting, deleting and updating data in a database. It basically is aimed at Performing read-only queries on the data.

 

Various commands used under DML category are

 

SELECT

Select command is used to retrieve the desired data from the database.

SELECT column_name(s) FROM table_name;

INSERT:-

Insert command is used to insert data into the database.

INSERT INTO table_name (column, column1, column2, column3, …)

VALUES (value, value1, value2, value3 …);

UPDATE:-

Update command is used to update the existing data within a table

UPDATE table_name

SET column=value, column1=value1,…

WHERE someColumn=someValue;

DELETE:-

if one desires to delte all the records form the database then use of DELTE command will be appropriate.

DELETE FROM tableName

WHERE someColumn = someValue;

 

 

 

Data Control Language ( DCL ) commands

 

Data control language commands known as the DCL commands in sql are used for authorization of the database. These commands basically control the access to the stored data within a database. GRANT and REVOKE are the two commands that are used for access control twords the database.

 

GRANT– this command allows the user access privileges to database.

GRANT privilege_name 
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

REVOKE– this command is used to withdraw the users access priviliges that were once given by the use of GRANT.

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

 

DATABASE OBJECTS

• Views

Derived Tables or “Virtual Tables” are also known as Views. They provide an alternative way to look at the data of one or more tables. This virtual table or view derives its values from the evaluation of a query expression in a CREATE VIEW statement. The query expression can reference base tables, other views, aliases, etc. Essentially, a view is a stored SELECT statement, of which you can retrieve the results at a later time by querying the view as though it were a table.  . A view can be read-only or updatable. Currently, Point Base supports Read-Only Views.

 

The definition of each view is stored in PointBase’s system catalog SYSVIEWS. If no errors are encountered, PointBase adds the view name to the SYSVIEWS catalog table. Additionally, all referenced columns of all referenced tables will be added to the SYSVIEWTABLES catalog table

• Indexes

Indexes. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book

 

• Synonyms

Synonyms are a form of database shorthand.

 

Synonyms allow the specification of a short reference name for a long, complex object name, e.g. sys.dba_tablespaces may be shortened to tabspaces by creating a synonym named tabspaces.

 

Synonyms allow access to other databases on other nodes of a distributed database network that is transparent to the system user.

 

Normally only private synonyms are created by system users – public synonyms are created by database administrators.

 

• Sequences

 

Sequences are special database objects used to generate numbers in sequential order, typically to be stored as values in data rows for a data table.

 

Primary use:  To generate unique key values for tables that can be used to link to other tables or that will serve as primary keys (sequence generated primary keys are termed surrogate keys).

 

Example:  Use a Order_Number_Sequence for a TestOrders table where the value of the Order_Number_Sequence must be unique and in numerical sequence.

 

• Data Dictionary

In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition.  The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views.

 

The data dictionary is used by SQL server to execute queries and is automatically updated whenever objects are added, removed, or changed within the database.

RDBMS and SQL —Questions from old papers

Dec 2015

Q1.  Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters). The SQL statement that finds all cities that have “GAR” somewhere in its name, is:
(1)    Select* from customers where city = ‘%GAR%’;
(2)    Select* from customers where city = ‘$GAR$’;
(3)    Select* from customers where city like’%GAR%’;
(4)    Select* from customers where city as ToGAR’;

 

Q2. Match the following database terms to their functions :
List-I                                               List-II
(a)    Normalization               (i) Enforces match of primary key to foreign key
(b)    Data Dictionary            (ii) Reduces data redundancy in a database
(c)    Referential Integrity  (iii) Defines view(s) of the database for particular user(s)
(d)    External Schema        (iv) Contains metadata describing database structure Codes :
(a)     (b)     (c)     (d)
(1)    (iv)    (iii)    (i)      (ii)
(2)    (ii)     (iv)    (i)      (iii)
(3)    (ii)     (iv)    (iii)    (i)
(4)    (iv)    (iii)    (ii)     (i)

Q3. Data which improves the performance and accessibility of the database are called :
(1)     Indexes                            (2)     User Data
(3)     Application Metadata    (4)     Data Dictionary

Q4.  A relation R = {A, B, C, D, E, F,G} is given with following set of functional dependencies :
F = {AD→E,BE→F,B→C,AF→G}
Which of the following is a candidate key ?
(1)     A    (2)     AB    (3)     ABC    (4)     ABD

Q5. Which of the following statements is FALSE about weak entity set ?

(1) Weak entities can be deleted automatically when their strong entity is deleted.
(2) Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity.
(3) A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included.
(4) Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set

 

 

June 2015

Q6. Which of the following concurrency protocol ensures both conflict serializability and freedom from deadlock ?
(a) z – phase Locking
(b) Time stamp – ordering
(1) Both (a) and (b)
(2) (a) only
(3) (b)only
(4) Neither (a) nor (b)

Q7. Drop Table cannot be used to drop a Table referenced by constraint.
(a) Primary key
(b) Sub key
(c) Super key
(d) Foreign key
(1) (a)
(2) (a), (b) and (c)
(3) (d)
(4) (a) and (d)

Q8. Database applications were built directly on top of file system to overcome the following drawbacks of using file-systems:
(a) Data redundancy and inconsistency
(b) Difficulty in accessing Data
(c) Data isolation
(d) Integrity problems
(1) (a)
(2) (a) and (d)
(3) (a), (b) and (c)
(4) (a), (b), (c) and (d)

Q9. For a weak entity set to be meaningful, it must be associated combination with some of their attribute values, is called as :
(1) Neighbour Set (2) Strong Entity Set
(3) Owner Entity Set (4) Weak Set

Dec 2014

Q10.Division operation is ideally suited to handle queries of the type :
(A) customers who have no account in any of the branches in Delhi.
(B) customers who have an account at all branches in Delhi.
(C) customers who have an account in atleast one branch in Delhi.
(D) customers who have only joint account in any one branch in Delhi

Q11. Which of the following is true ?
I. Implementation of self-join is possible in SQL with table alias.
II. Outer-join operation is basic operation in relational algebra.
III. Natural join and outer join operations are equivalent.
(A) I and II are correct.       (B) II and III are correct.
(C) Only III is correct.         (D) Only I is correct.

Q12  What kind of mechanism is to be taken into account for converting a weak entity set into strong entity set in entity-relationship diagram ?
(A) Generalization         (B) Aggregation
(C) Specialization           (D) Adding suitable attributes

Q13. The best normal form of relation scheme R(A, B, C, D) along with the set of functional dependencies
F = {AB → C, AB → D, C → A, D → B} is
(A) Boyce-Codd Normal form     (B) Third Normal form
(C) Second Normal form              (D) First Normal form

Q14. Identify the minimal key for relational scheme R(A, B, C, D, E) with functional dependencies
F = {A → B, B → C, AC → D}
(A) A           (B) AE
(C) BE         (D) CE

June 2014

Q15.Which of the following statements is false ?
(A) Any relation with two attributes is in BCNF.
(B) A relation in which every key has only one attribute is in 2NF.
(C) A prime attribute can be transitively dependent on a key in 3NF relation.
(D) A prime attribute can be transitively dependent on a key in BCNF relation.

Q16.A clustering index is created when
(A) primary key is declared and ordered
(B) no key ordered
(C) foreign key ordered
(D) there is no key and no order

Q17. Let R ={ A, B, C, D. E, F} be a relation schema with the following dependencies C→ F, E → A, EC → D, A → B
Which of the following is a key for R ?
(A) CD               (B) EC            (C) AE                (D) AC

Q18. Match the following :
List-I                                   List- II
a. DDL                                  i. LOCK TABLE
b. DML                                ii. COMMIT
c. TCL                                 iii. Natural Difference
d. BINARY Operation      iv. REVOKE
Codes:
a b C d
(A) ii i iii iv
(B) i ii iv iii
(C) iii ii i iv
(D) iv i ii iii

Dec 2013

Q19. An ER Model includes
I. An ER diagram portraying entity types.
II. Attributes for each entity type
III. Relationships among entity types.
IV. Semantic integrity constraints that reflects the business rules about data not captured in the ER diagram.
(A) I, II, III & IV            (B) I&IV
(C) I, II & IV                   (D) I & III

Q20. Based on the cardinality ratio and participation associated with a relationship type, choose either the Foreign Key Design, the Cross Referencing Design or Mutual Referencing Design.
(A) Entity          (B) Constraints
(C) Rules            (D) Keys

Q21.Data Integrity control uses
(A) Upper and lower limits on numeric data.
(B) Passwords to prohibit unauthorised access to files.
(C) Data dictionary to keep the data
(D) Data dictionary to find last access of data

JUNE 2013

Q22.Which of the following is not a type of Database Management System?
(A) Hierarchical
(B) Network
(C) Relational
(D) Sequential

Q23. Manager’s salary details are to be hidden from Employee Table. This Technique is called as
(A) Conceptual level Data hiding
(B) Physical level Data hiding
(C) External level Data hiding
(D) Logical level Data hiding

Q24.A Network Schema
(A) restricts to one to many relationship
(B) permits many to many relationship
(C) stores Data in a Database
(D) stores Data in a Relation

Q25. Which normal form is considered as adequate for usual database design ?
(A) 2NF
(B) 3NF
(C) 4NF
(D) 5NF

Q26. If D1, D2,…. Dn are domains in a relational model, then the relation is a table, which is a subset of
(A) D1+D2+…. + Dn
(B) D1x D2x… x Dn
(C) D1U D2U….U Dn
(D) D1- D2-….- Dn

DEC 2012

Q27. Which of the following is true ?
(A)    A relation in BCNF is always in 3NF.
(B)    A relation in 3NF is always in BCNF.
(C)    BCNF and 3NF are same.
(D)    A relation in BCNF is not in 3NF.

JUNE 2012

Q28. In multiuser database if two users wish to update the same record at the same time, they are prevented from doing so by
(A) Jamming
(B) Password
(C) Documentation
(D) Record lock

Q29.What deletes the entire file except the file structure ?
(A) ERASE
(B) DELETE
(C) ZAP
(D) PACK

 

 

DEC 2011

Q30. The SQL Expression Select distinct T. branch name from branch T, branch S where T. assets > S. assets and S. branch-city = DELHI, finds the name of
(A) all branches that have greater asset than any branch located in DELHI.
(B) all branches that have greater assets than allocated in DELHI.
(C) the branch that has the greatest asset in DELHI.
(D) any branch that has greater asset than any branch located in DELHI.

JUNE 2011

Q31.Which of the following is the recovery management technique in DDBMS ?
(A) 2PC (Two Phase Commit)
(B) Backup
(C) Immediate update
(D) All of the above

Q32.Which of the following is the process by which a user’s privileges ascertained ?
(A) Authorization
(B) Authentication
(C) Access Control
(D) None of these

Q33.  The basic variants of time-stamp based method of concurrency control are
(A) Total time stamp-ordering
(B) Partial time stamp-ordering
(C) Multiversion Time stamp-ordering
(D) All of the above

Q34. A transaction can include following basic database access operations :
(A) Read_item(X)
(B) Write_item(X)
(C) Both (A) and (B)
(D) None of these

Q35.Decomposition help in eliminating some of the problems of bad design
(A) Redundancy
(B) Inconsistencies
(C) Anomalies
(D) All of the above

DEC 2010

Q36. In generalisation, the differences between members of an entity is
(A) maximized
(B) minimized
(C) both (A) & (B)
(D) None of these

Q37. The dependency preservation decomposition is a property to decompose database schema D, in which each functional dependency X ® Y specified in F,
(A) appeared directly in one of the relation schemas Ri in the decomposed D.
(B) could be inferred from dependencies that appear in some Ri.
(C) both (A) and (B)
(D) None of these

Q38. Which of the following is an optimistic concurrency control method ?
(A) Validation based
(B) Time stamp ordering
(C) Lock-based
(D) None of these

JUNE 2010

Q39. An entity instance is a single occurrence of an _______.
(A) entity type
(B) relationship type
(C) entity and relationship type
(D) None of these

Q40. Generalization is _______ process.
(A) top-down
(B) bottom up
(C) both (A) & (B)
(D) None of these

Q41. Match the following :
I.   2 NF      (a) transitive dependencies eliminated
II.  3 NF      (b) multivalued attribute removed
III. 4 NF      (c) contain no partial functional dependencies
IV. 5 NF      (d) contains no join dependency
Codes :
I   II  III  IV
(A) (a) (c) (b) (d)
(B) (d) (a) (b) (c)
(C) (c) (d) (a) (b)
(D) (d) (b) (a) (c)

Q42. Which data management language component enabled the DBA todefine the schema components ?
(A) DML
(B) Sub-schema DLL
(C) Schema DLL
(D) All of these

Q43. The PROJECT Command will create new table that has
(A) more fields than the original table
(B) more rows than original table
(C) both (A) & (B)
(D) none of these

EDUCATION JOCKEY

educationjockey@gmail.com