Description

th Assignment 3

1. Create User and Database called lab3 10 Points
(instructions for creating user and database are given at the last section of this page)
2. Create the following tables. The attributes with id mentioned are keys for the respective Tables.:
● part – part-no ( id ), part-name, color, weight 5 Points
● supplier – supplier-no ( id ), sup-name, city, bank 5 Points
(Shipment contains data about parts supplied by various suppliers) 5 Points
3. Add one tuple in each table interactively using the mysql insert statement. 6 Points
4. Create a .sql file to load more data in these tables so that they have at least 3 parts, 2 suppliers, and 4 shipments for each part. (instructions for creating .sql file are given at the last section of this document)
There are two tasks to be done here:
i. Create a .sql file with relevant insert statements,ii. Use the sql file to reflect the changes (i.e., insert query related changes) in the respective tables.
Please submit the .sql file which you have created. 14 Points
5. Write the SQL queries for the following questions. (Please write the same in the report)
a. List suppliers who have supplied red parts. 5 Points
b. Get the total cost of shipments for all suppliers for making payments to them. 10 Points
c. List suppliers who have supplied all parts. 15 Points
6. Demonstration of working commands and queries
NOTE:
2. What to submit?
a. You need to submit two .sql files 5 Points
i. .sql(<roll_number>_1.sql) which contains all the commands/queries related to
Q1, Q2, Q3 and Q5.
ii. .sql<roll_number>_2.sql) for Q4 which contains insert statements.
b. output(screenshot) of all the questions must be put in a document(.pdf).
3. Submit the .pdf and .sql files named with your <roll_number>.pdf and <roll_number>_<1/2>.sql respectively.
4. Mode of submission is moodle.
Instructions
● Creating the user:
mysql> create user ‘UserName’@’localhost’ identified by ‘Password’;
● Grant the privileges to the user
mysql> grant all privileges on db_name.* to ‘UserName’@’localhost’; ● To login with a new user
$ mysql -u UserName -p Enter password:
● Creating the database:
mysql> create database db_name;
● Connecting to database
mysql> use db_name;
● Instructions for creating .sql file
1. Use any text editor and create a file(such as gedit or sublime etc.) 2. Include all the relevant commands and queries in the .sql file as it is.
3. Save the file with extension as .sql
● Running .sql file in mysql terminal
mysql> source <path to file><filename.sql>