CS-115: Database Assignment

Preliminaries

Learn to use a database program effectively to organize and manipulate data.

OpenOffice is going to be used for this assignment. It is free. It is Open Source.
Download OpenOffice for your operating system and install it. You may also have to download the java runtime environment. runtime environment.

Windows Environment.

MACOS Environment.

If you are working in MACOS, you may get a message that you cannot run the application because the source of the application is unknown (I am not sure of the exact wording of the message). If you get this message, you will have to change your security settings to allow the application to run.

1. Click on the apple at the upper left.
2. Select System Settings (Preferences if older version).
3. Select Privacy and Security.
4. Scroll down to Security.
5. Select the option to allow appications from the app store and identified developers

You can change the setting back after completing the assignment.

Goals

1. Learn to define a database.
2. Learn to define tables within a database and enter data into a table.
3. Learn to create queries.

To Do

Americans have become diet-crazed. There are many many diets from which to choose: low fat, low calorie, low carb, etc. We are going to create a table of nutritional information. Given the table, you can sort on a given criteria to order foods or query foods that meet certain criteria.

The name of your table will be nutritional. The fields in the table should be:
1. Food Name
2. Serving Quantity
3. Serving Units
4. Number of Calories
5. Grams of Carbs
6. Grams of Fat.

1. Create a database called Nutritional.

In Open Office,


Select database

Click Next

Set Do Not Register and click finish

Specify the name Nutritional and the path to where you want the database file created.
Specify Create Table in Design View

2. Create a table called nutritional

Define the following fields in the table:

To select the field type, click on the arrow for the drop-down menu.

1. Primary Key. Remember, tables need a field that is guaranteed to be unique so that records can be identified. The first field is going to be your primary key. Type the name PK into the field name. The data type of the field should be integer.

   At the bottom of the screen under field properties, select Auto.
   
   

   Right click in the square next to the field name to make it the Primary Key.

2. Food Name. This should be a text field.
3. Serving Quantity. The data type of this field should be a Float.
4. Serving Units. This should be a text field.
5. Number of Calories. The data type of this field should an Integer.
6. Grams of Carbohydrates. The data type of this field should be an Integer.
7. Grams of Fat. The data type of this field should be an Integer.

After entering all of the fields, you are going to close the table by clicking on the x in the upper left corner of the table. You will be asked if you want to
save the table, click yes. You will then be prompted for a table name, specify nutritional.

Your table has been created in the database.

3. Enter data into nutritional

Double Click on the table name to open it to enter data,

Notice the ID field. It is the primary key that the database will guarantee to be a unique field in each record entered.

In order to be able to uniquely identify students, we will enter a dummy record with your name as the food. Under category, specify person and under food name specify your name. Enter the values specified below for the remaining fields. Tab through each field to enter data.

Enter the following data into the nutritional table..

After entering all of the data, click on x to close the table. Save the changes.

4. Generate queries.

Click on Add to add the Nutritional Table to the Query then click close.

The Tables appear in the top portion of the query window (we only have Nutritional). The query is constructed in the bottom portion of the screen. The bottom portion of the screen will initially be empty. Double click on each field name in the top portion to move them into the query that is going to be constructed.

In the bottom portion of the screen on the line labeled criterion is where the query criteria is entered.

1. All records such that the number of calories, grams of carbohydrates, and grams of fat all equal 0.

   For a record to be selected, all three fields must be equal to 0.
   

   To run the query, click edit and select run query. The results will appear in the top portion of the window.
   

   Click on the x to close, answer yes to save, and the name should be query1.

2. All records such that the number of carbohydrates is greater than 20.

   Repeat the procedures above. Only one field is involved in this query.
   

   Run the query.
   

   Click on the x to close, answer yes to save, and the name should be query2.

3. All records such that the number of calories is <= 100

   Save the query as Query3.

4. All records such that the number of grams of fat is greater than 5.

   Save the query as Query4.

5. All records such that the number of grams of fat < 5.

   Save the query as Query5.

6. All records such that the number of calories is > 100.

   Save the query as Query6.

Be sure to save each query. The saved queries should be named by the order of the queries listed: query1, query2, query3, etc.

Submission.

1. Check the accuracy of your data and your queries.
2. Be sure to close the database.
3. Submit your database file via D2L.

Last Modified 3/7/2023

This page is copyright protected © by Barbara Bracken