data generator

Random-Data-Generator

    A. First Edition

    This is the first edition of my "random-data-generator" which is a small tool for my comp353 project. In the

    database we need large amount of data, and I have to write this program to generate the sql script.

    B.The problem

Concordia University

Department of Computer Science

COMP 353 ¨C Databases

Summer 2004

Main Project: QAR Management System

Project Description:

The QAR (Quebec Auto Repair) requires your team to design and implement a relational

database management system for its chain of auto repair shops in Quebec. Each QAR

shop has two basic types of employees, blue-collar workers and office people including a

manager. Manager is only responsible to manage the shop and plan daily schedule for the

blue-collar workers. Blue-collar workers are divided into apprentices and senior workers.

Each QAR shop offers four types of car services: oil change, tire rotation and mounting,

brake service, and replacement of mufflers. Junior apprentices are only allowed to change

oil. After their graduation to senior apprentices they are also allowed to do tire rotation

and mounting. The senior workers can provide any of the four car services. Each car

service takes exactly one hour and is offered for a fixed price (excluding applicable

taxes). However, the prices for the service types are different and increase in the

following order: oil change, tire rotation and mounting, brake service, replacement of

mufflers. Each employee works 8 hours a day. Each repair is performed by one bluecollar

worker and must be completed on the same day. The blue-collar workers get paid

weekly; the others get paid biweekly. The blue-collar workers have a fixed salary and

also get a 1.5% commission for each service.

The auto parts are stored in a remote warehouse and delivered to the shop within a day.

Only the parts necessary for one day¡¯s work can be stored in the shop.

Each customer receives a confirmation listing the ordered services and price quote. When

customers pick up their car, they receive a bill listing all services and their price.

Customers have to pay their bill immediately. They can use cash, debit cards, or credit

cards. In case of paying with cash or debit cards they receive a 3% discount off the total

bill.

Implementation Requirement:

Use the Oracle DBMS to implement the required system. Your model QAR shop has 1

boss, 2 office people, 2 junior and 2 senior apprentices, and 2 senior workers. The QAR

shop has an average of 40 orders per day. These can be roughly divided into ~55% oil

changes, ~15% tire rotation and mounting, ~20% break service, and ~10% muffler

replacements. On each day customers pay with cash (~25%), debit (~35%), and credit

cards (~40%).

Create a database, collect appropriate data and store them in the database. Your QAR

shop starts on May 01, 2004. It is open from Monday to Friday from 08:00 until 17:00

with a launch break from 12:00 to 13:00. The database should include data until June 10,

2004. Unfortunately, 1 junior apprentice quits her/his job after the first week and 1 new

senior apprentice is immediately hired as replacement. The requirements mentioned

above have to be observed when you populate the database but not for drive-in

customers. You should show the data integrity of your database at the demo time. You

have to use HTML and PHP to build interfaces supporting the following queries and

transactions.

1. Create forms for manipulating (entity) tables: insertion, deletion and update of

tuples.

2. Give a list of all workers who made an oil change on any day of the week and

where the customer paid with cash.

3. Compute the schedule of the next day for workers. List the percentage of

unallocated work time.

4. Compute pay cheques (salaries) for workers at each Friday.

5. Report on the car services planned for the next day.

6. Produce an order for supplies needed for the next day by considering what is

currently in stock and what will be needed for the next day. Make sure the local

store will have 20% more in stock than required for the next day.

7. Create an HTML form for new customers without an appointment. The form

should ask for the required services and return the next 2 available time slots

(name of worker, status). The customer have the option to sign up one of the time

slots.

8. Create an HTML form for registered customers with an appointment. The form

should identify the customer and return the car services planned for this customer.

Customers have the option to cancel their appointment(s).

9. Generate a detailed report for all services provided for a given car during a

specified period of time.

What you should hand in:

The deliverables consist of the followings:

Phase I: Design

Develop an E/R diagram for the entities and their relationships. Make logical/intelligent

assumption(s) to clarify the entities and their relationships. The design should be as

compact as possible without sacrificing the required objectives. Convert the E/R diagram

into a relational database scheme. Make refinements to the scheme (if necessary).

Identify the various integrity constraints in the scheme such as primary keys and foreign

keys, functional dependencies and referential integrity constraints. Make sure that your

database scheme is at least in 3NF.

Any ambiguities in this problem statement will have to be resolved. Some of these could

be done via discussions that hopefully lead to design decisions. Your report should give

rational explanations for all assumptions and decisions.

Phase II: Implementation

Write the SQL scripts to create the tables with the schemas and constraints developed in

Phase I. Populate your tables with data, numerous and varied enough to test and

demonstrate the functions implemented. Build an interface, using HTML and PHP, for

each of the functions listed earlier. A working version of the project should be

demonstrated at Oracle lab before your Lab Instructor during the last week of the term.

Every member of the group MUST be present during this demo. During demo, you must

hand-in a Report containing all the details of the design and implementation of your

Project work. If you need further clarification about deliverables, you need to consult

with your lab instructor.

Note 1:

Source code of the application can be copied into Floppy/CD or printed on

papers (based on your preference and suitability) and hand-in along with Report.

Note 2:

Your project report must be properly bound in a folder (or binder) with your

names, ID¡¯s and the identification of the team clearly appearing on the cover.

Inappropriate submissions will be penalized.

Note 3:

Give details of each member¡¯s contribution in this project, that is, give a table

that shows who (which student) did which part of the project. It is wise to be realistic

since the lab instructors will also have to evaluate each team member.

¡¡

C.The idea of program
¡¡

I want to write it simple and fast because I have no time.

    D.The major functions

    There are few points to mention.

    ¡¡

    E.Further improvement

    ¡¡

    F.File listing

    1. main.cpp (main)

    file name: main.cpp

    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>
    #include <time.h>

    //using namespace std;

    enum Choices
    {PureNumber, PureAlpha, Mixed};

    const int EmpNumber=6;
    const int MaxOrderNum=1500;
    const int CustNumber=20;
    const int MaxCarNumber=5;
    const int MaxCarLicence=6;
    const int MaxNameLength=11;
    const int MaxFirstName=5;
    const int MaxLastName=5;
    const int MaxCustLicence=9;
    const int ServiceTypeNumber=4;
    const int MaxDayNumber=40;
    const int StartingDay=1;
    const int TimeSlotNumber=8;
    const int DifficultyLevelNumber=3;
    const int MaxModalChoice=5;
    const int MaxPhoneLength=12;

    char* payment_method[3]={"cash", "debit", "credi"};

    //double distribution[ServiceTypeNumber]={0.55, 0.15, 0.20, 0.1};
    int theDate;
    int order_id=301;
    int payment_id=100;
    long emp_sin[EmpNumber]=
    {
    	123456789,  234567891 , 345678912 , 456789123, 567891234, 678912345
    };
    char* modalChoices[MaxModalChoice]={"Ford","Suzuki","Toyota","GM","Honda"};

    char buffer[256];
    char str[256];
    char carLicence[MaxCarLicence+1];
    char custLicence[MaxCustLicence+1];
    char phone[MaxPhoneLength+1];

    char* generate(int length, Choices choice);
    char* genName();
    char* genCar();
    char* genLicence();
    char* genOrder();
    char* genDate(int day);
    char* genPhone();
    char* insertCar();
    char* insertCustomer();
    char* insertSchedule();
    char* genPayment();
    char ch=10;
    char ret[2];

    int main()
    {
    	srand(time(0));
    	ret[0]=ch;
    	ret[1]='\0';

    	for (int i=0; i<MaxOrderNum; i++)
    	{
    		printf("%s%c", insertCustomer(), ch);
    		for (int j=0; j<1; j++)
    		{
    			printf("%s%c", genOrder(), ch);
    			for (int k=0; k<2; k++)
    			{
    				printf("%s%c", insertCar(), ch);
    				for (int l=0; l<2; l++)
    				{
    					printf("%s%c", insertSchedule(), ch);
    				}
    			}
    			printf("%s%c", genPayment(), ch);
    			printf("%s%c", genPayment(), ch);//split payment

    		}
    	}
    	return 0;
    }

    char* genPayment()
    {
    	int method;
    	strcpy(str, "insert into payment(payment_id, payment_method, order_id,");
    	strcat(str, ret);
    	strcat(str, "payment_amount) values(");
    	strcat(str, ret);
    	itoa(payment_id++, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ",'");
    	method=rand()%100;
    	if (method<25)
    	{
    		method=0;
    	}
    	else
    	{
    		if (method<60)
    		{
    			method=1;
    		}
    		else
    		{
    			method=2;
    		}
    	}
    	//itoa(method, buffer, 10);
    	strcat(str, payment_method[method]);
    	strcat(str, "',");
    	strcat(str, ret);
    	itoa(order_id, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ",0.5*(select sum(service_price) from ");
    	strcat(str, ret);
    	strcat(str, "service_type t, schedule s where ");
    	strcat(str, ret);
    	strcat(str, "s.service_type=t.service_type and ");
    	strcat(str, ret);
    	strcat(str, "s.order_id=");
    	strcat(str, buffer);//the order_id str is not changed
    	strcat(str, "));");
    	strcat(str, ret);
    	return str;
    }

    char* insertSchedule()
    {
    	int day, type;
    	strcpy(str, "insert into schedule(");
    	strcat(str, ret);
    	strcat(str, "order_id, car_plate_number, employee_sin ,");
    	strcat(str, ret);
    	strcat(str, "service_date, service_time, service_type ) values (");
    	strcat(str, ret);
    	itoa(order_id, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ", '");
    	strcat(str, carLicence);
    	strcat(str, "',");
    	itoa(emp_sin[rand()%EmpNumber], buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ", '");
    	day=theDate+rand()%(MaxDayNumber-theDate);
    	strcat(str, genDate(day));
    	strcat(str, "', ");

    	itoa(rand()%TimeSlotNumber, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ", ");
    	type=rand()%100;
    	if (type<55)
    	{
    		type=0;
    	}
    	else
    	{
    		if (type<70)
    		{
    			type=1;
    		}
    		else
    		{
    			if (type<90)
    			{
    				type=2;
    			}
    			else
    			{
    				type=3;
    			}
    		}
    	}
    	itoa(type, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, "); ");

    	return str;
    }

    char* genDate(int day)
    {
    	buffer[1]=day%10+'0';
    	if (day<10||day>30)
    	{
    		buffer[0]='0';
    	}
    	else
    	{
    		buffer[0]=day/10+'0';
    	}

    	buffer[2]='\0';
    	if (day>30)
    	{

    		strcat(buffer, "-JUN-04");
    	}
    	else
    	{
    		strcat(buffer, "-MAY-04");
    	}
    	return buffer;
    }

    char* insertCustomer()
    {

    	strcpy(str, "insert into customer(");

    	strcat(str, "customer_licence, customer_name, customer_address, ");

    	strcat(str, "customer_phone)values");
    	strcat(str, ret);
    	strcat(str, "('");
    	strcpy(custLicence, genLicence());
    	strcat(str, custLicence);
    	strcat(str, "', '");
    	strcat(str, generate(MaxFirstName, PureAlpha));
    	strcat(str, " ");
    	strcat(str, generate(MaxLastName, PureAlpha));
    	strcat(str, "', '");
    	strcat(str, generate(4, PureNumber));
    	strcat(str, ",");
    	strcat(str, generate(MaxNameLength, PureAlpha));
    	strcat(str, "', '");
    	strcat(str, genPhone());
    	strcat(str, "'); ");

    	return str;
    }

    char* genPhone()
    {
    	strcpy(phone, generate(3, PureNumber));
    	strcat(phone, "-");
    	strcat(phone, generate(4, PureNumber));
    	strcat(phone, "-");
    	strcat(phone, generate(3, PureNumber));
    	return phone;
    }

    char* insertCar()
    {
    	strcpy(str, "insert into car (");

    	strcat(str, "car_plate_number, customer_licence, modal )values(");

    	strcat(str , "'");
    	strcpy(carLicence, genCar());
    	strcat(str, carLicence);
    	strcat(str, "', '");

    	strcat(str, custLicence);
    	strcat(str, "', '");
    	strcat(str, modalChoices[rand()%MaxModalChoice]);
    	strcat(str, "'); ");

    	return str;
    }

    char* genOrder()
    {
    	theDate=rand()%(MaxDayNumber-1)+1;
    	strcpy(str, "insert into service_order(");
    	strcat(str, ret);
    	strcat(str, "order_id, customer_licence,  ");

    	strcat(str, "order_issue_date) values ");
    	strcat(str, ret);
    	++order_id;
    	strcat(str, "(");
    	itoa(order_id, buffer, 10);
    	strcat(str, buffer);
    	strcat(str, ",'");
    	//keep a copy
    	//strcpy(custLicence, genLicence());
    	strcat(str, custLicence);
    	strcat(str, "','");
    	strcat(str, genDate(theDate));
    	//No due date here!!!!!!!!!!!!
    	//strcat(str, "', '");
    	//theDate=theDate+rand()%(MaxDayNumber-theDate);
    	//strcat(str, genDate(theDate));
    	strcat(str, "'); ");

    	return str;
    }

    char* genName()
    {
    	generate(MaxNameLength, PureAlpha);
    	return buffer;
    }

    char* genCar()
    {
    	generate(MaxCarLicence, Mixed);
    	return buffer;
    }

    char* genLicence()
    {
    	generate(MaxCustLicence, Mixed);
    	return buffer;
    }

    char* generate(int length, Choices choice)
    {
    	char ch;
    	int shift;
    	for (int i=0; i<length; i++)
    	{
    		switch (choice)
    		{
    		case PureNumber:
    			ch='0';
    			shift=10;
    			break;
    		case PureAlpha:
    			ch='A';
    			shift=26;
    			break;
    		case Mixed:
    			if (rand()%2==1)
    			{
    				ch='A';
    				shift=26;
    			}
    			else
    			{
    				ch='0';
    				shift=10;
    			}
    			break;
    		}
    		buffer[i]=rand()%shift+ch;

    	}
    	buffer[i]='\0';
    	return buffer;
    }

    The result is like following:

    insert into customer(customer_licence, customer_name, customer_address, customer_phone)values
    ('L1O2183H6', 'JLIRQ IBURX', '5294,KOJJJIQIQRX', '716-2482-852');
    insert into service_order(
    order_id, customer_licence, order_issue_date) values
    (302,'L1O2183H6','05-MAY-04');
    insert into car (car_plate_number, customer_licence, modal )values('J9UY3L', 'L1O2183H6', 'Suzuki');
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    302, 'J9UY3L',567891234, '23-MAY-04', 7, 0);
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    302, 'J9UY3L',234567891, '04-JUN-04', 0, 2);
    insert into car (car_plate_number, customer_licence, modal )values('15D957', 'L1O2183H6', 'Ford');
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    302, '15D957',567891234, '01-JUN-04', 7, 0);
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    302, '15D957',567891234, '17-MAY-04', 3, 2);
    insert into payment(payment_id, payment_method, order_id,
    payment_amount) values(
    100,'credi',
    302,0.5*(select sum(service_price) from
    service_type t, schedule s where
    s.service_type=t.service_type and
    s.order_id=302));

    insert into payment(payment_id, payment_method, order_id,
    payment_amount) values(
    101,'debit',
    302,0.5*(select sum(service_price) from
    service_type t, schedule s where
    s.service_type=t.service_type and
    s.order_id=302));

    insert into customer(customer_licence, customer_name, customer_address, customer_phone)values
    ('T5122K33K', 'MBQAX NPLQR', '6336,AOOGTDQXBQY', '056-0883-240');
    insert into service_order(
    order_id, customer_licence, order_issue_date) values
    (303,'T5122K33K','16-MAY-04');
    insert into car (car_plate_number, customer_licence, modal )values('K1P97W', 'T5122K33K', 'Suzuki');
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    303, 'K1P97W',567891234, '08-JUN-04', 3, 0);
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    303, 'K1P97W',678912345, '22-MAY-04', 5, 0);
    insert into car (car_plate_number, customer_licence, modal )values('9ZO3BY', 'T5122K33K', 'Toyota');
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    303, '9ZO3BY',345678912, '02-JUN-04', 4, 0);
    insert into schedule(
    order_id, car_plate_number, employee_sin ,
    service_date, service_time, service_type ) values (
    303, '9ZO3BY',234567891, '16-MAY-04', 1, 2);
    insert into payment(payment_id, payment_method, order_id,
    payment_amount) values(
    102,'credi',
    303,0.5*(select sum(service_price) from
    service_type t, schedule s where
    s.service_type=t.service_type and
    s.order_id=303));

    insert into payment(payment_id, payment_method, order_id,
    payment_amount) values(
    103,'credi',
    303,0.5*(select sum(service_price) from
    service_type t, schedule s where
    s.service_type=t.service_type and
    s.order_id=303));
    ...

    ...

    ¡¡