Database project- inventory database for a hardware store, Database Management System

Assignment Help:

For this database project, you will use Microsoft Access to create an inventory database for a hardware store, which contains more than 10 records in each table and with the fields listed below for each table. 

 

In this project, you will:

           Design the Table Structures and Relate them

           Enter the Data

           Create Two Reports (one report has to be saved as a screen shot before creating the second report)

           Create Two Queries

 

Procedure:

 

  1. DESIGN YOUR TABLE STRUCTURES with the following field names: (10 Points)

Table 1 -

Field Name

Data Type

Width

Other Information

Item ID

Auto #

5 chars.

Primary Key – may only contain automated integers created by the database system.

Description

Text

20 chars.

 

Vendor ID

Number

6 Characters

6 digits in length

List Price

Currency

 

Leave decimal places as 2

On-Hand Qty

Number

 

Long Integer

Item Type

Text

4 chars.

Type Codes must be validated, and must be formatted in upper case.  Codes are: SOFT, HARD, ELEC, or OTHR.  Default value is OTHR

On Sale

Yes / No

 

Assume Sale Price is 25% off

 Table 2

Field Name

Data Type

Width

Other Information

Customer ID

Auto #

5 chars.

Primary Key – may only contain automated integers created by the database system.

Description

Text

20 chars.

 

Customer Name

Memo

50 chars.

 

Customer Address

Memo

50 chars

 

Customer State

Text

2 Char

Use Postal Codes

Customer Zip

Number

Upto 9 char..

Postal Codes from US Postal Service

Phone Number

Number

10 chars

 

Table 3 – Sales Receipts

Field Name

Data Type

Width

Other Information

Receipt ID

Auto #

10 chars.

Primary Key – may only contain numbers and must be auto generated

Customer ID

Number

5 chars.

Secondary Key from other Table

Item ID

Number

5 chars.

Secondary Key from other Table

Quantity Purchased

Number

6 chars.

 

Sub-Total

Calculation / Curency

10 Chars

Calculate this from the data given make it Curency with 2 decimal places (Qty * Price)

Tax

Curency

8 Chars

Curency same as Total. Assume 9% for Tax Rate

Total

Curency

12 Chars.

Add Subtotal & Tax

 

Start Access and select “Blank Access Database.”  Important: when prompted to “Save in”, name your database file “Inventory.”  Then click on Create.   Don’t try to go any further until this is done!

In the main database window, the Table button should be selected (embedded).  Double-click on Create Table in Design View to set up the structure of your database file.  The cursor will be at the 1st field.  (note: you will create the Table in Design View, not using a Wizard or in Datasheet View).

When the Table Design Screen is displayed, being by typing in the first field name and then tab over to Data Type. Choose the field type from the arrow options (or you can just type the first letter of the type).  Remember that Access uses “text,” “number,” “yes/no,” for what our book calls “character,” “numeric,” and “logical.”

 At this point, you’ll see a General tab in the lower left corner of the screen, with many options, including field size. You must set up all field specifications here, as we did in class. After you have entered all the fields described in the table on Page 1 above, make the Item ID to be the primary key, using the key icon, as described in class.  As you enter data in Section 2 below, using Datasheet View, you will have to be careful to insure that you do not have any duplicate Item IDs.  When finished, save file.

 2. ENTER YOUR DATA (10 points)

 Enter your data directly into the table. You can make up fictitious data, but it must be reasonable, because we will be sorting and making queries from this data.

 

An example of what one record in tabloe 1 of data may look like:

·         ITEM ID               – Mouse

·         DESCRIPTION   – Computer Accessory

·         VENDOR ID             – 4501 (a number – you may want to number your vendors 1, 2, 3, etc.  You may also want to have, say, all of one kind of item come from the same vendor id)

·         LIST PRICE         - $9.95

·         ON-HAND QTY  – 150

·         ITEM TYPE         – HARD

·         ON SALE             - NO

 Your data must contain at least tweleve records and there must be one of each of the following Item Types:

·                     SOFT    Software (Window XP, Microsoft Office, Adobe Acrobat, etc.)

·                     HARD   Hardware (monitor, hard disk, CPU, etc.)

·                     ELEC    Electrical (TV, VCR, DVD, MP3 player, battery charger, etc.)

·                     OTHR   Other (pencil, pen, notepad, bulletin board, marker, etc.)

You must validate that the values keyed into Item Type are one of the four types listed above, and display an appropriate error message when something else is keyed in.

Combination of data required to test your Select query (Query 2) to prove that it works:

·         You must have at least two Item Type HARD records, one that is On Sale, and one that is not On Sale

·         You must have at least one On Sale record whose Item Type is not HARD.

 3. REPORTS (15 points)

 You will be required to create two reports, one before and one after you have deleted one record from your Inventory table.  Remember, you will be deleting a record from the table, not the report (you cannot delete a record from a report).  The report just prints what is in the table.  Thus one report should have 10 or more records on it, and the second report should have one less record on it.

 Using the Report Wizard, create a Report on your data. Choose all fields except On Sale Item, using the arrow  >.  Do not do any grouping, but sort the records descending by Item ID. Choose Tabular and Landscape.  Choose any print style you like.  For the Title, use All Inventory Items Report.  Now print the Report. Please bring this report hard copy to the lab.

 Go back into datasheet view of the Table, and delete one of the records from the table, as shown in class.  Then make another report, using the same process as the first one.  For the title of this one, use One Item Deleted Report.

 4. QUERYs (15 points)

 First Query

·         Using Design View, create a query on your data.  Select the Item ID, Description, Item Type, and On-Hand Quantity of all items. Sort the records descending by Item ID.

·         Click on the save icon, and save it as Query 1.

·         This query will not contain all the fields in the table, but it will contain all of the records.

 Second Query

·         Create another query, using Design View, selecting the following fields: Item ID, Description, Item Type, List Price, and On Sale.

·         The query (using the Criteria row) should ONLY include rows that are both On Sale and Item Type HARD.  In addition, do not show the On Sale field.

·         There is no need to sort this query.

·         Click on the save icon, and save it as Query 2.


Related Discussions:- Database project- inventory database for a hardware store

Which database level is closest to the users, Which database level is close...

Which database level is closest to the users? External database level is closest to the users.

Relationship between security and integrity, Relationship between Security ...

Relationship between Security and Integrity: Database security usually refers to access, while database integrity refers to avoidance of accidental loss of consistency. But usually

Design of distributed databases, Design Of Distributed Databases: The dist...

Design Of Distributed Databases: The distributed databases are mainly relational at local level. So a local database schema is the similar as that of a centralised database design

The extract statement do in extract datasets, What does the extract stateme...

What does the extract statement do in extract datasets? The data is written to virtual memory by extract commands.

Define seek time, Define seek time. The time for repositioning the arm ...

Define seek time. The time for repositioning the arm is known as the seek time and it enhances with the distance that the arm is known as the seek time.

Dirty reads-problems of concurrent transactions, Dirty Reads: T10 reads a ...

Dirty Reads: T10 reads a value which is updated by T9. This update has not been committed and T9 aborts. T9 T10 Value of x old value = 200

Explain view in rdbms, Explain view in RDBMS? View - A view is a relati...

Explain view in RDBMS? View - A view is a relation (virtual rather than base) and could be used in query expressions, which is, queries can be written by using the view as a re

User error-database errors, User error :  This contains errors in the progr...

User error :  This contains errors in the program (e.g., Logical errors) with errors made by online users of database.  These types of errors can be avoided by applying some check

State the rearranging the execution order for efficiency, Define the step -...

Define the step - Rearranging the Execution Order for Efficiency As we know that algorithm and data structure are closely related to one another, but data structure is conside

What is secondary index, What is Secondary Index While making the index...

What is Secondary Index While making the index, generally the index table is kept in the primary memory (RAM) and the main table, because of its size is keeps in the secondary

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd