Database Managment System

Reference Books

• Database System Concept - By Korth and Silberschatz
• Principles of Database Management - By James Martin
• Computer Database organization - By James Martin
• Database management systems - By Bipin Desai
• Introduction to database systems - By C J Date
• Relational database design - Prentice Hall
  
  (102) - Database Management System - Question Bank
  Questions for 6 to 12 Marks
  1. Define the term "DBMS" and state advantages & Disadvantages of Database Management System.
  2. Discuss Disadvantages of Traditional Flat File System.
  3. Discuss various levels of data abstraction
  4. Define the term "Data Independence". State types of data independence
  5. Discuss software modules available in DBMS or Architecture of DBMS.
  6. Discuss Entity relationship diagrams in detail
  7. State codd's rules for RDBMS
  8. Discuss various Integrity Constraints
  9. Hardware and Software Backup
  10. Define the term "Transaction" & state its stages and properties
  11. Recovery mechanisms
  12. What is concurrency control. Discuss various mechanisms for concurrency control.
  13. What is data distribution. Discuss in details methods of data distribution.
  14. Database Security - Physical and Logical
  Short Notes - 4 to 6 Marks
  1. Data abstraction
  2. Users of DBMS
  3. DBM
  4. DBA
  5. Keys
  6. Generalization, Specialization & Aggregation
  7. Hierarchical Database model
  8. Network database model
  9. Distinguish between Relational and Non relational databases
  10. Basic concepts of RDBMS
  11. Anomalies
  12. Normalization rules (Normal Forms)
  13. BCNF / DKNF / PJNF
  14. Referential Integrity
  15. Log based recovery
  16. Shadow Paging technique
  17. Check Point mechanism
  18. Locks
  19. Optimistic scheduling
  20. Time stamp based ordering
  21. Multi Version technique
  22. Serializability
  23. Intention locking
  24. Two Phase Protocol
  25. Dead Locks - Prevention mechanisms
  26. Replication & fragmentation
  27. Local & Global transactions
  28. Multi database systems
  29. Systems catalog and object naming
  30. Data transparency
  31. Data encryption and decryption
  32. Logical security
  Define the following terms - 2 to 4 Marks
  1. Instance of Database
  2. DDL - DML - Data dictionary
  3. View
  4. Mapping in ERD
  5. User work area
  6. DBTG Set
  7. Degree and cardinality of table
  8. Domain
  9. Tuple
  10. Normalization
  11. Integrity
  12. Functional Dependency
  13. Transaction and its significance in processing
  14. Lock
  15. Schedule
  16. Strong and weak entity sets
  17. Role of front end tools
  Questions related with SQL
  1. Describe various data integrity constraints available to create the tables in SQL
  2. Views in SQL
  3. Joins
  4. Embedded SQL
  5. Index
  6. Data types
  7. Group by clause
  Topics for Minimum / Maximum Reading
  1. Data warehousing (www.datawarehousing.org) (www.dwinfocenter.org)
  2. Data mining ( www.kdnuggets.com) (http://www.digimine.com/usama/datamine)
  3. Dead locks
  4. ODBC
  5. Database Engine
  6. Object oriented fully distributed database management system.
  7. OODBMS
  8. Small office databases / Home office databases
  9. Web mining
  10. Database Administration
  11. DB2
  DBMS - Cases for Entity Relationship Diagram
• There are various galaxies in universe, which consists of various solar systems. Planets and moons belong to solar system and occasionally there are comets, which may be travelling across various systems.
• Transport Company is responsible for picking up shipment from various warehouses and delivering them to their depot locations. Currently there are 6 warehouses and 40 depot locations. Company uses a fleet of 15 trucks to make schedule trips to transport the goods. Each trip consists of
  1. Picking up consignments from one or more warehouse by truck.
  1. Delivering them to respective depot locations.
  Each truck has specific weight and volume carrying capacity. Each consignment is of known weight and volume. Trip schedules are made considering these parameters.
• Computer Society India (CSI) holds seminars on various subjects in different cities. The same seminar may be schedule at various places on different dates. At the same venue different seminars may be schedule on same date. Some of the members of CSI body are assign duties related to organizational activities for the scheduled seminar at every location. Booking forms are received from all over India. A unique booking number is given to every applicant. The delegates specified in the form, the seminars he wants to attend along with the date of seminar. The delegate also sends the DD as a fee towards the participation in the seminar. The date of receipt of the DD is also recorded.

An information system is to be design for keeping the record of world cup cricket matches. 12 teams. Every team has 19 members out of 15 players and 4 are non players. Age and name of each member is to be recorded where as for non players designation and for players number of ODI is the additional information. Matches are scheduled between teams on specific days and on specific ground. There are 12 grounds selected as venue, where each ground has fixed sitting capacity. For 41 matches scheduled 11 umpires. Two are assigned duties for each match. The performance of every match is to be recorded in terms of the runs he scored and wickets he took.
Cases for Normalization
1. Shriram sales & services and Pvt. Ltd. is the dealer of auto spare parts, as well as vehicles. Following are the details of job card document, design stepwise normalization up to 3 NF. Write suitable assumptions, if any.

2. The following is the list of attributes pertaining to quotation system where record of quotations issued to customer is maintained. Normalize the same up to 3NF by giving proper explanation and assumptions.
Quotation No, Quotation Date, Enq No, Item No, Item Name, Customer Code, Quantity Of Item, Rate For Item, Discount For Entire Quotation, Customer Name, Customer Address, Enq Date, UOM
3. Using following details of railway reservation form design a normalized file structure up to 3NF.
Ticket No, Train No, Train Name, Railway Zone, Journey Date, Class, No Of Seats, Station From, Station To, Boarding At, Sr. No., Name, Sex, Age, Choice, Name Of Person, Booking Address, Telephone, Signature.
4. ABC is an advertisement agency, which collects advertisements from various clients on commission basis for various publications in the city. Using following document design normalization up to 3 NF. Write suitable assumptions.
ABC Advertisement & Publicity Agency
Cash / Credit Memo
Client Name : Bill No : Bill Date :
Client Address : Release Order No : Release Order Date :
Receipt No : Receipt Date :
Client Type Excise Charge :
Sr. No Publication Publication Type Size
Col x Cm Rate Amount
Rs. Ps.

Service Tax Charges :
Box Charges :
Other Charges :
Total :
Signature
XYZ Hospital
2, ABC Lane
Pune
Phone : 5556530
Region No. :
OPD No :
Patient ID :
Name :
Address : Date :
Consultant :
Category :
Break up details Charges
Consultant Visit
Visit Date Name
13/9/2000 Dr. A Kulkarni

XX.X
Sign & Stamp Total :
Received :
Balance :
6. Using following details of Goods Delivery Challan, Design a normalized file structure up to 3NF. Write suitable assumptions
DC No, Date, Party Name, Address, Delivered by, goods returned by, return date, Return time, Sr. No, Description, Quantity, rate, Total amount
--- Best of Luck ---
Aims of the Module
The overall aim of this module is to enable you to develop an understanding of Database Management System, Importance of data and how data is managed, organized for better access and use. In addition the course will also provide an insight into the methods and techniques that are used for database management in heterogeneous environment with multiple users are accessing the distributed databases.
The module also focuses on Data definition and data manipulation using SQL, Designing Issues for databases such as Concurrency, Security, Integrity and Recovery.
Assessment of the Module
Internal : 4 class tests for each subject for each semester. The marks will be converted to 20 considering student's performance in the Class Test , Attendance and overall behavior of the student throughout the semester.
External : Semester Exam conducted by University of Pune for 80 Marks.
Total : 100 Marks (20 Internal + 80 External)
Teaching Strategy
The module will consist of approx. 40 lectures. The topics will be explained through discussion, case studies and online demonstrations. Full participation in the module will require extensive reading, particularly of course handouts in addition to basic references.