BELOW IS THE FILE ATTACHED OF THE PROJECTct5610_spring_1_2016_vrogers.docct5610_project.pdfHybrid-Revised 10/15
Course Number and Title: CT5610 Database Design
Term/Year: Spring 1 2016
Class Inclusive Dates: 1/14/2016 to 2/25/2016 Thursday, Science Building 113A
Instructor Information:
Faculty Name: Vaughn Rogers
Email Address: vrogers@nec.edu
Phone Number: 603-490-2800 between 8:00AM to 9:00PM
Required Materials and Textbook(s):
Database Design – 2nd Edition
http://www.saylor.org/site/wp-content/uploads/2014/12/CS403-1.10-Database-Design-2nd-EditionCCBY.pdf
Optional or Supplemental Materials:
http://www.tutorialized.com/tutorial/Database-Design-Tutorial-with-Example/77313
Course Description & Objectives:
This course introduces database design and creation. Emphasis is on data dictionaries,
normalization, data integrity, data modeling, and creation of simple tables, queries, reports, and
forms. Students should be able to design and implement normalized database structures by creating
database tables, queries, reports, and forms.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Fundamental Concepts
Characteristics and Benefits of a Database
Data Modelling
Classification of Database Management Systems
The Relational Data Model
The Entity Relationship Data Model
Integrity Rules and Constraints
Functional Dependencies
Normalization
Database Development Process
Database Users
SQL Structured Query Language
SQL Data Manipulation Language
1
Hybrid-Revised 10/15
Assignments & Schedule
The course is divided into seven weekly sessions. The class meets on Thursday from 6:00PM to
9:30PM starting on 1/14/2016.
Day 1
Thursday,
1/14/2016
Day 2
Thursday,
1/21/2016
Day 3
Thursday,
1/28/2016
Day 4
Thursday,
2/4/2016
Day 5
Thursday,
2/11/2016
Day 6
Thursday,
2/18/2016
Day 7
Thursday,
2/25/2016
Week
1/14/2016
Topic
Fundamental Concepts
Characteristics and Benefits of a
Database
Learning Obj
Student introductions
and understanding
Database Fundaments.
Reading & Assignment
Read chapters 1, 2 and 3
1/21/2016
Data Modelling
Classification of Database
Management Systems
The Relational Data Model
Data modelling,
Database Management
Systems and the
Relational Data Model
Read chapters 4, 5, 6 and
7
1/28/2016
The Entity Relationship Data
Model
Integrity Rules and Constraints
Functional Dependencies
Relationship data
modelling, Rules and
Constraints and
Dependencies
Read chapters 8 and 9
2/4/2016
Midterm
Chapters 1 through 9
Read Chapter 10 and 11
2
Hybrid-Revised 10/15
2/11/2016
2/18/2016
2/25/2016
Database Development Process
Database Users
SQL Structured Query Language
SQL Data Manipulation Language
Development Processes
and Users
Queries and Language
Final Exam
Chapters 1 through 13
Read chapter 12 and 13
Prepare for the final
Turn in the Final Project
Class Wrap-up
General Expectations/Housekeeping Information and Any Other Supplemental Information
Attendance to each class is required
Class participation is required
Late assignments will be lowered by one grade letter
Assignments need to be submitted to the BlackBoard no later than Wednesday by Midnight
Assignments are to be submitted using Microsoft Office
Assignments will be graded no later than seven days after submission
Course Discussion Forums – Protocols and Schedule
Students will participate in a Discussion Board forum each week, responding to questions posted
by the instructor. Weekly discussions will be available at 12:01 a.m. on Day 1 and the discussion will
close at midnight EST on Day 7. See schedule below. Using the chart above as a guide, be sure to
include the actual day in parentheses next to each “Day” reference for better clarification.
• Your initial posting is due by Day 3 of each week at midnight.
• You must respond to the posting of two other students by Day 7 at midnight.
1. Discuss Databases you have used and what was there
purpose
2. Discuss a process that is currently not using a Database and
how would it be better implementing a Database?
3. What are the benefits to Relationship Databases?
4. What is the difference between Microsoft Access and SQL?
5. What is the benefit to enabling an online Database?
6. How will the skills learned in this class, help your IT
career?
3
Discussion
Opens @
12:01 a.m.
1/14/2016
Discussion End
@ midnight
1/21/2016
1/27/2016
1/28/2016
2/4/2016
2/11/2016
2/18/2016
2/3/2016
2/10/2016
2/17/2016
2/24/2016
1/20/2016
Hybrid-Revised 10/15
•
•
•
•
•
•
•
•
•
Your postings must be submitted on time according to the instructions above to receive full
credit.
Responding to 2 other students is a requirement.
Your initial response to the Discussion Board should be approximately two paragraphs in length
and respond directly to the question. One or two sentences will not be sufficient to cover the
topic for each discussion.
The subject area of your initial posting must be “first/ last name initial posting”
ex: Bob Brown’s Initial posting.
Responses to other students should be approximately 1 paragraph. Points will not be given to
responses to classmates that simply state, “I agree” or “Good point.” Your responses to
classmates must serve to advance the conversation. You may agree or disagree, but all postings
must be courteous and respectful.
The subject area of your responses must be “firstname lastname to firstname lastname”
ex: Bob Brown to Sue Gifford
Your Discussion Board postings will be graded on the level of thoughtful analysis presented
each week and your participation by responding to classmates in a thoughtful manner (see
Grading Rubric below). Postings should be limited to the topic presented.
Your Discussion Board postings must be well written. Points will be deducted for postings that
contain grammatical errors and/or typos.
It is suggested that you create your posting in a word processor first so that you can save and
spell check before posting to the Discussion Board. Do not type lengthy postings to the
Discussion Board without saving your text elsewhere; you could easily lose your work if your
internet connection is disrupted or you experience other technical difficulties. Save your backup copy of your posting in the event of a system problem that might require re-posting of your
copy.
Occasionally the Blackboard system is off-line for a brief period. Students should re-try submission
at a later time. If, after repeated attempts, you are unable to post due to a system malfunction, you
should email your submission to the instructor by the deadline in order to avoid being marked
down. Postings should only be emailed as a last resort. Any posting emailed to the instructor will
need to be submitted to Blackboard once the system is restored.
4
Hybrid-Revised 10/15
Grading Policies:
Specific activity directions, grading guides, posting requirements, and additional deadlines can be
found in the Course Information area in the Assignment Guidelines and Rubrics folder.
Final Grade Calculation:
Percentage of Final
Grade
15%
25%
10%
15%
35%
100%
Assessment Item(s)
Midterm Exam
Final Exam
Participation
Discussion Board
Final Project
Total Grade
Grading Scale:
A
AB+
B
BC+
C
CD
F
100-94
93-90
89-87
86-84
83-80
79-77
76-74
73-70
69-60
59 and below
Please note: Final course grades of C+ or below will not meet graduate degree requirements.
Students will need to repeat any course in which they received a grade C+ or below. For more
information, please refer to the New England College Academic Catalog.
Required Technical Skills
In order to function successfully in an online community, students must have the following skills:
proficiency in Microsoft Office Word and other Microsoft Office software as required by course
assignments, proficiency in email, ability to conduct a web-search, and proficiency in managing and
attaching documents.
5
Hybrid-Revised 10/15
Attendance Policy
Students are expected to attend and participate in all dimensions of every course. Missing more
than one class or not contributing online for more than a week will adversely affect your grade.
Students are expected to attend class and actively contribute to the discussions online. A significant
portion of your grade is based upon this. Students are personally responsible for the material
missed as a result of absence from class. It is the responsibility of each student to understand fully
the attendance policies and procedures for every course in which the student is enrolled. New
England College respects student absences from classes due to religious observances. In such cases,
students are expected to notify their instructors prior to the anticipated absence. Making up missed
assignments is the student’s responsibility.
While hybrid classes combine face-to-face class time with an online component delivered through
Blackboard, they are not correspondence or self paced. Students must participate (defined as
logging in to the Blackboard classroom and interacting; posting questions, thoughts, homework,
assignments, etc) in the online portion of class no less than three times per week. All hybrid classes
include an online component that constitutes 35% of the class in addition to the weekly face-to-face
meetings.
Expectations for Online Behavior
NEC requires a learning environment in which all people are respected and safe to take the risks
necessary for learning. All online communication must be respectful and constructive. Using
profanity or textual attributes (e.g.: all caps, etc.) designed to belittle class participants is strictly
forbidden. Language which indicates positive or negative generalizations about any social and/or
political group is also forbidden. . Students who violate this policy will be referred to the Office of
Judicial Affairs.
NEC Academic Integrity Policy: Graduate Programs
The New England College community embraces an Academic Honor Principle. It consists of
honesty, trust, and integrity. Honesty is being true to oneself and others, engendering a culture of
trust. Trust builds mutual respect, fostering a disposition of responsibility and civility. Integrity
denotes inner strength of character: doing what is right and avoiding what is wrong. Students,
Faculty, and Staff accept these values as fundamental guides to our actions, decisions, and behavior.
6
Hybrid-Revised 10/15
Academic Dishonesty
Academic dishonesty includes, but is not limited to, the following infractions:
Plagiarism: Using other people’s ideas, research, opinions, or words and taking credit for it as if it is
your own work instead of copied. It is failing to cite quoted and/or paraphrased words or ideas from
another person’s work other than the common knowledge or original thinking prepared for the
course. Submitting an assignment or sections of an assignment that someone else has written –
without giving proper credit – is plagiarism. This includes work from other students, a purchased
paper, and text from the internet. The following list describes different ways of plagiarizing. Any of
these activities is academically dishonest:
1. Direct copying and pasting from a source, without citation
2. Including cited sources in your paper, but not including sufficient information or correct
formatting
3. Copying pieces of a source
4. Copying a source and then changing some of the words
5. Using pieces of many different sources to put together a new whole
6. Submitting a paper – or parts of a paper – that you have submitted for another course
7. Using more writing from other sources than from the author, even though it is cited
Misrepresentation: Having someone else do coursework, assignments, papers, quizzes and tests.
Facilitation of Academic Dishonesty: Helping someone else cheat. Examples include: supplying
questions and/or answers to a quiz or examination, allowing someone to copy your homework,
doing homework together without the instructor’s permission, seeking input from others during a
take-home or open book test.
Cheating: Deliberate deceptive behavior to avoid work and learning. Examples include, but are not
limited to:
1. Communicating with others during an exam or quiz
2. Copying all or part of homework or another’s quiz, exam, or written work
3. Using notes when you are directed not to by the professor, using electronic equipment to look
up answers you don’t know
4. Making up data for research
5. Stealing quizzes or exams prior to their administration
6. Altering or attempting to alter college records
7. Offering a bribe to college personnel in exchange for special treatment or favors
Because academic dishonesty violates academic integrity, it cannot be condoned at NEC.
For further explanation on this topic, please refer to the New England College Academic Catalog.
7
Hybrid-Revised 10/15
Credit hours and student work
Regardless of the format (in-class, online, or hybrid) or the time period in which the course is
offered, (e.g. fifteen or seven weeks) the student work expectation for all courses is the same. One
credit represents 45 hours of work over the course of the term (including lectures, laboratories,
recitations, discussion groups, field work, study, etc.). Note that course grades are based on the
quality of the work submitted, not on hours of effort.
Statement on Fair Practices
New England College prohibits discrimination on the basis of race, color, creed or religion, national
origin, sex, sexual orientation, age, marital status, pregnancy, veteran’s status, or disability in regard
to treatment, access to, or employment in its programs and activities, in accordance with federal and
state laws and regulations. In compliance with the Americans with Disabilities Act (ADA),
individuals with disabilities needing accommodation should contact the ADA compliance officer.
For further explanation on this topic, please contact the Dean’s Office within the School of Graduate
and Professional Studies.
Class Accommodations
If you have a documented learning need that will require accommodations for this class, you should
contact Anna Carlson, Director of Disability Services at NEC. Anna Carlson can be reached at (603)
428-2302 and by email at: acarlson@nec.edu. She will help you determine possible accommodations
for this class. Once you have completed the Needs Assessment Form with Ms. Carlson, she will
discuss with you the best way to help you overcome any barriers to your academic success. Please
be aware that you must be able to demonstrate competency in this class. This means that with
accommodations, you can meet all the educational objectives of the course. Note that faculty
members are under no obligation to make accommodations for those without proper
documentation. In situations where several people are involved in developing accommodations,
three weeks prior notice will be required. For more information on class accommodations, please
refer to Support Services/Disability Services under Students, Faculty & Staff on the NEC website.
Administrative Withdrawal Policy
Nonattendance (understood in the online environment as lack of participation in the discussion
boards, and failure to submit the required assignments) does not constitute a withdrawal, nor does
stopping payment on a check for registration fees constitute a request for refund; it is the student’s
responsibility to add or drop classes—not the instructor’s.
8
Hybrid-Revised 10/15
Policy
If a student misses two or more consecutive weeks of class during the term, or if the student has
missed enough assignments that the instructor believes s/he cannot meet the goals of the course
within the remaining timeframe, s/he may be subject to administrative withdrawal. In the absence
of extenuating circumstances and 48 hours after the student has been notified, the administrative
withdrawal will take place and the student will be removed from the class. This Administrative
Withdrawal Policy will be implemented in all graduate level courses subject to the following
provisions:
• The Student Services staff will carry out a good faith effort to contact the student and advise him
or her of the situation and the consequences of an administrative withdrawal.
• The instructor will attempt to contact the student and issue a warning prior to the decision of
administrative withdrawal.
• The administrative withdrawal must be approved by the program director, upon
recommendation from the instructor and/or the student services staff. If the instructor does not
initiate the administrative withdrawal process, s/he must be fully informed by the Student
Services staff so that s/he can contact the student (via email) and issue the warning.
• Students must be informed that administrative withdrawal may have an impact on their
Financial Aid awards and status in the program.
• The Student Services office must have sufficient documentation of student notification prior to
the administrative withdrawal. This documentation, together with the instructor’s warning, will
become part of the student’s file at NEC.
• Administrative withdrawal will take place after the fee refund period. Students who are
administratively withdrawn from the course will not be eligible for a tuition refund.
• For the purpose of withdrawals, term weeks start on Mondays and end on Sundays.
• No withdrawals are permitted during the last week of the term.
9
Hybrid-Revised 10/15
Blackboard, Email, and Technical Issues
NEC College Email Account
• Institutional communication, including communication with instructor, student services,
administration, etc. MUST be conducted through your @nec.edu email account. Please check
your NEC email regularly. The college assumes no responsibility for messages not received
because of failure to check @nec.edu email. You cannot forward your NEC email into another
email account.
• You can access your NEC email account by going to www.nec.edu and clicking on the hyperlink
in the top right corner: Students, Faculty, and Staff (MyNEC).
• Click the red button for Webmail.
Personal computer problems
A computer with Internet access is required for this course. Students should retain copies of all
your course work on a flash drive or other backup device. Organize and manage your files so that
you can easily locate what you need. Should you have a personal computer failure during this
course, you must have plans to have access to another computer. This can be your local library
computer, a family member’s or friend’s computer. A DSL or cable connection is recommended.
Blackboard General Information
• This course will utilize Blackboard, an online course management system.
• There are two ways to access Blackboard:
1. Go to www.nec.edu. Click on the Students, Faculty, and Staff (MyNEC) link in the upper
right corner. Click on the red button for Blackboard.
2. visit http://blackboard.nec.edu/
Blackboard Technical Support
• If you have questions regarding Blackboard or the MyNEC system, please contact Student
Services at studentservices_gps@nec.edu or 603-428-2258. If you have questions regarding your
NEC email account, you can reach our help services by emailing helpdesk@nec.edu or by calling
603-428-2350.
• Specific Blackboard help and tutorials can be found at on the lower left corner of your MyNEC
Blackboard page: Quick Tutorials Catalog.
• The NEC Help Desk is available through www.nec.edu. Click on the Students, Faculty, and Staff
(MyNEC) link in the upper right corner. Click on the red button for MyNEC Help Desk.
Software Requirements
Save files in Microsoft Office format only. Microsoft Office software is available free to students
and employees through the IT website.
To acquire discounted and/or free software and hardware, please follow the instructions below:
1. Go to the www.nec.edu website.
2. Click on the top right link for MyNEC – Students, Faculty and Staff.
3. Click on the red button labeled MyNEC Help Desk.
10
Hybrid-Revised 10/15
4. Scroll midway down the page to Other IT Issues & Tip Sheets. Click on the red link for
Students.
5. Scroll down to the bottom of the page to the heading: Information about Information
Technology at NEC. Click on the link for “Discounts on Computers and Software.”
6. Scroll halfway down the page to the heading: “Discounted Microsoft Office and other
software through e-Academy.” Click on the link for http://nec.e-academy.com. Be prepared to
register using your 6-digit NEC ID or NEC email account.
7. Click on the icon for Microsoft Office 13 or Microsoft Office for Mac 2011.
8. Click to Add to Cart. You may want to click on the product description hyperlink to make
sure you have the adequate system to support the download. Please be aware that you can
only download this software once. If your system is not adequate for the software, you will
not be able to download another package on a different computer.
9. Sign in with your email user name and password. Click Register if this is your first time at
the site.
10. Verify that you have an institution issued email address. Click continue.
11. Type in your @nec.edu email address. Click continue.
12. Follow the directions to download the Microsoft Office software.
File Formats and Naming Conventions in Blackboard
If you are uploading files to Blackboard or attaching files to a Blackboard Discussion Board
post, please remember the following file naming conventions:
• Use only letters, numbers and underscores (_) in filenames.
• Don’t use periods anywhere else in your filenames except before the extension.
• Don’t use spaces, commas, pound signs (#), question marks, equal signs, dashes or any
other special characters in your filenames.
Technical Difficulties in Blackboard
•
In the event that the Blackboard system is temporarily unavailable and you are unable to submit
an assignment by the due date, you must email your assignment to the instructor by the deadline
in order to receive full credit. Your assignment must be posted to Blackboard once the system is
restored.
11
Hybrid-Revised 10/15
Blackboard Course Menu – Online Course Structure
Announcements:
o The instructor will post general announcements to this section, such as a change in the
time of an office hour chat, reminder of deadlines, etc. It is recommended that you read
posted announcements regularly.
Start Here/Syllabus:
o This is the area from which you accessed the course syllabus. The syllabus will be
available in this section for the duration of the course, and any revisions will be posted.
o You may also find starting instructions and other initial course activities.
Weeks
o
All assignments (readings, discussion board activities, learning units, etc.) will be
grouped in one location by the specific week. WEEK 1, WEEK 2, etc. will be added to the
course menu so you can click these links and go to one section with all items for the week
in one place. While you can access Blackboard sections as described in “Tools” below,
you will also find everything conveniently grouped according to the week.
Discussions:
o We will use the discussion board each week for asynchronous discussions. This means
that you can post your response any time before the due date and that students do not
have to participate in the discussion at the same time.
o The Discussion Board postings will facilitate collaboration as you will be required to
respond to the postings of at least two of your classmates each week.
o Your instructor will read all postings, but may not respond to every one. Students should
expect the instructor to respond to some postings at least twice during the week.
Grading matrix for Discussion Board postings
Criteria
Excellent (3 Pts)
Good (2 Pts)
Timeliness
Initial Posting
Initial posting and Initial posting is on
responses on time. time, one response
is late
Posts well
Posts well
developed
developed
statement that fully statement that
addresses and
addresses all
develops all
aspects of the task;
aspects of the task. lacks full
12
Acceptable(1 Pt)
Unacceptable(0
Pts)
Participates not at
all.
Initial Posting is
late and responses
are late
Posts adequate
Posts no initial
statement with
thoughts.
superficial thought
and preparation;
doesn’t address all
aspects of the task.
Hybrid-Revised 10/15
Follow-Up
Postings
Content
Contribution
References &
Support
Clarity &
Mechanics
development of
concepts.
Demonstrates
Elaborates on an
Posts shallow
Posts no followanalysis of others’ existing posting
contribution to
up responses to
posts; extends
with further
discussion (e.g.,
others.
meaningful
comment or
agrees or disagrees);
discussion by
observation. All
does not enrich
building on
required responses discussion. Or only
previous posts. All
one response.
required responses.
Posts factually
Posts information Repeats but does
Posts information
correct, reflective that is factually
not add substantive that is off-topic,
and substantive
correct; lacks full
information to the incorrect, or
contribution;
development of
discussion.
irrelevant to
Advances
concept or thought.
discussion.
discussion.
Uses references to Incorporates some Uses personal
Includes no
literature, readings, references from
experience, but no references or
or personal
literature and
references to
supporting
experience to
personal experience. readings or
experience.
support comments.
research.
Contributes to
Contributes
Communicates in Posts long,
discussion with
valuable
friendly, courteous unorganized or
clear, concise
information to
and helpful manner rude content that
comments
discussion with
with some errors in may contain
formatted in an
minor clarity or
clarity or mechanics. multiple errors or
easy to read style mechanics errors.
may be
that is free of
inappropriate.
grammatical or
spelling errors.
13
Hybrid-Revised 10/15
H. Raymond Danforth Library Services – accessing the Library Web Page
•
•
•
•
Open a new browser window, and be sure cookies are enabled on your computer.
To access the library catalog and databases, go to www.nec.edu/library, Click on the button that
says “Library Website.” This will bring you to the library’s Online Resources Page.
On the right hand side of the page under the heading that says “Danforth Library Databases &
Websites” there are links to our database holdings by subject and in alphabetical order. Choose
the database you would like to use. If you are looking for books, our catalog is listed in the
middle column under “Find Books & Audio Visual Resources.”
To log-in to the databases from off-campus, you will need to know your NEC ID #. If you don’t
know this number, please contact the library.
The Distance Services Librarian, Mark Rowland, is available to assist students in person on weekdays
between 8:00 AM – 4:00 PM and on Thursdays from 2:00-10 PM. His office is in the library and he can
be reached by phone: (603) 428-2344 or by email: mrowland@nec.edu Reference librarians are also
available to help students in person, by phone at 603-428-2344 or by email: libraryhelp@nec.edu
New England College is a member of both the GMILCS/NHCUC consortiums which allow NEC
students to check out books from several public and academic libraries across the state. To see more
information about this program, or to see if your library participates, please visit
http://www.nhcuc.org/our-campuses/ or http://findit.gmilcs.org/polaris/
This syllabus constitutes the agreement between the instructor and student.
Any modifications to this syllabus will be identified during the course.
14
Project
5 – Relational Databases (Access)
Project Objective
To operate efficiently and to remain competitive, it is essential for a company to manage its data
appropriately. Databases enable quick access to critical information; provide secure storage for
sensitive data, and offer analysis/reporting tools for real-time decision making. In addition,
databases also play an important role with a company’s presence on the Internet. Web-based
databases have the ability to store information pertaining to customers, employees, competitors,
online orders, and buying habits. With this in mind, you realize it is a necessity to integrate a
database into your existing IT infrastructure.
In this project you will learn to use Microsoft Access to create a relational database, which is a
type of database that has data organized into related tables. This database will include tables
that store the most common types of information pertinent to a corporation:
•
•
•
•
Employees
Customers
Products
Orders
In addition to the tables, you will also setup the relationships between tables, and design input
forms and queries (with formulas) plus create reports (also with formulas).
Project Overview
This project has been organized into 7 different parts:
1. Creating a Flat File database in Excel
2. Importing existing data from an Excel Spreadsheet
3. Creating the Database Tables
4. Establishing Table Relationships
5. Generating Forms to Input Data
6. Building Queries to Access Specific Data
7. Creating a Report
Page 1 of 1
Part 1 – Creating a Flat File Database
In part 1, you will create a flat file database for storing some employee information. Follow the
instructions and samples listed below.
1. Open Microsoft Office Excel 2010.
2. Rename Sheet1 to Employees.
3. Delete Sheets 2 & 3.
4. In cells A1:K1, merge and center the following title: Employees for Your Company. Replace
Your Company with your company’s name. Format it how you want. You may want to change
the size, weight, and color.
5. In cells A2:K2, enter the headings shown in Figure 7. They are: No., Title, First Name, Last
Name, Gender, Job Position, Salary, Address, City, State, Postal Code.
6. Beginning in row 3, enter your own information for 10 employees. Make sure you:
a. have 5 female employees and
b. only create 5 different job positions.
7. Format the table of employees, including the headings in row 2. Make sure you change the
Salary column to currency and do not show a decimal point. You may format the colors and
such however you wish.
8. Select the cells A2:K12. In the Formulas tab, select the Define Name button. For the name
enter employeelist, and press the OK key. This will name the range of cells that contain
your field headers and data, so that you can easily refer to it later here and in other projects
down the road.
9. Save your workbook as lastname_firstname_excel_database.xlsx
Page 2 of 2
Part 2 – Importing data from an Excel Spreadsheet
In part 2, you will import data from the database created in part 1 into your Access database.
Follow the instructions and samples listed below.
1. Open Microsoft Office Access 2010.
a) Click on Blank Database.
b) Under the Blank Database section on the far right in the File Name textfield, enter
lastname_firstname_access. Click on the Browse icon to change the location to save
your file. It will append .accdb. The Save as type is Microsoft Access 2007
Databases (*.accdb).
c) Click the Create button.
2. From the top, click on the External Data tab. Click on the Excel button.
3. In the Get External Data window, use the Browse… button to locate and select the excel file
you just created in part 1. Select the Import the source data into a new table in the
current database. option. Click the OK button.
4. In the Import Spreadsheet Wizard window:
a) Select the Show Named Ranges radio button. Select the range employeelist, which is
the name range you created in your Excel project. Select the Next > button to go to the
next step in the wizard.
b) Check the box First Row Contains Column Headings such that there is a checkmark
there. Click the Next > button.
c) Select the No column in the table. Under Field Options, change the Field Name to
EmployeeNum. Click the Next > button.
d) Select the Choose my own primary key. radio button. Using the pull-down, select the
EmployeeNum option. Click the Next > button.
e) Note: A primary key field is used to uniquely identify each record in your table.
f)
For the Import to Table textfield, enter EmployeesTbl. Click the Finish button. Click
the Close button.
5. Open your Employees table in Design View by clicking on the Home tab. In the upper left
corner, click on the View pull-down and select the Design View option.
6. Delete the Gender and Salary fields by right clicking on the field row and selecting the Delete
Rows option. (Another way to delete is clicking on the field row and selecting the Delete option
under the Records section in the Home tab.) Click the Yes button to permanently delete the
selected field.
7. Add a new field to the table by typing Employee Status in the first empty cell in the Field Name
column. Set the Data Type to the Lookup Wizard… option.
8. In the Lookup Wizard window:
a) Select the I will type in the values that I want. radio button. Click the Next > button.
b) Under Col1, type in the following three options in the first three cells: Full-time, Parttime, and On-call. Click the Next > button.
Page 3 of 3
c) Checkmark the Limit To List checkbox. Click the Finish button. You have just created
a Lookup Value for the Employee Status field.
9. Save your changes by clicking on the Save icon (3.5” disk) in the top left corner of the Access
window.
10. Open the EmployeesTbl in the Datasheet View by clicking on the View pull-down and selecting
the Datasheet View option. (If you do not see the View pull-down, then click on the Home tab
and you will see it in the upper left corner.)
11. Add a status to each of your employees in the table. To add a status, you can simply click in the
Employee Status cell for each employee and utilize the lookup values provided by clicking on
the pull-down menu.
Note: Your data is automatically saved each time you complete the editing of a record and
move on to another record.
12. When finished, close the table by right-clicking on the name EmployeesTbl and selecting the
Close option.
Page 4 of 4
Part 3 – Creating the Database Tables
In part 3, you will create three additional database tables that will store Customer, Product, and
Order Information. Follow the instructions below to create the tables.
Now, you will follow the steps below to create the Customers table that will store all customerrelated information (at least 10 entries). Below is a sample customer table:
1. Click on the Create tab and then click the Table Design option. This will let us create a new
table in Design View.
2. Enter the following Field Names and Data Types.
Field Name
CustomerNum
CustomerName
Address
City
State
Zip
Telephone
Fax
Data Type
AutoNumber
Text
Text
Text
Text
Text
Text
Text
3. Set up your key field by selecting the CustomerNum field and then selecting the Primary Key
icon under the Design tab.
Page 5 of 5
4. Save your table and name it CustomersTbl.
5. Set up an Input Mask for the Telephone field. An input mask specifies a pattern, e.g. (213) 7404542, for all data to be entered in the field.
a) Click on the Telephone row in the CustomersTbl.
b) Click on the General tab in the Field Properties section below the table.
c) Click on the Input Mask row and then click on the
Mask Wizard.
(Builder) button to start the Input
d) In the Input Mask Wizard window, select the Phone Number option and click the Next >
button.
e) For the Input Mask, the textfield should have !(999) 000-0000 in it. Click the Next >
button.
f)
You can leave the default for storing the data. Click the Next > button.
g) Finally click the Finish button. This will force everyone to use the same format for
inputting this data, since it can be typed many different ways.
6. Set up an Input Mask for the Fax field.
7. Switch to Datasheet View and enter information for 10 customers. Use the tab key to move
from cell to cell. Notice that CustomerNum is automatically entered.
8. Close the table and the information will be saved automatically.
Page 6 of 6
Now you will create a Products table for your products and their prices. Here is a sample of
what the ProductsTbl table will look like in Datasheet view:
9. Create the Products table using the same tasks as you followed when creating the Customers
table. The Products table uses the following field names and data types:
Field Name
ItemNum
ItemName
RetailPrice
Data Type
AutoNumber
Text
Currency
10. Make the ItemNum the Primary Key.
11. Save the table and name it ProductsTbl.
12. Switch to the Datasheet View and enter at least 7 different products into the table. Then
close the table.
Page 7 of 7
Create the Orders table that will store all order-related information. This table will be linked to
the previous tables. Here is a sample of what the Orders table will look like:
Lookup
Values
13. Create the Orders table using the same tasks as you have for the previous tables. The Orders
table uses the following field names and data types:
Field Name
OrderNum
OrderDate
ItemNum
Quantity
CustomerNum
EmployeeNum
Data Type
AutoNumber
Date/Time
Number
Number
Number
Number
14. Set the OrderNum as the Primary Key.
15. Add an Input Mask for the OrderDate field and select the Short Date option.
16. Save the table and name it OrdersTbl.
17. Set up a Lookup Values for the ItemNum such that it will look up values for the ItemNum from
the ProductsTbl.
a) In the Design View, select the ItemNum field.
b) Click on the Lookup tab below the Field Properties.
c) For the Display Control, select the pull-down and choose the List Box option.
Page 8 of 8
d) Set the following options:
List Box
ProductsTbl – The table to link to for information
1 – The column of the ProductsTbl which contains
the information to put into the ItemNum field
2 – The number of columns to display information
from the ProductsTbl when using the Lookup Value
(pull-down) in Datasheet View
0.25”;1” – The respective column widths to display
information (the number of widths will match the
Column Count)
18. Set the Lookup Values for CustomerNum field as follows:
CustomersTbl – The CustomerNum field is linked
to the CustomerTbl
1 – The column of the CustomerTbl which contains
the information to put into the CustomerNum field
2 – The number of columns to display
0.25”;1” – The appropriate column widths to
display information
19. Set the Lookup Values for the EmployeeNum field as follows:
EmployeesTbl – The EmployeeNum field is linked
to the EmployeesTbl
1 – The column of the EmployeesTbl which contains
the information to put into the EmployeeNum field
4 – Display information from four fields:
EmployeeNum, Title, First Name, and Last Name
0.25”;0”;.75”;1” – Column widths, but set the
second one to 0 so it won’t display the Title
20. Switch to the Datasheet View and enter at least 6 different orders into the table. Make sure
there are at least 4 orders for March 2012.
21. Save and close the table.
Page 9 of 9
Part 4 – Establishing Table Relationships
Once you create your four tables with the appropriate number of fields, key fields and field
attributes, you must set-up relationships between the tables including Enforce Referential
Integrity. Referential integrity is a set of rules that MS Access enforces to maintain consistency
between related tables when you update data in a database.
Next, you will establish relationships between the four tables. Refer to the diagram shown
below:
1. Click the Database Tools tab and click on the Relationships option.
2. Click on the Show Table button. Select all four tables (use the Shift key to multiple select).
Click the Add button.
3. Arrange the tables as shown above.
4. Click, hold, and drag the CustomerNum field in the CustomersTbl over the CustomerNum field
in the OrdersTbl. Let go.
5. In the Edit Relationships window, checkmark the Enforce Referential Integrity checkbox.
Click the Create button.
6. Click, hold, and drag the ItemNum field in the ProductsTbl over the ItemNum field in the
OrdersTbl. Let go.
7. In the Edit Relationships window, checkmark the Enforce Referential Integrity checkbox.
Click the Create button.
8. Click, hold, and drag the EmployeeNum field in the EmployeesTbl over the EmployeeNum
field in the OrdersTbl. Let go.
9. In the Edit Relationships window, click on the Create button. (Do not enforce referential
integrity.)
10. Click on the Close button to close the relationships and save your changes.
11. You can test the relationships by trying to add an order for a non-existent customer.
Page 10 of 10
Part 5 – Generate a Form to Input Data
You will create an input form for adding a new customer and another input form for taking new
orders.
Now, you will create an input form for the Customers table. Refer to the form shown below:
1. Click on the Create tab.
2. Click on the Form Wizard option.
a) For the Tables/Queries option, use the pull-down to choose Table: CustomersTbl.
Select all of the fields from the Customers table by clicking on the >> button. Click the
Next button.
b) For the layout, select the Columnar radio button. Click the Next button.
c) For the title of your form, enter CustomersFrm. Select the Modify the form’s design.
option. Click the Finish button.
Because the Customer Number is assigned automatically, you wish to prevent the user from
“clicking” within this field. To “lock” this field, perform the following tasks in the Design view:
3. Select the CustomerNum input field then click on the Property Sheet button under
the Design tab. The Property Sheet will be displayed on the right-hand side.
4. In the Property Sheet, select the Format tab. For the Back Style property, select the
Transparent option. For the Border Style property, select the Transparent option.
5. In the Property Sheet, select the Data tab. For the Locked property, select the Yes option.
Page 11 of 11
6. In the Property Sheet, select the Other tab. For the Tab Stop property, select the No option.
7. You may adjust the formatting of the form and change the labels.
8. Save your form.
9. Select the View pull-down and select the Form View option. You will see your new form.
Create a second form called NewOrderFrm. You will begin by using the form wizard as before,
however, you will have to finish the form by performing some manual design tasks described
below. A sample form is shown here:
10. Click on the Create tab.
11. Click on the Form Wizard option.
d) For the Tables/Queries option, use the pull-down to choose Table: OrdersTbl. Select
the following three fields: OrderNum, OrderDate, and Quantity. Click the Next button.
e) For the layout, select the Columnar radio button. Click the Next button.
f)
For the title of your form, enter NewOrderFrm. Select the Modify the form’s design.
option. Click the Finish button.
The next series of manual design tasks will explain how to add “Bound fields” (fields linked to
data from tables) and “Combo Boxes” to your form. You will also need to move, or rearrange
some of the fields and their labels to resemble the sample shown above. Here are some helpful
hints for moving objects on your forms (and on your report that you will create later).
Helpful design hints
a. Field (Textbox objects) and their label objects are
linked together
b. If you wish to move both together as one unit, select
the field object and place your cursor on the field’s
border so the cursor’s shape becomes a 4 arrows.
c. You can move the label object independently by
moving this handle.
d. You can move the field object independently by
moving this handle.
e. You can select more than one object by holding down the Shift key.
f. You can align multiple selected objects by right clicking and choosing Align.
Page 12 of 19
12. Set the following properties (using the Property Sheet) for the OrderNum and OrderDate field
objects:
Tab
Property
Value
Format
Format
Data
Other
Back Style
Border Style
Locked
Tab Stop
Transparent
Transparent
Yes
No
13. Set the following property for the OrderDate field object:
Tab
Property
Value
Data
Default Value
=Date()
14. Arrange the three label/field groups, which are currently on your form, as shown in the sample at
the top of the page.
15. For the Customer Name and Customer Number, we want to add a combo box. Under the
Design tab in the Controls section, select the Combo Box icon.
a) Drag and outline the location for the object on your form. The Combo Box Wizard
window will open.
b) Select the I want the combo box to get the values from another table or query
option. Click the Next button.
c) For the table or query question, select the Table: CustomerTbl option. Click the Next
button.
d) Using the > button, add the following two fields: CustomerNum and CustomerName.
Click the Next button.
e) Sort by CustomerName in Ascending Order. Click the Next button.
f)
Checkmark the Hide key column checkbox. Click the Next button.
g) Select the Store that value in this field option and select CustomerNum. Click the
Next button.
h) Enter Customer Name for the label. Click the Finish button.
i)
In the Property Sheet, click on the Others tab and set the Tab Index property to 0.
16. For the Employee Name and Employee Number, add a combo box by using the Combo Box
wizard. Use the same steps as above with the following exceptions:
a) For the table, select Table: EmployeesTbl.
b) Select the following two fields: EmployeeNum and LastName.
c) Sort by Last Name in Ascending Order.
d) Store the value in the field EmployeeNum.
Page 13 of 19
e) Label the field Employee Name.
f)
Set the Tab Index property to 1.
17. For the Item, add a combo box by using the Combo Box wizard.
a) For the table, select Table: ProductsTbl.
b) Select the following two fields: ItemNum and ItemName.
c) Sort by Item Name in Ascending Order.
d) Store the value in the field ItemNum.
e) Label the field Item.
f)
Set the Tab Index property to 2.
18. For the Quantity field, make sure the Tab Index property is set to 3.
19. Save your form design
20. View your Order Form in Form View.
21. Use the New (blank) record icon at the very bottom of the form to add 4 more orders.
Page 14 of 19
Part 6 – Build Queries to Access Data
You will create two queries. Both queries will extract data from at least two of the tables and sort the
resulting information to be displayed. Both queries will also include a formula.
1. Click on the Create tab.
2. Click on the Query Wizard option.
a) In the New Query window, select the Simple Query Wizard option. Click the OK
button.
b) For the Tables/Queries option, select Table: OrdersTbl. Select the following two fields:
OrderDate and Quantity.
c) For the Tables/Queries option, select Table: ProductsTbl. Select the following two
fields: ItemName and RetailPrice. Click the Next button.
d) Select the Detail radio button. Click the Next button.
e) For the title of your query, enter Orders for March Query. Select the Modify the query
design. radio button. Click the Finish button.
3. Select the Quantity column and drag it between the ItemName and RetailPrice columns.
4. Within the OrderDate column, set the Criteria for the query by typing Between #3/1/12#
And #3/31/12# in the cell on the Criteria row.
5. Sort the information by date by selecting Ascending from the pull-down on the Sort row within
the OrderDate column.
6. Add a new column of information for the Total. On the Field row in the first black column, enter
the following: Total:[Quantity] * [RetailPrice]
Page 15 of 19
7. Switch to the Datasheet View to view the query results.
Create a second query to list orders by customers. A sample is shown here.
8. Using the Simple Query Wizard, create a query named Orders by Customers Query.
9. The fields for your query will be taken from three tables:
Table
Field
CustomersTbl
CustomersTbl
OrderTbl
OrderTbl
ProductsTbl
CustomerNum
CustomerName
ItemNum
Quantity
RetailPrice
10. Create a field for Total and use an appropriate formula to calculate the price.
11. Place the columns of information in the order shown in the sample above.
12. Sort the information by both customer name and total price. The CustomerName should be
sorted in Ascending order and the Total in Descending order.
13. Switch to the Datasheet View to view the query results.
Page 16 of 19
Part 7 – Create a Report
You will create one report that collects at least one field of data from three of the tables.
Formulas will be used to keep a running total and to calculate a grand total for the report. The
name of your report will be lastname firstname created mm/dd/yy.
You will create a report of Accounts Receivables, in landscape format, using the Reports
Wizard. Add formulas for Totals, Running Totals, and a Grand Total. You may want to review
the helpful design hints given in Part 4.
1. Click on the Create tab.
2. Click on the Report Wizard.
a) The fields for your query will be taken from three tables. Add them in the following order:
Table
Field
CustomersTbl
CustomerName
ProductsTbl
ItemName
OrdersTbl
Quantity
ProductsTbl
RetailPrice
b) For viewing the data, select by OrdersTbl.
c) For sorting, select CustomerName in Ascending order.
d) For the Layout, select Tabular.
e) For the Orientation, select Landscape.
f)
For the Title, enter lastname firstname created mm/dd/yy.
g) Select the Modify the report’s design before clicking on the Finish button.
3. Add the Total label to your report using the Label tool.
a) Click on the Design tab. In the Controls section, select the Label icon (Aa).
Page 17 of 19
b) Draw the label object within the Page Header section of the report.
c) In the Label box, enter Total.
d) Adjust the size and location of the new object.
4. Add the Total field to your report using the Text Box tool.
a) In the Design tab in the Controls sections, select the Text Box icon.
b) Draw the field object within the Detail section of the report.
c) Access created a Text label within the Detail section. Delete this Text object by rightclicking on it and selecting the Delete option.
d) Select the new Unbound control object. In the Property Sheet, select the Data tab. Set
the Control Source property to =[Quantity]*[RetailPrice]
e) In the Property Sheet, select the Format tab. Set the Format property to Currency.
f)
Adjust the size and location of the new field object as shown in the sample.
5. Add the Running Total objects to the report by adding a Label in the Page Header section and
a Text Box in the Detail section.
a) Repeat steps 3 and 4 you did for the Total label and Total field.
b) Additionally, for the Running Total field object, in the Property Sheet in the Data tab, set
the Running Sum property to Over All.
6. Update the Page Footer section.
a) Remove any objects that are currently in the Page Footer section.
b) Add a horizontal line to the top of the section. In the Design tab in the Controls section,
use the Line option. You can format it by selecting on the Format tab.
c) Add a Grand Total text box. For the Grand Total label, change the text to Grand Total.
d) Select the Grand Total field object. In the Property Sheet, select the Data tab. Set the
Control Source property to the name of the Running Total field object. For example:
=[Text15]
Note: your text number may be different. Check the Name property of the Running Total
object on your report to get the correct name.
e) In the Property Sheet, select the Format tab and set the Format property to Currency.
7. In the Report Header section, change the title of the report to Accounts Receivable by your
name
8. Switch to the Report View and verify that your report resembles the sample and that all values
are calculated and displayed properly.
9. Return to the Design View to make any necessary corrections.
10. Save and close the report.
Page 18 of 19
Submitting the project
1. Your excel file should have one sheet with the flat file database. Your Access file should have
one database with four tables (CustomersTbl, EmployeesTbl, OrdersTbl, ProductsTbl), two
queries (Orders by Customers Query, Orders by October Query), two forms (CustomerFrm,
NewOrderFrm), and one report (lastname firstname created date).
2. You need to submit your Access file (lastname_firstname_access.accdb) and your Excel file
(lastname_firstname_excel_database.xlsx). Put them in the same folder.
3. Use a zip program such as 7-Zip and zip them into one file named
lastname_firstname_access.zip. This is the file that you will submit on Blackboard.
4. Submit your zip file on Blackboard (http://blackboard.usc.edu) under Assignments:
a.
b.
c.
d.
Click on the Lab5 assignment.
Next to Attach File, click on the Browse My Computer button.
Find your lastname_firstname_access.zip file and click the Choose button.
Click on the Submit button in the bottom right corner.
Page 19 of 19
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.