Project Part 1
1. Download MySQL DBMS (community edition server 5.7+) and install it on your local computer environment. There are several links and resources listed in your syllabus.
2. Construct a database containing the tables given below. When creating tables, your definitions should support key constraints and referential integrity constraints. You should infer primary and foreign keys from the schema.
3. Demonstrate that you have completed the creation of the tables by (i) retrieving each table from the database and printing the stdout if using CLI or screen capture if using the workbench IDE. (ii) show the system catalog that proves your schema has been constructed which should include table names and attribute names and types. Hint: research information_schema
1. For each of the following English descriptions, provide a valid SQL statement and show the evaluated result (command line or screen capture). Views are not allowed and the SQL should be a single statement.
(a) Get project numbers and names for projects supplied by supplier S2.
(b) Get the full details for parts supplied by a supplier in London.
(c) Get all shipments where the quantity is in the range 300 to 750 inclusive.
(d) Get part numbers for parts supplied by a supplier in London to a project in London.
(e) Get all supplier-number/part-number/project number triples such that the indicated supplier, part and project are collocated.
(f) Get the triples in (e) that are not collocated.
(g) For each part being supplied to a project, get the part number, project number and the total quantity.
(h) Get part numbers for those parts supplied to all projects in London.
2. Given the English description: For parts that are supplied to some projects in average quantity of more than 350, show part numbers and part names (popular bulk parts).
(a) Write a valid SQL statement
(b) Define a view that completes this description and show the result: For parts that are supplied to some projects in average quantity of less than 400.
(c) Prove that the view has been created in the system catalog (hint: SQL on information_schema again)
3. Do the following and prove that it was successful:
(a) Create an index with UNIQUE option on JNAME of the PROJECT table.
(b) Create an index with UNIQUE p[tion on PNAME of the PART table.
(c) Insert in the SPJ table this tuple: (S5, P6, J9, NULL)
This question belongs to Computer Science and Describing MySQL Database Management Systems
Download Full Solution
If you are here for the first time, you can request for a discount coupon, which can knock off upto 20% of the quoted price on any service.