ICT
 
 
home search Contact
Lmarsden is a non-profit information site

Week 14: Design, Implement, Validate and Evaluate

Class Task and Information

  • Lesson 01:

    • QBE Revision from previous lesson.

    • Entities and Tables: Class Handout
    • A source file or table stores data about one of the primary components or entities of the system. An entity is a person, student , item or things for which data is to be collected and stored. For example, in a car dealership business each customer is one entity. A group of customers forms an entity set. The terms entity set and file (or table) are then essentially interchangeable.

    • How do we name entities in a relational database? What are the conventions we use to uniquely identify these entities.

    • A database system is used to store data about visitors at a Sunshine Coat Zoo with more than one tour guide.

      What are the source entities?

    • You must be able to group data together to form entities.

    • Relationships between entities
          • 1 to 1
          • M to M
          • 1 to M
          • M to 1
      • What are the different ways to indicate relationships?
      • What are the advantages and disadvantages of each entity relationship?

    • Transaction/Junction Entities resolve Many to Many relationships.

    • In a database system for a Movie Cinema, list at least two other entities beside Customers and Cinemas

    • In a database system for a optometrist, is a transaction entity required? What would be its purpose?




    • Table Relationship Diagrams (TRD) presentation

      Suggested Solutions:



    • Reminder that the test will be Wednesday Week 5 (6th of Nov) Lesson 2


  • Lesson 02:
    • Analysis - Objectives and Outcomes: Class Handout

    • The objectives of any Information System should be general in nature and cover:
      • the storing of information keeping the data secure
      • user friendliness and efficient for data entry (UI and UX)
      • increased efficiency in the entire process and a reduction in errors
      • reports and listings according to specified criteria.

    • Write a set of objectives for an Information System for a database used for an L Plate Driving School.

    • An outcome is essentially an output from the system. including queries, forms, printable reports and data for applications to interpret. But as forms, reports or layouts all present specific data built from querying the system, an outcome is essentially a query.

    • The other three outcomes need to demonstrate data processing (the changing of data into information). This should include
      • the sorting of data (Create, Update and Delete)
      • the use of criteria. simple and complex (Read)
      • a statistical output (involving grouping, summaries such as sum, average, min, max and equations to calculate RRP from Mark Up %).

    • For a database system used in a MiniGolf Club, storing information about the golf played by members, what is a statistical outcome involving a criteria?

      • List the total number of match days each member has participated in within the last twelve months.

        • Total number = statistical
        • Twelve months = criteria



    • Reminder that the test will be Wednesday Week 5 (6th of Nov) Lesson 2



  • Lesson 03: Revision and key points for Major Test

    • How relational databases work.
    • The importance of Primary keys and Foreign keys
    • Naming conventions for primary keys
    • Table structure and Table Relationship Diagram (TRD)
    • QBE grids (Query By Example) - Tutorial on QBE
    • Developing Outcomes from a TRD

    • Consolidation Exercises: Work Experience Agency

      • An work experience agency matches people seeking work experience with opportunities made available by employers.

      • What are three source entities required in this database system?

      • Discuss the relationship between :
        • employers and work experience positions
        • work experience positions and work experience positions available
        • work experience positions available and people seeking work experience positions

      • Explain how to resolve any many to many relationships.

      • Task 01: Write out the table relationship of the TRD below.
      • Task 02: Identify the entities and transactional tables
      • Task 03: List all the transactional relationships between entities. e.g. Camps and Players where Enroll is transactional.

      • Develop some objectives/outcomes/outputs for this database using:
        • Single Criteria - All students in Bradman
        • Dual Criteria - All Student in Bradman with a XL shirt
        • Multi Criteria - 3 or more fields
        • Statistical Criteria - tallest student in the cohort
        • Simple Objective
        • Complex Objectives
        • Statistical Objectives




    • Task04: Using the above QBE - Develop examples of appropriate objectives/outcomes/outputs.






  • Lesson 04:
    • TRDs (Table Relationship Diagrams)
    • Reading a TRD


    • Left to Right - A Supervisor can have many students and a student can have many results and a result can only be for 1 subject.

    • Right to Left - Subject can have many results, a result can be for 1 student and a student can only have one supervisor.

    • Task 01: View the following TRDs and write out their relationships in words, left to right and right to left. Example Table Relationship Diagrams (TRDs)


    • Develop some objectives/outcomes/outputs for this database using:
      • Single Criteria - All students in Bradman
      • Dual Criteria - All Student in Bradman with a XL shirt
      • Multi Criteria - 3 or more fields
      • Statistical Criteria - tallest student in the cohort
      • Simple Objective
      • Complex Objectives
      • Statistical Objectives




    • Task01: Using the above QBE - Develop examples of appropriate objectives/outcomes/outputs.
    • Single Criteria - Dual Criteria - Multi Criteria

    • Task 02: Create a Complex Objective and Statistical Objective

    • Task 03: Use the following TRD and create a Single Criteria - Dual Criteria - Multi Criteria - Complex Objective and Statistical Objective:



    • QBE grids (Query By Example) - Tutorial on QBE

    • Aggregate queries:
      • SELECT MIN(column_name)
      • SELECT MAX(column_name)
      • SELECT COUNT(column_name)
      • SELECT AVG(column_name)
      • SELECT SUM(column_name)



      • The SQL HAVING Clause

        The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

      • The SQL GROUP BY Statement

      The GROUP BY statement group rows that have the same values into summary rows, like "find the number of customers in each country".

      SELECT column_name(s)
      FROM table_name
      WHERE condition
      GROUP BY column_name(s)
      ORDER BY column_name(s);


  • Reminder that the test will be Wednesday Week 5 (6th of Nov) Lesson 2



Readings / Homework

 

 

 

 

Focus Point

 

 

Information