HW#4 --- last modified November 02 2023 16:12:25.

Solution set.

Due date: Nov 15

Files to be submitted:
  Hw4.zip

Purpose: To gain experience with high-level to relational model mappings, to create transactions for a application, and to practice with relational algebra, datalog, and SQL queries.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO4 -- Use SQL as a data definition language (DDL) to create and alter databases, tables, views, and indexes

CLO5 -- Use SQL as a data manipulation language (DML) for querying and modifying databases

CLO6 -- Define and use constraints and triggers in SQL

CLO7 -- Describe the concept of transactions

Specification:

1. Take your solution to Problem 4 from the last HW. Recall for that problem you made an E/R diagram either for a website honor.com or hinder.com. Use the E/R to Relational model from class to map your diagram to the relational model. Then map the relational model to SQL and create table statements suitable for a Mysql database. Save the Mysql statements in project.sql file.
2. Often applications built making use of databases are developed with some variant of the Model View Controller design pattern such as MVA or MVVC. In these set-ups, a Model class, or its subclasses, encapsulate all communication between the application program and the database. Different Model methods are responsible for different common storage operations. For your application come up with a list of common tasks that might be handled by a Model class such as add and remove user, adding or removing helping or hindering events, etc. Describe these methods in Hw4.pdf file then write the SQL statements needed to support them. The SQL should work for a MySQL database.
3. Redraw the figures 4.13 and 4.33 out of the book as UML diagrams then map them to relations using the UML to relational algorithm from class.
4. Exercise 5.2.1 (b), (c), (h), (l), where the tuple (4,3) has been added to the relation R.
5. Do exercise 2.4.3 (c) (d) from the book but express your queries in datalog rather than the relational algebra.
6. Rework Exercise 5.2.1 (Problem 4) above, but this time using SQL. Specifically,
   1. Put code in SQL to create tables of that problem in create.sql and to populate the tables as per that problem in insert.sql
   2. Put code to compute the queries for (b), (c), (h), (l) part in files b.sql, c.sql, h.sql, and l.sql, respectively.

   Your submission should be in a zip file that contains a Hw4 folder. Add all the above files (Hw4.pdf, .sql files, readme.txt, etc.) to the Hw4 folder. This will allow the auto-evaluation tests to run.

Grading Rubric