Database Management System

1. Explain the three-tier architecture of DBMS and illustrate the concept of Data Independence with it.

2. What is the use of ER diagram? Explain the significance of the Specialization and Generalization concept.

3. What are the two Integrity Constraints in the relational databse model? Explain the difference between them.

4. What do you mean by Normalization? Why do we need it? Explain 2NF and 3NF with suitable examples.

5    What is a View in SQL? Consider the CUSTOMERS table having the following records −    
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+      Write  an SQL query to create a view of CUSTOMERS table with only Name and Address attributes and draw the output of the same.               
Ans- 

7. Illustrate the differences between Mandatory and Discretionary Security Issues. Explain the applicability of both in different situations.
8    What is a Transaction? Explain ACID properties of a transaction in detail.

SECTION B
Case study
A relational database is to be designed for a medium sized Company dealing with industrial applications of computers. The Company delivers various products to its customers ranging from a single application program through to complete installation of hardware with customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on long-term basis, i.e. there is no short-term or temporary staff. Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter-disciplinary way. For each project a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the Company's hierarchy. The following is a brief statement of some facts and policies adopted by the Company:
                                                                                                                                                                                                                                                          • Each employee works in some department.
• An employee may possess a number of skills
• Every manager (including the MD) is an employee
• A department may participate in none/one/many projects.
• At least one department participates in a project.
• An employee may be engaged in none/one/many projects
• Project teams consist of at least one member.                                 

Q.No 1: Analyze the data required. Identify all entities and attributes. 

Q.No 2: Draw the Relational Database Schema for the Company database.

Q.No 3: Draw the ER diagram. Mention proper cardinality.
Ans- 
A Customer has a one-to-many relationship with a Purchase Order because a customer can place many orders, but a given purchase order can be placed by only one customer. The relationship is optional because zero customers might place a given order (it might be placed by someone not previously defined as a customer).
A Purchase Order has a many-to-many relationship with a Stock Item because a purchase order can refer to many stock items, and a stock item can be referred to by many purchase orders. However, you do not know which purchase orders refer to which stock items.
Therefore, you introduce the notion of a Line Item. A Purchase Order has a one-to-many relationship with a Line Item because a purchase order can list many line items, but a given line item can be listed by only one purchase order.
A LineItem has a many-to-one relationship with a StockItem because a line item can refer to only one stock item, but a given stock item can be referred to by many line items. The relationship is optional because zero line items might refer to a given stock item.

SECTION C

A relational database consists of a collection of:
 (A): Tables
 (B): Attributes
 (C): Tuples
 (D): Keys

A ________ in a table represents a relationship among a set of values.
 (A): Column
 (B): Key
 (C): Row
 (D): Entry

The term _______ is used to refer to a row.
 (A): Attribute
 (B): Tuple
 (C): Table
 (D): Instance

For each attribute of a relation, there is a set of permitted values, called the ________ of that attribute.
 (A): Domain
 (B): Relation
 (C): Set
 (D): Schema

Course (course_id,sec_id,semester). Here the course_id,sec_id and semester are __________ and course is a _________ .
 (A): Relations, Attribute
 (B): Attributes, Relation
 (C): Tuple, Relation
 (D): Tuple, Attributes

A domain is atomic if elements of the domain are considered to be ____________ units.
 (A): Different
 (B): Indivisbile
 (C): Constant
 (D): Divisible

The tuples of the relations can be of ________ order.
 (A): Any
 (B): Same
 (C): Sorted
 (D): Constant

A _____ is a property of the entire relation, rather than of the individual tuples in which each tuple is unique.
 (A): Rows
 (B): Key
 (C): Attribute
 (D): Fields

Which one of the following attribute can be taken as a primary key?
 (A): Name
 (B): Street
 (C): Id
 (D): Department

Which one of the following cannot be taken as a primary key?
 (A): Id
 (B): Register number
 (C): Dept_id
 (D): Street

A _________ consists of a sequence of query and/or update statements.
 (A): Transaction
 (B): Commit
 (C): Rollback
 (D): Flashback

Which of the following makes the transaction permanent in the database?
 (A): View
 (B): Commit
 (C): Rollback
 (D): Flashback

Which one of the following is used to define the structure of the relation, deleting relations and relating schemas?
 (A): DML
 (B): DDL
 (C): Query
 (D): Relational Schema

Select * from employee What type of statement is this?
 (A): DDL
 (B): Integrity constraint
 (C): DML
 (D): View

The basic data type char (n) is a _____ length character string and varchar(n) is _____ length character.
 (A): Fixed, equal
 (B): Equal, variable
 (C): Fixed, variable
 (D): Variable, equal

To remove a relation from an SQL database, we use the ______ command.
 (A): Delete
 (B): Purge
 (C): Remove
 (D): Drop

Updates that violate __________ are disallowed.
 (A): Integrity constraints
 (B): Transaction control
 (C): Authorization
 (D): DDL constraints

Database __________, which is the logical design of the database, and the database _______, which is a snapshot of the data in the database at a given instant in time.
 (A): Instance, Schema
 (B): Relation, Schema
 (C): Relation, Domain
 (D): Schema, Instance

The union operation is represented by
 (A): U
 (B): n
 (C): -
 (D): *

If we want to retain all duplicates, we must write ________ in place of union.
 (A): Union all
 (B): Union some
 (C): Intersect all
 (D): Intersect some

For like predicate which of the following is true. i) % matches zero of more characters. ii) _ matches exactly one character.
 (A): i-only
 (B): ii-only
 (C): Both
 (D): None

The number of attributes in relation is called as its
 (A): Cardinality
 (B): Degree
 (C): Tuples
 (D): Entity

_________ joins are SQL server default
 (A): Outer
 (B): Inner
 (C): Equi
 (D): None

Entity is a ____________
 (A): Object of relation
 (B): Present working model
 (C): Thing in real world
 (D): Model of relation

The attribute AGE is calculated from DATE_OF_BIRTH. The attribute AGE is
 (A): Single valued
 (B): Multi valued
 (C): Composite
 (D): Derived

Which of the following is a single valued attribute.  Solve by www.solvezone.in contact for more details at 8882309876
 (A): Register_number
 (B): Address
 (C): SUBJECT_TAKEN
 (D): Reference

Update instructor _____ salary= salary * 1.05; Fill in with correct keyword to update the instructor relation.
 (A): Where
 (B): Set
 (C): In
 (D): Select

A stored procedure in SQL is a___________.
 (A): Block of functions
 (B): Group of Transact-SQL statements compiled into a single execution plan.
 (C): Group of distinct SQL statements.
 (D): None of mentioned

The____condition allows a general predicate over the relations being joined.
 (A): On
 (B): using
 (C): set
 (D): where

Which of the join operations do not preserve non matched tuples.
 (A): Left outer join
 (B): Right outer join
 (C): Inner Join
 (D): Natural join

In case of any shut down during transaction before commit which of the following statement is done automatically?
 (A): Commit
 (B): Rollback
 (C): Flashback
 (D): View

A __________ is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data.
 (A): Procedures
 (B): Triggers
 (C): Functions
 (D): None of these

The default extension for an Oracle SQL*Plus file is:
 (A): .txt
 (B): .pls
 (C): .ora
 (D): .sql

The intersection operator is used to get the _____ tuples.
 (A): Different
 (B): Common
 (C): All
 (D): Repeating

The _____________ is essentially used to search for patterns in target string.
 (A): Like Predicate
 (B): Null Predicate
 (C): In Predicate
 (D): Out Predicate

He ______ clause allows us to select only those rows in the result relation of the ____ clause that satisfy a specified predicate.
 (A): Where, from
 (B): From, select
 (C): Select, from
 (D): From, where

The ________ clause is used to list the attributes desired in the result of a query.
 (A): Where
 (B): Select
 (C): From
 (D): Distinct

Insert into employee _____ (1002,Joey,2000); In the given query which of the keyword has to be inserted ?
 (A): Table
 (B): Values
 (C): Relation
 (D): Field

The database administrator who authorizes all the new users, modifies database and takes grants privilege is
 (A): Administrator
 (B): Super User
 (C): Operator of OS
 (D): All of the mentioned

The union operation automatically __________, unlike the select clause.
 (A): Adds tuples
 (B): Eliminates unique tuples
 (C): Adds common tuples
 (D): Eliminates duplicate