Scribd Logo
Upload

Welcome to Scribd!

  • 106 views

    Advanced Data Base Manegment: Assignment 1

    Uploaded by

    Tizazu Selemon
    This document contains an assignment to map an entity relationship (ER) schema for a ship tracking database to a relational schema. The ER schema tracks information about ships, ports, ship movements, ship types, and ship visits. The summary is: The ER schema is mapped to relational tables, with each entity becoming a table and relationships becoming foreign keys. Primary keys are specified for each table. The tables are Ship, Ship_Type, State_Country, Sea_Ocean_Lake, Port, Ship_Movement, and Visits. Foreign keys link tables like Ship to Port and Ship_Movement based on the relationships in the ER schema.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Advanced Data Base Manegment: Assignment 1

    Uploaded by

    Tizazu Selemon
    106 views5 pages
    This document contains an assignment to map an entity relationship (ER) schema for a ship tracking database to a relational schema. The ER schema tracks information about ships, ports, ship movements, ship types, and ship visits. The summary is: The ER schema is mapped to relational tables, with each entity becoming a table and relationships becoming foreign keys. Primary keys are specified for each table. The tables are Ship, Ship_Type, State_Country, Sea_Ocean_Lake, Port, Ship_Movement, and Visits. Foreign keys link tables like Ship to Port and Ship_Movement based on the relationships in the ER schema.

    Original Title

    ADMS ASS1

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains an assignment to map an entity relationship (ER) schema for a ship tracking database to a relational schema. The ER schema tracks information about ships, ports, ship movements, ship types, and ship visits. The summary is: The ER schema is mapped to relational tables, with each entity becoming a table and relationships becoming foreign keys. Primary keys are specified for each table. The tables are Ship, Ship_Type, State_Country, Sea_Ocean_Lake, Port, Ship_Movement, and Visits. Foreign keys link tables like Ship to Port and Ship_Movement based on the relationships in the ER schema.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    106 views5 pages

    Advanced Data Base Manegment: Assignment 1

    Uploaded by

    Tizazu Selemon
    This document contains an assignment to map an entity relationship (ER) schema for a ship tracking database to a relational schema. The ER schema tracks information about ships, ports, ship movements, ship types, and ship visits. The summary is: The ER schema is mapped to relational tables, with each entity becoming a table and relationships becoming foreign keys. Primary keys are specified for each table. The tables are Ship, Ship_Type, State_Country, Sea_Ocean_Lake, Port, Ship_Movement, and Visits. Foreign keys link tables like Ship to Port and Ship_Movement based on the relationships in the ER schema.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    WOLAITA SODO UNIVERSITY

    SCHOOL OF INFORMATICS
    DEPARTMENT OF INFORMATION TECHNOLOGY
    (MSC weekend PROGRAM(1st year,2021)
    ADVANCED DATA BASE MANEGMENT
    ASSIGNMENT 1

    Name Tizazu Selemon


    ID pgw/48803/13
    Sub_Date june 2021

    1/Write raw data for a diagram


    A ship voyage database keeps track of ship_ movement, port, ship type, ship visits, port
    of sea/lake/ocean, and port of state/country that visits
    1. We store each ship’s unique name and owner of a ship. The ship lands on a port
    and the port has port name. many ships can land on a single port
    2. A ship’s movement history is stored on ship_ movement. Is has latitude,
    longitude and timestamp.
    3. The port exists on a state/country. The state/country has a unique name and
    continent. A single state/country can have many number of ports
    4. A port can be on a sea, lake or ocean. This sea/lake/ocean has a unique name.
    Many ports can exist on a single sea/lake/ocean.
    5. The ship’s type is stored on ship type which has a unique type, tonnage and hull.
    Many numbers of ships can have similar ship type.
    A ship can visit a visits which contains start date and end date
    2/FIGURE
    An ER schema for a SHIP_TRACKING database shows an ER schema for a database
    that may be used to keep track of transport ships and their locations for maritime
    authorities. Map this schema into a relational schema, and specify all primary keys and
    foreign keys.
    Answer:
    Step 1: Mapping of Regular Entity Types.
    – Clearly identify the primary key and attributes for each entity defined in the
    E-R model, and ensure that it is in accordance with the rules of the
    relational model as previously discussed. Each entity, with its clearly
    identified primary key (indicated by PK), and attributes satisfying the
    previously discussed rules, becomes a table in the relational model.

    SHIP SNAM OWNER SHIP_TYPE PNAME


    E

    TYPE TONNAGE HULL SHIP_TYPE


    STATE/COUNTRY
    NAME CONTIENT
    SEA/OCEAN/LAKE SEANAME

    Step 2: Mapping of Weak Entity Types…


    • Include primary key attribute of identifying entity as foreign key attribute of R
     Primary key of R is primary key of identifying entity together with partial
    key from R
     Omit the identifying relationship when subsequently translating (other)
    relationship types to relation schemas
    DATE TIME LONGITUDE LATITUD SSNAME
    E
    SHIP_MOVEMENT

    PNAME S_C_NAME S_O_L NAME


    PORT

    Step 3: Mapping of Binary 1:1 Relation Types


    For each binary 1:1 relationship type R, identify relation schemas that correspond to
    entity types participating in R
    Step 4: Mapping of Binary 1: N Relationship Types...
    – For each regular binary 1:N relationship type R, identify a relation S that
    represents the participating entity type at the N-side of the relationship
    type.

    – Include any simple attributes of the 1:N relation type as attributes of S.

    SNAME OWNER SHIP_TYPE PNAME


    SHIP

    TYPE TONNAGE HULL SHIP_TYPE

    PORT PNAME SC_NAME S_O_LNAME

    SEA NAME

    Step 5: Mapping of Binary M: N Relationship Types.


    – Also include any simple attributes of the M:N relationship type (or simple
    components of composite attributes) as attributes of S.
    SHIP SNAME ….

    PORT PNAME ….
    VISITS
    STARTDATE ENDDA
    TE
    VISITS

    VSNAME VPNAME STARTDATE ENDDATE


    Step 6: Mapping of Multivalued attributes.

    SHIP
    SNAME OWNER TYPE PNAME

    SHIP_TYPE
    TYPE TONNAGE HULL

    STATE_COUNTRY
    NAME CONTINENT

    SEA OCEAN LAKE


    NAME

    SHIP_MOVEMENT
    SSNAME DATE TIME LONGITUDE LATITUTE

    PORT
    S_C_NAME PNAME S_O_L_NAME

    VISIT
    VSNAME VPNAME STARTDATE ENDDATE

    You might also like