Inventory System for a Shopping Mall

Extend the above schema up to that level from where a Shopping Mall owner can manage their product inventory and billing to customer. This system must control the price; for example, selling price must not be smaller than recently buying price for a particular product. Consider that, a customer can buy more than one product at a time. Provide DDL, DML and Extended Schema in a Word File using link.

7 comments:

  1. https://www.dropbox.com/sh/jo3s0bw1y41o1z1/AAABXmTRZ3uv3zuUyVDyDeRYa?dl=0

    ReplyDelete
  2. https://www.dropbox.com/s/bnxrx3frys3sk7i/Inventory%20System%20for%20a%20Shopping%20Mall.txt?dl=0

    ReplyDelete
  3. CREATE table vendor
    (v_id varchar2(10),
    v_name varchar2(30),

    constraint vendor_pk primary key(v_id),
    constraint vendor_uq unique(v_name)
    );

    CREATE table product
    (p_id varchar2(10),
    p_name varchar2(30),
    v_id varchar2(10),

    constraint product_pk primary key(p_id),
    constraint product_uq unique(p_name),
    constraint product_fk_of_vendor foreign key(v_id) references vendor(v_id)
    );

    CREATE table client
    (c_id varchar2(10),
    c_name varchar2(30),
    city varchar2(20),

    constraint client_pk primary key(c_id),
    constraint client_uq unique(c_name),
    constraint client_chk check(city in('Dhaka','Sylhet','Chittagong'))
    );

    CREATE table buy
    (sl_no number(10),
    p_id varchar2(10),
    unit_price number(20),
    quantity number(20),
    buy_date date,

    constraint buy_pk primary key(sl_no),
    constraint buy_fk_of_product foreign key(p_id) references product(p_id),
    constraint buy_chk_unit_price check(unit_price>0),
    constraint buy_chk_quantity check(quantity>0)
    );


    CREATE table sell
    (sl_no number(10),
    c_id varchar2(10),
    p_id varchar2(10),
    quantity number(20),
    unit_price number(20),
    total_price generated always as(quantity*unit_price),

    constraint sell_pk primary key(sl_no),
    constraint sell_fk_of_client foreign key(c_id) references client(c_id),
    constraint sell_fk_of_product foreign key(p_id) references product(p_id),
    constraint sell_chk_quantity check(quantity>0)
    );

    INSERT into vendor
    VALUES('v101','Eva');

    INSERT into product
    VALUES('p101','fan','v101');

    INSERT into client
    VALUES('c101','zadid','Dhaka');

    INSERT into buy
    VALUES(100001,'p101',10,10,'11/22/2017');

    INSERT into sell
    (sl_no,c_id,p_id,quantity,unit_price)
    VALUES(100001,'c101','p101',10,10);

    select *from sell;
    commit;

    ReplyDelete
  4. create table vendor
    (v_id number(10),v_name varchar2(20));

    alter table vendor
    add constraint v_id_pk primary key(v_id);

    alter table vendor
    add constraint v_name_uq unique (v_name);

    Desc vendor;



    create table product
    (
    p_id number(10),p_name varchar2(10),v_id number (10)
    );
    alter table product
    add constraint p_id_pk primary key (p_id);
    alter table product

    add constraint p_name_uq unique (p_name);
    alter table product
    add constraint v_id_fk_product foreign key(v_id) references vendor (v_id);

    Desc product;

    create table client
    (
    c_id varchar2(20),c_name varchar2(20),city varchar2(20)
    )
    alter table client
    add constraint c_id_pk primary key (c_id);
    alter table client
    add constraint c_name_uq unique (c_name);
    alter table client
    add constraint city_ck check (city in('Dhaka,tongi'));
    Desc client;
    create table sell
    (
    sl_no number(10),c_id varchar2(20),p_id number(10),quantity number(5), unite_price number(5), total_price number(10)
    )
    alter table sell
    add constraint sl_no_pk primary key (sl_no);
    alter table sell
    add constraint c_id_fk_sell foreign key(c_id) references client (c_id);
    alter table sell
    add constraint p_id_fk_sell foreign key(p_id) references product (p_id);
    alter table sell
    add constraint quantity_ck check (quantity Between(1and500));
    Desc sell;

    create table buy
    (
    sl_no number(10),p_id number(10),unite_price number(10),quantity number(10),buy_date date
    )
    alter table buy
    add constraint slb_no_pk primary key (sl_no);
    alter table buy
    add constraint p_id_fk_buy foreign key (p_id) references product(p_id);
    alter table buy
    add constraint unite_price_ck check (unite_price Between(1 and99999));
    alter table buy
    add constraint quantity_ck check (quantity Between(1 and 500));
    Desc buy;

    ReplyDelete
  5. create table vendor
    (
    v_id number(10),
    v_name varchar2(11),

    constraint pk_vendor_v_id primary key(v_id),
    constraint uk_vendor_v_name unique(v_name)
    );
    desc vendor;

    insert into vendor
    values(201530,'aa');

    select *from vendor;

    create table product
    (
    p_id number(12),
    p_name varchar2(13),
    v_id number(10),
    constraint pk_product_p_id primary key(p_id),
    constraint uk_product_p_name unique(p_name),
    constraint fk_product_v_id foreign key(v_id) references vendor(v_id)
    );
    desc product;

    insert into product
    values(201531,'bb',201530);

    select * from product;



    create table buy
    (
    sl_no number(14),
    p_id number(12),
    unite_price number(10),
    quantity number(16),
    buy_date date,
    constraint pk_buy_sl_no primary key(sl_no),
    constraint fk_buy_p_id foreign key(p_id) references product(p_id),
    constraint ck_buy_unit check(unite_price>0),
    constraint ck_buy_quantity check(quantity>0)
    );

    insert into buy
    values(01,201531,1000,07,'03/23/17');

    select *from buy;

    create table client
    (
    cl_id number(10),
    c_name varchar2(11),
    city varchar2(12),
    constraint pk_client_cl_id primary key(cl_id),
    constraint uk_client_c_name unique(c_name),
    constraint ck_client_city check(city in('dhaka','mymensingh'))
    );

    insert into client
    values(201532,'cc','mymensingh');

    select *from client;


    create table sell
    (
    sl_no number(17),
    cl_id number(10),
    p_id number(12),
    unite_price number(15),
    total_price number(20),
    constraint pk_sell_sl_no primary key(sl_no),
    constraint fk_sell_cl_id foreign key(cl_id) references client(cl_id),
    constraint fk_sell_p_id foreign key(p_id) references product(p_id),
    constraint ck_sell_unite_price check(unite_price>0)

    );

    insert into sell
    values(02,201532,201531,200,5000);
    select *from sell;


    ReplyDelete
  6. create table vendore1
    (
    v_id number(15),
    V_name varchar2(20),
    constraint pk_vendor1__v_id primary key(v_id),
    constraint uq_vendor1_v_name unique(v_name)
    );

    desc vendore1;

    insert into vendore1
    values(0157256,'arif');
    select *from vendore1;

    create table product1
    (
    p_id number(15),
    p_name varchar2(20),
    v_id number(15),
    constraint pk_product1_p_id primary key(p_id),
    constraint uq_product1_p_name unique(p_name),
    constraint fk_product1_v_id foreign key(v_id) references vendore1(v_id)

    );

    desc product1;

    insert into product1
    values(0123245,'ahsan',0157256);
    select * from product1;

    create table buy1
    (
    sl_no varchar(15) primary key,
    p_id number(15),
    unit_price number(20),
    quantity number(15),
    buy_date date,
    constraint fk_buy1_p_id foreign key(p_id) references product1(p_id),
    constraint check_buy1_unit_price check(unit_price>0),
    constraint check_buy1_quantity check(quantity>0)

    );

    desc buy1;

    insert into buy1
    values(54511,'0123245',41152,815424,'02/07/17');
    select * from buy1;

    create table client1
    (
    e_id number(15) primary key,
    e_name varchar2(20),
    city varchar2(15),
    constraint uq_client1_e_name unique(e_name),
    constraint check_client1_city check(city in('barisal','khulna'))
    );

    desc client1;

    insert into client1
    values(151585,151354,'barisal');
    select * from client1;

    create table sell2
    (
    sl_no varchar(15) primary key,
    e_id number(15),
    p_id number(15),
    unit_price number(20),
    total_price number(20),
    constraint fk_sell2_e_id foreign key(e_id) references client1(e_id),
    constraint fk_sell2_p_id foreign key(p_id) references product1(p_id),
    constraint check_sell2_unit_price check(unit_price>0)

    );

    desc sell2;

    insert into sell2
    values('254654165',151585,0123245,545452,47478);
    select * from sell2;

    ReplyDelete
  7. INVENTORY SYSTEM FOR A SHOPPING MALL
    ....................................
    ....................................

    create table vendor
    (
    v_id number(10),
    v_name varchar2(20),
    constraint pk_vendor_v_id primary key(v_id),
    constraint uq_vendor_v_name unique(v_name)
    );
    desc vendor;
    insert into vendor
    values(2015820,'Rafiqul');
    select *from vendor;

    create table product
    (
    p_id number(12),
    p_name varchar2(20),
    v_id number(10),
    constraint pk_product_p_id primary key(p_id),
    constraint uq_product_p_name unique(p_name),
    constraint fk_product_v_id foreign key(v_id) references vendor(v_id)
    );

    desc product;

    insert into product
    values(2015820,'Sabbir',2015820);

    select * from product;

    create table buy
    (
    sl_no number(10),
    p_id number(12),
    unit_price number(20),
    quantity number(15),
    buy_date date,
    constraint pk_buy_sl_no primary key(sl_no),
    constraint fk_buy_p_id foreign key(p_id) references product(p_id),
    constraint ck_buy_unit_price check(unit_price>0),
    constraint ck_buy_quantity check(quantity>0)
    );

    desc buy;

    insert into buy
    values(10,2015820,2000,1000,'03/05/17');

    select *from buy;

    create table client
    (
    c_id number(10),
    c_name varchar2(20),
    city varchar2(10),
    constraint pk_client_c_id primary key(c_id),
    constraint uq_client_c_name unique (c_name),
    constraint ck_client_city check(city in ('dhaka','pabna'))
    );

    desc client;

    insert into client
    values(2015820,'Sohan','dhaka');

    select *from client;

    create table sell
    (
    sl_no number(10),
    c_id number(10),
    p_id number(10),
    quantity number(15),
    unit_price number(20),
    total_price number(20),
    constraint pk_sell_sl_no primary key(sl_no),
    constraint fk_sell_c_id foreign key(c_id) references client(c_id),
    constraint fk_sell_p_id foreign key(p_id) references product(p_id),
    constraint ck_sell_quantity check(quantity>0)
    );
    desc sell;

    insert into sell
    values(10,2015820,2015820,2000,3000,10000);

    select * from sell;

    ReplyDelete

Popular Posts