Business Rules  «Prev  Next»

Lesson 5 Business Rules Course project
Objective The project that you will be working on in this course.

Enterprise Business Rules - Course Project

For the course project, I will be referring to a timesheet database that can be used to track time and bill hours to a client or supervisor. Because many of you may be consultants or contractors, I thought it would be fun to build a real-world database that enables you to track your time and bill your supervisor or clients accurately. With the Timesheet database, you will be able to:
  1. Keep track of the time you spend on each task for each client
  2. Exclude lunch and errand time
  3. Track vacation time
  4. Compare actual versus estimated time per task

Master Database

Every SQL Server, regardless of version or custom modifications, has the master database. This database holds a special set of tables (system tables) that keeps track of the system as a whole. For example, when you create a new database on the server, an entry is placed in the sysdatabases table in the master database. All extended and system-stored procedures, regardless of which database they are intended for use with, are stored in this database. Obviously, since almost everything that describes your server is stored in here, this database is critical to your system and cannot be deleted. The system tables, including those found in the master database, were occasionally used when in a bind to provide system configuration information, such as whether certain objects existed before you performed operations on them. Microsoft warned developers for years not to use the system tables directly, but, because there were few other options, most developers ignored that advice. Nevertheless, Microsoft began providing other options in the form of system and information schema views; you can now utilize these views to get at the systems metadata as necessary. For example, if you try to create an object that already exists in any particular database, you get an error. If you want to force the issue, you could test to see whether the table already has an entry in the sys.objects table for that database. If it does, you would delete that object before re-creating it.

Model Database

The model database is aptly named, in the sense that its the model on which a copy can be based. The model database forms a template for any new database that you create. This means that you can, if you want, alter the model database if you want to change what standard, newly created databases look like. For example, you could add a set of audit tables that you include in every database you build. You could also include a few user groups that would be cloned into every new database that was created on the system. Note that because this database serves as the template for any other database, it is a required database and must be left on the system and you cannot delete it. There are several points to keep in mind when altering the model database: Any database you create has to be at least as large as the model database. That means that if you alter the model database to be 100MB in size, you cannot create a database smaller than 100MB. Similar pitfalls apply when adding objects or changing settings, which can lead to unintended consequences. As such, for 90 percent of installations, I strongly recommend leaving this one alone.