BSc IT Sem III 2017 2018 2018  Database Management System  2017 18 Question Paper - Mumbai University | munotes

Questions asked in this paper

1. Q1 Attempt any three of the following: 15 marks
   • a. Suppose you want to build a video site similar to YouTube and keep data in file-processing system. Discuss the relevance of each of the following points to the storage of actual video data, and to metadata about the video, such as title, the user who uploaded it, tags, and which users
   • i. Data redundancy and inconsistency
   • ii. Difficulty in accessing data
   • v. Atomicity problems
   • b. State the advantages and disadvantages of the following data models: Hierarchical, Network, Relational, Entity Relationship, Object Oriented and NoSQL. State if the models support data and State and explain the twelve Codd’s rules for relational databases
   • d. What is Unified modelling language? What are its parts? Show the ER diagram notations and equivalent notations in UML
   • e. Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Each insurance policy covers one or and has one or more premium payments associated with it. Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received an E-R diagram for keeping track of the exploits of your favourite sports team. You should store the matches played, the scores in each match, the players in each match, and individual player statistics for each match. Summary statistics should be modelled as derived an E-R which the same entity set appears several times, with its attributes repeated in more than one occurrence. Why is allowing this redundancy a bad practice that one should avoid?
2. Q2 Attempt any three of the following: 15 marks
   • a. The natural outer-join operations extend the natural-join operation so that tuples from the participating relations are not lost in the result of the join. Describe how the theta join operation can be extended so that tuples from the left, right, or both relations are not lost from the result of
   • Q.P. Code: 20943
   • b. Given the following relational schemas: R = (A,B,C) Suppose the relations r(R) and s(S) are defined. Write the expressions in tuple relational calculus equivalent to each of the following: Consider the relational database below, where primary keys are underlined Give an expression in tuple relational calculus for each of the following queries:
   • i. Find all employees who work directly for “Jones.”
   • ii. Find all cities of residence of all employees who work directly for “Jones.”
   • iii. Find the name of the manager of the manager of “Jones.”
   • iv. Find those employees who earn more than all employees living in the city “Mumbai.”
   • d. What is normalization? What is its objective? Give a distinguishing characteristic of INF, 2NF,
   • e. i. Using the INVOICE table structure shown in table below, write the relational schema, draw its dependency diagram and identify all dependencies (including all partial. and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite PROD. LABEL. | Rotary 0.25-in. — Band saw Rotary Power drill
   • ii. Using the initial dependency diagram drawn in question i, remove all partial dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure
   • iii. the table structures you created in question ii, remove all transitive dependencies and draw the new dependency diagrams. Also identify the normal forms for each table structure
   • f. Explain the phases of database design
3. Q3 Attempt any three of the following: 15 marks
   • a. What are constraints? What are the different types of constraints? Explain
   • b. What view? What are its advantages? State the rules for performing DML operations on a view
   • d. Explain GROUP BY and ORDER BY clauses with examples
   • e. What are NULL values? Explain. [TURN OVER]
   • Q.P. Code: 20943
   • f. What are joins? What are different types of joins? Explain
4. Q4 Attempt any three of the following: a The lost update anomaly is said to occur if a transaction reads a data item, then another transaction writes the data item (possibly based on a previous read), after which 7; writes the data item. The update performed by 7; has been lost, since the update done by the value 15 marks
   • i. Give an example of a schedule showing the lost update anomaly
   • ii. Give an example schedule to show that the lost update anomaly is possible with the read
   • iii. Explain why the lost update anomaly is not possible with the repeatable read isolation level b State and explain the ACID properties of transactions
   • i. Consider a database for a bank where the database system uses snapshot isolation. Describe a particular scenario in which a nonserializable execution occurs that would present a problem for the bank
   • ii. Consider a database for an airline where the database system uses snapshot isolation Describe a particular scenario in which a nonserializable execution occurs, but the airline may be willing to accept it in order to gain better overall performance d Show that the two-phase locking protocol ensures conflict serializability, and that transactions can be serialized according to their lock points e Consider the following two transactions: Add lock and unlock instructions to transactions and so that they observe the two-phase locking Can the execution of these transactions result in a deadlock? f Explain the different ways to handle deadlocks
5. Q5 Attempt any three of the following: 15 marks
   • a. What are triggers? What are different types of triggers? How are they created? Give the syntax and examples of the same
   • b. What are packages? What are the components of packages? How are packages developed? Explain with syntax and example What are functions? What are procedures? How do they differ from each other? What are the benefits of stored procedures and functions?
   • d. What is a cursor? Explain implicit and explicit cursors. How are explicit cursors controlled?
   • e. What are hierarchical queries? Explain the syntax of hierarchical queries
   • f. What are composite data types? Explain the PL/SQL records. How is a PL/SQL record created?

Read from the scan above, so a character or two may differ. The scan is the original.