Question

Develop a conceptual data model for the following scenario:

The data model should consist of the usual 5 components: E-R diagram, Entity Types (including entity type identifiers), assumptions, additional constraints, and limitations:

It is desired to develop a shop database for a shop of different classes of products.

A shop sells different classes of products to customers. For each class, it is required to keep the following information: class identification number (class-id) and class name. Each class has one or more products.

Every customer may or may not buy products. The following information of customers needs to be kept: customer identification number (customer-id), customer-name, address, mobile phone number and email address.

An employee may sell zero, one or more products. The following information of employees are required to be kept: employee identification number (emp-id), employee name, hire-date, number of working hours, salary, address, mobile phone number and email address. Each employee can work at most eight hours daily.

A product may or may not be be sold by employees to customers such as two HP laptops (quantity is 2) may or may not be sold by two different employees to two different customers. For every product, the product identification number (product-id), product name, quantity, price and description need to be kept. Each product is exactly belonging to a class.

The product-id should starts with letter p.

Every customer may or may not receive discounts on the products, and each product may or may not have discounts for customers. The discounts information are required to be kept, and these information are: customer identification number, product identification number, discount amount and discount date.

The customer can receive at most two discounts on products.

After the customer bought the products he/she chose. He/she can evaluate the service received by filling an evaluation form giving on a screen in the shop. A customer may or may not fill an evaluation form, and every evaluation form is filled by a customer. The evaluation form consists of the following information that needs to be kept: form identification number (form-id), description, evaluation result and evaluation-date.

Note: you should expect about 8 entity types, 8 relationship types, 32 attributes, 9 additional constraints, 4 assumptions and 2 limitations in the CDM for this application.

Answer 1

ASSUMPTIONS:

1. Evaluation form does not track customer details
2. Discount date is Order Date within Order file
3. Employee belongs to one and one shop
4. There may be many shops but same inventory of products
5. Discount has time limit period and hence from-date and to-date fields are defined

LIMITATIONS:

1. The product-id should starts with letter p could not be depicted in diagram
2. Discount details namely DISCOUNT-AMNT repeated in ORDER entity to discover data integrity issues.
3. TOTAL QUANTITY PRICE BEFORE DISCOUNT and TOTAL QUANTITY PRICE AFTER DISCOUNT are stored even though one can calculate it to discover data integrity issues in future
4. The ORDER-DATE field in ORDER table is in DATETIME format to ensure its unique in case customer makes two different orders in the same day
5. A single purchase of multiple products has the same ORDER-DATE
6. A single purchase by a single customer is determined by grouping rows with CUSTOMER-ID + ORDER-DATE field.
7. The tables can be simplified further by have line items for each product sold against an order but it is not done or depicted.

CONSTRAINTS:

1. The field ‘CLASS-ID’ in ‘PRODUCT’ table has a foreign key constraint in ‘PRODUCT CLASS’ table
2. The field ‘PRODUCT-ID’ in ‘DISCOUNT’ table has a foreign key constraint in ‘PRODUCT’ table
3. The field ‘SHOP-ID’ in ‘EMPLOYEE’ table has a foreign key constraint in ‘SHOP’ table
4. The field ‘CUSTOMER-ID’ in ‘ORDER table has a foreign key constraint in ‘CUSTOMER’ table
5. The field ‘PRODUCT-ID’ in ‘ORDER table has a foreign key constraint in ‘PRODUCT’ table
6. The field ‘DISCOUNT-ID’ in ‘ORDER table has a foreign key constraint in ‘DISCOUNT’ table
7. The fields ‘CUSTOMER-ID+PRODUCT-ID+ORDER-DATE’ is another PRIMARY key in ‘ORDER’ table
8. ‘ORDER-ID’ field in ORDER table is UNIQUE PRIMARY KEY for every product purchase
9. The field ‘EMP-ID’ in ‘ORDER’ table has a foreign key constraint in ‘EMPLOYEE’ table