Mariano Gálvez University of Guatemala

Faculty:Information Systems Engineering

Course: Database Foundations 1
Professor:Eng. Walter Obil
Cycle:6th Cycle
Section:A

Project 2

Nombre:Bernabé Feliciano David Chavajay Tzoc

No. Carné:1490-14-3475

Quetzaltenango, Quetzaltenango
04 de Noviembre de 2016
 INTRODUCTION

This report aims to provide information on

processes carried out in the development of the second
project in the databases course 1, like
also the analysis of it, said project was
structured and implemented in SQL and PLSQL code,
using ORACLE as the DBMS in its 12C version,
using the SQL DEVELOPER tool for the
development of the SQL language, creating objects, like
user, tables, procedures, indexes, views and
mainly the PACKAGE, it has also been used
the SQL DEVELOPER DATA MODELER tool, for
create the entity-relationship diagrams for each database
of data, on this occasion a database focused on
a HARDWARE STORE applying the 3 normal forms for
This way, to have a good design of said database, I ask that it be
the document is to be carefully analyzed for its
better understanding.
 PROJECT DESCRIPTION
This project is focused on the design of a database for a hardware store, the purpose
The main purpose of this project is to apply the knowledge acquired during the course, therefore
which have employed objects such as tables, views, procedures, indexes, and the package,
the DB is responsible for storing the following information:
1. Customers
2. The way you will pay for the products to be purchased
3. Suppliers
4. Description of the items
5. The receipt of the items sent by the suppliers
6. The details of the invoice to be issued
7. Issued invoice data
8. Customer delivery data (home service)

For the insertion of the different data for the previously mentioned tables, the following were used.
the 'procedures' for the facilitation of such insertions.

To facilitate queries, 'views' were implemented, and 3 management views were created.
focusing on the information most required by the owner of that hardware store.

Continuing with the queries, the 'indices' were also implemented to improve the
search time when executing a Query.
And to finalize the database, a package was created to store all the operations.
implemented in the database, and thus be able to be used at any time.

Below, you will find the ER diagram, SQL code, and PLSQL used in the
creation of said DB.

ER DIAGRAM

DDL AND PLSQL CODE

USER CREATION
create user C##PROYECTO2_1 IDENTIFIED BY PROYECTO2_1;
grant connect to C##PROYECTO2_1;
grant resource to C##PROYECTO2_1;
grant create view to C##PROYECTO2_1;

ALTER USER C##PROYECTO2_1 QUOTA UNLIMITED ON USERS;

TABLES
CREATE TABLE CLIENTE(
nit number(8) primary key,
customer_name varchar2(250) not null,
client_lastname varchar2(250) not null
direccion_cliente varchar2(200) not null,
telefono_cliente number(8) not null
);

CREATE TABLE PAYMENT(

payment_code number primary key,
payment_method varchar2(50) not null
);

CREATE TABLE PROVIDER(

provider_code number primary key
supplier_name varchar2(100) not null
direccion_proveedor varchar2(200) not null,
telefono_proveedor number(8) not null,
payment_code number not null,
foreign key (cod_pago) references PAGO(cod_pago)
);

CREATE TABLE ARTICLE(

article_code number primary key,
article_name varchar2(100) not null
color_articulo varchar2(100) not null,
item_measurements varchar2(30) not null
item_price number(8,2) not null
article_existence number(8) not null
supplier_code number not null
foreign key (cod_proveedor) references PROVIDER(cod_proveedor)
);

CREATE TABLE R_ARTICLE(

reception_code number primary key,
fecha_recepcion date not null,
article_code number not null,
supplier_code number not null,
foreign key (cod_articulo) references ARTICLE(cod_articulo),
foreign key (cod_supplier) references SUPPLIER(cod_supplier)
);

CREATE TABLE INVOICE(

invoice_number number primary key
issue_date date not null
customer_id number(8) not null,
payment_code number not null,
foreign key (nit_cliente) references CLIENTE(nit),
foreign key (cod_pago) references PAYMENT(cod_pago)
);

CREATE TABLE D_INVOICE(

detail_number number not null,
invoice_number number not null,
article_code number ,
quantity number(8) not null,
unit_price number(8,2) not null
precio_total number(8,2) not null,
primary key(num_detail,num_invoice),
foreign key (num_factura) references FACTURA(num_factura),
foreign key (cod_article) references ARTICLE(cod_article)
);

CREATE TABLE CUSTOMER_DELIVERY(

delivery_number number primary key
fecha_entrega date not null,
customer_number not null,
invoice_number number not null
foreign key (nit_cliente) references CLIENTE(nit),
foreign key (invoice_number) references INVOICE(invoice_number)
);
PROCEDURES
CREATE OR REPLACE PROCEDURE CustomerData(Pnit_customer number,
Pname varchar2
Last name varchar2,
Paddress varchar2,
Phone number
IS
BEGIN
INSERT INTO CLIENTE (nit,
nombre_cliente,apellido_cliente,direccion_cliente,telefono_cliente)
VALUES (Client_id, First_name, Last_name, Address, Phone);
DBMS_OUTPUT.PUT_LINE('Inserted Client: '||pnit_cliente|| ' ' ||pnombre|| ' ' ||papellido|| '
' ||pdirection|| ' ' ||pphone);
END;

CREATE OR REPLACE PROCEDURE PaymentData(Pcod_payment number,

Payment_Method varchar2
)
IS
BEGIN
INSERT INTO PAYMENT (payment_code, payment_method)
VALUES (Pcod_pago, Pforma_pago);
Inserted Payment:
END;

CREATE OR REPLACE PROCEDURE SupplierData(Psupplier_code number,

Pname varchar2
Paddress varchar2
 Phone number,
Pcod_payment number
)
IS
BEGIN
INSERT INTO
SUPPLIER(provider_code,supplier_name,supplier_address,supplier_phone,c
from_payment)
VALUES (Supplier_code, Name, Address, Phone, Payment_code);
DBMS_OUTPUT.PUT_LINE('Inserted Provider: '||pcod_proveedor|| ' ' ||pnombre|| ' ' ||
address || ' ' || phone || ' ' || payment_code;
END;

CREATE OR REPLACE PROCEDURE ArticleData(Pcod_article number,

Pname varchar2
Pcolor varchar2,
Pmeasures varchar2,
Pprecio_u number,
Pexistence number,
Pcod_supplier number
)
IS
BEGIN
INSERT INTO
ARTICULO(cod_articulo,nombre_articulo,color_articulo,medidas_articulo,precio_u_articulo,
existencia_articulo,cod_proveedor)
VALUES (Pcod_article, Pname, Pcolor, Pmeasurements, Punit_price, Pexistence, Pcod_provider);
Inserted Article:
' ||pmedidas|| ' ' ||pprecio_u||
' ' ||pexistencia|| ' ' ||pcod_proveedor);
END;

CREATE OR REPLACE PROCEDURE ReceptionData(Pcod_recepcion number,

Pfecha_recepcion date,
Pcod_article number
Pcod_supplier number
)
IS
BEGIN
INSERT INTO R_ARTICLE(receiving_code, receiving_date, article_code, supplier_code)
VALUES (Pcod_recepcion, Pfecha_recepcion, Pcod_articulo, Pcod_proveedor);
Inserted Send:
pcod_article || ' ' || pcod_provider;
END;

CREATE OR REPLACE PROCEDURE InvoiceData(invoice_number number,

Pfecha date,
Customer number,
Pcod_payment number
)
IS
BEGIN
INSERT INTO INVOICE(invoice_number,issue_date,customer_nit,payment_code)
VALUES (Pinvoice_number, Pdate, Pclient_id, Ppayment_code);
DBMS_OUTPUT.PUT_LINE('Invoice Inserted: '||pnum_factura|| ' ' ||pfecha|| ' ' ||
client_pnit || ' ' || payment_code
END;

CREATE OR REPLACE PROCEDURE InvoiceData(Pnum_detail number,

Invoice number,
Pcod_article number
Pcantidad number,
Pprecio_unidad number,
total_price number
)
IS
BEGIN
INSERT INTO
D_FACTURA(num_detalle,num_factura,cod_articulo,cantidad,precio_unidad,precio_total)
VALUES
(Pnum_detalle,Pnum_factura,Pcod_articulo,Pcantidad,Pprecio_unidad,Pprecio_total);
UPDATE INVOICE SET total_price=quantity*unit_price WHERE total_price=0;
DBMS_OUTPUT.PUT_LINE('Inserted Invoice Detail: '||pnum_detalle||' '||
invoice_number || ' ' || article_code
|| ' ' ||pcantidad|| ' ' ||pprecio_unidad|| ' ' ||pprecio_total);
END;

CREATE OR REPLACE PROCEDURE DeliveryData(Pnum_delivery number,

Pdate date
Client number,
Invoice number
)
IS
BEGIN
INSERT INTO CLIENT_DELIVERY(delivery_number, delivery_date, client_nit, invoice_number)
VALUES (Delivery_number, Delivery_date, Client_NIT, Invoice_number);
DBMS_OUTPUT.PUT_LINE('Delivery Inserted: '||pnum_entrega|| ' ' ||pfecha|| ' ' ||
pnit_cliente || ' ' || pnum_factura
END;
DATA INSERTION IN PROCEDURES
SET SERVEROUTPUT ON;

BEGIN
DatosCliente('12345678','Bernabé','Chavajay','Santa María Visitación,Sololá','49381201');
DatosCliente('87654321','Francisco','Chavajay','Santa María
Visitación, Sololá
DatosCliente('55555555','Cecilia','Chavajay','Santa María Visitación,Sololá','11111111');
DatosCliente('66666666','Ismael','Chavajay','Santa María Visitación,Sololá','22222222');
DatosCliente('77777777','Patricia','Chavajay','Santa María Visitación,Sololá','33333333');
END;

BEGIN
DatosPago('1','Efectivo');
DatosPago('2','Cheque');
DatosPago('3','Tarjeta Crédito');
DatosPago('4','Tarjeta Débito');
END;

BEGIN
DatosProveedor('1','Ferre S.A.','Guatemala, Guatemala','12345678','2');
DatosProveedor('2','Ferreteria Comercial Pacifico S.A.','Guatemala,
Guatemala
DatosProveedor('3','Ferreteria rex','Guatemala, Guatemala','44444444','1');
DatosProveedor('4','Ferretería la sexta','Chiquimula, Guatemala','11111111','1');
DatosProveedor('5','Ferretería la sexta, S.A.','Guatemala, Guatemala','22222222','1');
END;

BEGIN
DatosArticulo('1','Desarmador punta estrella','Negro','Sin Medida','20.50','30','1');
DatosArticulo('2','Desarmador 10 en 1','Rojo','Sin Medida','210.50','40','2');
DatosArticulo('3','Broca para pared','Plateado','1/4','14.75','20','1');
DatosArticulo('4','Broca para madera','Gris','1/2','34.75','20','3');
DatosArticulo('5','Broca para madera','Gris','1/4','24.75','20','3');
END;

BEGIN
DatosRecepcion('1','17/10/2016','1','1');
DatosRecepcion('2','17/10/2016','2','2');
DatosRecepcion('3','18/10/2016','3','1');
DatosRecepcion('4','19/10/2016','4','3');
DatosRecepcion('5','20/10/2016','5','3');
END;

BEGIN
DatosFactura('1','19/10/2016','12345678','1');
DatosFactura('2','20/10/2016','87654321','3');
DatosFactura('3','21/10/2016','55555555','1');
DatosFactura('4','22/10/2016','66666666','1');
DatosFactura('5','23/10/2016','77777777','1');
END;

BEGIN
DatosD_Factura('1','1','1','3','20.50','0');
DatosD_Factura('2','1','2','5','210.50','0');
DatosD_Factura('3','2','3','3','14.75','0');
DatosD_Factura('4','2','4','3','34.75','0');
DatosD_Factura('5','3','5','3','24.75','0');
DatosD_Factura('6','3','1','4','20.5','0');
DatosD_Factura('7','4','2','3','210.50','0');
DatosD_Factura('8','5','2','2','210.50','0');
END;

BEGIN
DatosEntrega('1','22/10/2016','12345678','1');
DatosEntrega('2','23/10/2016','87654321','2');
DatosEntrega('3','24/10/2016','55555555','3');
DatosEntrega('4','25/10/2016','66666666','4');
DatosEntrega('5','26/10/2016','77777777','5');
END;
QUERIES INTEGRATING 2 OR MORE TABLES
----------BillingQuery--------------
SELECT
det.num_factura,fac.fecha_emision,fac.nit_cliente,cl.nombre_cliente,cl.apellido_cliente,de
t.article_code,
art.nombre_articulo,art.medidas_articulo,art.color_articulo,[Link],det.precio_unidad
,det.precio_total,pag.forma_pago
FROM D_INVOICE det, INVOICE fac, CLIENT cl, ARTICLE art, PAYMENT pag
WHERE det.invoice_number=fac.invoice_number and fac.client_nit=[Link] and
det.cod_article=art.cod_article
and fac.cod_pago=pag.cod_pago;

---------Customer Delivery Query--------------

SELECT
ent.num_entrega,ent.fecha_entrega,fac.num_factura,ent.nit_cliente,cl.nombre_cliente,
cl.apellido_cliente,cl.direccion_cliente,cl.telefono_cliente
FROM CUSTOMER_DELIVERY ent, CUSTOMER cl, INVOICE fac
WHERE ent.num_entrega = fac.num_factura and ent.nit_cliente = [Link];

Query Products with their respective suppliers

SELECT
art.cod_articulo,art.nombre_articulo,art.precio_u_articulo,art.existencia_articulo,pro.cod_p
supplier
pro.nombre_proveedor,pro.direccion_proveedor,pro.telefono_proveedor,pag.forma_pago
FROM ARTICLE art, SUPPLIER pro, PAYMENT pag
WHERE art.cod_provider = pro.cod_provider and pro.cod_payment = pag.cod_payment;
MANAGERIAL VIEWS
CREATE OR REPLACE VIEW VW_BILLING
AS
(
SELECT
det.num_factura,fac.fecha_emision,fac.nit_cliente,cl.nombre_cliente,cl.apellido_cliente,de
t.article_code,
art.nombre_articulo,art.medidas_articulo,art.color_articulo,[Link],det.precio_unidad
,det.precio_total,pag.forma_pago
FROM D_INVOICE det, INVOICE fac, CLIENT cl, ARTICLE art, PAYMENT pag
WHERE det.invoice_number=fac.invoice_number and fac.client_nit=[Link] and
det.article_code=art.article_code
and fac.cod_pago=pag.cod_pago
);

SELECT * FROM INVOICING_VIEW;

CREATE OR REPLACE VIEW VW_DELIVERY

AS
(
SELECT
ent.num_entrega,ent.fecha_entrega,fac.num_factura,ent.nit_cliente,cl.nombre_cliente,
cl.apellido_cliente,cl.direccion_cliente,cl.telefono_cliente
FROM CUSTOMER_DELIVERY ent, CUSTOMER cl, INVOICE fac
WHERE ent.num_delivery = fac.num_invoice and ent.nit_client = [Link]
);

SELECT * FROM VW_DELIVERY;

CREATE OR REPLACE VIEW VW_PRODUCTOS

AS
(
SELECT
art.cod_articulo,art.nombre_articulo,art.precio_u_articulo,art.existencia_articulo,pro.cod_p
provider
pro.nombre_proveedor,pro.direccion_proveedor,pro.telefono_proveedor,pag.forma_pago
FROM ARTICLE art, SUPPLIER pro, PAYMENT pag
WHERE art.provider_code = pro.provider_code and pro.payment_code = pag.payment_code
);

SELECT * FROM VW_PRODUCTS;

INDICES
CREATE INDEX IND_CLIENTE ON CLIENTE
(
nit, nombre_cliente, apellido_cliente, direccion_cliente, telefono_cliente
);

select * from all_indexes where table_name = 'CLIENTE';

CREATE INDEX IND_PAGO ON PAGO

(
cod_pago, forma_pago
);

CREATE INDEX IND_PROVIDER ON PROVIDER

(
cod_proveedor,nombre_proveedor,direccion_proveedor,telefono_proveedor,cod_pago
);

CREATE INDEX IND_ARTICLE ON ARTICLE

(
cod_articulo,nombre_articulo,color_articulo,medidas_articulo,precio_u_articulo,existencia_
articulo,cod_proveedor
);

CREATE INDEX IND_RECEPCION ON R_ARTICLE

(
cod_recepcion,fecha_recepcion,cod_articulo,cod_proveedor
);

CREATE INDEX IND_DETAIL ON INVOICE_FACT

(
num_detalle,num_factura,cod_articulo,cantidad,precio_unidad,precio_total
);

CREATE INDEX IND_FACTURA ON INVOICE

(
num_factura,fecha_emision,nit_cliente,cod_pago
);

CREATE INDEX IND_ENTREGA ON CLIENT_DELIVERY

(
num_entrega,fecha_entrega,nit_cliente,num_factura
);
CREATION OF PACKAGE HARDWARE_OPERATIONS
------CREATION OF PACKAGE HARDWARE_OPERATIONS-----------------
CREATE OR REPLACE PACKAGE HARDWARE_OPERATIONS
AS
PROCEDURE CustomerData(Pnit_customer number,
Pname varchar2,
Last name varchar2,
address varchar2
Ptelephone number);

PROCEDURE PaymentData(Pcod_payment number,

Payment_method varchar2
);

PROCEDURE SupplierData(Pcod_supplier number,

Pname varchar2
Pdireccion varchar2
Phone number,
Pcod_payment number
);

PROCEDURE ArticleData(Pcod_article number,

Pname varchar2,
Pcolor varchar2,
Pmeasures varchar2,
Pprice_u number,
Pexistence number,
Supplier Pcod number
);

PROCEDURE ReceptionData(Pcod_recepcion number,

Reception date
Pcod_article number,
Pcod_provider number
);

PROCEDURE InvoiceData(Pnum_invoice number,

Pfecha date,
Client number,
Pcod_payment number
);

PROCEDURE DataD_Invoice(Pnum_detail number,

Invoice number,
Pcod_article number
Pcantidad number,
unit_price number
 total_price number
);

PROCEDURE DeliveryData(Pnum_delivery number,

Pdate date
Client number,
Invoice number
);
END HARDWARE_OPERATIONS;

--------CREATION BODY OF THE PACKAGE HARDWARE_OPERATIONS-----------

CREATE OR REPLACE PACKAGE BODY HARDWARE_OPERATIONS
AS
PROCEDURE CustomerData(Pnit_customer number,
Pname varchar2
Last name varchar2,
Pdirection varchar2
Phone number
IS
BEGIN
INSERT INTO CLIENTE (nit,
nombre_cliente,apellido_cliente,direccion_cliente,telefono_cliente)
VALUES (Pnit_client, Pfirst_name, Plast_name, Paddress, Pphone);
Inserted Client:
' ||pdireccion|| ' ' ||ptelefono);
END ClientData;

PROCEDURE PaymentData(Pcod_payment number,

Payment_method varchar2
)
IS
BEGIN
INSERT INTO PAYMENT (payment_code, payment_method)
VALUES (Pcod_payment, Pform_payment);
Inserted Payment:
END PaymentData;

PROCEDURE SupplierData(Pcod_supplier number,

Pname varchar2
Pdireccion varchar2
Phone number,
Pcod payment number
)
IS
BEGIN
INSERT INTO
PROVEEDOR(cod_proveedor,nombre_proveedor,direccion_proveedor,telefono_proveedor,c
od_payment)
VALUES (Supplier_code, Supplier_name, Supplier_address, Supplier_phone, Payment_code);
DBMS_OUTPUT.PUT_LINE('Inserted Supplier: '||pcod_proveedor|| ' ' ||pnombre|| ' ' ||
address || ' ' || phone || ' ' || payment_code
END SupplierData;
PROCEDURE ArticleData(Pcod_article number,
Pname varchar2
Pcolor varchar2
Pmeasures varchar2,
Price_u number,
Pexistence number,
Pcod_provider number
)
IS
BEGIN
INSERT INTO
ARTICULO(cod_articulo,nombre_articulo,color_articulo,medidas_articulo,precio_u_articulo,
existencia_articulo,cod_proveedor)
VALUES (Pcod_article,Pname,Pcolor,Pmeasurements,Pprice_u,Pexistence,Pcod_provider);
Inserted Item:
||pmedidas|| ' ||pprecio_u||
' ' ||pexistencia|| ' ' ||pcod_proveedor);
END ArticleData;

PROCEDURE ReceptionData(Pcod_recepcion number,

Pfecha_recepcion date,
Pcod_article number
Pcod_supplier number
)
IS
BEGIN
INSERT INTO R_ARTICLE(receiving_code, receiving_date, article_code, supplier_code)
VALUES (Pcod_recepcion, Pfecha_recepcion, Pcod_articulo, Pcod_proveedor);
Inserted Shipment:
pcod_article || ' ' || pcod_provider;
END ReceptionData;

PROCEDURE InvoiceData(Pnum_invoice number,

Pdate date
Client number,
Pcod_payment number
)
IS
BEGIN
INSERT INTO INVOICE(invoice_number, issue_date, client_nit, payment_code)
VALUES (Pnum_invoice, Pdate, Pclient_nit, Ppayment_code);
Invoice Inserted:
pnit_cliente || ' ' || pcod_pago;
END InvoiceData;

PROCEDURE DataD_Invoice(Pnum_detail number,

Invoice number,
Pcod_article number
Pcantidad number,
unit_price number
Total_price number
)
IS
BEGIN
INSERT INTO
D_FACTURA(num_detalle,num_factura,cod_articulo,cantidad,precio_unidad,precio_total)
VALUES
(Detail number, Invoice number, Article code, Quantity, Unit price, Total price);
UPDATE D_FACTURA SET total_price=quantity*unit_price WHERE total_price=0;
DBMS_OUTPUT.PUT_LINE('Inserted Invoice Detail: '||pnum_detalle||' '||
invoice_number || ' ' || article_code
|| ' ' || quantity || ' ' || unit_price || ' ' || total_price);
END DataD_Invoice;

PROCEDURE DeliveryData(Pnum_delivery number,

Pfecha date,
Customer number,
Pnum_factura number
)
IS
BEGIN
INSERT INTO CLIENT_DELIVERY(delivery_number, delivery_date, client_nit, invoice_number)
VALUES (Pnum_delivery, Pdate, Pclient_nit, Pinvoice_number);
DBMS_OUTPUT.PUT_LINE('Delivery Inserted: '||pnum_entrega|| ' ' ||pfecha|| ' ' ||
client_id || ' ' || invoice_number
END DeliveryData;

END HARDWARE_OPERATIONS;
EXECUTING PACKAGE HARDWARE_OPERATIONS
EXECUTE
OPERACIONES_FERRETERIA.DatosCliente('88888888','Prueba1','Prueba1','Prueba1','99999
999');

select * from client;

 CONCLUSIONS
It is very important to understand the basic concepts
of SQL.

The ER diagram is fundamental for understanding

the basic structure of a database, so its good
modeling will greatly help us in the work
of the DBA.

The use of VIEWS is fundamental for

perform more requested queries from a database and so on
place to write and execute the same query several times
Sometimes the VIEW is executed.

The package is essential for storing everything.

operations implemented in the DB design
and it can be used at any time
making use of the EXECUTE command remaining the
syntax of the next way
EXECUTE_PACKAGE_NAME.OPERATION_NAME

Constant practice improves skills in

BD for what is important to seek new
information about the topic, to be a good
DBA.
 RECOMMENDATIONS
Constant practice of knowledge
acquired.

Create a good ER Diagram to improve the

ease of design.

Detailed analysis, to provide the best solution to

problem posed

Apply the normal forms to achieve a good

Database Design.

Self-taught.