Download DBMS: Database Management System - Complete Solutions and more Exams Advanced Education in PDF only on Docsity!
DBMS - Database Management System
with complete solutions
Database - correct answer ✔✔Collection of Data Management System - correct answer ✔✔a set of programs to store and retrieve data. DBMS - correct answer ✔✔(Database Management System) A collection of inter-related data that contains programs to store & access that data in an easy and effective manner. What is the need of DBMS? - correct answer ✔✔Store Data in an optimized and systematic manner and Retrieve Data Fast What are some Examples Applications where we use Database Management Systems are: - correct answer ✔✔Telecom Industry Banking Education sector Online shopping
Advantages of DBMS over a FILE System - correct answer ✔✔no redundant data Data consistency and Integrity Secure Privacy Easy access to data Easy recovery Flexible Disadvantages of DBMS over FILE System - correct answer ✔✔Higher implementation Cost Complexity Performance for some applications What must you have to understand the view of data? - correct answer ✔✔Data Abstraction Instance and Schema What is data abstraction? - correct answer ✔✔The process of hiding irrelevant details from user What are the 3 levels of Data abstraction? - correct answer ✔✔Physical Logical View Physical abstraction level - correct answer ✔✔This is the lowest level of data abstraction. It describes how data is actually stored in database. You can get the complex data structure details at this level.
3 Schema levels - correct answer ✔✔Physical Logical View keys in DBMS - correct answer ✔✔Used for identifying unique rows from table. Establishes relationship among tables. Types of keys - correct answer ✔✔Primary Super Candidate Alternate Composite Foreign Primary Key - correct answer ✔✔a column or set of columns in a table that uniquely identifies tuples (rows) in that table. -can not contain duplicate values or nulls Super Key - correct answer ✔✔a set ofone or more columns (attributes) to uniquely identify rows in a table. Candidate key - correct answer ✔✔minimal super keys with no redundant attributes.
non-prime attribute - correct answer ✔✔An attribute that is not part of any candidate key prime attribute - correct answer ✔✔An attribute that is a part of one of the candidate keys is known as prime attribute. Super Key vs Candidate Key - correct answer ✔✔Candidate keys are selected from the set of super keys. they do not have any redundant attributes. That's the reason they are also termed as minimal super key. Foreign Key - correct answer ✔✔The columns of a table that points to the primary key of another table. They act as a cross-reference between tables. Foreign keys are the columns of a table that points to the candidate key of another table. Composite Key - correct answer ✔✔A key that consists of more than one attribute to uniquely identify rows in a table is called composite key. It is also known as compound key. Alternate key - correct answer ✔✔Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternative or secondary keys. Normalization - correct answer ✔✔a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly
Boyce & Codd normal form (BCNF) First normal form (1NF) - correct answer ✔✔an attribute (column) of a table cannot hold multiple values. It should hold only atomic values. a single row for each unique value Second normal form (2NF) - correct answer ✔✔Table is in 1NF (First normal form) No non-prime attribute is dependent on the proper subset of any candidate key of table. break column into a new table instead of having to update it twice if there is more then one record. Ie. add details table for age, height, etc. Third normal form(3NF) - correct answer ✔✔Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. Example of 3NF - correct answer ✔✔Break emp_zip, emp_state, emp_city and emp_district out of Employee table into Employee_Zip table so that they are not all dependent on employee_id. Then just have emp_zip in employee table Boyce & Codd normal form (BCNF) - correct answer ✔✔Advanced version of 3NF that's why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.
Transitive functional dependency - correct answer ✔✔each functional dependency X-> Y at least one of the following conditions hold: X is a super key of table Y is a prime attribute of table -can only occur in a relation of three of more attributes. Data Model - correct answer ✔✔A logical structure of Database that describes the design of database to reflect entities, attributes, relationship among data, constrains etc. Types of Data Models - correct answer ✔✔Object Based Record Based Object Based logical models - correct answer ✔✔Describe Data at the conceptual and view levels. E-R Model Object Oriented Model Physical Data Models - correct answer ✔✔describe data at the lowest level of abstraction. E-R Data Model - correct answer ✔✔(Entity-Relationship model) a systematic way of describing and defining a business process. An ER model is typically implemented as a database. The main components of E-R model are: entity set and relationship set.
DML (Data Manipulation Language) DCL (Data Control language) DDL (Data Definition Language) - correct answer ✔✔Used for specifying the database schema CREATE ALTER DROP TRUNCATE RENAME DML (Data Manipulation Language) - correct answer ✔✔Used for accessing and manipulating data in a database. SELECT INSERT UPDATE DELETE DCL (Data Control language) - correct answer ✔✔Used for granting and revoking user access on a database. GRANT REVOKE
Functional dependency in DBMS - correct answer ✔✔The attributes of a table is said to be dependent on each other when an attribute of a table uniquely identifies another attribute of the same table. If column A of a table uniquely identifies the column B of same table then it can represented as A->B (Attribute B is functionally dependent on attribute A) Types of Functional Dependencies - correct answer ✔✔Trivial functional dependency non-trivial functional dependency Multivalued dependency Transitive dependency Trivial functional dependency - correct answer ✔✔The dependency of an attribute on a set of attributes Symbolically: A ->B is trivial functional dependency if B is a subset of A. The following dependencies are also trivial: A->A & B->B the following dependencies are trivial: {emp_id, emp_name} -> emp_name [emp_name is a subset of {emp_id, emp_name}] non-trivial functional dependency - correct answer ✔✔If a functional dependency X->Y holds true where Y is not a subset of X then this dependency is called non trivial Functional dependency. An employee table with three attributes: emp_id, emp_name, emp_address.
tuples Field - correct answer ✔✔name of column Column / Attribute: - correct answer ✔✔Each attribute and its values are known as attributes in a database. Cardinality in context of Data models - correct answer ✔✔The relationship between two tables. One to One One to Many Many to One Many to Many One to One - correct answer ✔✔A single row of table 1 associates with single row of table 2 One to Many - correct answer ✔✔A single row of table 1 associates with more than one rows of table 2 Many to One - correct answer ✔✔Many rows of table 1 associate with a single row of table 2 Many to Many - correct answer ✔✔Many rows of table 1 associate with many rows of table 2 Cardinality In Context of Query Optimization - correct answer ✔✔Refers to the uniqueness of a column in a table. The column with all unique values
would be having the high cardinality and the column with all duplicate values would be having the low cardinality. These cardinality scores helps in query optimization. ACID properties in DBMS - correct answer ✔✔To ensure the integrity of data during a transaction, the database system maintains the following properties. Atomicity Consistency Isolation Durability Atomicity - correct answer ✔✔This property ensures that either all the operations of a transaction reflect in database or none. Suppose Account A has a balance of 400$ & B has 700$. Account A is transferring 100$ to Account B. This is a transaction that has two operations a) Debiting 100$ from A's balance b) Creating 100$ to B's balance. Let's say first operation passed successfully while second failed, in this case A's balance would be 300$ while B would be having 700$ instead of 800$. This is unacceptable in a banking system. Either the transaction should fail without executing any of the operation or it should process both the operations. Consistency - correct answer ✔✔To preserve the consistency of database, the execution of transaction should take place in isolation (that means no other transaction should run concurrently when there is a transaction already running). For example account A is having a balance of 400$ and it is transferring 100$ to account B & C both. So we have two transactions here. Let's say these transactions run concurrently and both the transactions read 400$
Domain Constraint = data type + Constraints (NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT) Mapping constraints: - correct answer ✔✔constraints can be explained in terms of mapping cardinality Mapping Cardinality - correct answer ✔✔One to One: One to Many Many to One Many to Many Deadlock in DBMS - correct answer ✔✔a condition wherein two or more tasks are waiting for each other in order to be finished but none of the task is willing to give up the resources that other task needs. In this situation no task ever gets finished and is in waiting state forever. Coffman conditions - correct answer ✔✔Mutual exclusion condition Hold and wait condition No preemption condition Circular wait condition Mutual exclusion condition - correct answer ✔✔There must be at least one resource that cannot be used by more than one process at a time. Hold and wait condition: - correct answer ✔✔A process that is holding a resource can request for additional resources that are being held by other processes in the system.
No preemption condition - correct answer ✔✔A resource cannot be forcibly taken from a process. Only the process can release a resource that is being held by it. Circular wait condition - correct answer ✔✔A condition where one process is waiting for a resource that is being held by second process and second process is waiting for third process ....so on and the last process is waiting for the first process. Thus making a circular chain of waiting. Deadlock Handling - correct answer ✔✔Ignore the deadlock (Ostrich algorithm) Deadlock detection Ignore the deadlock (Ostrich algorithm) - correct answer ✔✔When it is believed that deadlocks are very rare and cost of deadlock handling is higher, in that case ignoring is better solution than handling it. For example: Let's take the operating system example - If the time requires handling the deadlock is higher than the time requires rebooting the windows then rebooting would be a preferred choice considering that deadlocks are very rare in windows. Deadlock detection - correct answer ✔✔use a resource scheduler. if there is a deadlock it is known to the resource scheduler. resource scheduler - correct answer ✔✔one that keeps the track of resources allocated to and requested by processes. how is a deadlock corrected once it is detected? - correct answer ✔✔Terminating processes involved in deadlock Resource Preemption
in increasing order of precedence. This avoid circular wait. Another way of doing this to force one resource per process rule - A process can request for a resource once it releases the resource currently being held by it. This avoids the circular wait. two algorithms for deadlock avoidance - correct answer ✔✔Wait/Die Wound/Wait What does the Wait/Die algorithm do when an Older process needs a resource held by a younger process? - correct answer ✔✔Older process waits. What does Wound/Wait algorithm do when an Older process needs a resource held by a younger process? - correct answer ✔✔Younger process dies What does the Wait/Die algorithm do when a Younger process needs a resource held by older process - correct answer ✔✔Younger process dies What does Wound/Wait algorithm do when a Younger process needs a resource held by older process - correct answer ✔✔Younger process waits
