Web Project Assignment 1
Database Design and Implementation
In this assignment you are going to going to set up the database that you
will be using for your web project. This will involve:
- Writing the SQL DDL for the database.
- Creating an ER diagram for the database.
- 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:
- 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 can
be enumerated but there are at least two others).
- It is not necessary to define
any other type of constraints.
- Every relation must have a primary key declaration.
- 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.
- Use DROP TABLE IF EXISTS, not DROP TABLE, for deleting tables.
- 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 in your ER diagram and 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).
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!
- 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.
- 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.
- 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.
- 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.
- Print the narrative summary for CS majors for outcome 2 that
were assessed by sectionId 3. Print the strengths, weaknesses, and
actions.
- 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:
- 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.
- 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.
- q1.sql - q6.sql: The 6 sql queries you write.