CIS336 Group Project All Task (1,2,3,4,5) with screenshots – Guaranteed 100% Score

Lab Price = $50
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

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.
Project Deliverables Schedule:
• Task 1 (Due at the end of Week 3)
• Task 2 (Due at the end of Week 4)
• Task 3 (Due at the end of Week 5)
• Task 4 (Due at the end of Week 6)
• Task 5 (Due at the end of Week 7)
See Syllabus/”Due Dates for Assignments & Exams” for due date information.
Each project task deliverable will be graded on the merits of that task. Tasks 1 through 3 have a weight of 30 points each. Task 4 has a weight of 45 points and Task 5 has a weight of 65 points, with the project as a whole worth a total of 200 points (20% of your total grade points). Each task has a defined due date and any task submitted late will be assessed a 10-point reduction in that task’s grade.
Project teams will be assigned at the end of Week 1 or the beginning of Week 2. We will discuss each aspect of the project in more detail within the threads. This may be the first time that you have had to work as a member of a virtual project team, so if you are apprehensive about this, it is understandable. We will discuss different aspects of working in teams as we go through the class.

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, Texas, 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 ten 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 program scheduling for MiniQuest is done manually based on the day the program is to air and the length of the program provided by the supplier. Once this is done, the guides are produced. When the Weekly Showing guide is produced, MiniQuest finds it difficult to work out when a program finishes so that it can determine when the next program starts. The Weekly Showing guide is currently mailed to all customers, but Ms. Smith wants to e-mail it in the future to all customers (who get a free e-mail account).
• In addition to the programming problem, MiniQuest is experiencing the need to keep track of its customers in a better manner (it currently has paper cards and is doing this manually). Furious customers have been reporting that they have been getting fewer channels than they have paid for. As such, MiniQuest wants 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 e-mail. 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 package(s) the customer has subscribed to (this determines the channels the customer receives – see Table 1), the amount to be paid monthly (based on the package(s) subscribed to), and whether the bill has been paid. Bills are currently sent on a monthly basis. These are currently sent by mail, but will be sent via e-mail 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 feel 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 Monday’s 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, channel type, and rating (see table 2). Once this is done, the programming for each channel for that day is developed.
• Each customer is e-mailed 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 are entered into the system. As a customer subscribes for the service, the details are recorded and the favorite channel is identified.
• Billing is done on a 1st and 15th billing cycle (based on when a customer signs up) and is due 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 (for example, 54 days from the bill generation date) 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 in them will need to be sufficient to enable these tasks to be completed. A guide is given with each report regarding 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. This is a date-driven report, therefore it should only display programs for a single date specified.
• 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 favorite channel, address, and minimal billing details. This report is to be date driven, so you will need to specify a date in your query.
• Report 4 – A list of all the customers made inactive on a specific date. This should show enough details about the customer to allow contact with the customer, the reason he or she was made inactive, and the user who made them inactive. 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 who picked 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 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 MiniQuest. Since MiniQuest has stored historical data in the current manual system it uses, 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 data). That data which cannot be changed is noted below. The data which can be changed will 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 channels offered in each package
• The package prices
Table 1: Program Packages (This data is not to be changed)
Movie News Lifestyle Documentary Sports Cost/Month
Movies Galore 1, 2 3 6 7 9 $30
News Globe 1 3, 4 6 7 9 $30
Total Watcher 1, 2 3 6 7, 8 9, 10 $40
Couch Potato 1, 2 3, 4 5, 6 7, 8 9, 10 $50
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 times rather than trying to manipulate the system date and time.
The “Movie Galore” Package Guide – Monday July 23rd
Channel Show Time Program Short Description Audience Rating
1 1:00 am Starry Wars the Trilogy Science Fiction epic about the generation of a brave new world Mature Audiences Recommended
5:30 am Gomer Piles the Hero Comedy showing the advantages of being an army sergeant Parental Guidance
2:00 pm Twinkle Toes in Paris Romantic Drama about a Scottish Ballet Dancer making his fortune Mature Audiences Recommended
5:00 pm Segall in New York Horror Action: Terrorists have taken over New York Cemetery. Can it be brought back under control? Restricted – Adults Only
2 1:55 am Fantasia Disney Favorite General Exhibition
4:30 am Dances with Fairies New age movie showing the interaction of humans in the afterlife Mature Audiences Recommended
8:30 pm Speed 34 The Space Shuttle is taken over by aliens. Bruce Willis to fix the problem Mature Audience 15+
11:30 pm Prettier Woman Drama: A woman learns to deal with the truth in her life Mature Audience 15+
Table 3: Sample Listing of Suppliers (You may add to this)
Supplier Name Supplier Address Contact Person Contact Phone
NBC 10880 Wilshire Blvd # 1200
Los Angeles, CA Jeff King (310) 369-5231
ABC Los Angeles, CA Jack Caverisi (310) 840-9966
FOX 10960 Wilshire Blvd
Los Angeles, CA Janis Carmikel (310) 235-5233
WARNER 3701 W Oak St
Burbank CA Simon Philips (310)532-6588
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. You can use the program start time and the length to calculate the end time for each program. For obvious reasons, one show can’t start before another ends on the same channel.
Program Name Length Short Description Channel Type Rating Code
Sale of the Year 24 Game Show Lifestyle Lifestyle G
Opera by the Nile 240 Luciano Pavorotti sings opera on the Nile Documentary G
Fly Killers are Back 175 Flies attack the city once again; the Bygone Brigade are back Movie MA
NBC Tonight 360 News from around the world News PG
The Shopping Show 120 The sites to see when you shope online Lifestyle G
Angel Fire 120 Angel is back on the streets Movie R
Lawn Bowl Championships 400 Live from Canberra Sport G
Mud Wrestling 120 The mud pits come alive for the muddy challenge Sport M

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, but will only be shown once per channel per day
• 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

You and your team will be required to create a project notebook (the notebook you submit will be a Zip file with all of your documentation in it). This project notebook will contain the final, polished version of each task deliverable for the project. The notebook will be submitted as Task 5 for grading in Week 7.
Each project task has a due date associated with it and will be graded based on the requirements for that task. Any task not submitted for grading by the time it is due will be recorded as late and will result in a 10-point deduction from that task’s grade. Each task has a prescribed format and you will be expected to adhere to it unless special permission has been given by the instructor to deviate from it. Be sure you have read the project specifications (all of it) and understand what is expected of you and your database at the end of this project before starting on the first task. The more knowledge and understanding of the business needs and requirements you have in the beginning will help minimize the number of times you have to make changes as you go along.

Task 1 (Due at the end of Week 3)

Present a detailed data model for the scenario. Your data model should include an ERD, and metadata chart (data dictionary). You can create your data model using Microsoft Visio, which you will have access to through the Citrix link and Microsoft Excel as parts of Microsoft Office. Other tools may be used as long as the output is legible and conforms to standard format (I will not be able to grade the data model if I cannot tell what it is supposed to be!). The ERD should be formatted to allow printing on a single 8×10 page.
Deliverables for this task:
• Task 1 comprised of an Entity/Relationship Diagram (ERD) and Data Dictionary.
• Team member responsibilities document outlining contributions by team members to this deliverable.

Task 2 (Due at the end of Week 4)

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 TEAM_X_TASK2.TXT, where X represents your team letter. 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. DROP TABLE statements should be in reverse order of CREATE TABLE statements due to integrity constraints. After testing and verifying that all of your create statements work, create a spooled output file with the SET ECHO ON session command set so that your code and the results will show.
Deliverables for this task:
• Any revisions of Task 1 based on feedback from your instructor.
• Task 2 comprised of the CREATE TABLE script file.
• An output file showing that the script works.
• Team member responsibilities document outlining contributions by team members to this deliverable.

Task 3 (Due at the end of Week 5)

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 your corrected script file from Task 2. 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 any 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 table’s primary key field. Name this file TEAM_X_TASK3.TXT, where X represents your team letter. For example, Team A would name the file TEAM_A_TASK3.TXT.
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:
• Any revisions of Task 2 based on feedback from your instructor.
• Task 3 comprised of the script file with corrections from Task 2 and the INSERT statements.
• An output file showing that the script works.
• Team member responsibilities document outlining contributions by team members to this deliverable.

Task 4 (Due at the end of Week 6)

Develop a query that will identify and mark inactive those customers that have bills overdue by more than 30 days (this can usually 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 your group project SQL script, and name the file TEAM_X_TASK4.TXT. This should be the same script that contains your DROP TABLE, CREATE TABLE, and INSERT statements. Once you have verified that your SQL statement or statements work, create a spooled output showing the results of your statements.
Deliverables for this task:
1. Any revisions of task 3 based on feedback from your instructor.
2. Task 4 comprised of the complete script file (DROP, CREATE, INSERT, SELECT, and UPDATE statements) and the output file showing that it works.
3. Team member responsibilities document outlining contributions by team member to this deliverable.

Task 5 (Due at the end of Week 7)

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
Create a heading of REPORTING STATEMENTS as a comment at the bottom of your group project database script file. Add the six report queries to this area. In instances where you are asked to allow for user input, please specify an appropriate value based upon the data in your script. For example, Report 1 is a DATE driven report; therefore, you should use a single date in your WHERE clause to display the information. Task 5 is the final task for the project and is designed to demonstrate that you have adequately satisfied the requirements of the project. The following is what you are to submit for Task 5:
Your virtual notebook containing :
1. The final polished version of the Data Dictionary, ERD, and complete database script. The script should include DROP TABLE and CREATE TABLE statements, appropriate CONSTRAINTS, INSERT statements, UPDATE statement, and 6 REPORT QUERIES.
2. A Group Responsibilities Log.
3. A “Lessons Learned” document with a paragraph from each team member stating the key things learned through the course of this project. Team members have the option of submitting their Lessons Learned independent of the group.
4. A Team Evaluation Form from each team member. This form can be found in Doc Sharing. To maintain confidentiality, this should be submitted independently by each team member to their own Week 7: Group Project drop box.

Submission of Tasks

Each task has a due date attached to it. Each task deliverable must include all documents as outlined under that task’s specifications. Each task deliverable must also have a “Group Responsibilities” document included that outlines what each team member’s responsibility was for that task (usually part of the specifications listing). Tasks are to be submitted by the team leader no later than midnight Sunday of the week they are due and are to be submitted via the Drop Box on the course website as a single zipped file. Any late work will be noted as such and points will be deducted from that task’s overall grade.

Team Member Responsibilities

Project teams will be formed at the end of Week 1 or the beginning of Week 2 and each team will be expected to select a project leader for the team. Teams may choose to rotate team leadership on a weekly basis or choose a leader for the duration of the course. It is the project leader’s responsibility to be the first line of communication between the project team and the instructor. The project leader needs to be willing and able to deal with any problems or issues that might arise within his or her team. Each team member has a responsibility to not only contribute to the overall project end, but to also assist each fellow team member.

Relevant Material
Screenshots
CIS336 Group Project Task 3 - Spool File
CIS336 Group Project Task 3 – Spool File

CIS336 Group Project Task 2- Spool File
CIS336 Group Project Task 2- Spool File
CIS336 Group Project Task 1 - Data Dictionary
CIS336 Group Project Task 1 – Data Dictionary
CIS336 Group Project Task 1 - ERD
CIS336 Group Project Task 1 – ERD
CIS336 Group Project Task 4 - Spool File
CIS336 Group Project Task 4 – Spool File
CIS336 Task 5 - Report 1
CIS336 Task 5 – Report 1

CIS336 Task 5 - Report 6
CIS336 Task 5 – Report 6
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $50
Please feel free to send us your queries at: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply