Course: Database subject: Computer Science Topic: bill management system

CSC1335: Project Bill Management System We are in process to create a Bill Management System for mobile telecom company. Therefore, we need to design and create the database of the system to store all needed data and get the necessary info about customers and their invoices and apply penalties if needed. Our database should store data of: Customer: Every customer has a unique identifier (Assume that every customer might have Max 3 numbers), address and ID number and other KYC (Know Your Customer) info. Rates: The Rates of calls (Land Lines and International). SMS, data (4G) and Penalties. You might have day rates and night rates. (Specify the time, e.g. from 8pm till 6am) Cell Numbers: Each number has the registration date for the owner (customers might sell the number), payment cycle and the number might be deactivated based on grace period. Every number might have several services (Call barring, Clip alert, etc...) and each service has its starting date. Cell Numbers Pool: all available numbers with their classification (Gold, Silver &Normal) Countries: Each country has its own Code and description and if it is allowed to call this destination Calls: All calls have starting and ending date and time, destination number Services Each service has its own price and frequency (Monthly, daily, etc...) Recharge Cards: Each card has a serial number, its price, expiry date, amount to be recharged and number of days. ALT 1 Normal 1 No Spac... Heading 1 Heading 2 Subtitle Subtle Em... Emphasis Title Inte. Styles Steps to Be Done Step 1: Data Modeling - Create an ERD Step 2: Map ERD into relational model Step 3: Create the tables and fill them with sample records Step 5: SQL Queries according to the requirements below SQL Queries: You should get the following information from your database: List top 5 cell numbers' calls for a specific customer's number List all numbers which did not pay last invoice yet Decrease the rates of international Night rates by 20% List all customers that have more than one line(cell number) > Give the name the country which received the largest number of calls for specific month and not cycle. List number of prepaid lines and postpaid cards for each customer > Get the invoice for specific Cell number and specific month(cycle). Write 5 additional queries that you find useful for this application. They should be complex queries (e.g. union, intersection, minus, nested queries...)