CSE2/4DBF (2018) – Assignment 1


CSE2/4DBF (2018) – Assignment 1, Part 1 Page 2/7

To get your Assignment/Homework solutions;

Simply Click ORDER NOW and your paper details. Our support team will review the assignment(s) and assign the right expert whose specialization is same to yours to complete it within your deadline. Our Editor(s) will then review the completed paper (to ensure that it is answered accordingly) before we email you a complete paper
______________________________________________________________________________________
PROBLEM DESCRIPTION:
House2Home Franchise Application Database System
House2Home is a retail chain that operates throughout Australia and some foreign countries. Their main
business revenue is generated from selling computers, home electronics and furniture. Originally, the central
operational team manages all of the House2Home retail shops. However, due to the business changes, the
company increasingly franchise out their departments to different franchisee teams. So far, the franchise
application process has been handled using a paper based system. With the continuous growth of the retail
chain, House2Home wants to automate the application and selection process in Australia. For the automated
web-based system to work, they need an underlying database that can cater for the relevant data. They have
asked for a solution that will work best to support their business process, which is described below.
The company has many stores spread across Australia. To keep track of each store, the company wants to
store information such as unique store identification number, store address (including street & number,
suburb, postcode and state), weekdays operating hours and weekend operating hours.
The departments of each store are autonomous. A department is the unit of operation that is managed by a
franchisee team. A store can have several departments such as computers, electronics, beddings, furniture
and so on. A department with a unique id belongs to exactly one store. Other information pertaining to a
department are title of the department (i.e. computers) and number of employees.
The franchise application database also needs to store information about employees of House2Home as some
of them are eligible to submit a team application for franchise of a department. Each employee has an
employee id. The name, phone number, email address, home address, date-of-birth and gender of an
employee are also recorded by the company for convenience. Based on the nature of employment, there are
three types of employees in House2Home, i.e. casual, part-time and full-time. Casual employees have a casual
hourly rate. Casual and part-time employees are both not eligible for paid annual leave. Only full-time
employees are eligible for paid annual leave and the number of days they are eligible for are recorded against
each employee along with their annual salary. For each part-time employee, the weekly working hours and
annual salary are recorded.
Each employee is also categorized as either a local or a central employee. Local employees are those who
work for the department but do not have any affiliation with the central management. Central employees are
assigned to a department by the House2Home central management. The central employees have a central
authentication number or CANumber apart from their departmental employee ID. The CANumber is
recorded in the database for the convenience of the franchisee team. The central employees are not allowed
to operate the sales software used in the store. Only the local employees are allocated with EFTPOS IDs
using which they can process sales for customers in the store. Employees are assigned to a department, not a
store and are managed by that department’s franchisees.
As a private limited company, House2Home has many shareholders who are direct stakeholders of the
company’s business. According to company policy, only a full-time employee or a shareholder can apply
to become part of the franchisee team of a department. Also, a full-time employee must have at least 10
years of experience with House2Home before joining a franchisee team. Information about every shareholder
is stored in the records including their ID, name, address, number of shares and so on. The central
management stores further information about which shares are owned by a particular shareholder but this
does not concern the system that deals with the franchise application process.
A number of team members, who are either full-time employees or shareholders, must form a team before
applying for a franchise. Each team member is assigned a team member ID to be tracked easily. A team is
also identified by an ID and headed by one member of the team. Each team has a name that the team members
can pick. According to current policies, a franchisee team must have at least 2 members and can have at most
CSE2/4DBF (2018) – Assignment 1, Part 1 Page 3/7
______________________________________________________________________________________
4 members. A franchise team member can belong to a number of teams. The percentage of profit share of a
team member in a particular team is also recorded.
Each franchise application, made for a particular department by a specific team, is currently stored in a file.
An application has a unique application number, application date, date on which a decision was made and
the application status. A team can lodge any number of applications but each application can be made for
exactly one department. According to the current business process, a team submits a “Franchise Application
Form” (Appendix A) to formally apply for a franchise of a department. The application form contains
information about the team and individual team members including the information about the team leader.
When a new department opens up, the formal launch of the department’s business is held-off by the
management until a suitable franchise is assigned.
When the assessments of different applications are finalized, a department is assigned a franchisee team, who
take all the responsibilities of that department. There can be many departments across Australia that a single
franchisee team manages, but each department of a store is managed by one franchisee team.
There are some financial aspects associated with the franchise application process. For instance, when a team
is selected to take up the responsibilities of a department, they need to pay an initial franchise fee of AU$
100,000. Other transactions include commission on sale and maintenance fees. All the transactions between
a franchisee team and the central management are handled through company accounts. Each department has
a designated account through which franchisee pay different fees and receive commissions on sales. An
account is identified by an account number and has an account name. Moreover, the account balance indicates
how much money the management owes to the franchise team (positive balance) and how much money the
franchise team owes to the management (negative balance).
Although the details of sales transactions are not part of the franchise database, a weekly sales report is still
kept in the database for the commission calculations. Each department generates a weekly sales report on
Sundays based on the sales transactions for the week. Each report contains a report ID, the start and end dates
of the sale period, total amount of sales made by the department and a commission amount (based on
commission percentage) to be paid to the franchise team. Apart from these top-level details, the report also
contains information about the bestselling brand for the week. Information includes brand ID and name.
Additionally, most sold item type, average sales price for that type is also mentioned in the report. At the end
of each week, a single employee of the department is awarded with a weekly bonus based on his/her
performance for the week. Hence, the employee who has made the maximum amount of sales for the week
also appears in the report. For a comprehensive view, a sample weekly sales report is given in Appendix B.
The first weekly sales report of a newly launched department is generated at the end of the first week of
launch. It is important for the database designer to understand that any information regarding brands, item
types and employee performance is being extracted from the (separate) sales database (which we do not cover
here). Hence, the referential integrity constraints, for these items only, is not a concern within the scope of
the franchise application database.
CSE2/4DBF (2018) – Assignment 1, Part 1 Page 4/7
______________________________________________________________________________________
TASKS:
You are required to develop an EER model for the above problem description. The EER should
contain all necessary information such as entities, attributes, primary keys, relationships (including
specialization/generalization if any), cardinalities, and participation (including (min, max)). All
specialization/generalization and union type must be represented accordingly, instead being treated
as simple association relationships. Any assumptions should also be stated clearly. If assumptions
are made, it is important to make sure that the assumptions reflect possible real practice for a
particular industry and do not contradict with the problem description above. Assignment should
be typed, not written/drawn by hand. Use any software to draw figures in your assignment.
[100%]
CSE2/4DBF (2018) – Assignment 1, Part 1 Page 5/7
______________________________________________________________________________________
Appendix A: Franchise Application Form
Franchise Application Form
Each team member should read the following instructions before filling up this form:
*Upon approval of the application the team needs to deposit the initial franchise fee of AU $100,000 into a
designated account within 7 business days.
*This application form should be treated in a confidential manner and should not be disclosed outside the
members of the team.
Franchisee Information (Section A)
Team name:
Number of team members:
Application date:
Store ID:
Store address:
Department:
(Tick one)

Computers
Electronics
Furniture
Bedding
Details of Team Members (Section B)
Please note that the percentage of share for each member is the percentage of profit share within the
team. Also, upon approval of the franchise, each member will be assigned a team member ID and the
team leader will be notified.
Member 1:
Name:
S.H ID/Employee ID:
Percentage of share:
Previous or existing franchise: Yes/No
Previous or existing franchise team ID:
By signing below this team member consents to
his/her percentage of share mentioned above.
Signature:
Member 2:
Name:
S.H ID/Employee ID:
Percentage of share:
Previous or existing franchise: Yes/No
Previous or existing franchise team ID:
By signing below this team member consents to
his/her percentage of share mentioned above.
Signature:
Member 3:
Name:
S.H ID/Employee ID:
Percentage of share:
Previous or existing franchise: Yes/No
Previous or existing franchise team ID:
By signing below this team member consents to
his/her percentage of share mentioned above.
Signature:
Member 4:
Name:
S.H ID/Employee ID:
Percentage of share:
Previous or existing franchise: Yes/No
Previous or existing franchise team ID:
By signing below this team member consents to
his/her percentage of share mentioned above.
Signature:
Team leader (Please circle member number): M1–M2–M3–M4
Team leader signat
CSE2/4DBF (2018) – Assignment 1, Part 1 Page 6/7
______________________________________________________________________________________
Approval and ID Assignment (Section C: Administrative Use Only)
Application number:
Instruction for approver: Please cross off the previous decision status and update the decision date
appropriately.
Decision status:
Granted
Under review
Rejected
Decision date:
Team ID:
(If franchise is granted)
Team member IDs will be generated based on team ID. Each team member ID will be team ID followed by
member number. E.g. TeamIDM1, TeamIDM2, etc.
Approver:
Approver signatu
CSE2/4DBF (2018) – Assignment 1, Part 1 Page 7/7
______________________________________________________________________________________
Appendix B: Weekly Sales Report
Weekly Sales Report
Net Sales and Franchisee Commissions
Report ID: R100
Department ID: D001
Store ID: S001
Store address: 121 Bell Street, Preston, VIC 3072
Week start date: 29-01-2018
Week end date:
(also report generation date)
04-02-2018
Total sale amount: $150,000
Commission rate: 5.00%
Total commission amount to be paid: $7,500
Highest Sold Brand
Brand ID: BSAM1
Brand name: Samsung
Total brand sale amount: $80,000
Highest Sold Item Type
Type ID: T001
Type name: Television
Total sale amount for type: $65,000
Best Performing Employee
Employee ID: E005
Employee name: Jennifer Walker
Total sale amount: $10,000
Weekly bonus amount: $500

To get your Assignment/Homework solutions;

Simply Click ORDER NOW and your paper details. Our support team will review the assignment(s) and assign the right expert whose specialization is same to yours to complete it within your deadline. Our Editor(s) will then review the completed paper (to ensure that it is answered accordingly) before we email you a complete paper

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: