Database Management System 1

Assignment A

Q1. What are the disadvantages of flat-file system

Q2. What are different database constraints ?

Q3. What is the difference between internal & external schema ?

Q4. What are the characteristics of data in the database?

Q5. What are the views ? How are they useful?

Q5. What is the difference between Union & Set Intersection Operation.

Assignment B

Ql. Explain the DBMS architecture in detail.

Q2. What is ER model? Draw ER diagram for a Hospital Management System.

Q3. What is normalization? Explain different types of normal forms.

Q4. Design an E/R diagram for the following situation:

We wish to model cities, counties and states in the US. For states, we wish to record the name, population, and state capital (which would be a city). For counties, we wish to record the name, the population, and the state in which it is located. For cities, we wish to record the name, the population, the state in which it is located and the county/counties in which it is located. Names of states are unique. Names of counties are only unique within a state (e.g. 26 states have counties called “Washington”), and cities are likewise unique only within a state (e.g., there is a city called “Lafayette” in Lousiana as well as Indiana). Some counties and cities have the same name, even within a state (example: San Francisco). Almost all cities are located within a single county, but some (e.g., New York City) extend over several counties.

Assignment C

PartA-(Ql-Q20)

1. What does acronym SQL stands for?

a) Standardized Query Language

b) Structured Query Language

c) Simple Query Language

2. List at least three examples of one-to-many relations:

a) mother and children, currency and bank account, inhabitants of town

b) authors and books, prime ministers and countries, client and rented car

c) students and courses, currency and bank account, flight and tourist

3. SELECT – SUM( ) – FROM – GROUP BY

a) is the projective query

b) is the action query

c) is used for creating queries which divide items into groups according to a given attribute and an aggregate function

4. Which are the basic types of DB models?

a) networked, structured, centralized

b) networked, hierarchical, relational

c) relational, hierarchical, action

d) aggregate, projective, action

5. Key word DISTINCT will:

a) sum different data

b) list different values only once

c) add the distinct values

d) none of these

6. What does acronym DBMS stands for?

a) Database Management System

b) Database Management Structure

c) Database Management Standards

d) Database Management Studies

7. Keyword COUNT when used in query will:

a) group dynaset by defined attributes

b) answer the question “How many…?”

c) answer the question “What is the sum of…?”

8. DML stands for:

a) Database Management Language

b) Data Manipulation Language

c) Data Multiplication Language

d) None of these

9. Which format does not belong to the type of Number:

a) integer

b) scientific

c) currency

10. Basic table structure is defined by:

a) attributes, fields

b) attributes, fields, values

c) records, fields, values

11. John is working in the customer table and needs to know what customers are located in
Florida. To find the information, he would.

a) create a new table

b) create a new query

c) create a new form

d) utilize the Database Wizard

12. Where does the DBMS store the definitions of data elements and their relationships?

a) Data file

b) Data dictionary

c) Index

d) Data map

13. When the DBMS translates logical requests into commands that physically locate and
retrieve the requested information, it is fostering data____ .

a) Integrity

b) Inconsistency

c) Independence

d) Mining

14. All of these are true about a database except

a) it is a shared, integrated structure

b) it must contain multiple tables

c) it stores user data

d) iit stores metadata

15. Metadata is:

a) raw facts

b) user created data

c) data about data

d) warehoused data

16. The DBMS stores definitions of the data elements and their relationships in a:

a) fixed length record

b) data dictionary

c) fixed length field

d) information diary

17.____ is defined as “the condition in which all of the data in the database are consistent

with the real-world events and conditions.”

a) data dictionary

b) data integrity

c) data anomaly

d) data processing

18.____ is a DBMS function in which the DBMS creates and manages the complex

structures required for data storage.

a) data dictionary management

b) backup and recovery management

c) data storage management

d) database communication interfaces

19. The___________ manages interaction between the end user and the database.

a) DM query engine

b) DBMQ

c) DBMS

d) DP

20. The hierarchical database model is based on a____ .

a) Tree Structure

b) Lack of a child segment

c) Lack of a parent segment

d) Matrix

Part B(Q21-40)

Write SQL commands

1. To create the above table structure with proper constraints.

2. Select all the PROG type published by BPB from Library.

3. Display a list of all books with Price more then 130 and sorted by Qty.

4. Display all the books sorted by Price in Ascending Order.

5. Display a report. Listing Book No. current value and misplacement charges for each book in the above table. Calculate the misplacement charges for all books Price* 1.25

6. Count the number of books published by PHI

7. Insert a new book in the Library.

8. Count the no of books in each Type.

9. Add one more attribute “Noofcopies” in the above table.

10. Update the new attribute value with 2 for each book.

11. Count the total number of Publishers.

12. Drop table Library.

Give the Output of the following SQL commands.

1. Select Count(Distinct Pub) from Library

2. Select MIN(price) from Library where price<l50

3. Select qty, SUM(qty) from Library GROUPBY Type.

4. Select AVG(price) from Library where qty<3.

5. Select * from Library where Qty IN(Select average(Qty) from Library));

Write relational algebraic expression for :

1. Find all books of Prog type.

2. Find Pub, who publish FND Books.

3. Find all the PROG type published by PHI from Library.