1.Scenario:
Jonny is appointed as an intern position, which might lead to a junior database architect role in TechTrend Corporation (TTC). Jonny is assigned to redesign two legacy tables related to “Suppliers,” to be normalization compliant. The following business rules are provided.
Business Rules:
- Suppliers can be local or national, thus can have multiple locations.
- Any location can supply items to TechTrend.
- Any location manager can have a separate pricing deal with TechTrend, based on production process specific to that location and localization
- Each location has a contact person assigned to TechTrend.
- Traditionally, TechTrend dealt with a singular location, at the most two. With rapid growth in business, the TTC officials are finding better leverage possibilities to negotiate beneficial terms with suppliers in multiple locations.
- The DB administrator has specifically requested Johnny to keep in mind the strategic business needs, while restructuring the tables.
- There are other tables within the database, which are beyond the context of this exercise.
Based on the table structures and business rules, you will match the following questions with the correct answers listed. Following, are the existing Legacy table structures. Next, you will see a list of questions displayed. You will drag column A to column B.
Your answers should be in coordination with concepts discussed in Chapter 6
The existing (Legacy) Table(s) Structure
Supplier table structure:
| Attribute (Field) | Data Type | Example | Required |
| Supplier_ID | Character | SUP-112 | Y |
| Supplier_Name | Character | ACM Medical Supplies, Inc. | Y |
| Tel_Num | Character | 1-888-666-4444 | Y |
| Location1_ID | Character | SUP-112-L1 | Y |
| Location2_ID | Character | SUP-112-L2 | N |
| Location3_ID | Character | SUP-112-L3 | N |
| Rep1 | Character | Hansen, Joe | Y |
| Rep2 | Character | Smith, Tiffany | N |
| Rep3 | Character | N | |
| Tel#1 | Character | 511-7277-7744 | Y |
| Tel#2 | Character | 572-7777-7745 | N |
| Tel#3 | Character | N | |
| Memo | Character | 1. Called Tiffany for Part# 112232. The expected delivery is on or before 2/28/18.2. Joe reported that the Part# 111232 is out-of-stock. | N |
Location table structure:
| Attribute (Field) | Data Type | Example | Required |
| Location_ID | Character | 112-L2 | Y |
| Supplier_ID | Character | 112 | Y |
| Address | Character | 123 Main Street | Y |
| City | Character | New York | Y |
| State | Character | NY | Y |
| Zip | Character | 10008-212 | Y |
Based on the table structures and business rules, match the following questions with the correct answers. Your answers should be in coordination with concepts discussed in Chapter-6.
