Assignment Part #1 pdf PDF

Preview

Summary

Assignment 1 of Introduction to Sql. Enjoy with the answers...

Description

11/28/2021

Assignment Part#1 DAT702 NAA December 3rd 2021 Group 5:Fardeen Islam

#149506198

Mehadi Akram Mahim

#118698190

1|Page

11/28/2021

PART A 2. Show the table structure for the tables in the World Database in MYSQL (desc tablename; for each table. Take a screenshot.

PART B 1. Write a short paragraph describing and explaining the relational links between the tables. Include the following in your explanation: a. Identify all Primary Key/Foreign Key relationships - In City table, ID is Primary Key and CountryCode is Foreign Key. - In Country language table, CountryCode and Language are the Primary Keys. - In Country table, Code is the Primary Key.

2|Page

11/28/2021 b. Explain why the columns are linked - Looking across all tables, in city CountryCode is foreign key and primary key for it is Code in Country table. Also in Countrylanguiage table not only is the CountryCode primary key, the type of char(3) matches. c. Explain how you now that the columns are linked - As checking their data structure out by using the desc command. It seems all three of them are linked due to reasons mentioned in answer b. 2. Explain why it is acceptable for the CountryLanguage table to have two Primary Keys - A table can have a composite primary key. A composite key primary key is a primary key made by two or more columns.

PART C 1. When creating the trigger in Assignment Part 2 you will need to add the old population for both the city and country, and the updated population for the city and country to a new table. Therefore, you must create a new table. Create a new table named: ‘noveau_population’. The table should have the following columns. Take a screenshot of the table structure in MYSQL. (Hint: desc) a. City_ancienne_population b. City_courante_population c. Country_ancienne_population d. Country_courante_population

3|Page

11/28/2021 2. In order to prove that the trigger you create in Assignment Part 2 works you must create a view. Create a View named: Output_Table, that will output the contents of the table you created above. Take a screenshot of the command line you used to successfully create the view.

4|Page...