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

Expected duration: 1.5 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. Name some entities and their attributes in a university environment. (Question 3.2)

    Student
    given name, surname, birthday, class year, major
    Department
    name
    Course
    name, description, department
    Offering
    course, instructor, term, period, maximum enrollment
    Grade
    course offering, grade
    Faculty
    given name, surname, birthday, department
    Degree
    type (e.g., BA or BS), department

  2. Name the four basic operations on stored data. In what important way is one in particular different from the other three? (Question 3.7)

    The CRUD acronym is often used to describe these operations:

    • Create (or insert)
    • Read (or retrieve)
    • Update
    • Delete

    Reading (or retrieving) data does not modify it, unlike the other operations.

  3. Give an example of and describe an application that would require sequential access in a university environment. (Question 3.9)

    Listing all the students who have attended the university.

  4. Give an example of and describe an application that would require direct access in a university environment. (Question 3.10)

    Changing the grade of a single student after the end of the semester (e.g., in cases where an “incomplete” was assigned due to extraordinary circumstances).

  5. What are the two kinds of data redundancy, and what are the three types of problems that they cause in the information systems environment? (Question 3.13)

    Data redundancy may occur across multiple files or within a single file. For example, two separate applications might be used to track student grades, such as a web-accessible learning management system (LMS) and a proprietary historical database used by the university. Data redundancy within a single file occurs when records contain attributes (specifically, non-key attributes) of multiple entities. For example, including the department’s name with course information because the department’s name will be repeated for each course offered by that department.

    The problems that redundant information cause are threefold:

    • Wasted storage space due to the duplicated data
    • Longer update times because all copies of the data must be updated
    • Data integrity problems when all copies of the data aren’t correctly updated

  6. What are the two defining goals of a database management system? (Question 3.18)

    Achieving data integration without data redundancy.

  7. What are the four major DBMS approaches? Which approaches are used the most and least today? (Question 3.25)

    The four major approaches (historically, not necessarily true today) are

    • hierarchical,
    • network,
    • relational, and
    • object oriented.

    Relational databases are most used today. Navigational approaches (i.e., hierarchical and network databases) used in cases of high data volume and a requirement for very fast response times. Object-oriented databases primarily being used for niche applications.

  8. Answer the following questions based on the following Happy Cruise Lines’ data. (Minicase 3.1)

    Ship Number Ship Name Year Built Weight (Tons)
    005 Sea Joy 1999 80000
    009 Ocean IV 2003 75000
    012 Prince Al 2004 90000
    020 Queen Shirley 1999 80000
    Sailor Number Sailor Name Ship Number Home Country Job Title
    00536 John Smith 009 USA Purser
    00732 Ling Chang 012 China Engineer
    06988 Maria Gonzalez 020 Mexico Purser
    16490 Prashant Kumar 005 India Navigator
    18535 Alan Jones 009 UK Cruise Director
    20254 Jane Adams 012 USA Captain
    23981 Rene Lopez 020 Philippines Captain
    27467 Fred Jones 020 UK Waiter
    27941 Alain DuMont 009 France Captain
    28184 Susan Moore 009 Canada Wine Steward
    31775 James Collins 012 USA Waiter
    32856 Sarah McLachlan 012 Ireland Cabin Steward
    1. Regarding the Happy Cruise Lines Member file…

      1. Describe the file’s record type.

        A record consisting of a sailor number, sailor name, ship number, home country, and job title

      2. Show a record occurrence.

        00536 John Smith 009 USA Purser

      3. Describe the set or range of values that the Ship Number field can take.

        Any integer (but presumably one that appears in the “Ship Number” field of the “Ship” table, currently 005, 009, 012, and 020)

      4. Describe the set or range of values that the Home Country field can take.

        The names of all the countries in the world

    2. Assume that the record of the Crew Member file are physically stored in the order shown.

      1. Retrieve all the records of the file physically sequentially.

        00536 John Smith 009 USA Purser
        00732 Ling Chang 012 China Engineer
        06988 Maria Gonzalez 020 Mexico Purser
        16490 Prashant Kumar 005 India Navigator
        18535 Alan Jones 009 UK Cruise Director
        20254 Jane Adams 012 USA Captain
        23981 Rene Lopez 020 Philippines Captain
        27467 Fred Jones 020 UK Waiter
        27941 Alain DuMont 009 France Captain
        28184 Susan Moore 009 Canada Wine Steward
        31775 James Collins 012 USA Waiter
        32856 Sarah McLachlan 012 Ireland Cabin Steward

      2. Retrieve all the records of the file logically sequentially based on the Sailor Name field.

        27941 Alain DuMont 009 France Captain
        18535 Alan Jones 009 UK Cruise Director
        27467 Fred Jones 020 UK Waiter
        31775 James Collins 012 USA Waiter
        20254 Jane Adams 012 USA Captain
        00536 John Smith 009 USA Purser
        00732 Ling Chang 012 China Engineer
        06988 Maria Gonzalez 020 Mexico Purser
        16490 Prashant Kumar 005 India Navigator
        23981 Rene Lopez 020 Philippines Captain
        28184 Susan Moore 009 Canada Wine Steward
        32856 Sarah McLachlan 012 Ireland Cabin Steward

      3. Retrieve all the records of the file logically sequentially based on the Sailor Number field.

        00536 John Smith 009 USA Purser
        00732 Ling Chang 012 China Engineer
        06988 Maria Gonzalez 020 Mexico Purser
        16490 Prashant Kumar 005 India Navigator
        18535 Alan Jones 009 UK Cruise Director
        20254 Jane Adams 012 USA Captain
        23981 Rene Lopez 020 Philippines Captain
        27467 Fred Jones 020 UK Waiter
        27941 Alain DuMont 009 France Captain
        28184 Susan Moore 009 Canada Wine Steward
        31775 James Collins 012 USA Waiter
        32856 Sarah McLachlan 012 Ireland Cabin Steward

      4. Retrieve all the records of the file logically sequentially based on the Ship Number field.

        16490 Prashant Kumar 005 India Navigator
        00536 John Smith 009 USA Purser
        18535 Alan Jones 009 UK Cruise Director
        27941 Alain DuMont 009 France Captain
        28184 Susan Moore 009 Canada Wine Steward
        00732 Ling Chang 012 China Engineer
        20254 Jane Adams 012 USA Captain
        31775 James Collins 012 USA Waiter
        32856 Sarah McLachlan 012 Ireland Cabin Steward
        06988 Maria Gonzalez 020 Mexico Purser
        23981 Rene Lopez 020 Philippines Captain
        27467 Fred Jones 020 UK Waiter

      5. Perform a direct retrieval of the records with a Sailor Number field value of 27467.

        27467 Fred Jones 020 UK Waiter

      6. Perform a direct retrieval of the records with a Ship Number field value of 020.

        06988 Maria Gonzalez 020 Mexico Purser
        23981 Rene Lopez 020 Philippines Captain
        27467 Fred Jones 020 UK Waiter

      7. Perform a direct retrieval of the records with a Job Title field value of Captain.

        20254 Jane Adams 012 USA Captain
        23981 Rene Lopez 020 Philippines Captain
        27941 Alain DuMont 009 France Captain

    3. The value 009 appears as a ship number once in the Ship file and four times in the Crew Member file. Does this constitute data redundancy? Explain.

      No, the ship number associates crew members with the ship on which they work. Removing this information from the Crew Member file would remove this (one-to-many) relationship.

    4. Merge the Ship and Crew Member files based on the common ship number field (in a manner similar to Figure 3.8 for the General Hardware database).

      Sailor Number Sailor Name Ship Number Home Country Job Title Ship Name Year Built Weight (Tons)
      00536 John Smith 009 USA Purser Ocean IV 2003 75000
      00732 Ling Chang 012 China Engineer Prince Al 2004 90000
      06988 Maria Gonzalez 020 Mexico Purser Queen Shirley 1999 80000
      16490 Prashant Kumar 005 India Navigator Sea Joy 1999 80000
      18535 Alan Jones 009 UK Cruise Director Ocean IV 2003 75000
      20254 Jane Adams 012 USA Captain Prince Al 2004 90000
      23981 Rene Lopez 020 Philippines Captain Queen Shirley 1999 80000
      27467 Fred Jones 020 UK Waiter Queen Shirley 1999 80000
      27941 Alain DuMont 009 France Captain Ocean IV 2003 75000
      28184 Susan Moore 009 Canada Wine Steward Ocean IV 2003 75000
      31775 James Collins 012 USA Waiter Prince Al 2004 90000
      32856 Sarah McLachlan 012 Ireland Cabin Steward Prince Al 2004 90000

      Is the merged file an improvement over the two separate files in terms of…

      1. Data redundancy? Explain.

        No, many attribute values are repeated. For example, the name of the ship “Prince Al” is stored 4 times for ship number 012.

      2. Data integration? Explain.

        Yes, identifying the ship name for a particular sailor does not require accessing another other files.

    5. Explain why the Ship Number field is in the Crew Member file.

      The Ship Number field associates each crew member with a particular ship. It expresses a one-to-many relationship between a ship and its crew.

    6. Explain why ship number 012 appears four times in the Crew Member file.

      The Crew Member file contains records about four crew members of that ship (the Prince Al)

    7. How many files must be accessed to find…

      1. The year that ship number 012 was built?

        One, the Ship file

      2. The home country of sailor number 27941?

        One, the Crew Member file

      3. The name of the ship on which sailor number 18535 is employed?

        Two, first retrieve the Ship Number from the Crew Member file followed by the Ship Name from the Ship file

    8. Describe the procedure for finding the weight of the ship on which sailor number 00536 is employed.

      Retrieve the record for Sailor 00536 from the Crew Member file and retrieve the Ship Number (which is 009). Retrieve the record for Ship Number 009 from the Ship file and retrieve the weight from that record.

    9. What is the mechanism for recording the one-to-many relationship between crew members and ships in the Happy Cruise Lines database above?

      The Ship Number field of the Crew Member file.