Consider a database schema with three relations: - Parts (pid:integer, pname:str

Question

Consider a database schema with three relations:

- Parts (pid:integer, pname:string, year:integer, price:integer)

-Suppliers (sid:integer, sname: string, state:string, zipcode:string)

-Orders (pid:integer, sid:integer, quantity:integer)

The description is as follows: a factory keeps a database with parts that it uses, the suppliers of those
parts, and purchase orders. Each part is uniquely identified by pid. Each part has a string description
pname, year of fabrication and price per unit. Parts are provided by suppliers, and each supplier is
uniquely identified by sid. Each supplier has a name sname, and it operates at the given state and
zipcode. The company has a number of orders for parts, and each order contains the pid of the part
ordered, the sid of the supplier from which the part is ordered, and the quantity ordered. You are
allowed flexibility on the exact attribute types you use for your schema, as long as they reasonably match
the specification above (e.g., in terms of number types, string types). Include the schema definition in
your submission in a file called schema.sql.

Question 2
Write a PL/SQL procedure that receives as arguments pid, sid and quantity of a prospective order.
First, you need to determine if the value (i.e., dollar amount) of that order will be lower or equal than 75%
of the average previous order value for that part. If the answer is yes, go ahead and input the new order
into the database.
Otherwise, compute the price value that would make the prospective order value be exactly at the 75%
limit above, and then insert a NEW part with that price, and the same attributes as the part given in the
pid parameter (except for the pid of course, for which you need to determine a unique value). Then,
input in the database an order with the sid and quantity given, but for the new pid.

Explanation / Answer

Question 1)

Answer)

Creation of tables of Parts, Suppliers, Orders:

File : schema.sql

create table Parts (pid integer primary key, pname varchar, year integer, price integer);

create table Suppliers(sid integer primary key, sname varchar, state varchar, zipcode varchar);

create table Orders(pid integer, sid integer, quantity integer, primary key(pid,sid), foreign key (pid) references Parts(pid), foreign key (sid) references Suppliers(sid));

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.