Business RequirementsBefore we sit down at the keyboard, we need to ensure that we have a solid understanding of the customer's requirements. What's the best way to obtain this insight? Talking to the customer, of course! After sitting down with XYZ's Human Resources Director, we've learned that they are a widget sales company and are primarily interested in tracking information on their sales personnel. XYZ Corporation divides their sales force into eastern and western regions, each of which is divided into many territories covered by individual sales reps. The HR department would like to track the territory covered by each employee as well as each employee's salary information and supervisory structure.
To meet these requirements, we've designed a database consisting of three tables, shown in the Entity-Relationship diagram on this page.
Choosing a Database PlatformWe've decided to use a database management system (or DBMS) that is built upon the Structured Query Language (SQL). Therefore, all of our database and table creation commands should be written with standard ANSI SQL in mind. As an added benefit, using ANSI-compliant SQL will ensure that these commands will work on any DBMS that supports the SQL standard, including Oracle and Microsoft SQL Server. If you haven't selected a platform for your database yet, the article Database Software Options walks you through the selection process.
Creating the DatabaseOur first step is to create the database itself. Many database management systems offer a series of options to customize database parameters at this step, but our database only permits the simple creation of a database. As with all of our commands, you may wish to consult the documentation for your DBMS to determine if any advanced parameters supported by your specific system meet your needs. Let's use the CREATE DATABASE command to set up our database:
Take special note of the capitalization used in the example above. It's common practice among SQL programmers to use all capital letters for SQL keywords such as "CREATE" and "DATABASE" while using all lowercase letters for user-defined names like the "personnel" database name. These conventions provide for easy readability.
CREATE DATABASE personnel
Continue reading this tutorial as we create tables for our database.