Database Management – Assignment: Database System Chapter 11 / Lesson 1

If you have a Study.com College Accelerator membership and are seeking college credit for this course, you must submit an assignment and pass the proctored final exam. You must submit your assignment before registering for the final. Below you will find prompts and instructions for submitting your assignment.

About this Assignment

Database management systems are key element of information technology systems that are used on a day-to-day basis by many organizations. The keys to using a database management system are to collect the necessary requirements for the database, translate these requirements into a database design, create the database from the design, load data into the database, and finally to perform manipulations of the database. The purpose of this final assignment is to ensure that you understand and can carry out the necessary tasks in order to manage a sample database.

From a high-level standpoint, this final project consists of the following steps:

1. Selection of a database.
2. Identifying the requirements for the database based on the purpose for the database and the data to fill the database.
3. Create a database design based on the requirements for the database, create the physical database, and load data into the database.
4. After loading the database, carry out sample queries that would be performed on the actual database. Show the results of the sample queries.

This project will consist of a series of steps defined in the prompts of the assignment. Some of the prompts are necessary to properly prepare for the manipulation of the database whereas others will be necessary for you to complete to demonstrate your competence with the concepts of this topic. The answers to these prompts will be examined against the actual expected answers to the prompt. All prompts that will be assessed against a answer key, meaning that you will get either full, partial, or no points per prompt.

Setting up the Database/Preparing for this Project

In order to carry out this project it will be necessary for you to set up your access to a database system. The following instructions will explain how to do this. You will be using web-based database server that will allow you to use a database and see the results of entering SQL statements. A trial account can be created on this system that you will be able to use for this project.

There are websites that will provide you with short term free access to a MySQL server and client of your own. The name of the web site that is recommended for this exercise is FREESQLDATABASE.com. You can create a 30-day trial account which is free. This exercise should not require more than 30 days to complete.

Open a browser and go to FREESQLDATABASE.com. Signup for a free account. On your home page, there is a button to ‘Start New Database’ in the section ‘Database Details.’ Click that button and the site will generate a database and email you credentials. Once you receive your database information (server, username, and password), click on the link on the page that takes you to phpmysqladmin and sign onto your database. This will give you a screen something like:

On the top of the window you will see a table bar. Click on the tab labeled ‘SQL.’

The screen displayed will be as shown below.

Additional Project Preparations

Each prompt will ask you to enter an SQL statement, or to formulate an SQL statement. After entering the SQL statement, a window containing the results of the statement (called a query) will be displayed. This results displayed on this screen will be the answer to the prompt and what will be graded. You are to turn in a word document that contains a series of screen captures that contain the results of an SQL statement. The word document will contain multiple pages, each page containing one or more screen captures associated with the prompt. The screens associated with a prompt should be labeled with some text that indicate the prompt they are associated with.

For example:

There are different ways to obtain a screen capture. On a PC you can press the CTRL key and the Print Screen key. Then you can past the captured screen into the document. Pressing Shift-Command-3 on a Mac will capture the current screen on the Mac and place a file of this capture on the desktop.

If you drag this icon into the word document, the screen will appear in the word document like so.

When you have completed this final project, save the word document with all of your screen captures. This is the file you will submit as your work for the final project.

Final Project Prompts

Prompt 1

Your database has already been created by the website. Now let’s check out whether the system will pay attention to your MySQL commands.

Enter the command ‘show databases;’ in the large dialog box at the top below the tab bar.

Then click on the GO button.

When the new screen appears, capture it and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 1.’

Prompt 2

This table lists the names of the database that have been created for you. You will have an information_scheme database and also your database which will be sqlXXXXXXX. Let’s tell MySQL to use your database. Enter the command ‘use sqlXXXXXXX;’ after clicking the ‘Show Query Box’ link.

The answer to this statement (it is called a query in SQL parlance) is shown below.

This means that your database is set for use and right now it is empty (which makes sense because we haven’t created any tables. We can see if there are any tables by entering the query ‘show tables;’.

And again, notice that nothing is shown. So we are ready to begin creating some tables in our database.

The database we shall create will be one that we use to keep tract of where we take 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 with in 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.

This will constitute one of the tables in the database.

userid

name

username

address

city

state

zip

password

We will call this tables the Users table. Every person who uses this database will have an entry in the Users table.

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:

USERS Table

userid numeric, integer

name alphanumeric

username alphanumeric

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.

LOCATIONS Table

itemid numeric, integer

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.

PHOTOGRAPHS

photoid numeric, integer

locationid numeric , integer

With the tables defined we will use the SQL command CREATE TABLE to create each of the tables. Enter each of the CREATE TABLE commands shown below to create the necessary tables.

USERS TABLE creation

CREATE TABLE users (userid INT, name VARCHAR(45), username VARCHAR(20), address VARCHAR(25),

city VARCHAR(15), state VARCHAR(2), zip VARCHAR(5), password VARCHAR(20));

You can check to make sure that the table was defined correctly by entering the command DESCRIBE users; The result of this statement is shown below.

Execute this command by clicking GO on the screen. When the new screen appears, capture it and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 2.’

Prompt 3

1. All of the fields have a default value of NULL. We do not wish to allow this.
2. No field is considered a KEY in this table.

We will make corrections to this table in one of the steps coming up. In the meantime, lets create the remaining two tables.

CREATE TABLE locations (itemid INT, type INT, description VARCHAR(50), lng DOUBLE, lat DOUBLE);

CREATE TABLE photographs (photoid INT, locationid INT);

(Make sure you enter the proper command(s) to display the tables.)

Execute this command by clicking GO on the screen. When the new screen appears, capture it and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 3.’

Prompt 4

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.

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.

Modify the remainder of the fields of this table so that NULL is not an acceptable value for the remaining fields.

Execute this command by clicking GO on the screen. When the new screen appears, capture it

and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 4.’

Prompt 5

Next we want to make userid a KEY for this table. Keys enable the database to retrieve data more quickly by storing the location of a row with a particular key.

An index is usually created when a table is create 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.

Execute these command(s) by clicking GO on the screen. When the new screen appears, capture it and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 5.’

Prompt 6

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 VALUES (value1, value2, …)

INSERT INTO users VALUES (1, ‘Bonnie Buntcake’, ‘bbunt’, ‘6709 Wonder Street’, ‘Wonderbread’, ‘OH’, 46105, ‘eclectic’);

To ensure that the data was properly entered we can use the select command to view the data that is in the table users.

SELECT * FROM users;

Using the INSERT statement enter the three remaining users into the users table and then display the table with the SELECT * FROM users;

Execute these command(s) by clicking GO on the screen. When the new screen appears, capture it and place it in the word file that will contain all of your screen captures. Label this screen capture ‘Prompt 6.’

Prompt 7

If you want to determine the number of rows in a table you can enter the query

SELECT count(*) from users;

Execute these command(s) by clicking GO on the screen. When the ne

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"