I need help with this lab to complete assignment. This requires MySQL Workbench. I did step $1,$ need help with remaining steps.

Next, we will create a table in order to help us keep track of photographs. As you know, today's digital cameras can record not only a picture but the GPS location of the picture. We wish to keep this information and eventually display it on a map so we can see where our pictures are located.

We begin by naming some of the data we will keep within our database. For example we would like to keep the name and address of the user of the database, along with their password to access the database. We would also like to keep the city, state, zip code, and telephone number of the person using the database.

From the 'finalproject' schema, create a new table and label it users. The following fields should be created:

userid

name

username

address

city

state

zip

password

We must also think about the data types of each of these attributes in the users table. We can make the data types to be:

userid $-$ numeric, integer, not null, primary key, unique, auto$-$increment

name $-$ alphanumeric

username $-$ alphanumeric $(20$ characters$)$

address $-$ alphanumeric

city $-$ alphanumeric

state $-$ alphanumeric $(2$ characters$)$

zip $-$ numeric $($integer$,5$ digits$)$

password $($alphanumeric$)$

The second table we will need is the one to hold the locations for people and locations where the photos were taken. This table will be called locations. It needs to contain two items for identification and another two items for the actual location.

itemid $-$ numeric, integer, not null, auto$-$increment

type $-$ numeric, integer

description $-$ alphanumeric

lng$,$ real

lat, real

In the LOCATIONS table itemid contains either a userid or a photograph id depending what location it represents. The type refers to whether the location entry refers to a person or a photograph. The description is an alphanumeric $($textual$)$ field that describes the person or the photograph location. Lastly, lng contains the longitude of the location and lat contains the latitude of the location.

The remaining table is the photograph table. This table contain two fields, one for the photograph $($an id$),$ and the second to the location of the photograph. Name this table photographs.

photoid $-$ numeric, integer

locationid $-$ numeric $,$ integer

For the photograph table, use SQL to create the table. In MySQL Workbench, click the icon labeled SQL with the plus sign:

In the workbench window, type the correct CREATE TABLE statement to add the photoid and the locationid fields, ensuring the proper type is used.

For each table created, be sure to capture a screen print.

Prompt $3-$ Alter Tables

We wish to make some changes to each of the tables. When a row is created in any of the tables we do not want to allow NULL values.

In order to make a change to a table, the ALTER statement is used. Below is an example of how to modify the users table to ensure the userid is not null:

ALTER TABLE users MODIFY userid INT NOT NULL;

This will change the field userid in the table users so that this field cannot have NULL values.

Create a new SQL statement and modify the following fields so that they are not null.

Table Field$($s$)$

locations type, description, lng$,$ lat

users name, username, password

photograph photoid, locationid

Paste the SQL statements in the Word document with a heading of Prompt $3-$ Alter Tables.

Prompt $4-$ Create Index

Next we want to make userid an index for the users table. An index is usually created when a table is created but we can also create the index separately using the CREATE INDEX statement.

CREATE UNIQUE INDEX id ON users $($userid$)$;

This statement create an index called id where each KEY will be unique $($different from any other$).$ The table users will be used and the index will be based on the first named userid.

Now that the index is created on the users table, add an index for the photograph table.

Execute these command$($s$)$ in a new SQL creation window. After running the command, right$-$click on the photograph table in the Schema panel and click Table Inspector. Click the indexes tab and take a screen print. Paste the SQL statements and screen capture under Prompt $4.$

Prompt $5-$ Enter Data

Now let's enter some data into the database.

We will enter data for the following four individuals.

Userid name username address city state zip password

$1$ Bonnie Buntcake bbunt $6709$ Wonder Street Wonderbread OH $46106$ eclectic

$2$ Sam Smarf ssmarf $356$ A Street Beefy PA $19943$ swimming

$3$ Wendy Grog wgrog $900$ Star Street Mary MD $21340$ wells

$4$ Joe Jogger jjogger $183713$ N North Street Norther WV $51423$ tarts

To enter data into a table you can use the INSERT statement.

INSERT INTO table$\_$name $($column$1,$ column$2,...)$

INSERT INTO users VALUES $(\text{'}$Bonnie Buntcake', 'bbunt', $\text{'}6709$ Wonder Street', 'Wonderbread', $\text{'}$OH$\text{'},46105,$ 'eclectic'$)$;

We don't enter the userid because we set it to an auto$-$nu