Relational Database Management Systems

Relational Database Management Systems (RDBMS) such as Microsoft’s Access contains many objects such as tables, queries, forms, and reports. Each of these objects allow you to process, update, maintain, and store information in distinct tables within the database. Tables within the database can be related to other tables within the same or different database system using what is referred to as a “foreign” key. (all the instructions are in the attachment below) please read it
lab8.docx

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on
Relational Database Management Systems
Just from $13/Page
Order Essay

Purpose: Relational Database Management Systems (RDBMS) such as Microsoft’s Access
contains many objects such as tables, queries, forms, and reports. Each of these objects
allow you to process, update, maintain, and store information in distinct tables within
the database. Tables within the database can be related to other tables within the same
or different database system using what is referred to as a “foreign” key.
Keys are important data elements and components within a database. For example, a
“primary” key is a unique identifier assigned to each record in the database. Customer
Number, Social Security Number, Credit Card Number are examples of a primary key
that could be assigned to a Customer, Social Security Recipient or Credit Card Holder.
Thus, the uniqueness of the primary key assigned identifies a single record in the
database. This record, in turn, can and may contain one or more “foreign” keys to
records in other tables within the same or a different database.
Access supports creating/maintaining a database:
•
•
•
•
With or without templates
On a Server using SharePoint
Copying, importing, appending, or linking data from another source into an
Access Table
Adding an application interface.
A few comments:
•
•
•
•
Once you start Access and begin working in a database, you can add fields,
tables, and/or application parts to your project.
An application part provides a powerful tool that allows you to incorporate
various “object” as part of your Access project. For example, an application
part could be a table and a form, query, or report based on the table.
Template can be a useful tool that reduces development time to make your
Access project available for use. This requires you to analyze the feasibility
and practicality of a template satisfying what you are trying to achieve. The
drawback to using a template is that templates have a predefined data structure
that might not satisfy what you are doing and therefore might require a
significant amount of work to be expended to alter the structure to suit your
needs.
You may elect to not use a template if you are importing data from another
source such as Excel
Assignment: This is a two part lab assignment. In part one, you will create a database and two
tables; a Sales Rep Table and a Sales Rep Manager Table. Input data forms for
each table will be created with the two tables populated with data listed in Table 1
and 2 below. A relationship between will be established between the two tables
based on Sales Manager ID with a query set up to show Sales Rep Manager and
the assigned Sales Rep.
In Part Two, you will add two more tables to the database created in Part One and
establish relationships between the four tables, completing a query, report, and
exporting the data to an Excel Workbook. Part Two will be given you next week.
PART ONE:
1. Start Access – a Backstage view opens allowing you to select three methods for creating
a database:
a.) A custom web-based application.
b.) A blank desktop database.
c.) A database from a template.
NOTE: You will create a blank database.
2. Click the File tab and then the New button.
a.) In the Backstage view, click Blank desktop database.
3. In the text box, enter the database name as follows:
Last Name_First Name InitialMiddleName Initial.
NOTE: It is recommended you frequently save your database on either your “H”
drive or preferably on a flash drive. A flash drive allows you to work on the
project outside of school on another computer.
When you create a new database in Access 2013, a default table called
“Table1” will be created as well.
a.) Click the Browse button to navigate to the Drive, Folder, flash drive, etc., where you
want to save your database and then click OK.
b.) .After naming the database, click the “Create” button.
4. Select Design View from the View tool bar tab.
a.) Change the name of the default table to:
SalesRepTable
b.) Click the OK button.
c.) Save the database and table.
Data Types and Uses
Data Type
Description
Example
Short Text
Stores alphanumeric data: alphabetic character,
numbers, and special characters
John Jones, Sr.
Long Text
Length text such as sentences, etc.
Sentences, etc.
Number
Contains numbers that can be used in
calculations, etc., including decimal points and
negative signs.
123.45-
Date/Time
Holds dates or times that can be used in
date/time arithmetic.
1/1/2015
Contains monetary values.
$2,350.00
Currency
1:00:05 AM
AutoNumber A sequential unique number assigned to a
record every time you add a record to the table.
1, 2, 3, etc.
Yes/No
A Boolean field with values such as Yes/No,
True/False, and On/Off.
Yes
OLE Object
Contains an object from another application
such as spreadsheets, pictures, audio/sounds,
and graphics
JPEG Image
Hyperlink
A web address (URL) or a path to a folder or
file.
http://www.towson.
edu
Attachment
Handles multiple spreadsheet files, images, etc. Excel workbook
path/name
Calculated
The results of an expression from one or more
fields
GrossPay*HourlyR
ate
Lookup
Wizard
Creates a field that allows you to choose a
value from another table or to set up a list box
or combo box to search for values.
InvProdCost to look
up unit price from
the Inventory table.
Table 1. Data Field Data Types and Uses.
5. In Design View, set up the SalesRepTable entering the data fields in the order listed
below and named as indicated.
NOTE 1: In design View, the data field definition screen allows you to both define
the data field name and it’s attributes. Some attributes are easy to assign.
For example, SalesRepID, Last Name, First Name are required. Middle
Initial is not. Money and similar type fieds should be initialized with
zeroes. Of interest, the “Caotion” entry is the information that will be
displayed on Forms, Reports, etc. You should think abour and analyze
what you are doing and act. If you have any questions about any entry,
ask your instructor what to do>
The Table Property Types table below defines various data elements and
what can be assigned, entered, etc., in the field. Explore entries such as
Validation Rule and Validation Text by going online and using Google or
the Help available in Access. Use some of your ingenuity and creative skills
when working with the assignment.
Common Table Property Types and Description include:
Property Type
Description
Field Size
Assigns the maximum length of a text field or the format of a
number field.
Format
Changes the way a field is displayed or printed but does not
change how the data is stored.
Input Mask
Provides a mechanism that facilitates data entry operations such as
displaying hyphens for a Social Security, telephone number or
slashes for a data field.
Caption
Permits an alternate name that will be displayed, printed, etc., on
datasheets, forms, and reports.
Default Value
Automatically enters a predetermined value when a new record is
being added to the table.
Validation Rule
Allows data to be verified against defined rules during data entry.
Validation Text
The error message to be displayed when the Validation Rule fails.
Required
Indicates whether a value must be entered in the field.
Allow Zero
Length
Allows entry of zero length text strings, “” for Hyperlinks, Short,
or long text strings.
Indexed
Defines a field as a Primary (No Duplicates) or Secondary key
(No Duplicates or Duplicates Allowed).
Expression
Used on calculate fields. Enter he expression to be calculated.
Result Type
Handles the format for calculated results.
Table 2. Data Field Property/Data Field Types and Type Descriptions.
Data Field List for the Sales Rep Table
Field Name
•
•
•
•
•
•
•
•
•
•
Sales RepID
RepLastName
RepFirstName
RepMI
RepSalesGoal
RepActsales
RepCommRate
RepGoldClub
RepGoldClubCommRate
ManagerIDNo
Data Type:
Text (A/N)
Text (A/N)
Text (A/N)
Text (A/N)
Currency (N)
Currency (N)
Number (N)
Yes/No
Number (N)
Text (A/N)
Length
5
16
12
1
5
Description:
Sales Rep ID Number
Sales Rep Last Name
Sales Rep First Name
Sales Rep Mid Init
Sales Rep Sales Goal
Act Rep YTD Sales
Comm Rate
Rep Gold Club?
Rep Gold Comm Rate
Sales Rep Mgr ID
NOTE: Currency are in the format 9,999,999.99± except for the commission rate
fields. The format for commission is 0.999. In the lower half of the data
field definition screen under “Auto” select the decimal places desired.
The Commission Rate fields are defined as Number (N- Numeric). Enter
the format as Currency and the number of decimal places to 3. This
applies to the SalesRepTable and the SalesMgrTable.
a.)
b.)
c.)
d.)
Make the SalesRepID the Primary Key field.
Save the database and table often while you are entering each data field.
Be aware of the field sizes as you are entering each data field.
Take note of the Validation text. Validation of data entered rules can be entered here
to ensure the kind of information you are looking for is being entered. For example,
you can test to see if RepActSales is equal to or greater than RepSalesGoals to place a
Y or an N in RepGoldClub.
6. Create a Data Entry Form for this table by clicking the Create tab on the Access Main
tool bar and then clicking the Form button.
a.) Name the form SalesRepInputForm.
b.) Enter the data listed in Table 4 below to populate the Sales Rep Table.
Sales
Rep ID
Last Name
First
Name
Mid
Init
Sales
Goal
Actual
Sales
Com
m
Rate
Gold
Club
Gold
Com
Rate
105 Adams
Joseph
A
50,000
38,225.70
0.015
N
0.020
152
122 Cuddington
Thomas
R
75,000
82,125.90
0.020
Y
0.025
110
158 Baff
Andrew
U
55,000
47,550.15
0.020
N
0.025
110
195 Adala
Mary
S
50,000
52,257.75
0.020
Y
0.025
215
210 Smith
John
J
35,000
36.666.75
0.020
Y
0.025
215
225 O’Hare
Louis
N
50,000
47,750.55
0.020
N
0.025
152
270 Notto
Joseph
S
75,000
72.450.85
0.025
N
0.025
110
330 Gordon
Lewin
H
80,000
85,123.45
.0.025
Y
0.030
325
355 Lewis
Maurice
K
40,000
32,848.90
0.015
N
0.020
152
370 Petri
Wendell
J
75,000
74,995.50
0.020
N
0.025
325
397 Dee
Louis
R
35,000
38,987.65
0.025
Y
0.030
215
420 Caramanico
Janice
F
45,000
49,255.90
0.025
Y
0.030
325
Table 4. Sales Rep Table Data.
7. After you have populated the Sales Rep Table, create your next table by returning to the
Main Access Tool bar.
8. To create the next table:
a.)
b.)
c.)
d.)
e.)
Click the Create button.
Click the Table button.
Click the Access Main button.
Click the Save As button.
Rename the table
SalesMgrTable
Mana
ger ID
f.) Click the OK button.
g.) Save the Table and Database.
h.) Follow the procedure starting from Step 5 above through Step replacing the Data
Field List in Step 4 above with the Data Field List below for the Sales Managers
Table and populate the table with the data in Table 5 below.
i.) When you get to the create form step, name the form SalesMgrInputForm.
j.) After completing this operation, go to Step 9 below.
Data Field List for the Sales Rep Managers Table
Field Name:
Data Type:
Length
•
•
•
•
•
•
•
•
•
Text
Text
Text
Text
Currency
Currency
Number (N)
Yes/No
Number (N)
5
16
12
1
ManagerIDNo
MgrLastName
MgrFirstName
MgrMidInit
MgrSalesGoals
MgrActSales
MgrCommRate
MgrGoldClub
MgrGoldCommRate
Actual
Sales
Description:
Manager’s ID
Mgr Last Name
Mgr First Name
Mgr Middle Initial
MgrSales Goal
MgrAct YTD Sales
Mgr Comm Rate
Mgr Received Gold
Mgr Gold Comm Ra
Manager Last Name
ID
First
Name
Mid Sales
Init Goal
Com
m
Rate
Gold
Club
Gold
Comm
Rate
110 Jones
Mary
K
525,000 635,200.90
0.027
Y
0.350
152 Brown
Peter
A
475,000 470,128.55
0.020
N
0.020
215 Johnson
Andrew
J
500,000 488,230.50
0.015
N
0.020
325 Caligula
Charles
B
495,000 525,127.75
0.025
Y
0.035
Table 5. Sales Manager Table.
9. In a relational database, the same field can be in 2 or more tables and thus a relationship
can be established between the tables.
a.) Tables are normally joined in a one-to-many relationship.
b.) One to many relationships enforce referential integrity.
c.) A foreign key is a field in one table that is a Primary Key in another table.
d.) The ManagerIDNo in the SalesRepTable is the foreign key and a common field with
the SalesMgrTable Primary Key ManagerIDNo.
e.) This commonality allows you to join the two tables and thus access the data from
both tables.
10. On the database tools tab, click the Relations Group.
a.) When you create a relationship, you enforce referential integrity.
b.) Select the two tables, SalesRepTable and SalesMgrTable, to be joined
11. To establish the relationship when you click the Relations Group, a dialog box, Edit
Relationships, displays.
a.) In most cases, check the Enforce Referential Integrity.
b.) Check Cascade Update Related Fields.
c.) Establish a one to many relationship between the SalesMgrTable (the 1 side) and the
SalesRepTable (the many side).

Purchase answer to see full
attachment

GradeAcers
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.

Order your essay today and save 15% with the discount code DISCOUNT15