Detailed Analysis of ERD and SQL Queries for HRM Database System

This assignment presents an analysis of an HRM (Human Resource Management) database, focusing on its Entity-Relationship Diagram (ERD) and SQL queries. The solution includes descriptions of various database tables such as 'bookings', 'country_lookup', 'facility_list', 'guests', 'notes', 'payments', 'payment_methods', 'reservations', 'rooms', 'room_facility', 'room_type', 'service_charges', 'service_type', 'staff', and 'users'. The SQL queries demonstrate data retrieval and manipulation, including retrieving guest names and emails, selecting rooms based on criteria, querying payment methods, finding guests with missing mobile numbers, and searching for guests based on suburb. The analysis provides insights into the database structure, relationships between tables, and practical examples of how SQL is used to extract and present information. This assignment is designed to provide a practical understanding of database design and querying within the context of an HRM system.

ERD
HRM

Database tables
MariaDB [hrm]> describe bookings;
+--------------+-----------+------+-----+---------------------
+-----------------------------+
| Field | Type | Null | Key | Default | Extra
|
+--------------+-----------+------+-----+---------------------
+-----------------------------+
| BookingID | int(11) | NO | PRI | NULL |
|
| GuestId | int(11) | NO | MUL | NULL |
|
| ROOMNUM | int(11) | NO | MUL | NULL |
|
| CheckInDate | date | NO | | NULL |
|
| CheckInTime | timestamp | NO | | CURRENT_TIMESTAMP | on
update CURRENT_TIMESTAMP |
| CheckOutDate | date | NO | | NULL |
|
| CheckOutTime | timestamp | NO | | 0000-00-00 00:00:00 |
|
| NUMDAYS | int(11) | NO | | NULL |
|
| INVOICENUM | int(7) | NO | | NULL |
|
| INVOICEDATE | date | NO | | NULL |
|
+--------------+-----------+------+-----+---------------------+--------
MariaDB [hrm]> describe country_lookup;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| COUNTRYID | int(4) | NO | PRI | NULL | |
| COUNTRY | varchar(20) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe facility_list;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| FacilityID | int(4) | NO | PRI | NULL | |
| Facility | char(10) | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe guests;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| guestID | int(4) | NO | PRI | NULL | |
| title | char(10) | NO | | NULL | |
| GFirstName | char(20) | NO | | NULL | |
| GLastNAME | char(20) | NO | | NULL | |
| Address | varchar(255) | NO | | NULL | |

| Suburb | char(30) | NO | | NULL | |
| State | char(3) | NO | | NULL | |
| PostCode | int(4) | NO | | NULL | |
| Country | int(11) | NO | | NULL | |
| Mobile | int(9) | NO | | NULL | |
| PhoneOther | int(9) | NO | | NULL | |
| emailID | varchar(255) | NO | | NULL | |
| Licence_No | varchar(10) | NO | | NULL | |
| PassportNumber | int(9) | NO | | NULL | |
| Note | varchar(255) | NO | | NULL | |
| DATE_ENTERED | date | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
16 rows in set (0.02 sec)
MariaDB [hrm]> describe notes
-> ;
+--------------+--------------+------+-----+-------------------
+-----------------------------+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+-------------------
+-----------------------------+
| Note_ID | int(4) | NO | PRI | NULL |
|
| GuestId | int(11) | NO | MUL | NULL |
|
| Entrydate | date | NO | | NULL |
|
| EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | on
update CURRENT_TIMESTAMP |
| Initials | char(2) | NO | | NULL |
|
| Assigned_to | int(11) | YES | MUL | NULL |
|
| Remarks | varchar(255) | NO | | NULL |
|
| FollowupDate | int(11) | NO | | NULL |
|
| Complete | int(11) | NO | | NULL |
|
+--------------+--------------+------+-----+-------------------
+-----------------------------+
9 rows in set (0.02 sec)
MariaDB [hrm]> describe payments;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| PaymentID | int(11) | NO | PRI | NULL | |
| BookingID | int(11) | NO | MUL | NULL | |
| PaymentAmount | decimal(10,0) | NO | | NULL | |
| PaymentDate | date | NO | | NULL | |
| PaymentMethod | int(11) | NO | MUL | NULL | |
| CheckNumber | int(11) | YES | | NULL | |
| CreditCardType | int(11) | YES | | NULL | |
| CreditCardNumber | int(11) | YES | | NULL | |
| CreditCardNAME | char(20) | YES | | NULL | |
| CreditCardExpDate | date | YES | | NULL | |

| PAYMENT_TERMS | varchar(255) | YES | | NULL | |
| Note | varchar(255) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
12 rows in set (0.02 sec)
MariaDB [hrm]> describe payment_methods;
+-----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| PaymentMethodID | int(11) | NO | PRI | NULL | |
| PAYMENTMETHOD | char(10) | NO | | NULL | |
| CREDITCARD | tinyint(1) | NO | | NULL | |
+-----------------+------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
MariaDB [hrm]> describe reservations;
+-----------------------+------------+------+-----
+---------------------+-----------------------------+
| Field | Type | Null | Key | Default
| Extra |
+-----------------------+------------+------+-----
+---------------------+-----------------------------+
| BookingId | int(11) | NO | PRI | NULL
| |
| GuestId | int(11) | NO | MUL | NULL
| |
| BookingDate | date | NO | | NULL
| |
| BookingTime | timestamp | NO | | CURRENT_TIMESTAMP
| on update CURRENT_TIMESTAMP |
| NumAdults | int(11) | NO | | NULL
| |
| NumChildren | int(11) | NO | | NULL
| |
| ExpectedArrivalDate | date | NO | | NULL
| |
| ExpectedArrivalTime | timestamp | NO | | 0000-00-00 00:00:00
| |
| ExpectedDepartureDate | date | NO | | NULL
| |
| ExpectedDepartureTime | timestamp | NO | | 0000-00-00 00:00:00
| |
| VIP | tinyint(1) | NO | | NULL
| |
| NOTES | int(11) | YES | | NULL
| |
| ROOMNUM | int(11) | NO | MUL | NULL
| |
| Confirmed | tinyint(1) | NO | | NULL
| |
| Cancelled | tinyint(1) | NO | | NULL
| |
+-----------------------+------------+------+-----
+---------------------+-----------------------------+
15 rows in set (0.02 sec)
MariaDB [hrm]> describe rooms;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+

| RoomId | int(11) | NO | PRI | NULL | |
| ROOMNUM | int(11) | NO | UNI | NULL | |
| RATE | decimal(10,0) | NO | | NULL | |
| NUMBEDS | int(11) | NO | | NULL | |
| VIEW | tinyint(1) | NO | | NULL | |
| QUIET | tinyint(1) | NO | | NULL | |
| SUITE | tinyint(1) | NO | | NULL | |
| DELUXE | tinyint(1) | NO | | NULL | |
| ROOM_TYPE | int(11) | NO | | NULL | |
+-----------+---------------+------+-----+---------+-------+
9 rows in set (0.02 sec)
MariaDB [hrm]> describe room_facility;
+------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------+------+-----+---------+-------+
| RoomID | int(4) | NO | PRI | NULL | |
| FacilityID | int(4) | NO | PRI | NULL | |
+------------+--------+------+-----+---------+-------+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe room_type;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| RoomTypeId | int(11) | NO | PRI | NULL | |
| RoomType | char(10) | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe service_charges;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| BookingId | int(11) | NO | PRI | NULL | |
| INVOICENUM | int(11) | NO | | NULL | |
| ServiceID | int(11) | NO | PRI | NULL | |
| SERVDESC | varchar(255) | NO | | NULL | |
| SERVDATE | date | YES | | NULL | |
| SERVCHG | decimal(10,2) | NO | | NULL | |
| Quantity | int(11) | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
MariaDB [hrm]> describe service_type;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ServiceId | int(11) | NO | PRI | NULL | |
| SERVCODE | char(2) | NO | | NULL | |
| SERVDESC | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+

MariaDB [hrm]> describe staff;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| Staff_ID | int(11) | NO | PRI | NULL | |
| FirstName | char(20) | NO | | NULL | |
| LastName | char(30) | NO | | NULL | |
| Title | char(20) | NO | | NULL | |
| WorkPhone | int(10) | YES | | NULL | |
| EmploymentType | char(2) | NO | | NULL | |
+----------------+----------+------+-----+---------+-------+
MariaDB [hrm]> describe users;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| User_ID | int(11) | NO | PRI | NULL | |
| StaffId | int(11) | NO | | NULL | |
| Initials | char(2) | NO | | NULL | |
| FName | char(20) | NO | | NULL | |
| Surname | char(30) | NO | | NULL | |
| LoginName | char(2) | NO | | NULL | |
| password | char(2) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
Queries
1. MariaDB [hrm]> select concat(gfirstName, ' ', glastName) as GuestName,
emailID from guests order by glastName desc;
+-----------------+-----------------------------+
| GuestName | emailID |
+-----------------+-----------------------------+
| Vaughn Wilson | |
| Dan Van Buren | |
| Don Tompkins | [e-mail] |
| U. Sheppard | |
| Noel Ritter | [e-mail] |
| Len Raymond | |
| E. Poirot | |
| Steve Marsh | |
| Y. Kent | |
| Wendy Johnson | [e-mail] |
| Ken Jefferson | |
| William Jainani | [e-mail] |
| Bill Hamlin | |
| Larry Gerry | |
| Mark Franklin | [e-mail] |
| Jim Daton | [e-mail] |
| Yvonne Colfax | |
| Sam Clinton | |
| Edwina Calhoun | |
| James Calgary | [e-mail] |
| Rick Burr | [e-mail] |

| Julie Blunt | |
| Nancy Adams | |
+-----------------+-----------------------------+
2. MariaDB [hrm]> select roomnum, rate, numbeds from Rooms where numbeds =
2 AND quiet = 1 and view =1;
+---------+------+---------+
| roomnum | rate | numbeds |
+---------+------+---------+
| 23 | 0 | 2 |
| 25 | 0 | 2 |
| 26 | 0 | 2 |
| 27 | 0 | 2 |
+---------+------+---------+
4 rows in set (0.03 sec)
3. MariaDB [hrm]> select r.roomnum, r.rate, b.numdays , p.paymentAmount,
pm.PAYMENTMETHOD from rooms r, bookings b, payments p, payment_methods pm
where pm.paymentMethodID = p.PaymentMethod AND p.bookingID = b.BookingID
AND b.ROOMNUM = r.ROOMNUM AND pm.PAYMENTMETHOD IN ('VISA', 'MasterCard')
group by pm.`PAYMENTMETHOD`;
+---------+------+---------+---------------+---------------+
| roomnum | rate | numdays | paymentAmount | PAYMENTMETHOD |
+---------+------+---------+---------------+---------------+
| 6 | 0 | 1 | 795 | Mastercard |
| 5 | 0 | 1 | 450 | VISA |
+---------+------+---------+---------------+---------------+
4. MariaDB [hrm]> select glastname, gfirstName, g.mobile, g.emailID from
guests g where g.mobile ='' order by glastName, gfirstName;
+-----------+------------+--------+----------------------+
| glastname | gfirstName | mobile | emailID |
+-----------+------------+--------+----------------------+
| Calgary | James | 0 | [e-mail] |
| Colfax | Yvonne | 0 | |
| Daton | Jim | 0 | [e-mail] |
| Kent | Y. | 0 | |
| Marsh | Steve | 0 | |
| Poirot | E. | 0 | |
| Raymond | Len | 0 | |
| Wilson | Vaughn | 0 | |
+-----------+------------+--------+----------------------+
5. MariaDB [hrm]> select glastname from guests where suburb LIKE '%hill%'
order by concat(gfirstName, ' ', gLastName);
+-----------+
| glastname |
+-----------+
| Blunt |

| Adams |
| Jainani |
| Colfax |
6. MariaDB [hrm]> select gfirstName, glastname, n.remarks from guests g,
notes n where g.guestID = n.guestID AND n.followupDate < sysDate() and
n.complete = 0;
+------------+-----------+-----------------------------------+
| gfirstName | glastname | remarks |
+------------+-----------+-----------------------------------+
| Nancy | Adams | 2 beds/cable |
| Rick | Burr | Wake-up call 6:00 am. Newspaper. |
| E. | Poirot | Requested firmer bed. |
| Len | Raymond | Wants brochure on surf school. |
| Y. | Kent | Wi-Fi not working. |
| U. | Sheppard | Requested late checkout time |
+------------+-----------+-----------------------------------+
7. MariaDB [hrm]> select g.glastname, b.roomnum, b.checkInDate,
sc.quantity, sc.servdesc, (sc.quantity * sc.servchg) as TotalChargeAmount
from guests g, bookings b, service_charges sc, service_type s where
s.serviceID = sc.serviceID and g.guestID = b.guestID and b.bookingID =
sc.bookingID and s.serviceID = 7;
+-----------+---------+-------------+----------+-------------
+-------------------+
| glastname | roomnum | checkInDate | quantity | servdesc |
TotalChargeAmount |
+-----------+---------+-------------+----------+-------------
+-------------------+
| Ritter | 4 | 2017-08-12 | 1 | Holding Fee |
50.00 |
+-----------+---------+-------------+----------+-------------
+-------------------+
8. MariaDB [hrm]> select count(r.roomID) as "Count of room Type",
r.room_type from rooms r group by r.room_type;
+--------------------+-----------+
| Count of room Type | room_type |
+--------------------+-----------+
| 6 | 1 |
| 4 | 2 |
| 20 | 3 |
+--------------------+-----------+

9. MariaDB [hrm]> select r.roomnum, f.facility from rooms r, facility_list
f, room_facility rf where r.roomID = rf.roomID and
rf.facilityID=f.facilityID and r.roomnum IN (18, 19);
+---------+------------+
| roomnum | facility |
+---------+------------+
| 18 | Sauna |
| 18 | Sofa bed |
| 19 | TV |
| 19 | Safe depos |
+---------+------------+
4 rows in set (0.00 sec)
10. MariaDB [hrm]> select glastName, gfirstName from guests where guestID
NOT IN (select guestID from bookings);
+-----------+------------+
| glastName | gfirstName |
+-----------+------------+
| Marsh | Steve |
+-----------+------------+
11. MariaDB [hrm]> select sum(p.paymentAmount), pm.paymentmethod from
payments p, payment_methods pm where p.paymentMethod = pm.paymentMethodID
and p.paymentDate > '2014-07-01' group by pm.paymentMethod;
+----------------------+---------------+
| sum(p.paymentAmount) | paymentmethod |
+----------------------+---------------+
| 2526 | Amex |
| 1463 | Cash |
| 1231 | Cheque |
| 1938 | Mastercard |
| 2963 | VISA |
+----------------------+---------------+
5 rows in set (0.02 sec)
PART C: Report to department manager
During the course of development, I faced various challenges. First was the set up of XAMPP
and make phpMyAdmin working due to some username issue. I resolved it by uninstalling
and then installing a 32-bit xampp on my windows machine. Next, challenge was the data
import in the database through CSV file. Crating files in CSV format was still an easy task
but there were some rows present at the beginning of each sheet describing the table
structure. Whenever, I imported CSV file in the database, it showed me CSV column

mismatch error. Then, I tried deleting those rows and then importing the CSV file. Then it
worked perfectly. Critical issue I faced and was not able to resolve was the complete
importing of Service_charges data. I created the correct CSV file, but whenever I tried
importing it, it gave me primary key duplicate issue. I even made (bookingID, serviced) as
primary key but there were still some shows having duplicate entry for this combination.
Also, I thought of including invoicenum in this primary key but there were some entries
which are having same values for all three columns. But that also, it didn’t work. This was
the only data which was not loaded successfully.
Next unresolved issue was feeding correct timestamp in the tables like in bookings table. I
formatted the data before importing but still the database tables like booking have 0000-00-
00 as an entry for all TIMESTAMP based values.