CIS114-S1 Written Assignment The Dew Drop Inn was built during the age of “mom a
ID: 3840119 • Letter: C
Question
CIS114-S1 Written Assignment
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. Begin by designing tables for each of the entities contained in the representative data shown below:
ROOMS
RoomNum
RoomSize
RoomBedCnt
RoomRate
101
Double
1
$44.00
102
Double
2
$49.00
103
Queen
2
$61.00
104
Queen
2
$61.00
105
Queen
2
$61.00
106
King
1
$61.00
107
King
1
$61.00
108
King
1
$61.00
REGISTRATIONS
RegDate
FirstName
LastName
PhoneNum
AdultCnt
ChildCnt
RoomNum
6/1/2015
Matthew
Andrews
555-957-3780
Matthew.Andrews@ourcampus.edu
2
0
104
6/1/2015
Matthew
Andrews
555-957-3780
Matthew.Andrews@ourcampus.edu
0
2
103
6/1/2015
Linda
Becky
555-761-8562
Linda.Becky@mymail.com
2
0
107
6/1/2015
Jason
Tully
555-172-8639
Jason.Tully@mymail.com
2
0
108
6/1/2015
James
Thompson
555-243-8344
James.Thompson@ourcampus.edu
1
0
101
6/1/2015
George
Berkely
555-662-3565
George.Berkely@mymail.com
1
0
106
6/1/2015
Richard
James
555-455-5163
Richard.James@somewhere.com
2
1
105
6/1/2015
Samantha
Jackson
555-632-7417
Samantha.Jackson@mymail.com
2
0
102
(35 points) Complete the following table structures for each of the entities listed above. Primary Keys should be underlined, and Foreign Keys should be in italics. The first table has been built for you already.
ROOMS(RoomNum, RoomSize, RoomBedCount, RoomRate)
PATRONS(
REGISTRATIONS(
(35 points) Add Surrogate IDs to the last two table structures you completed above, and change primary and foreign keys where appropriate.
PATRONS(
REGISTRATIONS(
RoomNum
RoomSize
RoomBedCnt
RoomRate
101
Double
1
$44.00
102
Double
2
$49.00
103
Queen
2
$61.00
104
Queen
2
$61.00
105
Queen
2
$61.00
106
King
1
$61.00
107
King
1
$61.00
108
King
1
$61.00
Explanation / Answer
create table ROOMS(
RoomNum int NOT NULL,
RoomSize int NOT NULL,
RoomBedCnt int,
RoomRate Float,
PRIMARY KEY (RoomNum),
);
create table REGISTRATIONS(
RegDate DATE,
FirstName VARCAR2(255),
LastName VARCAR2(255),
PhoneNum number,
eMail VARCAR2(255),
LastName VARCAR2(255),
AdultCnt int,
ChilsCnt int,
RoomNum int NOT NULL
PRIMARY KEY (FirstName),
FOREIGN KEY (RoomNum) REFERENCES Rooms(RoomNum)
);