Four problems this week, though the last 3 are all related. The first deals with Docker containers and the last three are working with SQL constraints.

Problem 1: Building a Dock

We introduced the concept of Docker containers this week, and this problem will involve both creating and then running a Docker container. As such, if you haven’t already downloaded and gotten Docker running on your system, you will need to start with that, as was described in the lecture slides.

The first (and main) part of this problem will involve created a Docker image of your own. In the Docker_Materials folder in the starting template repository, create a file named Dockerfile. Inside that file, following the conventions that we laid out in the slides, you will need to add the necessary lines/commands to build your own custom Docker container. For this container, you will need the following (not necessarily in this exact order):

Your base image will be the “ubuntu” image, which gets you a mostly full Linux environment at only around 80 MB. The particular script you are running does not run correctly on the latest version, so you should specify the jammy tag, which is the previous Ubuntu release.
It is always a good idea to set a working directory for you to copy files into and work inside. You can follow what we showed in class and name that folder /app, or give it a different name of your own choosing.
You will need to copy over the mysterycmd file from the local folder into the working directory on the image.
This mystery command happens to require some programs and applications that are not installed by default in Ubuntu. As such, you’ll need to have the image install these programs when it is built. Ubuntu, like many flavors of Linux built on the Debian base, utilizes a package manager / installer called aptitude, which is interacted with through the use of apt-get. Before you can install anything with apt-get though, you should update it:
```
apt-get update
```
These are commands that need to be run inside the image, and so you should be prefacing them with the RUN keyword in your Dockerfile. Once the update command has been run, you need to install (in the image) the two needed programs. Here the -y flag says to automatically accept any prompts, which is necessary when building things automatically.
```
apt-get install -y figlet lolcat
```
Annoyingly, lolcat gets installed in an odd location in Ubuntu, which is not in the default PATH. As such, we need to add its location to the PATH environment variable. Setting environment variables in Dockerfiles is done with the ENV keyword, so the entire line would look like:
```
ENV PATH=/usr/games:$PATH
```
which effectively adds the directory /usr/games to the start of the PATH variable.
The image needs to be told what command to run when the container is launched. In this case, the mysterycmd script can just be launched with bash (aka bash mysterycmd). Remember though that in the command portion of the Dockerfile you give it a list of commands and options/arguments inside square brackets, where each is surrounded by double quotes.

Then you can build your image! When building your image, I’d strongly recommend adding a tag so that it has a full name + tag:

docker build -t yourname:latest .

assuming you are currently in the same folder as the Dockerfile. If all goes as planned, you should see it working through the process of building your image!

Once the image is built, launch a new container using that image. I’d strongly recommend using the -it and --rm flags so that the old container will be cleaned up for you once it terminates. When the container is run, you should see something colorful printed to your terminal screen! When you do, take a screenshot and upload it back to GitHub along with your Dockerfile to submit this problem.

If you do not see something, or if you get some sort of error, go back to your Dockerfile and investigate what you may need to change. And then remember that once you change something you will need to rebuild the Docker image before trying to run another container using that new image.

I’ve tried to provide lots of guidance and lay this process out cleanly, but if something in the above process is utterly failing or making no sense, please reach out with questions! I can’t guarantee I’ve foreseen every possible issue that could come up with everyone’s varied architecture.

Problem 2: Constraining Movies

You’d like to set up some tables to keep track of some movie scores from a variety of reviewers. As such, you have sketched out a basic design shown in the below schematic:

The file in the starting repository named Prob2.sql has the initial layout of these tables, in addition to defining a schema to place them into. Using this file as your starting point, you should add the necessary lines to create the correct columns and types for each table, including primary and foreign keys. Additionally, there are some further constraints that you would like to place on the data:

The combination of title and year in the movies table should be unique. (Only one movie with a given title should come out each year)
The name of the reviewer should not be null.
The number of stars a reviewer gives a movie should not be null.
The date that a reviewer rates a movie should not be null.
Only movies that came out after the start of the year 1900 are allowed in the movies table.
Reviewers can only give 1, 2, 3, 4, or 5 stars.
All ratings must come after the start of the year 2000.

After adding your columns and constraints, you should be able to execute everything within Prob2.sql to create your necessary tables. To test that everything has gone well, you should then be able to run all the commands in Successful_Inserts.sql, which should add all the given rows without any errors. If an error occurs here, go back to your tables and determine what may have happened and fix it. Make sure your column names and types are just as indicated in the schematic. Once everything in Successful_Inserts is inserting successfully, upload your Prob2.sql back to GitHub to submit this problem. (Though you may want to wait until the next two problems are complete to ensure you don’t need to make further changes here.)

Problem 3: Finding Flaws

Now that you have created and populated the tables from Problem 2, trying to run any of the commands in Prob3_Failures.md should result in an error. For each command below (and also shown in Prob3_Failures.md), indicate specifically what constraint caused the operation to fail and why. Use plain English here to describe the constraint: do not just regurgitate the error message that pops up! Realize that some queries may fail because of the same constraint. If any of these queries completes successfully, then you have missed a constraint somewhere back in your table creation that you need to go fix, and then regenerate and populate your tables.

INSERT INTO hw5.movies (id,title,year,director) 
VALUES
 (2,'The Graduate',1967,'Mike Nichols');

INSERT INTO hw5.movies (id,title,year,director) 
VALUES
  (51,'The Graduate',1865,'Mike Nichols');

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (4,36,5,'2010-01-01');

```
DROP TABLE hw5.reviewers;
```

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (1,3,NULL,'2015-04-02');

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (1,3,3,'1999-04-18');

INSERT INTO hw5.reviewers (id,name) 
VALUES
  (11,'Daniel Day');

Problem 4: Finding Compound Flaws

This is a continuation of what you were doing in Problem 3. Trying to run any of the commands in Prob4_Failures.md should result in an error. For each command, indicate specifically what constraint(s) caused the operation to fail and why. Use plain English here to describe the constraint: do not just regurgitate the error message that pops up! Realize that some queries may fail because of the same constraint, and some queries might have multiple constraints being violated. So don’t just assume a single thing is wrong. If any of these queries completes successfully, then you have missed a constraint somewhere back in your table creation that you need to go fix, and then regenerate and populate your tables.

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (1,3,6,'1999-04-18');

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (1,2,0,'2012-02-18');

```
DROP TABLE hw5.movies;
```

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (4,3,-2,'2014-01-03');

INSERT INTO hw5.reviewers (id,name) 
VALUES
  (20,NULL);

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (2,3,4.3,'1989-10-30');

INSERT INTO hw5.ratings (reviewer_id,movie_id,stars,rating_date) 
VALUES
  (1,3,3,NULL);

INSERT INTO hw5.movies (id,title,year,director) 
VALUES
  (51,'The Graduate',1867,'Mike Nichols'),
  (52,'The Graduate',1867,'Krzysztof Kieslowski');

Sidenote: These ratings were all artificially generated for these mysterious reviewers, so if your favorite movie on the list didn’t get an appropriate score, then I apologize!