CSE2DBF Assignment Help
Assignment 1 – Part 1 Assignment help
PROBLEM DESCRIPTION:
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. The information regarding who leads a team are also recorded. 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 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).
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/union 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. However, make sure to follow the notations introduced in this subject.
NOTE: For this part of the assignment, you are NOT required to perform normalization on the user-views presented in the appendix. The form is just a supplementary source of information. You are also NOT required to perform transformation of EER into relational tables.
[100%]
Leave A Comment