1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql for Oracle

Discussion in 'General' started by yellow1, Mar 3, 2012.

  1. yellow1

    yellow1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Can anyone please help me with this question? I will be really grateful. I am a naive when it comes to Oracle. And I need to get it done. I have no idea of it. Please help.

    James Morgan owns and operates Morgan Importing, which purchases antiques and home furnishings in
    Asia, ships those items to a warehouse facility in Los Angeles, and then sells these items in the United
    States. James tracks the Asian purchases and subsequent shipments of these items to Los Angeles by
    using a database to keep a list of items purchased, shipments of the purchased items, and the items in
    each shipment. His database includes the following tables:
     ITEM (ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
     SHIPMENT (ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
     SHIPMENT_ITEM (ShipmentID, ShipmentItemID, ItemID, Value, SpecialInformation)
    In the database schema above, the primary keys are underlined and the foreign keys are shown in
    italics.


    Problem 1 (10 points)
    Based on the above schema, create an Entity-Relationship Diagram.

    Problem 2 (20 points)
    Create a database resulting from the Entity-Relationship Diagram. Assume appropriate data types based
    on the best information that you have from reading their names. Be sure to enforce referential integrity
    in your design.

    Problem 3 (20 points)
    Modify the scripts you created in Problem 2 to enforce the following constraints on your database.
    a. SpecialInformation should default to the value ‘N’.
    b. SpecialInformation should be one of the following values (‘N’,’F’,’L’,’H’) where N stands for
    Nothing, F stands for Fragile, L stands for Liquid, H stands for Hazardous. Note: you don’t need
    to do anything with what these abbreviations stand for.
    c. Value must be a number greater than 0.
    d. Insured Value must be a number greater than 1000.

    Problem 4 (20 points)
    Create procedures for the following:a. Show the ShipperName and DepartureDate of all shipments that have an item with a value of
    $1,000 or more. Present results sorted by ShipperName in ascending order and then departure
    date in descending order.
    b. Show the ShipperName, Departure Date of Shipment, and Value for items that were purchased
    in Singapore. Present results sorted by ShipperName in ascending order and then
    DepartureDate in descending order
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi yellow1. I am sorry but this isn't a very good way of asking for help. You can't just post questions assigned to you and ask someone else to solve them for you. I would encourage you to read some PL/SQL basics and attempt the questions. Then please post your code here and we can review and help you where you get stuck. All the best.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I agree with Sadik, this is not the way to ask for assistance. Also, your post does not provide enough information for anyone to build the schema (primary keys and foreign keys are not revealed -- the italics and underliining did not post). Do you understand what a primary key is and what it does? Possibly a definition of the term is in order; a primary key is a unique constraint on a table that must not contain NULL values and must be unique for each row in a table. This constraint is easy to declare:

    alter table yartz add constraint yartz_pk primary key(smelbo);

    The statement above makes the SMELBO column of the YARTZ table NOT NULL and enforces uniqueness through a unique index.

    It's also possible that you don't understand the concept of a foreign key; a foreign key is a column or set of columns in a table (known as the child table) that match the primary key of the source, or parent, table. Such a constraint ensures that no 'orphan' records can enter the child table thus guaranteeing what is known as referential integrity (no unreferenced records exist in the child table). For example we have a table named wert, and it will be a child table to yartz. Ideally it will also have a column named smelbo (let us presume that it does). Setting up the foreign key would be done in this manner:

    alter table wert add constraint foreign key(smelbo) references yartz(smelbo);

    Now there cannot be any records in wert that have a smelbo value which is not found in yartz -- Oracle will not allow it to happen.

    As to the other items in your homework you will need to speak with your instructor regarding how to define the other constraints listed. Also you'll need to get assistance, from your instructor, on the coding items in your list (the PL/SQL procedures).

    We are not here to be a substitute for your instructor; we will assist you with your assignments when we see you have put forth sufficient effort in solving them but fall short of the mark.
     
  4. yellow1

    yellow1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks both. I have done most of the first few questions. As I was doing the question, I come across another issue. Here is the question:
    Database includes the following tables:
    • ITEM: (ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
    • SHIPMENT: (ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
    • SHIPMENT_ITEM: (ShipmentID, ShipmentItemID, ItemID, Value, SpecialInformation)
    In the database schema above, the primary keys are underlined (yellow color) and the foreign keys are shown in italics (red color). For example, for table ITEM, the primary key is ItemID, for table SHIPMENT, the primary key is ShiptmentID and for SHIPMENT_ITEM table, the primary key is ShipmentID and Shipment ID and the foreign key is ItemID and ShipmentID.

    The question asked me to :
    Show the ShipperName, Departure Date of Shipment, and Value for items that were purchased
    in Singapore. Present results sorted by ShipperName in ascending order and then DepartureDate in descending order.

    Please help me with this. My Answer (which I know is not correct as I have not selected VALUE from Item table as I do not know how to do this):
    Select ShipperName, DepartureDate
    From SHIPMENT
    Where ShipmentId IN
    (Select ShipmentId
    From SHIPMENT_ITEM
    Where itemid IN
    (Select ItemID
    From ITEM
    Where city = 'Singapore'))
    Order by DepartureDate Desc;