isd

**Information Systems and Databases**
toc **The Essentials **

Glossary of Key Terms
click here to edit media type="custom" key="10638466"

**Week 1 - Information Systems and Examples of DBMS **

 * Please place the following work in your Onenote page – Information Systems or your paper based notes. **


 * Worksheet 1 - Click here and view the worksheet entitled Information Systems: Purpose and Types
 * Complete Questions 1-4 about the School Library System "Oliver". Click [|here] to see "Oliver"
 * Construct a Context diagram of the Oliver system.
 * Construct a Data Flow Diagram for a user borrowing a resource from the Library
 * Click [[file:iptnow/oliver_2011.pdf|oliver_2011.pdf]] to see the Presentation PDF

Solutions media type="youtube" key="gWE_NtLgFBg" height="288" width="512"
 * By the end of this week, you should have defined at least 10 terms in the [|Glossary] List.
 * Compulsory terms to be defined for next week include Flat File database, File, Field, Record, Character, Tuple,
 * Provide examples of each of the types of Information Systems eg. TPS, DSS, MIS, OAS


 * Worksheet 2 - View the worksheet Examples of Database Information Systems. Complete the associated activities

(PP108-117 of your text)
 * In groups of 3, you will prepare a presentation on DBMS used in the context of the RTA, the School Admin System [|(SAS200)] and a Video Store.


 * You must include in your presentation:
 * An Information System Diagram where each element can be hyperlinked to a slide/page to explain the component eg. Environment
 * A context diagram of the information system
 * Type of information collected and how long it is stored
 * Data Security - who has access to the information and how is security maintained?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Backup - what disaster management plan does the organisation employ?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">A 1 A4 page Google Docs summary sheet which can be shared with the rest of the class.

Text resource

**<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Week 2 - Examples of DBMS and Flat File Databases **

 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Please place the following work in your Onenote page – Database Information Systems or your paper based notes. **
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Worksheet 3 - Click [[file:flat_file.pdf]] and read the sheet
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Complete the Anderson's Real Estate Activity. - Click [[file:Flat File Database intro anderson realestate.pdf]]

<span style="color: #333333; font-family: Arial,sans-serif;">media type="youtube" key="Tlhk0O-2l2A?version=3" height="480" width="640"

<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;"> <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Read pages 120 -123 of your text. Click [[file:iptnow/flat_file.pdf|flat_file.pdf]]
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Complete HSC style question on page 123
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Worksheet 4 - [|The Flat File Database] - click and complete the activity
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Explain in one paragraph the diagram which appears below giving examples of a file, record, field and character in relation to the real estate agent's database completed above.
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Construct a data dictionary suitable to store your CD collection - //use the diagram and explanation below as your guide.//
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Construct the Flat File database as designed in your data dictionary and populate it with 20 records...Not CD's :)
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">The hard copy of the White pages is an example of a Flat File database. **Construct** a PMI (Plus, Minus, Interesting) for this example of a Non-Computer database.
 * **<span style="font-family: Arial,sans-serif; font-size: 12pt;">Plus ** || **<span style="font-family: Arial,sans-serif; font-size: 12pt;">Minus ** || **<span style="font-family: Arial,sans-serif; font-size: 12pt;">Interesting ** ||

**<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Week 3 - Relational Databases/Organisation of Database Systems **

 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Anderson's Normalisation Lesson **




 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Please place the following work in your Onenote page – Organisation or your paper based notes. **
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">View the Access Introduction in this Youtube Clip

<span style="color: #333333; font-family: Arial,sans-serif;">media type="youtube" key="AUmr5O1ss8Y" height="324" width="576"


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">What is the main disadvantage of a Flat File Database?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">How does a Relational Database structure seek to address this disadvantage?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Watch the clip on creating a relationship in Access

<span style="color: #333333; font-family: Arial,sans-serif;">media type="youtube" key="1CZrCx3egsM" height="324" width="576"


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Movie Time Database - A relational database to serve a purpose. Click [|here] to download Movie Time
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Worksheet 5 - [[file:relationships.pdf]]
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Relationships Presentation - Download [[file:Linking Database Tables.ppt]]

<span style="color: #333333; font-family: Arial,sans-serif;">media type="custom" key="10985826"


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Define the term referential integrity. (p134 of your text)
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Why is referential integrity important in a relational database?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Give an example of how you applied referential integrity in the database you constructed.

<span style="color: #333333; font-family: Arial,sans-serif;">media type="youtube" key="nQwgtLQG0hU" height="324" width="576"
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Normalising the Database **
 * **<span style="font-family: Arial,sans-serif; font-size: 10pt;">Normalisation **<span style="font-family: Arial,sans-serif; font-size: 10pt;"> - Normalisation is a process used in the design of relational databases where we attempt to minimise data duplication by breaking the database up into a number of smaller linked tables. Each table should include fields, which are solely dependent on the primary key set in each table. If a table were to contain unnecessary data duplication (ie redundant data), there exists the possibility that these data elements would not be kept up to date, leading to a data integrity problem. Databases represented in 3rd Normal Form do not have any integrity issues because their structure prevents redundant data being stored.


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Worksheet 5 - Click [[file:understanding normalisation.pdf]]to read and complete the following worksheet on Normalisation


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Searching, Selecting and Sorting **
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Databases can be searched for specific data. The search process fo searching databases utilises a query language. Some DBMS use their own query languages, or query by example.
 * <span style="font-family: Arial,sans-serif; font-size: 12pt;">Field: || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Given Name || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Surname || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Reg: No ||
 * <span style="font-family: Arial,sans-serif; font-size: 12pt;">Table || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Players || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Players || <span style="font-family: Arial,sans-serif; font-size: 12pt;">Players ||
 * <span style="font-family: Arial,sans-serif; font-size: 12pt;">Show || <span style="font-family: Arial,sans-serif; font-size: 12pt;">√ || <span style="font-family: Arial,sans-serif; font-size: 12pt;">√ || <span style="font-family: Arial,sans-serif; font-size: 12pt;">√ ||
 * <span style="font-family: Arial,sans-serif; font-size: 12pt;">Criteria ||  || <span style="font-family: Arial,sans-serif; font-size: 12pt;"><"LZZ" || <span style="font-family: Arial,sans-serif; font-size: 12pt;"><"a9999" ||
 * <span style="font-family: Arial,sans-serif; font-size: 12pt;">Or: ||  ||   ||   ||
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Experienced users tend to prefer to use Structured Query Language (SQL). SQL is a non-proprietary language which enables users to execute queries across a number of tables.
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">The Four key words in SQL are
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">SELECT
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">FROM
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">WHERE
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">ORDER BY

<span style="color: #333333; font-family: Arial,sans-serif;">SELECT: - this enables the user to select the attirubutes or fields which will be used in the query eg. Surname.Student_Details FROM: - this enables the user to select the entity from which the attributes are retrieved. eg. Student_Details, Classes WHERE: - the condition on which the data will be sorted eg. Class_id="12IPT" ORDER BY: Surname.Student_Details ASC

<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">When conducting a SQL query, the user will most often use either **Relational** or **Logical operators**, or a combination of both. <span style="color: #333333; font-family: Arial,sans-serif;">These include:


 * <span style="color: #333333; font-family: Arial,sans-serif;">Relational Operators **
 * **<span style="font-family: Arial,sans-serif; font-size: 8pt;">TERM ** || **<span style="font-family: Arial,sans-serif; font-size: 8pt;">SQL ** ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">EQUALS ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;">= ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">NOT EQUAL TO ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;"><> ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">GREATER THAN ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;">> ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">GREATER THAN, EQUAL TO ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;">>= ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">LESS THAN ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;">< ||
 * **<span style="font-family: Arial,sans-serif; font-size: 7pt;">LESS THAN, EQUAL TO ** || <span style="font-family: Arial,sans-serif; font-size: 7pt;"><= ||


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Logical Operators **

AND - when both expresssions are TRUE OR - When ONE expression is TRUE NOT - Opposite expression

<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Complete the following SQL exercises. <span style="color: #494980; font-family: Arial,sans-serif; font-size: 10.5pt;"> <span style="color: #494980; font-family: Arial,sans-serif; font-size: 10.5pt;">


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Explain the purpose of each of the following key terms:
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">SELECT
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">FROM
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">WHERE
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">ORDERBY
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">RELATIONAL OPERATORS
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">LOGICAL OPERATORS
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Complete HSC style question - pg 190 of your text


 * Access Database Fields**
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Collecting and Displaying for Database Systems **
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Collecting is the information process that gathers data from the environment. It includes knowing what data is required, from where it will come and how it will be gathered.
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Displaying is the process that outputs information from an information system
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Outline the various Collecting methods you setup in your database design project eg. Forms as opposed to tables
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">What design considerations did you take into account when designing your form?
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">How did you display your queried data? eg. A report
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10.5pt;">Why is a report preferred to printing out a table? What appeared on your reports when you completed them for your design project?


 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Hypertext, Hypermedia and Search Engines **
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Read the following worksheet [[file:hypermediaandhtml.pdf]]on Hypertext, storyboards and Hypermedia.Complete exercise 2.4
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Complete the worksheet on [[file:ch2searcheng.pdf]]
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Define the following terms:
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">free text searching in hypermedia
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">indexing (as it relates to web pages)
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">metadata (as it relates to web page keywords)
 * <span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">Centralised and Distributed Databases/Issues associated with Database Management Systems **
 * <span style="color: #333333; font-family: Arial,sans-serif;">Read the following worksheet on Centralised and Distributed databases [[file:central_distributed_databases.pdf]]
 * Construct a table - Advantages and Disadvantages of each database type


 * //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Storing and Retrieval in a Database system //**

//<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">¨ ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Record summary notes on Storage features such as: // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Direct and Sequential access // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Online and Offline Storage // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Magnetic Storage // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Hard Disks // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Cartridge and Tape // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Optical Storage // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Backup and Recovery // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Physical security Methods // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Usernames and passwords for securing databases // //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 10pt;">o ////<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Encryption and decryption //
 * //<span style="color: #333333; font-family: Arial,sans-serif; font-size: 12pt;">Read pages 162 to 169 of your text //**