Information Processes and Technology
Home > Information Processes and Technology > Information Systems and Databases > Data modelling: Schematic Diagrams
Data modelling: Schematic Diagrams
This activity addresses aspects of the following syllabus outcome:
Outcomes
A student:
H6.1 analyses situations, identifies needs,
proposes and then develops solutions
H6.2 selects, justifies and applies methodical approaches to planning,
designing or implementing solutions
H7.1 implements and explains effective management techniques
H7.2 uses methods to thoroughly document the development of individual and team projects.
Source: Information Processes and Technology Syllabus, June
2009
http://www.boardofstudies.nsw.edu.au/syllabus_hsc/pdf_doc/ipt-st6-syl-from2010.pdf 
Schema and Schematic Diagrams
A Schema shows the organisational structure of a database. It should show the entities (the tables in the database), and their characteristics (ie their properties/attributes/fields). It should clearly identify the primary key in each table and the links and relationships between tables.
A Primary Key is a grouping or arrangement of characters (letter, numbers etc) which is used to identify a unique record in a database.
A Foreign Key is a field in a relational table that matches the primary key column of another table. This foreign key can be used to cross-reference tables.
Source:
IPT Course Specifications
A schematic diagram is similar to an ERD (entity relationship diagram) generated by the DBMS (Database Management System, e.g. MS Access). A schematic diagram serves as a plan before a database is created. An ERD is generated after the database has been created.
Entity Relationship Diagram (ERD) generated by Microsoft Access 2007
Activity:
- Create a schema for a database information system to be used to book
appointments in a medical centre.
Include the following entities (tables):
- Patients
- Doctors
- Rooms
- Appointments
Include appropriate attributes (fields), primary and secondary keys and relationships - Use a DBMS to create the database.
Populate the database with dummy data: create at least 5 patients, doctors and rooms then book some test appointments. - Use the DBMS to generate an ERD then compare this to your schema.
|