Expert answer:Creating Relational Databases with Microsoft Offic
Answer & Explanation:Information technologylab2.pdf
lab2.pdf
Unformatted Attachment Preview
LAB-1
CS612AO1: Information Technology
Spring 2014
_____________________________________________________________________________________________
CS612AO2 Information Technology
LAB No. 2: Creating Relational Databases with Microsoft Office Access
•
•
Create a new database, “db1-Lab1-MyLastName.mdb”;
Create seven tables (LABOR-CODE, CUSTOMER, SERVICE-LABOR-DETAIL,
SERVICE-CALL, SERVICE-PARTS-DETAIL, TECHNICIAN, and PARTS) with
proper primary keys and field formats (see below):
LABOR-CODE Table:
LABOR-CODE
LABOR-CODE HOURLY-RATE
A
$35.00
B
$45.00
C
$55.00
D
$65.00
CUSTOMER Table:
CUSTOMER
CUST-NUM CUST-LAST CUST-FIRST CUST-ADDRESS CUST-CITY CUST-STATE
1456 Jones
Albert
43 Oak Drive
Denver
CO
1933 Valdez
Trini
1011 Hilltop Road Charlotte
NC
2836 Gonzalez
Juan
678 8th Street
Seattle
WA
6321 Belli
Mary
101 Main Street San Jacinto CA
8760 Sansone
Jackie
9 Blueberry Court Boston
MA
8848 McSorley
John
321 Smith Lane Hudson
FL
SERVICE-LABOR-DETAIL Table:
SERVICE-LABOR-DETAIL
CALL-NUM LABOR-CODE LABOR-HOURS
10796 A
1.50
10796 C
2.00
10798 B
5.00
10799 C
1.50
10799 D
1.50
10801 A
2.50
1
LAB-1
CS612AO1: Information Technology
Spring 2014
_____________________________________________________________________________________________
SERVICE-CALL Table:
SERVICE-CALL
CALL-NUM CUST-NUM
DATE
TECH-NUM
10796
1456 12/10/2007
4
10797
2836 12/10/2007
17
10798
8848 12/12/2007
21
10799
1456 12/17/2007
4
10800
6321 12/17/2007
21
10801
1456 12/19/2007
9
SERVICE-PARTS-DETAIL Table:
SERVICE-PARTS-DETAIL
CALL-NUM PART-NUM QTY
10796 DD-9192
4
10796 SS-1111
2
10797 AB-6784
6
10799 AB-6784
1
10799 YY-9651
15
10800 SS-1024
8
10801 XC-3133
12
TECHNICIAN Table:
TECHNICIAN
TECH-NUM TECH-LAST TECH-FIRST HIRE-DATE
4 Robison
Gerry
6/1/1995
9 Whitfield
David
8/1/1997
17 Kaplan
Carl
1/1/1990
20 Chou
Leland
12/1/2000
21 Johnson
Marie
2/1/2001
PARTS Table:
PART-NUM PART-DESC
AB-6784
METER
DD-9192
DISC
SS-1024
COVER
SS-1099
EMBLEM
SS-1111
JUMPER
XC-3133
CABLE
YY-9651
PIN
PARTS
COST PRICE QTY-ON-HAND
$98.50 $125.00
33
$1.30 $1.69
220
$16.00 $19.79
78
$38.00 $50.00
41
$12.50 $15.00
3
$6.75 $7.99
77
$0.99 $1.25
1056
2
LAB-1
CS612AO1: Information Technology
Spring 2014
_____________________________________________________________________________________________
1. Notice in the above tables, there are column headings that appear in more than
one table. For example, cust-num appears in both the Customer table as well as
the Service-call table. The header Labor-code appears in both the Labor-Code
table and the Labor-code-details table. Having different tables sharing column
headers links the information contained in the two tables. With the sharing of the
cust-num column between Customer and Service call, we can tell that the
customer name of the customer being serviced on service call 10797 is Albert
Jones. And we can tell that Albert Jones had service calls on Dec 12, Dec 17,
and Dec 19. This information is not in a single table; rather we collect it from the
two tables by “joining” the tables on common values of the cust-num column.
Obviously, these connections are important to capture when designing the data
requirements in the initial data design using Entity-Relationship Diagrams
(ERDs).
a. Go to the web pagehttp://dia-installer.de/download/index.html.en in order to
download the Dia Diagram Editor that you will use to create your ERD. By
default, the download button for Dia 0.97.2 is for the Windows version; if you
use a Mac or Linux, there are links underneath for those operating systems.
Click the button for your operating system and download the installer
program. Remember where you save the installer if it is not run automatically
by your browser. If you save the installer, navigate to the folder and execute
the installer program. It will have for a language (English by default), to agree
to the license terms, select the packages to install (use the default), and the
location to install. Then the install will extract and install. When finished, you
should see the Dia program in your list of programs.
b. I have create a set of short videos demonstrating how to use Dia to create a
small set of tables and how to draw the relationships between them. If you
have any questions after watching the videos, please let me know.
c. For this lab, each of the tables above will be represented by an Entity. Each
of the columns in the table above will become a column in the entity you
create. Create entities for each of the tables. You should create primary keys
for each entity. A primary key is a column or columns which uniquely
identifies the rows of the table. For example, in the customer table, each
customer has a unique cust-num. In the Labor-code table, each labor type is
uniquely identified by the column labor-code. However, in service-call,
service-labor-details, and service-parts-details, the primary key must be a pair
of columns. What are these?
d. The tables must be linked via Relationships. The videos guide you through
creating a relationship between two entities. You need to create relationships
for the following pairs of tables/entities (they will all be one-to-many
relationships):
i. Labor-code and Service-labor-details
ii. customer and service call
iii. service-call and service-labor-details
iv. service-call and service-parts-details
v. service-parts-details and parts
3
LAB-1
CS612AO1: Information Technology
Spring 2014
_____________________________________________________________________________________________
vi. service-call and technician
e. Save your ERD project. Use the Export entry under the File menu item to
export your diagram as a PDF file for submission.
2. Create a database with the above tables using either Microsoft Access or the
equivalent OpenOffice or LibreOffice SBase application. Tutorials for database
creation for Microsoft access can be found at
• http://www.baycongroup.com/access2007/index.html
• http://lib.rivier.edu/record=b1223924~S0 (Link to the electronic book “Access
2010 Bible by Michael R. Groh through the Rivier Library)
Links to tutorials for LibreOffice/OpenOffice SBase are at:
• http://www.tutorialsforopenoffice.org/category_index/base.html (Links to
several OpenOffice SBase tutorials)
• http://gofree.com/Tutorials/LibreOfficeBase1.php (LibreOffice SBase tutorial,
but only using wizards)
Microsoft Access and the SBase all provide a visual means for creating database
tables. The one difference between the tutorials and your tables, is that you do
not want to automatically create primary keys for your tables. Instead, for each
table, you select the field names to be part of the primary key (you must use CtlLeft button to select multiple fields) and right-click and select the primary key
option. The fields should change to have a
3. Create a Query 1 to find all customers who received service after 12/15/2007;
Double click Query 1 to run it. Verify that the returned customers are correct.
Submit your .mdb or odb file containing your database and the PDF file containing your
ERD using the link in the Lab 2 folder.
Please let me know any difficulties you have in downloading/installing any of the
required applications.
4
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades