Database : Understanding Database

1. Database à Tables à Rows & Columns
2. Rows à Tuples
3. Columns àattributes
4. Tables have Constraints,Indexes,Defaults,Customized User data types
5. Declarative referential integrity (DRI) constraints can be added to the tables to make sure that interrelated data in different tables remains consistent.
6. Tables can have indexes similar to those in books that enable rows to be found quickly.
7. Database can have Tables,Stored Procedures,Triggers,Functions,Views.
8. For example, you create a database named MyCompanyDB to manage the data in your company. In the MyCompanyDB database, you create a table that is named Employees to store information about each employee. The table also contains columns that are named EmpId, LastName, FirstName, Dept, and Title. To make sure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table.Because you want to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to theEmployees table for each employee, so you have to also create a stored procedure named AddEmployee. This procedure is customized to accept the data values for a new employee and perform the operation of adding the row to the Employees table. You may need a departmental summary of employees. In this case, you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. 
9. An instance of SQL Server can support many databases. Each database can store either interrelated or unrelated data from other databases.
10. You should not create any user objects, such as tables, views, stored procedures, or triggers, in the master database. The master database contains system-level information used by the instance of SQL Server, such as logon information and configuration option settings.
11. System Databases: Master database,Msdb database,Model database,Resource database,Tempdb database
12. Master database:The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.
13. Msdb database:The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
14. Model database:The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.
15. Resource database:The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such assys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of theResource database with the older version.
16. Tempdb database:The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.Internal objects that are created by the SQL Server 2005 Database Engine, for example, work tables to store intermediate results for spools or sorting. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
17. SQL Server does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Microsoft does not support triggers defined on the system tables, because they might modify the operation of the system.
18. You should not code Transact-SQL statements that directly query the system tables, unless that is the only way to obtain the information that is required by the application. Instead, applications should obtain catalog and system information by using :System catalog views,SQL-SMO ,Windows Management Instrumentation (WMI) interface ,Catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC. 

19. Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. 

20. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

21. The transaction log supports the following operations:

• Recovery of individual transactions.
• Recovery of all incomplete transactions when SQL Server is started.
• Rolling a restored database, file, filegroup, or page forward to the point of failure. 
• Supporting transactional replication.
• Supporting standby-server solutions.

22. The following table defines the database states.

FAQs -II - OOPs

What is software architecture?

Software Architecture is defined to be the rules, heuristics and patterns governing.

What are steps to follow when architecting a solution? 

• Partitioning the problem and the system to be built into discrete pieces
• Techniques used to create interfaces between these pieces
• Techniques used to manage overall structure and flow
• Techniques used to interface the system to its environment
• Appropriate use of development and delivery approaches, techniques and tools.

Why is Architecture Important?

Architecture is important because it:

• Controls complexity
• Enforces best practices
• Gives consistency and uniformity
• Increases predictability
• Enables re-use.

What are various principles to design a class

Five principles that should be followed when designing a class,

•  SRP - The Single Responsibility Principle - A class should have one, and only one, reason to change.
• OCP - The Open Closed Principle - You should be able to extend a classes behavior, without modifying it.
• LSP - The Liskov Substitution Principle- Derived classes must be substitutable for their base classes.
• DIP - The Dependency Inversion Principle- Depend on abstractions, not on concretions.
• ISP - The Interface Segregation Principle- Make fine grained interfaces that are client specific.

What is the best way to design a sytem?

In software world the concept of dividing and conquering is always recommended, if you start analyzing a full system at the start, you will find it harder to manage. So the better approach is to identify the module of the system first and then dig deep in to each module separately to seek out classes.

What are four Pillars of OOP?

 

1. Encapsulation,
2. Abstraction,
3. Inheritance, and
4. Polymorphism

What is Encapsulation?

• Encapsulation is Information Hiding.
• You hide the internal implementation and just expose the contract to the outside world.
• Encapsulation is achieved through Association , Aggregation and Composition.
• Interfaces are best examples of encapsulation.

 

 

Describe Association, Aggregation and Composition

• Association - Is A relationship Animal Cat
• Aggregation - HAS A relationship School Principal
• Composition - Special type of aggregation.Book and Chapters
• If you dispose Chapters , book won't exist.
• Composition is used in Collections. Key -Value pairs.

What is Abstraction?

•  Simplification of a system can be said to be abstraction.
• abstraction is derived from its ability to hide irrelevant details and from the use of names to reference objects.
• It places the emphasis on what an object is or does rather than how it is represented or how it works. Thus, it is the primary means of managing complexity in large programs.

  

What is Polymorphism?

•  Reducing the complexities of a system.
• Polymorphsim is generalization.

 

 

What is abstract class?

• Abstract classes, which declared with the abstract keyword, cannot be instantiated.
•  It can only be used as a super-class for other classes that extend the abstract class.
• Abstract class is the concept and implementation gets completed when it is being realized by a subclass.
• In addition to this a class can inherit only from one abstract class (but a class may implement many interfaces) and must override all its abstract methods/ properties and may override virtual methods/ properties.

 

 

What is an interface?

Interface can be used to define a generic template and then one or more abstract classes to define partial implementations of the interface.

Interfaces just specify the method declaration (implicitly public and abstract) and can contain properties (which are also implicitly public and abstract).

Interface definition begins with the keyword interface. An interface like that of an abstract class cannot be instantiated.

 

 

Difference between Class and Interface?

• Class and an interface are two different types (conceptually).
• Theoretically a class emphasis the idea of encapsulation, while an interface emphasis the idea of abstraction (by suppressing the details of the implementation).
• The two poses a clear separation from one to another.
• Therefore it is very difficult or rather impossible to have an effective meaningful comparison between the two, but it is very useful and also meaningful to have a comparison between an interface and an abstract class.

 

Differences between Interface and abstract class

• Interface definition begins with a keyword interface so it is of type interface
• Abstract classes are declared with the abstract keyword so it is of type class
• Interface has no implementation, but they have to be implemented.
• Abstract class’s methods can have implementations and they have to be extended.
• Interfaces can only have method declaration (implicitly public and abstract) and fields (implicitly public static)
• Abstract class’s methods can’t have implementation only when declared abstract.
• Interface can inherit more than one interfaces
• Abstract class can implement more than one interfaces, but can inherit only one class
• Abstract class must override all abstract method and may override virtual methods
• Interface can be used when the implementation is changing
• Abstract class can be used to provide some default behavior for a base class.
• Interface makes implementation interchangeable
• Interface increase security by hiding the implementation
• Abstract class can be used when implementing framework
• Abstract classes are an excellent way to create planned inheritance hierarchies and also to use as non-leaf classes in class hierarchies.

What is Inheritance?

Ability of a new class to be created, from an existing class by extending it, is called inheritance.

 

What is method Overloading?

The method overloading is the ability to define several methods all with the same name but different signature and return type.

Ex : + ; operator overloading

 

What is method overriding?

Changing the implementation.
Ex : ToString () method overriding

What is an extender class?

An extender class allows you to extend the functionality of an existing control. It is used in Windows forms applications to add properties to controls.

What is implementation and interface inheritance?

When a class (type) is derived from another class(type) such that it inherits all the members of the base type it is Implementation Inheritance.

When a type (class or a struct) inherits only the signatures of the functions from another type it is Interface Inheritance.

In general Classes can be derived from another class, hence support Implementation inheritance. At the same time Classes can also be derived from one or more interfaces. Hence they support Interface inheritance.