Answer the following: Consider a scenario where we are required to update a table that already contains data, e.g., adding a new attribute Is it better to regenerate the table or just update it? Consider the situation when the table has not 20, but 20 million records. What are the pros and cons of updating the table vs. re-creating it from scratch? What could you do if you were not allowed to re-create a table from scratch, i.e., you can only modify it? CREATE TABLE HOTEL ( hotelNo INT NOT NULL, hotelName VARCHAR(50), City VARCHAR(50), primary key (hotelNo) ); CREATE TABLE ROOM ( roomNo INT NOT NULL, H_hotelNo INT NOT NULL, roomType CHAR(10), price decimal(5,2), primary key (roomNo, H_hotelNo), foreign key (H_hotelNo) REFERENCES HOTEL(hotelNo), CHECK (price >= 50.00), CHECK (price <= 500.00), CHECK (roomType = 'Single' OR roomType = 'Double' OR roomType = 'Standard'), CHECK (RoomNo >= 1), CHECK (RoomNo <= 9999) ); CREATE TABLE BOOKING ( H_hotelNo INT NOT NULL, G_guestNo INT NOT NULL, dateFrom DATE NOT NULL, dateTo DATE, R_roomNo INT NOT NULL, primary key (H_hotelNo, G_guestNo, dateFrom, R_roomNo), foreign key (H_hotelNo) REFERENCES HOTEL(hotelNo), foreign key (G_guestNo) REFERENCES GUEST(guestNo), foreign key (R_roomNo) REFERENCES ROOM(roomNo), CHECK (dateFrom < dateTo) ); CREATE TABLE GUEST ( guestNo INT NOT NULL, guestName CHAR(100), guestAddress VARCHAR(100), primary key (guestNo), unique (guestName) );
Answer the following: Consider a scenario where we are required to update a table that already contains data, e.g., adding a new attribute Is it better to regenerate the table or just update it? Consider the situation when the table has not 20, but 20 million records. What are the pros and cons of updating the table vs. re-creating it from scratch? What could you do if you were not allowed to re-create a table from scratch, i.e., you can only modify it?
CREATE TABLE HOTEL
(
hotelNo INT NOT NULL,
hotelName VARCHAR(50),
City VARCHAR(50),
primary key (hotelNo)
);
CREATE TABLE ROOM
(
roomNo INT NOT NULL,
H_hotelNo INT NOT NULL,
roomType CHAR(10),
price decimal(5,2),
primary key (roomNo, H_hotelNo),
foreign key (H_hotelNo) REFERENCES HOTEL(hotelNo),
CHECK (price >= 50.00),
CHECK (price <= 500.00),
CHECK (roomType = 'Single' OR roomType = 'Double' OR roomType = 'Standard'),
CHECK (RoomNo >= 1),
CHECK (RoomNo <= 9999)
);
CREATE TABLE BOOKING
(
H_hotelNo INT NOT NULL,
G_guestNo INT NOT NULL,
dateFrom DATE NOT NULL,
dateTo DATE,
R_roomNo INT NOT NULL,
primary key (H_hotelNo, G_guestNo, dateFrom, R_roomNo),
foreign key (H_hotelNo) REFERENCES HOTEL(hotelNo),
foreign key (G_guestNo) REFERENCES GUEST(guestNo),
foreign key (R_roomNo) REFERENCES ROOM(roomNo),
CHECK (dateFrom < dateTo)
);
CREATE TABLE GUEST
(
guestNo INT NOT NULL,
guestName CHAR(100),
guestAddress VARCHAR(100),
primary key (guestNo),
unique (guestName)
);
Step by step
Solved in 3 steps
