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.

Data Storage1

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.

Data Storage2

  • 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.

Attribute

Data type

Data format

Size

Description

Example

ID

Number

NNNNN

5

Unique identification number

12345

Name

Text

-

50

Full name of employee

Jones John

Phone

Text

NNNNNNNNNN

10

Phone number of customers containing only digits without country code.

0123456789

Email

Text

-

100

Email address of the customer

hello@gmail.com

Customer

Attribute

Data type

Data format

Size

Description

Example

ID

Number

NNNNN

5

Unique identification number

12345

Name

Text

-

50

Full name of employee

Jones John

Role

Text

-

50

Role of the employee such as cashier, salesperson and inventory manager

Cashier

Date_of_joining

Date

DD/MM/YYYY

-

Date of joining

01-01-2000

Hourly_rate

Decimal

00.00

2,2

Hourly rate with maximum two decimal digits.

35.75

Designer

Attribute

Data type

Data format

Size

Description

Example

ID

Number

NNNNN

5

Unique identification number

12345

Name

Text

-

50

Name of the designer/ firm

Designer XYZ

 

Attribute

Data type

Data format

Size

Description

Example

Outfit_ID

Number

NNNNN

5

Unique identification number

12345

Outfit_type

Text

-

20

Type of the outfit such as dress, top etc.

Skirt

Price

Decimal

3,2

-

Price of the outfit

199.9

Designer_ID

Number

NNNNN

5

ID of the designer for the outfit

12345

Size

Text

-

10

Size of the outfit

Small

Stock_status

Number

NNN

3

Available stock units of the outfit

50

Attribute

Data type

Data format

Size

Description

Example

RecordID

Number

NNNNN

5

Unique identification number

12345

customerID

Number

NNNNN

5

Customer identification number

12345

outfit_ID

Number

NNNNN

5

Outfit identification number

12345

quanity

Number

NN

2

Quantities of the outfit purchased

12

amount

Decimal

4, 2

-

Total amount of transaction for a purchase

205.49

purchase_date

Date

DD/MM/YYYY

-

Date of purchasing

01-01-2000

employee_ID

Number

NNNNN

5

Employee who performed transaction

2

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

Hey MAS, I need Assignment Sample of

Distinctive Advantage

  • 21 Step Quality Check
  • 24/7 Customer Support
  • Live Expert Sessions
  • 100% Plagiarism Free Content
  • 0% Use Of AI
  • Guaranteed On-Time Delivery
  • Confidential & Secure
  • Free Comprehensive Resources
  • Money Back Guarantee
  • PHD Level Experts

All-Inclusive Success Package

  • Turnitin Report

    FREE $10.00
  • Non-AI Content Report

    FREE $9.00
  • Expert Session

    FREE $35.00
  • Topic Selection

    FREE $40.00
  • DOI Links

    FREE $25.00
  • Unlimited Revision

    FREE $75.00
  • Editing/Proofreading

    FREE $90.00
  • Bibliography Page

    FREE $25.00
  • Get Instant Quote

Enjoy HD Grade Assignments without overpayingSave More. Score Better. Bless YOU!

Order Now

My Assignment Services- Whatsapp Get 50% + 20% EXTRAAADiscount on WhatsApp