This is a narrative description of the main project for CSC 432. The details may change over time, so please visit the online version again.

You are to build an information system for a company called The Psychotic Network (TPN). TPN is a somewhat unusual service provider. Its main line of business to dupe gullible people into carrying on long conversations over a 900 telephone number.

The following is a partial description of TPN's modus operandi. You are to design and implement the information system that will be used to help manage information about TPN's customers, staff, advertising, and tie-ins with other disreputable providers.

Here's how we speculate that TPN achieves its business goals. It advertises over TV or otherwise to reach people who are the likeliest to be gullible. When such a person calls in at TPN's 900 number, the TPN system obtains the caller's telephone number (unless this information is blocked by the caller). From the telephone number, they bring up a lot of information about the household from which the call is being made. They also determine if it is new customer. When the call is forwarded to an operator, the operator can judge from the first few words roughly how old the caller is and their sex. From this additional information, the operator knows the caller's name and crucial information such as age marital status and number of children. Disclosing a few of these details to the caller helps build trust, which can than be exploited in a long meaningless conversation.

What follow are a bunch of statements about The Psychotic Network that you may glean from talking to the people who run it or work for it. They are not organized in any particular manner. In doing this project, you might discover that not every statement has to be explicitly captured in the database. Part of the modeling effort it to decide what to keep and what to discard.

1. The workers in TPN are employees (with benefits, monthly, eligible for bonus) or contractors (without benefits, hourly, ineligible for bonus).
2. Each worker has at least one skill. Most of the workers are talkers; some are managers.
3. The corporate titles are techie, call screener, junior talker, talker, senior talker, and manager. The call screeners
4. The skills of the talkers are based on the kinds of callers they can deal with - young, middle-aged, or old; male or female; stressed, disturbed, or neurotic; needing sympathy or advice. Additionally, some talkers can handle new callers (these are the hardest). A talker who specializes in new callers will pass them along to a less glib talker with the right specialty.
5. For each caller, TPN keeps information about their preferred talkers, their levels of gullibility and desperation, health, religious affiliation, emotional status, hopes, and worries. For each caller, TPN also keeps relevant information about their close associates, family members, and coworkers.
6. TPN also has information about the address for each telephone number from where they may receive a call. TPN has demographic information about each zipcode: average income, family size, education, crime rates, broken homes.
7. TPN sometimes discloses its callers' particulars to a scam artist of the appropriate religious denomination. TPN gets a share of the profit made by the scam artist.
8. Each worker is assigned to a home branch, and has an office in that branch. Workers who have been with TPN for more than 6 months can work from home talking to old customers; new calls are always handled within the office.
9. For each worker, TPN keeps information about their regular customers and the average minutes of conversation (per call and per caller) sorted by caller type. TPN also stores the workers' preferred schedules, so they can be contacted if there is a shortfall.
10. Each employee has a mentor (someone who has been in the company longer and can advise them about how to handle various kinds of callers).
11. Any employee who contributes 5% of his or her salary to a retirement account gets an additional 5% as matching from the company. The cumulative value in each account gets an interest of 1% every month (we wish). The employees' and the company's contributions and the interest they accrue are kept separate.
12. The vesting schedule for retirement benefits is 25% a year, and 100% after 4 years of service. This means that if an employee leaves the company before they have completed an year of employment, they get nothing from the money put in by the company; if they complete just one year, they get 25%, and so on. The money that is forfeited by an employee upon quitting the job early is distributed equally among those who are employed at that time (it is added to their company contribution subaccount). The amount of money contributed by the employee belongs to the employee and is given to them when the leave.

Tasks and Operations

The following are the four major kinds of tasks that need to be performed using your database. Each task potentially consists of a number of operations. An "operation" is something that corresponds to a separate action. For example, calculating the retirement account value, determining the money given to an employee when they leave, and redistributing the forfeited money are three separate operations.

Each student should write about the same number of application programs, but it is up to each team to decide how to allocate the effort.

1. Hire and fire; process resignations. Give raise or bonus to employees; renegotiate contract with contractor. Change rank. If an office has a shortfall of some skill and a worker of the right skills is available, then suggest this worker's name.
2. For each talker, find the average number of minutes per call that they achieve. For each talker, and for each of their regular customers, find how well they do with that customer compared to other talkers talking to the same customer.
3. For each new caller, display as much information as is available from the telephone and zipcode tables. For an old caller, display previously stored information. When a caller mentions another person (e.g., their sister), quickly bring up information on that person and add more information online. Record (and later find) any advice given to the caller.
4. Calculate the total retirement account value for each employee each month. When they leave, determine their contribution and the interest accrued on it. Also, determine how much money is forfeited, and distribute it among the remaining employees.

Assumptions

In doing your project, you will need to make additional assumptions as well as identify the potential inconsistencies and resolve them. Any reasonable assumptions are fine, but they must be documented in your reports. You can consult with the TA if you have questions about the assumptions.

Disclaimer

The above is all a fictitious scenario meant to be taken in humor as you learn about databases. If any part of it bothers you, please let me know.