Databases & Spreadsheet - Week 3

1. Databases & Spreadsheet - Week 3 • Answer for Last Week Practical Session as revision for Database Design • Catch-up Practical Session for Week 1 & Week 2 • Get Started with Form Design

2. Acceptable Design Solution 1 (From Tuesday Group) • Assumed Scenario: A coach can work in different teams, each of which has many players. (simple assumption with a logical sequence) • Each coach can work in and manage one or more teams, each team can be only managed by one coach (none of the coach can work in the same team) Coach Team Player • Each team has one or more teams, each player can be only managed by one coach (none of the players can be in the same team) P021 P001 P746 P111 P222 P122 P120 P129 C01 C02 C03 T01 T02 T03 T04 Sample Record for each table

3. Table View Mapping - (From Tuesday Group) Coach Team Player P01 P02 P03 P04 P05 C01 C02 T01 T02 T03

4. Acceptable Design Solution 2 • Assumed Scenario: A team has many coach, each of whom teaches many players. (simple assumption with a logical sequence) Team Coach Player • Each team has one or more coaches, each coach can only work in one team (a team can not have the same coach; and a coach can’t work in more than one team) • Each coach can teach one or more players, each player can be only taught by one coach (none of the coach can teach the same player) P021 P001 P746 P111 P222 P122 P120 P129 T01 T02 T03 C01 C02 C03 C04 Sample Record for each table

5. Table View Mapping Team Player Coach P01 P02 P03 P04 P05 T01 T02 C01 C02 C03

6. Simple Implication For any one-to-many relationship, FK is in the many end. PK FK

7. Other Design Solution • Assumed Scenario: A team has one or more coaches and one or more players. (Simple but less precise assumption) Coach Player Team P021 P001 P746 P111 P222 P122 P120 P129 C01 C02 C03 T01 T02 • Problem: Who are the players taught by the coach (C02 or C03) in T02?

8. Other Design Solution • Assumed Scenario: A coach manages one or more team, and teach one or more players. (Again, a simple but less precise assumption) Team Player Coach P021 P001 P746 P111 P222 P122 P120 P129 T01 T02 T03 C01 C02 • Problem: Who are the players exactly in T02 or T03?

9. Summary Acceptable Design • Clear forward connection flow (from 1:m) or backward connection flow from (m:1) Coach Team Player • No fan shape connection flow (from 1:m) • In this module, try to avoid many-to-many relationship and make things simpler. Some techniques: PK FK

10. Recap Step 1:Identify the entities with attributes and the Relationships among entities • Make assumptions, by clearly understanding the scenario/background information for your database design. • Map entities with attributes and find the relationship between different entities. • Clearly identify the types of relationship Step 2: Use some rules to check your design Step 3: Realize the Relationships in table view • Find and create Foreign Key (thematching field) between the entities (tables) as the link to realize the relationship Step 4: Normalize the Tables (not required in this module)

11. Some notes related to Attribute/Field Data Type • Text • Numeric • Date/Time • Memo • Counter • Currency • Yes/No Properties • Field Size • Required (or null) • Indexed • Format • Validation rules (e.g., enter wrong value; error handling using msgbox)

12. Reading • Colin Ritchie (2002), Relational Database Principle, 2nd Edition, Thomas Learning.