Embedded Files
What is the primary key in the Game table, and why?
NAME. 1)It is unique 2)this is a Game table, so it makes sense that the name of the game would be unique, and serve well as a primary key.
The Complexity field is also unique, but why would you not use it as a primary key in Game?
Although this field is unique, it may well be populated with a duplicate value in the future. Populating the Name field, on the other hand, would be controlled by the Games Cafe.
What is the primary key in the Booking table, and why?
There is no single field in Booking that is unique. As far as the GCSE is concerned, that is it. However, a composite (multi-field) Primary Key could be built between Customer, Name and Date. This is advanced and you should not need to know this for GCSE.
How would you join the two tables, and why?
The primary key from the Game table has been copied over to the Booking table as a foreign key. I can see this because the data within the field on both tables, is similar, so they are obviously related, and also because this field in Game is unique (PK), and not unique in Booking (FK).
What would be the most appropriate datatype for the GameTableID field?
INT - as it is always a whole number
What would be the most appropriate datatype for the Customer field?
VARCHAR - as it is a character set of varying lengths
Why would DECIMAL not be the right datatype for the LengthOfGame field?
This field holds whole numbers, not decimals. Hence DECIMAL would not be the correct datatype, but INT would.
The manager has decided to close the cafe on 29th May 2019. He needs to inform the customers that had booked in advance.
Write a query to show the manager the name of the game, the name of the customer and complexity of the game that has been booked.
select booking.name, booking.customer, game.complexity
from game, booking
where game.name = booking.name
and booking.date = 20190519
The manager is removing Pandemic as it is broken. He has asked you to write the code to delete the game and any bookings for that game from the system.
delete booking
where name = 'pandemic'
delete game
where name = 'pandemic'
The manager is adding a game called Warrior. He has asked you to write the code to add the new game to the system. The details are: 2 to 7 players; 75 minutes long; a complexity of 4.55; duration of 75. There are no bookings as yet of this new game.
insert into game (name,minplayers,maxplayers,lengthofgame,complexity)
values('Warrior',2,7,75,4.55)
A customer would like to make a new booking for Johnson to play Terra Mystica on 28th May 2019 on table 3 at 11am. They would like to hold the table for 2 hours.
Is this possible?
If so, write the code to make it happen.
Yes, there is availability because table 3 is free at 11am on 28th May & Terra Mystica is not being played on any other table at the same time.
Here is the code to insert a new record into the Booking table:
insert into Booking (GameTableID, Name, Date, StartTime, Customer, Hours)
values (3, 'Terra Mystica', 20190528, 11, 'Pattni', 2)
Page updated
Google Sites
Report abuse