ACC 444 Assignment #1 Relational Databases and Entity Relationship Diagrams (ERDs) Entity Relationship Diagrams (ERDs) ERDs reflect the database...

ACC 444 Assignment #1

Relational Databases and Entity Relationship Diagrams (ERDs)

Entity Relationship Diagrams (ERDs)

ERDs reflect the database entities (e.g., master data table, transaction data table) and the relationships among entities. The ERD is the structure of the relational database. ERP systems are applications. The data is stored in a relational database (e.g., IBM DB2, Oracle, Microsoft SQL Server, MySQL, etc.)

The components of an ERD are:

•   Entities - something that can be uniquely identified; represented by two- dimensional tables (aka relations); labelled with a singular noun

•   Cardinalities - the relationship between two entities (maximums and minimum relationships can be noted). Types of Cardinalities :

1. One-to-one: Each record in the first table is associated with only one occurrence in the second table, and each record in the second table is associated with only one occurrence in the first table.

2. One-to-many: Each record of one table can be associated with zero, one, or many records in another table.

3. Many-to-many: the primary key in one table is associated with more than one record in a second table and the primary key in the second table is associated with more than one record in the first table. Databases do not allow many-to- many. Must break a many-to-many into two one-to-many's using an "associative entity" (aka junction table).

There are maximum and minimum cardinalities.

•   Maximum cardinality is the maximum number of occurrences of an entity in relation to another entity. These go on the outside of the line. Will be 1 or 
many. 

•   Minimum cardinality is the minimum number of occurrences of an entity in relation to another entity. These go on the inside of the line. Will be 1 or 0 (simply means is an occurrence necessary/required) 

Required:

1. From the below scenario determine the master and transaction tables involved.

2. Draw rectangles for each entity (table) in the ERD.

3. Label:

a.                   Name of table

b.                  Primary Key (PK) for each table,

c.                   Foreign Key(s) (FK) in each table, and

d.                  at least one other field in each table (use common sense...think about what non-key data fields would exist in each entity).

4. Connect the tables together to form an ERD using appropriate "crows-feet" notation (as shown/discussed in class). Include associative entities where appropriate (i.e., junction tables to appropriately represent any many-to- many relationships)

5. Make sure to label the maximum and minimum cardinalities appropriately

Scenerio:

At XYZ company, a sales rep can be assigned to more than one customer. The sales rep can be put into the ERP system before serving customers (hint: this part is referring to a minimum cardinality). A customer is assisted by one and only one sales rep, and must be assisted to a sales rep.

A customer can be put into the system before placing an order. A customer may place many orders, but a sales order comes from only one customer (i.e., customers do not share a sales order).

An order contains at least one inventory item, but could contain many items. All items that XYZ keeps track of are not sold (some are used for maintenance of machines, etc.). An inventory item can be sold on more than one sales order.

Sales orders are later invoiced. Multiple sales orders can be on one invoice and there may also be partial billings (e.g., sales order gets partially shipped and that shipment gets invoiced/billed, then the remaining portion of the sales order gets shipped and subsequently billed on another invoice).

The same applies for payments. Customers can make a partial payment (remittance) on their invoice(s), pay an invoice in full, or a single payment can be made to apply to multiple invoices.