Consider the following set of tables describing penguins and regions of Antartic
ID: 3803934 • Letter: C
Question
Consider the following set of tables describing penguins and regions of Antartica: a. Assuming that PID and RNum are unique identifiers, what are likely primary keys for the Penguins and Regions tables?
a. Assuming that PID and RNum are unique identifiers, what are likely primary keys for the Penguins and Regions tables?
c. What are foreign keys necessary for this set of tables in order to ensure that only registered penguins can be in regions of the Antartica and penguins can only reside in registered regions of the Antartic?
d. Can you add a tuple (105, M, Gentoo, 5.7, 69) to the Penguin table? Why or why not?
Example solution:
Please do it like the example solution.Thanks
3. Consider the following set of tables describing penguins and regio Penguins PID Gender Breed Weight Height Regions kg cm. RNum Area Av Temp Emperor 35.6 118 100 M Sq Km C 1 Gentoo 5.1 67 101 M 200000-5 102 Gentoo 8.4 84 2 150000 -1 Emperor 41.9 127 103 F Emperor 29.4 111 105 F a. Assuming that PID and RNum are unique identifiers, what areExplanation / Answer
Primary key of in a relationl database is a key that can uniquely identify the record or a data entry in a database. Hence all the values which are unique and not null can be primary key. In our case Penguin PID is unique, same goes for Rnum in Regions.hence both are primary key
a) PK for Penguins(PID) , PK for Regions(RNum)
Here, There are two tables whicha re completely different and hence there has to be a table that can relate two tables in order to have a relation between penguin and a database. Assuming the table in the simplest form, we can say the table need to have the structure as
<PenguinID, RegionNumber> with name say PenguinArea
In such a case, the two tables need to have two foreign keys referencing Region IDs and Penguin IDs. from the tables Regions and Penguins respectively.
c) If the new table is as assumed above, then the foreign key should have the values as follows:
FK PenguinArea(PenguinID) referencing Penguins(PID)
FK PenguinArea(RegionNumber) refereancing Regions(Rnum)
The above two lines simply means that in the new table, PenguinID will the Foreign Key referencing PID attribute from Penguin Table
Similarly RegionNumber will be the ForeignKey referencing Rnum attribute from the Regions table
Ans d) As mentioned above, primary is the key that uniquely identify a dataentry in a table. Hence No column with primary key can have two entries with the same values. In the case given above, the PID field has 105 value. Since 105 entry is already present in the table under PID column, we can't enter the data entry with 105 as PID.
/*In the second part, the structure of second table is assumed to have PID and Region numbers only. Since there are only two tables apart from that and both table have only one primary keys. Had it been a case where more than 2 unique keys are there in the Penguin table, Then structre of assumed table is not necessary to be the one mentioned in answer two*/