Logical Database Design

Overview:

The objective of Project #1 is to develop a relational data model describing the primary business processes of Polytechnic Supply Corporation (PSC). PSC is in the business of Business-to-Business wholesale distribution, whereby they purchase all of their products directly from various suppliers, and then they sell these products to their customers. Relevant business rules and a report that represents one user view are provided below.

Business Rules:

1.      Each customer may place multiple orders. Each order must be placed by one and only one customer.

2.      Each order must contain one or many products. Each product may be requested by multiple orders.

3.      All products are purchased from suppliers – a product may be purchased from multiple suppliers at various unit costs; one supplier may provide multiple products.

4.      PSC sells the same product at the same unit price to all customers.

5.      PSC organizes their products into categories, such as books, food, appliances, electronics, sports, etc. A product can be in one and only one category, while one category may contain one or more products.

Sample User View:

Customer Order Report

Acme TNT                                                                             Customer # 2000 654 Dusty Road Salt Lake City UT 84115

(800) 444-3876

Order Number                                          Order Date     Ship Date1515                                                  6/1/2017         7/1/2017

Line Item        Product Number            Description            Quantity Unit Price1              12398                       Fire Alarm                        1              $500.00 2              84038                       Security Alarm                         1              $800.00 3              1705                         Blender                    30            $30.00 4              1700                         Ironing Board                           1              $25.00

Task 1: Create the data dictionary and referential integrity based upon Business Rules Given.

·        Create a project report in MS Word and name it “[FirstNameLastName]_Project1.doc”.

·        Complete Table 1 Data Dictionary below, list entities (all letters in capitalization) and their attributes (first letter of each word capitalized), mark Primary Keys (PK) and Foreign Keys (FK), and their data types, as the example shows.

·        Then complete Table 2 Referential Integrity below, specify the relationships and their cardinalities, as the example shows.

·        Make sure to maintain 3rd Normal Form for each relation, i.e. no repeating groups, partial dependencies or transitive dependencies.

Table 1: Data Dictionary

* When you create a new attribute you get to assign its data type in ERwin:

·        You can choose one from the four domains: Blob, Date/Time, Number, String.

·        Under each domain there are various categories, for example, Number can be Integer or Decimal. For Decimal, each number has precision (number of digits in a number), scale (number of digits to the right of the decimal point in a number), and length (number of bytes that are used to store the number).

Table 2: Referential Integrity

** Cardinality Constraints can be:

·        Optional Many - means 1 to many with the minimum 0.

·        Mandatory Many – means 1 to many with the minimum 1.

·        Optional One - means 1 to 1 with the minimum 0.

·        Mandatory One – means 1 to 1 with the minimum 1.

Task 2: Create a logical data model in ERwin based on Task 1.

·        Open Erwin, follow “Instructions on Initial ERwin Setup” below and set up your Erwin correctly.

Instructions on Initial ERwin Setup:

Set up ERwin with the following specifications:

·        Model –> Model Properties -> Notations

o   Under both Logical and Physical notation, check “IE (Information Engineering)”

·        Right click any spot on canvas and select “properties”

o   On the “Display” tab:

§  Check mark “Display Parent to Child Verb Phrase”

o   On the “Entity” tab:

§  Select “Attribute” from the drop-down for Logical Display Level

§  Check mark all of the following

·        Primary Key designator

·        Foreign Key designator

·        Show Migrated Attributes

·        Based on the data dictionary and referential integrity in Task 1 Table 1 and Table 2, create a new Logical/Physical model in ERwin and name it “[FirstNameLastName]_Project1.erwin”, including:

o   Create entities, their attributes, and relationships between the entities.

o   Mark primary and foreign keys.

o   Set the correct properties for each relationship (including Type properties, Relationship properties, and Cardinality properties).

o   Make sure everything is consistent with the Table 1 Data Dictionary and Table 2 Referential Integrity in Task 1.

o   Assign data type and field length to each attribute as you defined in the data dictionary in Task 1.

·        Make sure all entities, attributes, primary keys, and relationships are properly labeled following common practice as shown in textbook and lectures.

·        Double check the consistency between your Table 1 & 2 with your ERwin diagram, including details like cardinality constraints and data types. Make sure PK (marked with key icon and above the line inside its entity) and FK (marked with (FK) beside the attribute name) are correctly created. When in doubt create some fictitious data and enter into your table design and see if it makes sense.

·        Capture a screenshot of the logical model in ERwin and insert into your Word project report. Make sure the screenshot is readable to the level of each relationship description and cardinality constraint. Use Horizontal layout if necessary.