ITEC-8145 Database Design Assignment: Customer, Sales, Order Analysis

This assignment solution for ITEC-8145 focuses on database design principles, specifically normalization and the creation of an Entity-Relationship Diagram (ERD). The solution includes detailed tables for customers, sales representatives, orders, order lines, and part descriptions, all normalized to the Third Normal Form (3NF). Each table includes relevant attributes such as customer names, addresses, sales representative details, order information, and part specifications. The assignment also defines business rules that govern the relationships between entities, such as customers, orders, and sales representatives. The ERD visually represents these relationships. The document also provides a list of references used for the assignment. This comprehensive solution offers a practical application of database design concepts and principles.

Running head: ITEC-8145
ITEC-8145
Name of the Student:
Name of the University:
Author Note:

1ITEC-8145
Table of Contents
Normalization.............................................................................................................................2
Customer................................................................................................................................2
Sales Representatives.............................................................................................................4
Orders.....................................................................................................................................5
Order Lines.............................................................................................................................6
Part Description......................................................................................................................7
Business Rules...........................................................................................................................9
Entity Relationship Diagram....................................................................................................11
References................................................................................................................................13

2ITEC-8145
Normalization
Customer
3NF
CustName
Customer ID Last Name First Name
124 Student’s last name Student’s first name
256 Instructor’s last name Instructor’s first name
311 Charles Don
315 Daniels Tom
405 Williams Al
412 Adams Sally
522 Nelson Mary
567 Dinh Tran
587 Galvez Mara
622 Martin Dan
CustAddress
Customer ID Street Address City State Zip Code
124 481 Oak Lansing MI 49224
256 215 Pete Grant MI 49219
311 48 College Ira MI 49034
315 914 Cherry Kent MI 48391
405 519 Watson Grant MI 49219

3ITEC-8145
412 16 Elm Lansing MI 49224
522 108 Pine Ada MI 49441
567 808 Ridge Harper MI 48421
587 512 Pine Ada MI 49441
622 419 Chip Grant MI 49219
CustBlnc
Customer ID Current Balance Credit Limit
124 818.75 1000
256 21.5 1500
311 825.75 1000
315 770.75 750
405 402.75 1500
412 1817.5 2000
522 98.75 1500
567 402.4 750
587 114.6 1000
622 1045.75 1000
CustSales
Customer ID Sales Rep ID
124 3
256 6

4ITEC-8145
311 12
315 6
405 12
412 3
522 12
567 6
587 6
622 3
Sales Representatives
3NF
SalesName
Sales Rep
ID
Last
Name
First
Name
Street
Address
City State Zip
3 Jones Mary 123 Main Grant MI 49219
6 Smith William 102
Raymond
Ada MI 49414
12 Diaz Miguel 419
Harper
Lansing MI 49224
SalesReg
Sales Rep ID Region Region Description

5ITEC-8145
3 C Central
2 E Eastern
6
12
W
SC
Western
South Central
12 UP Upper Pennisula
SalesRate
Sales Rep ID Total Commission Commission Rate
3 2150 .05
6 4912.5 .07
12 2150 .05
Orders
3NF
OrderCust
Order ID Customer
12489 124
12491 311
12494 315
12495 256
12498 522
12500 124
12504 522

6ITEC-8145
OrderDt
Order ID Order Date Shipping Date
12489 02-JUL-11 22-JUL-11
12491 02-JUL-11 22-JUL-11
12494 04-JUL-11 12-JUL-11
12495 04-JUL-11 22-AUG-11
12498 05-JUL-11 -
12500 05-JUL-11 22-AUG-11
12504 05-JUL-11 -
Order Lines
3NF
PartOrd
Part ID Order ID
AX12 12489
BT04 12491
BZ66 12491
CB03 12494
CX11 12495
AZ52 12498
BT04 12500

7ITEC-8145
CZ81 12504
PartPrice
Part ID Number Order Quoted Price
AX12 11 21.95
BT04 1 149.99
BZ66 1 399.99
CB03 4 279.99
CX11 2 22.95
AZ52 2 12.95
CZ81 2 325.99
Part Description
3NF
PartDsc
Part ID Part Description Units on Hand
AX12 Iron 104
AZ52 Dartboard 20
BH22 Corn Popper 95
BT04 Gas Grill 11
BZ66 Washer 52
CA14 Griddle 78
CB03 Bike 44

8ITEC-8145
CX11 Blender 112
CZ81 Treadmill 68
PartCls
Part ID Class
AX12 HW
AZ52 SW
BH22 HW
BT04 AP
BZ66 AP
CA14 HW
CB03 SG
CX11 HW
CZ81 SG
PartPrc
Part ID Warehouse Number Unit Price
AX12 3 24.95
AZ52 2 12.95
BH22 3 24.95
BT04 2 149.99
BZ66 3 39.99
CA14 3 39.99
CB03 1 299.99

9ITEC-8145
CX11 3 22.95
CZ81 2 349.95
Business Rules
CustName
 Customer ID is PK.
 1 Customer ID : 1 Customer Name
CustAddress
 Customer ID is PK.
 Many Customer ID : 1 Street address or City or State or Zip.
CustBlnc
 Customer ID is PK.
 Many customer: Same current balance or credit limit.
CustSales
 Customer ID is PK.
 1 Customer ID : 1 Sales ID
SalesName
 Sales ID is PK.
 1 Sales ID: 1 Sales Representative Name.
 Many Sales ID: Same Street address or City or State or Zip.
SalesReg

10ITEC-8145
 Sales ID is PK.
 1 Sales ID: 1 Region or region description.
SalesRate
 Sales ID is PK.
 Many Sales ID: Same Commission rate or total commission.
OrderCust
 Order ID is PK.
 1 Order Id: 1 Customer ID.
OrderDt
 Order ID is PK.
 1 Order ID: 1 Order date or shipping date.
PartOrd
 Part ID is PK.
 1 Part ID: 1 Order ID.
PartPrice
 Part ID is PK.
 1 Part ID: Same Number Ordered or Quoted Price.
PartDesc
 Part ID is PK.
 1 Part ID: 1 Part Description.
 Many Part ID: Same Units on Hand.

11ITEC-8145
PartCls
 Part ID is PK.
 1 Part ID: 1 Class
PartPrc
 Part ID is PK.
 1 Part ID: 1 Warehouse number or Unit price

12ITEC-8145
Entity Relationship Diagram

13ITEC-8145
References
Amran, N., Mohamed, H., & Bahry, F. D. S. (2018). Developing Human Resource Training
Management (HRTM) Conceptual Model Using Entity Relationship Diagram (ERD).
INTERNATIONAL JOURNAL OF ACADEMIC RESEARCH IN BUSINESS AND
SOCIAL SCIENCES, 8(12).
Cooijmans, T., Ballas, N., Laurent, C., Gülçehre, Ç., & Courville, A. (2016). Recurrent batch
normalization. arXiv preprint arXiv:1603.09025.
Dunn, C., & Nel, L. D. (2017). Entity Relationship Diagram Mapping.
Santurkar, S., Tsipras, D., Ilyas, A., & Madry, A. (2018). How does batch normalization help
optimization?. In Advances in Neural Information Processing Systems (pp. 2483-
2493).
Singhal, A., Buckley, C., & Mitra, M. (2017, August). Pivoted document length
normalization. In ACM SIGIR Forum (Vol. 51, No. 2, pp. 176-184). New York, NY,
USA: ACM.
Villa, F., Moreno, F., & Guzmán, J. (2018, August). An Analysis of a Methodology that
Transforms the Entity-Relationship Model into a Conceptual Model for a Graph
Database. In International Conference for Emerging Technologies in Computing (pp.
70-83). Springer, Cham.
Wu, Y., & He, K. (2018). Group normalization. In Proceedings of the European Conference
on Computer Vision (ECCV) (pp. 3-19).
Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-
Relationship Diagram (ERD) to Relational Schema Mapping. International Journal
of Modern Education and Computer Science, 8(7), 1.

14ITEC-8145