Describe Concepts of database-orientated programming language, PL/SQL, as well as of data analysis techniques for decision-making. Write PL/SQL procedures and functions to populate and query that database and design interesting and useful data analysis queries over that database.
1 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:
_ 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).
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.
Implement Additional PL/SQL Functions
You must also implement PL/SQL functions that compute and return the following:
1. age of a customer, based on date-of-birth and current date;
2. duration of a phone call rounded up to the nearest minute;
3. the total cost of phone calls of a particular customer for a range of dates;
4. 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:
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.
2. Based on the above, show the top 5 callers with respect to the total number of minutes.
3. 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.