Unit 17 Database Design Concepts
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:
- Understand databases and data management systems
- Understand database design techniques
- Be able to design, create and document databases.
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; 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.|
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.
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.