DEPARTMENT OF BUSINESS AND TECHNOLOGY
|Professor:||Floyd Jay Winters||Email:||WinterF@scf.edu (please use Canvas email)|
|Office:||Bldg 18 Rm 131||Best Office Hours:||Best if after class|
| This is a live, interactive syllabus. Do NOT print.
Below (in blue background) are quick links for all assignments with detailed
Grading Criteria, directions, keystrokes, and specific point values for all assignments:
Click here for 30 Second Welcome YouTube Video from Professor Floyd Jay Winters
|Assignments: Photo | Design | Table | Form | Query | Report | Tables | Forms | Queries | Reports|
Course Description: Database Design and Implementation (3) (A.S.). Three hours of lecture per week, plus laboratory as assigned by instructor. Prerequisite: CGS 1000 or CGS 1570 or COP 2170 or permission of instructor. Students will study relational database design principles in the context of a popular database management software package. Relationships, entity-relationship diagrams and normalization will be emphasized. Implementation will include the creation of tables, queries, forms, reports, macros and programs.
During the course you will be analyzing, designing, and creating a business Database to support a particular business. The business can be real or make-believe. But you will work with the same business and same database throughout the course, and in the end create a valuable Portfolio Project.
Course Performance Standards: Access course performance standards by clicking on the link below:
Email: So that I know what class you are from, and so we have a good record of our communication:
You must use Microsoft Office 2007 or 2010 or
2013 or Office 365 for your projects. If Microsoft Office
software is not available from a PC at home or work, you may use the
Computer Lab at SCF. Sorry, but you can NOT use Microsoft Works or another program.
Remember, Life happens: If you must go out of town take a laptop or use an Internet Cafe or a hotel computer or the computer of whom you are visiting. So START EARLY! Use the SCF computer lab if necessary.
If you have problems: Be specific - give the exact message and exactly what you were doing when it happened. When appropriate, tap the [Print Screen] key to capture your error message or problem screen, then open Paint (Start > Programs > Accessories > Paint) and paste the screen copy into Paint (Ctrl + V). Save it as a JPG [File > Save As > Save as type: JPEG (*.jpg)] and attach the JPG screen capture
file to me in an email so I can see exactly what is happening. Also, feel free to visit me in my office in 18-131.
Finally - if you wait till the last minute, I probably cannot help you solve your problem before it is due. So be sure to do your work early.
Computer Access: You can do all assignments and take all exams in the SCF computer lab. If you plan to do your work at home, you must have access to a Computer connected to Internet and Microsoft Office. You are also expected to have "backup computer" plans: at a friend's computer, a relative's computer or at the library. Establish computer plans in case your usual computer access is disrupted during the semester. The course will continue to move along regardless of whether your computer works or not. Be sure your virus scanner is updated frequently to avoid problems during the semester. If your computer does not work for any period of time during the semester, you are expected to find a way to do the work.
SCF Helpful Links:
Standards of Conduct: Students are expected to abide by all SCF Student Handbook guidelines.
Withdrawal Policy: In accordance with the State College of Florida policy as stated in the college catalog, students may withdraw from any course or all courses without academic penalty of a WF by the withdrawal deadline as listed in the State College of Florida academic calendar. See the online Academic Calendar for important dates. The student must take responsibility for initiating the withdrawal procedure. Students are strongly encouraged to talk with their instructors first before taking any withdrawal action. In addition, students should note that faculty may also withdraw students for violating policies, procedures, or conditions of the class, as outlined in individual class syllabi, and such action could affect financial aid eligibility.
Statement of Plagiarism: Plagiarism is the use of ideas, facts, opinions, illustrative material, data, direct or indirect wording of another scholar and/or writer -professional or student- without giving proper credit. Expulsion, suspension, or any lesser penalty may be imposed for plagiarism. Copying another student's work will result in a zero for both students.
Disability Resource Center Information: SCF has a Disability Resource Center (DRC) to assist students. To request academic accommodations due to a disability, please contact the DRC at 752-5295 in Bradenton or 408-1448 in Venice, or by email at DRC@scf.edu. If you have already requested academic accommodations through the DRC for this course please meet with me to discuss your specific needs.
Course Evaluation: All assignments and
their grading criteria are shown below.
It is the student's responsibility to keep track of your grades and averages throughout the semester.
If you have questions, feel free to visit me after class.
The link below is a very handy link of Microsoft Office Shortcuts:
R Aug 27
|All Assignments must be submitted online through
During the entire course you will be doing one Database project to support a particular business. The business can be real or make-believe. But you will work with the same business and same project throughout the entire course, and in the end create a Portfolio Database Project. So before you start, carefully think about what business you will adopt. Past examples: Beauty shop, Paint ball park, Computer repair, Custom jewelry, a Tattoo parlor, a Lawn service, Pet service, Auto Detailing, …
|If your computer is not properly working or
configured to use Microsoft Access, then go to the SCF lab or
to friend's home to do your work.
See below for how to use the MOS certification test to replace missing or low grades or your final exam.
For a 1 page pictorial summary of a database see: 0ClassFolders/1543DB/DatabaseTerms_diagram.pdf
For extra credit there will be an Canvas Photo assignment, an online Office and Windows Shortcut quiz, an online Windows Quiz.
There will be a quiz at the beginning of almost every class.
We will do a practice grade on the next project that will be due after the quiz.
M Sep 07
|Upload Your Photo to your Canvas Profile
There is no Photo Assignment dropbox. But please add your photo to Canvas so that your professors can learn your name easily.
R 10 pt Quiz
01 Gather a list of
reports, records, forms, invoices, pay stubs, etc... that you would collect as you prepare to
design a database. Then using Microsoft Excel, logically enter and arrange this information to plan your database Tables, Fields, Key Fields, Data Types and Field Sizes. Give this Excel file an appropriate name, such as
WinchesterWebsites_DesignPhase. (See sample in text book)
Key Term: Normalization - Break your data into the smalles logical pieces. For instance, do not use Name; instead use LastName and FirstName.
For a sample see:
R 10 pt Quiz
02 Using Microsoft Access create
at least three related tables in Design View that will be needed for your database.
(Do NOT design a
File Database which has repetitive data entry, resulting in
unnecessarily large file sizes and data entry errors.) Give
your file an appropriate name, such as WinchesterWebsites.
Start to set your relationships now. See page AC06.3 in your textbook. Also see:
When done - Go back the to Tables assignment dropbox.
R 10 pt Quiz
03 Using the same file that you submitted
last week, create at least two forms that will be
needed for your database. Because you may have an
Invoice and a Invoice Details or an Orders and an
Orders Details or a Payroll and a Payroll Details,
which will require subforms, it may be better to save these forms till
later in the semester when we cover subforms. Instead do at least two
forms such as very professional Employees, Customers, Clients, Products,
Services, Inventory... forms
10 Make sure you have attractive form headers that include both a appropriate Titles and Subtitles.
10 Neatly align all labels and text boxes
10 No spaces for textbox field names, however use appropriate spaces in associated labels (ie: Field name, without spaces= FirstName, Label with space=First Name)
10 Appropriately set the width and height properties for all labels and text boxes
10 Appropriately space horizontally and vertically all labels and text boxes
10 Apply the same attractive Theme to all forms
10 Use the Property Sheet to appropriately format ALL numeric fields
10 Use the Property Sheet to set at least two different distinctive fonts and two different distinctive colors for each form
* 10 Add at least one unbound control to each form
10 Add an image or logo to each form
10 Make sure each forms Tab Order is top to bottom and left to right for all forms.
* In the message section of the Canvas assignment Dropbox, list and discuss how and where you added the unbound control.
R 10 pt Quiz
04 Using the same file that you submitted
last week, create at least two queries that will be
needed for your database.
10 Create at least two queries in Design View which omit one of more table fields and which have at least one or two fields in a different order than appears in the tables
10 Create at least one query which links to fields from more than one table
10 Create at least one query which uses at least one of the following criteria expressions:
< or > or <= or >= or < >
10 Create at least one query which uses at least one "Between" criteria expression based on a date.
Example: Between #06/05/2011# And #06/06/2011# But field must be set as a Date Datatype
10 Add at least one appropriately formatted calculated field (Ex: SubTotal: [Price]*[Quantity])
10 Use at least one wildcard such as a * or ? or Like criteria
Example: you type Jeff* Access displays Like "Jeff"
Example: you type Like "?ill" Access displays Bill and Will
10 Use at least one AND or OR Criteria (Use Criteria Row)
10 Sort at least one query (Use Sort Row)
10 Add at least one GrandTotal, Minimum, Maximum, Average or Count Total (Use Total Row)
10 Create at least one Crosstab Query. Include the word Crosstab in the query name.
Set your relationships now. See page AC06.3 in your textbook. Also see:
-5 to -10 points will be deducted for each relationship problem:
R 10 pt Quiz
|05 Using the same file that you submitted
last week, use the Report Wizard to create at least two Attractive and Professional Reports that will be needed for your
Note: If the Access Wizard does not display all fields, start again and the next time do not add as may fields or dislay report in Landscape.
10 Include both a Title and Subtitle in the Header of each report
10 Appropriately set the width and height properties for all labels and text boxes and appropriately space horizontally and vertically all labels and text boxes
10 Apply the same attractive theme to all reports
10 Use the Property Sheet to appropriately Format ALL numeric fields
10 Use the Property Sheet to set at least two different distinctive fonts and two different distinctive colors for each report
10 Add at least one Unbound Control to each report
10 Add a relevant Logo to each report
10 Create at least one attractive report which includes Grouping by a key field
(Examples: Sales by EmployeeID, Customers by State, Employees by Admin or Sales)
10 Create at least one attractive report which includes a Line Total for a numeric field
(Calculations can be easy when you build a report from a query with calculations.)
10 Neatly display the page number and date in the Footer of each report
-10 points will be deducted for each relationship problem:
Every table should somehow be connected directly or indirectly to all other tables.
Ex: Orders connected to Order Details which is connected to both Products and Customers. Each of the tables above must be in a One-To-Many relationship: One Product can be sold many times; one Customer many have many purchases.
|R Oct 8||Work in pairs and in class with professor to make sure your database, and especially its relationships, are working properly.|
R 10 pt Quiz
|06 Using the same file that you submitted
last week, create or modify at least two tables that will be needed for
your database, meeting the following criteria:
10 All Relationships are properly set for all tables: key field linked to key field
10 Most, if not all, tables will have one-to-many relationships (1- ∞)
(These were probably already set from earlier lessons after in class lectures)
10 Enforce Referential Integrity for most, if not all, tables (cannot sell a product that is not on Products table)
PLEASE DO ALL OF THE FOLLOWING ON ONE TABLE
* 10a Add at least one Yes/No Data type (Ex: College Degree, Certified, Tax Exempt)
* 10b Set Alternate Row Color for at least one table (Home > Text Formatting)
* 10c Make at least one field Required at least one table (See Required field property in Design View)
* 10d Set a Default value in at least one table (See Default field property in Design View)
* 10e Set an Input Mask in at least one table (ex: Date, Social Security, Phone)
* 10f Set a Data Validation rule and message in at least one table (Validation Rule & Text property)
* 10g Use a Lookup Wizard in at least one table (It is a Data Type found in Design View)
* 10pts In the message section of the Canvas assignment Dropbox, list a-f give the TABLE where you a. added a Yes/No data type, b. set gridlines, c. made at least one field required d. set a default value, e. input mask...
|100 Points 40 Question Multiple Choice Exam. For Review see: Midterm Review Terms
Note: there is a built-in extra "hurricane/snow date." I may move the last assignments up one week.
R 10 pt Quiz
07 Using the same file that you submitted
last week, create or modify at least one form (preferably the Invoice or Orders or Services form) that will be needed for
your database, meeting the following criteria:
(It may be easier to create the new form and Subform from scratch at the same time.)
30 Add an attractive, appropriate, formatted and professional Subform to a form using related tables. (Ex: Create > Forms > Forms Wizard then choose desired fields from Invoice table, then choose desired fields from InvoiceDetails table.) Use an appropriate and descriptive Title and Subtitle. The subform should have a linked lookup feature that allows you to select products or items from the first column which will automatically populate some of the columns to the right. Ex: Chose ProductID from dropdown in the 1st column to automatically fill the associated values for Description and Cost in the 2nd and 3rd columns.
20 Use Calculated Totals on the Subform.
(Ex: In the subform, add a textbox in the Control Source Property enter = [Price] * [Quantity])
10 Add the Current Date to your Form that contains the Subform
(Choose Design > Header/Footer and double-click on Date and Time. It will place the Date and Time Control in your Header. Drag it to where you would like it, such as in the Form Footer.)
* 10a Lock a form field (Select field, choose the Data tab in Property Sheet, set Locked Yes)
Set the color of the locked field to red.
* 10b Add a Control Screen Tip to the Locked field
(Select field, choose the Other tab in Property Sheet, set ControlTip)
* 20c Create a Pop Up Form (Create > Forms > Forms > in Property Sheet choose Other tab > Pop Up)
Include PopUp in the Form Name, ex: Customers PopUp form.
* In the message section of the Canvas assignment Dropbox, list a-c and discuss how and where you added these features (specifically what form and what fields)
R 10 pt Quiz
08 Using the same Access database file that you submitted
last week, create or modify at least two queries that will be needed for
your database, meeting the following criteria:
R 10 pt Quiz
09 Using the same file that you submitted
last week, create or modify at least two reports that will be needed for
your database, using an appropriate and descriptive Title and Subtitle, meeting the following criteria:
BEFORE STARTING THIS ASSIGNMENT: BACK UP YOUR DATABASE!
10 Add an attractive and consistently placed and descriptive Title, Subtitle and Theme to ALL the reports in your database, including the imported ones.
10 Add an attractive and consistently placed Logo to ALL the reports in your database, including the imported ones.
10 Download http://faculty.scf.edu/winterf/0ClassFolders/1543DB/WageDemo2010.accdb
10 Import the Employees table from the WageDemo database. (If you already have an Employees table the new imported table will be named Employees1) Rename the imported table as ImportEmployees
(External Data > Import and Link)
20 Import the Employees List report from the WageDemo database. Notice it will not work. You will have to set the record source of the imported report to ImportEmployees.
20 Create and add a subreport to a new or existing report
Practice doing this with a backup of your database: Ctrl + C, then Ctrl + V before you do this on your working database. There are several ways to do this listed below. Try more than one method and submit the Report and Subreport that looks and works best. Make sure the final report has a Title, Subtitle, dates, page numbers and every thing is professionally spaced and aligned.
One way to do this is to build a query that includes multiple related tables such as Invoice, Invoice Details, Employees, Customer, and Products table. (Do not include all fields). be sure to add a column to calculate price times quantity. Then you can build your report and subreport from the query sing the Report Wizard, choose the Summary option.
Another way is to: Select a table > Create > Report > Design > Controls > Subreport then click in the Details section then draw the subreport and use the wizard to create a subreport from existing related tables.
Or: Select a table > Create > Report > Design > Controls > Subreport then click in the Details section then draw the subreport and use the wizard to choose an existing subform - be sure to do an appropriate calculation.
Name the report as AR_Report
Ex: Create an Employees Report, then in the detail section add a subreport control, then use the wizard to add the Invoice Details table to summarize all invoices that the employee sold.
The subreport shrinks or grows depending on the number of detail lines it contains
10 Save a subreport as a report (File > Save As > Save Object As) name it AR_SaveSubreport
10 Run the Performance Analyzer on all the reports in the database and fix any Recommendations that Access lists (Database Tools > Analyze > Analyze Performance)
Do a screen capture of this report (Alt + PrintScreen) to capture the active window - then paste into Paint and save the file as a .jpg or pasteinto Word and save, or use the Snipping Tool and save as a .jpg. Upload your database and the Performance Analyzer .jpg file to the assignment dropbox.
M Nov 23
R 10 pt Quiz
10 Using the same file that you submitted
last week, add an Interface and Startup feature, meeting the following
R 10 pt Quiz
11 Using the same file that you submitted
last week, demonstrate that you can import and export, meeting the
10 Export your main table to Excel and name it ExportedTable.xlsx.
Upload in this order 1. your access Database
Payroll_Import and Payroll_Linked, 2. ExportedTable.xlsx, 3.
ExportedReport.pdf, 4. ExportedWebPage.htm, 5. PublishedWordTable, 6.
MailMerge.docx with the file that
shows the chevrons « ».
Please do before.
12 Using the same file that you submitted
last week, demonstrate that you can meet the following criteria:
* Create navigation buttons for a form (Previous, Next, First, Last, Find)
Design View > Controls > Button
* Create a macro that displays a message when it is run
Create tab > Macro
* Add a command button to a form that runs a macro that displays adaptable reports
* Make a copy of an object and then rename the copy
* Delete the copied object
* Make a backup copy of your database
* Analyze the performance of your database
* Run the Database Documenter on at least four objects within your database
* Run the Compact and Repair Database function
File > Info > Compact and Repair Database
* Set a password for your database (be sure to write down or store your password)
First File > Open > Browse > Open drop down > Open Exclusive
File > Info > Encrypt
|R Dec 10
Final Exam Review See 0ClassFolders/1543DB/CGS1543 Final Exam Review Terms.pdf
or http://faculty.scf.edu/winterf/0ClassFolders/1543DB/CGS1543 Final Exam Review Terms.docx
|R Dec 17
|Final Exam 400 Points|
|Replace a missed assignment or low grade:
Due anytime up to five days before finals.
A Microsoft Office Specialist Certificate (MOS) will look great on your resume and help your average.
As a way to raise your average and replace a low grade or to use in place of your final exam, you may choose to take the Microsoft Office Specialist Certification Access exam at the Lakewood Ranch campus or any other authorized testing center. When you purchase an exam at many testing centers, it also often includes a free retake. This is a nationally recognized Microsoft Office certification exam. You will have to email me (firstname.lastname@example.org) the URL that links to your Authenticated Digital Transcript of Certification from the Certiport secure site to verify the test results. (You should be given this link after passing the exam.) If you pass you will get a 100 for a low grade. If you do not pass, you will get whatever score is on your test results - mail me a copy of your printed results if you do not pass (a 65 is much better than a 0). You can use this grade to replace a missing project or your lowest grade. It will be used and averaged in at the end of the semester. Cost at Lakewood Ranch campus: $98 (includes a free retake if you fail it the first time). For SCF Lakewood Ranch MOS test registration information call: 941-363-7199 or 941-363-7254. Click here for MOS Exam Hints.