QAR (project of comp353)
A. First Edition
I don't know which edition of these file are.
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 onpapers (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 yournames, 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 tablethat 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.
¡¡
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.
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> </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.2 Objectives and Requirements
1.2.1 List of other requirements:
1.2.2 Queries and forms implemented:
2.1 E/R Diagram and Assumptions
2.4.1 List of all functional dependencies:
2.4.2 Justify all relational schemas are at least in 3NF
3.1 Database implementation details
3.2 Data integrity and database consistency
3.3 Interface level implementation and testing
4..... LIMITATIONS AND FUTURE IMPROVEMENT
¡¡
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.
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.
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.
¡¡
¡¡
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.
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 )
¡¡
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.
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.
¡¡
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
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>
++++++++++++++++++++++++++++++++++++++++++++++++++++
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.
¡¡
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.
¡¡
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:
¡¡
-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
Qinzhe HUANG¡¯s log:
Insoo KIM¡¯s log :
Jean-Luc ORGIAZZI¡¯s log:
Vincent THERIAULT¡¯s log:
Eric VALLEE ¡¯s log:
¡¡
¡¡
