Knowledge: BCA Sem-II DBMS Assignment

MS Access Assignment

C Create Tables

Using MS Access, create a product table, a customer table, and an order according to the following specifications. Use the names, datatypes, and descriptions as indicated. (Hint: If you have questions about specific fields, look at the data that is going to be entered into the tables.)

Product Table

Attribute Name	Data Type	Description
prodno	number	The primary key - used to uniquely identify each product
desc	text	Product name or description
price	number (or currency if supported by the DBMS system)	Current price of the product

Customer Table

Attribute Name	Data Type	Description
custno	number	The primary key - a unique customer number to identify each customer
custname	text	Customer' s name - including first and last name
addr	text	Customer's street address
custcity	text	Customer' s city
custstate	text	Customer' s state or province
custzip	text	Customer' s zip or postal code
country	text	Country name
wphone	text	Work phone number
hphone	text	Home phone number
email	text	Customer' s email address

Order Table (continues on next page)

Attribute Name	Data Type	Description
ordno	number	The primary key - a unique number for each order
custno	number	A foreign key - custno is the primary key in the customer table. It will be used to link the order table with the customer table.
prodno	number	A foreign key - prodno is the primary key in the product table. It will be used to link the order table with the product table
saleamt	number (or currency if supported by the DBMS)	The price of the snowboard purchased by the customer (only one board can be ordered per transaction)
orddate	date	The date the order was placed
saletype	text	The type of sale made: Internet, Phone, or Direct
payment	text	Type of payment. May be credit card, cash, or COD
crdtype	text	Name of the customer' s credit card. May be Passport, MiseryCard, Discovery, American Indebtedness, or Diver' s Club
crdno	text	Card number
expdate	date	Expiration date of credit card

EEnter Data Into Tables

· After you create the three tables using Access, you will need to enter some test data into these tables. Enter the data, as indicated, for the appropriate table. Pay attention to detail and enter the information as written. Print and label the tables after adding the data.

Data For Product Table

prodno	desc	price
2101	Freeform	395.00
3120	Halfpipe	350.00
4290	Crossbow	295.00
5435	Sidecut	250.00

Data For Order Table

ordno	custno	prodno	saleamt	orddate	saletype	payment	crdtype	crdno	expdate
1	1	2101	395	12/08/98	internet	card	Passport	154-45-78	12/31/2001
2	3	3120	350	12/05/98	phone	COD
3	2	2101	395	12/09/98	internet	card	Passport	225-56-74	09/30/2005
4	5	4290	295	12/12/98	direct	cash
5	4	3120	350	12/15/98	direct	card	MiseryCard	565-65-98	07/31/2002
6	6	2101	395	12/15/98	phone	card	American Indeptedness	555-55-41	12/31/1999
7	8	5435	250	12/16/98	internet	card	Diver's Club	233-45-87	10/31/2001
8	7	2101	395	12/17/98	phone	card	Discovery	233-65-88	09/30/2002
9	9	4290	295	12/18/98	direct	cash
10	10	3120	350	12/19/98	internet	card	Passport	444-77-77	12/31/2001

Understanding Data Types

In Access, you use data types to specify the type of data each field can capture. A field with a data type of text can store alphabetic characters and numbers. Generally speaking, you cannot perform mathematical calculations by using a text field. For example, you can use a text field to store a street address. Unless you do some manipulation, you cannot use the numbers in the street address in mathematical calculations. You will not be able to sum or average the numbers in an address field, which is fine, because you probably do not want to. Alternatively, you can assign a Test Score field a data type of Number. You can enter numbers into the field and then average, sum, or perform other calculations with the numbers. However, you cannot enter an alphabetic character in a number field.

Data Types
Data Type	Use	Notes
Text	Alphanumeric data. Use for text and for numbers that are not used in mathematical calculations. Use for names, addresses, and other relatively short pieces of text. Can store up to 255 characters.	.
Memo	Long text. Use for long pieces of text, such as notes and long descriptions. Can store up to 64,000 characters.
Number	Numeric data. Use for numbers you want to use in mathematical calculations.	If you are working with currency, use the currency type.
Date/Time	Use for dates and times.
Currency	Use for currency.	Prevents rounding during calculation.
AutoNumber	Unique sequential numbers or random numbers automatically inserted when you create a record. Use to create a primary key.
Yes/No	Logical data. Use when only one of two values is valid. Yes/No, True/False, etc.
Hyperlink	Use to store hyperlinks.
Attachment	Use to store attachments.
OLE Object	Use to attach an OLE object such as a Word document, Excel spreadsheet, or PowerPoint presentation.

After you create the fields for a table, you can enter data by typing in each field. As you type, Access assigns a data type to each field based on your entry.

Assigned Data Types
Sample Entry	Data Type Assigned
Smith	Text
http://www.website.com	Hyperlink
10000	Number, Long Integer
10,000	Number, Long Integer
10,000.99	Number, Double
10000.999	Number, Double
01/01/2009 The date and time formats recognized are those of your user locale.	Date/Time
January 1, 2009	Date/Time
12:10:33	Date/Time
12:30 am	Date/Time
16:50	Date/Time
100.50	Number, Double
25.00%	Number, Double
1.23E+02	Number, Double

Explicitly Assign Data Types and Formats

You may want to change the data type Access assigned to a field, or you may want to explicitly assign a data type to each field. You can do so by choosing the Datasheet tab and then selecting the proper option in the Data Type field in the Data Type & Formatting group.
Some data types allow you to select the formatting you want. By formatting, you determine how data in a field displays. For example, if you choose a data type of number and a format of Euro, any number you enter will appear with a Euro sign in front.
Windows regional settings enable you to display information such as dates, times, and currency that match the standards or language used in the country in which you live. For example, if you live in the United States, the currency setting uses a dollar sign.

Regional Settings for English (United States)
Number	123,456,789.00
Currency	$123,456,789.00
Time	3:39:44 PM
Short Date	7/28/2008
Long Date	Monday, July 28, 2008

Use the Windows Control panel’s Regional and Language options to view or change regional settings.

Data Types
Data Type	Format	How Numbers Display
Number	General Number	As typed.
	Currency	Uses thousands separator. Follows regional settings.
	Euro	Uses currency format with Euro symbol.
	Fixed	Displays at least one digit. Follows regional settings.
	Standard	Uses thousands separator. Follows regional setting.
	Percent	Converts entry to percent.
	Scientific	Uses scientific notation.
Currency	General Number	As typed.
	Currency	Uses thousands separator. Follows regional settings.
	Euro	Uses currency format with Euro symbol.
	Fixed	Displays at least one digit. Follows regional settings.
	Standard	Uses thousands separator. Follows regional setting.
	Percent	Converts entry to percent.
	Scientific	Uses scientific notation.
Date/Time	General Date	Date values display as numbers and time values as hours, minutes, and seconds followed by AM or PM. Follows regional settings.
	Long Date	Uses the Long Date format specified in your Windows regional settings.
	Medium Date	Uses dd/mmm/yy, using the date separator specified in your Windows regional settings.
	Short Date	Uses the Short Date format specified in your Windows regional settings.
	Long Time	Uses hours, minutes, and seconds followed by AM or PM. Uses the separator specified in the Time setting in your Windows regional settings.
	Medium Time	Displays hours and minutes followed by AM or PM. Uses the separator specified in the Time setting in your Windows regional settings.
	Short Time	Uses hours and minutes. Uses the separator specified in the Time setting in your Windows regional settings.
Yes/No		Yes/No True/False On/Off

To explicitly assign a data type or format to a field:

Click the field label for the field to which you want to assign a data type.
Activate the Datasheet tab.
Click the down-arrow next to the Data Type field and then choose a data type.
Click the down-arrow next to the Format field and then choose a format. Access assigns a data type and format to the field you selected.

Tip: If you want every record in a field to be unique, check the Unique box on the Datasheet tab in the Data Type & Formatting group. If you do not want the user to leave a field blank, check the Is Required box.
Tip: In the Data Type & Formatting group, there are several formatting options you can apply to numbers. If you want to use the Currency format, click the Currency button

; if you want to use the Percent format, click the Percent button

; if you want to use a Comma number format, click the Comma button

; or if you want to increase or decrease the number of decimal place, click the Increase Decimal

or Decrease Decimal button

.
Tip: You can create a new table at any time by activating the Create tab and then clicking Table.

Understanding Design View

Access provides several ways to view the objects in your database. You can use Design view to create or modify an Access table. You can use the View button on the Home tab or the Table Design button on the Create tab to change to Design view. Using Design view is the preferred method for creating a table because it provides you with the most options and enables you to precisely define your table. In addition to selecting a data type, you can set all of the following options in Design view.

Design View Options
Field Property	Data Type	Comments
Field Size	Text	Enables you to restrict the number of characters stored in a text field to 0 to 255 characters. The default is 255.
	Number	Enables you to select the type of number stored in a field.
	Number Types	Values Stored
	Byte	0 to 255. (No fractions)
	Decimal	–9.999... x 1027 through +9.999... x 1027
	Integer	–32,768 to +32,767 (No fractions)
	Long Integer	–2,147,483,648 to +2,147,483,647
	Single	–3.4 x 1038 to +3.4 x 1038 numeric floating point values. Up to seven significant digits.
	Double	–1.797 x 10308 to +1.797 x 10308 1038 numeric floating point values. Up to fifteen significant digits.
	Replication ID	Globally Unique Identifier (GUID). Used by Access to establish a unique identifier for replication.
Format	Number	Determines how numbers display. When you use the currency, fixed, standard, and percent formats. Access follows the settings specified in Regional Settings in the Windows Control Panel for negative amounts, decimal and currency symbols, and decimal places.
Currency Number	General Number	Displays as typed.
	Currency	Uses thousands separator. Follows regional setting.
	Euro	Uses currency format with Euro symbol.
	Fixed	Displays one digit. Follows regional settings.
	Standard	Uses thousands separator. Follows regional settings.
	Percent	Converts entry to percent.
	Scientific	Uses scientific notation.
Date/Time	General Date	Displays date and time. Example: 01/02/99, 06:28:21 PM
	Long Date	Displays Day of Week and Date: Example: Saturday, January 02, 1999
	Medium Date	Example: 02-Jan-99
	Short Date	Example: 01/02/99
	Long Time	Example: 6:28:21 PM
	Medium Time	Example: 6:28 PM
	Short Time	Example: 18:28
Text and Memo	@	Text character required.
	&	Text character not required.
	<	Changes all characters to lowercase.
	>	Changes all characters to uppercase.
Yes/No	Yes/No	If the Lookup Display Control is a text box, displays Yes/No.
	True/False	If the Lookup Display Control is a text box, displays True/False.
	On/Off	If the Lookup Display Control is a text box, displays On/Off.
Decimal Places	Determines number of decimal places Access displays.	Auto. Number of decimals displayed depends on the format setting.
		0–15. Used with format property. Determines the number of digits that display to the right of the decimal point.
Input Mask	Special characters used to control the values the user can input.
Caption	Field name displayed on forms.
Default Value	Sets the value that appears in the field by default when a record is created.
Validation Rule	Sets the requirements for user input.
Validation Text	Text for error messages that are sent when validation rules are broken.
Required	Specifies whether the field is required or not.	Yes: Required No: Not Required
Allow Zero Length	Determines whether a zero-length field is a valid entry.	Yes: Is valid No: Not valid
Index	Specifies whether an index should be created in a field. Indexes speed up queries.	Yes: Create index. No: Do not create ind

Knowledge

Thursday, 8 December 2011

BCA Sem-II DBMS Assignment