Study the following case study and answer the questions that are based thereon:

ABC DELIVERY SPECIALISTS is a courier service that is fast-paced and competitive. ABC DELIVERY SPECIALISTS pride themselves on two things:
1) Getting their deliveries to their customers on time, and
2) Ensuring that that their packages are delivered safely.

After a current customer analysis, 18% of their customers stated they received their packages late, and 11% said they’ve received their parcels damaged. The company at present do not make use of a structured database system and feel that implementing a database system will give them the ability to improve and monitor their customers, employees, drivers, billings and deliveries. At present all the data is stored in a flat file system, which needs to be imported into a new database management system.

You have been contracted to perform the design and implementation of a database system for ABC DELIVERY SPECIALISTS. You have decided to implement a solution using Oracle database.

The data in flat files has been provided in the following comma separated value files (csv):
• Billing.csv
• Customers.csv
• Delivery_Items.csv
• Driver.csv
• Driver_Deliveries.csv
• Staff.csv
• Vehicle.csv

Question 1 (Marks: 20)
The management of ABC DELIVERY SPECIALISTS requires an Entity Relationship Diagram (ERD) based on the flat files that have been supplied. Your ERD must incorporate all the entities and attributes supplied, as well as establishing the relationships between the entities. A proper design is essential to ensure that the data in the flat files can be imported successfully.

Refer to Marking Rubric at the end of assignment

Question 2 (Marks: 10)

Using the Entity Relationship Diagram (ERD) you have created in Question 1, create the tables, and import the values supplied in each of the csv files provided.

Refer to Marking Rubric at the end of assignment

Question 3 (Marks: 10)
ABC DELIVERY SPECIALISTS requires you to create users and grant privileges to use the database. You have been provided with the following details:

Q.3.1 Create the required users and permissions.

Note: Additional research might be required in line with the Oracle XE version installed. (8)

Q.3.2 Explain the importance of separation of duties for the different users. (2)
Refer to Marking Rubric at the end of assignment

Question 4 (Marks: 15)

Q.4.1 Management of ABC DELIVERY SPECIALISTS require a report of the driver’s name, driver code, vehicle identification number (VIN) and mileage of the vehicle that was used in the deliveries. In your solution only display the results for a vehicle
that has a mileage of less than 80 000 miles. (10)

Sample Output:

DRIVER: Jono, Mvuyisi
CODE: EC1
VIN NUMBER: 1ZA35868540 MILEAGE: 79058
Q.4.2 In Q.4.1, you were required to create a solution that will not be called but rather run on ad hoc basis.

Explain what database solution you have used to create the report and justify its benefit to ABC DELIVERY SPECIALISTS. (5)

Question 5 (Marks: 25)

Q.5.1 The Operations Manager of ABC DELIVERY SPECIALISTS requires a report of the staff member who has processed the most deliveries. For the report, create a PL/SQL query indicating the Staff member’s id, first name, surname, and a count of deliveries that they have processed.
Sample Output:
———————————————-
DRIVER ID: 51014
FIRST NAME: Jabu
SURNAME: Xolani DELIVERIES PROCESSED: 2
———————————————-

Q.5.2 PL/SQL blocks are broken into three (3) sections. Describe each of these sections and how each section has been implemented in the query you created in Q.5.1.
Q.5.3 You would want to ensure that management are able to pull this report on their own.
Q.5.3.1 You have suggested an alternative solution using a View. Explain to the Operations Manager how a View works and state a benefit of this solution.
Q.5.3.2 Modify your code in Q.5.1 to use a View. Add necessary comments to aid understanding for the managers.

Question 6 (Marks: 20)
Q.6.1 You have been tasked with creating a PL/SQL query that will produce a report displaying the customer’s full name, phone number, customer email, number of bills allocated to the customer. In your solution, also display the customer rating. The customer receives an Elite rating if the number of bills is greater than or equal to 4. Otherwise, the customer receives a standard rating. All Elite customers will receive a note “Thank you for being our valued customer”.

Refer to Marking Rubric at the end of assignment (15)

Sample Output:

CUSTOMER NAME: Bob, Smith CUSTOMER PHONE: 0877277521
CUSTOMER EMAIL: bobs@isat.com NumberOfBills: 2

CUSTOMER NAME: Sam, Hendricks CUSTOMER PHONE: 0863257857
CUSTOMER EMAIL: shen@mcom.co.za NumberOfBills: 5 (ELITE)
Thank you for being our valued customer

CUSTOMER NAME: Jeff, Jones CUSTOMER PHONE: 0612547895
CUSTOMER EMAIL: jj@isat.co.za NumberOfBills: 4 (ELITE)
Thank you for being our valued customer

CUSTOMER NAME: Andre, Kerk CUSTOMER PHONE: 0827238521
CUSTOMER EMAIL: akerk@mcal.co.za NumberOfBills: 2

CUSTOMER NAME: Larry, Clark CUSTOMER PHONE: 0834567891
CUSTOMER EMAIL: larc@mcom.co.za
NumberOfBills: 3

CUSTOMER NAME: Nicole, Kerk CUSTOMER PHONE: 0827238529
CUSTOMER EMAIL: nk@mcal.co.za NumberOfBills: 1

CUSTOMER NAME: Mel, Hendricks CUSTOMER PHONE: 0863257852
CUSTOMER EMAIL: melh@mcom.co.za NumberOfBills: 1

CUSTOMER NAME: Stuart, Jones CUSTOMER PHONE: 0827238521
CUSTOMER EMAIL: sjones@mcal.co.za NumberOfBills: 1

CUSTOMER NAME: Catherine, Smith CUSTOMER PHONE: 0877277523
CUSTOMER EMAIL: cath@isat.com NumberOfBills: 1

CUSTOMER NAME: Josh, Maverick CUSTOMER PHONE: 0612547897
CUSTOMER EMAIL: joshm@isat.co.za NumberOfBills: 1

Q.6.2 Analyse your code in Q.6.1; As an experienced database developer, suggest
and briefly explain any two improvements to the code or any two alternatives you could use to produce the same report.

Answers to Above Questions on Advanced Databases

Answer 1: The development of Entity Relationship Diagram (ERD) for the management of ABC DELIVERY SPECIALISTS is performed as follows:

Hire the programming experts of Student Life Saviour South Africa to get assistance in all the questions above.