The Database Schema

The Sales Data Analyst tool uses a SQL Server database called SalesAnalysis. The data is imported into SalesAnalysis from the SalesData database. Figure 28.5 displays the database schema.

Figure 28.5: The schema for the SalesAnalysis database.

The database schema includes the following tables:

• tblCustomers. Contains data such as the customer's ID, name, address, e-mail address, age, city, state, and gender.

• tblSalesData. Contains the customer's address, order ID, item description, category amount, purchase month, and purchase year.

• tblParameters. Contains parameters such as SMTP server, POP server, username, password, From e-mail address, current year, and current month.

The tblSalesData table has CustomerID as the foreign key that refers to the primary key in the tblCustomers table. There's a one-to-many relationship between tblCustomers and tblSalesData tables. The tblParameters table is an independent table.

The personal and contact information for the customers is stored in the tblCustomers table. The structure of the tblCustomers is shown in Figure 28.6.

Figure 28.6: The design of the tblCustomers table.

The tblCustomers table contains CustomerID as the primary key column, and its datatypes are Integer and autonumber. The other fields in this table are Name, Address, E-Mail, Age, City, State, and Gender.

The details of the item sold are stored in the tblSalesData table, as shown in Figure 28.7.

Figure 28.7: The design of the tblSalesData table.

The tblSalesData table contains CustomerID, OrderID, and ItemDesc as the primary key columns, and the datatypes of the keys are integer, char, and char, respectively. The other fields in this table are Category, Amount, PurchaseMonth, and PurchaseYear.

The information about the parameters is stored in the tblParameters table, as shown in Figure 28.8.

Figure 28.8: The design of the tblParameters table.

There are no primary keys in this table. The fields in this table are SMTPServer, POPServer, UserName, Password, FromEmailAddress, CurrentYear, and CurrentMonth.