A candidate following a programme of learning leading to this unit will be able to:

Full text

(1)

Unit 42: Database Design

Learning Outcomes

A candidate following a programme of learning leading to this unit will be able to: • Analyse a business for its business rules

• Create and produce supporting documentation for the entity relationship diagram (ERD) • Create and produce an ERD for the conceptual model

• Create and produce a logical table diagram

NB Before starting this unit please refer to the Vendor website for up-to-date specifications, examination objectives and availability.

(2)

Unit content: Database Design

Assessment Objectives Knowledge, Skills and Understanding

1 Analyse a business for its business rules

a Identify those aspects of a business about which data must be known when given source documentation from a business

To include:

• Business rules • Process rules • Programmatic rules

Source documentation must include at least one of the following:

• Interview transcripts

• Documentation (forms and reports) used by a business

• Promotional material (either hardcopy or from a website)

2 Create and produce supporting documentation for the entity relationship diagram (ERD)

a Identify the entities and their attributes To include:

• Definition and example of each entity • Name and description for each attribute for

each entity

• A sample value for each attribute to support its inclusion in the Data Model

b Create relevant relationships between the entities

To include:

• A Matrix diagram

• Relationships should be correctly written out, including name, optionality, degree

Where required by the business rules of the business being modelled, to include one or more of the following:

• Non-transferability • A recursive relationship

• Example of a one-to-one relationship • A definition and example of a hierarchical

relationship c Create Unique Identifiers (UID) for entities To include:

• Application of the rules of unique identifiers and identify UID's for entities in an existing model

Where required by the business rules of the business being modelled, to include evidence of analysis of business rules and justification for the creation of:

• An artificial UID • A composite UID • A secondary UID

(3)

Assessment Objectives Knowledge, Skills and Understanding

d Identify assumptions made To include:

• The assumption(s) made relating to o Interview transcripts

o Documentation (forms and reports) used by a business

o Promotional material (either from hardcopy or from a website) • The effect(s) on the ERD e Identify constraints that may not be

diagrammed

To include:

• Constraints that will need to be implemented in code

f Create a model to take account of changes to data over time

Where required by the business rules of the business being modelled, to include one or more of the following:

• Identification of the need to track data that changes over time

• Identification of the UID of an entity that stores historical data. Explain and justify the choice of UID

• Distinction between using DATE as an attribute and DAY as an entity in a data model, depending on business requirements • Solution to the problem of keeping

characteristics of a date by constructing a model that uses DAY as an entity

• Definition and example of conditional non-transferability that can arise in a time-constrained model

• Solution for the business requirement of tracking changes in price or values by constructing a model that uses a historical entity

• Description of the meaning of journaling/logging

• Identification of the business need for journaling/logging and construct a model that addresses this requirement

3 Create and produce an ERD for the conceptual model

a Using recognised drawing conventions to construct an ERD that represents entities, attributes and their relationships

To include:

• Use of recognised drawing conventions • All m:m relationships should be resolved Where required by the business rules of the business being modelled, to include one or more of the following:

• An arc constraint to represent an Exclusive OR relationship

(4)

Assessment Objectives Knowledge, Skills and Understanding

• A super type / subtype relationship between entities

• A recursive relationship • Hierarchical relationship

b Ensure ERD is in third normal form • To provide evidence of the process of normalization to third normal form through first and second normal forms

4 Create and produce a logical table diagram

Create a logical table diagram To include:

• Basic mapping to transform an ERD into a logical model

• The use of naming conventions for tables and columns used in logical models • The application of the rules of relationship

mapping to correctly transform 1:m and/or barred relationships

Where required by the business rules of the business being modelled, to include one or more of the following:

• Application of the rule of relationship mapping to correctly transform 1:1 relationships

• Application of the rule of relationship

mapping to correctly transform relationships in an arc.

• Application of the table, column, identifiers, relationship and integrity constraint rules for mapping super type implementations • Application of the table, column, identifiers,

relationship and integrity constraint rules for mapping subtype implementations

• Application of the table, column, identifiers, relationship and integrity constraint rules for mapping super type and subtype arc

implementations

Assessment

Form of assessment

Assessment will take the form of practical activities that are centre set as part of the Oracle Academy programme, centre assessed and externally moderated.

In order to achieve a pass in this unit, candidates must demonstrate skill in each assessment objective.

(5)

Assessment Tasks

Candidates are required to a complete a project and additional activities that address all of the assessment objectives. Evidence that shows where and how the assessment objectives have been achieved, must be available.

Evidence

Candidates will be required to complete OCR-provided forms identifying where and how the assessment objectives have been met. Additionally, candidates will be required to provide copies of the documentation that they have completed as part of their tasks.

Tutors will be required to complete an observation checklist confirming that all activities have been completed to a standard that would be acceptable in the workplace.

An evidence checklist and/or observation checklist are included immediately after this page. Tutors are able to photocopy these forms. Tutors and candidates must use these checklists to record assessment objectives achieved and the page numbers where these objectives can be evidenced on candidate work. It is essential that the candidate numbers the pages of the submitted evidence, as a reference to these will be required as part of the evidence checklist.

A sample test plan is included for use at the back of the handbook. This form may be photocopied.

Notes for tutors

The project required for this unit is part of the official Oracle Academy: Introduction to Computer Science and Business course: Database Fundamentals.

(6)

Evidence Checklist

Candidate Name: Centre Number:

Qualification: OCR Level 3 Certificate/Diploma for IT Professionals

Unit 42 Database Design

Ensure the following are included as a minimum Complete boxes with a √ or required information

Please reference the assessment objective achieved with the relevant page number where the evidence can be found

Assessment Objective Covered by Evidence Candidate Checked (√ ) Page Reference Tutor Checked (√) Printouts/Reports Required

1a Produce evidence identifying those aspects of a business about which data must be known when given source documentation from a business 2a Produce evidence identifying the

entities and their attributes

2b Produce evidence identifying relevant relationships between the entities 2c Produce evidence identifying creation

of Unique Identifiers (UID) for entities 2d Produce evidence identifying

assumptions made

2e Produce evidence identifying constraints that may not be diagrammed

2f Show evidence of the model taking account of changes to data over time 3a Produce evidence of using recognised

drawing conventions to construct an Entity Relationship Diagram (ERD) 3b Produce an ERD that is in third normal

form

4 Produce evidence of the construction a logical table diagram

Date of Completion: Candidate’s Signature: Assessor’s Signature:

Figure

Updating...

References

Updating...