SQL Server: Database Developer (T-SQL) Interview Questions and Answers (Day-1)

What do you know about the Hierarchical Data Model?

The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments. This structure implies that a record can have repeating information, generally in the child data segments. Data in a series of records, which have a set of field values attached to it.

It collects all the instances of a specific record together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. To create links between these record types, the hierarchical model uses Parent Child Relationships.

What do you know about the Network Model?

The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data.

The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set.

What do you know about the Relational Model?

(RDBMS – relational database management system) A database based on the relational model developed by E.F. Codd. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organised in tables. A table is a collection of records and each record in a table contains the same fields.

What are the basic properties of Relational Tables?

  • Values Are Atomic
  • Each Row is Unique
  • Column Values Are of the Same Kind
  • The Sequence of Columns is Insignificant
  • The Sequence of Rows is Insignificant
  • Each Column Has a Unique Name

What do you know about the Object Relational Data Model?

  • Object/relational database management systems (ORDBMSs) add new object storage capabilities to the relational systems at the core of modern information systems.
  • These new facilities integrate management of traditional fielded data, complex objects such as time-series and geospatial data and diverse binary media such as audio, video, images, and applets.
  • By encapsulating methods with data structures, an ORDBMS server can execute comple x analytical and data manipulation operations to search and transform multimedia and other complex objects.

What do you know about the Object-Oriented Model?

  • Object DBMSs add database functionality to object programming languages. They bring much more than persistent storage of programming language objects.
  • Object DBMSs extend the semantics of the C++, Smalltalk and Java object programming languages to provide full-featured database programming capability, while retaining native language compatibility.
  • A major benefit of this approach is the unification of the application and database development into a seamless data model and language environment.

Would you please tell us some of the Dr Edgar F. Codd rules of RDBMS?

  • Rule 1: Information Rule
  • Rule 2: Guaranteed Access Rule
  • Rule 3: Systematic Treatment of NULL Values
  • Rule 4: Active Online Catalog
  • Rule 5: Comprehensive Data Sub-Language Rule
  • Rule 6: View Updating Rule
  • Rule 7: High-Level Insert, Update, and Delete Rule
  • Rule 8: Physical Data Independence
  • Rule 9: Logical Data Independence
  • Rule 10: Integrity Independence
  • Rule 11: Distribution Independence
  • Rule 12: Non-Subversion Rule

What do you know about the E-R Diagram and As a Database Developer, have you ever designed E-R Diagram for Database System?

An entity-relationship diagram (ERD) is a data modeling technique that graphically illustrates an information system’s entities and the relationships between those entities. An ERD is a conceptual and representational model of data used to represent the entity framework infrastructure.

The elements of an ERD are:

  • Entities
  • Relationships
  • Attributes

What do you know about the Logical Data Independence and Physical Data Independence?

Logical Data Independence:

Logical data is data about database, that is, it stores information about how data is managed inside. For example, a table (relation) stored in the database and all its constraints, applied on that relation.

Logical data independence is a kind of mechanism, which liberalizes itself from actual data stored on the disk. If we do some changes on table format, it should not change the data residing on the disk.

Physical Data Independence:

All the schemas are logical, and the actual data is stored in bit format on the disk. Physical data independence is the power to change the physical data without impacting the schema or logical data.

For example, in case we want to change or upgrade the storage system itself − suppose we want to replace hard-disks with SSD it should not have any impact on the logical data or schemas.

What is the Logical Processing Order of the SELECT statement?

  • FROM
  • ON
  • JOIN
  • TOP

What is OLTP (Online Transaction Processing)?

In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

What is OLAP (Online Analytical Processing)?

OLAP is an acronym for Online Analytical Processing. OLAP performs multidimensional analysis of business data and provides the capability for complex calculations, trend analysis, and sophisticated data modeling.

What is the main difference between Database Normalization and Database Denormalization?

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. 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 using the defined relationships.

Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.In some cases, denormalization is a means of addressing performance or scalability in relational database software.

How to Remove Trailing Character from Number Column?

SQL Server: Script to Remove Trailing Character from Number Column

Would you please write a script to First Sunday of Next Month using EOMONTH function of SQL Server 2012?

SQL Server 2012: Find First Sunday of Next Month using EOMONTH

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of