This assignment is ungraded and should not be submitted. The questions review key concepts from the reading for this module.

Expected duration
2.5–3 hours

Help Policy

Authorized Resources
Any
Notes
You may jointly work on this assignment with classmates

Assignment

Answer the following questions from Fundamentals of Database Management Systems (Gillenson, 2011).

  1. Why was the commercial introduction of relational database delayed during the 1970s? What factors encouraged its introduction in the early 1980s? (Question 5.1)

    The use of logical links to associate related data incurs significant hardware and software overhead compared to navigational approaches that use physical links (e.g., pointers). In short, data integration (i.e., a relational join) is inherently slower. In addition, companies were investing significantly in navigational approaches in the 1970s, and relational approaches were counter this investment.

    In the 1980s, interest grew in alternatives to simplify data models, which where too complex to use by non-professionals, and navigational databases were too large to store on personal computers. In addition, hardware advances and the development of new algorithms decreased the overheads of relational joins.

  2. What are the characteristics of a candidate key? (Question 5.5)

    A candidate key [is a group of attributes that] uniquely identifies the entities in a relation.

  3. What is a primary key? What is an alternate key? (Question 5.6)

    A relation always has a primary key, which is a candidate key selected on the basis of the expected use of the database. For example, US tax returns may be identified by individuals’ Social Security numbers (SSNs) rather than, say, an individual’s name, birthday, and place of birth.

    An alternative key is a candidate key that was not selected to be the relation’s primary key.

  4. Define the term “foreign key.” (Question 5.7)

    A foreign key is an attribute (or group of attributes) that is a candidate key of one relation and also appears in another relation (where it is termed a foreign key).

  5. Describe why an additional relation is needed to represent a many-to-many relationship in a relational database. (Question 5.9)

    An additional relation is required because the alternative (i.e., recording the relationship as an attribute in one of the relations that participates in the relationship) is a violation of First Normal Form, which requires that all attributes be atomic, not multi-valued. Such a design also results in variable-length records, which precludes direct access, and increases the risk of update anomalies.

  6. The main relation of a motor vehicle registration bureau’s relational database includes the following attributes:
    • Vehicle Identification Number
    • License Plate Number
    • Owner Serial Number
    • Manufacturer
    • Model
    • Year
    • Color

    The Vehicle Identification Number is a unique number assigned to the car when it is manufactured. The License Plate Number is, in effect, a unique number assigned to the car by the government when it is registered. The Owner Serial Number is a unique identifier of each owner. Each owner can own more than one vehicle. The other attributes are not unique. What is/are the candidate key(s) of this relation? If there is more than one candidate key, choose one as the primary key and indicate which is/are the alternate key(s). (Exercise 5.1)

    The candidate keys are as follows:

    • Vehicle Identification Number (VIN)
    • License Plate Number

    It seems most logical to use the license plate number and owner serial number as the primary key because the license plate number is much shorter than the VIN and more easily accessible (e.g., by a highway patrol officer).

  7. Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A “cruise” is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2009, is a cruise.

    SHIP
    Ship Number, Ship Name, Ship Builder, Launch Date, Gross Weight
    CRUISE
    Cruise Number, Start Date, End Date, Cruise Director, Ship Number
    PORT
    Port Name, Country, Number of Docks, Port Manager
    VISIT
    Cruise Number, Port Name, Country, Arrival Date, Departure Date
    PASSENGER
    Passenger Number, Passenger Name, Social Security Number, Home Address, Telephone Number
    VOYAGE
    Passenger Number, Cruise Number, Stateroom Number, Fare

    Note the following facts about this environment.

    • Both ship number and ship name are unique in the SHIP Relation.
    • A ship goes on many cruises over time. A cruise is associated with a single ship.
    • A port is identified by the combination of port name and country.
    • As indicated by the VISIT Relation, a cruise includes visits to several ports, and a port is typically included in several cruises.
    • Both Passenger Number and Social Security Number are unique in the PASSENGER Relation. A particular person has a single Passenger Number that is used for all of the cruises that she takes.
    • The VOYAGE Relation indicates that a person can take many cruises and a cruise, of course, has many passengers.

    Answer the following (Minicase 5.1):

    1. Identify the candidate keys of each relation.

      SHIP
      Ship Number
      Ship Name
      CRUISE
      Cruise Number
      Ship Number, Start Date (not included in the “official” answer, but a ship presumably can only be on one cruise at a time)
      PORT
      Port Name, Country
      VISIT
      Cruise Number, Port Name, Country
      PASSENGER
      Passenger Number
      Social Security Number
      VOYAGE
      Passenger Number, Cruise Number

    2. Identify the primary key and any alternate keys of each relation.

      The primary key of each relation follows:

      SHIP
      Ship Number (because this attribute appears as a foreign key in the CRUISE relation)
      CRUISE
      Cruise Number (because this attribute appears as a foreign key in the VISIT and VOYAGE relations)
      PORT
      Port Name, Country
      VISIT
      Cruise Number, Port Name, Country
      PASSENGER
      Passenger Number (because this attribute appears as a foreign key in the VOYAGE relation)
      VOYAGE
      Passenger Number, Cruise Number

      All other candidate keys are alternate keys.

    3. How many foreign keys does each relation have?

      SHIP
      0 foreign keys
      CRUISE
      1 foreign key – Ship Number
      PORT
      0 foreign keys
      VISIT
      2 foreign keys – Cruise Number and Port Name and Country (the latter being a foreign key that comprises two attributes)
      PASSENGER
      0 foreign keys
      VOYAGE
      2 foreign keys – Passenger Number and Cruise Number

    4. Identify the foreign keys of each relation.

      See prior response.

    5. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?

      The VISIT relation uses the Cruise Number and Port Name and Country as its primary key. The VOYAGE relation uses the Passenger Number and Cruise Number as its primary key. In both instances, combining the foreign keys is required to uniquely identify the tuples in the relation created to express a many-to-many relationship.

    6. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.

      The VISIT and VOYAGE relations express many-to-many relationships between the CRUISE and PORT relations and the CRUISE and PASSENGER relations respectively. Intersection data for each is as follows:

      VISIT
      Arrival Date, Departure Date
      VOYAGE
      Stateroom Number, Fare

      See prior responses for the primary keys of these relations.

    7. Using the informal relational command language described in this chapter, write commands to:

      Note: The following answer include not only the “informal” style presented in this chapter but also formalizes it using the relational algebra.

      1. Retrieve the record for passenger number 473942.

        Select rows from the PASSENGER relation where the Passenger Number is 473942.

        \[\sigma_{\text{Passenger Number} = 473942}(\text{PASSENGER})\]

      2. Retrieve the record for the port of Nassau in the Bahamas.

        Select rows from the PORT relation where the Port Name is Nassau and the Country is the Bahamas.

        \[\sigma_{\text{Port Name} = \text{Nassau} \wedge \text{Country} = \text{Bahamas}}(\text{PORT})\]

      3. List all of the ships built by General Shipbuilding, Inc.

        Select rows from the SHIP relation where the Ship Builder is General Shipbuilding, Inc.

        \[\sigma_{\text{Ship Builder} = \text{General Shipbuilding, Inc.}} (\text{SHIP})\]

      4. List the port name and number of docks of every port in Mexico.

        Select rows from the PORT relation where the Country is Mexico. Project the Port Name and Number of Docks over that result.

        \[\pi_{\text{Port Name}, \text{Number of Docks}}( \sigma_{\text{Country} = \text{Mexico}}(\text{PORT}))\]

      5. List the name and number of every ship.

        Project the Ship Name and Ship Number over the SHIP relation.

        \[\pi_{\text{Ship Name}, \text{Ship Number}}(\text{SHIP})\]

      6. Who was the cruise director on cruise number 38232?

        Select rows from the CRUISE relation where the Cruise Number is 38232, and project the Cruise Director over that result.

        \[\pi_{\text{Cruise Director}}( \sigma_{\text{Cruise Number} = 38232}(\text{CRUISE}))\]

      7. What was the gross weight of the ship used for cruise number 39482?

        Join the SHIP and CRUISE relations using the Ship Number. Select rows from that result where the Cruise Number is 39482. Project the Gross Weight of that result.

        \[\pi_{\text{Gross Weight}}(\sigma_{\text{Cruise Number} = 39482}( \text{SHIP} \bowtie \text{CRUISE}))\]

      8. List the home address of every passenger on cruise number 17543.

        Join the PASSENGER and VOYAGE relations. Select rows from that result where the Cruise Number is 17543. Project the Home Address of that result.

        \[\pi_{\text{Home Address}}(\sigma_{\text{Cruise Number} = 17543}( \text{PASSENGER} \bowtie \text{VOYAGE}))\]

  8. Describe the concept of the unary many-to-many relationship. (Question 6.3)

    A unary many-to-many relationship is a many-to-many relationship with one entity set – e.g., relating components that are assembled together to create a larger system.

  9. How is a unary many-to-many relationship constructed in a relational database? (Question 6.4)

    Exactly like a binary many-to-many relationship – using a separate relation that has two foreign keys, both to the primary key of original relation.

  10. Describe the problem of referential integrity. (Question 6.8)

    Referential integrity ensures that a database’s logical links among tuples (i.e., foreign keys) are valid. Without referential integrity, the database could be left in an inconsistent state as a result of inserting, updating, or deleting data.

  11. Compare and contrast the three delete rules: restrict, cascade, and set-to-null. (Question 6.9)

    Restrict precludes any change that violates referential integrity (thus requiring the referenced data to be modified first). Cascade causes changes to “ripple” through the database; for example, modifying a tuple’s primary key will cause any references to it (by foreign keys) to also be updated. Set-to-null is similar to cascade except that references are set to NULL rather than updated (or deleted entirely).

  12. Leslie’s Auto Sales has a relational database with which it maintains data on its salespersons, its customers, and the automobiles it sells. Each of these three entity types has a unique attribute identifier. The attributes that it stores are as follows:
    1. Salesperson Number (unique), Salesperson Name, Salesperson Telephone, Years with Company
    2. Customer Number (unique), Customer Name, Customer Address, Value of Last Purchase From Us
    3. Vehicle Identification Number (unique), Manufacturer, Model, Year, Sticker Price

    Leslie’s also wants to keep track of which salesperson sold which car to which customer, including the date of the sale and the negotiated price. Construct a relational database for Leslie’s Auto Sales. (Exercise 6.1)

    Salesperson
    number,* name, telephone, years with company
    Customer
    number,* name, address, value of last purchase
    Vehicle
    VIN,* manufacturer, model, year, sticker price
    Sale
    salesperson,* customer,* VIN,* date, negotiated price
  13. Happy Cruise Lines (Minicase 6.1)

    1. Look at the Happy Cruise Lines database (Minicase 5.1) but, for this question, consider only the SHIP, PORT, and PASSENGER relations. The company wants to keep track of which passengers visited which ports on which ships on which dates. Reconstruct these three relations as necessary and/or add additional relation(s) as necessary to store this information.

      This problem describes a ternary relationship among the PASSENGER, PORT, and SHIP relations.

      SHIP
      Ship Number,* Ship Name, Ship Builder, Launch Date, Gross Weight
      PORT
      Port Name,* Country,* Number of Docks, Port Manager
      PASSENGER
      Passenger Number,* Passenger Name, Social Security Number, Home Address, Telephone Number
      VISIT
      Passenger Number,* Port Name,* Country,* Ship Number,* Date*

      For the VISIT relation, the date must be part of the primary key or else passengers would not be permitted to visit the same port more than once.

      Note: If one assumes that passengers always visit ports of call on their cruise, then technically the prior design with the VISIT and VOYAGE relations captures this relationship, albeit not directly as a ternary relationship.

    2. Consider the following data from the SHIP and CRUISE relations of the Happy Cruise Lines database:

      Ship Number Ship Name Ship Builder Launch Date Gross Weight
      005 Sea Joy Jones 1999 80000
      009 Ocean IV Ajax 2003 75000
      012 Prince Al Ajax 2004 90000
      020 Queen Shirley Master 1999 80000
      Cruise Number Start Date End Date Cruise Director Ship Number
      21644 2002-07-05 2002-07-12 Smith 009
      23007 2002-08-14 2002-08-24 Chen 020
      24288 2003-03-28 2003-04-04 Smith 009
      26964 2003-07-01 2003-07-11 Gomez 020
      27045 2003-07-15 2003-07-22 Adams 012
      28532 2003-08-17 2003-08-24 Adams 012
      29191 2003-12-20 2003-12-27 Jones 009
      29890 2004-01-15 2004-01-22 Levin 020

      What would happen if:

      1. The delete rule between the SHIP and CRUISE relations is restrict and an attempt is made to delete the record for ship number 012 in the SHIP relation?

        Deleting the record is not allowed due to the references in the CRUISE relation.

      2. The delete rule between the SHIP and CRUISE relations is restrict and an attempt is made to delete the record for ship number 005 in the SHIP relation?

        The record is deleted (because there are no records in the CRUISE relation that reference ship number 005).

      3. The delete rule between the SHIP and CRUISE relations is cascade and an attempt is made to delete the record for ship number 012 in the SHIP relation?

        The Prince Al is deleted from the SHIP relation and cruises 27045 and 28532 are deleted, too.

      4. The delete rule between the SHIP and CRUISE relations is cascade and an attempt is made to delete the record for ship number 005 in the SHIP relation?

        The Prince Al is deleted from the SHIP relation.

      5. The delete rule between the SHIP and CRUISE relations is set-to-null and an attempt is made to delete the record for ship number 012 in the SHIP relation?

        The Prince Al is deleted from the SHIP relation and the ship number is set to NULL for cruises 27045 and 28532. That is, the records in the CRUISE relation are updated as follows:

        Cruise Number Start Date End Date Cruise Director Ship Number
        27045 2003-07-15 2003-07-22 Adams NULL
        28532 2003-08-17 2003-08-24 Adams NULL

      6. The delete rule between the SHIP and CRUISE relations is set-to-null and an attempt is made to delete the record for ship number 005 in the SHIP relation?

        The Prince Al is removed from the SHIP relation.

      7. The delete rule between the SHIP and CRUISE relations is restrict and an attempt is made to delete the record for cruise number 26964 in the CRUISE relation?

        Cruise number 26964 is deleted.

      8. The delete rule between the SHIP and CRUISE relations is cascade and an attempt is made to delete the record for cruise number 26964 in the CRUISE relation?

        Cruise number 26964 is deleted.

      9. The delete rule between the SHIP and CRUISE relations is set-to-null and an attempt is made to delete the record for cruise number 26964 in the CRUISE relation?

        Cruise number 26964 is deleted.