Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    Unanswered: First SQL*Plus project for class


    I'm taking RDBMS I and I just got done my first project. Basically it's for a mail-order database. Any suggestions or comments would be appreciated.

    Thanks in advance,

    -- Drop tables and any constraints that exist within tables
    drop table zipcodes cascade constraints;
    drop table odetails cascade constraints;
    drop table orders cascade constraints;
    drop table customers cascade constraints;
    drop table parts cascade constraints;
    drop table employees cascade constraints;

    create table employees (
    eno varchar2(4),
    ename varchar2(25) constraint employee_ename_nn not null,
    zip char(5) constraint employee_zip_nn not null,
    hdate date,
    constraint employee_eno_pk primary key(eno));

    -- insert values and rows into table
    insert into employees values ('1000','Jones','67226','12-DEC-1995');
    insert into employees values ('1001','Smith','60606','01-JAN-1992');
    insert into employees values ('1002','Brown','50302','01-SEP-1994');

    create table parts (
    pno varchar2(5),
    pname varchar2(35) constraint parts_pname_nn not null,
    QOH number(3) constraint parts_qoh_nn not null,
    price number(6,2)constraint parts_number_nn not null,
    -- should not use oracle reserved words when naming tables or columns
    "LEVEL" number(3)constraint parts_LEVEL_nn not null,
    constraint parts_pno_pk primary key(pno));

    -- insert values and rows into table
    insert into parts values ('10506','Land Before Time I',200,19.99,20);
    insert into parts values ('10507','Land Before Time II',156,19.99,20);
    insert into parts values ('10508','Land Before Time III',190,19.99,20);
    insert into parts values ('10509','Land Before Time IV',60,19.99,20);
    insert into parts values ('10601','Sleeping Beauty',300,24.99,20);
    insert into parts values ('10701','When Harry Met Sally',120,19.99,30);
    insert into parts values ('10800','Dirty Harry',140,14.99,30);
    insert into parts values ('10900','Dr. Zhivago',100,24.99,30);

    create table customers (
    cno varchar2(5),
    cname varchar2(25) constraint customers_cname_nn not null,
    street varchar2(25) constraint customers_street_nn not null,
    zip char(5) constraint customers_zip_nn not null,
    phone char(12) constraint customers_phone_nn not null,
    constraint customers_cno_pk primary key(cno));

    -- insert values and rows into table
    insert into customers values ('1111','Charles','123 Main St.','67226','316-636-5555');
    insert into customers values ('2222','Bertram','237 Ash Avenue','67226','316-689-5555');
    insert into customers values ('3333','Barbara','111 Inwood St.','60606','316-111-1234');

    create table orders (
    ono varchar2(4),
    cno varchar2(5) constraint orders_cno_nn not null,
    eno varchar2(5) constraint orders_eno_nn not null,
    received date constraint orders_received_nn not null,
    shipped date,
    constraint orders_ono_pk primary key(ono),
    constraint orders_cno_fk foreign key(cno) references customers(cno),
    constraint orders_eno_fk foreign key(eno) references employees(eno),
    constraint orders_shipped_ck check(shipped >= received));

    -- insert values and rows into table
    insert into orders values ('1020','1111','1000','10-DEC-94','12-DEC-94');
    insert into orders values ('1021','1111','1000','12-JAN-95','15-JAN-95');
    insert into orders values ('1022','2222','1001','13-FEB-95','20-FEB-95');
    insert into orders values ('1023','3333','1000','20-JUN-97',NULL);

    create table odetails (
    ono varchar2(4),
    pno varchar2(5) constraint odetails_pno_nn not null,
    qty number(3) constraint odetails_qty_nn not null,
    constraint odetails_ono_pno_pk primary key(ono,pno));

    -- insert values and rows into table
    insert into odetails values ('1020','10506',1);
    insert into odetails values ('1020','10507',1);
    insert into odetails values ('1020','10508',2);
    insert into odetails values ('1020','10509',3);
    insert into odetails values ('1021','10601',4);
    insert into odetails values ('1022','10601',1);
    insert into odetails values ('1022','10701',1);
    insert into odetails values ('1023','10800',1);
    insert into odetails values ('1023','10900',1);

    create table zipcodes (
    zip char(5),
    city varchar2(20) constraint zipcodes_city_nn not null,
    constraint zipcodes_zip_pk primary key(zip));

    -- insert values and rows into table
    insert into zipcodes values ('67226','Wichita');
    insert into zipcodes values ('60606','Fort Dodge');
    insert into zipcodes values ('50302','Kansas City');
    insert into zipcodes values ('54444','Columbia');
    insert into zipcodes values ('66002','Liberal');
    insert into zipcodes values ('61111','Fort Hays');

    -- Complete last transaction

  2. #2
    Join Date
    Oct 2003


    Sadly, i got a C- on the project.

  3. #3
    Join Date
    Oct 2003

    Class assignment

    You didn't give much specific info on the assignment so take all this with a grain of salt.

    The database definition for a mail-order company can be much more complex, but you seem to have captured many key entities. There are others, such as SHIP_METHOD (and then use a foreign key from ORDERS). You could get a little crazy and allow for partial/multiple shipments and backorders, etc. Of course, there are lots of additional columns that might be useful, but this is just an exercise, right?

    A few comments regarding the syntax and layout. You've accurately determined that you want your DROP statements in the opposite order of your CREATE statements if you are using foreign keys. You have missed one foreign key constraint (from CUSTOMERS to ZIPCODES).

    As you know, constraints can be specified in-line or out-of-line. You've used both, which is fine but can sometimes be a little confusing to read. I generally try to put all that I can in-line, but that's a matter of personal choice. (All constraints can be done out-of-line, but a few, such as multi-column primary key, unique and foreign keys have to be done out-of-line.)

    Use indenting and spacing formatting to help make the SQL readable. Align each part of the column definitions across all lines. So the datatypes will all be in a line, the constraints will all line up, etc. (You may have done this already...I've noticed that the forum drops initial spaces on a line in posts, unfortunately.

    Your ODETAILS table (I'd personally name it something like ORD_DETAILS) uses the order number and the part number as the primary key. That shouldn't cause any major problems, but most DBAs would create an ORDER_LINE column as part of the key and then carry the PNO as an attribute. Otherwise your column is going to have figure out if somebody order the same product on multiple lines and then combine them before inserting. Not a big deal...

    I personally like somewhat longer column names that are more descriptive. In a large application columns such as 'CNO' become hard to remember and potentially redundant. I'd suggest 'CUST_NO' as being more meaningful.

    Avoid using quoted column or table names (such as "LEVEL")! Oracle will require you to use the exact name, including case and quotes, to access these columns or tables in your subsequent SQL. With quotes you can create a column named 'LeVeL' as well--can be a real pain! Don't go there.

    Another common convention is to use a table prefix such as 'CU' for all columns in the CUSTOMER table: CU_CUST_NO, CU_ZIP, etc. Then you can use the same prefix on your constraints.

    Most DBAs don't bother to name their NOT NULL constraints. FYI, NOT NULL was the first constraint offered in Oracle back in version 4 (if memory serves) long before the standard constraint syntax was added in Oracle7. Hence, they are handled a little differently and don't get an ugly system-assigned constraint name. Certainly no problem at all about naming is certainly more consistent.

    Why did you include zipcode in the EMPLOYEES table? You don't have other address info. It might be part of an actual employee table but probably not to support order entry functions.

    You might want to include a STATUS column in the ORDERS table. Having a value or NULL in the SHIPPED column might cover the main status change, but there might be other status values of interest (such as BACKORDERED). An additional STATUS_VALUES reference table could be built and referenced via a foreign key.

    Do you need a termination date on your EMPLOYEES table? You will be unable to delete terminated employees since they are referenced by the foreign key in ORDERS. But you probably don't want any new orders credited to a terminated employee. (You'll need a trigger to make that check, though.)

    Another minor suggestion is to consider some other constraints (for instance you might want to use a CHECK CONSTRAINT to limit QOH to be >= 0.

    You haven't created any indexes (other than the ones automatically created by the primary key constraints). For instance it is likely that there will be a common query to retrieve all ORDERS for a specific customer so an index on ORDERS.CNO is probably going to be useful.

    To be a more complete solution, you could also define tablespaces and assign specific tables and indexes to those tablespaces and also specify storage parameters for each object.

    Well, that should give you a few things to consider. Good luck with the class

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts