Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2008
    Posts
    29

    Unanswered: Bookstore database

    Hi am making a database for a bookstore as my project...am a little stuck ..could anyone have a look at my tables I have problem with my last 8 tables
    my foreign keys are not working

    Code:
    /* create the tables and constraints
    constraints are illustrated with multiple variations
    */
    
    /*
    cretae publishers pick list
    */
    drop table publishers;
    CREATE TABLE Publishers (
    	publisher_name varchar2 (50) primary KEY
    );
    /*
    create authors pick list
    */
    drop table authors;
    CREATE TABLE Authors (
    	author_name varchar2 (50) primary key 
    );
    /*
    create table of sources of books
    */
    drop table sources;
    CREATE TABLE Sources (
    	source_numb int not null,
    	source_name varchar2 (55) ,
    	source_street varchar2 (55) ,
    	source_city varchar2 (55) ,
    	source_state char (2) ,
    	source_zip char(5) ,
            source_phone char (12) NULL,
            primary key (source_numb)				
    ) ;
    /*
    create the customers table
    */
    drop table customers;
    CREATE TABLE Customers (
    	customer_numb int  primary key,
    	customer_first_name varchar2 (15) NULL ,
    	customer_last_name varchar2 (25) NULL ,
    	customer_street varchar2 (55) NULL ,
    	customer_city varchar2 (55) NULL ,
    	customer_state char (2) NULL ,
    	customer_zip varchar2 (5) NULL ,
    	customer_phone varchar2 (12) NULL ,
    	customer_email varchar2 (55) NULL,
                 referred_by int NULL
    ) ;
    /*
    create books table with FK's to sources, authors and publishers
    */
    drop table books;
    CREATE TABLE Books(
    	isbn varchar2(20) NOT NULL,
    	author_name varchar2 (50)  NULL, -- disallow null values
    	title varchar2 (255) NULL,
    	publisher_name varchar2 (50) NULL,
    	publication_year int NULL,
    	book_binding varchar2 (55) NULL,
    	source_numb int NULL,
    	retail_price decimal(8,2) NULL,
    	number_on_hand int NULL
    );
    /*
    add primary key constraint via alter statement
    */
    ALTER TABLE Books
                  ADD CONSTRAINT PK_Books
                  PRIMARY KEY  (isbn);
    /*
    create books discontinued table
    */
    drop table books_discontinued;
    CREATE TABLE Books_discontinued(
    	isbn varchar2(20) NOT NULL,
    	author_name varchar2 (50)  NULL, -- disallow null values
    	title varchar2 (255) NULL,
    	publisher_name varchar2 (50) NULL,
    	publication_year int NULL,
    	book_binding varchar2 (55) NULL,
    	source_numb int NULL,
    	retail_price decimal(8,2) NULL,
    	number_on_hand int NULL
    );
    /*
    add primary key constraint via alter statement
    */
    ALTER TABLE Books_discontinued
                  ADD CONSTRAINT PK_Books_discontinued
                  PRIMARY KEY  (isbn);
    
    /*
    create orders table
    */
    drop table Orders;
    CREATE TABLE Orders (
    	order_numb int NOT NULL ,
    	customer_numb int NOT NULL ,
    	order_date date NULL ,
    	credit_card_numb varchar2 (50) NULL ,
    	order_filled varchar2 (3) NULL,
    	credit_card_exp_date date NULL 
    ) ;
    
    
    ALTER TABLE Orders
                   ADD CONSTRAINT PK_Orders
                   PRIMARY KEY (order_numb) ;
    /*
    create orderlines child table with one or more rows for each order
    */
    drop table Orderlines;
    CREATE TABLE Orderlines (
    	order_numb int NOT NULL ,
    	isbn varchar2 (20) NOT NULL ,
    	quantity int NULL ,
    	cost_each decimal(8,2)  NULL ,
    	cost_line decimal(8,2) NULL ,
    	shipped char (1) NULL
    ) ;
    /*
    create a composite(more than one column) PK on orderlines table
    */
    ALTER TABLE Orderlines
                  ADD CONSTRAINT PK_Orderlines
                  PRIMARY KEY (order_numb,isbn) ;
    /*===============================*/
    
    drop table shipment_method;
    
    create table shipment_method
    (shipment_method_id int not null
     );
    
    ALTER TABLE shipment_method
                  ADD CONSTRAINT PK_shipment_method
                  PRIMARY KEY (shipment_method_id) ;
    
    /*
    create shipment table
    */
    drop table shipment;
    CREATE TABLE shipment (
    	shipment_id int NOT NULL ,
    	shipment_method_id 	int not null
    ) ;
    
    ALTER TABLE shipment
                  ADD CONSTRAINT PK_shipment
                  PRIMARY KEY (shipment_id);
    
    
    
    drop table federal_express;
    
    create table federal_express
    ( fe_price int,
    fe_id int not null,
    fe_shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE federal_express
                  ADD CONSTRAINT PK_federal_express
                  PRIMARY KEY (fe_id) ;
    
    
    drop table us_portal_service;
    
    create table us_portal_service
    ( us_price varchar(20),
    us_shipment_number int not null,
    us_shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE us_portal_service
                  ADD CONSTRAINT PK_us_portal_service
                  PRIMARY KEY (us_shipment_number);
    
    
    
    drop table registered_post;
    
    create table registered_post
    ( registered_price varchar(20),
    shipment_number int not null,
    shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE registered_post
                  ADD CONSTRAINT PK_registered_post
                  PRIMARY KEY (shipment_number);
    
    
    
    drop table payment_method;
    
    create table payment_method
    (payment_method_id	int not null
    );
    
    
    ALTER TABLE payment_method
                  ADD CONSTRAINT PK_payment_method
                  PRIMARY KEY (payment_method_id) ;
    
    
    
    
    drop table payment;
    
    create table payment
    (Payment_id  int not null,
    payment_company varchar(25),
    payment_username varchar (30),
    payment_password varchar(30),
    payment_method_id	int);
    
    ALTER TABLE payment
                  ADD CONSTRAINT PK_payment
                  PRIMARY KEY (payment_id) ;
    
    
    drop table bank_transfer;
    
    create table bank_transfer
    (bank_transfer_id int not null,
    bank_transfer_transaction_no varchar(20),
    bank_transfer_account varchar (25),
    payment_method_id	int
    );
    
    ALTER TABLE bank_transfer
                  ADD CONSTRAINT PK_bank_transfer
                  PRIMARY KEY (bank_transfer_id) ;
    
    
    drop table credit_card;
    
    create table credit_card
    (credit_card_id 		int not null,
    credit_card_type		varchar(20),
    credit_card_number		varchar(18),
    credit_card_ccv			varchar(3),
    credit_card_expired_date	date,
    payment_method_id		int
    );
    
    ALTER TABLE credit_card
                  ADD CONSTRAINT PK_credit_card
                  PRIMARY KEY (credit_card_id) ;
    
    /*=================================*/

  2. #2
    Join Date
    Sep 2008
    Posts
    29

    Foreign Key problem

    Hi am making a database for a bookstore as my project...am a little stuck ..could anyone have a look at my tables I have problem with my last 8 tables
    my foreign keys are not working

    Code:
    /* create the tables and constraints
    constraints are illustrated with multiple variations
    */
    
    /*
    cretae publishers pick list
    */
    drop table publishers;
    CREATE TABLE Publishers (
    	publisher_name varchar2 (50) primary KEY
    );
    /*
    create authors pick list
    */
    drop table authors;
    CREATE TABLE Authors (
    	author_name varchar2 (50) primary key 
    );
    /*
    create table of sources of books
    */
    drop table sources;
    CREATE TABLE Sources (
    	source_numb int not null,
    	source_name varchar2 (55) ,
    	source_street varchar2 (55) ,
    	source_city varchar2 (55) ,
    	source_state char (2) ,
    	source_zip char(5) ,
            source_phone char (12) NULL,
            primary key (source_numb)				
    ) ;
    /*
    create the customers table
    */
    drop table customers;
    CREATE TABLE Customers (
    	customer_numb int  primary key,
    	customer_first_name varchar2 (15) NULL ,
    	customer_last_name varchar2 (25) NULL ,
    	customer_street varchar2 (55) NULL ,
    	customer_city varchar2 (55) NULL ,
    	customer_state char (2) NULL ,
    	customer_zip varchar2 (5) NULL ,
    	customer_phone varchar2 (12) NULL ,
    	customer_email varchar2 (55) NULL,
                 referred_by int NULL
    ) ;
    /*
    create books table with FK's to sources, authors and publishers
    */
    drop table books;
    CREATE TABLE Books(
    	isbn varchar2(20) NOT NULL,
    	author_name varchar2 (50)  NULL, -- disallow null values
    	title varchar2 (255) NULL,
    	publisher_name varchar2 (50) NULL,
    	publication_year int NULL,
    	book_binding varchar2 (55) NULL,
    	source_numb int NULL,
    	retail_price decimal(8,2) NULL,
    	number_on_hand int NULL
    );
    /*
    add primary key constraint via alter statement
    */
    ALTER TABLE Books
                  ADD CONSTRAINT PK_Books
                  PRIMARY KEY  (isbn);
    /*
    create books discontinued table
    */
    drop table books_discontinued;
    CREATE TABLE Books_discontinued(
    	isbn varchar2(20) NOT NULL,
    	author_name varchar2 (50)  NULL, -- disallow null values
    	title varchar2 (255) NULL,
    	publisher_name varchar2 (50) NULL,
    	publication_year int NULL,
    	book_binding varchar2 (55) NULL,
    	source_numb int NULL,
    	retail_price decimal(8,2) NULL,
    	number_on_hand int NULL
    );
    /*
    add primary key constraint via alter statement
    */
    ALTER TABLE Books_discontinued
                  ADD CONSTRAINT PK_Books_discontinued
                  PRIMARY KEY  (isbn);
    
    /*
    create orders table
    */
    drop table Orders;
    CREATE TABLE Orders (
    	order_numb int NOT NULL ,
    	customer_numb int NOT NULL ,
    	order_date date NULL ,
    	credit_card_numb varchar2 (50) NULL ,
    	order_filled varchar2 (3) NULL,
    	credit_card_exp_date date NULL 
    ) ;
    
    
    ALTER TABLE Orders
                   ADD CONSTRAINT PK_Orders
                   PRIMARY KEY (order_numb) ;
    /*
    create orderlines child table with one or more rows for each order
    */
    drop table Orderlines;
    CREATE TABLE Orderlines (
    	order_numb int NOT NULL ,
    	isbn varchar2 (20) NOT NULL ,
    	quantity int NULL ,
    	cost_each decimal(8,2)  NULL ,
    	cost_line decimal(8,2) NULL ,
    	shipped char (1) NULL
    ) ;
    /*
    create a composite(more than one column) PK on orderlines table
    */
    ALTER TABLE Orderlines
                  ADD CONSTRAINT PK_Orderlines
                  PRIMARY KEY (order_numb,isbn) ;
    /*===============================*/
    
    drop table shipment_method;
    
    create table shipment_method
    (shipment_method_id int not null
     );
    
    ALTER TABLE shipment_method
                  ADD CONSTRAINT PK_shipment_method
                  PRIMARY KEY (shipment_method_id) ;
    
    /*
    create shipment table
    */
    drop table shipment;
    CREATE TABLE shipment (
    	shipment_id int NOT NULL ,
    	shipment_method_id 	int not null
    ) ;
    
    ALTER TABLE shipment
                  ADD CONSTRAINT PK_shipment
                  PRIMARY KEY (shipment_id);
    
    
    
    drop table federal_express;
    
    create table federal_express
    ( fe_price int,
    fe_id int not null,
    fe_shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE federal_express
                  ADD CONSTRAINT PK_federal_express
                  PRIMARY KEY (fe_id) ;
    
    
    drop table us_portal_service;
    
    create table us_portal_service
    ( us_price varchar(20),
    us_shipment_number int not null,
    us_shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE us_portal_service
                  ADD CONSTRAINT PK_us_portal_service
                  PRIMARY KEY (us_shipment_number);
    
    
    
    drop table registered_post;
    
    create table registered_post
    ( registered_price varchar(20),
    shipment_number int not null,
    shipment_state varchar(20),
    shipment_method_id int
    );
    
    ALTER TABLE registered_post
                  ADD CONSTRAINT PK_registered_post
                  PRIMARY KEY (shipment_number);
    
    
    
    drop table payment_method;
    
    create table payment_method
    (payment_method_id	int not null
    );
    
    
    ALTER TABLE payment_method
                  ADD CONSTRAINT PK_payment_method
                  PRIMARY KEY (payment_method_id) ;
    
    
    
    
    drop table payment;
    
    create table payment
    (Payment_id  int not null,
    payment_company varchar(25),
    payment_username varchar (30),
    payment_password varchar(30),
    payment_method_id	int);
    
    ALTER TABLE payment
                  ADD CONSTRAINT PK_payment
                  PRIMARY KEY (payment_id) ;
    
    
    drop table bank_transfer;
    
    create table bank_transfer
    (bank_transfer_id int not null,
    bank_transfer_transaction_no varchar(20),
    bank_transfer_account varchar (25),
    payment_method_id	int
    );
    
    ALTER TABLE bank_transfer
                  ADD CONSTRAINT PK_bank_transfer
                  PRIMARY KEY (bank_transfer_id) ;
    
    
    drop table credit_card;
    
    create table credit_card
    (credit_card_id 		int not null,
    credit_card_type		varchar(20),
    credit_card_number		varchar(18),
    credit_card_ccv			varchar(3),
    credit_card_expired_date	date,
    payment_method_id		int
    );
    
    ALTER TABLE credit_card
                  ADD CONSTRAINT PK_credit_card
                  PRIMARY KEY (credit_card_id) ;
    
    /*=================================*/
    
    
    

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the problem you are having is?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2008
    Posts
    29
    My foreign key is not working

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    which foreign key(s)
    what error message(s)
    what db

    are you really creating primary keys on varchar 50?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    according to:-
    International Standard Book Number - Wikipedia, the free encyclopedia
    The International Standard Book Number (ISBN) is a unique[1][2] numeric commercial book identifier based upon the 9-digit Standard Book Numbering (SBN) code created by Gordon Foster, Emeritus Professor of Statistics at Trinity College, Dublin,[3] for the booksellers and stationers WHSmith and others in 1965.[4]
    currently the ISBN number is 13 DIGITS
    so you shoudl not need to use varchar for the ISBN

    you could be a smart aleck and leave more space for the ISBN to grow(it has grown twice in the last 40 years or so
    depending on how real world this application is you may also need to allow for 10 digit ISBN's which are from 1970 on
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2008
    Posts
    29
    I have highlighted which tables am having problem with its in red.....those tables are the ones with problem....am not sure exactly what you are asking for....My foreign keys in those 8 tables are not working....if i remove it and create the table without the foreign key it works well....but thats not what i need....

    its really a real world application per say...but ok sure I'll change the ISBN number...thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you must be getting an error message...
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whatever else this is, it aint valid MySQL
    there is no way you could have created the preceedign tables, just havign a problem with the DDL in red
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    telephone numbers should be more than 12 characters, at least 13, possibly 15
    Ive yet to see a credit card number of upto 50 characters
    Im assuming costline is the quantity sold * the cost_each... if so there is no need to store it in orderlines

    for a yes/no value consider using the boolean datatype

    why you think you need an id aswell as the credit card, isn't the credit car number it self unique?
    having waded through your DDL I can see no sign of FOREIGN KEY decalratiosn at all, primary keys yes, but foreign keys no
    I suspect you problem may well be down to trying to use a varchar for a primary key. its not a design style Id use myself, and I can't remember if its invalid
    Last edited by healdem; 05-20-14 at 15:23.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id strongly suggest you find a standard/namign convention and stick to it
    either use CamelCase or all lowercase_with_underscore
    be cosnistent in your column names
    eg No, number
    tables names in plural
    dont refer to the table name in the column if its int he same name
    eg
    don't call the authors name author_name in authors..its just clunky
    authors.authorname
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK Ive waded through your DDL
    aside from pushing the priamry key definition intot he table (removing the need to 'ALTER TABLE' after creation on single column primary keys everything works fine, as expected. however Im not using varchar2 at all, which is an Oracle construct
    no am I using varchar datatype on columns which are primary keys

    I also added
    Code:
    drop table if exists <tablename>;
    prior to an attempt to create the table
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Sep 2008
    Posts
    29
    ok got it ...thank you

Posting Permissions

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