How Well My Table is Normalized? - Relational Database

I posted two table DDLs - Motel and MotelRoom. These tables are intended to be normalized up to 3rd normal form.

Regarding Motel:

I had Country, City and Province each seperated in its own table and then
i just put the foreign key CountryID, ProvinceID and CityID in the Motel's table. However,
an expert long time ago didn't recommend to do it that way because it just adds an overhead when you want to query the data. I'm not convinced yet since his recommendation doesn't fulfill the normalization process. I don't know i might be wrong. Any idea?

Regarding MotelRoom:

I had roomType and bedType each in its own table and then i just added
the foreign key roomTypeID and bedTypeID in MotelRoom. However, one
of the dba's suggest me to put rid of roomType and bedType tables and
put these two as columns with CHECK constraints. I'm kind of confused
now, does this fullfill normalization process?

Thanks

CREATE TABLE Motel
(
        motelID     NUMBER(7)    ,  CONSTRAINT motel_motelid_pk     PRIMARY KEY(motelID),
        branchname  VARCHAR2(50)    CONSTRAINT motel_branchname_nn  NOT NULL            ,
        numofrooms  NUMBER(10)      CONSTRAINT motel_numofrooms_nn  NOT NULL            ,
        address1    VARCHAR2(100)   CONSTRAINT motel_address1_nn    NOT NULL            ,
        address2    VARCHAR2(100)                                                       ,
        city        VARCHAR2(50)    CONSTRAINT motel_city_nn        NOT NULL            ,
        province    VARCHAR2(50)    CONSTRAINT motel_province_nn    NOT NULL            ,
        postalcode  VARCHAR2(50)    CONSTRAINT motel_postalcode_nn  NOT NULL            ,
        country     VARCHAR2(50)    CONSTRAINT motel_country_nn     NOT NULL
);

-------------------------------------------------------------------------------

CREATE TABLE MotelRoom
(
        roomID       NUMBER(7),     CONSTRAINT motelroom_roomid_pk      PRIMARY KEY(roomID)                                                      ,
        motelID      NUMBER(7),     CONSTRAINT motelRoom_motelid_fk     FOREIGN KEY(motelID) REFERENCES Motel(motelID)                            ,
        roomtype     CHAR(3)        CONSTRAINT motelroom_roomtype_nn    NOT NULL                                                                  ,
                                    CONSTRAINT motelroom_roomtype_ck    CHECK(roomtype IN('STD', 'MOD', 'SUP', 'DLX', 'ROH', 'JST', 'STE', 'STU')),
        bedtype      CHAR(1)        CONSTRAINT motelroom_bedtype_nn     NOT NULL                                                                  ,
                                    CONSTRAINT motelroom_bedtype_ck     CHECK(bedtype  IN ('K', 'C', 'Q', 'D', 'T'))                              ,
        price        NUMBER(6,2)    CONSTRAINT motelroom_price_nn       NOT NULL                                                                  ,
        notes        VARCHAR2(500)                                                                                                                ,
        isavailable  CHAR(1)        CONSTRAINT motelroom_isavailable_nn NOT NULL                                                                  ,
                                    CONSTRAINT motelroom_isavailable_ck CHECK(isavailable IN ('Y', 'N'))
);

Select allOpen in new window