TyroCity

DATA 210 WK4 Assignment

In the following exercises, you will use the data in the JC Consulting database. Don’t forget to use a copy of the JC Consulting database so any changes you make will not affect future modules.

Examine the JC Consulting data and complete the following case exercises/problems:

  1. What is or are not a benefit(s) of a view within the JC Consulting database?
  2. Which fields from the Projects table is or are not a good candidate to index?
  3. Which security features is or are not available to the JC Consulting database given it is an Access database?
  4. In the Employees table, how could you ensure that no HireDate values were entered prior to 1/1/2019?
  5. To query whether the Clients table has records that do not have any matching records in the Projects table, which join statement should be used?
  6. Write a SQL command that adds a new field named Region to the Clients table.
  7. What feature could you use to create system information about the tables and fields in the JC Consulting database?
  8. Where do you create data macros in the JC Consulting Access database?
  9. According to the article referenced by the Bureau of Labor Statistics, which fundamental competency for a job in database administration is not worth knowing?

Apply key module concepts to the JC Consulting case:

  1. Using Access, open the Relationships window and identify all of the one-to-many relationships in the database as well as the field used in both tables to make the connection using the following pattern found in SQL, when connecting two tables using a WHERE or INNER JOIN clause. An example of one of the relationships is provided as a guide.

Clients.ClientID = Projects.ClientID

  1. Using Access, an employee at JC Consulting tried to delete TaskID CODE01 from the TaskMasterList table and received the following error message: “The record cannot be deleted or changed because table ‘ProjectLineItemsincludes related records.”

Why did the employee receive this error message?

Submit your answers to the Examine and Apply questions in a single Word document.

===

Quick Guide:

Q1: List the benefits of "view" for JC Consulting DB. What does it enable? Think and answer based on it.

Q2: Index refers to primary, foreign key. From the projects table - what is and what is not the index?

index identification: projects table

Q3: This is a MSAccess DB - so what security limitations does it have? Go general.

Q4: You can do this in either MySQL (trigger to make it no new entries are with that detail) or Access (with criteria)

Q5: Review the relationships of the table - you simply need to answer what type of JOIN is to be used and why (or write the query itself)

Q6: SQL query - general.

Q7: General question, no review of data itself required.

Q8: Macro creation - one of your group presented about it in the class itself. Does not ask you to write a macro itself but rather "where do you create" - as think smart.

Q9: Refer to post here: https://www.bls.gov/ooh/computer-and-information-technology/database-administrators.htm#tab-1 or any other as you see fit. Navigate across different tabs for further info.

Part 2 Q1: Below is the relation chat - one of the answer is already there in question list other such answers.

JC Consulting DB relationship

Part 2 Q2: Why is there the error message - a simple answer would suffice.

Latest comments (0)