Constructing MySQL Database with Given Tables

Question

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

Part 2

Question

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)
Submit

 

Summary

This question belongs to Computer Science and Describing MySQL Database Management Systems 

 

 

 

 

Download Full Solution

Comments

  • HWA
    Rasha

    this is a very good website

  • HWA
    maani

    I have 50 questions for the same test your page is showing only 28

  • HWA
    joeanne

    hi can you please help or guide me to answer my assignments. thanks

  • HWA
    joeanne

    hi can anyone help or guide me to my assignments. thanks

  • HWA
    Monik


  • HWA
    Cristina

    This solution is perfect ...thanks

  • HWA
    Janete

    Hello Allison,I love the 2nd image that you did! I also, had never heard of SumoPaint, is something that I will have to exolpre a bit! I understand completely the 52 (or so) youtube videos that you probably watched. Sometimes they have what you want, sometimes they don't! However, it is always satisfying when you are able to produce something that you have taught yourself. Great job!Debra 0 likes

  • HWA
    Sandeep

    Perfect bank of solution. 

  • HWA
    Oxana

    great !

  • HWA
    Paul Brandon-Fritzius

    thanks for the quick response. the solution looks good. :)

  • HWA
    tina Johnson

    thnx for the answer. it was perfect. just the way i wanted it. 

  • HWA
    Giuseppe

    works fine.