Download Database Management Systems (DBMS): Concepts, Types, and Applications and more Exams Nursing in PDF only on Docsity!
DBMS - Database Management System Database Right Ans - Collection of Data Management System Right Ans - a set of programs to store and retrieve data. DBMS Right Ans - (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? Right Ans - Store Data in an optimized and systematic manner and Retrieve Data Fast What are some Examples Applications where we use Database Management Systems are: Right Ans - Telecom Industry Banking Education sector Online shopping Advantages of DBMS over a FILE System Right Ans - no redundant data
Data consistency and Integrity Secure Privacy Easy access to data Easy recovery Flexible Disadvantages of DBMS over FILE System Right Ans - Higher implementation Cost Complexity Performance for some applications What must you have to understand the view of data? Right Ans - Data Abstraction Instance and Schema What is data abstraction? Right Ans - The process of hiding irrelevant details from user What are the 3 levels of Data abstraction? Right Ans - Physical Logical View
An instance is the software (and memory) that Oracle uses to manipulate the data in the database. Database Schema Right Ans - Design of a database. Defines the variable declarations in tables that belong to a particular database; 3 Schema levels Right Ans - Physical Logical View keys in DBMS Right Ans - Used for identifying unique rows from table. Establishes relationship among tables. Types of keys Right Ans - Primary Super Candidate Alternate Composite Foreign
Primary Key Right Ans - 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 Right Ans - a set ofone or more columns (attributes) to uniquely identify rows in a table. Candidate key Right Ans - minimal super keys with no redundant attributes. non-prime attribute Right Ans - An attribute that is not part of any candidate key prime attribute Right Ans - An attribute that is a part of one of the candidate keys is known as prime attribute. Super Key vs Candidate Key Right Ans - 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 Right Ans - The columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data. Insert anomaly Example: Right Ans - Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if emp_dept field doesn't allow nulls. Delete anomaly Example: Right Ans - Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department. How do you overcome anomalies? Right Ans - Normalize Data Common normal forms Right Ans - First normal form (1NF) Second normal form (2NF) Third normal form(3NF) Boyce & Codd normal form (BCNF) First normal form (1NF) Right Ans - 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) Right Ans - 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) Right Ans - Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. Example of 3NF Right Ans - 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) Right Ans - 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 Right Ans - each functional dependency X-> Y at least one of the following conditions hold: X is a super key of table
Record based logical Models Right Ans - describe data at the conceptual and view levels. These models specify logical structure of database with records, fields and attributes. Relational Model Hierarchical Model Network Model Relational Model Right Ans - The data and relationships are represented by collection of inter-related tables. Each table is a group of column and rows, where column represents attribute of an entity and rows represents records. Hierarchical Model Right Ans - Data is organized into a tree like structure with each record is having one parent record and many children. The main drawback of this model is that, it can have only one to many relationships between nodes. Network Model Right Ans - data is organized into a graph-like structure Allows each record to have more than one parent record. DBMS languages Right Ans - Languages used for read, update and store data in a database. ie. SQL SQL Right Ans - Structured Query Language a dbms language
Types of DBMS languages Right Ans - DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control language) DDL (Data Definition Language) Right Ans - Used for specifying the database schema CREATE ALTER DROP TRUNCATE RENAME DML (Data Manipulation Language) Right Ans - Used for accessing and manipulating data in a database. SELECT INSERT UPDATE DELETE DCL (Data Control language) Right Ans - Used for granting and revoking user access on a database.
{emp_id, emp_name} -> emp_name [emp_name is a subset of {emp_id, emp_name}] non-trivial functional dependency Right Ans - 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. The following functional dependencies are non-trivial: emp_id -> emp_name (emp_name is not a subset of emp_id) emp_id -> emp_address (emp_address is not a subset of emp_id) On the other hand, the following dependencies are trivial: {emp_id, emp_name} -> emp_name [emp_name is a subset of {emp_id, emp_name}] Multivalued dependency Right Ans - occurs when there are more than one independent multivalued attributes in a table. Here columns manuf_year and color are independent of each other and dependent on bike_model. In this case these two columns are said to be multivalued dependent on bike_model. These dependencies can be represented like this: bike_model ->> manuf_year
bike_model ->> color RDBMS Right Ans - Relational Database Management System has following major components: Table, Record / Tuple, Field & Column /Attribute. Table Right Ans - a collection of data represented in rows and columns. other names for rows Right Ans - records tuples Field Right Ans - name of column Column / Attribute: Right Ans - Each attribute and its values are known as attributes in a database. Cardinality in context of Data models Right Ans - The relationship between two tables. One to One One to Many Many to One
Durability Atomicity Right Ans - 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 Right Ans - 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$ balance, in that case the final balance of A would be 300$ instead of 200$. This is wrong. If the transaction were to run in isolation then the second transaction would have read the correct balance 300$ (before debiting 100$) once the first transaction went successful. Isolation Right Ans - For every pair of transactions, one transaction should start execution only when the other finished execution.
Durability Right Ans - Once a transaction completes successfully, the changes it has made into the database should be permanent even if there is a system failure. The recovery-management component of database systems ensures the durability of transaction. Constraints in DBMS Right Ans - enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table. Types of constraints Right Ans - NOT NULL UNIQUE DEFAULT CHECK Key Constraints - PRIMARY KEY, FOREIGN KEY Domain constraints Mapping constraints Domain constraints: Right Ans - Each table has certain set of columns and each column allows a same type of data, based on its data type. The column does not accept values of any other data type. Domain constraints are user defined data type and we can define them like this: Domain Constraint = data type + Constraints (NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT)
No preemption condition Right Ans - 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 Right Ans - 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 Right Ans - Ignore the deadlock (Ostrich algorithm) Deadlock detection Ignore the deadlock (Ostrich algorithm) Right Ans - 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 Right Ans - use a resource scheduler. if there is a deadlock it is known to the resource scheduler. resource scheduler Right Ans - one that keeps the track of resources allocated to and requested by processes. how is a deadlock corrected once it is detected? Right Ans - Terminating processes involved in deadlock Resource Preemption
Terminating processes involved in deadlock Right Ans - Terminating all the processes involved in deadlock or terminating process one by one until deadlock is resolved can be the solutions but both of these approaches are not good. Terminating all processes cost high and partial work done by processes gets lost. Terminating one by one takes lot of time because each time a process is terminated, it needs to check whether the deadlock is resolved or not. Thus, the best approach is considering process age and priority while terminating them during a deadlock condition. Resource Preemption Right Ans - the preemption of resources and allocation of them to the other processes until the deadlock is resolved. Deadlock prevention Right Ans - Removing mutual exclusion Removing hold and wait condition Preemption of resources Avoid circular wait condition Removing mutual exclusion Right Ans - All resources must be sharable that means at a time more than one processes can get a hold of the resources. That approach is practically impossible. Removing hold and wait condition Right Ans - This can be removed if the process acquires all the resources that are needed before starting out. Another way to remove this to enforce a rule of requesting resource when there are none in held by the process.
