database代写/数据库作业代写: 这是一个基础的oracle数据库代写，后面附了标准答案，可以学习一下
CENTENNIAL COLLEGE
Information and Communication
Engineering Technology (ICET) DEPARTMENT
Assignment 3 & Project
SEMESTER: FALL 2018
SUBJECT NAME: Advanced Database Concepts
SUBJECT CODE: COMP214
INSTRUCTOR NAME: ERSAN CAM
MARKS ALLOTTED: %100 for Assigment3 and %100 for Project
Important information :
Cheating policy : This assignment and project is design you to implement all of your learning from this course and expectation is based on trust and self-future development benefits. So I highly suggest to do by yourself and do not get any help from anybody else… Do not copy paste from somebody else’s code , or from internet or from similar books/ projects.. Come up with your own coding , design please.
If cheating is observed, then student will be marked zero (0)
Due Date is December 8th, you will be presenting your development codes to your instructor in instructors’ computer
ASSIGMENT 3
Marking: (Each question is 20 in Assignment section , Total of 100 Marks for actual PL/SQL coding , design, build and test.
1.
Creating a Procedure to allow employee to change product Desc.
NAME: Sp_Prod_Desc_Change
Use these steps to create a procedure that allows a company employee to make corrections to
a product’s assigned name. Review the BB_PRODUCT table and identify the PRODUCT NAME
and PRIMARY KEY columns. The procedure needs two IN parameters to identify the product
ID and supply the new description. This procedure needs to perform only a DML action, so no
OUT parameters are necessary. (Optional : It is best to list Product code name from Drop down list menu and let user choose one product name but find proper Product Code and pass that code as Product Code)
a. In SQL Developer, create the following procedure to do UPDATE: It will receive Product ID and Product description (New Description) as INPUT (IN) parameters and UPDATE bb_product table’s description column.
P.S : Don’t forget to add COMMIT in your UPDATE, DELETE, INSERT commands whenever possible
create or replace procedure Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2)
as
begin
update bb_product set description = productDesc
where IDPRODUCT=productId;
commit;
end;
/
b. Before testing the procedure, verify the current description value for product ID 1 with
SELECT * FROM bb_product;.
SELECT * FROM bb_product;
c. Call the procedure with parameter values of 1 for the product ID and
‘CapressoBar Model #388’ for new description.
call Sp_Prod_Desc_Change(1, ‘CapressoBar Model #388’);
d. Verify that the update was successful by querying the table with
SELECT * FROM bb_product;.
SELECT * FROM bb_product;
2.
Using a Procedure with IN Parameters
Follow these steps to create a procedure that allows a company employee to add a new
product to the database. This procedure needs only IN parameters.
a. In SQL Developer, create a procedure named PROD_ADD_SP that adds a row for a new
product in the BB_PRODUCT table. Keep in mind that the user provides values for the
product name, description, image filename, price, and active status. Address the input
values or parameters in the same order as in the preceding sentence.
create sequence seq_product start with 11 increment by 1;
create or replace procedure PROD_ADD_SP(productName VARCHAR2,
productDesc VARCHAR2, imageFilename VARCHAR2, price number,
activeStatus number)
as
nextId number;
begin
select seq_product.nextval into nextId from dual;
insert into bb_product(idProduct, ProductName, Description, ProductImage, Price, Active) values
(nextId, productName, productDesc, imageFilename, price, activeStatus);
commit;
end;
/
b. Call the procedure with these parameter values:
(‘Roasted Blend’, ‘Well-balancedmix of roasted beans, a medium body’, ‘roasted.jpg’,9.50,1).
call PROD_ADD_SP(‘Roasted Blend’, ‘Well-balancedmix of roasted beans, a medium body’, ‘roasted.jpg’, 9.50, 1);
c. Check whether the DML Insert was successful by querying the BB_PRODUCT table.
At the database in SQL Developer run this command to show the new product… SELECT * FROM BB_PRODUCT;
Place your screen shot here.
3.
Calculating the Tax on an Order (Function)
Follow these steps to create a Function for calculating the tax on an order. The BB_TAX table
contains states that require submitting taxes for Internet sales. If the state isn’t listed in the
table, no tax should be assessed on the order. The shopper’s state and basket subtotal are the
inputs to the procedure, and the tax amount should be returned.
a. In SQL Developer, create a function named TAX_COST_SP. Remember that the state
and subtotal values are inputs to the Function, which should return the tax amount.
Review the BB_TAX table, which contains the tax rate for each applicable state.
CREATE OR REPLACE function TAX_COST_SP(p_state VARCHAR2, p_subtotal number)
return number
as
rate number;
begin
select sum(taxrate) into rate from BB_TAX where state=p_state;
return rate * p_subtotal;
end;
/
b. Call the Function with the values VA for the state and $100 for the subtotal. Display the
tax amount the function returns. (It should be $4.50.)
select TAX_COST_SP(‘VA’, 100) from dual;
4.
Returning Order Status Information
Create a procedure that returns the most recent order status information for a specified basket.
This procedure should determine the most recent ordering-stage entry in the BB_BASKETSTATUS
table and return the data. Use an IF or CASE clause to return a stage description instead
of an IDSTAGE number, which means little to shoppers.
The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage as follows:
• 1—Submitted and received
• 2—Confirmed, processed, sent to shipping
• 3—Shipped
• 4—Cancelled
• 5—Back-ordered
The procedure should accept a basket ID number and return the most recent status
description and date the status was recorded. If no status is available for the specified basket
ID, return a message stating that no status is available. Name the procedure STATUS_SP.
Test the procedure twice with the basket ID 4 and then 6.
CREATE OR REPLACE
function STATUS_SP(basketId number)
return VARCHAR2
as
stage VARCHAR2(56);
stageDate DATE;
begin
select case idstage
when 1 then ‘Submitted and received’
when 2 then ‘Confirmed, processed, sent to shipping’
when 3 then ‘Shipped’
when 4 then ‘Cancelled’
when 5 then ‘Back-ordered’
else ” end,
dtstage into stage, stageDate
from BB_BASKETSTATUS where idbasket=basketId and rownum=1;
if stage = ” then
return ‘no status is available’;
end if;
return stage || ‘, ‘ || to_char(stageDate, ‘yyyy-MM-dd’);
end;
/
select STATUS_SP(4) from dual;
select STATUS_SP(6) from dual;
- CREATE an Oracle database application PL/SQL Package named BB_ORDER_SYSTEM and in this package include all above codes from step 1 to 4…
NAME: Prod_Desc_Change_SP (PROCEDURE)
NAME: PROD_ADD_SP (PROCEDURE)
NAME : TAX_COST_SP (FUNCTION)
NAME : STATUS_SP. (PROCEDURE)
CREATE OR REPLACE PACKAGE BB_ORDER_SYSTEM IS
PROCEDURE Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2);
PROCEDURE PROD_ADD_SP(productName VARCHAR2, productDesc VARCHAR2,
imageFilename VARCHAR2, price number, activeStatus number);
FUNCTION TAX_COST_SP(p_state VARCHAR2, p_subtotal number) return number;
FUNCTION STATUS_SP(basketId number) return VARCHAR2;
END BB_ORDER_SYSTEM;
/
CREATE OR REPLACE PACKAGE BODY BB_ORDER_SYSTEM IS
procedure Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2)
as
begin
update bb_product set description = productDesc
where IDPRODUCT=productId;
commit;
end Sp_Prod_Desc_Change;
procedure PROD_ADD_SP(productName VARCHAR2,
productDesc VARCHAR2, imageFilename VARCHAR2, price number,
activeStatus number)
as
nextId number;
begin
select seq_product.nextval into nextId from dual;
insert into bb_product(idProduct, ProductName, Description, ProductImage, Price, Active) values
(nextId, productName, productDesc, imageFilename, price, activeStatus);
commit;
end PROD_ADD_SP;
function TAX_COST_SP(p_state VARCHAR2, p_subtotal number)
return number
as
rate number;
begin
select sum(taxrate) into rate from BB_TAX where state=p_state;
return rate * p_subtotal;
end TAX_COST_SP;
function STATUS_SP(basketId number)
return VARCHAR2
as
stage VARCHAR2(56);
stageDate DATE;
begin
select case idstage
when 1 then 'Submitted and received'
when 2 then 'Confirmed, processed, sent to shipping'
when 3 then 'Shipped'
when 4 then 'Cancelled'
when 5 then 'Back-ordered'
else '' end,
dtstage into stage, stageDate
from BB_BASKETSTATUS where idbasket=basketId and rownum=1;
if stage = '' then
return 'no status is available';
end if;
return stage || ', ' || to_char(stageDate, 'yyyy-MM-dd');
end STATUS_SP;
END BB_ORDER_SYSTEM;
/
PROJECT
Marking: (There are 7 tables to be created… To receive %100 full mark, you have to write all tables’ CREATE and /or ALTER Commands (DDL) as clean ,with no syntax error …And you also need to present /run -execute your commands in the class from teacher’s machine.
This project is to build Physical Database tables and their relationships based on Logical Database design and ER model along with Fine-tuned Normalized version below.
Write CREATE TABLE or ALTER TABLE (when necessary) DDL Commands without any syntax error for each table with Proper Primary, Foreign Key, NOT NULL, CHECK constraints.
Table Name: PBLISH_Customers
Check Constraint:
CHECK (region IN (‘N’, ‘NW’, ‘NE’, ‘S’, ‘SE’, ‘SW’, ‘W’, ‘E’))
create table PBLISH_Customers (
Customer# number(4) primary key,
LastName varchar2(10) not null,
FirstName varchar2(10),
Address varchar2(20),
City varchar2(12),
State varchar2(2),
Zip varchar2(5),
Referred number(2),
Region char(2) check (Region IN (‘N’, ‘NW’, ‘NE’, ‘S’, ‘SE’, ‘SW’, ‘W’, ‘E’)),
Email varchar2(30)
);
================================
TABLE NAME : PBLISH_Publisher
create table PBLISH_Publisher (
PubID number(2) primary key,
Name varchar2(25),
Contact varchar2(15) not null,
Phone varchar2(12)
);
===============
TABLE NAME: PBLISH_Books
Hint:
FOREIGN KEY (pubid) REFERENCES PBLISH_publisher (pubid)
create table PBLISH_Books (
ISBN varchar2(10) primary key,
Title varchar2(30),
PubDate date,
PubID number(2) REFERENCES PBLISH_publisher (pubid),
Cost number(5, 2),
ActualPrice number(5, 2),
Discount number(4, 2),
Category varchar2(12)
);
================================
TABLE Name: PBLISH_Orders
FOREIGN KEY (customer#) REFERENCES PBLISH_customers(customer#)
create table PBLISH_Orders (
Order# number(4) primary key,
Customer# number(4) REFERENCES PBLISH_customers(customer#),
OrderDate date not null,
ShipDate date,
ShipStreet varchar2(18),
ShipState varchar2(4),
ShipZip varchar2(5),
ShipCost number(4, 2)
);
Additional Task:
After you create table, business rule has changed and asked to add this column
Also change one existing column structure .. Size Drop from 4 digit to 2 digit
alter table PBLISH_Orders add ShipCity VARCHAR2(15);
alter table PBLISH_Orders modify ShipState VARCHAR2(2);
=====================
TABLE NAME: PBLISH_ORDERITEMS
Hints :
a)Primary key is composite key (order#, item#) – Add this Primary key at the bottom of CREATE TABLE or add primary key after table creation with ALTER TABLE ADD CONSTRAINT command.
b)FOREIGN KEY (order#) REFERENCES PBLISH_orders (order#)
c)FOREIGN KEY (isbn) REFERENCES PBLISH_books (isbn)
d)CHECK (quantity > 0))
create table PBLISH_ORDERITEMS (
Order# number(4) REFERENCES PBLISH_orders (order#),
Item# number(2),
ISBN varchar2(10) REFERENCES PBLISH_books (isbn),
Quantity number(3) CHECK (Quantity > 0) not null,
AmountPayEach number(5, 2) not null,
ShipState varchar2(4),
ShipZip varchar2(5),
ShipCost number(4, 2),
constraint PK_POI primary key(Order#, Item#)
);
==========================
TABLE NAME : PBLISH_Author
create table PBLISH_Author (
AuthID VARCHAR2(4) primary key,
Lname VARCHAR2(10),
Fname VARCHAR2(10)
);
====================
TABLE NAME : PBLISH_BOOKAUTHOR
Hints:
PRIMARY KEY is composite key: (isbn, authid)
FOREIGN KEY (isbn) REFERENCES PBLISH_books (isbn)
FOREIGN KEY (authrid) REFERENCES PBLISH_Author (authid))
create table PBLISH_BOOKAUTHOR (
ISBN VARCHAR2(10) REFERENCES PBLISH_books (isbn),
AuthID VARCHAR2(4) REFERENCES PBLISH_Author (authid),
primary key(isbn, authid)
);
标准答案：
create table PBLISH_Customers (
Customer# number(4) primary key,
LastName varchar2(10) not null,
FirstName varchar2(10),
Address varchar2(20),
City varchar2(12),
State varchar2(2),
Zip varchar2(5),
Referred number(2),
Region char(2) check (Region IN (‘N’, ‘NW’, ‘NE’, ‘S’, ‘SE’, ‘SW’, ‘W’, ‘E’)),
Email varchar2(30)
);
create table PBLISH_Publisher (
PubID number(2) primary key,
Name varchar2(25),
Contact varchar2(15) not null,
Phone varchar2(12)
);
create table PBLISH_Books (
ISBN varchar2(10) primary key,
Title varchar2(30),
PubDate date,
PubID number(2) REFERENCES PBLISH_publisher (pubid),
Cost number(5, 2),
ActualPrice number(5, 2),
Discount number(4, 2),
Category varchar2(12)
);
create table PBLISH_Orders (
Order# number(4) primary key,
Customer# number(4) REFERENCES PBLISH_customers(customer#),
OrderDate date not null,
ShipDate date,
ShipStreet varchar2(18),
ShipState varchar2(4),
ShipZip varchar2(5),
ShipCost number(4, 2)
);
alter table PBLISH_Orders add ShipCity VARCHAR2(15);
alter table PBLISH_Orders modify ShipState VARCHAR2(2);
create table PBLISH_ORDERITEMS (
Order# number(4) REFERENCES PBLISH_orders (order#),
Item# number(2),
ISBN varchar2(10) REFERENCES PBLISH_books (isbn),
Quantity number(3) CHECK (Quantity > 0) not null,
AmountPayEach number(5, 2) not null,
ShipState varchar2(4),
ShipZip varchar2(5),
ShipCost number(4, 2),
constraint PK_POI primary key(Order#, Item#)
);
create table PBLISH_Author (
AuthID VARCHAR2(4) primary key,
Lname VARCHAR2(10),
Fname VARCHAR2(10)
);
create table PBLISH_BOOKAUTHOR (
ISBN VARCHAR2(10) REFERENCES PBLISH_books (isbn),
AuthID VARCHAR2(4) REFERENCES PBLISH_Author (authid),
primary key(isbn, authid)
);
— 1. Creating a Procedure to allow employee to change product Desc.
— a
create or replace procedure Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2)
as
begin
update bb_product set description = productDesc
where IDPRODUCT=productId;
commit;
end;
/
— b
SELECT * FROM bb_product;
— c
call Sp_Prod_Desc_Change(1, ‘CapressoBar Model #388’);
— d
SELECT * FROM bb_product;
— 2. Using a Procedure with IN Parameters
— a
create sequence seq_product start with 11 increment by 1;
create or replace procedure PROD_ADD_SP(productName VARCHAR2,
productDesc VARCHAR2, imageFilename VARCHAR2, price number,
activeStatus number)
as
nextId number;
begin
select seq_product.nextval into nextId from dual;
insert into bb_product(idProduct, ProductName, Description, ProductImage, Price, Active) values
(nextId, productName, productDesc, imageFilename, price, activeStatus);
commit;
end;
/
— b
call PROD_ADD_SP(‘Roasted Blend’, ‘Well-balancedmix of roasted beans, a medium body’, ‘roasted.jpg’, 9.50, 1);
— c
SELECT * FROM BB_PRODUCT;
— 3. Calculating the Tax on an Order (Function)
— a
CREATE OR REPLACE function TAX_COST_SP(p_state VARCHAR2, p_subtotal number)
return number
as
rate number;
begin
select sum(taxrate) into rate from BB_TAX where state=p_state;
return rate * p_subtotal;
end;
/
–b
select TAX_COST_SP(‘VA’, 100) from dual;
— 4.Returning Order Status Information
— 1
CREATE OR REPLACE
function STATUS_SP(basketId number)
return VARCHAR2
as
stage VARCHAR2(56);
stageDate DATE;
begin
select case idstage
when 1 then ‘Submitted and received’
when 2 then ‘Confirmed, processed, sent to shipping’
when 3 then ‘Shipped’
when 4 then ‘Cancelled’
when 5 then ‘Back-ordered’
else ” end,
dtstage into stage, stageDate
from BB_BASKETSTATUS where idbasket=basketId and rownum=1;
if stage = ” then
return ‘no status is available’;
end if;
return stage || ‘, ‘ || to_char(stageDate, ‘yyyy-MM-dd’);
end;
/
select STATUS_SP(4) from dual;
select STATUS_SP(6) from dual;
— 5.
CREATE OR REPLACE PACKAGE BB_ORDER_SYSTEM IS
PROCEDURE Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2);
PROCEDURE PROD_ADD_SP(productName VARCHAR2, productDesc VARCHAR2,
imageFilename VARCHAR2, price number, activeStatus number);
FUNCTION TAX_COST_SP(p_state VARCHAR2, p_subtotal number) return number;
FUNCTION STATUS_SP(basketId number) return VARCHAR2;
END BB_ORDER_SYSTEM;
/
CREATE OR REPLACE PACKAGE BODY BB_ORDER_SYSTEM IS
procedure Sp_Prod_Desc_Change(productId int, productDesc VARCHAR2)
as
begin
update bb_product set description = productDesc
where IDPRODUCT=productId;
commit;
end Sp_Prod_Desc_Change;
procedure PROD_ADD_SP(productName VARCHAR2,
productDesc VARCHAR2, imageFilename VARCHAR2, price number,
activeStatus number)
as
nextId number;
begin
select seq_product.nextval into nextId from dual;
insert into bb_product(idProduct, ProductName, Description, ProductImage, Price, Active) values
(nextId, productName, productDesc, imageFilename, price, activeStatus);
commit;
end PROD_ADD_SP;
function TAX_COST_SP(p_state VARCHAR2, p_subtotal number)
return number
as
rate number;
begin
select sum(taxrate) into rate from BB_TAX where state=p_state;
return rate * p_subtotal;
end TAX_COST_SP;
function STATUS_SP(basketId number)
return VARCHAR2
as
stage VARCHAR2(56);
stageDate DATE;
begin
select case idstage
when 1 then 'Submitted and received'
when 2 then 'Confirmed, processed, sent to shipping'
when 3 then 'Shipped'
when 4 then 'Cancelled'
when 5 then 'Back-ordered'
else '' end,
dtstage into stage, stageDate
from BB_BASKETSTATUS where idbasket=basketId and rownum=1;
if stage = '' then
return 'no status is available';
end if;
return stage || ', ' || to_char(stageDate, 'yyyy-MM-dd');
end STATUS_SP;
END BB_ORDER_SYSTEM;
/