SQL for the Web

There are 12 lessons in this course:

1. Introduction to Databases
   • What is a database
   • DBMS
   • The relational model
   • Primary keys
   • Foreign keys
   • Relationships
   • Normalisation
   • Other normal forms
   • De normalisation
2. Fundamentals of SQL
   • Installing a DBMS
   • SQL
   • The select statement
   • Common errors
   • Identifiers
3. Building a database with SQL
   • Building a database: RAD tool, CSV file, opening database, commands, etc
   • Data types and MS access
4. Retrieving, Storing, Updating and Deleting Data
   • Retrieving data
   • Retrieving from tables with relationships
   • Creating column aliasesEliminating duplicate rows withDISTINCT
   • Filtering rows with WHERE
   • Matching patterns with LIKE
   • Escaped and unescaped patterns
   • Range filtering with BETWEEN
   • List filtering with IN
   • Evaluating conditional values with CASE
   • Sorting rows by ORDER BY
   • Storing, updating and deleting dataUpdating rows with UPDATE
   • Deleting rows with DELETE
5. Advanced SQL database access methods
   • Relational databases
   • Creating outer joins with OUTER JOIN
   • Subqueries
   • Summarising data
   • Grouping rows
   • Using HAVING for filtering rows
   • Set operations
   • Union
   • Intersect
   • Except
   • Handling duplicates
6. Database Security
   • Security is important
   • Triggers
7. Using SQL in applications
   • Using SQL in an application
   • Using SQL in web sites
   • Using SQL in desk top applications
   • Using SQL in mobile devices
   • Embedded SQL
   • SQL injection
8. Cursors
   • What are cursors
   • Preventing updates and deletions
   • Scrollable Cursors
9. Stored procedures
   • Introduction
   • Compound statements
   • Stored functions
   • Stored modules
   • Views
   • Indexes
   • Controlled flow statements
10. Error Handling
    • Stability
11. Dynamic SQL
    • Introduction
    • Execution of dynamic SQL
    • Single step execution
    • Two step execution
    • Dynamic cursors
12. Advice and Tips
    • Common mistakes
    • Assuming the client knows what they need
    • Underestimating project scope
    • Only considering technical factors
    • Not seeking client feedback
    • Skipping beta testing

Each lesson culminates in an assignment which is submitted to the school, marked by the school's tutors and returned to you with any relevant suggestions, comments, and if necessary, extra reading.

Workplace Project

You next must complete a Workplace Project or work experience (approved by a tutor and equal to 200 hours duration)

There are four options available to you to satisfy the Workplace Project Requirement requirement:

Alternative 1.

If you work in the industry that you have been studying; you may submit a reference from your employer, in an effort to satisfy this industry (ie. workplace project) requirement; on the basis of RPL (ie. recognition for prior learning), achieved through your current and past work experience.

The reference must indicate that you have skills and an awareness of your industry, which is sufficient for you to work in a position of responsibility.

Alternative 2.

A one module credit (100 hrs) can be achieved by verifying attendance at a series of industry meetings, as follows:

•  Meetings may be seminars, conferences, trade shows, committee meetings, volunteer events (eg. Community working bees), or any other meeting where two or more industry people or people who are knowledgeable about their discipline.
•  Opportunity must exist for the student to learn through networking, observation and/or interaction with people who know their industry or discipline
•  A list of events should be submitted together with dates of each attended and times being claimed for each
•  Documentary evidence must be submitted to the school to indicate support each item on the above list (eg. Receipts from seminars, conference or shows, letters from committee or organisation secretaries or committee members. All such documentation must contain a contact details)

Alternative 3.

Credits can be achieved by completing standard modules Workplace Project I, II and/or III

Each of these modules comprises a series of “hands on” PBL projects, designed as learning experiences that involve interaction with the real world. (This approach is based upon tried and proven learning approaches that originated in American universities but are now widely used and respected by academia throughout many countries). See the web site or handbook for more detail.

Alternative 4.

If you do not work in the relevant industry, you need to undertake a project as follows.

Procedure for a Workplace Project

This project is a major part of the course involving the number of hours relevant to the course (see above). Although the course does not contain mandatory work requirements, work experience is seen as highly desirable.

This project is based on applications in the work place and specifically aims to provide the student with the opportunity to apply and integrate skills and knowledge developed through various areas of formal study.

Students will design this project in consultation with a tutor to involve industry based activities in the area of specialized study which they select to follow in the course. The project outcomes may take the form of a written report, folio, visuals or a mixture of forms. Participants with relevant, current or past work experience will be given exemption from this project if they can provide suitable references from employers that show they have already fulfilled the requirements of this project.

For courses that involve more than 100 hours, more than one workplace project topic may be selected. For example, 200 hours may be split into two projects each of 100 hours. This will offer the student better scope to fulfill the needs of their course and to meet the number of hours required. Alternatively, the student may wish to do one large project with a duration of 200 hours.

Students will be assessed on how well they achieve the goals and outcomes they originally set as part of their negotiations with their tutor. During each 100 hours of the project, the students will present three short progress reports. These progress reports will be taken into account when evaluating the final submission. The tutor must be satisfied that the work submitted is original.

If the student wishes to do one large 200 hour report, then only three progressive reports will be needed (however the length of each report will be longer).

HOW TO PROCEED

1. Students are expected to select a suitable project or task to complete that allows the student to apply and integrate the knowledge and skills they have obtained as part of their studies.

2. The student should submit a draft proposal outlining their proposed project, study or task. The expected outcomes of this project should be clearly stated. This will be looked at by a tutor and comments made. Students are welcome to visit the school or to talk to a tutor to obtain advice on how to draw up their proposal. The proposal should indicate what the student intends to do, how they intend to do it, where they intend to do it, and what they expect to produce (e.g. a written report, a folio, references from an employer) as a means of showing what they have achieved during their project/study/task.

3. A refined proposal will be submitted by the student incorporating changes based on the comments made by the tutor. This updated proposal will either be accepted as being suitable or further comments made. The proposal may need to be submitted several times before it is finally accepted.

4. The student will then be expected to carry out the project, study or task.

Progress Reports

The student will be expected to submit three progress reports during the duration of the progress. This is in addition to the final project product (e.g. report, folio). Each progress report should show what you have done so far (e.g. what research you have done, what tasks you have carried out, etc.). It should also cover any problems you have had so far, and if so, what you have done to overcome these problems. Each progress report should be in the vicinity of 300 - 500 words in length.

Progress Report 1.

This should be submitted about one quarter of the way through your study/project/task.

Progress Report 2.

This should be submitted about one half way through your study/project/task.

Progress Report 3.

This should be submitted about three quarters of the way through your study/project/task.

Final Report

This report is to be typed and submitted to the school.

The final report should summarise the objective of the workplace project, and be set out like a professional report.

Although content is the most important factor in determining a pass grade for the workplace project, your report should exhibit elements of professional report writing (in regards to spelling, grammar, clarity and presentation).

Final Report Length

For 100 hours Workplace Projects:    this report should be about 1,500 to 3,000 words.

For a 200 hour Workplace Project:     this report should be about 3,000 to 5,000 words.