Specifications of the Database Tables - Essay Example

Introduction:

This report describes the features and specifications of the fields formulated for the database tables designed for “Fernando’s Skate Shop”. This design is implemented using MS-Access 2007. The database design comprises six tables. The descriptions of the fields of tables are shown in Table 1.1 – Table 1.6

Table 1.1. The Field specifications for the CUSTOMER Table

Field Name

Type

Size

CustomerID

Text

5

Primary Key

LastName

Text

20

FirstName

Text

20

Address

Text

30

City

Text

15

State

Text

3

Zip

Text

5

Telephone

Text

10

Input mask attached

E-mail

Text

40

Table 1.2. The Field specifications for the SUPPLIER Table

Field Name

Type

Size

CustomerID

Text

5

Primary Key

LastName

Text

20

FirstName

Text

20

Address

Text

30

City

Text

15

State

Text

3

Zip

Text

5

Telephone

Text

10

Input mask attached

E-mail

Text

40

Table 1.3. The Field specifications for the PRODUCT TYPE Table

Field Name

Type

Size

TypeID

AutoNumber  

Long Integer

Primary Key

TypeDescription

Text

15

Table 1.4. The Field specifications for the PRODUCTS Table

Field Name

Type

Size

ProductID

Text

20

Primary Key

ProductName

Text

40

ProductType

Number

Long Integer

Foreign Key. Long Integer synchronizes with the data type of the Primary key of the PRODUCT TYPE table. The lookup property is used to attach this field to its master table.

Price

Number

Single

Involves decimal places.

StockUnits

Number

Long Integer

Table 1.5. The Field specifications for the DAILYORDERS Table

Field Name

Type

Size

OrderID

AutoNumber

Long Integer

Primary Key

DateOf Sale

Date/Time

ProductID

Text

20

Foreign Key. A combo box is attached in Lookup Property

CustomerID

Text

5

Foreign Key. The combo box is attached in Lookup Property.

Quantity

Number

Long Integer

ModeOfTransaction

Text

15

Like Cash, Credit Card, Debit Transfer, etc.

Table 1.6. The Field specifications for the PURCHASERECORDS Table

Field Name

Type

Size

PurchaseID

AutoNumber

Long Integer

Primary Key

ItemDescription

Text

20

QuantityPurchased

Number

Long Integer

RateperUnit

Number

Single

Date of Purchase

Date/Time

SupplierID

Text

5

Foreign Key. The combo box is attached to the Lookup property.

Queries:

Their explanation of the two queries introduced in the database is as follows,

Query1: Customer with Orders

This query uses 4 tables to retrieve data and produce results. It returns the sale details with Customer names, Product Names, Product Types, and Date of Sale. Figure 1.2 highlights the design of this query.

Figure 1.2. Query 1: Customer with Orders

Query2: Products with details of Sales

This query uses 3 tables to retrieve data and produce results. It returns the sales volume details with Product Names, respective Prices, the Sum of the quantity of each product sold, and the Sum of the sales amount for each product (calculated by retrieving values from two different tables). Figure 1.3 highlights the design of this query.

Figure 1.3. Query 2: Product with Details of Sales

The Learning Outcomes

Following are the things that are learned through this exercise,

• Identification of possible specifications for fields. from a given scenario.
• Maintaining constraints for foreign keys.
• Using the lookup property
• Understanding and Creating queries to produce useful output from the recorded data.