Database Development using Microsoft Access CMPCP0015

1
Database Development using Microsoft
AccessCMPCP0015

• Lecture 5
• Table Design in MS-Access
• Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk

2
Recap

• In the last lecture
• 1NF (1st normal form)ATTRIBUTES ARE DEPENDENT ON
  THE PRIMARY KEY
• 2NF (2nd normal form)ATTRIBUTES ARE DEPENDENT ON
  THE WHOLE OF THE PRIMARY KEY
• 3rd Normal Form (3NF)ATTRIBUTES ARE DEPENDENT ON
  NOTHING BUT THE PRIMARY KEY

3
In this Lecture

• We will learn about-
• Physical Database Design
• Database Design by use of MS-Access
• Database Design in MS-Access
• Table Design
• Fields Definition

4
Tutorial 3 Question 2-- Answer

• UNF
• (PropertyNo, PropertyAddress, (InspectionDate,
  InspectionTime, Comments, StaffNo, StaffName,
  CarReg))
• Could be simplified as follows-
• Property (pN, pA, (IDate, iTime, cmts, sNo,
  
  sN, cR))

Repeating Group
5
Tutorial 3 Question 2-- Answer

• 1NF
• The Rule of 1NF Remove the Repeating Group with
  a copy of the original primary key.
• Property-1 (pN, pA)
• Property_inspection-1 (pN, iDate, iTime,
  cmts,
  sNo, sN, cR)
• Assumption a property is only inspected once
  on a given date.

Composite Key
Question 1 ) Think about whether the single
attribute pN is sufficient enough to act
as the primary key ?? 2 ) Is there any partial
functional dependency here?
6
Tutorial 3 Question 2-- Answer

• 2NF
• The Rule of 2NF Remove the partial functional
  dependency.
• Property-1 (pN, pA)
• Property_inspection-1 (pN, iDate, iTime,
  cmts,
  sNo, sN, cR)
• There is no partial functional dependency in
  the results of 1NF.
• Property-2 Property-1
• Property_inspection-2 Property_inspection-1

7
Tutorial 3 Question 2-- Answer

• 3NF
• The Rule of 3NF Remove the functional
  dependency not on the primary key.
• Property-2 (pN, pA)
• Property_inspection-2 (pN, iDate, iTime,
  cmts,
  sNo, sN, cR)
• Functional Dependency sNo ? sN
• Property-3 Property-2
• Property_inspection-3 (pN, iDate, iTime,
  cmts, cR)
• staff-3 (sNo, sN)

8
Physical Database Design

• 2NF and 3NF Normalization
• The purpose of 2NF
• The purpose of 3NF
• The concepts of Foreign Key
• The Relational Keys
• Primary Key, Foreign Key, Candidate key

9
Create A New Database

• Uses a Database Template from MS-Access
• Template Database files
• Contains the structure and tools of the
  desirable database
• Pre-defined Tables, Queries, Forms, and Reports
• Similar to the template used in MS-PowerPoint
• Downloads more templates
• From the New File task pane, click Templates on
  Microsoft.com.

10
Create A New Database

• Creates a New Empty Database
• Our main interests !!!.
• Creates a new database via File New Database
• Uses the results of normalization to guide the
  database implementation
• Entities ? tables
• Attributes ?fields

11
Create A Table

• Three ways to create a new table
• Desirable in this module
• Create table in Design view
• Not Recommended
• Create table by using wizard
• Create table by entering data
• Microsoft Access automatically assesses the
  structure of the data you inputted

12
Create A Table

• Right-click in the Database Window View
  Details
• No space in the tables name.

13
Define the Fields of Tables

• Defines all the fields in the Design View
• Selects the appropriate Data Type for fields
• Basic Data type
• Text, Number, Date / Time, Currency, Auto Number
  etc.
• Lookup field a special Data type
• Provides a list of values
• Makes data entry easier and efficient
• Sets the Primary key
• Default setting ID as in AutoNumber
• Sets the primary key based on the results of
  Normalization

14
Set a Field Property

• Field Size
• Input Mask
• Patterns for data entry
• Default Value
• Required
• Allow Zero Length
• Used as a combination
• Indexed
• Speed up the data searching
• Validation Rule
• e.g. ltgt 0 nonzero value
• Validation Text
• For more details, Press F1 for help

15
Set the Table Design Options

• Customizes how you work with tables in Design
  view
• Choose Tools Option

16
Printing a Table Definition

• Print out the table definitions in a permanent
  paper record
• Choose from Tools Analyze Documenter
• Select the tables from the list and press ok
  button to print them out

17
Summary

• Review of the Process of Normalization
• Tutorial 3 Question 2
• Table Design in MS-Access
• Table Definition
• Field Definition

18
Next Lecture

• In the next lecture -
• Join tables together by the internal
  relationships
• Defines the relationships among tables
• Query Design in MS-Access
• QBE Design
• Structure Query Language (SQL) Part 1

19

• As part of the services, the company undertakes
  regular inspections of the property by members of
  staff. When staff are required to undertake these
  inspections, they are allocated a company car for
  the day. A member of staff may inspect several
  properties on a given date, and a property is
  only inspected once on a given date.

• A property can be inspected by a staff on
  different dates
• A staff can inspect different properties on
  different dates.
• A property on a given date cannot be
• PropertyNo, Date ? StaffNo
• (pN, iDate, iTime, cmts, sNo, sN, cR)