Database Assignment 2

Site hosted by Angelfire.com: Build your free website today!

Database Assignment #2

1. Read section 4 from the database training module. The modules lead you step by step through the process of designing one table

2. Your assignment is to design the other tables needed for the South Dakota Pens database. As you read through the module, it will become apparent exactly what this means.

3. Using Access, develop the specifications for these tables. You may want to jot down a few notes to remind yourself later why you made some of choices.

4. You do NOT have to add any data to the table at this time, the focus should be on the table layout only.

5. At the end, you will have an Access file on your machine. That is all that is required for this assignment.

6. NEW - Please create a web page that shows the description of the table that you made - NEW

The (above) assignment asks us to design the tables needed for the SD Pens database. It then asks us to develop the specifications for these five tables to indicate why these were developed and needed. This is a web page that will show the description of the table that I made:

Note: I used what I thought to be the appropriate field size for each field. All were estimated according to the approximate usage for each one. For instance, First Name would not normally be over 10 characters in length, whereas last name could possible be a hyphenated name so it must allow for more length, approximate 30.

EMPLOYEES:

Employee ID:

auto number
A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated. (see below)

Primary Key
each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null values from being entered in the primary key fields.

I chose auto number because it is the primary key of the table. It needs to be automatically incremented in order for this to be used as a foreign key in the other tables so that the information recorded in this table may be easily accessed. Also in auto number, no duplicates are allowed so that no number may be used twice so as to confuse data retrieval.

An auto number field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don't set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key

Employee FN, MN, LN, Social Security, address, city, state, zip, phone numbers, email, job classification

Text
(Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.

All the other fields of the table are used as text with the exception of Date Hired so that they may be easily retrieved and so that Access recognizes the characters used in data input.

Date Hired

Date/Time
Date and time values for the years 100 through 9999.

I used the MM/DD/YYYY format which is the short date form. This is easiest to access for the purpose of retrieving specific dates as well as making it possible to retrieve a date within a range of time.

Employee FirstName, LastName, Social Security, Address, City, State, Zip, Job Classification

Required Fields
(zero length not allowed)

This information is absolutely necessary when retrieving data for cross-referencing from table to table. I also believe that this information is necessary for any employer to have on hand for referencing of any kind, whether it is a query that is being used to retrieve marketing information or whether it is just for the sole purpose of using it for the employee info.

Employee MiddleName, Phone, Fax, Email, Date Hired

Non-Required Fields
(zero length allowed)

This is not required, although the Employee Middle Name is recommended. This information may not be available, therefore not necessarily able to access data retrieval. For instance, an employee may not have a middle name, phone, fax, email.

Foreign Key

none used in this table

DISTRICTS:
Location	Primary Key Single Field Primary Key If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. You can specify a primary key for a field that already contains data as long as that field does not contain duplicate values or Null values		This is the primary key of the table because it is used to access the location name of each district. For example, when you wish to locate a particular area of usage type of pens, this would be a useful tool in finding out what is popular in what section of the state, especially if there would be a special on the type of pen then you could market that particular region.
Location, County Members	Text (Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	Again, these fields are designed to use Text when entering data.
Employee ID	Number Numeric data used in mathematical calculations. For more information on how to set the specific Number type, see the Field Size property topic.	This must be used because it is a primary key in the Employee table. If it is not designated as a number, then Access cannot recognize the information and retrieve the data needed.
Employee ID	Foreign Key One or more table fields (columns) that refer to the primary key field (s) in another table. A foreign key indicates how the tables are related	Because this is the only FK in the Districts Table, it will be used to retrieve data from the Employee Table. Perhaps, some information is required as to which employee is the District Manager of the SE location. This would be an optimal functioning to be able to retrieve such information for practical purposes.
Location, County Members, Employee ID	Required Fields (zero length not allowed)	This information is absolutely necessary when retrieving data for cross-referencing from table to table.
None	Non-Required Fields (zero length allowed)	This information may not be available, therefore not necessarily able to access data retrieval. But in this table, all information was required so this was not used.

INVOICES:

Invoice Number

auto number
A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated. (see below)

Invoice Date

Date/Time
Date and time values for the years 100 through 9999.

Again, I used the MM/DD/YYYY format which is the short date form. This is easiest to access for the purpose of retrieving specific dates as well as making it possible to retrieve a date within a range of time.

Customer ID, Employee ID

Number
Numeric data used in mathematical calculations. For more information on how to set the specific Number type, see the FieldSize property topic.

This must be used because it is a primary key in the Employee table. If it is not designated as a number, then Access cannot recognize the information and retrieve the data needed.

Customer ID, Employee ID

Foreign Key
One or more table fields (columns) that refer to the primary key field (s) in another table. A foreign key indicates how the tables are related

Again, I needed to have these in this table designated as numbers (see above) in order for the retrieval and cross-referencing between the tables to be made possible.

Invoice Number, Invoice Date, Customer ID, Employee ID

Required Fields
(zero length not allowed)

All fields were required so that any query could be performed in order to retrieve specific information needed. For instance, you might need to retrieve the Invoice Number for a certain customer (using Customer ID) and then to find out which Employee (via Employee ID) had been the representative.

None

Non-Required Fields
(zero length allowed)

This information may not be available, therefore not necessarily able to access data retrieval. But in this table, all information was required so this was not needed.

CUSTOMERS:
Customer ID	auto number A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated. Primary Key each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null values from being entered in the primary key fields.	I chose auto number because it is the primary key of the table. It needs to be automatically incremented in order for this to be used as a foreign key in the other tables so that the information recorded in this table may be easily accessed. Also in auto number, no duplicates are allowed so that no number may be used twice so as to confuse data retrieval. Also, by making Customer ID a PK, then I will be able to access the customer information whenever I do a query regarding invoice and shipping information, etc.
Customer FN, MN, LN, Address, City, State, Zip, and Phone	Text (Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	All the other fields of the table are used as text with the exception of Customer ID so that they may be easily retrieved and so that Access recognizes the characters used in data input.
Customer FN, LN, Address, City, State, Zip, and Phone	Required Fields (zero length not allowed)	All fields were required here with the exception of the Customer's Middle Name because all information about the customer is necessary. For instance, a Customer First and Last Name is necessary for cross-referencing to who bought what and to which employee sold in what district to whom and to where items will be shipped. Therefore, it will stand to reason that Customer full Mailing Address and Phone would be necessary as well for future referencing, future sales, etc.
Customer Middle Name	Non-Required Fields (zero length allowed)	This is recommended but not necessary as there are some people who never use middle names or who do not have these as well.
	Foreign Key	none used in this table

SHIPPING:
Receipt Number	auto number A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated. Primary Key each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null values from being entered in the primary key fields.	Again, I chose auto number because it is the primary key of the table. It needs to be automatically incremented in order for this to be used as a foreign key in the other tables so that the information recorded in this table may be easily accessed. Also in auto number, no duplicates are allowed so that no number may be used twice so as to confuse data retrieval. Also, by making Receipt Number a PK, I will then be able to access the shipping information whenever I do a query regarding invoice and shipping information, etc. Also handy to have if tracking a shipment.
Invoice Number, Customer ID	Foreign Key One or more table fields (columns) that refer to the primary key field (s) in another table. A foreign key indicates how the tables are related	Again, I needed to have these in this table designated as numbers (see above) in order for the retrieval and cross-referencing between the tables to be made possible
Invoice Number, Customer ID	Number Numeric data used in mathematical calculations. For more information on how to set the specific Number type, see the Field Size property topic.	Again.... This must be used because it is a primary key in the Employee table. If it is not designated as a number, then Access cannot recognize the information and retrieve the data needed.
Description	Text (Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	This is needed in order to know what type of pens the customer is purchasing and may go to patterns in marketing to the customer. Text was used for this because it will be in text.
Receipt Number, Invoice Number, Customer ID, Description	Required Fields (zero length not allowed)	These fields have to be required so that information may be cross-referenced and accessed from table to table. For Example, a query might require a Description of the type of pen a customer (using Customer ID) just purchased or last purchased. You would then need to retrieve both customer information as well as the description from this table.
	Non-Required Fields (zero length allowed)	none needed for this table