Pinnacle Advertising Services is the largest broker of newspaper advertising space in the New Delhi. PAS has been in existence for some time and has traditionally recorded the above information on a spreadsheet. New senior management wishes to reorganize the company.
PAS currently use the MS small business server exchange which would make Oracle the logical choice. However, they are planning to upgrade to Windows Server edition and are prepared to use Oracle10g, depending on your recommendation. You are not required to cost the system but your justification should include a brief discussion of the architecture of the proposed DBMS.
PAS receives requests from individual companies and advertising agencies and places advertisements with independent newspapers and newspaper groups. PAS deals with national, regional and local papers. Your system must be able to record which group a newspaper belongs to or whether the newspaper is independent.
Newspaper advertising is currently sold in 2 ways but this may change over time. Different newspapers charge different rates. Rates changes over time. The rate is set by the individual newspaper. Advertisements are sold as follows:
By lineage (cost per line). There is usually a minimum charge.
By 5 cm column block. An advertisement is specified as e.g. 2 columns, 10 cm which is 4 column blocks.
The system must be able to record all the details of an advertising transaction including the newspaper, date on which the advertisement should appear the lineage or number of blocks. You do not need to specify details such as the page on which the advertisement should appear. The system must record whether payment has been received from the client and whether payment has been made to the newspaper/newspaper group. You should assume that payments are never made to the newspaper group until 48 hours after the date on which the advertisement should have appeared. You may choose to implement this business rule but are not forced to do so.
Your system must be able to record which customer & which type of customer placed an advertisement, what type of advertisement was placed & to calculate the amount payable to the newspaper group. PAS makes its profit by charging individual customers a fee which is 10% of the amount paid to the newspaper, and by charging advertising agencies an 8% fee. To simplify things, assume that VAT is included in all charges.
PAS has been experiencing a high volume of complaints about advertisements which have not appeared on the correct date. A member of staff is employed to check that adverts have appeared as & when requested but the recording system is not effective. Your database should include provision to record when an advertisement has not appeared on the correct date, any other details about the complaint and what action was taken about the complaint. Every call about a complaint must be logged. Sometimes it happen that more than one member of staff deals with a complaint and your system should be able to handle this.
Part 2
PAS wishes to measure staff productivity and wants to record which member of staff dealt with the placing of which advertisement, how long it took and how much revenue was generated. Management is vague as to what 'how long it took' means. You should develop your own criteria and recording system for this and provide a brief explanation. You are required to design this element but will not be required to implement it.
QUESTION 1 (A) Enhanced Entity Relationship Diagram
Data Modeling Profile:
Data Modeling Profile provides an easy to use and understand adoptiozn of UML for the need of database modeling, and database design. These are the part of the data modeling profiles which are shown below.
Node
Schemas
Table
View
Column
Key
Constraint
Index
Relationship
Here we are choosing one of the data Modeling Profile (View) and give the brief description of that profile.
VIEW
A View is a virtual Table. It represents a set of records of the same structure, exactly like a table, only with the difference that the physical source of the data is in other tables. A View is a stereotyped class and part of the Data Modeling Profile. Views are represented in the data model diagram which is described as below.
SOURCE CODE
create or replace procedure complaint_status (comp_id1 in varchar2)
IS
stat_cmp varchar2(5);
BEGIN
select comp_status into stat_cmp from complaint where comp_id=comp_id1;
if (stat_cmp='open')
dbms_output.put_line('Complaint status is ' || stat_cmp || '. Complaint will be solved soon...');
select * from complaint_handled where
else
if(stat_cmp='close')
dbms_output.put_line('Complaint status is ' || stat_cmp);
select
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid Complain ID');
select complaint_ID.nextval into comp_id from dual;
insert into complaint values(
'comp'||comp_id,
sysdate,
detail,
'open',
cust_id1,
adv_id);
/* displaying */
dbms_output.put_line('Complaint registered, Ref. ID: comp'||comp_id );
END;
/
DESCRIPTION
This view is showing the complaint status wheatear the complain has been resolved or not. It shows the limited data to the user.
QUESTION 1 (B) - Mapping between the logical and physical designs and a justification of any design optimization techniques such as de-normalization.
MAPPING BET. LOGICAL AND PHYSICAL DESIGN
Customer
Cust_id
Cust_name
Cust_type
Cust_address
Adv_request
Adv_id
Posted_date
Publish_date
Cust_id
Np_id
Content
Expectg_date
Cust_paid
Adv_way
Rate
Cust_amt.
Complaint
cmp_id
cmp_date
cmp_det.
Cmp_stat
Cust_id
Adv_id
Payment
Pay_id
Adv_id
Cust_id
Rate
Staff_id
Cust_amt.
News_paid
Newspaper
Np_id
Np_name
Localize
Rate_line
Rate_block
Staff
Staff_id
Staff_name
QUERY OPTIMIZATION
Query optimization is of great importance for the performance of a relational database, especially for the execution of complex SQL statements. Oracle's query optimizer technology is unmatched in the breadth of its functionality. [1]
Query optimization is of great importance for the performance of a relational database, especially for the execution of complex SQL statements. A query optimizer determines the best strategy for performing each query. The query optimizer chooses, for example, whether or not to use indexes for a given query, and which join techniques to use when joining multiple tables. These decisions have a tremendous effect on SQL performance, and query optimization is a key technology for every application, from operational systems to data warehouse and analysis systems to content-management systems. The query optimizer is entirely transparent to the application and the end-user. Because applications may generate very complex SQL, query optimizers must be extremely sophisticated and robust to ensure good performance. For example, query optimizers transform SQL statements, so that these complex statements can be transformed into equivalent, but better performing, SQL statements. [2]
DESCRIPTION AND JUSTIFICATION OF CHOOSEN OPTIMIZATION STRATEGY
Following strategies are followed for query optimization
INDEXES
DESCRIPTION
By using an index searching of any particular record would be easy for the user. To make the data retrieval process faster, indexes are used.
JUSTIFICATION
CUSTIDs or ADVERTISEMENTIDs of requested rows are found out by using indexes and after that those rows are retrieved from the table. The values fetched using indexes create an extra overhead of additional I/O step in processing the query but it's usually much faster to retrieve data using an index. Rather than retrieving all the data from the table and then imposing the selection condition, it is a good practice to use the index values to select the desired rows.
GLOBAL OPTIMIZATION TECHNIQUE
DESCRIPTION
Splitting the large table into smaller is another way for query optimization. This is the global optimization technique generally followed by the database designer.
JUSTIFICATION
The use of idea of global optimization technique is to prevent the optimizer from spending more time on larger table for optimizing the query because for large tables, the query optimizer considers more indexes. Optimizer should actually spend more time to execute the query. Optimizer takes little time to optimize the query for smaller tables rather than optimizing the query.
HISTOGRAMS
DESCRIPTION
Oracle used cost based optimizer and histograms can help in cardinality. Histogram is basically seen when the column referenced within a SQL query WHERE clause has a non-uniform distribution of values, making a full-table scan faster than index access (Consulting, 2003). It provides improved selectively estimates to the optimizer.
JUSTIFICATION
Histogram affect performance by making cost based optimization execution plan faster. Some designed table columns of beacon database are referenced in one or more queries. Therefore developer decided to use histogram to make execution plan faster.
DENORMALIZING THIRD NORMAL FORM (3NF) TO 2NF
3NF:
Customer (cust_id, cust_name, cust_type, cust_address)
Complaint (comp_id, comp_date, comp_detail, comp_status, cust_id, adv_id)
Denormalized 2NF:
Customer (cust_id, cust_name, cust_type, cust_address)
Complaint (comp_id, comp_date, comp_detail, comp_status, cust_id, cust_name, cust_type, cust_address, adv_id)
Note that the cust_id in the complaint table is a foreign key linking the complaint and customer tables. Denormalizing the table structures by duplicating the cust_name, cust_type, cust_address in the Complaint table results in a solution that is 2NF because the non-key cust_name field is determined by the non-key cust_name field. As before, a comparison of the 3NF solution and the denormalized 2NF solution reveals that the cust_name could easily be recorded to the denormalized complaint table at the time that the complaint takes place.
3NF:
Newspaper (Np_Id, Np_Name, Category,Localize, Rate_Lineage, Rate_Block)
Adv_request (adv_id, posted_date, publicshing_date, cust_id, np_id, content, expected_date, customer_paid, adv_way, rate, cust_amount)
Denormalized 2NF:
Newspaper (Np_Id, Np_Name, Category,Localize, Rate_Lineage, Rate_Block)
Adv_request (adv_id, posted_date, publicshing_date, cust_id, np_id, np_name, content, expected_date, customer_paid, adv_way, rate, cust_amount)
Here also the np_id in the adv_request table is a foreign key linking the adv_request and newspaper tables. Denormalizing the table structures by duplicating the np_name in the adv_request table results in a solution that is 2NF because the non-key np_name field is determined by the non-key np_name field. As before, a comparison of the 3NF solution and the denormalized 2NF solution reveals that the np_name could easily be recorded to the denormalized adv_request table at the time that the request for the advertisement takes place.
QUESTION 2 (A):- Document, demonstrate & justify the way in which you have implemented your physical design.
Tables
Table No
1
Table Name
Adv_request
Description
To store the details of the customer.
Attribute
Data Type
Size
Description
Adv_id
Varchar2
10
To store the advertisement id.
Posted_date
Timestamp
6
To store the posted date.
Publishing_date
date
-
To store the publishing date.
Cust_id
Varchar2
10
To store the customer id.
Np_id
Varchar2
10
To store the newspaper id.
content
Varchar2
500
To store the content.
Expected_date
Timestamp
6
To store the Expected date.
Customer_paid
Varchar2
5
To store the customer paid.
Adv_way
Varchar2
10
To store the advertisement way.
Rate
Number
5,2
To store the rate.
Customer_amount
Number
10,2
To store the customer amount.
Table No
2
Table Name
Complaint
Description
To store the details of the user's complaint.
Attribute
Data Type
Size
Description
Comp_id
Varchar2
10
To store the complain id.
Comp_date
Timestamp
6
To store the complain date.
Comp_detail
Varchar2
100
To store the complain detail.
Comp_status
Varchar2
5
To store the complain status.
Cust_id
Varchar2
10
To store the customer id.
Adv_id
Varchar2
10
To store the advertisement id.
Table No
3
Table Name
Customer
Description
To store the details of the customer.
Attribute
Data Type
Size
Description
Cust_id
Varchar2
10
To store the customer id.
Cust_name
Varchar2
30
To store the customer name.
Cust_type
Varchar2
10
To store the customer type.
Cust_address
contacts
2,5
To store the customer address.
Table No
4
Table Name
Payment
Description
To store the Payment details.
Attribute
Data Type
Size
Description
Pay_id
Varchar2
10
To store the payment id.
Adv_id
Varchar2
30
To store the advertisement id.
Cust_id
Varchar2
10
To store the customer id.
Rate
decimal
5,2
To store the rate.
Staff_id
Varchar2
10
To store the staff id.
Customer_amount
decimal
6,2
To store the customer amount.
Newspaper_paid
Varchar2
3
To store the newspaper_paid.
Table No
5
Table Name
Newspaper
Description
To store the newspaper details.
Attribute
Data Type
Size
Description
np_id
Varchar2
10
To store the newspaper id.
Np_name
Varchar2
30
To store the newspaper name.
Category
Varchar2
10
To store the category.
Localize
Varchar2
10
To store the localize.
Rate_linage
number
3,2
To store the rate_linage.
Rate_block
number
3,2
To store the rate_block.
Table No
6
Table Name
Staff
Description
To store the staff details.
Attribute
Data Type
Size
Description
staff_id
Varchar2
10
To store the staff id.
Staff_name
Varchar2
30
To store the staff name.
Sufficient test data to test referential integrity and support the queries
Referential integrity prevents users or applications from entering inconsistent data. There are many referential integrity rules that we can apply while defining the relationship between two tables.
These are the following implemented referential integrity
REFERENTIAL INTEGRITY BETWEEN Newspaper &Adv_request
QUERY
create table adv_request (
adv_id varchar2(10) PRIMARY KEY ,
posted_date timestamp(6) not null,
publishing_date date not null,
cust_id varchar2(10) references customer,
np_id varchar2 (10) references newspaper,
content varchar2 (500) not null,
expected_date timestamp(6),
customer_paid varchar2 (5) not null,
adv_way varchar2(10) not null,
rate number(5,2) not null,
cust_amount number(10,2)
)
Newspaper
Np_id(PK)
Np_name
Localize
Rate_line
Rate_block
Adv_request
Adv_id
Posted_date
Publish_date
Cust_id
Np_id(FK)
Content
Expectg_date
Cust_paid
Adv_way
Rate
Cust_amt.
REFERENTIAL INTEGRITY BETWEEN Complaint &Customer
QUERY
create table complaint
(
comp_id varchar2 (10) PRIMARY KEY,
comp_date timestamp(6),
comp_detail varchar2 (100),
comp_status varchar2(5),
cust_id varchar2(10) references customer,
adv_id varchar2 (10) references advertisement,
check (comp_status in ('open', 'close'))
)
Complaint
cmp_id
cmp_date
cmp_det.
Cmp_stat
Cust_id(FK)
Adv_id
Customer
Cust_id(PK)
Cust_name
Cust_type
Cust_address
REFERENTIAL INTEGRITY BETWEEN Payment &Adv_request
QUERY
create table payment(
pay_id varchar2 (10) PRIMARY KEY,
adv_id varchar2 (10) references advertisement,
cust_id varchar2 (10) references customer,
rate decimal(5,2) not null,
staff_id varchar2 (10) references staff,
customer_amount decimal(6,2),
newspaper_paid varchar2 (3),
check(newspaper in ('yes','no'))
)
Payment
Pay_id
Adv_id(FK)
Cust_id
Rate
Staff_id
Cust_amt.
News_paid
Adv_request
Adv_id(PK)
Posted_date
Publish_date
Cust_id
Np_id(FK)
Content
Expectg_date
Cust_paid
Adv_way
Rate
Cust_amt.
Appropriate constraints including referential integrity and check constraints
Table Name:
Customer
Constraints:
Check Constraint is applied to the column Cust_type.
Check Constraint is applied to the column Cust_address.pin.
Check Constraint is applied to the column Cust_address.ph.
Primary Key Constraint is applied to the column Cust_id
Description & Justification:
The column Cust_id is used as the primary key to uniquely identify each row in the table. As each Customer will have its own unique id.
The column cust_type stores the two type of the customer weather the customer is individual or agency thus a CHECK constraint is applied which will only allow entering individual or group character in this field.
The column cust_address.pin stores the pin code number of the particular city thus a CHECK constraint is applied to enter the number between 100000 and 999999.
The column cust_address.phone stores the phone number of the user thus a CHECK constraint is applied to enter the number between 1000000000 and 9999999999.
Table Name:
Adv_request
Constraints:
Primary Key Constraint is applied to the column Adv_id.
Foreign Key Constraint is applied to the column cust_id.
Foreign Key Constraint is applied to the column np_id.
Check Constraint is applied to the column Posted_date.
Check Constraint is applied to the column Publishing_Date.
Check Constraint is applied to the column content.
Check Constraint is applied to the column Customner_paid.
Check Constraint is applied to the column Adv_way.
Check Constraint is applied to the column Rate.
Description & Justification:
The column Adv_id is used as the primary key to uniquely identify each row in the table. It specify the particular advertisement.
The column cust_id is used as the foreign key to obtain the id of the particular customer. This is done by referring the cust_id column in the customer table.
The column np_id is used as the foreign key to obtain the id of the particular customer. This is done by referring the np_id column in the newspaper table.
A CHECK constraint is applied to the column Posted_date so that it will not accept any null value.
A CHECK constraint is applied to the column Publishing_date so that it will not accept any null value.
A CHECK constraint is applied to the column content so that it will not accept any null value.
A CHECK constraint is applied to the column Customer_paid so that it will not accept any null value.
A CHECK constraint is applied to the column Adv_way so that it will not accept any null value.
A CHECK constraint is applied to the column rate so that it will not accept any null value.
Table Name:
Staff
Constraints:
Primary Key Constraint is applied to the column staff_id.
Check Constraint is applied to the column name.
Description & Justification:
The column staff_id is used as the primary key to uniquely identify each row in the table. As each staff will have its own unique staff id.
A CHECK constraint is applied to the column name so that it will not accept any null value.
Table Name:
Newspaper
Constraints:
Primary Key Constraint is applied to the column np_id.
Check Constraint is applied to the column np_id.
Check Constraint is applied to the column Category.
Check Constraint is applied to the column Rate_linage.
Check Constraint is applied to the column Rate_block.
Description & Justification:
The column np_id is used as the primary key to uniquely identify each row in the table. As each newspaper will have its own unique newspaper id.
A CHECK constraint is applied to the column np_id so that it will not accept any null value.
A CHECK constraint is applied to the column Category so that it will not accept any null value.
A CHECK constraint is applied to the column Rate_linage so that it will not accept any null value.
A CHECK constraint is applied to the column Rate_block so that it will not accept any null value.
Table Name:
Payment
Constraints:
Primary Key Constraint is applied to the column pay_id.
Foreign Key Constraint is applied to the column cust_id.
Foreign Key Constraint is applied to the column adv_id.
Foreign Key Constraint is applied to the column staff_id.
Check Constraint is applied to the column Rate.
Check Constraint is applied to the column Newspaper_paid.
Description & Justification:
The column pay_id is used as the primary key to uniquely identify each row in the table. As each payment column will have its own payment id.
The column cust_id is used as the foreign key to obtain the id of the particular customer. This is done by referring the cust_id column in the customer table.
The column adv_id is used as the foreign key to obtain the id of the particular advertisement. This is done by referring the adv_id column in the adv_request table.
The column staff_id is used as the foreign key to obtain the id of the particular staff. This is done by referring the staff_id column in the staff table.
A CHECK constraint is applied to the column rate so that it will not accept any null value.
A CHECK constraint is applied to the column newspaper_paid so that it will accept only two character value i.e. 'yes' or 'no'.
Table Name:
Complaint
Constraints:
Primary Key Constraint is applied to the column comp_id.
Foreign Key Constraint is applied to the column cust_id.
Foreign Key Constraint is applied to the column adv_id.
Description & Justification:
The column comp_id is used as the primary key to uniquely identify each row in the table. As each complaint column will have its own complaint id.
The column cust_id is used as the foreign key to obtain the id of the particular customer. This is done by referring the cust_id column in the customer table.
The column adv_id is used as the foreign key to obtain the id of the particular advertisement. This is done by referring the adv_id column in the adv_request table.
The column staff_id is used as the foreign key to obtain the id of the particular staff. This is done by referring the staff_id column in the staff table.
A CHECK constraint is applied to the column rate so that it will not accept any null value.
A CHECK constraint is applied to the column newspaper_paid so that it will accept only two character value i.e. 'yes' or 'no'.
Automatic entry of PK values
None
Appropriate UDTs (User Defined Types)
CONTACTS
Source code
create type Contacts as object
(
location varchar(20),
pin number(6),
phone number(10)
);
Description
One object is created named as contacts with their attributes (location, pin, phone), size and data type.
SCRIPTS
Cursor Name:
Complaint_view
Script
create or replace procedure complaint_view IS
CURSOR crsr_complaint IS select comp_id, comp_date,comp_detail, cust_id, adv_id from complaint where comp_status='open' order by comp_date asc;
str_comp_id complaint.comp_id%type;
date_comp_date complaint.comp_date%type;
str_comp_detail complaint.comp_detail%type;
str_cust_id complaint.cust_id%type;
str_adv_id complaint.adv_id%type;
BEGIN
OPEN crsr_complaint;
dbms_output.put_line('Complaint_ID date detail customer_ID adv_ID');
dbms_output.put_line('----------- ---- ------- ---- ------------ ---------');
LOOP
FETCH crsr_complaint INTO str_comp_id, date_comp_date, str_comp_detail, str_cust_id, str_adv_id;
EXIT WHEN crsr_complaint%NOTFOUND;
IF crsr_complaint%FOUND THEN
dbms_output.put_line(str_comp_id || ' ' || date_comp_date|| ' ' || str_comp_detail || ' ' ||str_cust_id || ' ' || str_adv_id);
END IF;
END LOOP;
CLOSE crsr_complaint;
END;
/
Description & Justification:
This cursor complaint_view has been created to view the complaint.
Cursor Name:
newspaper_view
Script
create or replace procedure newspaper_view IS
CURSOR crsr_newspaper IS select np_id, np_name, category, localize, rate_lineage, rate_block from newspaper;
str_np_id newspaper.np_id%type;
str_np_name newspaper.np_name%type;
str_cat newspaper.category%type;
str_localize newspaper.localize%type;
num_lineage newspaper.rate_lineage%type;
num_block newspaper.rate_block%type;
BEGIN
OPEN crsr_newspaper;
dbms_output.put_line('Newspaper_ID Name Category Localize Rate_lineage Rate_block');
dbms_output.put_line('----------- ---- ------- ---- ------------ ---------');
LOOP
FETCH crsr_newspaper INTO str_news_id, str_np_name, str_cat, str_localize, num_lineage, num_block;
EXIT WHEN crsr_newspaper%NOTFOUND;
IF crsr_newspaper%FOUND THEN
dbms_output.put_line(str_news_id || ' ' || str_np_name|| ' ' ||str_cat|| ' ' || str_localize|| ' ' ||num_lineage || ' ' || num_block);
END IF;
END LOOP;
CLOSE crsr_newspaper;
END;
/
Description & Justification:
This cursor newspaper_view has been created to view the newspaper details.
PROCEDURES
Procedure Name:
Complaint_status
Script
create or replace procedure complaint_status (comp_id1 in varchar2)
IS
stat_cmp varchar2(5);
BEGIN
select comp_status into stat_cmp from complaint where comp_id=comp_id1;
if (stat_cmp='open')
dbms_output.put_line('Complaint status is ' || stat_cmp || '. Complaint will be solved soon...');
select * from complaint_handled where
else
if(stat_cmp='close')
dbms_output.put_line('Complaint status is ' || stat_cmp);
select
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid Complain ID');
select complaint_ID.nextval into comp_id from dual;
insert into complaint values(
'comp'||comp_id,
sysdate,
detail,
'open',
cust_id1,
adv_id);
/* displaying */
dbms_output.put_line('Complaint registered, Ref. ID: comp'||comp_id );
END;
/
Description & Justification:
This procedure complaint_status has been created to check the status of the complaint registered by the customer.
Procedure Name:
Customer_payment
Script
create or replace procedure customer_payment (adv_id in varchar2, amount in number)
IS
total decimal(10,2);
pay_id1 varchar2(10);
paid varchar2(5);
BEGIN
select customer_paid into paid from adv_request where adv_id=adv_id;
if (paid='yes') then
/* displaying */
dbms_output.put_line('payment done');
else
select cust_amount into total from adv_request where adv_id=adv_id;
/* customer has enter correct amount*/
if (total = amount) then
select pay_id.nextval into pay_id1 from dual;
insert into payment values(
'pay'||pay_id1,
adv_id,
'',
total,
0);
/* displaying */
dbms_output.put_line('Payment made on ID: pay'|| pay_id1 );
else
dbms_output.put_line('Invalid Amount');
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid Advertisement ID');
END;
/
Description & Justification:
This procedure customer_payment has been created to view weather the payment has been done by the customer or not.
Procedure Name:
publishing_status
Script
create or replace procedure publishing_status (advid in varchar2)
IS
pub_date timestamp(6);
BEGIN
select expected_date into pub_date from adv_request where adv_id=advid;
dbms_output.put_line('Advertisement Published Date ' || pub_date);
/*if (pub_date =to_date(' ')) then
dbms_output.put_line('Advertisement Not Published Yet.');
else
dbms_output.put_line('Advertisement Published on Date: ' || pub_date);
end if;*/
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid advertisement ID');
END;
/
Description & Justification:
This procedure publishing_status has been created to check the status of the publishing of the advertisement.
Procedure Name:
Register_agency
Script
create or replace procedure register_agency (cust_name in varchar2, location in varchar2, pin in number, phone in number)
IS
cust_id1 varchar2(10);
BEGIN
/* getting next customer id */
select cust_id.nextval into cust_id1 from dual;
/* registering the customer as agency */
insert into customer values(
'cust'||cust_id1,cust_name,
'agency',
cust_address(location,pin,phone));
/* displaying the customer id generated */
dbms_output.put_line('your customer id is: cust' || cust_id1);
END;
/
Description & Justification:
This procedure register_agency has been created to register customer as agency.
Procedure Name:
Register_complaint
Script
create or replace procedure register_complaint (cust_id1 in varchar2, advid in varchar2, detail in varchar2)
IS
comp_id varchar2(10);
BEGIN
select comp_id into comp_id from complaint where (cust_ID=cust_id1 and adv_id=advid and comp_status='open');
dbms_output.put_line('Complaint is open.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
select complaint_ID.nextval into comp_id from dual;
insert into complaint values(
'comp'||comp_id,
sysdate,
detail,
'open',
cust_id1,
adv_id);
/* displaying */
dbms_output.put_line('Complaint registered, Ref. ID: comp'||comp_id );
END;
/
Description & Justification:
This procedure register_complaint has been created to register complaint made by the customer.
Procedure Name:
Register_individual
Script
create or replace procedure register_individual (cust_name in varchar2, location in varchar2, pin in number, phone in number)
IS
cust_id1 varchar2(10);
BEGIN
/* getting next customer id */
select custid.nextval into cust_id1 from dual;
/* registering the custome as individual */
insert into customer values(
'cust'||cust_id1,cust_name,
'individual',
cust_address(location,pin,phone));
/* displaying the customer id generated */
dbms_output.put_line('your customer id is: cust' || cust_id1);
END;
/
Description & Justification:
This procedure register_individual has been created to register customer as individual.
Procedure Name:
Adv_req
Script
create or replace procedure adv_req (cust_id in varchar2, pub_date in date, np_id1 in varchar2
, content in varchar2, num in number, way in varchar2)
IS
cust_id1 varchar2(10);
rate decimal(5,2);
total decimal(10,2);
adv_id varchar2(10);
BEGIN
if (way = 'lineage') then
select rate_lineage into rate from newspaper where np_id=np_id1;
total:=(rate*num);
select adv_id.nextval into adv_id from dual;
insert into adv_request values(
'advert'||adv_id,
systimestamp,
pub_date,
cust_id,
np_id1,
content,
'',
'no',
way,
rate,
total);
/* displaying */
dbms_output.put_line('Your advertisement ID is: advert' || adv_id);
dbms_output.put_line('Total Cost is: Rs. '|| total);
else
if (way = 'block') then
select rate_block into rate from newspaper where np_id=np_id1;
total:=(rate*num);
select adv_id.nextval into advid from dual;
insert into adv_request values(
'advert'||adv_id,
systimestamp,
pub_date,
cust_id,
np_id1,
content,
'',
'no',
way,
rate,
total);
/* displaying */
dbms_output.put_line('Your Advertisement ID is: advert' || adv_id);
dbms_output.put_line('Total cost is: Rs. ' || total);
else
dbms_output.put_line('Print method should be only lineage or block');
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('invalid input');
END;
/
Description & Justification:
This procedure adv_req has been created to view the advertisement request and to know the details of the advertisement.
QUESTION 2 (B):- Document, demonstrate and justify any ONE feature you have implemented to optimize query performance.
Trigger No.
2
Trigger Name
Paymentupdation_t
Script
CREATE OR REPLACE TRIGGER PAYMENTUPDATION_T
BEFORE INSERT ON PAYMENT
FOR EACH ROW
DECLARE
TRIG_PAY_AD_COST NUMBER(10);
TRIG_AD_FROM VARCHAR2(20);
TRIG_ADV_ID VARCHAR2(20);
TRIG_PAY_PAS NUMBER(10);
TRIG_PAY_NEWS NUMBER(10,2);
TRIG_PAY_AD_PAYMENTDATE DATE;
TRIG_CUST_ID VARCHAR2(20);
TRIG_STAFF_ID VARCHAR2(20);
BEGIN
SELECT ADV_ID INTO TRIG_ADV_ID FROM ADV_REQUEST WHERE ADV_ID= :new.ADV_ID;
SELECT CUST_ID INTO TRIG_CUST_ID FROM ADV_REQUEST WHERE ADV_ID= :new.ADV_ID;
SELECT STAFF_ID INTO TRIG_STAFF_ID FROM STAFF_ADVT_ASSIGN WHERE ADV_ID= :new.ADV_ID;
SELECT AD_FROM INTO TRIG_AD_FROM FROM ADREQUEST WHERE ADV_ID= :new.AD_ID;
IF TRIG_AD_FROM='INDIVIDUAL COMPANY' THEN
TRIG_PAY_NEWS := :new.COST * 0.1;
:new.COST := :new.COST-TRIG_PAY_NEWS;
INSERT INTO PASTRANSACTION(ADV_ID,COST,CUST_ID,STAFF_ID)VALUES(TRIG_AD_ID,TRIG_PAY_NEWS,TRIG_CUST_ID,TRIG_STAFF_ID);
End IF;
IF TRIG_AD_FROM='ADVERTISING AGENCY' THEN
TRIG_PAY_NEWS := :new.COST * 0.08;
:new.COST := :new.COST-TRIG_PAY_NEWS;
INSERT INTO PASTRANSACTION(ADV_ID,COST,CUST_ID,STAFF_ID)VALUES(TRIG_ADV_ID,TRIG_PAY_NEWS,TRIG_CUST_ID,TRIG_STAFF_ID);
END IF;
END;
Description
This trigger has been made which would be fired when the payment of any customer is done by the PAS to the newspaper group.
QUESTION 3(A) Create the following queries
Create a query which shows for 1 individual customer, the total number and total value of advertisements placed and the profit to PAS
SOURCE CODE
select c.cust_id,c.cust_name, k.total_Advert, k.total, case when c.cust_type ='individual' then k.total * 0.10
else k.total * 0.08
end as profit from (select x.cust_id as id, count(x.adv_id) as total_Advert, sum(x.cust_amount) as total from adv_request x group by x.cust_id) k, customer c where k.id=c.cust_id
/
DESCRIPTION
This query is generated for fetching the data of one individual customer which allows for showing the total number and total value of advertisement placed and the profit to PAS.
Create a query which shows, for one newspaper group, the number of different types of advertisements placed in each paper owned by the group.
SOURCE CODE
select
decode(lag(np.category) over( order by np.category),
np.category,null,np.category)
category,
np.np_name,
np.np_id,
k.t1 as total
from (
select count(x.np_id) as t1, x.np_id as f from adv_request x group by x.np_id
) k,newspaper np where k.f=np.np_id
/
DESCRIPTION
This query is generated for fetching the data of one newspaper group and the number of advertisement placed in that newspaper group.
QUESTION 4:- Object SQL
When an advertisement is marked as not having appeared on the correct date, payment to the newspaper/newspaper group for that advert should be marked as invalid. Create a trigger to enforce this rule.
SOURCE CODE
create or replace trigger complaint_t
after insert on complaint
FOR EACH ROW
declare
pub_date date;
begin
select publishing_date into pub_date from adv_request where adv_id=:new.adv_id;
if ((sysdate - pub_date) > 0) then
dbms_output.put_line('--complaint registered-------');
else
dbms_output.put_line('-----complaint could not be registered----');
delete from complaint where comp_id=:old.comp_id;
commit;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid input---------');
END;
/
DESCRIPTION
Above query is fetching the records of the payment to the newspaper or newspaper group. If the advertisement is not published then user will do the complaint. The above trigger will show the complaint trigger.
Use any object SQL element to create a feature which would be useful to PAS and which would support the management or configuration of the database. You can implement a security feature, or a business rule, or a data access control feature. This section should focus on data management not data retrieval.
SOURCE CODE
Trigger No.
1
Trigger Name
valid_complain_t
Script
create or replace trigger valid_complain_t
after insert on complain
declare
n number(5);
id varchar2(10);
begin
select count(*) into n from complain;
select compid into id form complain where rownum = 2;
dbms_output.put_line(n || 'dddddd');
/* select publishing_date into pubdate from advertisement where advertid=:old.advertid;
if (greatest(pubdate,sysdate) = pubdate) then
delete from complain where compid=:old.compid;
dbms_output.put_line('-------');
else
dbms_output.put_line('action taken-------');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('Invalid input---------');*/
END;
/
Description
This trigger has been made to check for restricting the action to take of complaints of the customers when it has not been published.before the publish time.
Trigger No.
2
Trigger Name
Paymentupdation_t
Script
CREATE OR REPLACE TRIGGER PAYMENTUPDATION_T
BEFORE INSERT ON PAYMENT
FOR EACH ROW
DECLARE
Trig_pay_ad_cost number(10);
Trig_ad_from varchar2(20);
Trig_adv_id varchar2(20);
Trig_pay_pas number(10);
Trig_pay_news number(10,2);
Trig_pay_ad_paymentdate date;
Trig_cust_id varchar2(20);
Trig_staff_id varchar2(20);
Begin
Select adv_id into trig_adv_id from adv_request where adv_id= :new.adv_id;
Select cust_id into trig_cust_id from adv_request where adv_id= :new.adv_id;
Select staff_id into trig_staff_id from staff_advt_assign where adv_id= :new.adv_id;
Select ad_from into trig_ad_from from adrequest where adv_id= :new.ad_id;
If trig_ad_from='individual company' then
Trig_pay_news := :new.cost * 0.1;
:new.cost := :new.cost-trig_pay_news;
Insert into pastransaction(adv_id,cost,cust_id,staff_id)values(trig_ad_id,trig_pay_news,trig_cust_id,trig_staff_id);
End if;
If trig_ad_from='advertising agency' then
Trig_pay_news := :new.cost * 0.08;
:new.cost := :new.cost-trig_pay_news;
Insert into pastransaction(adv_id,cost,cust_id,staff_id)values(trig_adv_id,trig_pay_news,trig_cust_id,trig_staff_id);
End if;
End;
Description
This trigger has been made which would be fired when the payment of any customer is done by the PAS to the newspaper group.
Trigger No.
3
Trigger Name
PaymentRestriction_t
Script
create or replace trigger PaymentRestriction_t
before insert on complaint
For Each Row
declare
trig_pub_date Date
begin
select published_date into trig_pub_date Date from adv_request where adv_id=:new adverted;
if(sysdate)-(trig_pub_date)>2 then
else
RAISE_APPLICATION_ERROR(-20000,'cannot make payment before 48 hour of publishing'');
End IF;
End;
Description
This trigger would be created in order to do the payment to the newspaper group after 48 hour of publishing the advertisement.
QUESTION 5) Provide a Justification of Enterprise Database
To choose the right database for the table designing, developer has studied the functionalities of different databases and finally their comparison made the developer to choose oracle 10g for the database designing for the given case study. Oracle 10g supports both cost based optimizer and rule-based optimizer. Rule based optimizer has fifteen rules, ranked in order of efficiency. To each execution strategy, rule based optimizer assigns a score and by using these ranking the best execution strategy will be selected. Cost-based optimizer chose the execution path that requires least number of logical I/O operations (Begg, 2003). Cost-based optimizer knows about the table size. It is known to optimizer which table was bigger and based on this knowledge; optimizer selects the right table to begin the query regardless of the syntax of the SQL statement.
SR. NO.
FEATURES
DESCRIPTION
Real Application Clusters (RAC)
It enables to run packaged or custom application in a clustered computing environment, without changing the code. Oracle 10g has improved RAC named as Grid Computing.
Automated Storage Management
It provides management of disk drives. ASM can be used on a variety of configurations, including Oracle9i RAC installations. ASM is an alternative to the use of raw or cooked file systems.
Flushing the Buffer Cache
It allows to flush the database buffer cache with the alter system command using the flush buffer cache parameter.
Sorted Hash Clusters
It ensures rows will be stored within the hash cluster in the order defined when you created the hash cluster.
Model Clause
This will enhance SQL for calculations. SQL result sets can be treated like multidimensional arrays.
Transportable table space
Transportable table spaces can now operate across platforms with convert table space.
Asynchronous Commit
It enables the database clients to do more and more work while the database writes the redo log to disk which improves the transaction throughput by reducing database commit time.
CONCLUSION
Advance database System is the module of 8th semester. We are working on oracle for the first time. It seems tough as in starting everything about database as passes over the mind. But step-by-step practice on oracle platform makes easy to understand and after working on our group assignment it seems familiar to us.
This is the time when our module assignment is finished. We were guided for 4 months by our module lecturer on Oracle database and to check our mind and knowledge we assigned a practical (assignment). We were supposed to work on Oracle and design and implement a solution to a business problem of PAS.
During the project we had gone through many of the new terms like specialization and generalization related with entity relationship model, normalization of the database, triggers, joints, indexes, procedures etc. Designed Entity relationship model of the database to show the clear view regarding the relationship between the tables or entities. Many huddles came in the path but our lecturer's supports and group determination and co-operation made it possible to complete the project successfully with healthy results on time.
Our Learning Experiences:
Learned about designing and implementation techniques applied in oracle database.
Entity Relation Modeling and Designing
Relations, Constraints, Triggers, Procedures, Functions, User Define Data Types, Pl/SQL.
Optimization principles, Security, Performance and Tuning etc.
