Table List with Description and Populated Data (additional description only provided when field name is not self
explanatory)
mysql> show tables;
+------------------------+
| Tables_in_pet_grooming |
+------------------------+
| appointment
|
|
creates |
| customer
|
| employee
|
| invoice
|
| line_item
|
| pet
|
| product
|
| user |
+------------------------+
9
rows in set (0.00 sec)
User Table
mysql> describe user;
+----------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uName | varchar(20)
| | PRI | |
|
| password | varchar(20) | |
| | |
| lName | varchar(20)
| | |
| |
| mName | varchar(20) |
YES |
| NULL | |
| fName | varchar(20)
| | |
| |
| street | varchar(30) | YES
| | NULL |
|
| city | varchar(20) | YES
| | NULL |
|
| state |
char(2) | YES | |
NULL | |
|
zip | varchar(10) | YES | | NULL
| |
| phone | varchar(14) | YES
| | NULL |
|
+----------+-------------+------+-----+---------+-------+
10
rows in set (0.00 sec)
mysql> select * from user;
+-------------+----------+------------+--------+---------+------------------------------+-------------+-------+-------+----------------+
| uName | password | lName | mName | fName | street | city | state | zip |phone
|
+-------------+----------+------------+--------+---------+------------------------------+-------------+-------+-------+----------------+
| dgreen | fred | Green | Robert | Damon | 1234 West Point Dr. | Chico | CA
| 95928 |(530) 123-4567 |
| ahund | xyzwer | Hund
| B | Andy | 3029 East Ave | Chico | CA
| 95956 |(530) 492-2354 |
| fkkhan | dknelk | Khan
| n/a | Lor | 2023 California St | Chico | CA
| 95963 |(530) 340-3432 |
| klor | fsnelw | Lor
| n/a | Ka | 8192 Pensylvania
Blvd | Chico | CA
| 98239 |(530) 345-2304 |
| sspringorum | jdlsne | Springorum | n/a | Sharon | 9230 Wisconsin Ct | Chico | CA
| 93042 |(530) 829-2343 |
| micmouse | msaker | Mouse
| M | Mickey | 3943 Hole in the wall lane | Disney Land | CA | 92343 |(345) 343-3423 |
| minmouse | serksa | Mouse
| M | Minnie | 3944 Hole in the wall lane | Disney Land | CA | 92343 |(345) 343-3423 |
| pluto | flenae | Pluto
| D | Mr. | 392 Doghouse Ln | Disney Lane | ca | 92934 |(345)
394-2342 |
| dduck | flneeq | Duck
| D | Daffy | 394 Lake St | Warner | CA
| 93492 |(345) 392-2342 |
| donduck | fnelck | Duck
| D | Donnald
| 353 Orange Juice | Grove | CA
| 39430 |(342) 932-2533 |
| bbunny | mmelsq | Bunny
| B | Buggs | 301 Hole in the ground | Forest | CA
| 30203 |(342) 395-2492 |
| ysam | lennsq | Yosimite | S
| Sam | 383 Huntingnton
Rd | Forest | CA
| 93023 |(305) 402-2343 |
| wcyote | melzei | Cyote | B
| Wile | 3902 That Hurts | Painville | CA
| 93201 |(503) 390-8354 |
| rrunner | akene | Runner
| R | Road | 3942 I got away Ln | Speedville | CA
| 93220 |(560) 392-8124 |
| tdevil | nenrle | Devil
| T | Taz |
94903 Don't get in my way Ln | Hurryville | CA
| 39340 |(328) 954-3423 |
+-------------+----------+------------+--------+---------+------------------------------+-------------+-------+-------+----------------+
15
rows in set (0.00 sec)
Customer Table
mysql> describe customer;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+--------+----------------------+------+-----+---------+----------------+
| custId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| eMail | varchar(30) | YES
| | NULL | |
| uName | varchar(20) |
| | | |
+--------+----------------------+------+-----+---------+----------------+
3
rows in set (0.00 sec)
mysql> select * from customer;
+--------+------------------------+----------+
| custId | eMail | uName |
+--------+------------------------+----------+
| 1 | mickey@disney.com | micmouse |
| 2 | minnie@disney.com | minmouse |
| 3 | pluto@disney.com | pluto |
| 4 | daffy@warner.com | dduck |
| 5 | donnald@disney.com | donduck |
| 6 | bugs@warner.com | bbunny |
| 7 | yosimitesam@warner.com | ysam |
| 8 | wile@warner.com | wcyote |
| 9 | runner@warner.com | rrunner |
| 10 | taz@warner.com | tdevil |
+--------+------------------------+----------+
10
rows in set (0.00 sec)
Employee Table
mysql> describe employee;
+-----------+----------------------+------+-----+------------+----------------+
|
Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+------------+----------------+
| empId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| birthDate | date | |
| 0000-00-00 | |
| hireDate | date | |
| 0000-00-00 | |
| adminLev | smallint(5)
unsigned | | | 0 | |
| uName | varchar(20) |
| | | |
| jobTitle | varchar(20) | YES
| | NULL | |
+-----------+----------------------+------+-----+------------+----------------+
6
rows in set (0.13 sec)
mysql> select * from employee;
+-------+------------+------------+----------+-------------+-----------+
| empId | birthDate | hireDate | adminLev | uName | jobTitle |
+-------+------------+------------+----------+-------------+-----------+
| 1996 | 1977-12-24 | 1999-12-24
| 0 | sspringorum
| Manager |
| 1997 | 1974-08-28 | 2002-08-28
| 1 | klor | Secretary |
| 1998 | 1980-01-07 | 1987-01-07
| 5 | fkkhan | groomer |
| 1999 | 1979-12-24 | 2000-12-24
| 2 | ahund | groomer |
| 2000 | 1976-06-02 | 2003-05-02
| 3 | dgreen | groomer |
+-------+------------+------------+----------+-------------+-----------+
5
rows in set (0.00 sec)
Pet Table
mysql> describe pet;
+-----------+----------------------+------+-----+---------+----------------+
|
Field | Type | Null | Key | Default |
Extra |
+-----------+----------------------+------+-----+---------+----------------+
| petId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| name | varchar(20)
| | |
| |
| birthDate | date | YES | |
NULL | |
| notes | varchar(200)
| YES | | NULL
| |
| animal | varchar(10)
| | |
| |
| color | varchar(10)
| | |
| |
|
breed | varchar(10) |
| | | |
| custId | smallint(5)
unsigned | | | 0
| |
+-----------+----------------------+------+-----+---------+----------------+
8
rows in set (0.00 sec)
mysql> select * from pet;
+-------+-----------+------------+-----------------------+--------+------------+-----------+--------+
| petId | name | birthDate | notes | animal | color | breed | custId |
+-------+-----------+------------+-----------------------+--------+------------+-----------+--------+
| 1 | Fred | 2000-12-24 | Does not like water | dog
| black | rotweiler
| 1 |
| 2 | George | 2000-12-24 | Like bottle water. | dog | black/brow | rotweiler
| 1 |
| 3 | Blitz | 1999-10-04 | None | dog | white
| poodle | 2 |
| 4 | Klutz | 1989-03-14 | Make lots of noise. | bird | yellow
| canarie
| 5 |
| 5 | Jack | 1900-11-10 | Bites | rabbit | white | n/a |
7 |
| 6 | Lucky | 2003-11-10 | None | turtle | n/a | box |
3 |
| 7 | Slow Poke | 1996-11-30 | Very
mean | feret | gray/brown | n/a |
4 |
| 8 | Slick | 1997-01-01 | Champion Dog | dog | brown
| lab | 6 |
| 9 | Blitz | 1999-05-13 | Do not cut hair short |
dog | golden | retriever | 10 |
| 10 | Susy | 1999-02-18 | Like to excape |
rat | gray | n/a |
9 |
| 11 | Lucy | 2003-12-02 | none | fish | gold
| gold | 8 |
+-------+-----------+------------+-----------------------+--------+------------+-----------+--------+
11
rows in set (0.00 sec)
Product Table
mysql> describe product;
+-----------------+----------------------+------+-----+---------+----------------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+---------+----------------+
| prodId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| name | varchar(20)
| | |
| |
| cost |
float | |
| 0 | |
| type |
char(1) | |
| | | Is the
product is service or merchandise
| serviceInterval | smallint(5) unsigned | YES
| | NULL | | The
interval between service appointments. (service only)
| active |
char(1) | YES | |
NULL | | Is the
product active or inactive.
| numRemain | smallint(5)
unsigned | YES | | NULL
| | Number of remaining inventory. (merchandise
only)
| duration | smallint(5) unsigned | YES
| | NULL | | The time
blocks needed for service. (service only)
+-----------------+----------------------+------+-----+---------+----------------+
8
rows in set (0.08 sec)
mysql> select * from product;
+--------+----------------------+------+------+-----------------+--------+-----------+----------+
| prodId | name | cost | type | serviceInterval | active | numRemain
| duration |
+--------+----------------------+------+------+-----------------+--------+-----------+----------+
| 1 | Hair Cut |
25 | s | 1 | a |
0 | 1 |
| 2 |
Nail Cutting | 15 | s
| 3 | a |
0 | 1 |
| 3 | Bathing | 30 | s
| 1 | a |
0 | 1 |
| 4 | Deluxe Groom |
50 | s | 6 | a |
0 | 2 |
| 5 | Muzzle | 10 | p
| 0 | a |
23 | 0 |
| 6 | Ear Cleaning |
13 | s | 3 | a |
0 | 1 |
| 7 | Teeth Cleaning |
20 | s | 1 | a |
0 | 1 |
| 8 | Leash | 15 | p
| 0 | a |
25 | 0 |
| 9 | Collar | 15 | p
| 0 | a |
28 | 0 |
| 10 | Flea Collar |
18 | p | 0 | a |
28 | 0 |
| 11 | Release of anal flui
| 20 | s | 12 | a |
0 | 1 |
| 12 | Deskunking | 100 | s | 0 | a |
0 | 3 |
| 13 | Dematting |
30 | s | 1 | a |
0 | 1 |
| 14 | Flea and Tick bath | 35
| s | 1 | a |
0 | 1 |
| 15 | Chew Toy | 5 | p
| 0 | a |
20 | 0 |
| 16 | Bird Cage |
40 | p | 0 | a |
5 | 0 |
| 17 | Hair Brush |
8 | p | 0 | a |
13 | 0 |
+--------+----------------------+------+------+-----------------+--------+-----------+----------+
17
rows in set (0.00 sec)
Invoice Table
mysql> describe invoice;
+----------+----------------------+------+-----+---------+----------------+
|
Field | Type | Null | Key | Default |
Extra |
+----------+----------------------+------+-----+---------+----------------+
| invId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| status | varchar(10)
| | |
| | Invoice status can be open or closed depending if all
services are done.
| balance | float | |
| 0 | |
| reminder | date | YES | |
NULL | | Date to
e-mail customer to make another appointment.
| petId | smallint(5)
unsigned | | | 0
| |
+----------+----------------------+------+-----+---------+----------------+
5
rows in set (0.00 sec)
mysql> select * from invoice;
+-------+--------+---------+------------+-------+
| invId | status | balance |
reminder | petId
|
+-------+--------+---------+------------+-------+
| 1 | closed | 40 | 2003-12-31 | 3 |
| 2 | closed | 0
| 2004-01-31 | 4 |
| 3 | open
| 130 | 2004-03-16 | 10 |
| 4 | open
| 45 | 2004-02-16 | 1 |
| 5 | closed | 0 | 2004-02-03 | 5 |
| 6 | closed | 10 | 2004-05-17 | 2 |
| 7 | closed | 80 | 2004-02-17 | 6 |
| 8 | open
| 70 | 2004-01-15 | 9 |
| 9 | closed | 37 | 2004-10-18 | 8 |
| 10 | closed | 0 | 2004-11-13 | 7 |
| 11 | closed | 15 | 2003-12-29 | 11 |
+-------+--------+---------+------------+-------+
11
rows in set (0.00 sec)
Creates Table
mysql> describe creates;
+------------+----------------------+------+-----+------------+-------+
|
Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+------------+-------+
| appId | smallint(5)
unsigned | | PRI | 0 | |
| uName | varchar(20) |
| PRI | | |
| method | varchar(10)
| | | |
| How
the appointment was created I in house, phone or web.
| createDate | date | |
| 0000-00-00 | |
+------------+----------------------+------+-----+------------+-------+
4
rows in set (0.10 sec)
mysql> select * from creates;
+-------+-------------+----------+------------+
| appId | uName | method | createDate |
+-------+-------------+----------+------------+
| 3427 | micmouse | web
| 2003-11-26 |
| 3430 | donduck | web
| 2003-11-26 |
| 3427 | ahund | phone | 2003-11-27 |
| 3436 | ysam | phone | 2003-11-28 |
| 3741 | klor | in house | 2003-11-30 |
| 3757 | tdevil | web
| 2003-11-30 |
| 3801 | bbunny | phone
| 2003-11-30 |
| 4109 | klor | phone | 2003-12-01 |
| 4195 | wcyote | web
| 2003-12-01 |
| 4274 | sspringorum
| in house | 2003-12-02 |
| 4374 | minmouse | web
| 2003-12-02 |
+-------+-------------+----------+------------+
11
rows in set (0.00 sec)
Line Item Table
mysql> describe line_item;
+------------+----------------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra
|
+------------+----------------------+------+-----+---------+-------+
| lineNumber | smallint(5)
unsigned | | PRI | 0 |
|
| invId | smallint(5)
unsigned | | PRI | 0 |
|
| quantity | smallint(5) unsigned |
| | 0 |
|
| prodId | smallint(5)
unsigned | | | 0
| |
| lineTotal | smallint(6) |
| | 0 |
|
| empId | smallint(5)
unsigned | YES | | NULL
| | Employee
who performed service.
+------------+----------------------+------+-----+---------+-------+
6
rows in set (0.08 sec)
mysql> select * from line_item;
+------------+-------+----------+--------+-----------+-------+
| lineNumber | invId
| quantity | prodId | lineTotal
| empId |
+------------+-------+----------+--------+-----------+-------+
| 1 | 1 |
1 | 3 | 30 | 1998 |
| 2 | 1 |
1 | 5 | 10 | 1998 |
| 1 | 2 |
1 | 15 | 40 | 1998 |
| 1 | 3 |
1 | 1 | 25 | 1998 |
| 2 | 3 |
2 | 15 |
10 | 1999
|
| 1 | 4 |
1 | 9 | 15 | 1999 |
| 1 | 5 |
3 | 17 | 24 | 1999 |
| 1 | 6 |
1 | 11 | 20 | 1996 |
| 1 | 7 |
1 | 3 | 30 | 1996 |
| 1 | 8 |
1 | 12 | 100 | 1996 |
| 1 | 9 |
1 | 7 | 20 | 1998 |
| 1 |
10 | 1 | 14 |
35 | 1997
|
| 1 |
11 | 1 | 3 |
30 | 1997
|
+------------+-------+----------+--------+-----------+-------+
13
rows in set (0.00 sec)
Appointment Table
mysql> describe appointment;
+---------+----------------------+------+-----+------------+----------------+
|
Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+------------+----------------+
| appId | smallint(5)
unsigned | | PRI | NULL | auto_increment
|
| slotNum | smallint(5)
unsigned | | | 0 | |
| date | date | |
| 0000-00-00 | |
| time | time | |
| 00:00:00
| |
| invId | smallint(5)
unsigned | YES | | NULL | | If invId is null the appt. slot is open.
+---------+----------------------+------+-----+------------+----------------+
5
rows in set (0.00 sec)
mysql> select * from
appointment where appid > 3420 && appid < 3455; (table is several thousand entries
long; output was limited to save trees)
+-------+---------+------------+----------+-------+
| appId | slotNum
| date | time | invId |
+-------+---------+------------+----------+-------+
| 3421 | 1 | 2003-12-01 | 15:00:00 | NULL |
| 3422 | 2 | 2003-12-01 | 15:00:00 | NULL |
| 3423 | 3 | 2003-12-01 | 15:00:00 | NULL |
| 3424 | 1 | 2003-12-01 | 16:00:00 | NULL |
| 3425 | 2 | 2003-12-01 | 16:00:00 | NULL |
| 3426 | 3 | 2003-12-01 | 16:00:00 | NULL |
| 3427 | 1 | 2003-12-01 | 17:00:00 | 1 |
| 3428 | 2 | 2003-12-01 | 17:00:00 | NULL |
| 3429 | 3 | 2003-12-01 | 17:00:00 | NULL |
| 3430 | 1 | 2003-12-01 | 18:00:00 | 2 |
| 3431 | 2 | 2003-12-01 | 18:00:00 | 3 |
| 3432 | 3 | 2003-12-01 | 18:00:00 | NULL |
| 3433 | 1 | 2003-12-02 | 08:00:00 | NULL |
| 3434 | 2 | 2003-12-02 | 08:00:00 | NULL |
| 3435 | 3 | 2003-12-02 | 08:00:00 | NULL |
| 3436 | 1 | 2003-12-02 | 09:00:00 | 4 |
| 3437 | 2 | 2003-12-02 | 09:00:00 | NULL |
| 3438 | 3 | 2003-12-02 | 09:00:00 | NULL |
| 3439 | 1 | 2003-12-02 | 10:00:00 | NULL |
| 3440 | 2 | 2003-12-02 | 10:00:00 | NULL |
| 3441 | 3 | 2003-12-02 | 10:00:00 | NULL |
| 3442 | 1 | 2003-12-02 | 11:00:00 | NULL |
| 3443 | 2 | 2003-12-02 | 11:00:00 | NULL |
| 3444 | 3 | 2003-12-02 | 11:00:00 | NULL |
| 3445 | 1 | 2003-12-02 | 12:00:00 | NULL |
| 3446 | 2 | 2003-12-02 | 12:00:00 | NULL |
| 3447 | 3 | 2003-12-02 | 12:00:00 | NULL |
| 3448 | 1 | 2003-12-02 | 13:00:00 | NULL |
| 3449 | 2 | 2003-12-02 | 13:00:00 | NULL |
| 3450 | 3 | 2003-12-02 | 13:00:00 | NULL |
| 3451 | 1 | 2003-12-02 | 14:00:00 | NULL |
| 3452 | 2 | 2003-12-02 | 14:00:00 | NULL |
| 3453 | 3 | 2003-12-02 | 14:00:00 | NULL |
| 3454 | 1 | 2003-12-02 | 15:00:00 | NULL |
+-------+---------+------------+----------+-------+
39
rows in set (0.01 sec)
Reports
Peak
Appointment Time Report.
mysql> select time, count( time
) AS cnt from appointment where invId
is not null group by time order by cnt desc;
+----------+-----+
| time | cnt |
+----------+-----+
| 09:00:00 | 3 |
| 13:00:00 | 3 |
| 18:00:00 | 2 |
| 17:00:00 | 1 |
| 11:00:00 | 1 |
| 15:00:00 | 1 |
+----------+-----+
6
rows in set (0.00 sec)
Unpaid Invoice Report
mysql> select invoice.invId, lName, fName, name, balance, date
-> from
invoice, customer, pet, user, appointment
-> where user.uName=customer.uName and pet.custId=customer.custId and invoice.petId = pet.petId and invoice.invId =
appointment.invId and balance > 0 and date
>= '2003-12-01' and date <= '2003-12-31';
+-------+--------+--------+--------+---------+------------+
| invId | lName | fName | name
| balance | date |
+-------+--------+--------+--------+---------+------------+
| 1 | Mouse | Minnie | Blitz |
40 | 2003-12-01 |
| 3 | Runner | Road | Susy |
130 | 2003-12-01 |
| 4 | Mouse | Mickey | Fred |
45 | 2003-12-02 |
| 6 | Mouse | Mickey | George | 10 | 2003-12-11 |
| 7 | Pluto | Mr. | Lucky |
80 | 2003-12-13 |
| 8 | Devil | Taz | Blitz
| 70 | 2003-12-22 |
| 9 | Bunny | Buggs | Slick
| 37 | 2003-12-25 |
| 11 | Cyote | Wile | Lucy
| 15 | 2003-12-30 |
+-------+--------+--------+--------+---------+------------+
8
rows in set (0.00 sec)
Schedule Report
mysql> select date, time, lname, fname, name as petName, animal
-> from
appointment, invoice, user, customer, pet
-> where appointment.invid=invoice.invid
and customer.uname=user.uname
and pet.custid=customer.custid
and
-> pet.petid=invoice.petid and date >= '2003-12-01' and date <= '2003-12-31';
+------------+----------+----------+---------+-----------+--------+
| date | time | lname | fname | petName | animal |
+------------+----------+----------+---------+-----------+--------+
|
2003-12-01 | 17:00:00 | Mouse
| Minnie |
Blitz | dog |
|
2003-12-01 | 18:00:00 | Duck
| Donnald | Klutz | bird
|
|
2003-12-01 | 18:00:00 | Runner
| Road | Susy | rat
|
|
2003-12-02 | 09:00:00 | Mouse
| Mickey |
Fred | dog |
|
2003-12-11 | 11:00:00 | Yosimite |
Sam | Jack | rabbit |
|
2003-12-11 | 15:00:00 | Mouse
| Mickey |
George | dog |
|
2003-12-13 | 09:00:00 | Pluto
| Mr. | Lucky | turtle |
|
2003-12-22 | 13:00:00 | Devil
| Taz
| Blitz | dog |
|
2003-12-25 | 09:00:00 | Bunny
| Buggs
| Slick | dog |
|
2003-12-27 | 13:00:00 | Duck
| Daffy | Slow Poke | feret |
|
2003-12-30 | 13:00:00 | Cyote | Wile
| Lucy | fish |
+------------+----------+----------+---------+-----------+--------+
11
rows in set (0.00 sec)
