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:
- What is or are not a benefit(s) of a view within the JC Consulting database?
- Which fields from the Projects table is or are not a good candidate to index?
- Which security features is or are not available to the JC Consulting database given it is an Access database?
- In the Employees table, how could you ensure that no HireDate values were entered prior to 1/1/2019?
- 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?
- Write a SQL command that adds a new field named Region to the Clients table.
- What feature could you use to create system information about the tables and fields in the JC Consulting database?
- Where do you create data macros in the JC Consulting Access database?
- 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:
- 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
- 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?
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.
Part 2 Q2: Why is there the error message - a simple answer would suffice.
Top comments (0)