CIF102 FIF 102

1
CIF102 / FIF 102

• Week 2
• Building a relational database

2
Outcomes

• To understand the entities in a system
• To be able to set up tables with primary and
  foreign keys and relevant data types
• To be able to successfully link the tables
  maintaining referential integrity

3
Entity Relation (ER) diagrams and database design

• One of the key steps in data modelling is to
  identify the entities in a system and the
  relationships between them.
• You are going to start your first database by
  designing a system for four friends sharing a
  flat who want to manage their collection of books
  and DVDs.

4
Flatmates database

• Sarah, Brian, Lisa and David are flatmates. They
  are owners of a collection of books and DVDs that
  need to be incorporated into a single system that
  can be searched. The items are either owned after
  purchasing them from the store, borrowed from
  friends.
• At present when each flatmate buys a book or DVD,
  they store the receipt in a filing cabinet. When
  a flatmate borrows a book or DVD she/he notes the
  name on a post-it. When someone borrows a book or
  DVD she/he notes the name and item on a post-it.
• The flatmates would like a system for a computer
  that will allow them to keep track of items- who
  a particular book or DVD belongs to and who has
  borrowed which item.

5
External entities and entities within the system
being designed

• External entities are those that documents or
  data may pass out to or in from
• Entities of interest to the system are those for
  which the system will store data (these are
  highlighted in green in the previous slide).

6
The context diagram

• Examine the diagram to see which agencies are
  going to contain data stored in your system
• Put a box around this part- this is the context
  diagram
• There are other entities-we need to separate
  those external to the system
• The entities in the bubbles are external to the
  system we are going to design and data about them
  will not be stored by the system. The flatmates
  are all owners-what are the entities in the
  flatmates (owner) system?

Store
Friend
Owner
Maintain book and DVD data
7
Entities

• Look at the nouns in the description-these are
  possible items of interest in the modelled system
  (entities).
• Look at the relations between each entity is it
  a one to many (1n), one to one (11) or many to
  many (mn).
• Examples are one to one 11 a managing director
  manages a company
• One to many 1n a company employs many engineers
• Many to many mn many engineers work on many
  projects
• (see section1 Warrender, 2003)
• In this example an owner can have many books
  therefore owner to books is 1n
• The notation for one to many is

8
Linking entities

• What are the entities within this context
  diagram?
• What is the relationship between each of them?
  Try to put links between them-11
• 11, 1n or mn

DVD
Book
Owner
DVDs Lent out
Books Lent out
9
Linked entities and database tables

• We will use these entities as tables of data in
  our database.

10
Entities in the flatmates system
DVDs lent out
DVD
Owner
Book
Books lent out
11

• What is the relationship between each one?

One owner can have many DVDs
Owner
DVD
DVD
Book
DVDs lent out
Books lent out
12
Full notation placing of verbs describing the
relationship at either end of the link
Owner
owns
owns
Is owned by
is owned by
DVD
DVD
Book
is lent to
is lent to
Borrowed by
Borrowed by
DVDs lent out
Books lent out
13
Starting the application in Access Tutorial

• Outcomes
• 1 To be able to open the Access application, use
  the menu to choose design view
• 2 To be able to set up tables with primary and
  foreign keys and relevant data types
• 3. To be able to successfully link the tables
  maintaining referential integrity
• 4. To be able to start entering data into an
  Microsoft Access tables using the form wizard

14
Opening a new application

• In Windows, go to Start/ All Programs/ Microsoft
  Access
• Open a blank database (right hand menu)
• Save the file with a meaningful name (not the
  default db1) and in a folder that is going to
  hold all your work.

15
The development environment
These are the objects that you are going to use
to design and implement your information system.
These features are designed in Access to help you
create the various objects
User-defined objects i.e. tables designed by the
user
16
Access components

• Tables are often the main place where data is
  stored. They work very much like a spreadsheet.
  You can enter new data in a table.
• Queries are usually filtered versions of a table
  of data. They are used for organising data by
  sorting or only including certain records. They
  are also like a spreadsheet, but usually are not
  used for entering new data.
• Forms are a way to make entering and searching
  for data more straightforward.
• .

17
Components continued

• Reports are a good way to present data for
  printing, emailing, or on-screen presentations..
  
• Data Access Pages are used to export data in a
  form-like setup. This is commonly used to show
  your data in a web page.
• Macros are a way to encode your database to
  automate certain tasks. For example, a macro
  could print out a copy of a report every time it
  is opened.
• Modules are an interface between the Visual Basic
  coding language and your database. Modules are
  most often used by database administrators

18
Next session

• Next session we will cover entity relations in
  more depth and how to input data using forms and
  subforms
• For next week read Chapters 1-3 of Warrender
  (2003) Databases.