DBMS Concepts

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

Deadline

None (no explicit submission is required)

Points

None

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 (solutions):

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.