Consider the follawing tobfas for questions 13 and 14. Customers customerID C001
ID: 3729624 • Letter: C
Question
Consider the follawing tobfas for questions 13 and 14. Customers customerID C001 C002 C003 C004 firstName Tony Daniel lastName email tony@gmail. com dan@yahoo.com phoneNumber 0432328888 0412348711 0725637822 0488226739 James Jimmy James jimmy@gmail.com hostID H001 H002 H003 H004 H005 Joe Hockey Malcom Turnbull Richy Rich Empire Builder Richy Rich address 101 Blue St 22 Riverview Rd 99 Penthouse Dr 01 Mountain Rd 22 Kiribilli St Bookings bookinglD B001 B002 B003 B004 B005 hostID H002 H004 H001 H004 H002 customerlD C001 C002 C001 C004 C001 dateFrom 20/08/2018 25/08/2018 2408/2018 25/08/2018 30/08/2018 numberOfNights Consider the tables above that show the relations and the existing tuples within the Hire a Holiday Home relational schema. Currently the database only has a few tuples in each relation. However, it will store significantly more information in the future. Note: there can only be one host at a given address, a customer can only book with one host for any given night (they cannot book for someone else) Choose the correct answer below for the following: How many superkeys are there in the Hosts reliv many candidate keys are there in the Bookings relation?rExplanation / Answer
1.
A superkey is a combination of one or more columns that uniquely identifies the rows in a given table.
For host table, possible combination of superkeys are:
1. HostId: cannot be a superkey as it will not check condition whether there is only one host at a given address
Example:
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
105 Blue St
2. Name: cannot be a superkey as 2 host can be there for same address which will not fulfill the condition for one host at a given address.
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
105 Blue St
3. Address: Possible as there will be one host for a particular address
HostId
Name
Address
H008
Joe
105 Blue St
H009
Joe
106 Blue St
4. HostId, Name: Not possible as 2 host can be there for same address.
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
105 Blue St
5. Name, Address: Not possible as 2 host can be there for same address
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
105 Blue St
6. HostId, Address: Possible as there will be one host for a particular address
HostId
Name
Address
H008
Joe
105 Blue St
H009
Joe
106 Blue St
7. HostId, Name, Address: Possible as it will uniquely identifies the rows and also fulfill the condition of one host at a given address.
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
106 Blue St
So, 3 superkeys are possible in Hosts relation.
2.
Candidate Key: A candidate key is a super key after removing redundancy from superkey
In our example, possible candidate keys from booking relation are:
bookingID
hostID, customerID, dateFrom
So, 2 candidatekeys are there in booking relation.
3.
Primary key is used to uniquely identifies each record in a table. It is from one of the candidate keys. In the above point(2), it is explained that two candidate keys are possible for booking relation.
Most appropriate primary key of booking relation can be: hostID, customerID, dateFrom
as with this it can be identified that a customer can only book with one host for any given night.
dateFrom is needed as a customer can only book with one host for a particular night. In the table given in question, if you look for booking Id B001 and B005, then both host and customer are same but they can book for different nights. This can be identified using dateFrom column only.
4.
Foreign key is a column in one table that refers to primary key in another table.
In booking relation, foreign keys are hostID and customerID.
hostID refers to hostID of Hosts table.
customerID refers to customerID of Customers table.
HostId
Name
Address
H008
Joe
105 Blue St
H009
Tom
105 Blue St
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.