Explain the difference between system objects and user objects.
A. SQL Server creates system objects during the installation process. They are part of the system, and most of them are necessary for SQL Server to function properly. Whereas system objects are part of the SQL Server system, you create user objects. User objects include the databases, stored procedures, functions, and other system objects that you build.
Q. Describe normalization.
A. Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive level of normal form achieves a better database design than the previous one. It is generally sufficient to apply the first three levels of normal forms to your data.
Q. What is the difference between a DDL trigger and a DML trigger?
A. A DDL trigger executes in response to a change to the structure of a database (for example, CREATE, ALTER, DROP). A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).
Q. Discuss the advantages of SQL Server 2005 Express.
A. SQL Server 2005 Express is free and is easy to use. It provides the same protection and information management provided by more sophisticated versions of SQL Server. It is easy to install, provides rich database functionality, and sports deep integration with Visual Studio 2005.
Q. Name some limitations of SQL Server 2005 Express.
A. SQL Server 2005 Express limits you to one gigabyte of RAM, a four-gigabyte maximum database size, and support for only one CPU. Furthermore, it does not come with either a job scheduling server or a database tuning advisor.
Q. Name some advantages of SQL Server 2005 Mobile Edition.
A. SQL Server 2005 Mobile Edition enables you to port applications to mobile devices. It provides full integration with SQL Server 2005, and even includes synchronization functionality. It offers excellent reliability and performance, as well as multi-user support. Finally, it provides full integration with Visual Studio 2005 and increased device support.
Q. Explain what the Master database is used for.
A. The Master database is the database of all databases. It keeps track of all the login accounts, linked servers, system configuration settings, and more. It also contains initialization settings for SQL Server.
Q. Explain how you utilize the Model database.
A. Model is a very special database. Any objects that you place in Model propagate automatically to any new databases that you create. This provides standardization as well as rapid application development.
Q. Describe MSDB and what it does.
A. MDSB stores data, including scheduling information and backup and restore history information.
Q. What is the difference between a check constraint and a rule?
A. Both check constraints and rules limit the range of values that a user can enter into a column. Whereas check constraints apply only to the table for which you enter them, you can apply rules to multiple tables.
Q. Explain what a primary key is, and describe the ideal criteria for a primary key.
A. A primary key is a column or a set of columns that uniquely identify a row in a table. A primary key should be short, stable, and simple.
Q. Describe a foreign key and indicate how many foreign keys each table can contain.
A. A foreign key constraint consists of a column or a set of columns that participate in a relationship with a primary key table. The primary key is on the one side of the relationship, whereas the foreign key is on the many side of the relationship. A table can have multiple foreign keys.
Q. What is the Tables and Columns specification?
A. The Tables and Columns specifications enable you to designate the foreign key table that will participate in the relationship, the field in the foreign key table that will participate in the relationship, and the field in the current table that will participate in the relationship.
Q. Describe three uses of a one-to-one relationship.
A. You use a one-to-one join when the number of fields required for a table exceeds the number of fields allowed in a SQL Server table, when certain fields that are included in a table need to be much more secure than other fields included in the same table, or when several fields in a table are required for only a subset of records in the table.
Q. Describe a many-to-many relationship and how you create one.
A. With a many-to-many relationship, records in both tables have matching records in the other table. You cannot create a many-to-many relationship directly. You must develop a junction table and relate the junction table to each of the two tables in one-to-many relationships.