Database Development using Microsoft Access

1
Database Development using Microsoft Access

• Lecture 2
• Conceptual Database Design
• Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk

2
Recap

• In the last lecture
• Database Approach
• - against traditional File-based approach
• - Database Definition
• a collection of related data, and also
  their inter-relationships

3
Recap

• In the last lecture
• Database Management System (DBMS)
• - Database Definition Language (DDL)
• - Database Manipulation Language (DML)
• Query Language (QL) Structure Query
  Language SQL

4
Recap

• In the last lecture

• MS-Access 2000 (XP) / 2003
• (A collection of)

Database Implementation
Database Management Systems (DBMS)
Application Development (User-interface,
applications)
Tables / Fields implementation
Query Design(QBE or SQL)
Forms / Reports design
5
Recap

• In the last lecture
• Database Design Phases
• - Conceptual Database Design Data Modelling to
  specify the database structure
• - Logical Database Design
• Normalization to test or verify the Data
  Modelling
• - Physical Database Design

6
In this Lecture

• The Concepts of Conceptual Database Design
• Entity-Relationship Data modelling entities,
  attributes, and relationships
• Relational Keys
• Primary key, Candidate key, Composite key and
  Foreign Key
• Local and global conceptual model

7
Relational Data Model

• It was first developed by E.F.Codd in the early
  1970s as an approach to find and control
  information away from the user and provide an
  interface which would accept simple appropriate
  commands to find whatever was needed
• Examines the problem area to identify the data
  needed and its use.
• Works towards building a formal conceptual data
  model
• Shows types of data relationships between types.

8
Relational Data Model
attribute
Relational Database Two-dimensional table
An Individual Record
9
Conceptual Database Design

• The use of high-level conceptual data models to
  support database design
• independent of implementation details such as
  DBMS, application programs, programming language
• Simply the database design process
• Create an accurate reflection of the real world
  in a database

10
ERDs - Concepts

• Identify-
• Entities
• Attributes
• Relationships
• Primary Key

11
Entities

• A distinct object in the organization that is to
  be represented in the database
• They are usually identified as nouns in a textual
  description
• They are represented in a ER-model as the
  rectangle
• The first letter of each word in the entity name
  is upper case

Staff
Branch
12
Entities

• An entity is something about which we wish to
  keep information
• Not all the nouns are entities
• Car (Registration_number, ManufacturerName)
• For Example
• Registration number would not normally be an
  entity since we do not wish to individually
  record information of registration numbers
• Car would be an entity if we wanted to store the
  Registration number AND some other information
  (e.g. ManufacturerName)

13
Attributes

• An attribute is a property that describes a
  certain aspect of the object that we wish to
  record.
• RegistrationNumber and ManufacturerName are the
  attributes of the entity Car
• A Staff entity type may be described by the
  staffNo, name, position and salary attributes
• Attributes hold values that describe each entity
• ManufacturerName is an attribute of the entity
  Car
• Ford is the value of attribute
• Each attribute can have only one value at a given
  time.The value of attribute may change over time

14
Type and Occurrence

• Entity and Entity Occurrence
• Car is an Entity Car
  (RegNumb, ManufacturerName)
• a particular car is an Occurrence myCar
  (123456, Ford)
• Attribute and Attribute Occurrence (Value)
• ManufacturerName is Attribute
• Ford is the value / Occurrence of attribute
• Summary
• 1. Data Model (ER-Model) specifies the
  high-level abstraction of database model.
• 2. Entities and attributes represent concepts
  rather than specific values (the
  Occurrences)

15
Type and Occurrence
attributes
Entity / Entity Type
CustomerDetails
An Entity occurrence
Attributes value / Occurrence
16
Relationships

• A relationship represents an association between
  entities which is meaningful to the organization
• It is normally represented as a verb (Member
  borrows a book)
• Summary
• Entities Attributes nouns
• Relationships verbs

17
Relationships

• Three Types of Relationship
• 1, n, m the cardinality of a
  relationship
• 1 a maximum of 1. n and m a maximum
  of more
  than one

One to One
One to Many
Many to Many
18
Relationships

• The relationship can be read as
• Left to right- A school can possible run
  many courses . It follows that a school may
  have one course or many courses or none at all.
• Right to left A course can be set by a
  maximum of one school. It follows that a
  course may be set by no school or by one school
  but not by more than one school.
• If this reading is contradicted by the
  real-world situation, then there is an error
  in the diagram.
• Summary
• - What you model is what you got
• - Mostly depends on the assumptions

19
Candidate key

• An identifier of every entity type
• An attribute or set of attributes that uniquely
  identifies individual occurrences of an entity
  type.
• Student (Student_No, Name, Address, Date_Birth)
• An entity may have one or more possible candidate
  keys, one of which is selected to be the primary
  key.
• Student (Student_No, Name, Address, Date_Birth)

20
Primary Key

• The primary key (PK) of an entity is an attribute
  or the smallest combination of attributes whose
  values in the real world can never be duplicated
• A search on the data in the db for a singe
  entity, using a single value for the PK, will
  retrieve at most one entity (occurrence).
• The PK of an entity is used repeatedly in the
  design process. An error in the PK definition
  produces many errors in the finished database

21
Simple Composite Primary Key

• Primary Key (PK) is one of the Candidate Keys-
• A Simple PK that consists of a single attribute.
• A Composite PK that consists of two or more
  attributes

22
Identifying Primary Key

• To determine the PK we should
• Consider PK that identifies a unique occurrence
  of the entity type
• Student (Student_no, name, address, course)
• Choose PK in consideration of attribute length
• Staff (Staff_no, name, address, NIN)
• NIN (National Insurance Number)
• Staff_no SG14 (maximum 5
  chars)
• NIN WL220658D (maximum 9
  chars)

23
Identifying Primary Key

• To determine the PK we should
• Draw an occurrence table of sample real-world
  data to help us visualise the unique and
  repeating combinations of data.
• ORDERLINE (order, item, qty)

24
Exercise

• What is the Primary Key (PK) of
• REPAIR ( serial, Technician_ID, DateOfRepaire,
  Notes, HoursWork)?
• Are you making any assumptions?

25
Simple Modelling steps

• Looks easy but BEWARE !
• Find the main entities of the problem.
• Look for the relationships between these
  entities.
• Work out the degrees of the relationship.
• Find the attributes of the entities.
• Determine candidate and primary key attributes
• Check model for redundancy
• Review local conceptual data model with user

26
ER- Diagram

• Write the name of the PK in brackets and
  underlined near the entity
• Write the cardinality of relationships

(Student_no)
m
Student
registers
1
m
Department
takes
m
Course
1
(Depat_no)
offers
m
(Course_no)
27
Function Areas

• Local Conceptual Model Global Conceptual Model
• Different areas within an organisation might have
  different views of data requirements.
• Applying data analysis to a functional area
  results in a LOCAL CONCEPTUAL MODEL
• Combining all the local conceptual models results
  in the GLOBAL CONCEPTUAL MODEL.
• Global model contains all entities and
  relationships off local models.

28
Function Area

• Build and Validate the Global logical Data Model
• Common entities / relationships appear only once
• Different local models might use the same name
  for different things or different names for the
  same thing.
• Further simplification may be possible by
  removing redundant relationships.
• This discussion is out of scope in this module

29
Summary

• Concepts of Conceptual Database Design
• Data modelling ER-diagram
• Entities, Attributes and Relationships
• Different keys
• Candidate Key, Primary Key, Composite Key
• ER-diagram

30
Next Lecture

• In the next lecture, Normalization-
• The purpose of normalization
• The concept of functional dependency
• How to undertake the process of normalization