CREATE TABLE Hotel ( HotelID INT NOT NULL IDENTITY PRIMARY KEY, HotelName VARCHAR(50) NOT NULL UNIQUE, HotelPhone
Fantastic news! We've Found the answer you've been seeking!
Question:
CREATE TABLE Hotel ( HotelID INT NOT NULL IDENTITY PRIMARY KEY, HotelName VARCHAR(50) NOT NULL UNIQUE, HotelPhone VARCHAR(12) NOT NULL UNIQUE CHECK(HotelPhone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), HotelAddress VARCHAR(50) NOT NULL UNIQUE, HotelCity VARCHAR(30) NOT NULL, HotelState VARCHAR(30) NOT NULL, HotelZip INT NOT NULL CHECK (LEN(HotelZip) = 5) ); CREATE TABLE Room ( RoomID INT NOT NULL IDENTITY PRIMARY KEY, RoomTypeID INT NOT NULL REFERENCES RoomType (RoomTypeID), HotelID INT NOT NULL REFERENCES Hotel (HotelID), RoomNumber INT NOT NULL CHECK(RoomNumber LIKE '[0-9][0-9][0-9]') ); CREATE TABLE RoomType ( RoomTypeID INT NOT NULL IDENTITY PRIMARY KEY, RoomID INT NOT NULL REFERENCES Room (RoomID), TypeName VARCHAR(15) NOT NULL CHECK((TypeName = 'One Bed' OR TypeName = 'Two Beds' OR TypeName = 'Suite')), Handicap BIT NOT NULL -- 0 = No and 1 = yes ); CREATE TABLE Booking ( BookingID INT NOT NULL IDENTITY PRIMARY KEY, RoomID INT NOT NULL REFERENCES Room (RoomID), BookingDate DATE NOT NULL, Rate INT NOT NULL, --price of the room at time of booking StartDate DATE NOT NULL, EndDate DATE NOT NULL, ); CREATE TABLE Customer ( CustomerID INT NOT NULL IDENTITY PRIMARY KEY, BookingID INT NOT NULL REFERENCES Booking (BookingID), FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, CustomerPhone VARCHAR(12) NOT NULL UNIQUE CHECK(CustomerPhone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), CustomerEmail VARCHAR(50) NOT NULL, CustomerAddress VARCHAR(50) NOT NULL );
I am struggling to get my Room and RoomType tables to create, how would I fix this? My other question is would this database design satisfy third normal form? If not what can I do to make it satisfy third normal form?
Related Book For
Introduction To Java Programming And Data Structures Comprehensive Version
ISBN: 9780136520238
12th Edition
Authors: Y. Daniel Liang
Posted Date: