Database Management System Q&A, Exams of Database Management Systems (DBMS)

Download Database Management System Q&A and more Exams Database Management Systems (DBMS) in PDF only on Docsity!

DATA BASE MANAGEMENT SYSTEMS

For Fifth Semester B.Sc. (Computer Science) / B.C.A. Students

(Common to two branches)

UNIVERSITY OF MADRAS

Syllabus for B.Sc. (Computer Science) / B.C.A. Degree Course

UNIT I :

Advantages and Components of a Database Management Systems - Feasibility Study - Class Diagrams - Data Types - Events - Normal Forms - Integrity - Converting Class Diagrams to Normalized Tables - Data Dictionary.

UNIT II :

Query Basics - Computation Using Queries - Subtotals and GROUP BY Command - Queries with Multiple Tables - Subqueries - Joins - DDL & DML - Testing Queries.

UNIT III :

Effective Design of Forms and Reports - Form Layout - Creating Forms - Graphical Objects - Reports - Procedural Languages - Data on Forms - Programs to Retrieve and Save Data - Error Handling.

UNIT IV :

Power of Application Structure - User Interface Features - Transaction - Forms Events - Custom Reports - Distributing Application - Table Operations - Data Storage Methods - Storing Data Columns - Data Clustering and Partitioning.

UNIT V :

Database Administration - Development Stages - Application Types - Backup and Recovery - Security and Privacy - Distributed Databases - Client/Server Databases Web as a Client/Server System - Objects - Object Oriented Databases - Integrated Applications.

Text:

1. G. V. Post - Database Management Systems Designing and Building Business Application

• McGraw Hill International edition - 1999.

References:

1. Raghu Ramakrishnan - Database Management Systems - WCB/McGraw Hill - 1998.
2. C.J. Date - An Introduction to Database Systems - 7th^ Edition - Addison Wesley - 2000.

DATABASE MANAGEMENT SYSTEMS

11. What is meant by first Normal Form? (NOV .2012, APR. 2016) When a table has no repeating groups it is said to be in first Normal form (1NF).For each cell in a table there can be only one value, it cannot be decomposed into smaller pieces. 12. What is the usage of the Report Writer? (NOV. 2013) The Report Writer enables to set up the report on the screen to specify how items will be displayed or calculated. 13. What is the purpose of a class diagram? (NOV .2014) The purpose of a class diagram is to visualize the model of the classes and associations connected to them. It gives more information about the classes and associations.

14. What are the primary data types used in Business Applications? (NOV. 2014) The primary data types used in Business Application are Text, Number, Date and Time, Interval, Image, auto number.

15. What is the purpose of a feasibility study? (NOV .2015) The purpose of a feasibility study is to analyse and evaluate a proposed project to determine if it is technically worth with in the cost and if it is benefit or profitable in an organization. 16. List down the advantages of DBMS. (APR .2013) The advantages of DBMS are Minimal Data redundancy Data Consistency Integration of Data Sharing of Data Enforcement of standards Ease of Application Development Uniform security, privacy and Integrity Data Independence 17. Define class Diagrams (NOV .2015, APR .2013) A class Diagram is a visual model of the classes and associations in an organization. The basic features are class names (entities) in boxes and associations (relationships) connecting them. 18. What are Events? (APR .2013) Events are actions that are dependent on time .It is an important component of modern data base system. Business events, Data change, user interface are the types of events occur in a Data Base Environment. 19. Define Foreign key (APR. 2015)

A Foreign key is a field or collection of fields in one that uniquely identifies a row of another table. The foreign key is defined in the second table but it refers to the primary key in the first table.

20. What is meant by Table? (APR. 2016) A Table is a collection of columns and rows that describes an entity. 21. What is a Data Base? (APR .2014) A Data Base is collection of data stored in a standardized format, designed to be shared by multiple users. 22. Define Entity, class and objects (APR. 2014) Entity: Something in the real world that you wish to describe or track is called entity. E.g. Customer, sales Class: Description of an entity that includes its attributes (properties) and behaviour (methods) is a class. Objects: One instance of a class with specific data is called an object of that class. 23. What is encapsulation and why it is important? (APR. 2014) Defining properties and methods with in the class is known as encapsulation. Encapsulation is important because it places all relevant definitions in one location and also provides security and control features.

Question & Answers for Five marks:

1.What are the primary benefits or advantages provided by a DBMS(APR. 2014, NOV .2015 ,10 MARKS IN NOV. 2012) The advantages of DBMS are Minimal Data redundancy Data Consistency Integration of Data Sharing of Data Enforcement of standards Ease of Application Development Uniform security, privacy and Integrity Data Independence Minimal Data redundancy: DBMS stores data efficiently. If we have some rules in our database, the data will be stored in a minimal wasted space. The data can be retrieved rapidly to answer any query. Data Consistency: DBMS has systems to maintain data consistency with minimal effort. Most systems enable to create basic business rules when defining a data. Integration of Data: The DBMS , particularly the query language makes it easy to integrate data. With DBMS any data can be easily retrieved , combined and compared using the query system.

Sharing of Data :

Increased value The value can be increased by better access to data,better decisions, better communication, more timely reports, faster reaction to change,new products and services. Strategic advantages: The strategic advantage is occurred by locking out of competitors.

3. Describe briefly on first and second normal forms with examples(APR. 2012) 1st Normal Form A database is in first normal form if it satisfies the following conditions:  Contains only atomic values  There are no repeating groups An atomic value is a value that cannot be divided. For example, in the table shown below, the values in the [Color] column in the first row can be divided into "red" and "green", hence [TABLE_PRODUCT] is not in 1NF. A repeating group means that a table contains two or more columns that are closely related. 1st Normal Form Example Consider the following example:

This table is not in first normal form because the [Color] column can contain multiple values. For example, the first row includes values "red" and "green." To bring this table to first normal form, we split the table into two tables and now we have the resulting tables:

Now first normal form is satisfied, as the columns on each table all hold just one value. 2nd Normal Form Definition A database is in second normal form if it satisfies the following conditions:  It is in first normal form  All non-key attributes are fully functional dependent on the primary key

A table that is in 1st normal form and contains only a single key as the primary key is automatically in 2nd normal form. 2nd Normal Form Example Consider the following example:

This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form. To bring this table to second normal form, we break the table into two tables, and now we have the following:

What we have done is to remove the partial functional dependency that we initially had. Now, in the table [TABLE_STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID].

4. What is Functional Dependency?Explain 2NF(APR. 2015,NOV. 2010) Functional dependency is a relationship that exists when one attribute uniquely determines another attribute. If R is a relation with attributes X and Y, a functional dependency between the attributes is represented as X->Y, which specifies Y is functionally dependent on X. 2nd Normal Form Example Consider the following example:

10

DKNF is important for application developers because it is a goal to work towards when designing applications. Example Student Table Advisor Table

This table is not in DKNF because a student can have more than one major. So SID would not be a unique primary key. Also advisor is related to major which is a hidden relationship. Solution:  Splitting the tables and makes the primary key unique.  The hidden relationship between Major and Discipline is made explicit through a foreign key constraint. The spitted table will be Student Table Advisor table Faculty Table

Here the primary key SID is unique and the hidden relationship is explicitly shown by the foreign key FID. Hence the table is in DKNF. 7.Write short notes on Class diagrams (APR. 2016) A class Diagram is a visual model of the classes and associations in an organization. The basic features are class names (entities) in boxes and associations (relationships) connecting them. The properties of class name or entities are included in the boxes. The associations or the relationships have several options.(1) The maximum number of objects that can be related and (2) The minimum number of objects must be included. A small class diagram for a basic order system.

SID Name Major Advisor 345 akash cs Hari 433 Banu Tamil Arun 450 Chndru commerce Raja

FID Name Office Discipline 155 Hari Computer Bsc 160 Raja English BA 165 Arun Tamil B.com

SID Name 345 akash 433 Banu 450 Chndru

SID Major FID 345 Cs 155 433 Tamil 160 450 commerce 165

FID Name Office Discipline 155 Hari Computer Bsc 160 Raja English BA 165 Arun Tamil B.A

Customer Salesperson

order

orderitem

Item

Here the numbers indicate the relationships. Each customer can place many orders , but a given order can come from only one customer.

8. Discuss about various Association details(APR. 2014) The Association details are ***** N-ary Associations ***** Aggregation *Composition *Generalization *Reflexive Associations N-ary Associations:

Many to many relationship between classes cause problems in the database design. Hence many to many associations are converted into one to many associations with an N-ary associations which include a new class. When more than two classes are related to each other through an Assembly Association ,the relationship is called an N-ary association and is drawn as Diamond. Many to many relationship:

N-ary Associations:

Employee

Component product

Employee name

Component Comp id Type Name

Product ProductID Type Name

Assembly

Assembly EmployeeID CompID ProductID

A reflexive Association is a relationship from one class back to itself. It arises in business that requires special handling. This can be shown as

manager worker

This shows a manager is an employer who manages other workers.

9.Explain the entity relationship diagram with class(NOV. 2013) An entity-relationship diagram ( ERD ) is a data modelling technique that graphically illustrates an information system's entities and the relationships between those entities. The major components of this diagram are Attributes Attributes are the properties of entities. Attributes are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity (rectangle).

Multi valued attributes are depicted by double ellipse.

Employee

Derived attributes are depicted by dashed ellipse.

Relationship

Relationships are represented by diamond-shaped box. Name of the relationship is written inside the diamond-box. All the entities (rectangles) participating in a relationship, are connected to it by a line.

Participation Constraints

 Total Participation − each entity is involved in the relationship. Total participation is represented by double lines.

 Partial participation − Not all entities are involved in the relationship. Partial participation is represented by single lines.

11. What are events? How they are describe in a database design? (NOV. 2011) Events are actions that are independent of time. It is a component of modern database systems. There are three types of events occur in a database environment. Business events trigger some function, such as sale triggering a reduction in inventory. Data changes events gives some alert, such as inventory that drops below a present level, which triggers a new purchase order. User Interface events that trigger some action .such as user clicking on an icon to send a purchase order to a supplier. UML provide several diagrams to illustrate events. Collaboration diagram: An interaction diagram that shows, for one system event described by one use case, how a group of objects collaborate with one another. It is useful for recording business processes and events. Sequence diagram: Complex user interface events can be displayed in the sequence diagrams. Process diagram:

For complex chain of events we draw a process diagram. The combined model can be shown as

Subtract (prod, Qty sold)

Analyse

Reorder (product, quantity)

Question & Answers for Ten marks:

1. Explain the components of DataBase Management Systems.(APR. 2014,APR.2012,

NOV. 2010,NOV. 2013,NOV. 2015,NOV.2011,NOV .2010)

The basic components of DBMS are Data Base Engine Data Dictionary Query Processor Report Writer Forms Generator Application Generator Communication and Integration Security Data Base Engine: It is the heart of DBMS responsible for storing ,retrieving and updating the data. It affects the performance and handle large problems. It is responsible for enforcing business rules regarding the data (i.e.) it will not allow any negative values.

Order

Ship order

Purchase Reorder

Inventory Subtract Analyse

Data Dictionary: The Data Dictionary holds the definitions of all the data tables. It describes the type of data being stored. It allows the DBMS to keep track of the data. It helps developers and users to find the data they need. Query Processor: All database operations can be run through query language. It is necessary to minimize the communication problem. When query runs, DBMS Query Processor works with a database engine to find the appropriate data. Report Writer: The summaries of data with common format is called report. The Report Writer enables to set up the report on the screen to specify how items will be displayed or calculated. It also formats the data according to the report template and creates the report complete with page numbers, headings and footers Forms Generator: It helps the developer to create input forms. The goal of Forms Generator is to create forms to make it easy for users to enter data. It includes graphs and images. Application Generator: Application is a collection of forms and reports designed for a specific user task. It can be small and consists few input forms and reports or they can be large, complex systems that integrate data from several databases with hundreds of forms and reports. Communication and Integration: It is designed to store and use data in several database running on different machines, even if they are in different locations. Modern operating systems and independent networks including internet made easier to connect databases running in different locations. Security: Security is a complex issue with database running on personal computers. DBMS is responsible for establishing and maintaining security access controls.

2. Explain about BCNF and 4 NF with examples (APR. 2013) BCNF: To overcome the problems in first three normal forms Codd and Boyce defined a version called Boyce-Codd Normal Form. Defn: A secondary relationship between columns with in a table can cause problems with duplication and lost data. Example: Employee_speciality(E#,speciality,manager) In this example the employee can have many specialities, Each speciality has many managers , Employee has only one manager for each speciality, and each manager has only one speciality. The speciality in the column shows hidden dependency in the table To solve this problem the table can be split into simple tables as Employee(e#, manager) Manager(manager,speciality)

The properties of class name or entities are included in the boxes. The associations or the relationships have several options.(1) The maximum number of objects that can be related and (2) The minimum number of objects must be included

A small class diagram for a basic order system.

Here the numbers indicate the relationships. Each customer can place many orders , but a given order can come from only one customer.

The Association details are ***** N-ary Associations ***** Aggregation *Composition *Generalization *Reflexive Associations N-ary Associations: Many to many relationship between classes cause problems in the database design. Hence many to many associations are converted into one to many associations with an N-ary associations which include a new class. When more than two classes are related to each other through an Assembly Association ,the relationship is called an N-ary association and is drawn as Diamond.

Many to many relationship:

Employee

Component product

Customer Salesperson

order

orderitem

Item

20

N-ary Associations:

Aggregation: UML defined some special technique of association called Aggregation or Collection. This is indicated by a small diamond on the association line next to the class that is the aggregate. This can be shown as

Composition: Composition is a stronger aggregate association that arise most often. In a composition , the individual items become a new object. The individual classes are separated and marked with a filled diamond.

Employee name

Component

Comp id

Type

Name

Product ProductID Type Name

Assembly

Assembly EmployeeID CompID ProductID

Sale Sale Date Employee

Item Description cost

Bicycle Size Model Type

Wheels Rims

Crank Item id

Stem Item id Weight