Skip to main content
Engineering LibreTexts

4.E: Persistent Data Storage (Exercises)

  • Page ID
    3711
  • [ "article:topic" ]

    Foor these assignments, usemysqldump()to submit a database export file or submit written work as a single document file.

    Database Design

    Come up with a small database you would like to develop. Compile a list of fields and follow the process of normalization to create a data model with at least 5 tables that includes primary and foreign keys, and at least one table that is a many-to-many relationship.

    Create a document that depicts your table structure and fields, identifies primary and foreign keys, and the connections between your tables.

    Database Development

    Using your database design from above, create the database using your system’s command prompt. Create a text file that includes all of the commands you used to create your database as well as inserts for 3 full records in each table. Submit this file and amysqldump of your actual completed database.

    Database Interaction

    Create queries that will interact with your database to complete the following:

    1. Display one of your tables without including foreign keys
    2. Joins two (or more) of your tables to show a complete record
    3. Generates information about your records that is not stored in your database
    4. Updates record(s) in a table to a new value

    Submit your queries and themysqldump file for your database.

    Discussion / Written Response

    1. Describe the three types of relationships between data and how to satisfy them in relational databases.
    2. Describe the first three forms of normalization.
    3. Describe the differences between flat file and structured query databases.
    4. Describe the differences between SQL and NoSQL databases.
    5. Describe when the different database types we examined are most useful.

    Questions

    1. What does SQL stand for?
      a. Standard Query Linguistics
      b. Structured Query Language
      c. Strict Query Language
      d. None of the above

      Answer:
      b
    2. Which of these is used to retrieve information from a database?
      a. Get
      b. Fetch
      c. Select
      d. Find

      Answer:
      c
    3. Which of these commands would get the name column from a Persons table?
      a. Select name from Persons;
      b. Get persons.name
      c. Select persons.name
      d. Get name from Persons

      Answer:
      a
    4. Which of the following select records where the name fields starts with an A?
      a. Select * from Persons where name=’a’
      b. Select * from Persons where name like “a%”
      c. Select * from Persons where name like “%a”
      d. None of these

      Answer:
      b
      a will get names that are only a, and c will select things that end in a
    5. Which of the following is the keyword used to sort results?
      a. Order By
      b. Sort By
      c. Filter By
      d. Ascending

      Answer:
      a
    6. Which of the following is used to find the number of records?
      a. Total()
      b. Count()
      c. Sum()
      d. None of these

      Answer:
      b
    7. Eliminating multiple identical columns from a table is which normalization?
      a. First
      b. Second
      c. Third
      d. Fourth

      Answer:
      a
    8. Eliminating repeated values from a table is which normalization?
      a. First
      b. Second
      c. Third
      d. Fourth

      Answer:
      b
    9. Ensuring no values are repeated in a database is which of the following?
      a. First
      b. Second
      c. Third
      d. Fourth

      Answer:
      c
    10. Ensuring all fields relate directly to the table they are in is which normalization?
      a. First
      b. Second
      c. Third
      d. Fourth

      Answer:
      c
    11. The CRUD actions are:
      a. Compose, Read, Update, Delete
      b. Create, Read, Update, Destroy
      c. Create, Read, Update, Delete
      d. Copy, Read, Update, Drop

      Answer:
      c
    12. Including all of the records in the first table of a join is done using:
      a. Join all
      b. Left join
      c. Right join
      d. None of these

      Answer:
      b
    13. Queries can be placed inside of other queries.
      a. True
      b. False

      Answer:
      a
    14. Which of the following is used to remove a record from a table?
      a. Delete from
      b. Drop from
      c. Destroy

      Answer:
      a
    15. MySQL requires which of the following to run?
      a. Windows
      b. Linux
      c. Apple
      d. Any of these

      Answer:
      d