Unit 17 Database Design Concepts

·   Aim

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:
LO1 Understand databases and data management systems	analyse the key issues and application of databases within organisational environmentscritically evaluate the features and advantages of database management systems
LO2 Understand database design techniques	analyse a database developmental methodologydiscuss entity-relationship modelling and normalisation
LO3 Be able to design, create and document databases	apply the database developmental cycle to a given data setdesign a fully functional database (containing at least four inter-relational tables) including user interfaceevaluate the effectiveness of the database solution and suggest methods of improvementprovide supporting user and technical documentation.

Guidance

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.