Due date: Nov 10th
Part 1 (total 30 marks)
 You are asked to create a new table called “part2” which has the same column definitions as the table “part” you created in assignment2. However, you should not copy the data contained in the course. How do you create a copy of the “part” table without copying its data contents (4 marks)?
 
What is the length of values defined for city column. Modify the length of “city” in the table “suppliers” (4 marks).
 
Are there any constraints defined for the above tables (table suppliers (S), supplier-part (SP), parts? (P) In order to find out, try to search through the USER_CONSTRAINTS table. ( 4 marks)
 
Add the following constraints for the tables (each 3 marks, total 18 marks):
– s# is the primary key for table “S”
– s#, p# is the primary key for the table “SP”
-p# in table “P” should be unique
– the primary key of the “S” table is posted as foreign key in the tables “SP”
– SNAME in table “S” may not be NULL
– the columns “qty” in table “SP”, “weight” in table “P” should be larger than 0.
Part 2: Write appropriate SQL for the following English queries. Use the
http://myweb.brooklyn.liu.edu/gnarra/database/downloads/datasheet.pdfSPJ datasheet for reference (each carries 5 marks, total 70 marks)
Get the names of parts whose weight is between 10 and 15
Which supplier supplied what parts to the ‘Sorter’ job?
Which jobs require red parts?
Get the names of the suppliers who never supplied anything in 1999
Which supplier supplied the most parts ever?
What is the total quantity of parts ordered for the ‘Sorter’ job?
What are the parts that ‘Adam’ ever supplied?
What is the most recently shipped job?
What is the total weight of a ‘Terminal’?
Which supplier has the lowest status?
What is the total weight of all the parts?
Which of the jobs had all the parts from the same city?
Get the names of the parts required to finish the ‘Tape’ job
Which city had the maximum number of parts originating from…