Web Project Assignment 1

1. Writing the SQL DDL for the database.
2. Creating an ER diagram for the database.
3. Writing a set of queries that will be used for querying the database.

Creating the Database

Using the relations described in the project description and the sample data provided in the sampleData.sql file, create a file named "abet.sql" that creates the tables corresponding to those relations. Use the mySql database that was created for you earlier in the course Your create table commands should provide declarations for the attributes, primary, and foreign keys. Here are some additional details:

1. There are a few instances where you can use an enumerated type for an attribute and you should do so if possible (the write-up explicitly identifies one attribute that must be enumerated but there is at least one other).
2. It is not necessary to define any other type of constraints.
3. Every relation must have a primary key declaration.
4. You must use the exact relation names and attribute names that I used since we will be using automated, script grading of your programs. I strongly recommend adding additional test data to my sample.sql file that I have provided you since we will be doing so as well. Do not put any test data in your abet.sql file. Instead put it in sample.sql.
5. Use DROP TABLE IF EXISTS, not DROP TABLE, for deleting tables.
6. A foreign key has to depend on an attribute that is declared as a primary key in another relation.

Creating the ER Diagram

Create an ER diagram for the database. You should be able to create the ER diagram from the relations that you have been provided and their corresponding description in the project description. Please include relationship multiplicities for binary relationships in your ER diagram and also include any compound or multi-valued attributes that are shown as relations in the project write-up (remember that a relation may represent an entity, relationship, or compound/multi-valued attribute). You do not have to provide multiplicities for ternary relationships.

Writing Queries

Please write the following queries and put each one in a separate file named q1.sql, q2.sql, q3.sql, and so on. Your web interface will need to use each of these queries at some point to obtain information that it must display. Only print the fields requested. If I did not request a field, then do not print it! You may have to use SELECT DISTINCT to remove duplicate tuples from your solution.

1. Print the instructorId, sectionId, courseId, major, semester, and year of all sections taught by an instructor with an email address of "coyote@utk.edu" and password of "password" (the password string in the sample data you have been given is the hash string for "password" that is created by MySQL's PASSWORD function). Note that the same sectionId could appear twice in the results because that section might be used to assess both EE and CpE majors or both CS and CpE majors. The results should be ordered by year in descending order and secondarily by semester in ascending order. You cannot use OutcomeResults to satisfy this query because you will be using this query to locate the sections that an instructor needs to enter outcome results for. If the instructor has not yet entered any results for this section, then there will not be any tuples in OutcomeResults for the section and your query will fail to return the appropriate set of sections. If you are tempted to use OutcomeResults for this query, then you need to take a longer look at some of the other relations in the database.
2. Print the outcomeIds and outcomeDescriptions of all outcomes assessed for CS majors by sectionId 3. Order the output by outcomeIds. As with query 1, OutcomeResults is not an appropriate relation to use to satisfy this query.
3. Print all outcome results for CS majors for outcome 2 that were assessed by sectionId 3. Print the performance description (e.g., Meets Expectations) and number of students that achieved that performance description. Order the results by performanceLevel. Note that you are printing the performance description, not the performanceLevel, but you are ordering the results by the performance Level.
4. Print all assessment plans for CS majors for outcome 2 that were assessed by sectionId 3. Print the assessment description and weight. Order the results by weight in descending order and secondarily by assessment description in ascending order.
5. Print the narrative summary for CS majors for outcome 2 that were assessed by sectionId 3. Print the strengths, weaknesses, and actions.
6. Print the sectionId, instructor email, outcomeId, major, and sum of the weight fields (name it weightTotal) for any outcome whose assessments' weights for that outcome and major do not exactly equal 100. As one example, the sum of the assessment weights for EE majors for outcome 1 in section 7 is 70, so you would print the requested information for this section. Order the results by instructor email in ascending order, then by major in ascending order, and finally by outcome in ascending order.

What To Hand In

You should upload the following items as a zip file to Canvas:

1. abet.sql: Your file for creating your tables. We do not want your sample.sql file as we will be using our own test data.
2. ER.pdf: Your ER diagram as a pdf file. This can be a scanned file or a file created using a computer-aided drawing program but it must be presentable by a pdf viewer.
3. q1.sql - q6.sql: The 6 sql queries you write.
4. If you are working with someone, please make only one submission and list the name of the other person in the submission comments.