Dependency

QAR (project of comp353)

    A. First Edition

    I don't know which edition of these file are.

    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 hate PHP and don't want to manipulate data at this level because I think if I were given enough parameter, I can almost do everything except interaction with user. So, the idea is to write as simple as PHP code and do
everything in SQL basis. You see PHP is only acting as a connection to ORACLE and SQL parameter inserting, and
displaying query. These code are quite general and simple, therefore they be used as a simple template. All you have to do is passing correct parameter from HTML file and write correct SQL to pass to PHP connection.
¡¡
I also have a faith that all your dependency determines your database implementation. You have to discover all user's dependency so that you will implement either with primary keys, foreign keys, checks, references, uniques, and triggers to constraint the data integrity. So, you see I write 10 triggers and some check, constraint files.
Another point is that try to name everything you write and create a .sql file for it so that you can easily modify or compare.
¡¡
The three distinct assumption for table schedule is the most important dependency in this database. It determines that our database is in 3NF, otherwise I would choose a BCNF.
¡¡

a) For each employee, at any certain date and time, he can only work on a certain car which is under a certain order.

employee_sin, service_date, service_time -> service_type, car_plate_number

b) For any service type scheduled on any car which is under a certain order will always be carried by a certain worker at a certain date, and time. service_type, car_plate_number, order_id -> employee_sin, service_date, service_time.

c) For any car at any certain date and time under repairing, it must be under a specific order, though may be under repairing by many workers on different problem at same time.

car_plate_number, service_date, service_time -> order_id.

Observing: a) and b) are both keys. One of them, say a, is implemented as a primary key. And b is constraint by unique constraint. For c) there is no obvious way except trigger. And c) is a violation of BCNF format, but it follows 3nf: The LHS is not key and RHK is part of key.

Please also refer the above in the report at end of this page.

    D.The major functions

    Basically for each query there is a HTML file for interface and a PHP file to generate result. Those triggers,

    constraints files are independent from queries. There is also two tool programs for this project. One is the

    dependency extractor which calculates canonical cover and decompositions for all dependency; The other one is a

    random data generator which generate about 3000 customers and 2000 cars with about 1500 valid repair orders.

    E.Further improvement

    ¡¡

    F.File listing

    1. rules.h

    2. rules.cpp

    3. set.h

    4. set.cpp

    5. main.cpp (main)

    file name: query2.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>query2  Table: Display Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view all the worker who made oil change on any day
    of the week <br> and where the customer paid with cash ,<BR>Please click on the
    following
    button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query2.php">
    <TR>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>
        <input name="param" type="text" value="07-may-04" size="20" maxlength="20">

    </TR>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    ¡¡

    ¡¡

    file name: query4.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Paycheck Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view paycheck amount for all employees,
    <BR>Please enter current date and click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query4.php">
    <label>     current_date
    <br>
    <TR>
        <input name="current_date" type="text" value="23-may-04" size="20" maxlength="20">
    </TR>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    file name: query5.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Next Day Plan Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view all car and services of next day,
    <BR>Please enter current date and click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query5.php">
    <label>     current_date
    <br>
    <TR>
        <input name="current_date" type="text" value="23-may-04" size="20" maxlength="20">
    </TR>
    	<br>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    file name: query6.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Next Day Part Order Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view part order for next day,
    <BR>Please enter current date and click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query6.php">
    <label>     current_date
    <br>
    <TR>
        <input name="current_date" type="text" value="23-may-04" size="20" maxlength="20">
    </TR>
    	<br>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    ¡¡

    file name: query7.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Student Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view all the most recent two available time slots,
    <BR>Please enter current date, current hour, service type. Then click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query7.php">
    <label>     current_date      curretn_time    <br>
    current_time: 8:00 --- 17:00
    service_type <br>
    0 : oil change 1: tire rotation and mounting 2: brake service 3: mufler replacement<br>

    <br>
    <TR>
        <input name="current_date" type="text" value="05-may-04" size="20" maxlength="20">
        <input name="current_time" type="text" value="1" size="20" maxlength="20">
        <input name="service_type" type="text" value="3" size="20" maxlength="20">
    <br>
    <label> customer licence    car plate number
        <input name="customer_licence" type="text" value="input licence" size="20" maxlength="20">
        <input name="car_plate_number" type="text" value="input car plate" size="20" maxlength="20">
    </label>
    </TR>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    file name: query8.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Student Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view all planned service for customer,please input customer licence number
    <BR>Please click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query8.php">
    <label>    customer license
    <br>
    <TR>
        <input name="customer_licence" type="text" value="Y0418MAL8" size="20" maxlength="20">

    </TR>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    ¡¡

    file name: query9.htm

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
                            "http://ww.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD><TITLE>Student Table: Diaplay Data</TITLE></HEAD>
    <BODY>

    <FONT COLOR="blue"><B>To view all planned service for the car,please input car licence number
    <BR>Please click on the following button!</B></FONT>

    <FORM METHOD="post" ACTION="/cgi-bin/cgiwrap/zdc353_1/query9.php">
    <label>     car-plate-number    start-date    end-date
    <br>
    <TR>
        <input name="car" type="text" value="05W76A" size="20" maxlength="20">
        <input name="start_time" type="text" value="08-may-04" size="20" maxlength="20">
        <input name="end_time" type="text" value="12-jun-04" size="20" maxlength="20">

    </TR>
    </label>
        <TD ALIGN="left"><INPUT TYPE="submit" NAME="showme" VALUE="Show Me!"></TD>

    </TABLE>
    </FORM>
    </BODY>
    </HTML>

    ¡¡

    file name: query2.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Schedule Table: Display Data</TITLE></HEAD>

    <H1>Query2 Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      $param= $_POST['param'];

      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string
      	$sql = sprintf ("select e.employee_name,
    	s.service_date,
    	s.service_time, s.car_plate_number, p.payment_id
            from employee e, schedule s, payment p
            where e.employee_sin=s.employee_sin and s.service_type=0
            and p.order_id=s.order_id and p.payment_method='cash'
    	and s.service_date>= next_day(to_date( :bind1)-7 ,'monday')
    	and s.service_date<=next_day(to_date( :bind1), 'friday')");

     	//$sql = sprintf ("select next_day(to_date(:bind1),'friday') from dual");

      //$sql = sprintf ("SELECT * from employee where ieldname =:bind1");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $param);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

     print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

    /*
      print "<TR>";
      for($i=1; $i<=$nclos; $i++) {
      		printf ("<TH>%s</TH>", OCIColumnName($stmt, $i));
      }
      print "</TR>\n";
    */
      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are only %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    ¡¡

    file name: query3.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Schedule Table: Display Data</TITLE></HEAD>

    <H1>Query3 Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      $param= $_POST['param'];

      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string

      	$sql = sprintf ("

    	select round(1-count(*)/8/6, 2) as unallocated_rate
    	from schedule s
    	where s.service_date=:bind1
    	");

      //$sql = sprintf ("SELECT * from employee where fieldname =:bind1");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $param);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR>";
      for($i=1; $i<=$nclos; $i++) {
      		printf ("<TH>%s</TH>", OCIColumnName($stmt, $i));
      }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are only %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    file name: query4.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Paycheck Listing  Table: Display Data</TITLE></HEAD>

    <H1>Paycheck Listing Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      //connect the database
      $current_date= $_POST['current_date'];

      //echo $param ;
      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string
       //many situations:
    	//1. blue collar, not quit, normal wage
    	//2. blue collar, quit, part weekly wage, workingdays/7*wage
    	//3. white collar, not quit, even weeks
    	//4. white collar, quit, even weeks, part wage, must start from week before
    	//5. white collar, quit, odd weeks, part wage, start this week
    	//6. blue collar, commission, not including those after quit date

      $sql = sprintf ("
    	select e.employee_name, salary, t.employee_type_name,
    	'working' as status
    	from
    	(select employee_sin, round(sum(weekpay), 2)as salary
            from
            (select e.employee_sin, t.wage as weekpay
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and t.employee_type<=2
            and
            e.quit_date not between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
             union
    	select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-7,'monday')
                                          )/7
            from employee e, employee_type t
            where e.employee_type=t.employee_type
    	and e.employee_type<=2
            and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
    	union
    	select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-21,'monday')
                                          )/14
            from employee e, employee_type t
            where e.employee_type=t.employee_type
    	and e.employee_type<=2
            and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
    		and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=0
             union
    	select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-7,'monday')
                                          )/7
            from employee e, employee_type t
            where e.employee_type=t.employee_type
    	and e.employee_type<=2
            and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
    		and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=1
             union
            select e.employee_sin, t.wage as weekpay
            from employee e, employee_type t
            where e.employee_type=t.employee_type and
            t.employee_type>2
            and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=0
            and
            e.quit_date not  between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
            union
            select s.employee_sin, sum(t.service_price)*0.015 as weekpay
            from schedule s, service_type t, employee_type y, employee e
            where e.employee_type=y.employee_type
            and s.employee_sin=e.employee_sin
            and t.service_type=s.service_type
            and s.service_date>=next_day(to_date(:bind1)-7,'monday')
            and s.service_date<=next_day(to_date(:bind1),'friday')
             and e.quit_date>s.service_date
            group by s.employee_sin
            )
    	group by employee_sin) emp, employee e, employee_type t
    	where e.employee_sin=emp.employee_sin
    	and e.employee_type=t.employee_type
    	and e.quit_date>:bind1
    	union
          	select e.employee_name, salary, t.employee_type_name, 'quited' as status
            from
            (select employee_sin, round(sum(weekpay), 2)as salary
            from
            (select e.employee_sin, t.wage as weekpay
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and t.employee_type<=2
            and
            e.quit_date not between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
             union
            select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-7,'monday')
                                          )/7
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and e.employee_type<=2
            and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
            union
            select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-21,'monday')
                                          )/14
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and e.employee_type<=2
          	and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
            union
            select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-21,'monday')
                                          )/14
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and e.employee_type<=2
            and e.quit_date between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
                    and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=0
             union
            select e.employee_sin, t.wage*(e.quit_date-
                    next_day(to_date(:bind1)-7,'monday')
                                          )/7
            from employee e, employee_type t
            where e.employee_type=t.employee_type
            and e.employee_type<=2
            and e.quit_date between
                  next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1)-7,'friday')
                    and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=1
             union
            select e.employee_sin, t.wage as weekpay
            from employee e, employee_type t
            where e.employee_type=t.employee_type and
            t.employee_type>2
            and mod(
                    round(
                          (to_date(:bind1)-to_date('01-may-04'))/7, 0
                    ), 2
                    )=0
            and
            e.quit_date not  between
                    next_day(to_date(:bind1)-7,'monday')
                    and
                    next_day(to_date(:bind1),'friday')
            union
            select s.employee_sin, sum(t.service_price)*0.015 as weekpay
            from schedule s, service_type t, employee_type y, employee e
            where e.employee_type=y.employee_type
            and s.employee_sin=e.employee_sin
            and t.service_type=s.service_type
            and s.service_date>=next_day(to_date(:bind1)-7,'monday')
            and s.service_date<=next_day(to_date(:bind1),'friday')
             and e.quit_date>s.service_date
            group by s.employee_sin
         )
            group by employee_sin) emp, employee e, employee_type t
            where e.employee_sin=emp.employee_sin
    	and e.employee_type=t.employee_type
            and e.quit_date<=:bind1
    ");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $current_date);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are total %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    file name: query5.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Schedule Table: Display Data</TITLE></HEAD>

    <H1>Next Day Plan Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      $param= $_POST['current_date'];

      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string
      	$sql = sprintf ("
    	select e.employee_name, s.car_plate_number, car.modal ,
    	 t.service_name, s.service_time+8 as start_hour,
    	s.service_time+9 as end_hour
    	from schedule s, service_type t, employee e, car
    	where s.service_type=t.service_type
    	and s.employee_sin =e.employee_sin
    	and car.car_plate_number=s.car_plate_number
    	and s.service_date=to_date(:bind1)+1
    	and s.service_time<=4
    	union
    	select e.employee_name, s.car_plate_number, car.modal ,
             t.service_name, s.service_time+9 as start_hour,
            s.service_time+10 as end_hour
            from schedule s, service_type t, employee e, car
            where s.service_type=t.service_type
            and s.employee_sin =e.employee_sin
            and car.car_plate_number=s.car_plate_number
            and s.service_date=to_date(:bind1)+1
            and s.service_time>4
    ");

     	//$sql = sprintf ("select next_day(to_date(:bind1),'friday') from dual");

      //$sql = sprintf ("SELECT * from employee where ieldname =:bind1");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $param);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

     print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

    /*
      print "<TR>";
      for($i=1; $i<=$nclos; $i++) {
      		printf ("<TH>%s</TH>", OCIColumnName($stmt, $i));
      }
      print "</TR>\n";
    */
      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are only %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    ¡¡

    file name: query6.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Paycheck Listing  Table: Display Data</TITLE></HEAD>

    <H1>Part Order for Next Day Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      //connect the database
      $current_date= $_POST['current_date'];

      //echo $param ;
      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string

      $sql = sprintf ("
    	select curdate, part_id,req_qty from
    	(select :bind1 as curdate, st.part_id, round(req.qty*1.2-st.stock_quantity, 0) as req_qty
            from
            (select p.part_id, sum(n.num*p.required_part_quantity) as qty
            from
            (select service_type,count(*) as num
            from schedule s
            where s.service_date=to_date(:bind1)+1
            group by service_type) n, part_required p
            where n.service_type=p.service_type
            group by part_id) req, part st
            where req.part_id=st.part_id
            and round(req.qty*1.2-st.stock_quantity, 0)>0)
    	");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $current_date);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are total %d record(s)!</B>", $nrows);
      $sql = sprintf ("
         insert into part_order (part_order_date, part_id, part_order_quantity)
            select curdate, part_id,req_qty from
            (select :bind1 as curdate, st.part_id, round(req.qty*1.2-st.stock_quantity, 0) as req_qty
            from
            (select p.part_id, sum(n.num*p.required_part_quantity) as qty
            from
            (select service_type,count(*) as num
            from schedule s
            where s.service_date=to_date(:bind1)+1
            group by service_type) n, part_required p
            where n.service_type=p.service_type
            group by part_id) req, part st
            where req.part_id=st.part_id
            and round(req.qty*1.2-st.stock_quantity, 0)>0)");

    	$stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $current_date);

      if ($stmt == false) {
                    echo OCIError($cursor)."<BR>";
                    exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
                    echo OCIError($cursor)."<BR>";
                    echo "no record found!\n";
                    exit;
      }

      //now free the DB connection
    OCICommit($conn);
    OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    ¡¡

    file name: query7.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Available  Table: Display Data</TITLE></HEAD>

    <BODY>
    <H1>Available Time Slots Table</H1>
    <form id="forTimeSlotSelection" method="post" action="/cgi-bin/cgiwrap/zdc353_1/query7_insert.php">
    <?
      // before execute it, you must have to change "gr" with your group number

      //connect the database
      $current_date= $_POST['current_date'];
      $current_time= $_POST['current_time'];
      $service_type= $_POST['service_type'];

       if ($current_time>12)
       {
       	$current_time = $current_time-9;
       }
       else
       {
       	if ($current_time==12)
    	{
    	   $current_time = 4;
    	}
    	else
    	{
    	   $current_time=current_time-8;
    	}
        }

      //echo $param ;
      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it through OCI as string
      $sql = sprintf ("
    	 select s.service_time+8 as start_hour,s.service_time+9 as end_hour,
    	 s.service_date, e.employee_sin,
    	 e.employee_name as worker_name, t.employee_type_name as worker_class
    	from
            (select timeslots.service_time, ser_date.service_date, emp.employee_sin
            from
                    (select 0 as service_time from dual
                    union
                    select 1 as service_time from dual
                    union
                    select 2 as service_time from dual
                    union
                    select 3 as service_time from dual
                    union
                    select 4 as service_time from dual
                    union
                    select 5 as service_time from dual
                    union
                    select 6 as service_time from dual
                    union
                    select 7 as service_time from dual) timeslots,
                    (select to_date(:bind1) as service_date from dual)
                    ser_date,
                    (select e.employee_sin
                    from employee e, employee_type t, service_type s
                    where e.employee_type=t.employee_type
                    and t.difficulty_level>=s.difficulty_level
                    and s.service_type=:bind2) emp
            minus
            select distinct service_time, service_date, employee_sin
            from schedule
            where service_date= :bind1)
    	s, employee e, employee_type t
            where service_time > :bind3
    	and s.employee_sin=e.employee_sin
    	and e.employee_type=t.employee_type
    	and s.service_time<=4
    	union
         	select s.service_time+9 as start_hour,s.service_time+10 as end_hour,
    	s.service_date, e.employee_sin,
             e.employee_name as worker_name, t.employee_type_name as worker_class
            from
            (select timeslots.service_time, ser_date.service_date, emp.employee_sin
            from
                    (select 0 as service_time from dual
                    union
                    select 1 as service_time from dual
                    union
                    select 2 as service_time from dual
                    union
                    select 3 as service_time from dual
                    union
                    select 4 as service_time from dual
                    union
                    select 5 as service_time from dual
                    union
                    select 6 as service_time from dual
                    union
                    select 7 as service_time from dual) timeslots,
                    (select to_date(:bind1) as service_date from dual)
                    ser_date,
                    (select e.employee_sin
                    from employee e, employee_type t, service_type s
    		where e.employee_type=t.employee_type
                    and t.difficulty_level>=s.difficulty_level
                    and s.service_type=:bind2) emp
            minus
            select distinct service_time, service_date, employee_sin
            from schedule
            where service_date= :bind1)
            s, employee e, employee_type t
            where service_time > :bind3
            and s.employee_sin=e.employee_sin
            and e.employee_type=t.employee_type
            and s.service_time>4
    	");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $current_date);
      OCIBindByName($stmt, ":bind2", $service_type);
      OCIBindByName($stmt, ":bind3", $current_time);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR><th>&nbsp;</th>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<2; $j++) {
      		reset($result);
    		print "<TR><td><input type='radio' name='radTimeSlot' value='$j' ";
    		if ($j == 0) {print "checked='checked'/></td>\n";}
    		else {print "/></td>\n";}

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }

      print "</TABLE>";

      printf ("<P><B>There are at most 2 record(s)!</B>");

    /*
      for ($j=0; $j<2; $j++) {
      		reset($result);
      		//loop through result
    		$k = 0;
      		while ($column = each($result)) {
      			$data = $column['value'];
    			if ($k == 0) {
    				echo "timeSlots";
    				echo "[$j]";
    				echo "['time'] = $data[$j];\n";
    			} else if ($k == 2) {
    				echo "timeSlots";
    				echo "[$j]";
    				echo "['date'] = '$data[$j]';\n";
    			} else if ($k == 3) {
    			 	echo "timeSlots";
    				echo "[$j]";
    				echo "['sin'] = $data[$j];\n";
    			}
    			$k++;
      		}
    	}
    */
      //now free the DB connection
    	print "<FORM METHOD=\"post\" ACTION=\"/cgi-bin/cgiwrap/zdc353_1/query7_insert.php\">";

     print "<br>";
     print "<TR>";
       print " <input name=\"service_date\" type=\"text\" value=\"05-may-04\" size=\"20\" maxlength=\"20\">";
       print "  <input name=\"service_time\" type=\"text\" value=\"service time:8--17\" size=\"20\" maxlength=\"20\">";
       print " <input name=\"service_type\" type=\"text\" value=\"service_type\" size=\"20\" maxlength=\"20\">";
       print " <input name=\"order_id\" type=\"text\" value=\"input order id\" size=\"20\" maxlength=\"20\">";
      print "  <input name=\"car_plate_number\" type=\"text\" value=\"input car plate\" size=\"20\" maxlength=\"20\">";
      print "  <input name=\"employee_sin\" type=\"text\" value=\"input employee_sin\" size=\"20\" maxlength=\"20\">";
    print "</TR>   ";
       print" <TD ALIGN=\"left\"><INPUT TYPE=\"submit\" NAME=\"showme\" VALUE=\"choose!\"></TD>";

    print "</TABLE>";
    print "</FORM>";

      OCILogOff($conn);

    ?>

    </BODY>
    </HTML>

    ¡¡

    file name: query8.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Available  Table: Display Data</TITLE></HEAD>

    <H1>Planned Services for Customer Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      //connect the database
      $customer_licence= $_POST['customer_licence'];
    //  $current_time= $_POST['current_time'];
      //$service_type= $_POST['service_type'];

      //echo $param ;
      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it tShrough OCI as string
      $sql = sprintf ("
    		select s.car_plate_number, s.service_date,
    		s.service_time, p.service_name, s.order_id
    		from schedule s, service_order o, service_type p
    		where s.order_id=o.order_id
    		and o.customer_licence=:bind1
    		and s.service_type=p.service_type
    		");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $customer_licence);
    //  OCIBindByName($stmt, ":bind2", $service_type);
     // OCIBindByName($stmt, ":bind3", $current_time);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are total %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);
    /*
    	print "<FORM METHOD=\"get\" ACTION=\"/cgi-bin/cgiwrap/zdc353_1/query8.php\">";
    	print "<label>     current_date      curretn_time    service_type";
    	print "<br>   ";
    	print "<TR>   ";
        	print "<input name=\"customer_licence\" type=\"text\" value=\"Y0418MAL8\" size=\"20\"" ;
    	print	"maxlength=\"20\">";

    	print " </TR>";
    	print "</label>";
    */

    ?>

    </BODY>
    </HTML>

    ¡¡

    file name: query9.php

    #!/usr/local/bin/php

    <HTML>
    <HEAD><TITLE>Available  Table: Display Data</TITLE></HEAD>

    <H1> Car Service Check Table</H1>

    <BODY>
    <?

      // before execute it, you must have to change "gr" with your group number

      //connect the database
      $car= $_POST['car'];
      $start_time= $_POST['start_time'];
      $end_time= $_POST['end_time'];

      //echo $param ;
      $conn = OCILogon("zdc353_1", "MyDBMS04", "");
      if ($conn == false){
      		echo OCIError($conn)."<BR>";
    		echo "Failed to get the Database connection, please try sometimes later...\n";
    		exit;
      }

      // create the SQL statement and pass it tShrough OCI as string
      $sql = sprintf ("
    		select  p.service_name, s.service_date,e.employee_name,
    		s.service_time,  s.order_id, c.customer_name
    		from schedule s, service_order o, service_type p,
    		employee e, customer c
    		where s.order_id=o.order_id
    		and c.customer_licence=o.customer_licence
    		and s.service_type=p.service_type
    		and s.service_date>=to_date(:bind2)
    		and s.service_date<=to_date(:bind3)
    		and s.car_plate_number=:bind1
    		and s.employee_sin=e.employee_sin
    		");

      $stmt = OCIParse($conn, $sql);

      OCIBindByName($stmt, ":bind1", $car);
      OCIBindByName($stmt, ":bind2", $start_time);
      OCIBindByName($stmt, ":bind3", $end_time);

      if ($stmt == false) {
      		echo OCIError($cursor)."<BR>";
      		exit;
      }

      $result = OCIExecute($stmt, OCI_DEFAULT);

      if ($result == false) {
    		echo OCIError($cursor)."<BR>";
    		echo "no record found!\n";
      		exit;
      }

      // get the information of columns and rows
      $ncols = OCINumCols($stmt);
      $nrows = OCIFetchStatement($stmt, $result);

      // draw the table as Matrix ($nrows, $ncols)
      print "<P><TABLE CELLSPACING=\"0\" CELLPADDING=\"3\" BORDER=\"1\" ALIGN=\"center\">\n";

      print "<TR>";
        while (list($key, $val) = each($result)) {
         echo "<th>$key</th>\n";
       }
      print "</TR>\n";

      // print the datas
      for ($j=0; $j<$nrows; $j++) {
      		reset($result);
      		print "<TR>";

      		//loop through result
      		while ($column = each($result)) {
      			$data = $column['value'];
      			print "<TD>$data[$j]</TD>\n";
      		}

      		print "</TR>\n";
      }
      print "</TABLE>";

      printf ("<P><B>There are total %d record(s)!</B>", $nrows);

      //now free the DB connection
      OCILogOff($conn);
    /*
    	print "<FORM METHOD=\"get\" ACTION=\"/cgi-bin/cgiwrap/zdc353_1/query8.php\">";
    	print "<label>     current_date      curretn_time    service_type";
    	print "<br>   ";
    	print "<TR>   ";
        	print "<input name=\"customer_licence\" type=\"text\" value=\"Y0418MAL8\" size=\"20\"" ;
    	print	"maxlength=\"20\">";

    	print " </TR>";
    	print "</label>";
    */

    ?>

    </BODY>
    </HTML>

    file name: createtable.sql

    create table employee_type(employee_type char(1),employee_type_name char(10), difficulty_level number(1),frequency_paid
    	char(1),wage number (7,2),commission number(5,4), primary key(employee_type));

    create table payment_method(payment_method char(5),discount number(5,4), primary key(payment_method));

    create table schedule(employee_sin number(9),car_plate_number char(6),service_type number(1),
    	service_time number(1),service_date date,order_id number(6),
    	primary key(car_plate_number, service_type, order_id));

    create table service_order(order_id number(6),customer_licence char(9),order_issue_date date, order_due_date date,
    	order_status char(1),payment_date date, primary key(order_id));

    create table payment(payment_id number(6),payment_method char(5),order_id number(6),payment_amount number(5,2), primary key(payment_id));

    create table part(part_id number(4),part_name char(10),stock_quantity number(4),primary key (part_id));

    create table part_required(service_type number(1),part_id number(4), required_part_quantity number(2), primary key(service_type, part_id));

    create table part_order(part_order_date date, part_id number(4), part_order_quantity number(4), primary key(part_order_date, part_id));

    create table employee(employee_sin number(9),employee_name char(15),employee_address char(20), employee_phone char(12) ,
    	employee_hire_date date ,employee_type char(1), quit_date date, primary key(employee_sin));

    create table customer(customer_licence char(9),customer_name char(15),customer_address char(20), customer_phone char(12),
    	primary key(customer_licence));

    create table car(car_plate_number char(6),customer_licence char(9),modal char(10),
    	 primary key(car_plate_number));

    create table service_type(service_type number(1),service_name char(10),difficulty_level number(1), service_price number(5,2),
    	primary key(service_type));

    ¡¡

    file name: constraint.sql

    alter table employee add constraint emp_emptype_fk foreign key (employee_type) references
     employee_type	 on delete set null;

    alter table car add constraint car_custlic_fk foreign key (customer_licence) references
    	customer on delete set null;

    alter table part_required add constraint partreq_part_fk foreign key (part_id) references part
    		 on delete set null;

    alter table part_required add constraint partreq_sertype_fk foreign key	(service_type)
    	references service_type
    		 on delete set null;

    alter table payment add constraint pay_paymeth_fk foreign key (payment_method) references
    	payment_method
    		 on delete set null;

    alter table schedule add constraint sch_emp_fk foreign key(employee_sin) references employee
    		 on delete cascade;

    alter table schedule add constraint sch_car_fk foreign key(car_plate_number) references car
    		 on delete set null;

    alter table schedule add constraint sch_sertype_fk foreign key(service_type) references service_type
    		 on delete set null;

    alter table schedule add constraint sch_serord_fk foreign key(order_id) references service_order
    		 on delete cascade;

    alter table schedule add constraint emp_datetime_uk unique (employee_sin, service_date, service_time);

    ¡¡

    file name: check.sql

    alter table employee_type add constraint emp_freq_ck check (frequency_paid in ('O','o','T','t'));

    alter table employee_type add constraint emp_comm_ck check (commission in (0, 0.015));

    alter table payment_method add constraint paymeth_disc_ck check (discount in (0, 0.03));

    alter table service_order add constraint serord_ordstatus_ck check (order_status in ('Y','N','y','n'));

    alter table part add constraint part_stkqty_ck check(stock_quantity>=0);

    alter table employee add constraint emp_quit_ck check (quit_date>=employee_hire_date);

    alter table part_order add constraint part_ordqty_ck check (part_order_quantity>=0);

    alter table service_type add constraint sertype_lvl_ck check (difficulty_level in (0,1,2));

    alter table employee_type add constraint emptype_lvl_ck check (difficulty_level in (0,1,2,-1));

    alter table part_required add constraint partreq_qty_ck check (required_part_quantity>0);

    file name: trigger1.sql

    create or replace trigger difficulty_level_match_trigger
    before insert or update on schedule for each row
    declare
    	we_donot_work_on_weekend_error exception;
    	difficulty_not_match_error exception;
    	oldrec_notallow_rem_err exception;
    	old_level integer;
    	new_level integer;
    	q_date date;
    begin
    	select employee_type.difficulty_level into old_level
    	from employee_type, employee
    	where employee.employee_sin=:new.employee_sin
    	and employee.employee_type=employee_type.employee_type;
    	select service_type.difficulty_level into new_level
    	 from service_type
    	where service_type.service_type=:new.service_type;
    	if new_level>old_level then
    	raise difficulty_not_match_error;
    	end if;
    	if :new.service_date = next_day(:new.service_date-7, 'saturday')
    	or
            :new.service_date=next_day(:new.service_date-7, 'sunday') then
            raise we_donot_work_on_weekend_error;
            end if;
    	if :new.employee_sin is null then
    		select quit_date into q_date
    		from employee
    		where employee_sin=:new.employee_sin;
    		if q_date>:new.service_date then
    		raise oldrec_notallow_rem_err;
    		end if;
    	end if;
    	exception
    	when we_donot_work_on_weekend_error then
    	raise_application_error(-20100, 'we donot work on weekend.');
    	when difficulty_not_match_error then
    	raise_application_error(-20200, 'The employee is not qualified
    	for assigned repair job.');
    	when oldrec_notallow_rem_err then
    	raise_application_error(-20700, 'You canot remove old records which
    	happened before this employee quit.');
    end;

    ¡¡

    file name: trigger2.sql

    create or replace trigger car_time_ord_match_trigger
    before insert  on schedule for each row
    declare
    	old_ord  number(6);
    	car_time_ord_not_unique_error exception;
    	num	integer;
    begin
    	--it must be a unique order
    	num:=0;

    	select count(order_id) into num
    	from schedule
    	where service_time=:new.service_time
    	and service_date=:new.service_date
    	and car_plate_number=:new.car_plate_number;

    	if num=1 then
    		select order_id into old_ord
    		from schedule
    		where service_time=:new.service_time
    		and service_date=:new.service_date
    		and car_plate_number=:new.car_plate_number;
    		if old_ord<> :new.order_id then
    			raise car_time_ord_not_unique_error;
    		end if;
    	end if;
    	exception
    	when car_time_ord_not_unique_error then
    	raise_application_error(-20400, 'We already have a similar
    	order with this car at same date time in our schedule');
    end;

    ¡¡

    file name: trigger3.sql

    create or replace trigger customer_car_not_match_trigger
    before insert or update on schedule for each row
    declare
    	customer_not_match_error  exception;
    	no_order_placed_error exception;
    	car_not_registered_error exception;
    	old_owner  char(9);
    	new_owner  char(9);
    	old_num    integer;
    	new_num    integer;
    begin
    	old_num := 0;
    	new_num := 0;

    	select count(customer_licence) into old_num
    	from service_order
    	where order_id=:new.order_id;
    	if old_num>0 then
    	select customer_licence into old_owner
    	from  service_order
    	where order_id=:new.order_id;
    	end if;
    	if old_num=0 then
    	raise no_order_placed_error;
    	end if;

    	select count(customer_licence) into new_num
    	from car
    	where car_plate_number=:new.car_plate_number;

    	if new_num>0 then
    	select customer_licence into new_owner
    	from car
    	where car_plate_number=:new.car_plate_number;
    	else
    	raise car_not_registered_error;
    	end if;

    	if old_num>0 and new_num>0 then
    		if  old_owner <> new_owner then
    		raise customer_not_match_error;
    		end if;
    	end if;
    	exception
    	when customer_not_match_error then
    	raise_application_error(-20500, 'The input car plate
    	number is different from our record, we recommand you
    	to check with this suspected customer');
    	when car_not_registered_error then
    	raise_application_error(-20900, 'The input car is not
    	registered in database, please register it first.');
    	when no_order_placed_error then
    	raise_application_error(-21000, 'The order id input
    	is invalid, please re-input.');
    end;

    file name: trigger4.sql

    create or replace trigger payment_default_trigger
    before insert or update on payment for each row
    declare
    	payment_exceed_error  exception;
    	old_amount	number(5,2);
    	sub_total       number(5,2);
    	num	integer;
    begin
    	if :new.payment_amount is null then
    		--make sure there is record or not
    		select count(*) into num
    		from service_type t, schedule s
    		where s.order_id=:new.order_id
    		and s.service_type=t.service_type;
    		if num>0 then
    			select sum(service_price) into old_amount
    			from service_type t, schedule s
    			where s.order_id=:new.order_id
    			and s.service_type=t.service_type;
    			sub_total:=0;
    			select count(*) into num
    			from payment p
    			where p.order_id=:new.order_id;
    			if num>0 then
    				select sum(payment_amount) into sub_total
    				from payment
    				where payment.order_id=:new.order_id;
    			end if;
    			:new.payment_amount := old_amount-sub_total;
    		else
    			:new.payment_amount:=0;
    		end if;

    	else
    		old_amount:=:new.payment_amount;
    		select count(*) into num
    		from service_type t, schedule s
    		where s.order_id=:new.order_id
    		and s.service_type=t.service_type;
    		if num>0 then
    			select sum(service_price) into old_amount
    			from service_type t, schedule s
    			where s.order_id=:new.order_id
    			and s.service_type=t.service_type;
    		end if;
    		if old_amount<:new.payment_amount then
    			raise payment_exceed_error;
    		end if;
    	end if;
    	exception
    	when payment_exceed_error then
    	raise_application_error(-20600, 'The payment already
    	exceeds the maximum of the bill amount, please re-input');
    end;

    ¡¡

    file name: trigger5.sql

    --suppose you have a new employee and you want him to work
    --immediately for a replacement. if there is one
    create or replace trigger employee_replacement_trigger
    after insert on employee for each row
    begin
    	update schedule set employee_sin=:new.employee_sin
    	where (car_plate_number,service_type, order_id) in
    	(select s.car_plate_number, s.service_type, s.order_id
    	from schedule s, service_type t, employee_type e
    	where s.service_type=t.service_type
    	and e.employee_type=:new.employee_type
    	and s.service_date>:new.employee_hire_date
    	and t.difficulty_level<=e.difficulty_level
    	and s.employee_sin is null);

    end;

    file name: trigger6.sql

    --a employee quit and only those schedule that happened
    --after his quit date would be delted

    create or replace trigger delete_employee_trigger
    after update of quit_date on employee for each row

    begin
    	delete from schedule where
    	employee_sin=:new.employee_sin
    	and service_date>=:new.quit_date;
    end;

    ¡¡

    file name: trigger7.sql

    create or replace trigger insert_order_max_id_trigger
    before insert on service_order for each row
    begin
    	if :new.order_id is null then
    	select max(order_id)+1 into :new.order_id
    	from service_order;
    	end if;
    end;

    ¡¡

    ¡¡

    file name: trigger8.sql

    create or replace trigger insert_payment_max_id_trigger
    before insert on payment for each row
    begin
    	if :new.payment_id is null then
    	select max(payment_id)+1 into :new.payment_id
    	from payment;
    	end if;
    end;

    ¡¡

    file name: trigger9.sql

    create or replace trigger update_order_schedule_trigger
    before update of customer_licence on service_order for each row
    --declare
    	--num  integer;
    	--customer_car_scheduled_error exception;
    begin
    	--select count(*) into num from schedule where
    	--order_id=:old.order_id;
    	--if num>0 then
    	--raise customer_car_scheduled_error;
    	insert into customer(customer_licence, customer_name,
    	customer_phone,	customer_address)
    	select :new.customer_licence as customer_licence,customer_name,
    	customer_phone,	customer_address
    	from customer
    	where customer_licence=:old.customer_licence;
    	update car set customer_licence=:new.customer_licence
    	where car_plate_number in
    	(select car_plate_number
    	from car where customer_licence=:old.customer_licence);
    	delete from customer
    	where customer_licence=:old.customer_licence;

    	--end if;
    	--exception
    	--when customer_car_scheduled_error then
    	--raise_application_error(-20800, 'Oh! you cannot modify the
    	--customer licence because he has car scheduled for repairing!');

    end;

    ¡¡

    ¡¡

    file name: trigger10.sql

    create or replace trigger payment_method_unique_trigger
    before insert or update on payment for each row
    declare
    	num  integer;
    	payment_method_same_error exception;
    begin
    	select count(*) into num from payment where
    	payment_id=:old.payment_id;
    	if num>0 then
    	raise payment_method_same_error;
    	end if;
    	exception
    	when payment_method_same_error then
    	raise_application_error(-20801, 'Oh! The customer already paid with same
    	method before, are you sure you are making senses?!');

    end;

    ¡¡

    file name: fill_employee.sql

    --junior apprentices
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(123456789,'Insoo Kim', '275 Dorval ave','5149305152',to_date('2004/01/08','yyyy/mm/dd'),'0');
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(234567891,'Joel Boudriau', '215 Saint-Charles','5144835156',to_date('2004/01/11','yyyy/mm/dd'),'0');
    --senior apprentices
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(345678912,'Kirk Yen', '207 Stillview','5145663214',to_date('2002/01/07','yyyy/mm/dd'),'1');
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(456789123,'Ryan Kim', '1024 Saint-Jean','5147893658',to_date('2002/01/24','yyyy/mm/dd'),'1');
    --senior workers
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(567891234,'Patrick Webber', '204 Dorval ave','5147123154',to_date('2000/01/16','yyyy/mm/dd'),'2');
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(678912345,'Jean Orgiazzi', '1550 Dr.Penfield ave','5148522149',to_date('2000/01/12','yyyy/mm/dd'),'2');
    --office people
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(789123456,'Bette Midler', '35 Angrignon ave','5146633425',to_date('2000/01/14','yyyy/mm/dd'),'3');
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(891234567,'Stan Streisand', '122 Verdun ave','5149588411',to_date('2000/01/10','yyyy/mm/dd'),'3');
    --boss/manager
    INSERT INTO employee(employee_sin,employee_name,employee_address, employee_phone,employee_hire_date,employee_type) VALUES(912345678,'Jennifer Glover', '4501 Beaudry ave','5147863364',to_date('2000/01/01','yyyy/mm/dd'),'4');

    file name: fill_part.sql

    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(1,'oil-basic',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(2,'oil-stand',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(3,'oil-medium',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(4,'oil-premiu',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(5,'oil-deluxe',10);

    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(6,'wheel',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(7,'tire-summ',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(8,'tire-wint',10);

    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(9,'muff-low',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(10,'muff-high',10);

    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(11,'brake-basi',10);
    INSERT INTO part(part_id,part_name,stock_quantity) VALUES(12,'brake-prem',10);

    ¡¡

    file name: fill_employee_type.sql

    --fill the table
    --table employee_type(
    --	employee_type char(1),
    --	difficulty_level number(1),
    --	frequency_paid char(1),
    --	wage number (7,2),
    --	commission number(5,4),
    --	primary key(employee_type)
    --);

    --EMPLOYEE TYPE AND THEIR RESPECTIVE DIFFICULTY LEVEL
    --junior apprentice=0,0
    --senior apprentice=1,1
    --senior worker=2,2
    --normal office worker=3,-1
    --manager=4,-1

    --junior apprentices
    INSERT INTO employee_type(employee_type,difficulty_level,frequency_paid,wage,commission)VALUES('0',0,'o',15.00,0.015);
    --senior apprentices
    INSERT INTO employee_type(employee_type,difficulty_level,frequency_paid,wage,commission)VALUES('1',1,'o',17.00,0.015);
    --senior workers
    INSERT INTO employee_type(employee_type,difficulty_level,frequency_paid,wage,commission)VALUES('2',2,'o',20.00,0.015);
    --office people
    INSERT INTO employee_type(employee_type,difficulty_level,frequency_paid,wage,commission)VALUES('3',-1,'t',18.00,0.00);
    --boss/manager
    INSERT INTO employee_type(employee_type,difficulty_level,frequency_paid,wage,commission)VALUES('4',-1,'t',25.00,0.00);

    ¡¡

    file name: fill_part_required.sql

    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(0,3,2);
    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(0,4,1);

    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(1,6,4);
    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(1,7,2);
    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(1,8,2);

    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(2,9,1);
    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(2,10,1);

    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(3,11,2);
    INSERT INTO part_required(service_type,part_id, required_part_quantity) VALUES(3,12,2);

    ¡¡

    file name: fill_payment_method.sql

    INSERT INTO payment_method(payment_method,discount) VALUES('cash',0.03);
    INSERT INTO payment_method(payment_method,discount) VALUES('credi',0.00);
    INSERT INTO payment_method(payment_method,discount) VALUES('debit',0.03);

    ¡¡

    file name: fill_service_type.sql

    INSERT INTO service_type(service_type,service_name,difficulty_level, service_price) VALUES(0,'oil change',0,35.95);
    INSERT INTO service_type(service_type,service_name,difficulty_level, service_price) VALUES(1,'tire',1,56.95);
    INSERT INTO service_type(service_type,service_name,difficulty_level, service_price) VALUES(2,'muffler',2,70.95);
    INSERT INTO service_type(service_type,service_name,difficulty_level, service_price) VALUES(3,'brake',2,85.95);

    ¡¡

    ¡¡

    ¡¡

    The following is the report:

    ¡¡

Concordia University - Summer 2004

COMP353 /1 AA

DATABASES

MAIN PROJECT

Design and Implementation of an Automobile Repair Shop:

QAR

TEAM_ID: zdc353_1

Team Members:

Qinzhe HUANG 5037735

Insoo KIM 4960920

Jean-Luc ORGIAZZI 4586727

Vincent THERIAULT 4921844

Eric VALLEE 4899571

Table of Content

1..... INTRODUCTION.. 3

1.1 Project Description. 3

1.2 Objectives and Requirements. 4

1.2.1 List of other requirements: 4

1.2.2 Queries and forms implemented: 5

1.3 Tools used. 5

2..... DESIGN.. 6

2.1 E/R Diagram and Assumptions. 6

2.1.1 E/R Diagram.. 6

2.1.2 Assumptions. 7

2.2 Relational Schema. 8

2.3 Relational Model 8

2.4 Functional Dependencies. 9

2.4.1 List of all functional dependencies: 9

2.4.2 Justify all relational schemas are at least in 3NF. 10

3..... IMPLEMENTATION DETAILS. 11

3.1 Database implementation details. 11

3.2 Data integrity and database consistency. 12

3.2.1 Triggers. 12

3.2.2 Random Generator 14

3.3 Interface level implementation and testing. 14

4..... LIMITATIONS AND FUTURE IMPROVEMENT. 15

5..... CONCLUSION.. 15

6..... MEMBER CONTRIBUTION.. 15

¡¡

# 1 INTRODUCTION

## 1.1 Project Description

The goal of this project is to design and implement a relational database management system for a chain of auto repair shops in Quebec, QAR shop (Quebec Auto Repair).

Some specifications are given to us from which we drew a list a requirements and specifications for the design and implementation of our database system, more assumptions will be described in the design part of this report:

¡¤ 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 blue-collar 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.

## 1.2 Objectives and Requirements

Our objectives, considering the limited amount of time provided (less than three weeks) and the very limited work force are to implement a robust and efficient database with a simple user interface (but not simplistic) to answer to the different queries as stated below. Many features could be added on this versatile design to add friendlier user interfaces, security and automation.

We emphasized our work on the robustness and efficiency of the database at a low level using many triggers to protect the data and avoid inconsistencies. The triggers ensure the data integrity and avoid problems that could arise from the PHP level.

### 1.2.1 List of other requirements:

- Model one QAR shop that 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%).

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

- 1 junior apprentice quits her/his job after the first week and 1 new senior apprentice is immediately hired as replacement.

- Use HTML and PHP to build interfaces supporting the following queries and transactions.

### 1.2.2 Queries and forms implemented:

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-checks (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 has 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.

## 1.3 Tools used

We worked in an Oracle9i environment with Sqlplus to develop the tables, the triggers and the SQL queries.

The interface was developed in HTM and PHP for which we used common text editor and Dreamweaver web development tools.

Also two pieces of software were designed and implemented in C++ to randomly fill our tables and another one to generate the 3NF repartition of our design from a canonical cover definition. The software created is called ¡°FD Optimizer Deluxe 1.0¡±is a freeware under a GNU license and the code has been attached in the source code CD.

¡¡

# 2 DESIGN

## 2.1 E/R Diagram and Assumptions

### 2.1.1 E/R Diagram

¡¡

### 2.1.2 Assumptions

1. This database system is only implemented for a single QAR.

2. Workers, employees or boss do not work on weekends.

3. A service order can have multiple services for one or more cars and can be paid in different payment methods but at the same time.

4. The stock provider always sends the ordered amount of parts the following day before 8:00am.

5. Employees can NEVER be sick and will always be present at work, even with a notice from the doctor.

6. For any employee in a particular date-time, he can only do one type of service, for a specific car, under a specific order.
   For any particular car at any particular date-time, it must be under a specific order, if it is repaired by one or more employee.
   ¡¡
7. For any car which is under a specific order for a specific type of service, it must be done by a specific employee at a specific date-time.

8. A customer can have many cars and there can be no cars without a customer.

9. When someone quits, the replacements worker hired has at least the same difficulty level.

10. No worker will be hired if no one quits.

11. We assume that our schedule is always fulfilled unless it is cancelled by customer.

12. We assume that when an employee is deleted, it means all data related with him is no long needed. They will all be removed by "foreign key" constraint at "on delete cascade".

13. We assume that when an employee quits, all his job after his quit date will be removed and this is done by a trigger. It will delete only those schedules after his quit date.

14. We assume that when searching for two earliest available time slots, the earliest two available time slots may be at same time, but with different employee. so, customer may choose the time slots according to worker's employee type class.
15. We assume that when searching for two earliest available time slots, the searching will only trace the current day and two following days.

16. We assume that any employee can quit at any working day and the salary must be calculated immediately. The working salary is calculated on working days. The wage for white collar employee is for two weeks and for blue collars is for one week.

17. We assume that customer can split his payment and the payment method for each spitted part should be different.

18. We assume that when order_id and payment_id is not specified, the system will automatically assign a maximum number for inserted order or payment.

19. We assume that when payment_amount is not specified, the customer intends to pay full order amount. It is done by a trigger.

20. We assume that when customer license in service order is updated, this implies that user are trying to correct a mistake in customer license. We update both customer and car table. A new customer is inserted into customer table and old one is deleted. The customer license in car table is modified accordingly. This is done by a trigger.

21. For all function dependency, we try to implement it by either constraint or triggers. For example, in table schedule there are three distinct un-reducible dependencies:

a) For each employee, at any certain date and time, he can only work on a certain car which is under a certain order.

employee_sin, service_date, service_time -> service_type, car_plate_number

b) For any service type scheduled on any car which is under a certain order will always be carried by a certain worker at a certain date, and time. service_type, car_plate_number, order_id -> employee_sin, service_date, service_time.

c) For any car at any certain date and time under repairing, it must be under a specific order, though may be under repairing by many workers on different problem at same time.

car_plate_number, service_date, service_time -> order_id.

Observing: a) and b) are both keys. One of them, say a, is implemented as a primary key. And b is constraint by unique constraint. For c) there is no obvious way except trigger. And c) is a violation of BCNF format, but it follows 3nf: The LHS is not key and RHK is part of key.

22. The payment amount in table payment is the face value of tuition.

## 2.2 Relational Schema

employee_type(employee_type ,employee_type_name , difficulty_level ,frequency_paid ,wage ,commission )

payment_method(payment_method ,discount )

schedule(employee_sin ,car_plate_number ,service_type , service_time ,service_date,order_id )

service_order(order_id ,customer_licence ,order_issue_date, order_due_date, order_status,payment_date )

payment(payment_id ,payment_method ,order_id ,payment_amount)

part(part_id ,part_name,stock_quantity)

part_required(service_type,part_id, required_part_quantity )

part_order(part_order_date, part_id, part_order_quantity)

employee(employee_sin,employee_name,employee_address, employee_phone, employee_hire_date , employee_type , quit_date )

customer(customer_licence,customer_name ,customer_address , customer_phone)

car(car_plate_number,customer_licence, modal)

service_type(service_type ,service_name ,difficulty_level , service_price )

## 2.3 Relational Model

¡¡

## 2.4 Functional Dependencies

### 2.4.1 List of all functional dependencies:

We used the software created to analyze our set of functional dependencies. Following is the list of dependencies used to describe our database and represent all the data inputted in the software.

QAR(employee_sin, employee_name, employee_address, employee_phone, employee_hire_date, employee_type, customer_licence,customer_name, customer_address, customer_phone, car_plate_number, modal, service_type, service_description,difficulty_level, service_price, service_name, frequency_paid, wage, employee_type, commission, payment_method, discount, service_time, service_date, order_id, order_issue_date, order_due_date,order_status, payment_date, payment_id, payment_amount, part_order_quantity, part_order_date,required_part_quantity, part_id, part_name, stock_quantity, quit_date);

employee_sin service_date service_time -> order_id car_plate_number service_type;

car_plate_number service_date service_time -> order_id;

car_plate_number service_type order_id -> employee_sin service_time service_date;

employee_type -> commission difficulty_level wage frequency_paid;

payment_method -> discount;

order_id -> order_issue_date order_due_date order_status payment_date customer_licence;

payment_id -> payment_method payment_amount discount order_id payment_date;

part_id -> part_name stock_quantity;

part_id service_type -> required_part_quantity;

part_id part_order_date -> part_order_quantity;

employee_sin -> employee_name employee_address employee_phone employee_type employee_hire_date wage frequency_paid difficulty_level commission quit_date;

customer_licence -> customer_name customer_address customer_phone;

car_plate_number -> modal customer_licence;

service_type -> service_description service_price difficulty_level service_name;

Running the software gave the following decompositions after a step of optimization and removal of some redoundancies. The final display shows the 3NF decompostion of our design.

before decomposition

employee_sin service_time service_date -> car_plate_number service_type ;

car_plate_number service_time service_date -> order_id ;

car_plate_number service_type order_id -> employee_sin service_time service_date ;

employee_type -> difficulty_level frequency_paid wage commission ;

payment_method -> discount ;

order_id -> customer_licence order_issue_date order_due_date order_status payment_date ;

payment_id -> payment_method order_id payment_amount ;

part_id -> part_name stock_quantity ;

service_type part_id -> required_part_quantity ;

part_order_date part_id -> part_order_quantity ;

employee_sin -> employee_name employee_address employee_phone employee_hire_date employee_type quit_date ;

customer_licence -> customer_name customer_address customer_phone ;

car_plate_number -> customer_licence modal ;

service_type -> service_description difficulty_level service_price service_name ;

decomposition #1:{employee_sin,car_plate_number,service_type,service_time,service_date}

dependency is:

employee_sin service_time service_date -> car_plate_number service_type ;

decomposition #2:{car_plate_number,service_time,service_date,order_id}

dependency is:

car_plate_number service_time service_date -> order_id ;

decomposition #3:{employee_sin,car_plate_number,service_type,service_time,service_date,order_id}

dependency is:

employee_sin service_time service_date -> car_plate_number service_type ;

car_plate_number service_time service_date -> order_id ;

car_plate_number service_type order_id -> employee_sin service_time service_date ;

decomposition #4:{employee_type,difficulty_level,frequency_paid,wage,commission}

dependency is:

employee_type -> difficulty_level frequency_paid wage commission ;

decomposition #5:{payment_method,discount}

dependency is:

payment_method -> discount ;

decomposition #6:{customer_licence,order_id,order_issue_date,order_due_date,order_status,payment_dat

e}

dependency is:

order_id -> customer_licence order_issue_date order_due_date order_status payment_date ;

decomposition #7:{payment_method,order_id,payment_id,payment_amount}

dependency is:

payment_id -> payment_method order_id payment_amount ;

decomposition #8:{part_id,part_name,stock_quantity}

dependency is:

part_id -> part_name stock_quantity ;

decomposition #9:{service_type,required_part_quantity,part_id}

dependency is:

service_type part_id -> required_part_quantity ;

decomposition #10:{part_order_quantity,part_order_date,part_id}

dependency is:

part_order_date part_id -> part_order_quantity ;

decomposition #11:{employee_sin,employee_name,employee_address,employee_phone,employee_hire_date,emp

loyee_type,quit_date}

dependency is:

employee_sin -> employee_name employee_address employee_phone employee_hire_date employee_type quit

_date ;

decomposition #12:{customer_licence,customer_name,customer_address,customer_phone}

dependency is:

customer_licence -> customer_name customer_address customer_phone ;

decomposition #13:{customer_licence,car_plate_number,modal}

dependency is:

car_plate_number -> customer_licence modal ;

decomposition #14:{service_type,service_description,difficulty_level,service_price,service_name}

dependency is:

service_type -> service_description difficulty_level service_price service_name ;

decomposition #15:{car_plate_number,service_type,employee_type,payment_id,part_order_date,part_id}

the key has no particular dependency

employee_sin employee_name employee_address employee_phone employee_hire_date

employee_type customer_licence customer_name customer_address customer_phone car_

plate_number modal service_type service_description difficulty_level service_pric

e service_name frequency_paid wage employee_type commission payment_method disc

ount service_time service_date order_id order_issue_date order_due_date orde

r_status payment_date payment_id payment_amount part_order_quantity part_order_d

ate required_part_quantity part_id part_name stock_quantity quit_date

employee_sin service_time service_date -> car_plate_number service_type ;

111111111111111111101001111111000000001

car_plate_number service_time service_date -> order_id ;

000000111111000000000001111111000000000

car_plate_number service_type order_id -> employee_sin service_time service_date ;

111111111111111111101001111111000000001

employee_type -> difficulty_level frequency_paid wage commission ;

000001000000001001101000000000000000000

payment_method -> discount ;

000000000000000000000110000000000000000

order_id -> customer_licence order_issue_date order_due_date order_status payment_date ;

000000111100000000000000011111000000000

payment_id -> payment_method order_id payment_amount ;

000000111100000000000110011111110000000

part_id -> part_name stock_quantity ;

000000000000000000000000000000000001110

service_type part_id -> required_part_quantity ;

000000000000111110000000000000000011110

part_order_date part_id -> part_order_quantity ;

000000000000000000000000000000001101110

employee_sin -> employee_name employee_address employee_phone employee_hire_date employee_type quit

_date ;

111111000000001001101000000000000000001

customer_licence -> customer_name customer_address customer_phone ;

000000111100000000000000000000000000000

car_plate_number -> customer_licence modal ;

000000111111000000000000000000000000000

service_type -> service_description difficulty_level service_price service_name ;

000000000000111110000000000000000000000

service_type -> service_name ;

111111111111111111111111111111111111111

final display

employee_sin service_time service_date -> car_plate_number service_type ;

car_plate_number service_time service_date -> order_id ;

car_plate_number service_type order_id -> employee_sin service_time service_date ;

employee_type -> difficulty_level frequency_paid wage commission ;

payment_method -> discount ;

order_id -> customer_licence order_issue_date order_due_date order_status payment_date ;

payment_id -> payment_method order_id payment_amount ;

part_id -> part_name stock_quantity ;

service_type part_id -> required_part_quantity ;

part_order_date part_id -> part_order_quantity ;

employee_sin -> employee_name employee_address employee_phone employee_hire_date employee_type quit

_date ;

customer_licence -> customer_name customer_address customer_phone ;

car_plate_number -> customer_licence modal ;

service_type -> service_description difficulty_level service_price service_name ;

The long string of ones present in the ouput shows that the join of the different elements of the decomposition is lossless.

### 2.4.2 Justification of the 3NF requirement

``

`Most of the relational schemas are in BCNF and it appeared that the functional dependencies of schedule where violating this rule and were only in 3NF, as explained below:`
`1. The schedule conceptually involved following 5 abstract objects:`
`employee, car, order, servicetype, date&time.`
`2. It can be observed that date and time is rather one object because we`
`never use them separately and service type in short is "type".`
`3. The following is our assertions:`

`a) For any employee in a particular datetime, he can only do one type of service, for a specific car, under a specific order.That is :employee+datetime -> car, order, type;`
`b) For any particular car at any particular datetime, it must be under a specific order, if it is repaired by one or more employee.That is: car+ datetime -> order;`
`c) For any car which is under a specific order for a specific type of service, it must be done by a specific employee at a specific datetime. That is:`

`car + type + order -> employee, datetime;`
`decomposition #1:`
`{employee_sin,car_plate_number,service_type,service_time,service_date}`
`dependency is:`
`employee_sin service_time service_date -> car_plate_number`
`service_type ;`
`decomposition #2:`
`{car_plate_number,service_time,service_date,order_id}`
`dependency is:`
`car_plate_number service_time service_date -> order_id ;`
`decomposition #3:`
`{employee_sin,car_plate_number,service_type,service_time,service_date`
`,order_id}`
`dependency is:`
`employee_sin service_time service_date -> car_plate_number`
`service_type ;`
`car_plate_number service_time service_date -> order_id ;`
`car_plate_number service_type order_id -> employee_sin service_time`
`service_date ;`

¡¡

`"car_plate_number service_time service_date ->order_id ;" is BCNF violation but it fits 3NF because our candidate key which is {employee_sin service_time service_date} and`
`{car_plate_number service_type order_id}. The LHS of 2nd dependency "car_plate_number service_time service_date" is not a superkey, but the RHS "order_id" is prime member of key.`
¡¡

# 3 IMPLEMENTATION DETAILS

## 3.1 Database implementation details

We use various script files to implement our database and following is a list of the main ones.

a) createtable.sql //these are bulk table structure with minimum constraint like primary key

b) constraint.sql //these are the foreign key constraint

c) check.sql //these are "check" constraint other than foreign key

d) trigger1.sql , trigger2.sql,...trigger4.sql ¡ //these are triggers to maintain some insert or update integrity

e) scripts to insert data in static tables

f) result.sql //this is the random-generated sql script for all dynamic data

Also we have some scripts to ease the manipulation of the data:

g ) delete.sql //delete all datas of dynamic-input data, may need to run twice

h) count.sql //show record count of all dynamic data

Example of running the script count.sql after inserting all the datas with the result.sql script:

ORDER NUMBERS

----- ----------

order 1000

CAR NUMBERS

--- ----------

car 2000

CUSTOMER NUMBERS

-------- ----------

customer 1000

SCHEDULE NUMBERS

-------- ----------

schedule 1100

PAYMENT NUMBERS

------- ----------

payment 4000

## 3.2 Data integrity and database consistency

### 3.2.1 Triggers

We use in our design a certain number of triggers to automate some functionality and protect the integrity of the data inputted and manipulated. Following is a description of the nature and function of each of them:

a) trigger1.sql:

-checks if the employee difficulty_level is up to that of service_type he is assigned.

-checks if boss wants employee to work on weekend.

-checks if old finished records are to be removed.

b) trigger2.sql:

-checks if the inserted car plate number is consistant with the one in customer table. Preventing our employee from giving wrong car to wrong customer.

c) trigger3.sql:

-checks if the inserted order schedule has a valid order or not

-checks if the car is registered or not

-checks if the customer is the correct owner

d) trigger4.sql

-checks if the inserted payment amount exceed total amount of current order, including other paid amount.

-if input amount is null, it will automatically assume that customer is going to make full payment. So, it will insert full amount.

e) trigger5.sql

-suppose you hire a new worker and you want him to be immediately scheduled to work, this is the place. It will search through schedule table and update all those null employee_sin field that is after its hire date. It won't check the difficulty level.

f) trigger6.sql

-when an employee quits, we will delete all those schedules that is after his quit date.

g) trigger7.sql

-auto generated new order id for inserted order.

h) trigger8.sql

- auto generated new payment id for inserted payment.

i) trigger9.sql

- when order is updated, I mean the customer license is modified, the trigger will do the cascade update because Oracle does support this update cascade function.

j) trigger10.sql

- payment method within the same order should logically be unique. Since the customer split payment and pay both of them by cash is really meaningless.

Example of the action of a trigger when an employee quits:

insert into employee values (678912345, 'nick', 'monk', '514-762-9189', '05-may-04', 2,'31-may-04');

insert into schedule

values(678912345,'62KH88',2,0,'13-may-04',992);

select * from schedule where employee_sin=678912345 and

service_date='13-may-04';

--delete from employee where employee_sin=678912345;

update employee set quit_date='10-may-04' wheren employee_sin=678912345;

select * from employee where employee_sin=678912345;

select * from schedule where employee_sin=678912345 and

service_date='13-may-04';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

a) inserting a new employee to test.

b) insert a new schedule record into schedule.

c) update his quit date to BEFORE the schedule date.

d) You can observe that schedule record is gone after quit_date is modified.

e) The following is the running result:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> @test

1 row created.

1 row created.

EMPLOYEE_SIN CAR_PL SERVICE_TYPE SERVICE_TIME SERVICE_D ORDER_ID

------------ ------ ------------ ------------ --------- ----------

678912345 62KH88 2 0 13-MAY-04 992

1 row updated.

EMPLOYEE_SIN EMPLOYEE_NAME EMPLOYEE_ADDRESS EMPLOYEE_PHO EMPLOYEE_ E

------------ --------------- -------------------- ------------ --------- -

QUIT_DATE

---------

678912345 nick monk 514-762-9189 05-MAY-04 2

10-MAY-04

no rows selected

SQL>

++++++++++++++++++++++++++++++++++++++++++++++++++++

### 3.2.2 Random Generator

A random generator was implemented to ease the insertion of tuples in the dynamic tables and work in accordance with the triggers that will reject some inconsistent results created by it. The generator will create tuples in accordance with the project problem.

The triggers and constraints will reject the tuples generated for Saturdays or Sunday for example, and also when a service is associated with an employee that doesn¡¯t have the qualification. The generator will take care of applying the rules of percentage as required.

The generator also filters some inconsistent data before sending it.

¡¡

## 3.3 Interface level implementation and testing

Query 1

The first query allows the corrector to both display the content of the whole and to edit the content of relevant tables

Query 2

This query displays the name of the workers who did an oil change on any day of the week, and where the customer has done at least a part of the payment by cash.

Query 3

Given the current day, query 3, while computing the schedule for the following day, computes the percentage of unallocated work time

Query 4

According to the inputted date, the pay check is computed for the following Friday.

Query 5

Query 5 displays all car services planned for the day following the provided date.

Query 6

Query 6 provides a list of part to order for the day following the provided date, including a 20% of overstock.

Query 7

Query 7 allows the manager to enter the necessary data for adding a service to the order of a drop-in customer.

Query 8

This query allows a customer to remove services from an order. This transaction is allowed only up to the day before the current day (as provided).

Query 9

Query 9 generates a report including all services provided on a car between the two provided dates.

¡¡

# 4 LIMITATIONS AND FUTURE IMPROVEMENT

Time was the main constraint all along the development of this project that lacks a user friendly interface with different interfaces for administrator manager and customer.Security could also have been included with more time. The following E/R diagram shows add-on feature we would have wanted to implement:

¡¡

# 5 CONCLUSION

-a lot was learned but the 3 weeks duration we had for the project didn¡¯t allow us to learn as much as we would have wanted for the implementation part of this course.

- group management/communication was eased by the use a yahoo forum dedicated for this project with sharing capabilities of messages, files, calendar,, all centralized in one forum

# 6 MEMBER CONTRIBUTION

Qinzhe HUANG¡¯s log:

- Design of E-R diagram: 10 Hours
- Function-Dependency-Check-Tool-Program: 30 Hours
- Random-Data-Generator-Program: 10Hours
- SQL statement for Query2¡Query9: 50Hours
- HTML, PHP file for some of Query2¡Query9: 15Hours

Insoo KIM¡¯s log :

- Assignment #3, SQL and RA queries with Vincent(10 hours)
- Edited insert/update/ delete SQL queries, php and web pages (10 hours)
- Help Jean-Luc to finish the report(2 hours)
- Discussion of E/R diagram (6 hours)
- Testing Queries(4 hours)

Jean-Luc ORGIAZZI¡¯s log:

- Assignment #1, design E/R diagram (6 hours)
- Warm-up project: sql tables and queries, E/R diagram (6 hours)
- Helped to define the E/R model and Schema for the database of Main project (10 hours)
- Created insert/update/ delete SQL queries, php and web pages for tables (20 hours)
- Debugged and coded with Nick the queries and some triggers (10 hours)
- Redaction, review, diagrams of the entire report for the project with the help of Insoo and Vincent (15 hours)

Vincent THERIAULT¡¯s log:

- Assignment 1 involvement (7 hours)
- Warm-up project involvement (group effort) (6 hours)
- Design phase (10 hours)
- Implementation, discussion and revision of E/R model (18 hours)
- Assignment 3 direction/implementation in collaboration with Insoo (11 hours)
- Interface design/coding in collaboration with Eric (23 hours)
- Report redaction in collaboration with Insoo and Jean-Luc (5 hours)

Eric VALLEE ¡¯s log:

- Helped to define the E/R model and Schema for the database (10 hours)
- Help Vincent (who did the interface for the main query) with the JavaScript/PHP (2 hours)
- Finished the interface for the main queries (2 hours)
- Did the 4rth query (but Nick¡¯s version was the one that was implemented) (3 hours)
- Did the 8nth query and generated a form and another query from the result (10 hours)
- Did the 9nth query (6 hours)
- Tested some of the queries (1 hour)
- Helped determine the insertion/deletion strategy for tuples (query 1) (2 hours)

    ¡¡

    ¡¡