Download Database Management Systems (DBMS) - Final Term Subjective Paper 2011 (Fall) and more Study notes Digital Logic Design and Programming in PDF only on Docsity!
CS403 - Database Management Systems
Solved Subjective
From Final Term Papers
JUNE 30 ,201 2
CS403 - Database Management Systems
Final term Subjective Paper 2012 Fall
Question No: 1 Difference between Commit and Rollback. (Marks 2) Answer:- Click here for detail in sql commit is used for saving the changes made in database and roll back is to roll back them , changes will not get saved in the database
Question No: 2 Commonality between RAID technology and Tape Drive (Marks 2) Answer:- Commonality between RAID technology and Tape Drive that they both are used for backup.
Question No: 3 Read the statement if incorrect rewrite in correct form. “Hashing provides slow, time consuming data retrieve from sequential files”. (Marks 2) Answer:- (Page 265) Hashing provides rapid, non-sequential, direct access to records.
Question No: 4 How do you explain Cross Reference Matrix? (Marks 2) Answer:- (Page 65) This is a tool available in the data dictionary and helps us in finding entities of the database and their associations.
Question No: 5 Explain Redundant Data. How Data Redundancy work in sequence file. (Marks 3 ) Answer:- (Page 14 ) It means if different systems of an organization are using some common data then rather than storing it once and sharing it, each system stores data in separate files. This creates the problem of redundancy or wastage of storage and on the other hand the problem on inconsistency.
Question No: 6 State the basic difference between Inner join and Left Outer Join.. (Marks 3 ) Answer:- (Page 226 & 229 ) Inner Join : Only those rows from two tables are joined that have same value in the common attribute while In a left outer join, PROGRAM rows without a matching COURSE row appear in the result left table (i.e. the one that precedes in SQL statement) regardless of the existence of matching records in the right table.
Question No: 7 Give one example of Deadlock. (Marks 3 ) Answer:- Click here for detail A simple computer-based example is as follows. Suppose a computer has three CD drives and three processes. Each of the three processes holds one of the drives. If each process now requests another drive, the three processes will be in a deadlock. Each process will be waiting for the "CD drive released" event, which can be only caused by one of the other waiting processes. Thus, it results in a circular chain.
Question No: 8 Create unique index on “IndexNum” on Cust_Name attribute of Customer table.. (Marks 3 ) Answer:- Click here for detail CREATE INDEX IndexNum ON Customer (Cust_Name)
Question No: 9 · Relate column 1 with column 2 .. (Marks 5 )
Column 1 Column 2
- Sigma A. Procedural DML lang
- Table B. Project operator
- Relational Algebra C. Select operator
- П D. Relational Data Model
- Intersection operator E. Binary operation
Answer:-
- Sigma C. Select operator (Page 149)
- Table D. Relational Data Model
- Relational Algebra A. Procedural DML lang
- ÏÏ B. Project operator
- Intersection operator E. Binary operation
Question No: 10 Crater a VIEW INSTRUCTOR_LIST which shows the list of instructor table with Inst_Nam and City of these instructors belong to ISLAMABAD or KARACHI. INSTRUCTOR (Inst_ID, Imst_Name, City) (Marks 5 )
Question No: 3 Analyze the below given statements and mark as correct and incorrect and also explain.
- Deadlock occurs when one transaction wants to read more than on object at same time. Answer:- (Page 320) The given statement is incorrect. The correct statement is A deadlock occurs when the first transaction has locks on the resources that the second transaction wants to modify, and the second transaction has locks on the resources that the first transaction intends to modify.
Question No: 4
- The mechanism “Wait for Graph” is used to apply join on multiple. Answer:- (Page 320) The statement is incorrect and correct statement is Wait – for Graph: It is used for the detection of deadlock
CS403 - Database Management Systems
Final term Subjective Paper 2012(fall)
Question No: 1 How many ways to create a new view in your data base? (Marks 2 ) Answer:- (Page 283) There are two ways to create a new view in your database. You can: • Create a new view from scratch. • Or, make a copy of an existing view and then modify it.
Question No: 2 Write any two similarities between Materialized views and indexes? (Marks 2 ) Answer:- (Page 290) Materialized views are similar to indexes in several ways:
- They consume storage space.
- They must be refreshed when the data in their master tables changes.
Question No: 3 Which method is in DBMS to detect Deadlock? Briefly explain (Marks 3 ) Answer:- (Page 300) The lock manager maintains a structure called a waits-for graph to detect deadlock cycles.
Question No: 4 Create new view as Product_list from the table PRODUCT including all the columns of the table PRODUCT? (Marks 5 )
Question No: 5 Given two tables DOCTOR (D_ID,D_CONTACT) , MEDICINE(M- CODE, Description) (i) Create unique index D_ID on DOCTOT (ii)Create unique index on both M-CODE, Description (Marks 5 )
Answer:- Click here for detail (i) CREATE UNIQUE INDEX D_ID ON DOCTOT (D_ID,D_CONTACT)
Answer:- (ii)CREATE UNIQUE INDEX D_ID ON MEDICINE (M- CODE, Description)
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: 1 What are two primary modes for taking locks (Marks 2 ) Answer: (Page 319) There are two primary modes for taking locks: optimistic and pessimistic.
Question No: 2 How minimum cardinality finding is important for relationship (Marks 2 ) Answer: (Page 91) It is very important to determine the minimum cardinality when designing a database because it defines the way a database system will be implemented.
Question No: 3 What are purpose of INPUT form (Marks 2 ) Answer: (Page 246) Input forms are especially useful when the person entering the data is not familiar with the inner workings of Microsoft Access and needs to have a guide in order to input data accurately into the appropriate fields.
Question No: 4 What are three concurrency problems (Marks 3 ) Answer:- Click here for detail The lost update problem. The uncommitted dependency problem The inconsistent analysis problem.
Question No: 9 Differentiate Total and Partial completeness (Marks 5 ) Answer:- (Page 103) Total Completeness: Total Completeness constraint exist only if we have a super type and some subtypes associated with that supertype, and the following situation exists between the super type and subtype. All the instances of the supertype entity must be present in at one of the subtype entities, i.e.—there should be not instance of the supertype entity which does not belong to any of the subtype entity.
Partial Completeness Constraint: This type of completeness constraint exists when it is not necessary for any supertype entity to have its entire instance set to be associated with any of the subtype entity. This type of situation exists when we do not identify all subtype entities associated with a supertype entity, or ignore any subtype entity due to less importance of least usage in a specific scenario.
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: 1 What is a transaction? What are ACID properties? Answer: (Page 291 ) A transaction can be defined as an indivisible unit of work comprised of several operations, all or none of which must be performed in order to preserve data integrity. ACID properties: Atomicity Consistency: Isolation: Durability:
Question No: 2 What happened when lack of durability in transaction? Answer:- Click here for detail In case of lack of durability, if the transaction programs fails, or the operating system fails, once the transaction has committed, all updates will be loss.
Question No: 3 What is alternate key? Answer:- (Page 83 ) Candidate keys which are not chosen as the primary key are known as alternate keys.
Question No: 4 What are five features of Views? Answer:- rep
Question No: 5 Difference between Delete and truncate commands? Answer:- Click here for detail The DELETE command is used to remove rows from a table while TRUNCATE removes all rows from a table After DELETE you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it while In TRUNCATE The operation cannot be rolled back IN DELETE triggers will be fired while in TRUNCATE no triggers will be fired. TRUCATE is faster and doesn't use as much undo space as a DELETE
Question No: 6 What is the purpose of DML commands? Answer:- (Page 2 00) Data Manipulation is retrieval, insertion, deletion and modification of information from the database. A DML is a language, which enables users to access and manipulate data. The goal is to provide efficient human interaction with the system.
Question No: 7 What is serial execution? Answer:- (Page 312 ) Serial execution is an execution where transactions are executed in a sequential order, that is, one after another.
Question No: 8 What are the features of indexed serial execution?
Question No: 9 What is Unary and Ternary relationship? Answer:- (Page 87 & 88 ) Unary Relationship An ENTITY TYPE linked with itself, also called recursive relationship. Ternary Relationship A Ternary relationship is the one that involves three entities
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: 1 What is the difference between Commit and rollback? (Marks 2 ) Answer:- rep
Address varchar(15), City varchar(15) )
INSERT INTO Persons VALUES ('aslam','kashif','civil line','Karachi')
INSERT INTO Persons VALUES ('shahid','ali','Defence','Lahore')
INSERT INTO Persons VALUES ('kamran','shaheen','Shadman','Faisalabad')
INSERT INTO Persons VALUES ('Ahmad','Ali','Muslim Town','Multan')
INSERT INTO Persons VALUES ('shamas','khan','shah street','Koita')
SELECT * FROM Persons WHERE FirstName='Ahmad' AND LastName='Ali'
Question No: 9 What is unary and ternary Relationship? (Marks 5 ) Answer:- rep
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: 1 Application programmer as user of database systems, (Marks 5 ) Answer:- (Page 24) Application programmers design the application according to the needs of the other users of the database in a certain environment. Application programmers are skilled people who have clear idea of the structure of the database and know clearly about the needs of the organizations.
Question No: 2 Differentiate between rollback and rollforward. (Marks 5 ) Answer:- Click here for detail Rollback :- Undoing the changes made by a transaction before it commits or to cancel any changes to a database made during the current transaction RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the changed calue again to ensure consistency
Question No: 3 why will you prefer delete command on drop command while deleting a table (Marks 5 ) Answer:- http://stackoverflow.com/questions/1143915/what-is-the-difference-between-drop-table- and-delete-table-in-sql-server Drop table. it will delete complete table from th Database.it can not retrieved back. Delete is used to deleting data from the table... Data can be retrieved using Rollback.
Question No: 4 how many number of clusters are used in database and reason of limit
Question No: 5 purpose of having clause Answer:- rep
Question No: 6 purpose of protection Answer:- rep
Question No: 7 explain data independency Answer:- (Page 16) Data and programs are independent of each other, so change is once has no or minimum effect on other. Data and its structure is stored in the database where as application programs manipulating this data are stored separately, the change in one does not unnecessarily effect other.
Question No: 8 two types of interface Answer:- (Page 240) Following are the two types of user interfaces:
- Text based
- Graphical User Interface (GUI) most commonly called as Forms
Question No: 9 Write DML statement that changes the values of one or more than one attribute based on some condition. Answer:- (Page 208) The UPDATE statement changes the values of one or more columns based on some condition.
Question No: 10 Describe insertion anomaly Answer:- Click here for detail insertion anomaly indicates that we cannot insert a fact about one entity until we have an additional fact about another entity. Suppose we want to store the information that the cost of car is Rs. 14,00,000, but we cannot
Question No: 7 In which situation self join is used? Answer:- (Page 231) In self join a table is joined with itself. This operation is used when a table contains the reference of itself through PK, that is, the PK and the FK are both contained in the same table supported by the referential integrity constraint.
Question No: 8 Why direct access is preferred over sequential access? Answer:- rep
Question No: 9 Define the domain of attribute? (Marks 2 ) Answer:- (Page 76) Domain is the set of possible values that an attribute can have, that is, we specify a set of values either in the form of a range or some discrete values, and then attribute can have value out of those values.
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: 1 Explain Secondary key and give it‟s an example. Answer:- Click here for detail An entity may have one or more choices for the primary key. Collectively these are known as candidate keys. One is selected as the primary key. Those not selected are known as secondary keys.
For example, an employee has an employee number, a National Insurance (NI) number and an email address. If the employee number is chosen as the primary key then the NI number and email address are secondary keys. However, it is important to note that if any employee does not have a NI number or email address (i.e.: the attribute is not mandatory) then it cannot be chosen as a primary key.
Question No: 2 Three Factors when designing an indexed sequential file. Answer:- (Page 261 ) The simplest indexing structure is the single-level one: a file whose records are pair‘s key-pointer, where the pointer is the position in the data file of the record with the given key.
Question No: 3 Procedure which truncates command removes the data. Answer:- (Page 207 ) The TRUNCATE is used to delete all the rows of any table but rows would exist. If we want to remove all records we must use TRUNCATE.
Question No: What is the mean of “Operational maintenance” of database? Answer:- (Page 56 ) Maintenance means to check that all parts of the system are working and once the testing of the system is completed the periodic maintenance measure are performed on the system to keep the system in working order.
Question No: Three problems which concurrency not controlled. Answer:- rep
Question No: Two primary modes of taking locks. Answer:- rep
Question No: What is the basics purpose of window control? Give an example. Answer:- (Page 244 ) Used to take input and display output like buttons, checkboxes etc.
Question No: Why prefer direct access over sequential access of files. Answer:- rep
Question No: Name two types of completeness Constraint. Answer:- (Page 103 ) There are two types of completeness constraints, partial completeness constraints and total completeness constraints Question No: Basic Syntax of INDEX. Answer:- Click here for detail CREATE INDEX index_name ON table_name (column_name)
Question No: Write any five advantage of database system (Marks 5 ) Answer:- Click here for detail Reduced data redundancy Reduced updating errors and increased consistency Greater data integrity and independence from applications programs Improved data access to users through use of host and query languages Improved data security
Question No: Describe To_Date() function (Marks 5) Answer:- Click here for detail The TO_DATE function converts a formatted TEXT or NTEXT expression to a DATETIME value. This function is typically used to convert the formatted date output of one application (which includes information such as month, day, and year in any order and any language, and separators such as slashes, dashes, or spaces) so that it can be used as input to another application.
Question No: Explain database objects 5 Answer:- rep
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: Q1- What is Logical data base? Answer:- (Page 187 ) In logical data base design we group things logically related through same primary key.
Question No: Q2- How do we DELETE row by row from the table? Answer:- Click here for detail It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE * FROM table_name
Q3- Write down the Three types of view. Answer:- rep
Q4- Write down the syntax of command CREATING INDEX. Answer:- rep
Q5- What do u mean by GROUP By command Answer:- Click here for detail The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Q6- Give the implementation of One to many relationship Answer:- (Page 92 )
Q 7 - Give similarities between Materialized view and indexes Answer:- rep
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Q no: 1 Write the two types of ordered indices? Answer:- (Page 276 ) There are Two types of ordered indices: Dense Index: Sparse Index:
Q no: 2 Write three types of data Independence? Answer:- Click here for detail
- Logical Data Independence
- Physical Data Independence
- View Data Independence
Q no: 3 How do we prevent deadlock in DBMS? Answer:- (Page 299 ) We can prevent deadlocks by giving each transaction a priority and ensuring that lower priority transactions are not allowed to wait for higher priority transactions (or vice versa).
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question#1 (Marks 2 ) SELECT * FRO Persons WHERE Firstname Like ―% da %‖; What does the above statement returne? Answer:- Return all records containing L in attribute name ‗Firstname‘ from persons table.
Question#2 (Marks 2 ) What is the difference between Primary key and a unique key with reference to clustered and non-clustered indexes? Answer:- Click here for detail Primary key can not be null but unique can have only one null value. Primary key create the cluster index automatically but unique key not. A table can have only one primary key but unique key more than one.
Question#3 (Marks 5) Create a simple index, named ―Personal Index‖ on the LastName field of the person table?
Answer:- Click here for detail TABLE person ( FirstName char(15), LastName char(15), Address char(50), City char(50), Country char(25), ) CREATE INDEX new_person_ LastName on person (LastName)
Question#4 (Marks 5 ) Writing the basic syntax of adding a record to a table. Answer:- Click here for detail INSERT INTO table_name VALUES (value1, value2, value3...)
Question#5 (Marks 5 ) Give 4 similarities between Materialized views and indexes? Answer:- (Page 290)
- They consume storage space.
- They must be refreshed when the data in their master tables changes.
- They improve the performance of SQL execution when they are used for query rewrites.
- Their existence is transparent to SQL applications and users.
Question#6 (Marks 3 ) How the stamping approach does works to give priorities to the transactions?
Question#7 (Marks 3 ) Name the primary key modes for taking locks? Answer:- rep
Question#8 (Marks 2 ) State any two problems that can come up as a result of inconsistent data base? Answer:- (Page 307 ) Lost Update Problem Uncommitted Update Problem Inconsistent Analysis Problem
CS403 - Database Management Systems
Final term Subjective Paper 2011(fall)
Question No: Q# briefly describes the dynamic view. (Marks 5 ) Answer:- (Page 284 ) Dynamic views are those types of views for which data is not stored and the expressions used to build the view are used to collect the data dynamically. These views are not executed only once when they are referred for the first time, rather they are created and the data contained in such views is updated every time the view is accessed or used in any other view or query. Dynamic views generally are complex views, views of views, and views of multiple tables.
Question No: Q# briefly explains the significance of not null constraint. (Marks 5 ) Answer:- Click here for detail NOT NULL constraints in Microsoft SQL Server allow you to specify that a column may not contain NULL values. When you create a new NOT NULL constraint on a database column, SQL Server checks the column‘s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.
