You have been hired to design a database for an Internet Service Provider (ISP) named “MegaNet”. The database must keep track of the details of the MegaNet’s plans, customers, staff and support jobs....

You have been hired to design a database for an Internet Service Provider (ISP) named “MegaNet”. The database must keep track of the details of the MegaNet’s plans, customers, staff and support jobs. You have the following information about the way MegaNet operates:
Details of the plans that MegaNet offers must be recorded. This includes a plan ID, plan name, monthly cost, monthly download allowance (“quota”) in GB, maximum download speed and shaped download speed (speed after exceeding monthly allowance) in Mbps.
Details of the Internet access types (e.g. ADSL or FTTN) used in the plans must be recorded. This includes an access type ID, access type name and maximum download speed in Mbps.
There can be many plans for each access type, but a plan only uses one access type.
Details of customers must be recorded. This includes a username, first name, last name and contact number. The database must record the customer’s current Internet plan.
Details of MegaNet’s support staff must be recorded. This includes a staff ID, first name, last name, contact number and hire date.
Each staff member is assigned a level to reflect their expertise and experience and determines their hourly pay rate. Details of levels must be recorded, including a level number, level name and hourly pay rate.
 Each staff member’s current level must be recorded.
Details of support jobs must be recorded, i.e. instances where a customer has contacted a staff member regarding problem with their service. Details of support jobs must include:
 A support job ID.
 The username of the customer and the staff ID of the staff member involved.
 The date and time that the job was lodged (default of current date/time).
 A short summary of the problem (100 character limit).
Based on the details above, you are required to draw both a logical E-R diagram for this database and then a corresponding physical E-R diagram. Clearly show all cardinality, primary and foreign keys, attributes and relationships as appropriate.
Adhere to the distinctions between logical and physical E-R diagrams defined in Lecture 3.
Use enhanced E-R model notation where/if appropriate.
Task 4 – Advanced Entity-Relationship Modelling (9 marks)
The ISP from Task 3 now requires you to update and expand the previous database you designed for them, in order to record some additional information primarily regarding support jobs. You must update and expand your E-R diagrams from Task 3 in order to incorporate these new requirements:
To ensure better training of support staff, MegaNet has implemented a mentor system. A staff member may be mentored by another staff member. A staff member can only have one mentor, although a staff member can mentor many other staff members.
 The database must record which staff are mentoring which other staff.
MegaNet wishes to store a list of different statuses for support jobs to keep track of whether they are resolved. A status ID and status name are all that need to be stored.
 The database must record the current status of each support job.
MegaNet also wishes to store two new details of support jobs:
 A resolution date (the date and time when the issue was resolved).
 The staff ID of the staff member who resolved the issue.
(both of these details would remain blank until the job is resolved)
To keep track of updates regarding support jobs, MegaNet wishes to be able to store notes about them. A support job can have many notes associated with it. Each note consists of:
 A note ID.
 The date and time that the note was added (default of current date/time).
 The support job ID of the job that the note regards.
 The staff ID of the staff member who added the note.
 The textual content of the note itself.
Create updated and expanded versions of your logical and physical E-R diagrams from Task 3 that incorporate these new requirements. You should not need to modify very much from your solution to Task 3 to complete this task; you mainly need to expand the diagrams by adding the new attributes, entities and relationships.