Organization of Data

HOW TO USE OBJECT BROWSER

After logging into upsql database using Management Studio, locate the object browser (frame on the left). Click the + to the left of Databases to expand the list of all the database schemas available on the server. Use the scroll bar within the object browser frame and find IST210-ONLINE database schema.

Click the + to the left of IST210-ONLINE to expand object categories. For this lab, you will be working with TABLES. The eight database tables for Magnet Supply Company can be identified with the prefix MSC_ in the name of the tables. You can click on the + to expand each of the tables. Explore the columns, keys and constraints for each table by expanding the object. The information provided under these items will provide insight into the database design and allow you to complete this lab.

HOW TO USE SP_HELP TO RETRIEVE METADATA

Another method to retrieve metadata is to use the SQL command window in Management Studio and execute SP_HELP and the name a database object. First, you need to navigate to the IST210-ONLINE database and click on the Tables folder. Now, click New Query located on the menu bar. This will open up the query editor to the right of the object browser. Type, in the command window, SP_HELP followed by a table name. For example: SP_HELP MSC_job (do not include the dbo. Prefix). Click the ! Execute button, which is located on the line just above the SQL command editor. You should now see a series of results that display various information about the database.

USE OF DRAWING SOFTWARE

Microsoft Visio is available to you in VLABS or you may decide to obtain the software for your own personal use. Microsoft Visio software is available free of charge through Penn States subscription of Microsoft Imagine.

YOUR LAB ASSIGNMENT

Here is the lab assignment:

Create a data dictionary for Magnet Supply Company Order System

Create a relational diagram for Magnet Supply Company Order System

Issue SQL select commands (see Getting Started lab) and answer the following questions: (answer the question but also provide SQL and result)

How many different job codes are available?

What is the job code for Linda Fickes?

What is/are the name(s) of the vendor(s) location in PA?

How many vendors are there?

What is the zip code for customer 1001?

What is the sale price for product 4010?

Please prepare your answers in a document and submit it here in Canvas. Remember to (1) answer the question (2) provide the SQL used to answer the question (3) provide the entire data display for the execution of the SQL command and (4) provide the "row(s) affected" message from the execution of the SQL command. Always be sure to include a heading in any document with your name, a brief descriptive title and a date.

need help with this assignment how do I get the calculation to answer those questions?