CIT 111: Introduction to Databases

Team Activity: CRUD in Practice

Instructions

Art Website Case Study

As a team read through the following case study

Case Study

CASE STUDY - A look at how a web-based system might use a database and how insert, updates, and delete statements might be a part of the system whenever a new entity needs to be added, edited or deleted from the database.

Go to https://art.byuiwebdev.com and play around with the simple system and small database it uses. This should look familiar. It contains the same structure we were working with to design the art database last week.

Try some of the keywords in a subject search and see what happens. This system is something the public would see and maybe explore as a virtual museum.

Now let's look at the backend of the system. The backend would be something that the owner of the museum might use. This is where administration could add a new artist or painting. Or maybe they need to edit or delete an existing artist or artwork. This is not something that would be accessible to the public. A login would be required to only allow access to those who should be able to get into the system since these commands could make some big changes to the database.

What is super cool, is this admin doesn't need to know SQL additions, edits or deletions to the database. The SQL is all coded into the program. In fact, in one form that the admin fills out, 3 different entities' data is being inserted. The artwork, keyword and linking table. This all happens behind the scenes in the code and the owner never has to worry about SQL syntax or adding data to a linking table like we do.

New artwork screenshot
New art item screen.

The following SQL statements look very different because they are combined with a backend language. In this case it's the language PHP. Don't worry if you don't understand this code. The point is just to show how the SQL is integrated into the PHP code here.

New artwork SQL INSERT example 1
SQL INSERT example 1
New artwork SQL INSERT example 2
SQL INSERT example 1
New artwork SQL INSERT example 3
SQL INSERT example 1

When the admin needs to update a piece of artwork. The same type of form can be used to run an update statement.

Artwork UPDATE example
SQL UPDATE example

When the admin might want to delete a piece of artwork there are also safeguards in place double checking that they really want to do this.

Artwork delete screenshot
Art item delete screen
Artwork DELETE SQL example
SQL DELETE example

Not all end users of a database system will know SQL, but somewhere in the code before the actual database is changed, you will see SQL in action.

Identify needed SQL statements?

As a team spend the remainder of the hour identifying at least 4 other SQL statements that will be needed for this site. Ignore the program code portion just focus on the SQL. Create a text document that shows:

  1. Purpose of statement (For example adds a new user to the system.
  2. The type of SQL statement (SELECT, INSERT, DELETE, UPDATE etc.) needed.
  3. The information needed to construct the SQL statement (For example, artists last name, artists first name
  4. Return data needed if any (columns to return).

You will need this information for Saturday's assignment.

Submission

Useful Links: