The Script

    1. (1) First, the script modifies the structures of a sample database such it is possible to store information about the manufacturers of the products listed in the database. A manufacturer is described by a unique name, country, and city where it is located. Assume, that a manufacturer can manufacture many products and a product can be manufacturer by many manufacturers.
    2. (2) Next, the script inserts into a database information about 3 manufacturers and make the insertions permanent.
    3. (3) Next, the script stores in a data dictionary PL/SQL procedure that links the manufacturers with the products. The procedure must enforce the following consistency constraint. No more than two manufacturers are allowed to manufacture the same product. The procedure must display an error when the consistency constraint is violated and it must not insert a link between a manufacturer and a product. Otherwise, the procedure must insert a link into a database and make the link permanent. Remember to put / in the next line after CREATE OR REPLACE PROCEDURE statement and a line show errors in the next line.
    4. (4) Next, the script performs the comprehensive testing of the stored procedure implemented in the previous step. The testing must refuse at least one time to link a manufacturer with a product due to the violated consistency constraint.
    5. (5) Finally, the script lists all information about the manufacturers and the names of product manufactured by each manufacturer.
  1. (1) First, store in a data dictionary PL/SQL function, that returns a string that consists of the names of products purchased by a customer with a given customer code. Make a customer code a formal parameter of the function. A name of a function is up to you. A string returned by the function must consists of the names of products separated with a comma (,). If a customer purchased the same product more than one time a name of a product must be listed only one time. If a customer purchased no products then the function must return an empty string.
  2. (2) To test your function implement SELECT statement that lists codes of the customers together with the names of product purchased by the customers. List the customers who purchased only 3 products. A sample output may look like below.


and taste our undisputed quality.