IS 342 School of CS Western Illinois University

Assignment#1 Amaravadi

Worth:20 pts

DBMS BASICS

In this assignment, we will learn the basics of DBMS activities, including data definition, data entry, query and reports. Please note that to do the assignment requires knowledge of DBMS basics. To start, log into MS Access go into office icon and create a new database. It prompts for the name of a database. Enter “PVF” then you are presented with the main menu of MS Access.

DATA DEFINITION

Go into “Create” tab and select “table design”. You are presented with a form as below. Using this, create the “Cust” table and then the “Ord” and “Req_on” tables.Make sure to define the primary keys.Do look at the sample data. Save the table by closing the tab. It will prompt you for name of table to save. Enter appropriate name.

Cust

cust#Number

cnameShort text

cdiscountsingle, with 0 decimal and % format (need to select “number”“field size”)

Ord

ord#Number

ord_dtDate

prom_dtDate

cust#Number

Req_on

ord#Number

prod#Text

qtyNumber

Hold down the shift key when defining primary keys for the Req_on table.

CHECK RELATIONSHIPS

You need to check if the tables are linked together. Go into “Database tools” and “relationships” from the main menu. It should show the tables linked by cross-reference keys. If they are not linked, “add” all three tables and link them by dragging and dropping attributes (see the diagram below).

DATA ENTRY FORM

2. Select the name of the table for which data entry form is being created (“Cust”). Go into “create” and “forms design” from the main menu.

a)Select the label “Aa” from the form menu and create a label with the mouse. Give it a title (see below).

b)Then add fields using “Add existing fields” under the “design” menu

c)Select rectangle icon from menu shown in diagram above and draw a rectangle around the fields (not shown).

d)Select rectangle that you drew and right click mouse button.

e)Fill the rectangle with suitable color. The fields are not seen now.

f)Go to “Arrange” and select “send to back”. Fields should be seen now.

g)Save the form as “Cust data entry.”

DATA ENTRY.

3. Now you can enter data. From list of objectson left, open up the “customer data entry” form by double clicking. Using the form and information below, add records. When you are done, open up the “ord” table and “req_on” tables and enter the data from the tables below; data is entered directly into table for these two tables.

CUSTOMER TABLE (CUST)

ORDERS TABLE (ORD)

REQUESTED ON RELATION (REQ_ON)

QUERYING

4. We will do four queries. To access the query interface, Go into “create” and “query design” from the main menu. You can select the table and fields as shown below. Note that you must not use the QBE interface.

“Add” appropriate table(s) table (depending upon the query it could be one or all of “Cust,” “Ord,” or “Req_on.”). While in the query menu, select “view” and “sql view.” If you have more than one table, Microsoft adds a “Join” in the “From” part which you should delete. You should type queries only as discussed in class/notes.

Now you can start typing your queries, but remember:

Use square brackets for attributes that have the “#” sign e.g. [cust#]
In order to get data from multiple tables you need to link the values of the common attribute or common field in the Where clause as in table1.linkattr1 = table2.linkattr2, where linkattr is the common attribute you are linking. In such cases, you need to precede all attributes by the table name as in Select ord.[ord#].
You don’t need to use any special operators for dates. However, use “#” to delimit dates as in “….Where Ord.[Ord_dt] = #2/20/09#…”
ACCESS expects “;” as the delimiter for the query (i.e. the end-of-query marker).
Queries need to follow format as discussed in notes/handout.

a)create an SQL query to list customers and their discounts and run it. The query is

Select CName, Cdiscount

From Cust;

The answer you should get is shown below. Copy your query and query result intoa word document. To copy the result table use the selector to the left of the query title. Copy and paste the result into the word document. Once in the Word document, select the table using the table-selector icon and format the left margins by moving the ruler guides at the top of your word document so that the table is properly indented as shown here.

b)Createan SQL query for a list of customers whose discount is less than or equal to 3%. Copy and paste both the query and your answer into the word document. This is left for you to figure out. Use the help facility and look up the “Select” statement. You can make use of online resources too.

c)Create a query to list customers and the orders they placed (i.e. their order#'s). Copy and paste the query and result into your word document.

d) Create a query to list customers and the products they ordered. Copy the SQL query for this and paste your answers into the document. Submit the document and the report (see below) stapled together in this order. Note that for this query there should be two sets of equality conditions in the “Where” part of the query, connected by an “AND.” The from part will have all three tables. This is left as a challenge for you to figure out.

REPORTING

5.We will create a report involving all three tables. Go into “Create” and “Report Design”. You will be presented with a blank screen with PH, Detail and PF. Use the right mouse button to insert Report Header and Footer.

a)Select the “Aa” icon and using the mouse draw a label. Type the report heading. (Note that the icons are under the “design” tab).

b)Using the line icon, draw a line below the heading (see below). You can double click on the line and change its property so as to have dashes.

c)For the page header, create a label or labels that contain the page headers (see second row below).

d)For the detail line select the “add existing fields” icon from report menu and add the fields. Delete the label of each field.

e)For the report footer, copy and paste the line from the RH. Beneath the footer, there is a text box for the date and a label box for your name. The textbox has the value “=Date()” as shown below. The label box simply uses “Aa” icon to create the label. Use your name where it says “Created by ….” Create these and save the report. Your report should be more or less identical to the one shown below except for your name.

f)You need to select and italicize the name and date

g)Print the report and attach it to the sheet listing the queries.

The report should be more or less identical tothat shown below, except for your name

SUBMISSION & GRADING

You should submit print out that contains outputs in the following order:

1)Four SQL queries (that you typed in) -- SQL statements should be typed in by you. You should not be using the queries given by ACCESS.

2)Results of running the queries copied and pasted from Access. Each result should follow each SQL statement.

3)The listing of the report as shown above. Except for name, date and font differences your report should be identical.

Please do not submit the Access database file. Each of the queries and results is worth 2.5 points, the report is worth 10 points. Grading will be based on conformance to requirements.

For e.g. if the words are cut off, -1 point. There are also penalties for not following submission requirements – if there is a problem for you please let me know.

COMMON QUERY PROBLEMS

Is there a semi-colon after the end of query? There should be one.
Is there a space between cust and #? No space.
Is cust# enclosed in square brackets? [cust#]
Are the names of attributes used in the query matching with those that are defined?
Are you using attributes in the ‘select’ part that are listed in the tables, in the ‘From’ part?
Are primary keys identified correctly?
Is your data typed accurately?