Build RDBMS tables

1. One-to-One : One table has relation with another table (entity relation between only 2 tables)

Master table: Table which has primary key is called master table
Child table: table which depended on the table which has primary key is called child table

Example: 1. Create the supplier with vendor_id, vendor_name. Vendor_id should be primary key column
2. Create purchase table with pono, item,qty,price,
vendor_id which should be referred in supplier table i.e., vendor_id should exist in supplier table

sample Data:

One-to-One

Table: Supplier Table: Purchase

Vendor_id Vendor_name PONO Item Qty Price Vendor_Id
——— ———— —————————————–
100 Dell 800 lp001 10 15000 100 (foreign key column)
101 HP
102 HCL
————————- ——————————————-

STep 1: Create supplier table and insert some data

SQL> create table supplier_78
(vendor_id number primary key,
vendor_name varchar2(10) not null);

Table created.

SQL> insert into supplier_78 values(&vendor_id, ‘&vendor_name’);
Enter value for vendor_id: 100
Enter value for vendor_name: dell
old 1: insert into supplier_78 values(&vendor_id, ‘&vendor_name’)
new 1: insert into supplier_78 values(100, ‘dell’)

1 row created.

SQL> /
Enter value for vendor_id: 101
Enter value for vendor_name: hp
old 1: insert into supplier_78 values(&vendor_id, ‘&vendor_name’)
new 1: insert into supplier_78 values(101, ‘hp’)

1 row created.

SQL> /
Enter value for vendor_id: 102
Enter value for vendor_name: hcl
old 1: insert into supplier_78 values(&vendor_id, ‘&vendor_name’)
new 1: insert into supplier_78 values(102, ‘hcl’)

1 row created.

SQL> select * from supplier_78;

VENDOR_ID VENDOR_NAM

---

   100 dell
   101 hp
   102 hcl

Step 2: Create purchase table and insert data

SQL> create table purchase_78
2 (pono number primary key,
3 item varchar2(10) not null,
4 qty number,
5 price number,
6 vendor_no number,
7 foreign key(vendor_no) references supplier_78 (vendor_id)
8 );

Table created.

Inserting records

SQL> insert into purchase_78 values(&pono, ‘&item’,&qty,&price,&vendor_id)
2 ;
Enter value for pono: 800
Enter value for item: lp001
Enter value for qty: 10
Enter value for price: 1500
Enter value for vendor_id: 105
old 1: insert into purchase_78 values(&pono, ‘&item’,&qty,&price,&vendor_id)
new 1: insert into purchase_78 values(800, ‘lp001’,10,1500,105)
insert into purchase_78 values(800, ‘lp001’,10,1500,105)
*
ERROR at line 1:
ORA-02291: integrity constraint (APPS.SYS_C001107617) violated – parent key not
found

Above record not inserted because 105 vendor_id is not exist in supplier table

SQL> /
Enter value for pono: 800
Enter value for item: lp001
Enter value for qty: 10
Enter value for price: 1500
Enter value for vendor_id: 100
old 1: insert into purchase_78 values(&pono, ‘&item’,&qty,&price,&vendor_id)
new 1: insert into purchase_78 values(800, ‘lp001’,10,1500,100)

1 row created.

One-To-Many

Supplier Purchase: Invoice
Vendor_id Vendor_name PONO Item Qty Price Vendor_Id InvNo Amt PONO
——— ———— ————————————— ——————–
100 Dell 800 lp001 10 15000 100 771 250000 800
101 HP

102 HCL

SQL> create table invoice_78
(invoice_no number primary key,
inv_amt number,
inv_dt date,
po_no number,
foreign key(po_no) references purchase_78 (pono)
)

Sample data:

INVOICE_NO INV_AMT INV_DT PO_NO

---

   771      15000 10-MAY-12        800
   772    25000 12-may-12        808   (wrong, because po does not exist in purchase table)

================================================================================================

Many-to-Many

Many-to-Many

Supplier Purchase: Invoice Payments:
Vendor_id Vendor_name PONO Item Qty Price Vendor_Id InvNo Amt PONO Pay_ID AmtPaid Dt Invno Vendor_ID
——— ———— ————————————— ——————– ————————————-
100 Dell 800 lp001 10 15000 100 771 250000 800 9001 20000 x- 771 100
101 HP
102 HCL
———————– ——————————————- —————————————————————-

SQL> create table payments_78
(payid number primary key,
amt_paid number,
paid_dt date,
invno number,
foreign key (invno) references invoice_78 (invno),
vendor_id number,
foreign key (vendor_id) references supplier_78 (vendor_id)
);

Task : Build tables for customers, sales orders, invoices and cash receipts

Share this post: on Twitter on Facebook on Google+