cosc2307 assignment 3 Solution

COSC 2307 Assignment 3
Start early—this programming assignment requires a fair amount of work to use ISQLPLUS.
Due: Nov 12th
Part 1: Queries
1. There is a table called USER_TABLES that stores the information about each user’s table. Use the select statement to make sure your tables are all created from assignment 2 (5 marks).
2. 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 (6 marks)?
3. What is the length of values defined for city column. Modify the length of “city” in the table “suppliers”(5 marks).
4. 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.
5. 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: More Queries (using the S-SP-P tables) (each 6 marks)
1. How many different kinds of parts do we have 2. Give the details of suppliers in Paris 3. Give details of shipments where the quantity shipped is less than 300 4. Give details of parts stored in London 5. Give the names and numbers of London based suppliers 6. Give the names of red Parts 7. Give the part number for parts weighting less than 17 8. Give details of parts supplied by S1
9. Give the color of parts supplied by S2 10. Where are the suppliers of P2 located 11. Get supplier numbers for suppliers who supply parts located in either Paris or London
Turn in a copy of all of your SQL commands, along with a script illustrating their execution (by using paste your terminal output and save it in a file or any other way). Your script should be sufficient to convince us that your commands run successfully.
Submission Guideline: 1. Upload your submission on CMS 2. Print out one hard copy and hand it to TA by Nov 12th, 2014
S# SNAME STATUS CITY S1 SMITH 20 LONDON S2 JONES 10 PARIS S3 BLAKE 30 PARIS S4 CLARKE 20 LONDON S5 ADAMS 30 ATHENS Supplier (S)
S# P# Qty S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P2 200 S4 P4 300 S4 P5 400 SP
P# Pname Colour Weight City P1 BOLT blue 10 London P2 NUT black 5 Paris P3 WASHER black 2 London P4 NUT red 6 Athens P5 CAM red 8 Paris P6 NUT blue 5 London Parts (P)
Powered by