SQL DATABASES BIT201

COURSE STRUCTURE
There are 12 lessons as follows:

1. Introduction to Databases -What is a database, DBMS, The relational model, Primary keys, Foreign keys, Relationships, Normalisation, Denormalisation
2. Fundamentals of SQL -Installing DBMS, SQL, The select statement, Common errors, Identifiers
3. Building a database with SQL -Building a database, Data types in MS Access
4. Storing & Retrieving Data -Reviewing data, Retrieving data from tables with relationships, Creating column aliases, Eliminating duplicate rows, Filtering rows, Matching patterns, Escaped and unescaped patterns, Range filtering, List filtering, Evaluating conditional values, Sorting rows, Sorting, updating and deleting data,
5. Advanced SQL database access methods -Relational databases, Creating outer joins, Sub queries, Summarising data, Grouping rows, Set operations
6. Database Security -Privelige heirachy, Triggers, Security etc
7. Using SQL in applications -Using DQL in an application, Embedded SQL, Object oriented RAD tool, etc
8. Cursors -What are cursors, scope of cursors, benefits ogf a cursor, scrollable cursor, etc.
9. Stored procedures -Stored procedures, Compound statements, Stored functions, Stored modules, Views, Indexes, Controlled flow statements
10. Error Handling -Stability, Constraint violation, Exception, etc
11. Dynamic SQL -What is dynamic SQL, Executing dynamic SQL, Dunamic cursors.
12. Advice & Tips -Common Mistakes: Assuming client knows their needs, Underestimating project scope, Only considering technical factors, Not asking client for feedback, etc.

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.

AIMS
On successful completion of the course you should be able to do the following:

• Understand the concept of relational databases.
• Understand the fundamentals of SQL.
• Build and maintain a database with SQL.
• Define how to store data in a database using SQL.
• Understand advanced and more efficient ways of working with databases in SQL.
• Keep databases secure with SQL.
• Understand how to use SQL in real world applications.
• Define how to use cursors to work with data in a database.
• Understand how to re-use common code and develop efficient database driven applications with the use of stored procedures.
• Define the benefits of error handling and how to implement it.
• Define how dynamic SQL works in applications.

WHAT THE COURSE COVERS
Here are just some of the things you may be doing:

• Explain the difference between a flat file and relational database.
• Define DBMS.
• Write an SQL statement that will perform a set task.
• Explain the difference between an Integer and Numeric data type.
• Learn what the four DDL commands are.
• Define set function.
• Write the SQL code needed to create a database for a car sales yard.
• What is the importance of a primary key.
• What is a RAD tool?
• Gain an understanding of referential integrity rules.
• Give an example of a logical connective and how it would be used in an SQL select statement.
• Learn the appropriate use of the keyword select.
• Learn about JOINS.
• Explain the access privilege hierarchy.
• Learn how to declare ROLE.
• Write an SQL statement for takes away or grants rights to a member of a particular list.
• Explain Embedded SQL.
• What is 3 tier web database architecture?
• Explain the benefits vs disadvantages of using a web based database application rather than a client based application.
• Develop a list of code needed to send a personalised email out to each customer.
• Explain the benefits of using cursors.
• What clause do you need to use to prevent updates and deletions within the scope of a cursor?
• Develop a stored procedure for creating an employees table.
• Explain the difference between stored procedures, stored functions and stored modules.
• Develop error handling.
• Explain what an exception is and how to handle one with SQL.
• Describe the differences between dynamic and static SQL.
  Extract from Course Notes:

  What is a database?

  A database is simply a place where you can store data. The most basic form of a database is a card file system, where information of different contacts or clients are written on cards that are then stored in alphabetical order. Each card is called a 'record' and each record contains 'fields' such as 'name', 'address' and 'phone number'.

  Databases come in all sizes, from simple collections of a few records to large systems holding millions of records. A Personal Database is designed for use by a single person on a single computer. Such a database usually has a simple structure and a relatively small size, whereas a Departmental or Workgroup database is used by members of a single department or workgroups within an organisation. This type of database is generally larger than a personal database and far more complex in its structure, by having the functionality to enable multiple users to access the same database simultaneously. Bigger still, are Enterprise databases, which are used to model critical information flow within entire large organisations.

  When computers were first used in business applications, database systems were developed to store data electronically. This information was usually stored in a text file where each record had a 'record number' that allowed the computer to easily find the information you need.

  As computers became more advanced, the demand for more powerful software grew. The old database systems (known as 'flat file' databases) became redundant as 'relational' databases were developed. Relational databases allowed multiple sets of records (known as tables) to have data that related to each other.

  For example, you may have a number of 'suppliers' and a number of 'customers'. In the past, you would need to have two separate databases that would not be able to talk to each other.

  With the use of a relational database, you could have two separate tables that have a common 'category'. You could then link suppliers to customers via this category to match which products suit which customers. If a supplier offers a special, you could easily retrieve a list of customers that match that supplier to let them know of the special.

  Whatever the size of the computer that hosts the database and regardless of whether the machine is connected to a network the flow of information between the database and user is the same. The DBMS (Database Software) masks the physical details of the database storage so that the application need only concern itself with the logical characteristics of the data, and not how the data is stored

  DBMS

  A DBMS (or Database Management System) is a software program that acts as a 'go-between' (interface) for the database information. When an application requires data, it sends its' request to the DBMS which searches the database records for the relevant data and then returns the result back to the application.

  You can think of a DBMS as a librarian. You go to the library and ask them "have you got any books on car engine repairs". The librarian then goes and looks through the shelves, finds all the relevant books and brings them back to you. Without the librarian, you would need to search through all the shelves yourself, which is slow and inefficient.

  DBMSs make life easy and reduce the time it takes for an application to process data.

  It is important to understand the difference between a Database and a DBMS. Many people confuse the two or do not realise there is a difference which can cause confusion.

  DBMSs come in all shapes and sizes with different features and uses. Many have features that are unique and almost all DBMSs are incompatible with each other (although some have partial compatibility).

  Some examples of popular DBMSs are:

  • Microsoft Access
  • Microsoft SQL Server
  • Microsoft MSDE
  • Oracle
  • Sybase SQL Server
  • MySQL

  There are many other DBMSs around too. Some are general ones used for a variety of applications and some are specifically designed for certain applications (such as banking and finance).

  This lesson covers the basics of relational databases, which is relevant for most new DBMSs. As SQL is one of the most popular database languages, it is relevant to many DBMSs but this course will focus on SQL use in Microsoft Access.

  The reason for this is Microsoft Access has an easy to use and understand 'front end' and skills developed can easily be carried across to Microsoft SQL Server or MSDE which are more heavy duty DBMSs.

  The Relational Model

  To become proficient with SQL, the User/Programmer should be familiar with the Relational Model i.e. the design that all modern relational databases are built upon. The foundation of the relational model, Set Theory, describes how to perform common algebraic operations (such as Unions and Intersections) on database tables in much the same way as they're performed on mathematical sets. Tables are similar to sets: they're collections of distinct elements having common properties. The structure of the relational database designed using a relational model, is effectively the combining of two dimensional tables, which contain columns and rows of data, which relate to each other by way of a common data structure (Primary and Foreign keys). The database serves the purpose of combining data sets in order to summarise and manipulate the stored information held within the tables.

ACS is a Vocational School and Technical School; offering Online Training Courses in Computing. Learn about IT, by External Studies.

Why choose this course?
This course provides a comprehensive introduction to SQL. By the end of the course, the student should be able to easily understand all the major aspects of SQL.
Is this course right for me?
This course is most suitable for programmers and web developers who are looking to expand their skills with SQL server