Week 02 Introduction to Data Modelling INFOSYS 222

Week 02 - Introduction to Data Modelling INFOSYS 222

Agenda • Introduction • The relational data model • Exercises • Introduction to ERWin • Exercises

Introduction • Who we are • What could you expect from us • What do we expect from you

Lab Schedule Week Lab 02 Introduction 03 ER diagram 04 Data modelling 05 Workshop 06 Normalisation 07 SQL 08 SQL 09 SQL 10 Workshop 11 Data warehouse 12 No lab

The Relational Data Model Demo – The Book. Store Model

The Relational Data Model Table/ Entity/ Relation Attribute / Column/ Field Values/ Data Rows / Tuple

The Relational Data Model Primary Key Domain/ Data Type

Microsoft Access • It is part of Microsoft Office • A desktop implementation of RDBMS • It has its own version of SQL • It has features beyond the requirements of a RDBMS, like Forms and Reports

Exercises • How many rows are there in the Books table? • Which column(s) in the Orderlines table is/are the key(s)? • Why author_name is not ideal to be used as a key for the Authors table? • Which tables are related to the Books table? • What advantages are there in separating Authors and Publishers as individual tables instead of just columns in the Books table? • List the steps needed to track what Emily Jones had ordered

ERWin - Start • Menu and Toolbars • The model explorer pane on the left – for a text based view of the data model • The diagram window pane on the right - a graphical view of the data model • Change model properties to IE • Model-> Model Properties -> Notation • This is the notation for this class

New Model • 3 types 1. Logical: A conceptual model that includes entities, relationships, and attributes 2. Physical: A database specific model that contains relational tables, columns and associated data types 3. Logical/Physical: A single model that includes both the conceptual level objects as well as physical level tables You will learn more about each type as you progress with your lectures In this class we normally use Type 1

Model – The bookstore RDBMS schema

Exercises • Experiment with drawing some of the entities shown in the bookstore schema and linking them up with relationships • Start with Author and Book • The tutors will demonstrate how to add relationships in to your diagrams