# Hotell DROP TABLE ★ ★ ★ ★

>Projektarbete av Daniel Frones, Emil Wallin och Patrick Snäll

## Om databasen
Denna databas ämnas användas för Hotell DROP TABLE© för deras 400 rum stora hotell centralt beläget i det drömska Katrinelund. Databasen täcker användningsområden så som användare, kunder, anställda och deras kontaktuppgifter, hotellets lokaler och bokningar därav, rabattkoder, samt ett lätt chattsystem där kunder och hotellanställda kan diskutera spörsmål.

### Tabeller
#### Persondata
| customer | | |
| -------------- | --------- | --- |
| FK (person.id) | person_id | INT |
| PK | id | INT |

| person | | |
| --------------- | ------------ | ------------ |
| PK | id | INT |
| | first_name | NVARCHAR(50) |
| FK (address.id) | address_id | INT |
| | last_name | NVARCHAR(50) |
| | tel_number | NVARCHAR(50) |
| | mail_address | NVARCHAR(50) |

| employees | | |
| ------------- | ------------- | ------------ |
| PK | id | INT |
| | salary | MONEY |
| | position | NVARCHAR(50) |
| FK(person.id) | person_id | INT |
| | work_phone_nr | INT |

| address | | |
| --------------- | -------------- | ------------ |
| PK | id | INT |
| | street_address | NVARCHAR(50) |
| FK (city.id) | city_id | INT |
| | postal_code | INT |
| FK (country.id) | country_id | INT |

| city | | |
| ---- | --------- | ------------ |
| PK | id | INT |
| | city_name | NVARCHAR(50) |

| country | | |
| ------- | ------------ | ------------ |
| PK | id | INT |
| | country_name | NVARCHAR(50) |

---
#### Rum
| room | | |
| --------------- | ------------------------ | ------------ |
| PK | id | INT |
| | room_number | INT |
| | room_description | NVARCHAR(50) |
| | room_cost | INT |
| FK (booking.id) | current_booking_id | INT = NULL |
| | booked_extra_beds | INT = 0 |
| | recommended_no_of_people | INT |
| | max_no_of_ppl | INT |

| room_description | | |
| ---------------- | ----------- | ------------ |
| | description | NVARCHAR(50) |
| PK | id | INT |

| room_booking | | |
| --------------------- | ------------- | ---- |
| FK (booking.id) | booking_id | INT |
| PK | id | INT |
| FK (room.room_number) | room_number | INT |
| | checkin_date | DATE |
| | checkout_date | DATE |

| room_history | | |
| --------------------- | ----------- | --- |
| FK (booking.id) | booking_id | INT |
| PK | id | INT |
| FK (room.room_number) | room_number | INT |

---
#### Bokningar och Betalning
| booking | | |
| --------------- | ---------------- | ------- |
| PK | id | INT |
| FK | customer_id | INT |
| | number_of_people | INT = 1 |
| | checkin_date | DATE |
| | late_checkin | BIT = 0 |
| | have_checked_in | BIT = 0 |
| | have_checked_out | BIT = 0 |
| | no_show | BIT = 0 |
| | checkin_date | DATE |
| FK(payment.id) | payment_id | INT |
| FK(coupon.id) | coupon_code | INT |
| FK(employee.id) | hotel_contact | INT |

| booking_history | | |
| --------------- | ---------------- | ------- |
| PK | id | INT |
| FK | customer_id | INT |
| | number_of_people | INT = 1 |
| | checkin_date | DATE |
| | late_checkin | BIT = 0 |
| | no_show | BIT = 0 |
| | checkin_date | DATE |
| FK(payment.id) | payment_id | INT |
| FK(coupon.id) | coupon_code | INT |
| FK(employee.id) | hotel_contact | INT |

| payment | | |
| ---------------------- | --------------- | ---------------- |
| PK | id | INT |
| | amount | MONEY |
| | description | NVARCHAR(50) |
| | time_of_payment | DATE = GETDATE() |
| FK (payment_method.id) | payment_method | INT |

| payment_method | | |
| -------------- | ---- | ------------ |
| PK | id | INT |
| | name | NVARCHAR(50) |

| coupon | | |
| ------------------- | ---------------- | ----------------- |
| PK | id | INT |
| | coupon_code | NVARCHAR(50) |
| | discount_amount | MONEY |
| | is_used | BIT = 0, NOT NULL |
| FK(discounttype.id) | discount_type_id | INT |

| discount_type | | |
| ------------- | ------------- | ------------ |
| PK | id | INT |
| | discount_tpes | NVARCHAR(50) |

---
#### Chattfunktion

| chat | | |
| --------------- | ----------- | --- |
| PK | id | INT |
| FK(customer.id) | customer_id | INT |

| message | | |
| ------------ | -------------- | ------------- |
| PK | id | INT |
| FK (chat.id) | chat_id | INT |
| | message_sender | NVARCHAR(50) |
| | message_text | NVARCHAR(500) |
| | timestamp | DATETIME |

---
### Vyer

#### show_employees
Visar samtliga anställda på hotellet oavsett rang.

---
#### show_expensive_rooms
Tar fram en lista med enbart alla rum som har ett pris på 140 USD upp till 200 USD.

---
#### show_budget_rooms
Tar fram en lista med enbart alla rum som har ett pris på 40 USD upp till 80 USD.

---
#### payment_statistics
Visar upp information om betalsätten och visar antalet gånger de respektive har använts.

---
#### all_rooms
Visar alla rum, om någon är incheckad, deras booking-id, när de checkar ut, samt rekommenderade maxantalet för personer i rummet.

---
#### show_all_bookings
Visar samtliga bokningar med information såsom check-in och check-out datum samt rummen bokade. Detta tillsammans med kontaktinformation.

---
#### previous_bookings
Tar fram en lista med alla tidigare bokningar, rummet som var bokat samt kontaktinformation till gästen.

---
#### show_not_booked_rooms
Visar lista på samtliga rum som för närvarande inte har en gäst som checkat in. Visar rumsinformation såsom rumsnummer,kostnad och rekommenderad max antal av gäster. Sedan visas även när rummets nästa bookning är planerad med ett datum.

---
#### show_occupied_rooms
Visar en lista på alla rum som för närvarande används av en gäst. Här är information om rumstyp, kostnad, rekommenderad antal gäster samt check-out datum.

---
#### show_all_chats
Visar en lista på samtliga chattar som ägt rum. Namn på personen som skickat meddelandet, vilket som var det senaste meddelandet i konversationen samt ett datum.

---
#### show_all_supervisors_rooms
Visar en lista på samtliga anställda på hotellet som har värdet i "position" till "supervisor". Vilka rum de har ansvar för, kontaktinformation till gästen som bor eller skall bo i det rummet samt planerat check-in/check-out datum.

---
### Procedurer

#### create_new_customer
Denna funktion tar in värden för skapandet av en ny användare. Den kontrollerar om inmatad stad och address redan återfinns i "Customer" tabellen. Gör den inte det så skapar den en ny row i dessa tabeller. Finns redan stad eller address så hämtar den helt enkelt värdet från tabellerna.

Parametrar:
@first_name - Kundens förnamn
@last_name - Kundens efternamn
@city_name - Stad som matats in av kunden
@street_address - Kundens gatuadress
@postal_code - Kundens postnummer
@tel_number - Kundens telefonnummer
@mail_address - Kunden mailadress
@country_name - Land som matats in av kunden

---
#### book_room
Bokar ett specifikt rum under en boknings bestämda tidsspann. Kontrollerar även att detta rum är ledigt under detta tidsspannet innan bokningen görs.

Parametrar:
@room_number - Rumsnummer som önskas bokas
@booking_id - Id för bokningen

---
#### unbook_room
Tar bort en kommande rumsbokning för en bokning.

Parametrar:
@room_number - Rumsnummer som önskas avbokas
@booking_id - Id för bokningen

---
#### create_booking
Skapar en ny bokning för en kund. Bokningen innehåller både incheckning- och utcheckningsdatum.

Parametrar:
@customer_id - Id för kund vars bokning ska skapas
@number_of_people - Antal personer som bokningen ska täcka
@checkin - Incheckningsdatum
@checkout - Utcheckningsdatum

---
#### delete_booking
Tar bort en bokning som tidigare gjorts. Tar även bort samtliga rumsbokningar som bokningen täcker.

Parametrar:
@id - Id för bokningen

---
#### show_rooms_of_type
Visar rum av en specifik typ: 'Single', 'Double', 'Suite', 'Master Suite'.

Parametrar:
@room_type - Beskrivningen av rummet (t.ex. 'Single')

---
#### set_late_checkin
Sätter en bokning till sen eller vanlig incheckning.

Parametrar:
@is_late_checkin - En BIT som sätter sen incheckning
@booking_number - Bokningsnummer för bokningen vars incheckning ska ändras

---
#### get_chat
Hämtar en kunds specifika chat. Skapar en chat och returnerar den nya om en tidigare chat inte existerar.

Parametrar:
@customer_id - Kundens id vars chat ska hämtas

---
#### create_chat
Skapar en chatt om en chatt inte redan finns för den användaren.

Parametrar:
@user_id - Avsändarens id

---
#### send_message
Denna funktion tar in ett meddelande samt ett användar_id. Den tar sedan värdet från inmatade användar ID:t och skapar därefter en ny chatt. Detta genom proceduren "get_chat". Om det redan finns en tidigare chat med samma ID skapas dock ingen ny utan den existerande uppdateras med det nya meddelandet.

send_message proceduren går sedan vidare för att kontrollera hurvida inmatat ID är en anställd eller kund, och sätter därefter värdet message_sender för att återspegla detta.

Parametrar:
@message - Meddelandet som skickas
@user_id - Kundens id
@is_hotel_staff - Visar på om det är hotellets personal som skickar meddelandet.

---
### Triggers
* Checked_in_booking
  Ändrar current_cooking när en bookings have_checked_in ändras
* new_booking_supervisor
  När bokning genomförs, tilldelas en supervisor till den bokningen. Väljer en
  supervisor som har minst antal aktiva uppdrag.
* add_room_history
 Adderar information till room_history när current_booking blivit NULL.

* check_out
 Lägger till information i tabellen booking_history när värdet "have_checked_out" uppdateras. Samt uppdaterar  värdet "current_booking_id" i tabellen Room. Sätter värdet till NULL så att den kan ta emot nästa gäst.

* create_room_booking_temp_trigger
 En temporär trigger som används för att lägga till room_bookings för bokningar som läggs in programmatiskt utan att proceduren book_room behöver användas för varje insert.

---
### Roller
#### manager
Behörigheter:
* SELECT
* UPDATE
* INSERT
* DELETE
* EXECUTE

#### employee
Behörigheter:
* SELECT (exklusive employees.salary)
* UPDATE (exklusive employees)
* INSERT (exklusive employees)
* DELETE (exklusive employees)

#### customer
Behörigheter:
* SELECT (exklusive employees, person och payment)
* EXECUTE

---
### Konton
#### Admin-konto
* CONTROL-behörighet
* Användarnamn: master_admin
* Lösenord: safeP@ssw0rd321
* User: admin_user

#### Manager/Chef
* Behörighet enligt manager-rollen
* Användarnamn: karin_svensson
* Lösenord: 'etthundn@mn123'
* User: karin_svensson_manager

#### Anställd
* Behörighet enligt employee-rollen
* Användarnamn: gustav_karlsson
* Lösenord: svagtP@ssword123
* User: gustav_karlsson_employee

#### Användare
* Behörighet enligt customer-rollen
* Användarnamn: knugen1337
* Lösenord: hotellP@ssword123
* User: knugen1337_customer

---
## Funktionsbeskrivning
### Persondata
För både kunders och anställdas kontaktuppgifter har en generell person-tabell (person) använts som i sin tur är kopplade till en adresstabell (address), stadstabell (city), och landstabell (country). För att sedan separera kunder och anställda används customer- och employee-tabeller. Tillhörande till dessa tabeller finns även en procedur som heter create_new_customer, som då tar in data, och skapar rader i de tabeller där det behövs.

### Bokningar och Rum
För att hålla koll på en bokning, dess incheckning och utcheckning samt annan relevant data, så används booking-tabellen. I bokningen finns även en hotelkontakt (hotel_contact) som då är en anställd med rollen "Supervisor" som agerar kontaktperson för bokningen. Room-booking används för att hålla koll på när och av vilken bokning som individuella rum är uppbokade. Det finns även historik för båda dessa tabeller i form av booking_history och room_history där det genom triggers (check_out, add_room_history) förs in data när en kund har checkat ut. En temporär trigger (create_room_booking_temp_trigger) används för att skapa room_bookings för en bokning när mockdata förs in i booking-tabellen (triggern tas bort efter att bokningarna förts in).

### Betalning och Rabattkoder
För att hålla reda på betalningsalternativen har vi skapat en övergriplig tabell vid namn (payment). Denna tar i sin tur hjälp av en under-tabell (payment_method) som enbart håller värdet av vilken betalningsmetod som används t.ex. paypal, swish osv. Här finns en vy (payment_statistics) som visar på antal gånger varje metod använts.

Kopplat till betalningar har vi även tabellerna coupon och discount_type som hanterar kuponger & kampanjer som används i samband med bokningar.

### Chatt & Recensioner
För att kunna hantera meddelande-funktionen finns tabellen (chat), för strutkuren på ett meddelande skapades tabellen (message). Tillsammans så sköter de hela processen för såväl hotellgäster som anställda. Tabellen (review) hanterar strukturen för recensioner från gäster.

Genom en vy vid namn (show_all_chats) så får man upp samtliga konversationer som skapats och det senaste meddelandet som skickats. Här har vi procedurer (get_chat) som ansvarar för att skapa ett nytt chatt-id & konversation för den aktuella kunden via dess id. Om kund-id redan återfinns och har en tidigare konversation så skapats ingen ny chatt utan tidigare meddelande uppdateras. Proceduren (send_message) hanterar inskickat meddelande från gäst men hjälper också att hålla isär meddelanden från anställda på hotellet och gäster.

---
## Diskussion
Då denna databas är det första större databasprojekt som vi skapat finns troligtvis fler brister än vi själva kan urskilja. I vissa fall kommer bristerna utav okunskap och osäkerhet på hur saker bör fungera för att säkerheten ska vara acceptabel, samt hur databaser brukar vara uppbyggda. Hur och vad som ska beröras av procedurer och triggers kontra hur mycket ansvar som ska läggas på andra delar av systemet för att se till att data förs in på rätt sätt är något som vi fann oss själva osäkra på flertalet gånger. Dock anser vi att utifrån vår sammanlagda kunskap skapat en databas som uppfyller de grundläggande funktionerna, samt utökar dessa med ytterligare tabeller, procedurer, triggers och vyer.

#### Möjliga/önskvärda lösningar

En önskad funktion som vi inte ännu skapat är möjligheten att debitera/fakturera kunder och företag. Vi har enbart tabeller som hanterar betalning via bokningar, men skulle det t.e.x bli en "no_show" eller skador på hotellets ägodelar efter en vistelse bör hotellet ha möjligheten att genom kontaktinformation skicka en faktura för omkostnaden.

Ett hotell utan en konferenssal är ju knappt ett hotell till att börja med. Så en utveckling kring att inte bara kunna boka rum utan även sektioner av hotellet för konferenser eller andra event hade varit ytterligare en önskvärd funktionalitet.

Det skulle även kunna skapas ytterligare lösningar för att bygga ut betalsystemet med procedurer och ytterligare data. Det är dock oklart för oss hur mycket av betalningssystemet som ska lämnas ut till en annan part och hur den datan som vi då behöver spara kommer se ut. Detta grundar sig i att vi tidigare inte använt något sådant system.

#### Eventuella brister

Något som vi idag kan se som en möjlig brist/problematik är faktumet att vi inte tillåter användare som inte är registrerade kunder att interagera med hotellet genom dess chatt-funktion. Något som vi dock tänker kan hanteras genom en annan del av programmets totala struktur än just databasen.

En identifierad brist är att man måste ha 4 st "dummy-bokningar" för att kunna hantera vilken supervisor som blir tilldelad till resp. bokning. SQL-skriptet har ingen bra lösning på hur man hanterar värden som startar ifrån null som ska adderas numeriskt.

Ett införande av power mode i front-end hade vart kritiskt för den totala upplevelsen +2
GG

DONE