unit 17:Database design concepts

Unit 17:Database Design Concepts

Unit code:R/601/0447

QCF Level 4:BTEC Higher National

Credit value:15


To give learners opportunities to develop an understanding of the concepts and issues relating to databases and database design as well as the practical skills to translate that understanding into the design and creation of complex databases.

Unit abstract

Databases play an integral part in commercial domains, they provide users with a tool in which to store, model and retrieve data. Database development is fundamental in the area of computing and ICT within organisational contexts. Database Management Systems (DBMS) provide the systems, tools and interfaces by which the organisation can manage their information and use it to assist in the effective running of the organisation. Databases offer many links to other areas such as programming, systems analysis, HCI, as well as embracing issues of compatibility and end-user interfacing.

This unit explores database architecture, DBMS and the use of databases in an organisational context. Database design techniques are investigated and successful learners will be able to apply theoretical understanding to design, create and document a database system.

Learning outcomes

On successful completion of this unit a learner will:

1     Understand databases and data management systems

2     Understand database design techniques

3     Be able to design, create and document databases.

Unit content

1     Understand databases and data management systems

Databases: database architectures; files and record structures; physical and logical views of data; advantages of using databases; reduction of data redundancy; data consistency (validity, accuracy, usability and integrity); independence of data; data sharing possibilities; security; enforcement of standards; database utilities; data dictionaries; query languages; report generators

Databases in an organisational context: database applications; role of the database administrator; key organisational issues eg integrity, security, recovery, concurrency; industry standards eg Microsoft SQL, Oracle, Sybase, dBase

Database Management Systems (DBMS): structures; purposes; features and advantages; applications; methods of data organisation and access

2     Understand database design techniques

Database design methods and methodology: requirements analysis; database designer working with expert in domain development area; requirement specification; logical design eg relational databases, tables; physical design eg data elements, data types, indexes; data analysis and design within systems analysis; database design within a system development methodology

Relational database design: tables, relations, primary/foreign/compound keys; entity-relationship modelling; normalisation theory to third normal form

3     Be able to design, create and document databases

Database development cycle: developing logical data model; implementing a physical data model based on the logical data model; testing the physical data model; comparing model with requirements analysis; user interface eg input masks, drop-down lists, option buttons, command buttons

Database software: using appropriate applications software, eg Microsoft Access, SQL; database tools eg create tables, add new rows, alter data, functions, relational database languages

Tools and techniques: field and table design; validation and verification techniques; forms including such features as dropdown lists or check boxes; reports; queries; macros

Documentation: technical documentation; user documentation

Learning outcomes and assessment criteria

Learning outcomes

On successful completion of this unit a learner will:

Assessment criteria for pass

The learner can:


Understand databases and data management systems

1.1 analyse the key issues and application of databases within organisational environments

1.2 critically evaluate the features and advantages of database management systems


Understand database design techniques

2.1 analyse a database developmental methodology

2.2 discuss entity-relationship modelling and normalisation


Be able to design, create and document databases

3.1 apply the database developmental cycle to a given data set

3.2 design a fully functional database (containing at least four inter-relational tables) including user interface

3.3 evaluate the effectiveness of the database solution and suggest methods of improvement

3.4 provide supporting user and technical documentation.


Links to National Occupational Standards, other BTEC units, other BTEC qualifications and other relevant units and qualifications

The learning outcomes associated with this unit are closely linked with:

Level 3

Level 4

Level 5

Unit 18: Database Design

Unit 9: Systems Analysis and Design

Unit 33: Data Analysis and Design

This unit has links to the Level 4 and Level 5 National Occupational Standards for IT and Telecoms Professionals, particularly the areas of competence of:

•     Data Analysis.

Essential requirements

Learners must have access to database software such as Microsoft Access or industry standard software, as learners can then transfer any skills and knowledge and map it directly into a commercial environment.

This unit must be divided into taught theory and practical sessions. In developmental activities, learners must be encouraged to develop designs and then be given feedback prior to any actual implementation errors and confusions within a database design can cause significant problems at implementation that are difficult to retrieve.

Learners must make connections and identify the role of database design within the systems development lifecycle.

Learners must be introduced to developmental methodologies such as Structured Systems Analysis and Design Methodology (SSADM), Rapid Application Development (RAD) and the SPIRAL software development process.



Avison D and Fitzgerald G Information Systems Development: Methodologies, Techniques and Tools (McGraw Hill Higher Publishing Company, 2006) ISBN 0077114175

Chao L Database Development and Management (CRC Press, 2006) ISBN 0849392381

Connolly T and Begg C Database Systems: A Practical Approach to Design, Implementation and Management (Addison Wesley, 2004) ISBN 0321210255

Howe D Data Analysis for Database Design (Butterworth-Heinemann Ltd, 2001) ISBN 0750650869

Kroenke D Database Concepts, 2nd Edition (Prentice Hall, 2004) ISBN 0131451413

Ritchie C Relational Database Principles (Thomson Learning, 2002) ISBN 0826457134





Employer engagement and vocational contexts

To further enrich the content of this unit and to provide more of a vocational context it would be beneficial to bring in guest speakers, such as database designers, end users and administrators to discuss their role within an organisation(s) and the importance of good database design.