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:
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


