DBMS - Database Management System, questions and answers

Database Collection of Data Management System a set of programs to store and retrieve data. DBMS (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? Store Data in an optimized and systematic manner and Retrieve Data Fast What are some Examples Applications where we use Database Management Systems are: Telecom Industry Banking Education sector Online shopping Advantages of DBMS over a FILE System no redundant data Data consistency and Integrity Secure Privacy Easy access to data Easy recovery Flexible Disadvantages of DBMS over FILE System Higher implementation Cost Complexity Performance for some applications What must you have to understand the view of data? Data Abstraction Instance and Schema What is data abstraction? The process of hiding irrelevant details from user What are the 3 levels of Data abstraction? Physical Logical View Physical abstraction level 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. records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory. Logical abstraction level - Middle level - Describes what data is stored in database - Developers and admins access this level. - records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented. View abstraction level - Highest level of data abstraction. - Describes the user interaction with database system. user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them. Database Instance The data stored in database at a particular moment of time Database vs Instance Database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. Database Schema Design of a database. Defines the variable declarations in tables that belong to a particular database; 3 Schema levels Physical Logical View keys in DBMS Used for identifying unique rows from table. Establishes relationship among tables. Types of keys Primary Super Candidate Alternate Composite Foreign Primary Key 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 a set ofone or more columns (attributes) to uniquely identify rows in a table. Candidate key minimal super keys with no redundant attributes. non-prime attribute An attribute that is not part of any candidate key prime attribute An attribute that is a part of one of the candidate keys is known as prime attribute. Super Key vs Candidate Key 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 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 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 Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternative or secondary keys. Normalization a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly Anomalies problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table 3 types of Anomalies in un-normalized db Insertion Update Delete Update anomaly Example: In a table we have two rows for an employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in 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: 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: 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? Normalize Data Common normal forms First normal form (1NF) Second normal form (2NF) Third normal form(3NF) Boyce & Codd normal form (BCNF) First normal form (1NF) 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) 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) Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. Example of 3NF 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) 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 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 A logical structure of Database that describes the design of database to reflect entities, attributes, relationship among data, constrains etc. Types of Data Models Object Based Record Based Object Based logical models Describe Data at the conceptual and view levels. E-R Model Object Oriented Model Physical Data Models describe data at the lowest level of abstraction. E-R Data Model (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. Record based logical Models 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 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.