Overview
The goal of the assignment is that you practice and explore further the concepts of Oracle 11g’s database-orientated programming language, PL/SQL, as well as of data analysis techniques for decision-making. In short, you will be asked to (1) create a database for a telecom, (2) write PL/SQL procedures and functions to populate and query that database and (3) design interesting and useful data analysis queries over that database. You have a choice of implementing (1) and (2) as pure-relational or object-relational solutions.
Tasks
Create the Telecom Database
Your first task is to create a database, using Oracle 11g SQL, to store data on customers and phone calls made by customers of a Telecom, according to the following requirements. The company holds data about their customers, whose attributes include:
- telephone area code and number
- first name
- last name
- date-of-birth
Each phone call made by a telecom’s customer is characterised by:
- customer that makes the phone call. There must be a foreign key constraint that relates the customer information in this table to that in the customer table above.
- area code and telephone number of the receiver of the phone call
- start date and time
- end date and time
- charge per minute in pounds and pence
The following shows the categorisation of a phone call receiver according to their area code’s prefix:
- 2 of 663IS6302 Assignment 2 February 5, 2012
- 01 or 02: local/national number. Ex.: 01612 338866.
- 075, 077, 078, 079: mobile phone number. Ex.: 07747 556647.
- 0800: free number. Ex.: 08002 223344.
- 0845, 0870: special service. Ex.: 08451 423456.
- 08442 to 08449: 5p special service. Ex.: 08444 404404.
- 08712 to 08719: 10p special service. Ex.: 08713 457893.
- 090, 091, 098: premium rate special service. Ex.: 09119 229595.
You must implement database constraints to ensure that no phone calls with receivers that do not fall into the categories above are inserted into the database. It is required that a table is maintained for premium rate special services (i.e., those 09 numbers above) to which a customer may make phone calls. Each premium rate special service is described by the following features:
- area code (prefixed by either 090, 091 or 098) and telephone number
- name
- cost per minute (pound and pence)
Populate the Customers and Premium Rate Special Services Tables
You are required to write an SQL script that populates (i.e., inserts rows into) the tables Customers and Premium Rate Special Services explained in section 2.1. The script must add (not fewer, not more than) ten distinct customers and five distinct premium rate special services.
Implement a PL/SQL Function to Obtain Call Cost
You must implement a PL/SQL function that returns the cost per minute of a phone call according to the following rules based on the prefix of the receiver’s area code (please note that the element in bold should be the parameter of the function):
- local and national numbers: 1p
- mobile phone numbers: 10p
- free numbers: 0p
- special services: 2p
- 5p special services: 5p
- 10p special services: 10p
- premium rate special service: cost according to table as described in section 2.1. 3
Implement Additional PL/SQL Functions
You must also implement PL/SQL functions that compute and return the following:
- age of a customer, based on date-of-birth and current date
- duration of a phone call rounded up to the nearest minute
- the total cost of phone calls of a particular customer for a range of dates
- the total cost of phone calls of a particular customer this month (from the first date of the current month up to the current date).
Please note that the elements in bold should be the parameters of the functions, and that phone call costs should be expressed in pounds and pence.
Devise and Apply Data Analysis Queries on the Telecom Data
Your third task is to write SQL expressions for the following data analysis queries on the Telecom database you created (see Section 2.1):
- Aggregate the calls by the telephone number they were made from, presenting the total number of phone calls, total number of minutes, and the total charge.
- Based on the above, show the top 5 callers with respect to the total number of minutes.
- Show the total number of calls made to normal telephone numbers (callsNonPremimum), the total number of calls made to premium numbers (callsPremium) and their difference (callsNonPremium - callsPremium), per month. Additionally, write another two data analysis queries of the types top/bottom, interor intra- partitioning over your Telecom database. You must provide a justification for each query you write, taking into consideration the assessment factors shown in Section 4.1
