CP2404 Assignment 1 Solution

Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others.


Assignment Part 1 – Conceptual Database Modelling
Assessment Weight: 20%

Rationale
This assignment has been designed to assess students’ ability to model a database, by constructing an entity-relationship diagram for a particular business scenario. This assignment addresses the following learning objectives for this subject:
• Develop a database model using the entity-relationship model
• Apply the techniques of normalisation

Requirements (Tasks)
2) Based on the descriptions draw a fully labeled and implementable Entity-Relationship Diagram (ERD). Include all entities, relationships, optionalities, connectivities, cardinalities and constraints. You must use Crow’s foot notation and MySQL Workbench (or other software) to create the ERD. A Hand-drawn ERD will NOT be accepted. A sample ERD can be found in Appendix A of this document. (Note: The ERD created using the drawing tool (e.g. MySQL Workbench) will need to be saved (or exported) as an image file and then be included in your document file to be submitted)
3) A summary to describe the major justifications, assumptions and limitations related to your database design. For example:
• Assumption/justifications for optionalitiy, connectivities, constraints data type and data domain; and
• Special cases or data integrity issues that cannot be handled.

Make sure that your final submission shows consistent design in ERD (Task 2).


Submission
• You need to submit a single document file (MS Word or PDF format) to LearnJCU.
The document should include all the answers for Task 1-3. Please name the file as LastnameFirstnameA1.doc or LastnameFirstnameA1.pdf.
• Timestamp shown on LearnJCU assignment submission will be used to determine if the assignment is late or not. Refer to the subject guide for the policy for late submission.

Business Description (Scenario)

As branch managers they need to manage staff schedules, collect tuition fees, and manage other daily activities including recruitment of new students.
Managers also gets commission for any new students who join their branch.
Each manager is expected to send a report to the head office in New York every quarter. This report summarizes the total revenues collected, the total expenses such as staff salary, materials purchased, food items for students etc. As TPS does not run an established database system, currently managers fill out a paper form and mail it back to head office. Many managers have complained that preparing this report is a very difficult and time-consuming process. Also, the managers at the head-office also have expressed concerns about the accuracy and verifiability of the reports.
To reduce these concerns and to improve the ease and efficiency with which the branch managers conduct their daily business, the company is proposing to develop a centralized database that can be used by the managers to track the daily business of their branch and to prepare their reports. You have been asked to design a database that satisfies general business description and various user requirements summarized above.
TPS also showed you some samples of various forms and reports to enhance your modeling job. The first example is of a spreadsheet to keep track of their staff details:
The second example is of a spreadsheet used to track students:
The third is an example of tracking subjects and teaching staff:
Subject Staff ID # Level Taught
Mathematics S110 Level 1-6
Science S120 Level 3-6
English S120 Level 1-6

The fourth is an example of tracking classroom allocation:
Classroom Day Time Subject Level


Finally, here is an example of the report that each branch manager must turn in to the main office quarterly. The sample below is from the Malaysia Branch office.
TPS Quarterly Report

Branch Location: Malaysia
1234 Kuala Lumpur,
Address: Malaysia
Quarter: 3
Manager In-Charge: XXX
Revenues
Total Fees Collected $ 60,000.00
Expenses
Utilities $ 6,000. 00
Maintenance $ 500.00
Stationery $ 2,000.00
Wages $ 39,000.00
Food Items $ 4,000.00
Other Expenses $ 1,000.00
Recreation $ 500.0 0
Total Expenses $ 53,000.00
Net Income/Loss $ 7,000.00



There are more business descriptions/requirements provided by TPS and they are summarized here:

- New students who register must specify their preference for in-house subject tuition (Eg. English, Math, Science) and the level.
- Each branch is paid a one-time 10% commission on new registered student based on their monthly fees.
- Branches which achieves a minimum enrolled students (10) per month receives a monthly bonus of $1000.
- Only 10 students per class per subject
- Each branch can organize 2 outdoor activities per quarter. Activities ranges from educational tours, recreational activities, etc.
- Each branch will allocate 3 staff in-charge to manage students’ daily activities including classroom activities, schedules, monthly test and keep track of students’ overall progress as well as reporting their progress to the parents/guardian (quarterly).
- TPS also runs seminars and activities with some schools on a quarterly basis. This is managed by the branch manager, and this is a form of recruitment exercise where they share the type of activities, students’ interactions and students’ progress with teachers and parents.
- For maintenance, the branches keep a list of local maintenance companies (e.g. electricians, plumbers, contractors) contracted to TPS and the branch manager selects one from the list to allocate a maintenance job. All maintenance records will be kept and reported to the head office (quarterly)
- Each branch is equipped with a number of pre-installed equipment like fire alarms, air-conditioners, and some classes are equipped with laptops for students’ access and learning. Details of each asset item is recorded and being managed. These asset items are inspected and maintenance periodically.










APPENDIX A ERD



(Note about the example ERD provided above:

- You are not required to specify obvious cardinalities like (0,M), (1,1), (1,M) etc. You are required to specify
specific cardinalites which are not presented using crow’s foot notation like (1,3), (4,10), (10,M) etc.

Note:
If you want to present the supertype-subtype relationships using an extended-ERD (EERD), you will need to draw the necessary EERD notations using facilities provided by the drawing tool software you use. Alternatively, you can present the supertype-subtype relationships using multiple 1:1 relationships having same PKs for all related entities and describe the special relationships in your document.

CP2404/CP5633 Assignment 1

Assignment – Part 1 Database Modelling: Marking Criteria


Criteria Exemplary Good Satisfactory Limited Very Limited
Solution Outline A brief discussion of your solution outlining the approach taken. (9, 10)
The solution is accurate, logical and desirable. The discussion of solution is appropriate and approaches for the solution chosen is properly detailed.
(7, 8)
Exhibits aspects of exemplary (left) and satisfactory
(right) (5, 6)
The solution is discussed and the solution chosen is described, but is insufficient in accurateness, logic, depth, etc. (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited
(right) (0, 1)
Not attempted or the discussion of the solution attempted is mostly incorrect or not relevant
Database
Modelling
Entities
(presented in Business
Rules and ERD) (9, 10)
All required entities (based on the business scenario given) are included both in Business Rules and in ERD.
All entities are in 3NF
(7, 8)
Exhibits aspects of exemplary (left) and satisfactory
(right) (5, 6)
All entities included are in 3NF but some required entities are missing either in Business Rules
or in ERD
OR
Most required entities are included but some entities are not in 3NF (need to further normalized) (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited
(right) (0, 1)
Most required entities are missing and most included entities are not in 3NF
Attributes (Fields)
(presented in ERD)

(9, 10)
Attributes in each entity are relevant to desired information and correctly designed.
All required attributes are included. Attributes are named correctly and logically (7, 8)
Exhibits aspects of exemplary (left) and satisfactory
(right) (5, 6)
Some of required attributes are missing but most of included attributes are named appropriately
OR
Most of required attributes are
included but named
inappropriately
(2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited
(right) (0, 1)
Most of required attributes are missing or incorrectly designed (not relevant, or included in a wrong entity etc.)
Connectivities
(presented in Business
Rules and ERD) (9, 10)
All relationships are correctly designed and presented both in Business Rules and in ERD. (7, 8)
Exhibits aspects of exemplary (left) and satisfactory (right) (5, 6)
Most but not all (more than 50%)
relationships are correctly designed and presented in Business Rules and ERD (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
Most of required relationships between entities are missing or incorrectly designed.
Cardinalities &
Optionalities
(presented in Business
Rules and ERD) (9, 10)
All cardinalities and optionalities are appropriately designed and presented both in Business Rules and in ERD. (7, 8)
Exhibits aspects of exemplary (left) and satisfactory
(right) (5, 6)
Most but not all (more than 50%) cardinalities and optionalities are correctly designed and presented in Business Rules and ERD (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited
(right) (0, 1)
Most of required cardinalities and optionalities are missing or incorrectly designed.
Relationship Names (presented in ERD) (5)
All relationships presented in ERD are appropriately named and well corresponded to Business Rules (4)
Exhibits aspects of exemplary (left) and satisfactory (right) (3)
Most but not all (more than 50%) relationships are named appropriately (2)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
Most relationship names are missing
- Page 6 -
CP2404/CP5633 Assignment 1

Relationship Strength (presented in ERD) (5)
All relationship strength (weak or strong) is appropriately designed and correctly presented in ERD (dotted line or solid line) (4)
Exhibits aspects of exemplary (left) and satisfactory (right) (3)
Most but not all (more than 50%) relationships are presented correctly corresponding with its strength (2)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
Most relationship strengths are not presented appropriately
Primary keys (9, 10)
All primary keys are correctly identified and unique (7, 8)
Exhibits aspects of exemplary (left) and satisfactory (right) (5, 6)
Most but not all (more than 50%) primary keys are correctly identified and unique (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
Most primary keys are not properly identified or unique
Foreign keys (9, 10)
All necessary foreign keys are correctly identified (7, 8)
Exhibits aspects of exemplary (left) and satisfactory (right) (5, 6)
Most but not all (more than 50%) necessary foreign keys are
correctly identified (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
Most foreign keys are not correctly identified
Presentation ERD (9,10)
All components included in ERD are neatly and clearly presented without unnecessary complexity (7, 8)
Exhibits aspects of exemplary (left) and satisfactory (right) (5, 6)
Some parts in ERD are not clear
inappropriate display of components (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited (right) (0, 1)
ERD is not included or attempted but mostly inappropriate
Assumptions and
Justifications (9, 10)
All necessary assumptions and justifications are appropriately made and listed.
All assumptions are correctly
incorporated in ERD (7, 8)
Exhibits aspects of exemplary (left) and satisfactory
(right) (5, 6)
Most but not all assumptions are made appropriately or correctly incorporated in ERD (2, 3, 4)
Exhibits aspects of satisfactory (left) and very limited
(right) (0, 1)
Assumptions are not listed or attempted but mostly incorrect or incorporated in
ERD

- Page 7 -