Module_4_Exercises_-_MS_Office_2007

Module_4_Exercises_-_MS_Office_2007 - Module 4 Exercises...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
115.107 Management Information Systems Module 4 Exercises Page | 1 Module 4 Exercises 1 Topic Relational databases are an underlying technology used in most of the information systems covered in this course. Database technology is quite ubiquitous – a database may be serving a large organization where it is being shared by thousands of employees, or you may be using a desktop database to manage data belonging to a small group or even just your personal data. In relational databases, data is organized by using the so-called relational data model. In this module’s exercises, you will learn how to convert an ER data model into a relational data model and implement relations as tables in Microsoft Access 2007 desktop DBMS. 2 Relevance to Assignments These exercises will prepare you for completing Part 2 of the Assignment. 3 Relational Data Model The relational data model is, in fact, very simple – all data is organized in tables (also known as “relations” – not to be confused with “relationships” from the ER data model). Consider the following example, representing a database serving a small plumbing business. CustomerTbl name address phone customerId Aaron Blake Palmerston North, Linton Str. 41 06 342 1144 18 John Winkler Palmerston North, Queen Str. 19 06 389 3410 37 JobTbl customerId description jobId 18 Leaking hot water tank 118 37 Shower does not work 120 37 Install a new bath 121 EmployeeTbl employeeId name role 32 Peter Adams workman 36 Anna Ingham manager jobAssignmentTbl employeeId jobId 32 118 36 118 32 120
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
115.107 Management Information Systems Module 4 Exercises Page | 2 CustomerTbl holds information about customers , JobTbl – about jobs, and EmployeeTbl – about employees. customerId , jobId and employeeId columns serve as primary keys – their values uniquely identify rows in the respective tables. The customerId column in the JobTbl table is an example of the so-called “foreign key” – it is a primary key from a different table, CustomerTbl , added to JobTbl in order to link rows of the two tables. By specifying a customerId for a job, we record which customer the job is for (and, hence the address where the job is to be performed and the phone number to use to keep in touch with the customer). Note that if we included all of the customer information in the JobTbl table with each job, for a given customer his address etc. could be recorded many times. This would open possibilities for inconsistencies, such as an old address and a new address recorded in the database at the same time. By connecting job information to customer information via a foreign key, we can record customer information for each customer just once even if there are many jobs for that customer. Note, that the way we connected jobs to customers, there can be many jobs for a given customer (two jobs for Aaron in our example), but no more than one customer for each job. An alternative way of connecting rows of two tables is exemplified by how we connect employees to jobs.
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 06/01/2010 for the course PN 178.328 taught by Professor Sue during the Spring '10 term at Massey Palmerston North.

Page1 / 9

Module_4_Exercises_-_MS_Office_2007 - Module 4 Exercises...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online