Entities and Attributes Identification in a Sales Organization Assessment Solutions

Part A

Entities and their attributes

An entity is a real-world object that has distinct and separate existence whereas an attribute defines the properties of an entity. In the given case study, following major entities and attributes identified:

Inventory entity

• Outfit type
• Designer name
• Size
• Price
• Stock Status

Employee entity

• Name
• ID
• Date of Joining
• Hourly wage
• role

Customer entity

• Name
• Phone number
• Email address
• Purchase record

In this given information, it is identified that the organisation is storing sales transaction with the customers’ information. For instance, ‘purchase record’ is representing the sales transaction but it is initially identified as part of the customer entity. Data can be stored effectively after normalization process.

Entity-relationship model

It is a high-level model used to define data elements and their relationship within a system. In entity-relationship model, database structure is demonstrated as a diagram and can be used to develop conceptual design for the target system. However, entity-relationship model at high-level may not normalized entities and therefore, it is useful only to develop a basic understanding how the entities are related to each other.

This model is showing three entities and their relationship. For example, customer purchases an outfit from inventory. Similarly, employee records a purchase record and customer’s information during sales. At this high-level modelling, primary keys are not given and not even assumed. However, primary key for employee is identified as their identification number (ID).

Normalization up to third normal form

Normalization helps to eliminate redundancy and anomalies in database. Following are three normalization forms and their implementation to the above entity relationship model:

First normal form : According to this rule, a relation must contain atomic values. It also means that each record is unique. For example, all the attributes in inventory entity are atomic where it is assumed that there is a separate record if a product is considered as two or more types of the outfit. For instance, there should be two records for a product if it is considered a dress as well as night suit. For employee and customer, all the attributes are atomic except the purchase record. A customer may have one or more purchase records and therefore, separate records are required to store each purchase.

Second normal form : a relation must be in first normal form to be in second normal form and all the non-key attributes should be functionally dependent on the primary key. Note that a primary key is an attribute that can be used to identify each record uniquely in an entity. Considering this, the following entities and attributes are identified for second normal form:

• Employee ( ID , name, role, date_of_joining, hourly_rate)
• Inventory ( outfit_ID , outfit_type, price, designer, size, stock_status)
• Customer (customerID, name, phone, email, purchase_records)

Third normal form : a relationship must follow the rules of second normal form and there should not be any transitive dependency among attributes. Here, transitive dependency means the attributes belong to a different entity can be separated and access using reference keys. Assuming some common attributes for the ‘purchase record’, following entities are identified in third normal form:

• Employee ( ID , name, role, date_of_joining, hourly_rate)
• Inventory ( outfit_ID , outfit_type, price, designerID , size, stock_status)
• Customer ( customerID , name, phone, email)
• Purchas record ( recordID , customerID, outfitID, quantity, amount, purchase_date, employeeID )Designer ( designer , designer_name)

Relational database model

The presented model indicates the relationship between various data items. Following relations are demonstrated in below model:

• If a person is a customer then the person must have one or more purchase records. Also, one or many records may be associated to single customer. No two customers may become the part of a record.
• An employee may performance none or many purchase recordings. However, it is not compulsory. For example, a manager may not record any purchasing as it is done by a salesperson.

• A designer may have designed none or many products. There is optional zero to optional many relationship. It means, the designer table may contain the designers who have not yet started to provide their products but registered in database.
• At list one outfit must be the part of each purchase record. However, there might be many purchase records for a customer and they can contain many outfits.

Customer

Designer

Find More of Our Work:- MIS602 File-Based Framework Assignment Sample

For Additional Information, Read Our Blog On:- How To Use Google Sites to Make and Learn Web Designing?

Find Assignment Help Here:- IT Assignment Sample