Entity Relationship Diagram
#1. Entity Relationship Diagram
#2. a. & b.
One Room can be designated only one Room Type, whilst a particular Room Type can be assigned to several rooms (i.e. there may be many rooms that have double beds). Therefore, Room has a one-to-one relationship with RoomType and RoomType has a one-to-many relationship with Room.
From the table, it can be seen that a particular room can only be designated as belonging to one type / category, thus confirming that Room RoomType has a one-one relationship. From the table we can also see that RoomType occurs several times, thus confirming that RoomType Room has one-many relationship.
#2 C.
CREATE TABLE ROOM(
RoomId varchar2(5) Not Null;
RoomTypeId varchar2(10) Not Null;
AvailableStatus varchar2(10) Not Null;
PRIMARY KEY(RoomId);
FOREIGN KEY (RoomTypeId) references ROOMTYPE (RoomTypeID);
The cardinalities of the relationship between Room and RoomType shown in my ERD specifies that each Room is uniquely identified by a RoomId, which can be described by only one of all available RoomTypes. As such, each RoomId must be classified into a RoomType, and therefore, in accordance with entity integrity, both primary keys must not be null. Since RoomType will occur several times in the RoomTable, it should be “NotNull” as not null condition must be defined for the dependent table’s foreign key, but because of its multiple occurrence, RoomType cannot be “unique”.
#3. The Registration table has two (2) foreign keys, RoomId and EmpId. These foreign keys are attributes in one table, and then are primary keys in another relation. It can be seen that GuestId is the primary key of the Guest table, and serves as a link to the Registration table. Since the Guest ID can exist as a foreign key within the Registration Table, it can exist many times, hence the cardinality values between the tables were determined.
From the sample data above it can be seen that RoomId’s were
#2. a. & b.
One Room can be designated only one Room Type, whilst a particular Room Type can be assigned to several rooms (i.e. there may be many rooms that have double beds). Therefore, Room has a one-to-one relationship with RoomType and RoomType has a one-to-many relationship with Room.
From the table, it can be seen that a particular room can only be designated as belonging to one type / category, thus confirming that Room RoomType has a one-one relationship. From the table we can also see that RoomType occurs several times, thus confirming that RoomType Room has one-many relationship.
#2 C.
CREATE TABLE ROOM(
RoomId varchar2(5) Not Null;
RoomTypeId varchar2(10) Not Null;
AvailableStatus varchar2(10) Not Null;
PRIMARY KEY(RoomId);
FOREIGN KEY (RoomTypeId) references ROOMTYPE (RoomTypeID);
The cardinalities of the relationship between Room and RoomType shown in my ERD specifies that each Room is uniquely identified by a RoomId, which can be described by only one of all available RoomTypes. As such, each RoomId must be classified into a RoomType, and therefore, in accordance with entity integrity, both primary keys must not be null. Since RoomType will occur several times in the RoomTable, it should be “NotNull” as not null condition must be defined for the dependent table’s foreign key, but because of its multiple occurrence, RoomType cannot be “unique”.
#3. The Registration table has two (2) foreign keys, RoomId and EmpId. These foreign keys are attributes in one table, and then are primary keys in another relation. It can be seen that GuestId is the primary key of the Guest table, and serves as a link to the Registration table. Since the Guest ID can exist as a foreign key within the Registration Table, it can exist many times, hence the cardinality values between the tables were determined.
From the sample data above it can be seen that RoomId’s were