Babu's Study Deck Flashcards
getting a developer job
What is a RDBMS?
Database management systems that maintain data records and indices in tables. A program that allows you to Create, Read, Update, and Delete a relational database.
What are the properties of the relational tables?
- Values are atomic
- Column values are of the same kind
- Each row is unique
- The sequence of columns is insignificant
- The sequence of rows is insignificant
- Each column must have a unique name
What is normalization?
The process of organizing data to minimize redundancy. It usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What is de-normalization?
The process of attempting to optimize the performance of a database by adding redundant data. It is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. It eliminates redundant data (storing the same data in more than one table) and it ensures data dependencies make sense.
How is ACID property related to database?
ACID properties help to maintain consistency in a database, before and after a transaction. The ACID properties, in totality, provide a mechanism to ensure correctness and consistency of a database in a way such that each transaction is a group of operations that acts as a single unit, produces consistent results, acts in isolation from other operations and updates that it makes are durably stored.
What is the “A” in ACID?
Atomicity the entire transaction takes place at once or doesn’t happen at all. It involves two operations (1) Abort: if a transaction aborts, changes made to database are not visible (2) Commit: if a transaction commits, changes made are visible. Atomicity is as known as the ‘All or nothing rule’.
What is the “C” in ACID?
Consistency integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to correctness of a database.
What is the “I” in ACID?
Isolation ensures that multiple transactions can occur concurrently without leading to inconsistency of database state. Transactions occur independently without interference. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved if these were executed serially in some order.
What is the “D” in ACID?
Durability ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if system failure occurs. These updates now become permanent and are stored in a non-volatile memory.
What are the different normalization forms?
- 1NF: Eliminate Repeating Groups
- 2NF: Eliminate Redundant Data
- 3NF: Eliminate Columns Not Dependent On Key
- BCNF: Boyce-Codd Normal Form
- 4NF: Isolate Independent Multiple Relationships
- 5NF: Isolate Semantically Related Multiple Relationships
- ONF: Optimal Normal Form
- DKNF: Domain-Key Normal Form
What is 1NF?
Eliminate repeating groups. Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
- It should only have single (atomic) valued attributes/columns
- Values stored in a column should be of the same domain
- All columns in a table should have unique names
- The order in which data is stored does not matter
What is 2NF?
Eliminate redundant data. If an attribute depends on only part of a multi-valued key, then remove it to a separate table. For a table to be in the Second Normal Form:
- It should be in the First Normal Form
- It should not have Partial Dependency
What is 3NF?
Eliminate columns not dependent on key. If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database. A table is in Third Normal Form when:
- It is in Second Normal Form
- It does not have Transitive Dependency
What is BCNF?
Boyce-Codd Normal Form is a higher version of the Third Normal Form and deals with certain type of anomaly that is not handled by 3NF. If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.
- R must be in 3rd Normal Form
- Each functional dependency (X → Y), X should be a super key
What is 4NF?
Isolate independent multiple relationships. No table may contain two or more 1:n or n:m relationships that are not directly related.
- It is in the Boyce-Codd Normal Form
- It doesn’t have Multi-valued Dependency
What is 5NF?
Isolate semantically related multiple relationships. There may be practical constrains on information that justify separating logically related many-to-many relationships.
What is ONF?
Optimal Normal Form. A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
What is DKNF?
Domain-key Normal Form. A model free from all modification anomalies is said to be in DKNF.
What is a stored procedure?
A named group of SQL statements that have been previously created and stored in the server database. They reduce network traffic and improve performance and can be used to help ensure the integrity of the database.
What is a trigger?
A SQL procedure that initiates an action when an event (INSERT, DELETE, or UPDATE) occurs. They are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. It cannot be called or executed. You can have nested triggers.
What are the different types of triggers?
1) DML Trigger
a) Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete
b) After Triggers execute following the triggering action, such as an insert, update, or delete
2) DDL Trigger is fired against Drop Table, Create Table, Alter Table, or Login events and are always after triggers.
What is a view?
A virtual table. It’s like a subset of a table and can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. Views do not exist in the database unless you add an index.
What is an index?
A physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. Indexes are used to speed up queries.
What is a linked server?
Is when you add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy-to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data.
What is a cursor?
It is a loop that allows you to do row by row processing. You should avoid using a cursor because it kills performance. The following order must be followed: • Declare cursor • Fetch row from the cursor • Process fetched row • Close cursor • Deallocate cursor
What is collation?
Specifies how data is sorted and compared in a database. It provides the sorting rules, case, and accent sensitivity properties for the data in the database.
What is the difference between a Function and a Stored Procedure?
- Function must return a value but in Stored Procedures it is optional (Procedure can return zero or n values)
- Functions can have only input parameters for it whereas Procedures can have input/output parameters
- Functions can be called from Procedures whereas Procedures cannot be called from Function
What is a subquery?
It is a SELECT statement that is nested within another T-SQL statement. A subquery can be used anywhere an expression can be used.
Explain the properties of a subquery.
- Must be enclosed in the parenthesis
- Must be put in the right hand of the comparison operator
- Cannot contain an ORDER BY clause
- Can contain more than one subquery
What are the different types of JOINS?
- Cross JOIN
- Inner JOIN
- Outer JOIN
- Self-JOIN
What is a Cross Join?
- Cross Join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join
- The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table
What is an Inner JOIN?
Displays only the rows that have a match in both joined tables and is the default type of JOIN in the Query and View Designer.
What is an Outer JOIN?
It includes rows even if they do not have related rows in the joined table. There are three types of Outer JOINS:
• Left Outer JOIN all the rows in the first-named table which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.
• Right Outer JOIN all the rows in the second-named table which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
• Full Outer JOIN all the rows in all joined tables are included, whether they are matched or not.
What is a Self-JOIN?
When one table joins to itself with one or two aliases to avoid confusion. Can be of any type, as long as the joined tables are the same. It is unique in that it involves a relationship with only one table. It can also be an Outer JOIN or an Inner JOIN.
What are Primary Keys and Foreign Keys?
Primary keys are the unique identifiers for each row and must contain unique values which cannot be null. A table can have only one Primary key. Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.
What is User-defined Functions?
Allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
What are the types of User-defined Functions that can be created?
- Scalar User-defined Function returns one of the scalar data types. Data types not supported are: text, ntext, image, and timestamp
- Inline Table-value User-defined Function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL SELECT command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
- Multi-statement Table-value User-defined Function returns a table, and it is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.
What is an identity?
A special type of column that is used to automatically generate key values based on a provided seed (starting point) and increment.
What is data-warehousing?
- The data in the database is organized so that all the data elements relating to the same real-world event or object are linked together
- Changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time (time-variant)
- Non-volatile – the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting
- Integrated – the database contains data from most or all of an organization’s operational applications, and that this data is made consistent
What languages does BI use to achieve the goal?
- MDX – Multidimensional Expressions retrieves data from SSAS cubes
- DMX – Data Mining Extensions used for data mining structures.
- XMLA – XML for Analysis commonly used in administration tasks such as backup or restore database, copy and move database, or for learning Meta data information.
What is a Standby Server?
It is a computer that is located in close proximity to the production server(s) and can be used to temporarily replace a production server if it experiences a hardware failure. It can also be used to verify that you can recover databases from their full backups.
Explain Hot Standby Server.
A redundant method in which one system runs simultaneously with an identical primary system. Upon failure of the primary system, the hot standby system immediately takes over, replacing the primary system. The data is mirrored in real time.
