Member Avatar for Komlika_1

Consider the following database
Room (room_no,room_name,room_type,charges)
Guest(Guest_code, Gname,city)
The relationship is as follows: Room-Guest: one-to-one. room_type can have values as either ‘AC’ or
‘NonAC’.
Q 2) A Create the above database in PostGreSQL and insert sufficient records.

  • 4 Contributors
  • 3 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by john_111

Dani AI

Generated

a quick way to meet the spec and still keep things normalized is to separate static entities (Room, Guest) from the current assignment between them. As hints, the missing piece is a link table that enforces the one-to-one at any given moment. That way, rooms can exist without a guest, and guests can exist before they are assigned a room. If you later need history (check-in/out), replace the link with a dated Stay/Booking table.

-- minimal schema
CREATE TABLE room (
  room_no   integer PRIMARY KEY,
  room_name text    NOT NULL,
  room_type text    NOT NULL CHECK (room_type IN ('AC','NonAC')),
  charges   numeric(10,2) NOT NULL CHECK (charges >= 0)
);

CREATE TABLE guest (
  guest_code integer PRIMARY KEY,
  gname      text NOT NULL,
  city       text
);

-- one-to-one link: each room appears at most once, and each guest at most once
CREATE TABLE room_guest (
  room_no    integer PRIMARY KEY REFERENCES room(room_no) ON DELETE CASCADE,
  guest_code integer UNIQUE NOT NULL REFERENCES guest(guest_code) ON DELETE CASCADE
);

-- sample data
INSERT INTO room(room_no,room_name,room_type,charges) VALUES
  (101,'Lotus','AC',2500.00),
  (102,'Jasmine','NonAC',1800.00),
  (103,'Tulip','AC',2200.00);

INSERT INTO guest(guest_code,gname,city) VALUES
  (1,'Asha','Pune'),
  (2,'Ravi','Delhi'),
  (3,'Meera','Mumbai');

-- current assignments (one-to-one is enforced)
INSERT INTO room_guest(room_no,guest_code) VALUES
  (101,1),
  (102,2);

-- check current occupancy (unassigned rooms still show)
SELECT r.room_no, r.room_name, r.room_type, g.gname, g.city
FROM room r
LEFT JOIN room_guest rg USING (room_no)
LEFT JOIN guest g ON g.guest_code = rg.guest_code
ORDER BY r.room_no;

Notes:

  • The CHECK keeps room_type to AC/NonAC.
  • The UNIQUE on guest_code plus the PK on room_no enforce a true one-to-one.
  • If you need stays over time, create a stay(room_no, guest_code, check_in, check_out) table and enforce no overlapping stays per room. and are right: once you know which model you need, implementation is straightforward.

Recommended Answers

All 3 Replies

Member Avatar for Dani

Creating the database on your database server isn't something we can do for you via a message forum. That's like saying, "Install this app on my phone." I can't exactly do it without physical access to your phone, of course. Do you have a specific question about how to do it?

Member Avatar for john_111

I'll give you a clue - there is at least one table missing from the spec depending on the business rules used, and they want you to work that out and add it/them as well.
Think normalisation.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.