The Dew Drop Inn was built during the age of "mom and pop" motels. It started out as four rustic log cabins in the style popular during the sixties. During the ensuing years it was rebuilt, and now offers eight efficiency units with high speed internet, kitchenettes, 140 TV channels and a variety of rooms. Several years ago the owners moved from an entirely paper system to a system using Microsoft Excel. This was sufficient for such a small operation, but now that the Inn has an internet connection, they want to make more use of the data. For this reason you have been retained to create a proper database using Microsoft Access. Continue your project by using the following table structures to build a Microsoft Access database.
ROOMS(RoomNum, RoomSize, RoomBedCnt, RoomRate)
PATRONS(PatronID, FirstName, LastName, PhoneNum, eMail)
REGISTRATIONS(RegID, RegDate,PatronID, AdultCnt, ChildCnt, RoomNum, RegNote)
Write SQL CREATE TABLE statements for each of the table structures listed above. Create these SQL statements in Microsoft Word. Be sure to add CONSTRAINTS for any PRIMARY and FOREIGN KEYS. The first SQL statement has been written for you already.
CREATE TABLE ROOMS(
RoomNumIntNot Null,
RoomSizeChar(6)Not Null,
RoomBedCntIntNot Null,
RoomRateNumeric(18,2) Not Null,
CONSTRAINTROOM_PKPRIMARY KEY(RoomNum)
);
CREATE TABLE PATRONS(
);
CREATE TABLE REGISTRATIONS(
);
Use the SQL CREATE TABLE statements above to create three tables in Microsoft Access. Once the tables are built, go into DATABASE TOOLS|Relationships. Your tables and constraints should be added automatically. Use PrintScreen (Alt-PrtScr) to copy your Relationships to the clip board and paste it to your Microsoft Word document.