Q. What is database or database management systems (DBMS)?
TWIST: - What's the different between file and database? Can files qualify as a database?
NOTE: Probably these questions are too basic for experienced SQL Server guys. But from freshers point of view it can be a difference between getting a job and to be jobless.
Ans: Database provides a systematic and organized ways of storing, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.
OK, let me spend a few sentence more on explaining the third aspect. A simple figure of text file which has personal detail information. The first column of the information is Name, second address and finally the phone number. If you picture a non-Uniform text file which was designed by a programmer for a specific application.
Its work fine in the boundary of the application. Now some years down the line a third party application has to be intergrated with this file, so in order the third party application intergrates properly it has the following option:-
-Use Interface of the original application.
-Understand the complete detail of how the text file is organized, example the first column is Name, then address and finally phone number. After analyzing write a code which can read the file, parse it etc...Hmm lot of work right.
That's what the main difference between a simple file and database; database has independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database but not the independent way of accessing data.
NOTE: Many experienced programmers think that the main different is that the file can not provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where file where the only means of storing data, you can see functionalities like locking, multi-user etc provided very efficiently. So it's matter of debate if some interviewers think this is a main difference betwwen files and database accept it...going into debate is probably loosing a job.
(Just a note for fresher's multi-user capabilities means that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS provides this as built funtionalities but if you are storing information in files it's up to the application to write logic to achieve these fuctionalities)
Q. What's difference between DBMS and RDBMS?
Ans: OK as said before DBMS provides a systematic and organized way of storing, managing, and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say
RDBMS = DBMS + REFERENTIAL INTEGRITY
Example above is that every person should have an address this is referential integrity between "Name" and "Address". If we break this referential integrity in DBMS and File's it will no complain, but RDBMS will not allow you to save this data if you have defined the relation intergrity between person and address. These relations are defined by using "foreign Keys" in any RDBMS.
NOTE: One of the biggest debate, is Microsoft Access a RDBMS? We will answer this question in later section.
Q. (DB) What are CODD rules?
TWIST: Does SQL Server support all the twelve CODD rules?
NOTE: This question can only be asked on two conditions when the interviewer is expecting you to be a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from perspective of SQL Server.
Rule 1: Information rule.
"All information in a relational data base is represented explicity at the logical level and in exactly one way - by values in tables.
Rule 2: Guaranteed access rule.
"Each and every datum(atomic value) in a relational database is guranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."
In flat files we have to parse and know exact location of field values. But if a DBMS is truly RDBMS you can access the value by specifying the table name, field name, for instance Customer.fields ['Customer Name']
SQL Server also sastisies this rule in ADO.NET we can access field information using table name and field names.
Rule 3: Systematic treatment of null values.
"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type."
Rule 4. Dynamic on-line catalog based on the relational model.
"The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."
The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure od the database.
Rule 5. Comprehensive data sub-language Rule.
"A relational system may support several languages and various modes of terminal use (for example, the fill-in-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined sysntax, as character strings and that is someprehensive in supporting all the following items
Data Definition
View Definition
Data Manipulation (interactive and by program)
Integrity Constrainst
Authorization
Transaction boundaries (Begin, commit, and roll back)
SQL Server uses SQL to query and manipulate data which has well-defined syntax and is being accepted as an international standard for RDBMS.
NOTE: According to this rule CODD has only mentioned that some language should be present to support it, but not neccessary that it should be SQL. Before 80's different database vendors where providing there own flavor of sysntaxes until 80 ANSI- SQL came into standardlize this variation between vendors. As ANSI-SQL is quiet limited, every vendor including Microsoft introduced there additional SQL syntaxes in addition to the support of ANSI-SQL. You can see SQL syntaxes varying from vendor to vendor.
Rule 6. View updating Rule.
"All views that are theoritically updatable are also updateable by the system."
In SQL Server not only views can be updated by user, but also by SQL Server itselt.
Rule 7. High-level insert, update, and delete.
"The capablility of handling a base relation or a derived relation as a single operand appliesnot only to the retrieval of data but also to the insertion, update and deletion of data."
SQL Server allows you to update views which in turn affect the base tables.
Rule 8. Physical data independence.
"Apllication programs and terminal activities remain logically unimpaired whenever any changes made in either storage representations or access methods."
Any application program(C#, VB.NET, Vb6, VC ++ etc) Does not need to be aware of where the SQL Server is physically stored or what type of protocal it's using, database connection string encapsulates everything.
Rule 9. Logical data independence.
"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to these tables.
Application programm written in C# or VB.NET does not need to know about any structure changes in SQL Server database example:- adding of new field etc.
Rule 10. Integrity independence.
"Integrity constraints specific to a particular relational database must defineable in the relational data sub-langage and storeable in the catalog, not in the application programs."
In SQL Server you can specify data types data types (integer, nvarchar, boolean etc) which puts in data type checks in SQL Server rather than through application programs.
Rule 11. Distribution independence.
"A relational DBMS has distribution independence."
SQL Server can spread across more than one physical computer and across several networks; but from application program point of view it is not a big difference but just specifying the SQL Server name and the computer on which located.
Rule 12. Non-Subversion Rule.
"If a relational system has a low-level(single-record-at-a-time) language, that low level cannot be used to subver or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."
In SQL Server whatever integrity rules are applied on every record are also applicable when you process a group of records using application program in any other language (C#, VB.NET, J# etc...)
Q. Is Access database a RDBMS?
Ans: Access fullfills all rules of CODD, so from this point of views yes it's truly RDBMS. But many people can contradict it as a large community of Microsoft professional thinks that ACCESS is not.
See Database Questions and Answers Concepts Part2, and don't forget check out my personal website: http://m.aatechsources.com Article and Forum