Question

The owner of Horne Design Wallcoverings has hired you as a consultant to design a database management system for his chain of three stores that sell wallpaper and accessories. He would like to track sales, customers and employees. After initial meeting with him, you have developed a list of business rules and specifications to begin the design of an E-R Model. The list of business rules are:

• Customers place orders through a branch
• A customer may place many orders
• A Customer does not always have to order though the same branch all the time
• Customers may have one or more accounts; although they may also have no accounts.
• Customer may be a member or non member with Home Design Wallcoverings. As a member, the customer can get certain amount of discount.
• Accounts related information need to be recorded such as balance, last payment date, last payment amount and accounts type
• A branch may have many customers
• A branch may sell all items or may only sell certain items
• Orders are composed of one or more items
• Items may be sold by one or more branches
• An item can be composed of multiple items: for example a dining room wall covering set may consists of wallpaper and borders
• Home Design Wallcoverings employs 54 employees
• Each employee have one or more dependents
• Employees can have one or more skills

Questions / Requirement:

You are required to design a database solution for the above case study by coming out with the following parts:

1. Identify the entities and provides the possible attributes for each entity.
2. Draw Enhanced Entity Relationship Diagram using either Crow’s Foot model. Specify primary keys and cardinality in your diagram.
3. In cases where the above requirement specification is ambiguous, state clearly the assumptions which you made for justifying your modelling choices
4. Create the tables identified from the above using SQL code (only need the codes to create the tables) – it’s fine even the tables are not go through normalization.

Answer 1

Entity - Entity is anything like place, person etc. which has attributes to describe them.

Entity and attributes for given scenario:

ER diagram using crow foot notation

Here primary keys are underlined for each entity.

Tables for given ER

/* Create a table called customer */
CREATE TABLE customer(cust_Id integer PRIMARY KEY, cust_name char(10),phone int);

/* Create a table called order */
CREATE TABLE order(ord_Id integer PRIMARY KEY,ord_date date, quantity int);

/* Create a table called employee */
CREATE TABLE employee(emp_Id integer PRIMARY KEY, emp_name char(10),skill char(20));

/* Create a table called homeDesign */
CREATE TABLE homeDesign(H_location char(20) );

/* Create a table called account */
CREATE TABLE account(a_Id integer PRIMARY KEY, a_type char(10),balance decimal, last_pay_date date);

/* Create a table called item */
CREATE TABLE item(item_id integer PRIMARY KEY, item_type char(10),price decimal, description char(50));