AIST6410 TechPrac01 Specs

1. 20 pts - Questions from Chapter 2 of Data Management by Richard Watson

   Download the Word document WatsonCh02Quiz which contains questions taken from Chapter 2 of Data Management (Watson).  Answer the questions in the downloaded document and attach your completed copy of the Word document to this assignment in D2L.

2. 35 pts - Querying a Single Entity

   Ref: Instructor video: SQL queries of a single entity | Demo SQL

   Review Watson Exercise 2 from Chapter 3 (p. 74 in the pdf). Using the guidelines below, create SQL queries which meet the requirements stated in the exercise. Your deliverables are the SQL statements which produce the results; no need to turn in the results themselves. Since SQL is a standard, the two files submitted can (and should) be very similar, maybe identical (i.e., no need to re-invent the wheel for another SQL DBMS flavor).

   1. Using SQL Server

      In the course remote desktop for the course there is a SQL Server database named AIST6410 with a table named share containing sample share data (the data from the textbook augmented by other records). Begin with the SQL file WatsonCh03Exer02.sql and develop SQL which provides the results required by each part of the exercise below the query requirement statement. Save the completed SQL file as Ch03Ex02SqlSrv.sql, leaving a copy in your Documents folder on the course VM and also attaching a copy to this assignment in D2L

   2. Using MySQL

      In the course remote desktop for the course there is a MySQL database named aist6410 with a table named share containing sample share data (the data from the textbook augmented by other records). Begin with the SQL file WatsonCh03Ex02.sql and develop SQL which provides the results required by each part of the exercise below the query requirement statement. Save the completed SQL file as Ch03Ex02MySQL.sql, leaving a copy in your Documents folder on the course VM and also attaching a copy to this assignment in D2L

3. 35 pts - Modeling One-to-Many Relationships

   Ref: Web article Crow's Foot Notation

   Ref: Instructor video: Visio Crow's Foot Diagrams

   Review Watson Exercise 1 from Chapter 4 (p. 91 of the pdf) but complete the exercise using the following guidelines.

   Create a Visio document named WatsonCh04Ex01.vsdx in the course remote desktop (Visio is one of the tools available). Use Visio to prepare Crow's Feet diagram data models for each of the five situations (1a thru 1e) in the exercise, putting the diagrams on separate pages labeled 1a thru 1e. Use an attribute named {entity}_id as the primary key (identifier) for each entity; for example, the farmer entity in 1a should have farmer_id as the primary key. For entities where the exercise does not provide clear guidance on non-key attributes, use one attribute {entity}_data to stand in for data attributes about the entity; for example, the farmer entity in 1a should have farmer_data as the non-key attribute. Any foreign key attributes should use the same name as the primary key they relate to.

   Be sure your diagrams show both minimum^* and maximum cardinality for the relationships among entities and adjust Visio shapes so all of the relationship endpoints are 'jumbo' in size (hard to see them otherwise). Place a copy of your completed WatsonCh04Ex01.vsdx in your Documents folder in the course VM and, in addition, attach a copy to this assignment in D2L.

   ^*Minimum cardinalities are not explicitly provided by the relationship descriptions in the exercise. You thus have some latitude in applying your judgment defining them yourself. Any reasonable interpretation answer will be acceptable grade-wise; just be prepared to defend your choice if asked.

4. 35 pts - Querying One-to-Many Relationships

   Ref: Instructor video: SQL queries involving one-to-many relationships | Demo notes

   Review Watson Exercise 8 from Chapter 4 (p. 92 of the pdf) but complete the exercise using the following guidelines. In the database, a 'person' is a salesperson who may have sold zero or many cars and a 'car' may have zero or one salespersons. A car without a salesperson will have a null carsell attribute; otherwise, this attribute is a foreign key for the personid of the salesperson making the sale. Your deliverables are the SQL statements which produce the results; no need to turn in the results themselves. Since SQL is a standard, the two files submitted can (and should) be very similar, maybe identical (i.e., no need to re-invent the wheel for another SQL DBMS flavor).

   1. Using SQL Server

      In the course remote desktop for the course there is a SQL Server database named AIST6410 with a tables named person and car containing sample data. Complete the exercise by downloading the SQL file WatsonCh04Exer08.sql, opening it as a query in SQL Server Management Studio and developing SQL which provides the results required by each part of the exercise below the query requirement statement; note that some of the queries have been taken from the exercise in the text but other queries have been added. Save the completed sql file as Ch04Ex08SqlSrv.sql, leaving a copy in your Documents folder on the course VM and also attaching a copy to this assignment in D2L

   2. Using MySQL

      In the course remote desktop for the course there is a MySQL database named aist6410 with s named person and car containing sample data. Complete the exercise by downloading the SQL file WatsonCh04Exer08.sql, opening it as a query in MySQL Workbench and developing SQL which provides the results required by each part of the exercise below the query requirement statement; note that some of the queries have been taken from the exercise in the text but other queries have been added. Save the completed sql file as Ch04Ex08MySQL.sql, leaving a copy in your Documents folder on the course VM and also attaching a copy to this assignment in D2L