Entity Identification Process & ERD

One of the first processes performed in developing a new database and an associated application is to identify the entities involved in the current business operations and system (if aa current system exists). The process often involves the collection of data about the related company, the current processes being used, what the future processes need to do, and what data is currently being used, stored or discarded.

Using the business descriptions and report descriptions below from the project specifications, generate two lists of the following type:

1)A list of all the entities you can identify. Do Not be picky but keep in mind that an entity is a Person, Place, Thing or Event. You are basically looking for things that you can describe.

2)A list of all the attributes you can identify. Again, do not be picky with the list. Remeber that you are looking for those things that will describe the entities you identified in the first list. This list will obviously be longer than the first list.

The following is an excerpt from the current project:

Company Description-

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 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 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 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 email in the future.

Ms. Smith has visions about how the system will operate daily (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.

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, channel type, and rating (see table 2). Once this is done, the programming 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 are entered into the system. 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 is 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.

Required Reports:

A list of the programs on all channels for aa 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.

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.

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.

AA 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.

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.

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 packaage. Once again, remember that this is going to be a date driven report.

Turn the following in for a Lab grade:

Your document with the two lists. Be sure your name is on the document.

Create an ERD diagram for the above scenario.