BUS 442: Information Systems Development
Project 3 (Teams of 3): Creating an App to Track and Manage Product Data
Part 1: Design and Build the Database
StrongestLink Logistics, LLC is a small, hypothetical tech startup company who just relocated to the downtown Raleigh, NC market from San Francisco, CA. The management of the company has established a presence in their leased headquarters at HQ Raleigh but still primarily sells its products and services to clients in the United States and in Canada.
The company owners want to build an app to track information about products and suppliers in a supply chain for companies that cannot yet afford expensive Supply Chain Management or ERP software. They have decided to hire a team of college interns to build an app for them to track product information. The deliverable of this project will be an application to track and manage product data. At a minimum, the app should allow users to view, maintain, and search for individual and aggregated data about all company products and suppliers, as well as display the sources of suppliers and their products.
Project (Part 1) Requirements
The project has three parts: a relational database design and implementation, an interface design and implementation, and C# code design and implementation. For part 1, design and create a new relational database (using MS Access), called VendorProductDatabase, that stores data about products and suppliers. Although a database like this in an industry environment may require many tables, you only need to create and import a few tables for this project. The main table for this project is the Product table: PRODUCT. You will also create a vendor (supplier) table, VENDOR, that connects to the Product table. Theirs is a 1:M relationship; that is, one vendor supplies many Products, but a Product is supplied by one and only one vendor. Vendors are located in various states and regions/provinces of both the United States and other countries.
Defining the Data and MetaData
Launch MS Access and create a new Product database. Add two tables to this database schema: the VENDOR table and the PRODUCT table. Create a 1:M relationship between the two tables. Additionally, your team may choose to add other tables and/or attributes to your Product database that you think would enhance the app.
VENDOR Table:
The VENDOR table should have the following attributes with the metadata defined as follows. All data should be defined and entered at the atomic level.
VendorID: Required; 4 integers, Primary Key VendorName: Required; 30 characters
VendorRegion: Required; 5 characters; Lookup Column constraints of North, East, South, and West
State/Province Abbreviation: Required; 2 characters; If the Country is USA, the first release of the software will limit this field to valid U.S. states of: NY, NJ, VT, MA, RI, and DE. Each are ISO- standardized to two characters. Other countries, like Canada and China, use provinces. Use the Lookup Wizard Column constraints to add all appropriate states or provinces. This app release will not validate the states or provinces of countries other than the U.S., China, and Canada, but the company plans to later modify the product in another release to do that.
City: Required; 25 characters
Postal (Zip) Code : Required; 9 numbers; Example: 27526-0298. (Note: One option is to only store the postal code in the VENDOR table and link to another table in the database to retrieve city and state. This option would be consistent with a relational database normalized to third- normal-form (3NF). However, this particular choice is optional for this app.)
Country: Required; 3 characters; Lookup Column constraints limited to USA (United States of America), CAN (Canada), CHI (China), SKO (South Korea), ISR (Israel), and IND (India).
Email Address: Required; 20 characters
Phone Number: Required; 13 numbers (formatting symbols not required)
Website Address: Required; 25 characters
Classification: Required; 15 characters; Lookup Column constraints of Preferred or Not Preferred
Active Status: Required; 3 characters; Lookup Column constraints of Yes or No Notes: Not required; 50 characters
Contact Name: Not required; 30 characters; Example: John Doe
Product Table:
The PRODUCT table should have the following attributes with the metadata defined as follows.
All data should be defined and entered at the atomic level.
ProductID: Required; 9 integers, Primary Key
ProductName: Required; 30 characters; This is a product name (e.g., Ipod Mini)
VendorID: Required; 4 characters; Constraints of valid entries in the VENDOR table
Product Unit Price: Required; 9 Numeric; 2 Decimal places
Product Cost: Required; 9 Numeric; 2 Decimal places
Quantity-On-Hand: Required; 9 Integer
Quantity-On-Order: Required; 9 Integer
Product Lead-Time: Required; 4 Integer; (in days)
Product Reorder Level: Required; 4 Integer
ProductLine: Required; 20 characters
Brand: Required; 20 characters
Discount %: Not required; 4 decimal; Example: 10% would be entered as .10.
YTD Purchases: Required; 12 decimal; This attribute will be eventually sourced by the Purchasing application. For purposes of this app, enter a hypothetical number for each product.
YTD Sales: Required; 12 decimal; This attribute will be eventually sourced by the Sales application. For purposes of this app, enter a hypothetical number for each product.
Adding Constraints in the DBMS
To insert a validation rule in the Access database to constrain the VENDOR StateAbbr field to only legitimate states within correct countries, perform the following. In the Validation Rule Property of the Property Sheet, type:
[State] In (“NY”,”NJ”,”VT”,”MA”,”DE”) And [Country] Like “USA”
Or [State] In (“YU”,”HA”,”SH”,”SI”,”ZH”) And [Country] Like “CHI”
Or [State] In (“NS”,”QC”,”ON”,”PE”,”NB”) And [Country] Like “CAN”
In the Validation Text property of the Property sheet, type an error message, like:
Invalid state. Please enter a valid state.
Populating the Database:
Once the database is built, populate the VENDOR table first with fictitious suppliers and their data. Assign a unique number to each vendor as the unique primary key. Then, populate the PRODUCT table by keying information for at least twenty fictitious products, again with a unique Product number as the unique key. Part 2 of the project is covered in another document. Please contact me for any information that needs clarification.