Results 1 to 4 of 4

Thread: Urgent Help!

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Urgent Help!

    hi this is my first time in here so am goin to need all the necessary help.

    i have created my logical relational schema as below.

    Customer{customer_id, customer_fname, customer_lname, customer_address, customer tel no, customer_type*}

    Customer category{customer type, discount}//private =0, silver = 1.5, gold = 2.5, diamond = 4.5 //a customer has to be in one of the four categories. silver, gold and diamond customers are said to be business customers

    category{category_id, category_name}

    brand(brand id, brand name}

    supplier{supplier id, supplier name, supplier address}

    equipment{equipment id, category id*, brand id*, supplier id*, price, delivery time}

    Transaction{transaction_id, equipment_id, customer_id, return_date, cancellation_reason, service_type//refund or replacement}

    .................................................. .................................................. ......

    i need to display the following applications from my schema

    1. For a given particular equipment, show current stock and current items on hire with expected return dates. the name of the equipment should be captured at run-time from the user.

    2.for a particular business customer, show the current items on hire with expected return dates plus any previous complaints that were made by that customer that involved a replacement or refund.

    3.produce, for a given category, the total number of equipmets(i.e. items) under that category available in stock and the number currently on hire to customers.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have two questions for you:

    1. what have you tried?

    2. when is this homework assignment due?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Hi, sorry if i took o much time off the board, i was sent out to do some reseaerch work in a rural area but regardless to answer your question

    2. Wen is the deadline: the assignment is due on the 22nd of this month

    1. What have i tried: for question

    1. First off i have to admit i forgot to include the Equiment_name attribute in the Equipment table.

    select e.equipment id, t.return date
    from transaction t, equipment e
    where e.equipment_name = equipments//

    my solution shows me all the equipments that are in the transaction table i.e that are on hire, meaning it is wrong.

    2.SELECT t.Equipment_id, t.Return_date, t.Cancellation_Reason, t.Support
    FROM [Transaction] AS t, Customer AS c, Membership AS m
    WHERE t.Customer_id=c.Customer_id And m.Discount>1;

    Here its the same problem, and to make it worse, am not given an option of entering the business customer i want, and the last line(m.discount) which i put in so that the private customer is not included is not also working.

    3. with the third query,, am stuck......................

    any help.............................................. ..

  4. #4
    Join Date
    Oct 2012
    Posts
    1
    Did you ever get an answer to question 3? i now have the same coursework and i'm suck with question 3

Posting Permissions

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