computer science

Status Update

An ERD is a graphical representation of an information system that shows the relationship between tables and concepts. An ERD is a data modeling technique which helps in defining business processes and can also be used as a foundation for a relational database (Bagui, & Earp, 2012). This week I have a created a normalized Entity Relationship Diagram (ERD) which models the required tables and relationships using Smart Draw software. The table design and relationships has the support the following functionality as shown by the above ERD diagram.

First, the customer’s entity has several attributes which are customer ID which is the primary key (PK), Name, Address and Phone ID. The inventory entity also carries several attributes which are the diamond code which is the PK (Primary Key), Price, Type, CUT and CARAT. The order details entity has several attribute which is the diamond code which in this case is the PK (Primary Key), the sales ID is the PF (Primary Foreign Key), cost and Quantity. The sales team entity carries the sales ID which is the PK (Primary Key), customer ID which is a foreign key, staff ID, stamp and date. With this ERD the DBMS can be designed in such a way that customers can purchase multiple diamonds of different types on the sales order

References

Bagui, S., & Earp, R. (2012). Database design using entity-relationship diagrams. Boca Raton: CRC Press, Taylor & Francis Group.

Levene, M., & Loizou, G. (2014). A guided tour of relational databases and beyond. London: Springer.

CUSTOMERS

Customer_IDINT <PK>

Name VAR CHAR

Address VAR CHAR

Phone_ID INT

SALES TEAM

Sales_ID INT <PK>

Customer_ID INT<FK>

Staff_ID INT

Date TIMESTAMP

SUM_TOTAL FLOAT

INVENTORY

Diamond_code INT <PK>

Price FLOAT

TYPE VAR CHAR

COLOR VAR CHAR

CUT VAR CHAR

CARAT VAR CHAR

ORDER DETAILS

Diamond_code INT <PK>

Sales_ID INT <PF>

Cost VAR CHAR

Quantity SMALL INT