520574 Oracle SQL

Overview The first thing you need to do is read through the entire project document completely. Keep in mind that the overall objective of this project is to be able to deliver a working database that will be able to meet the desired outcomes as outlined in the complete specifications. The following project deliverables schedule outlines which weeks deliverables will be due. A more detailed overview of each deliverable will be presented later in this document. Case Project Overview Terri Smith has contacted you with regard to an advertisement you had in the Age promoting your company's expertise in database design and implementation. Ms Smith is the Managing Director of MiniQuest, a broadcasting company based in Melbourne that broadcasts television via cable to a six county area in Texas. From your discussion with Ms Smith, you have identified the following information about the company: • MiniQuest currently has 10 channels of five distinct types (2 Movie Channels, 2 News Channels, 2 Lifestyle Channels, 2 Documentary Channels, and 2 Sport Channels). Each channel shows programs on a 24-hour basis. Programs can appear on either of the two channels in a category or on both channels at any time. • A guide (Weekly Showing) is produced that lists all the programs on each channel on a daily basis. Currently all the programming for MiniQuest is done manually based on the day the program is to air and the length of the program provided by the supplier. One this is done then the guides are produced. When the Weekly Showing guide is produced they find it difficult to work out when a program finishes so that they can determine when the next program starts. The Weekly Showing guide is currently mailed to all customers, but Ms Smith wants to email it in the future to all customers (who get a free email account). • In addition to the programming problem, MiniQuest is experiencing the need to keep track of their customers in a better manner (they currently have paper cards and are doing this manually). Furious customers have been reporting that they have been getting fewer channels than they have paid for. As such, they want to incorporate the customer information and the programming information into one database that will produce a daily guide of shows to be sent to customers via Email. This guide could then be personalized to only list the shows that the customer is paying for. In addition, only those channels being paid for will be broadcast to that customer's home. • Ms Smith has instigated a customer survey to determine which channel is the favorite for each customer. The result of this survey needs to be stored in the database along with the date of the survey. Billing information also needs to be stored in the database. This information includes the billing date (either the 1st or 15th of the month depending on when the customer signed up), the amount to be paid monthly (based on the package subscribed to), whether the bill has been paid and the package that has been paid for (this determines the channels the customer receives - see Table 1). Bills are currently sent on a monthly basis. These are currently sent by mail, but will be sent via email in the future. • Ms Smith has visions about how the system will operate on a daily basis (This information is included so that you can get a "feeling" for how the business operates. It should also give you some idea about how the interface would operate between the operator and your database. YOU ARE NOT REQUIRED TO CONSTRUCT AN INTERFACE FOR THIS PROJECT). Each day the following may happen • The programs for the same day of the following week are entered into the database (if the day was Monday then you would be entering in next Mondays programs). The details to be entered come from the list of programs provided by the suppliers. Supplier lists generally include the program name, length, short description, type (channel type), and rating (see table 2). Once this is done, the program for each channel for that day is developed. • Each customer is emailed his or her individual program guide for the next day (this is not in the scope of this project and will not be addressed). New Customers may arrive. As a customer subscribes for the service, their details are recorded and their favorite channel is identified. • Billing is done on a 1st and 15th billing cycle (based on when a customer signs up) and are due payable in 24 days. The customer's bills are generated on the first or fifteenth day of each month and are mailed out. • Customers pay their bills and their records are updated. • Customers who have a bill overdue by more than 30 days (i.e., 54 days from the day it was mailed out) are delinquent and their service is discontinued. These customers are not removed from the files but they are marked as inactive. Needed reports At the end of each day, Ms Smith needs to have a number of reports produced. These reports will be used for a variety of tasks within the organization. As a result, the information contained on them will need to be sufficient to enable these tasks to be completed. A guide is given with each report as to the information it contains. Some of the reports will be date driven, meaning that a date will need to be entered or provided upon which the results of the report will be determined. Reports that will be needed for the project • Report 1 - A list of the programs on all channels for a specific day showing the channel number,supplier, package, program name, rating code, and show time. This will be similar to a program guide only not package specific and should allow a date to be specified for the report. • Report 2 - A sample program guide showing the channel number, show time, program name, and rating description. For the purposes of this project, your report should be package id specific (based on a given package id) and you only need to demonstrate a single package id. Your report should include all channels associated with the specified package. • Report 3 - A list of all new customers signed up on a specific day. This should show enough details about the customer including their favorite channel, address, and minimal billing details. This report is to be date driven so you will need to be able to specify a date when you run the report. • Report 4 - A list of all the customers deleted on a specified date. This should show enough details about the customer to allow contact with the customer, the reason for the deletion and the user who carried out the deletion. Again, keep in mind that this is a date driven report. • Report 5 - A list of each channel (both channel name and channel number) and a count of the number of customers that have that channel as a favorite channel. • Report 6 - A summary of sales for any given day, categorized by Package type. (i.e., the total sales written on the specified day for each package type subscribed to by customers, not bills paid). This report should give details about each package type, the package price, the number of packages sold, and then the total sales amount for that package. Once again, remember that this is going to be a date driven report. Sample Data The sample data that follows represents examples of the current data being used by Prime Time Cable. Since Prime Time Cable has stored historical data in the current manual system they use, some of the data you will be working with is not subject to revision or change (i.e., don't go making up your own stuff). That data which cannot be changed is noted below. The data can be changed and will also be noted as such. Sample Data for the project (take note of what can or cannot be changed) DO NOT change: • The package names (Movies Galore, News Globe, Total Watcher, Couch Potato) • The channel types (Movie, News, Lifestyle, Documentary, Sports) • The channel numbers or package channels • The package Prices Table 1: Program Packages (This data is not to be changed) Table 2: Sample Program Guide Extract You are at liberty to create your own program names, starting times, program descriptions, and wording for ratings descriptions. For the purpose of reporting, you may find it easier to hardcode the programs starting time rather than trying to manipulate the system date and time. The "Movie Freak" Package Guide Monday July 23rd Table 3: Sample listing of suppliers (You may add to this) Table 4: Sample Supplier Listing of programs for NBC Again, you are at liberty to create your own program names, length, and descriptions. Do not change the channel type. The program length is in minutes, and in a system where you could use a procedural language program you would be able to use the program length to determine beginning and ending times for that program based on the ending time of the previous program. In this project you will have to manually calculate this data. Some known assumptions • Customers subscribe to packages • A single customer may or may not subscribe to more than one package • Programs can show on multiple channels • A single channel can exist in more than one package • A program is supplied with a date to be shown and a program length • Programs have ratings What your team will be required to do Task 1 Present a detailed data model for the scenario. You can create your data model using Microsoft Visio, and Microsoft Excel, which comes with Microsoft Office. Other tools may be used as long as the output is legible and conforms to standard format. Your data model should include a minimum of an ERD, and metadata chart (data dictionary). Deliverables for this task: • Task 1 comprised of an Entity/Relationship Diagram (ERD), and Data Dictionary. Task 2 Using Oracle, develop a database for MiniQuest based on your data model. The database should contain all the aspects outlined in your data model. Be sure to use your data dictionary when creating your tables. To submit this task you need to create a file in notepad called TASK2.TXT. In this file create a heading called CREATE STATEMENTS and then develop the CREATE TABLE statements required to create your database tables. Be sure to include the DROP TABLE statements at the top of your file so that you can reuse the file. After testing and verifying that all of your create statements work, create a spooled output file or HTML file (depending on whether you are using SQL*Plus or iSQL*Plus) with the SET ECHO ON session command set so that your code and the results will show. Deliverables for this task: • Task 2 comprised of the CREATE TABLE script file and the output file showing that it works. Task 3 Develop the SQL statements to insert records into the database. You should ensure that the data in the database is sufficient to allow for each of the reports requested by Ms Smith to be tested. You need to show a minimum of three insert queries for each database table that you have created. Some tables will obviously require more to ensure that all of the required data is in the database (i.e., the packages table, program table, etc). To submit this task, create a comment heading called INSERT STATEMENTS in an SQL document. Add your INSERT statements for each table after this heading. Keep in mind that you will need to insert data into the tables in the same order that the tables were created. This will minimize and integrity constraint errors you might encounter. Keep in mind also that any values being inserted into a field defined as a foreign key field must first exist in a previous tables primary key field. After testing your statements to verify that your data will insert into your tables, create a spooled output file with the SET ECHO ON session command set for submitting. NOTE : Although you are only being required to have three records per table in most of your tables for this deliverable, you will need considerable more data in your tables for the final project. Deliverables for this task: • Task 3 comprised of the INSERT statements script file and the output file showing that it works. Task 4 Develop a query that will identify and mark for deletion those customers that have bills overdue by more than 14 days (you may need a series of SQL statements to accomplish this, but it usually can be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system. To submit this task, create a heading called MODIFY STATEMENTS in an SQL script and name the file INACTIVE_CUST.TXT. Once you have verified that your SQL statement or statements work, create a spooled or HTML output showing the results of your statements. Deliverables for this task: • Task 4 comprised of the [login to view URL] file and the output file showing that it works. Task 5 Develop the queries to support each of the reports that are required by Ms Smith. It is expected that the information returned by the queries is adequate to be used in the reporting process. Make sure your output contains enough information! The following is a recommendation and the MINIMUM amount of data you should have access to: • Customer Table: A minimum of 12 customers (this will allow several channels to have more than one customer selecting it as a favorite channel) • Billing Table: Records to support the above customers • Program Table: A minimum of 25 programs (your program guide should have enough programs for various channels to support the program guide report) • Other tables: A sufficient amount of data to support the above when reports are run Show each query and its output in your submission (you will have a minimum of six queries). To submit this task, create a heading REPORTING STATEMENTS in the SQL document and create a spooled or HTML output of your queries and the result set for each.