DBMS Lecture Questions PDF

## Relational Model 1. Relational model is a simple model in which the database is represented as a collection of **relations**. 2. Each row in a table represents a record and is called a **tuple**. 3. is a collection of tuples for a given relational schema at a specific point in time. - Rela...

## Relational Model 1. Relational model is a simple model in which the database is represented as a collection of **relations**. 2. Each row in a table represents a record and is called a **tuple**. 3. is a collection of tuples for a given relational schema at a specific point in time. - Relational Schema - **Relational Instance** - Relational Tuple - Relational Attributes 4. is the number of attributes in the relation, while is the number of tuples in a relation. - Degree, **Cardinality** - Column, Attribute - Table, Database - Primary Key, Foreign Key ## Relational Constraints 1. All the following are types of relational constraints except **attribute constraints**. - Domain Constraints - Attribute Constraints - Key Constraints - Integrity Constraints 2. constraints specify that each attribute in relation to an atomic value from the corresponding domains. - **Domain Constraints** - Attribute Constraints - Key Constraints - Integrity Constraints 3. constraints states that the key attribute value in each tuple must be unique. - Domain Constraints - Attribute Constraints - **Key Constraints** - Integrity Constraints 4. constraints states that no primary key value can be null and unique. - Domain Constraints - Attribute Constraints - Key Constraints - **Integrity Constraints** 5. constraints states that the tuple in one relation that refers to another relation must refer to an existing tuple in that relation. - Domain Constraints - Attribute Constraints - Key Constraints - **Referential integrity Constraints** 6. constraints is specified on two relations. If a column is declared as foreign key that must be primary key of another table. - Domain Constraints - Attribute Constraints - Key Constraints - **Referential integrity Constraints** ## Anomaly 1. is an irregularity or something which deviates from the expected or normal state. - Primary key - Constraints keys - **Anomaly** - Referential integrity 2. All the following are types of anomaly expect **key anomaly**. - Key anomaly - Delete anomaly - Update anomaly - Insert anomaly ## Keys 1. Any set of attributes that allows us to identify unique rows (tuples) in a given relation are known as **super keys**. - Super keys - Constraints keys - Primary keys - Composite keys 2. Out of super keys we can always choose a proper subset among them which can be used as a **primary key**. - Key anomaly - **Primary key** - Candidate key - Super key 3. Out of super keys we can always choose a proper subset among them which can be used as a primary key: Such keys are known as **candidate keys**. - Super keys - **Candidate keys** - Primary keys - Composite keys 4. If there is a combination of two or more attributes which is being used as the primary key then we call it as a **composite key**. - **Composite key** - Primary key - Candidate key - Super key ## Payroll Table - **Table Name:** Payroll - **Columns:** `SN`, `Name`, `Last Name`, `Salary`, `DepartmentID` - **Data:** - `SN`: 1, 2, 3 - `Name`: Harry, John, Johnny - `Last Name`: Potter, Paul, Alex - `Salary`: 5000, 5600, 2000 - `DepartmentID`: 1001, 1002, 1003 ## SQL Queries 1. How can I create the above payroll table. 2. How can I make the SN column uniques and auto increment. 3. How can I rename Johnny to Peter. 4. How can I insert the following record into the payroll table: `SN=4, Name=Lisa, Salary=6000, DepartmentID=1002`. 5. How can I rename the LastName column to Surname. 6. How can I make the DepartmentID column a foreign key. 7. How can I get the maximum, minimum, total, and average salaries. 8. How can I remove the salary column. 9. How can I display the payroll table structure. 10. How can I show a name-salary merged column. ## MySQL Command Output 1. `SELECT * FROM employee WHERE title='HEAD TELLER'`: **All columns but only those rows which contain 'HEAD TELLER' as a "title"**. 2. `SELECT * FROM employee WHERE (title='HEAD TELLER') AND (start_date='2013-01-24')`. **All columns but only those rows which contain 'HEAD TELLER' as a "title" and 2013-01-24 as a "start date"**. 3. `SELECT emp_id, fname, lname FROM employee WHERE title='HEAD TELLER' AND start_date='2008-11-24'`: **Columns mention with "SELECT" clause and only those rows which contain 'HEAD TELLER' as a "title" and start_date as 2008-11-24**. 4. `CREATE table PERSON(ID int(10), Name varchar(20), Phone varchar(10), Address varchar(100))`. **A Person table with four attributes will be created**. ## MySQL Query Generation **Table:** | CUST\_NAME | CUST\_COUNTRY | OUTSTANDING\_AMT | |---|---|---| | Micheal | USA | 6000.00 | | Albert | USA | 6000.00 | **Query:** - `select CUST_NAME, CUST_COUNTRY, OUTSTANDING_AMT FROM customer WHERE CUST_COUNTRY = 'USA' AND OUTSTANDING_AMT >= 3000;` **Output:** **Table:** | CUST\_NAME | CUST\_COUNTRY | OUTSTANDING\_AMT | |---|---|---| | Micheal | USA | 6000.00 | | Albert | USA | 6000.00 | **Query:** `SELECT sum(outstanding_amt), max(outstanding_amt), min(outstanding_amt), avg(outstanding_amt) FROM CUSTOMER` **Output:** `12000, 6000, 6000, and 6000` ## Updating Table 1. `Alter table customer add customer_id = 5, first_name = Harry, last_name = Potter, age = 31, and country = 'USA';` 2. `Insert into customer values(5, 'Harry', 'Potter', 31, 'USA');` 3. `Update customer set first_name = 'Johnny' where customer_id = 1;` ## Query Processing 1. is the entire process of translating a query from a high-level language to a low-level language. - Relational Algebra - **Query processing** - SQL language - None of the above 2. In SQL query processing, alternatives for evaluating an entire expression tree can be done using. - Materialization - Pipelining - **A and B** - None of the above 3. In SQL query processing evaluation of an expression, **materialization** expression evaluates one relational operation at a time. - Materialization - Pipelining - A and B - None of the above 4. In SQL query processing evaluation of an expression, **pipelining** evaluates each relational operation of the expression simultaneously. - Materialization - **Pipelining** - A and B - None of the above 5. What is the goal of Query processing in DBMS? - To make sure that only the correct SQL query is properly translated into relational algebra - **To find an efficient Query Execution Plan for a given SQL query that would minimize the cost considerably, especially time.** - To maximize and minimize the SQL query execution plan - None of the above 6. In Query processing cost factors are determined by: - Disk access - Read operation - Write operation - **All of the above** 7. The major steps involved in query processing are: - Parser -> Optimizer -> Translation -> Evaluation Engine -> Execution Plan - **Parser -> Transalation -> Optimizer -> Execution Plan -> Evaluation Engine** - All of the above - None of the above 8. The **parser** of the query processor module checks the syntax of the query, the user's privileges to execute the query, the table names and attribute names, etc. - Evaluation Engine - Optimzer - Translator - **Parser** 9. The correct table names, attribute names and the privilege of the users can be taken from the system catalog. - **True** - False - Maybe - I don't know 10. The correct table names, attribute names and the privilege of the users can be taken from the system catalog, which is also known as **data dictionary**. - Evaluation Engine - Optimizer - **Data dictionary** - Parser 11. Translation in Query process is **process of translating the high level SQL query into low level relational algebra**. - Process of translating the low level SQL query into high level relational algebra - **Process of translating the high level SQL query into low level relational algebra** - It is the process of creating the execution plan - It is done by the evaluation engine. And it translate the relational algebra into the equivalent SQL query for processing 12. **Optimizer** uses the statistical data stored as part of data dictionary. - Evaluation Engine - Parser - Data dictionary - **Optimizer** 13. The statistical data are information about the size of the table, the length of records, the indexes created on the table, etc. - **True** - False - Maybe - I don't know 14. Optimizer also checks for the conditions and conditional attributes which are parts of the query. - **True** - False - Maybe - I don't know 15. The following are the ways a query can be optimized. - Analyze and transform equivalent relational expressions - Using different algorithms for each operation - **All of the above** - None of the above 16. Alternatives for evaluating an entire expression tree are **materialization and pipelining**. - Evaluation and translation - Evaluation and optimization - Evaluation and parsing - **Materialization and pipelining** 17. In this method, the given expression evaluates one relational operation at a time. Also, each operation is evaluated in an appropriate sequence or order. - Pipelining - **Materialization** - All of the above - None of the above 18. In this approach, after evaluating one operation, its output is passed on to the next operation, and the chain continues till all the relational operations are evaluated thoroughly. - **Pipelining** - Materialization - All of the above - None of the above 19. There is no requirement of storing a temporary relation in **pipelining**. - Pipelining - Materialization - All of the above - None of the above 20. There are two types of pipelining: **demand driven and producer driven**. - Demand driven and producer driven - Big pipeline and small pipeline - Demand driven and lazy evaluation - Producer driven and eager pipelining 21. In **demand driven or lazy evaluation** the result of lower level queries are not passed to the higher level automatically. It will be passed to higher level only when it is requested by the higher level. In this method, it retains the result value and state with it and it will be transferred to the next level only when it is requested. - Demand Driven or Lazy evaluation - Producer Driven or Eager Pipelining - Materialization - All of the above 22. In **producer driven or eager pipelining** the lower level queries eagerly pass the results to higher level queries. It does not wait for the higher level queries to request for the results. - Demand Driven or Lazy evaluation - Producer Driven or Eager Pipelining - Materialization - All of the above 23. In **producer driven or eager pipelining** method, lower level query creates a buffer to store the results and the higher level queries pulls the results for its use. If the buffer is full, then the lower level query waits for the higher level query to empty it. - Demand Driven or Lazy evaluation - Producer Driven or Eager Pipelining - Materialization - All of the above 24. Another name for Producer Driven or Eager Pipelining is **push and pull pipelining**. - Push pipelining - Pull pipelining - Push and pull pipelining - Materialization ## Relational Algebra 1. The relational algebra query language is **procedural**. - Analytical - Procedural - Symmetrical - Instrumental 2. How does SQL engine work? - SQL query -> Relational Algebra -> Execution - SQL query -> Relational Algebra -> Optimized Relational Algebra -> Execution - SQL query -> Optimized Relational Algebra -> Relational Algebra -> Execution - **SQL query -> Relational Algebra -> Optimized Relational Algebra -> Execution** 3. Which of the following is a type of relational operation? - Project Operation - Union Operation - Set Difference - **All of the above** 4. Relational algebra allow us to translate declarative SQL query into **precise and optimizable expressions**. - Executional queries - Precise and optimizable expressions - Precise expressions only - Optimizable expressions only 5. Select operation is denoted by **σ**. - σ - R - P - S 6. Projection operation is denoted by **Π**. - σ - R - P - Π 7. Cartesian product operation is denoted by **X**. - σ - X - P - Π 8. Which of the following is derived or auxiliary operator? - Joins - Renaming - Intersection - **All of the above** 9. Which of the following is not derived or auxiliary operator? - Joins - Renaming - Intersection - **None of the above** 10. In relational algebra division is an example of **derived or auxiliary operator**. - Basic operator - Derived or auxiliary operator - All of the above - None of the above 11. In Relational Algebra, queries are performed using **operators**. - Entities - Relationships - Operators - Objects 12. Which of the following is NOT a type of relational operation? - Select Operation - Set Difference - **Set Update** - Cartesian Product 13. Relational Algebra operates on **sets**. - SQL - Sets - Data - Multisets 14. RDBMS use **multisets**. - SQL - Multisets - Data - Sets 15. What does the selection σ operator in relational algebra do? - Convert the SQL to Relational Algebra - Returns cross join of the records - **Returns all tuple that satisfy a condition** - Return attributes not in the condition 16. The notation of the selection σ operator is denoted by **σ_C(R)**. - σ_C - X + σ(R) - σ(R) - σ_C(R) 17. What the basic algebra operator "Projection Π" do? - Convert the SQL to Relational Algebra - **Eliminate columns then remove duplicates** - Returns all tuple that satisfy a condition - Return attributes not in the condition 18. The notation of the Projection Π operator is denoted by **Π_A1,...An(R)**. - Π_A1 - Π + (R) - Π(R) - Π_A1,...An(R) 19. `SELECT * from STUDENTS WHERE gpa > 2;` **σ_gpa>2(students)** 20. `SELECT DISTINCT gpa from STUDENTS;` **Π_gpa(students)** 21. `Π<sub>sname,gpa</sub>(σ<sub>gpa>3.5</sub>(Students))` **Select sname, gpa from students where gpa > 3.5;** 22. `Students × People` **Select * from STUDENTS, PEOPLE;** 23. `Students⋈People` **SELECT DISTINCT ssid, S.name, gpa, ssn, address FROM Students S, People P WHERE S.name = P.name;** 24. `P<sub>studId, name, gradePtAvg</sub>(Students)` **SELECT Sid AS studId, sname AS name, gpa AS gradePtAvg FROM Students;** 25. `σ<sub>A=5</sub>(Π<sub>A</sub>(R))` **The relational algebra expression below is equivalent to: σ_A=5(Π_A(R)) =** 26. If there are 2 types of tuples, A & B, the **union** operation contains all the tuples that are either in A or B or both in A & B. - Project - Select - Union - Rename 27. Union operation eliminates the **duplicate** tuples. - Simple - Single - Duplicate - NULL 28. Union operation is denoted by **U**. - U - ^ - * - ! 29. The following conditions must be met by a union operation. - There must be a common attribute between A and B. - A duplicate tuple is automatically discarded. - **Both A. and B.** - None of the above 30. If all the tuples are there in the given two tuples, what operation is it called? - Union - Select - Rename - **Set Intersection** 31. What does Set Intersection Operation denote as? - U - **∩** - Π - ⊥ 32. What does Set Difference Operation denote as? - + - **-** - U - n 33. In **cartesian products**, the rows in one table are combined with the rows in another table. - Cross - Cartesian - Both A and B - None of the above 34. Renaming the output relation is done via the **rename** operation. - Update - Alter - Rename - Reverse 35. Renaming the output relation is done via the **rename** operation. - Update - Alter - Rename - Reverse 36. Which of the following relational algebra query/queries computes/compute the name of sailors who have reserve boat 103? - Both Q1 and Q3 - Both Q 2 and Q3 - **Only Q3** - Only Q2 ## Normalization 1. A properly normalized database should have the following characteristics except **repeating values and groups**. - Minimal redundancy. - Minimal use of null values. - Minimal loss of information - Repeating values and groups 2. INF, 2NF, 3NF, and 4NF are example of various levels of normalization (NF = Normal Form). Other examples of various levels of normalization include all the following except **integrity normal form**. - Integrity Normal Form - Boyce-Codd Normal Form - 5NF - Domain Key Normal Form 3. A relation is in 2NF if **all of the above**. - It is in 1NF - Non-key attributes are fully functional dependent on the primary key - All of the above - None of the above 4. If one set of attributes in a table determines another set of attributes in the table, then the second set of attributes is said to be functionally dependent on the first set of attributes. - False - **True** - Not sure - I don't know 5. A table that has no partial functional dependencies is in **2NF** form. - INF - 2NF - 3NF - BCNF 6. When no attribute is transitively dependent on the primary key, a table is said to be in **3NF** form. - INF - 2NF - 3NF - 4NF 7. **Boyce-Codd Normal Form (BCNF)** form does not allow dependencies between attributes that belong to candidate keys. - Boyce-Codd Normal Form (BCNF) - 3NF - 4NF - 5NF 8. Third normal form (3NF) and BCNF are not same if the following conditions are true: - The table has two or more candidate keys - At least two of the candidate keys are composed of more than one attribute - The keys are not disjoint - **All of the above** ## Table Product - Product ID: 1, 2, 3, 4, 5 - Color: `red, green`, `yellow`, `green`, `yellow, blue`, `red` - Price: 15.99, 23.99, 17.50, 9.99, 29.99 1. Which of the following is not true about the Table_Product table above? - There is function dependency between Product ID -> Price - **All values are scalar** - There is function dependency between Product ID -> Price - It is not in 1NF 2. The 2NF of the Table_Product table would be **all of the above**. ## Big Student Table - `Author ID`, `A_name`, `A_Phone`, `A_City` - `A Country`, `Editor ID`, `E_Name`, `Paper ID`, `P_Title` 1. What are the possible full functional dependencies in the table? 2. What are the possible partial dependencies in the table? 3. What are the possible transitive dependencies in the table? 4. The 2NF of the Big_Student_Table will give **1 table**. 5. The 3NF of the Big_Student_Table will give **5 tables**. ## Indexing and Hashing 1. When a database structure has a huge number of index values, it is very **inefficient** to search all of them for the desired information. - Sufficient - Efficient - Inefficient - None 2. By using the **hashing** algorithm, it is possible to find a data record directly on the disk without the need for an index. - Hashing - Static Hashing - Dynamic Hashing - None 3. A hashing function is used to generate the **addresses** of the data blocks in this technique. - Data - Addresses - Numbers - Records 4. **A data block** is a memory location where these records are stored. - Data bucket - Data block - Both A and B - None of the above 5. Any value in the column can be used by a **hash** function to generate the address. - Log - Data - Hash - Heap 6. To generate the address of a data block, the hash function most often uses the **primary** key. - Primary - Foreign - Composite - Alternate 7. The primary key may even be considered to be the address of a **data block**. - Data Address - Data Set - Data Block - Data Item 8. The data block will consist of every row whose address is the same as its **primary** key. - Alternate - Composite - Foreign - Primary 9. How many types of hashing are there? - 2 - 3 - 4 - 5 10. Which of the following is the type of hashing? - Static - Dynamic - **Both A and B** - None of the above 11. A technique for direct search is **hashing**. - Binary Search - Linear Search - Tree Search - Hashing 12. The following is a possible state of the hash table with a hash function (3x + 4)mod7 where the sequence 1, 3, 8, 10 is inserted into the table using closed hashing. - 8, 10, _, _, _, _, _, _, _, _ - **1, 8, 10, 3, _, _, _, _, _, _** - 1, 3, _, _, _, _, _, _, _, _ - 1, 10, 8, 3, _, _, _, _, _, _ 13. The order in which the elements 34, 16, 2, 93, 80, 77, 51 are inserted in the hash table when the table size is 10 is **80, 51, 2, 93, 34, null, 16, 77, null, null** - null, null, 77, 16, null, 34, 93, 2, 51, 80 - 77, 16, 34, 93, 2, 51, 80 - 80, 51, 2, 93, 34, null, 16, 77, null, null - 80, 51, 2, 93, 34, 16, 77 14. The following is the hash table state when the keys 15, 2, 1, 5, 20, 31, 12, 21, 17, 34 are inserted in the order: 15, 2, 1, 5, 20, 31, 12, 21, 17, 34 using linear probing and a hash function (k) = k mod 10. - 20, 31, 2, 1, 12, 15, 5, 17, 21, 34 - 20, 12, 31, 34, 5, 15, 21, 17, 2, _ - **20, 12, 31, 12, 15, 5, 21, 17, 34, _** - 20, 31, 2, 1, 34, 15, 5, 21, 17, 12 15. If the hash function is h(element) = element % 10, so for which of the pair of values, the collision will take place? - 15 and 14 - **12 and 2** - 1 and 10 - 11 and 22 16. Given the following input (4322, 1334, 1471, 9679, 1989, 6171, 6173, 4199) and the hash function x mod 10, **i and ii only** of the following statements are true. - i only - ii only - i and ii only - iii or iv 17. The keys 12, 18, 13, 2, 3, 23, 5 and 15 are inserted into an initially empty hash table of length 10 using open addressing with hash function h(k) = k mod 10 and linear probing. The following is the resulting hash table. - 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 - 1, 2, 23, 13, _, 15, _, 18, _, _ - 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 - 1, 12, 13, _, _, 15, 23, 18, _, _ - **0, 1, 2, 3, 4, 5, 6, 7, 8, 9 - 1, 12, 13, 3, 23, 5, 15, 18, _, _** - 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 - 1, 12, 13, 3, 23, 5, 15, 18, _, _ 18. A hash table of length 10 uses open addressing with hash function h(k)=k mod 10, and linear probing. After inserting 6 values into an empty hash table, the table is as shown below. - 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 - 42, 23, 34, 52, 46, 33, _, _, _, _ The following is a possible order in which the key values could have been inserted in the table. - 46, 42, 34, 52, 23, 33 - 34, 42, 23, 52, 33, 46 - **46, 34, 42, 23, 52, 33** - 42, 46, 33, 23, 34, 52 ==End of OCR for page 66==