For these assignments, use mysqldump() 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 a mysqldump 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 the mysqldump 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?
   1. Standard Query Linguistics
   2. Structured Query Language
   3. Strict Query Language
   4. None of the above
2. Which of these is used to retrieve information from a database?
   1. Get
   2. Fetch
   3. Select
   4. Find
3. Which of these commands would get the name column from a Persons table?
   1. Select name from Persons;
   2. Get persons.name
   3. Select persons.name
   4. Get name from Persons
4. Which of the following select records where the name fields starts with an A?
   1. Select * from Persons where name=‘a’
   2. Select * from Persons where name like “a%”
   3. Select * from Persons where name like “%a”
   4. None of these
5. Which of the following is the keyword used to sort results?
   1. Order By
   2. Sort By
   3. Filter By
   4. Ascending
6. Which of the following is used to find the number of records?
   1. Total()
   2. Count()
   3. Sum()
   4. None of these
7. Eliminating multiple identical columns from a table is which normalization?
   1. First
   2. Second
   3. Third
   4. Fourth
8. Eliminating repeated values from a table is which normalization?
   1. First
   2. Second
   3. Third
   4. Fourth
9. Ensuring no values are repeated in a database is which of the following?
   1. First
   2. Second
   3. Third
   4. Fourth
10. Ensuring all fields relate directly to the table they are in is which normalization?
    1. First
    2. Second
    3. Third
    4. Fourth
11. The CRUD actions are:
    1. Compose, Read, Update, Delete
    2. Create, Read, Update, Destroy
    3. Create, Read, Update, Delete
    4. Copy, Read, Update, Drop
12. Including all of the records in the first table of a join is done using:
    1. Join all
    2. Left join
    3. Right join
    4. None of these
13. Queries can be placed inside of other queries.
    1. True
    2. False
14. Which of the following is used to remove a record from a table?
    1. Delete from
    2. Drop from
    3. Destroy
15. MySQL requires which of the following to run?
    1. Windows
    2. Linux
    3. Apple
    4. Any of these