SWDDD401
BDCPC301 - Develop Database
Competence
RQF Level: 4 Learning Hours
100
Credits: 10
Sector: ICT and Multimedia
Trade: Software Development
Module Type: Specific
Curriculum: ICTSWD4002 TVET Certificate IV in Software Development
Copyright: © Rwanda TVET Board, 2023
1|Page
1200 Issue Date: September 2023
Purpose statement This module describes the knowledge, skills and attitudes to be acquired by
the learner to Analyse database, design database, implement database and
secure database. At the end of this module the learner will be able to
analyse database, design database, implement database and secure database.
Learning assumed N/A
to be in place
Delivery modality Training delivery 100% Assessment Total 100%
Theoretical content 30% 30%
Practical work:
Group project and 20% Formative
50%
presentation 70% assessment 70%
Individual project 50%
/Work
Summative Assessment 50%
2|Page
Elements of Competence and Performance Criteria
Elements of Performance criteria
competence
1.1 Database fundamentals are properly described based on database
standards
1. Analyse Database 1.2 Data dictionary is clearly described based on database model
1.3 Database Task requirements are properly identified based on user
requirements
2.1 Conceptual Database schema is properly designed based on system
requirements
2.2 Logical Database schema is properly designed based on system
requirements
2. Design Database
2.3 Database optimization is effectively enforced based on database
schema
2.4 Physical Database Schema is appropriately created based on the
Physical Data Model.
3.1 Data definition language commands are effectively applied based on
database schema
3.2 Data manipulation language commands are effectively applied based
on database schema
3.3 Data control language commands are effectively applied based on
database schema
3. Implement Database
3.4 Data Query Language commands are effectively applied based on
database schema
3.5 Transaction Control Language commands are effectively applied
based on database schema
3|Page
4.1 Access control is properly enforced based on database security
measures
4.2 Auditing and logging are clearly managed based on the security
policies
4. Secure Database
4.3 Data encryption is correctly implemented based on data security
measures
4.4 Backup and Recovery of data are regularly configured based on DBMS
Intended Knowledge Skills and Attitudes
Knowledge Skills Attitudes
✓ Describe database ✓ Design conceptual database ✓ Creativity
fundamentals schema ✓ Innovation
✓ Describe data ✓ Design logical database ✓ Adaptability
dictionary schema ✓ Flexibility
✓ Identify database ✓ Optimize database ✓ Teamwork
requirements ✓ Design Physical database ✓ Confidence
✓ Describe database schema ✓ Time management
schema ✓ Apply DDL, DML, DQL, DCL and ✓ Passion
✓ Integrity
✓ Describe SQL TCL commands
✓ Curiosity
✓ Enforce data access control
✓ Organized
✓ Manage Auditing and logging
✓ Implement Data encryption
✓ Configure database backup
and restore
4|Page
Course content
Learning outcomes At the end of the module the learner will be able to:
1. Analyse Database
2. Design Database
3. Implement Database
4. Secure Database
Learning outcome 1: Analyse Database Learning hours: 20
Indicative content
• Description of database fundamental
✓ Definition of key terms
Database
Data
Information
Entities
Attributes/Field
Records
Table
Database schema
DBMS
SQL
✓ Applications of database
✓ Advantages and Disadvantages of Database
✓ Identification of database models
Relational database
Hierarchical database
Network database
5|Page
Object oriented model
✓ Identification of database Relationship
One to one
One to many
Many to one
Many to many
✓ Determination of data types
Character
Number
Date
• Description of data dictionary
✓ Definition of data dictionary
✓ Elements of data dictionary
• Identification of database requirements
✓ Types of database requirements
Functional requirement
Non-functional requirement
✓ Methods to collect data
Interview
Documentation
Questionnaire
Observation
Resources required for the learning outcome
Equipment ▪ Computer
Materials ▪ Internet
Tools ▪ NA
6|Page
Facilitation techniques ▪ Brainstorming
▪ Demonstration
▪ Group discussion practical work
Formative assessment ▪ Written assessment
methods /(CAT) ▪ Oral presentation
▪ Performance assessment
Learning outcome 2: Design Database Learning hours: 25
Indicative content
• Description of database schema
✓ Introduction of database schema
✓ Types of database schema
✓ Data abstraction levels
✓ Types of data independence
• Design of conceptual database schema
✓ Description of conceptual database schema
✓ Entity relationship diagram (ERD)
Description of ERD
Components of ERD
Define relationships
Create an ERD
Draw an ERD (MS-Visio, Draw-Max)
• Design of logical database schema
✓ Description of logical database schema
✓ Table constraints
NOT NULL Constraint.
UNIQUE Constraint.
7|Page
DEFAULT Constraint.
CHECK Constraint.
PRIMARY KEY Constraint.
FOREIGN KEY Constraint.
✓ Convert conceptual database schema to logical database schema
• Optimization of database
✓ Data normalization
First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)
✓ Indexing
• Design of Physical database schema
✓ Description of DBMS
✓ Preparation of DBMS Environment (MySQL)
✓ Convert logic database schema to physical database schema
Resources required for the learning outcome
Equipment ▪ Computer
Materials ▪ Internet
Tools ▪ E-Draw max
▪ Microsoft Visio
▪ MySQL
Facilitation techniques ▪ Brainstorming
▪ Demonstration
▪ Group discussion practical work
Formative assessment ▪ Written assessment
methods /(CAT) ▪ Oral presentation
▪ Performance assessment
8|Page
▪ Project based assessment
Learning outcome 3: Implement Database Learning hours: 36
Indicative content
• Description to SQL
✓ Introduction of SQL
✓ SQL sub-languages
✓ SQL Operators
SQL Arithmetic Operators
SQL Bitwise Operators
SQL Compound Operators
SQL Logical Operators
• Application of DDL commands
✓ CREATE
Database
Table Constraints
Table
✓ ALTER Table
✓ DROP
Database
Table
✓ TRUNCATE Table
✓ MODIFY
Database
Table
• Application of DML commands
✓ INSERT
9|Page
✓ UPDATE
✓ DELETE
✓ CALL
✓ EXPLAIN CALL
✓ LOCK
• Application of DQL Command
✓ SELECT
✓ SQL aggregate function
✓ SQL clause
• Application of DCL commands
✓ GRANT
✓ REVOKE
• Application of TCL commands
✓ COMMIT
✓ SAVEPOINT
✓ ROLLBACK
✓ SET Transaction
✓ SET Constraints
Resources required for the learning outcome
Equipment ▪ Computer
Materials ▪ Internet
Tools ▪ MySQL
▪ Apache
▪ XAMPP
▪ WAMP
▪ LAMP
10 | P a g e
▪ Browsers
Facilitation techniques ▪ Brainstorming
▪ Demonstration
▪ Group discussion practical work
Formative assessment ▪ Written assessment
methods /(CAT) ▪ Oral presentation
▪ Performance assessment
▪ Project based assessment
Learning outcome 4: Implement Database security Learning hours: 19
Indicative content
• Enforcement of data access control
✓ Description of database security
Introduction of database security
Types of database security
✓ Data access control
✓ Access control policies
Identify the data classifications
Define roles and permission
✓ Authentication
Identify user accounts
Create privileges
Configure the authentication system
Test the authentication system
Monitor and maintain
✓ Authorization
Create roles
11 | P a g e
Assign permissions/privilege to roles
Assign roles to users
Test the authorisation system
Monitor and maintain
• Management of Auditing and logging
✓ Logging
Identify the logging requirements
Configure logging settings
Monitor log data
Analyse log data
Archive log data
Corrective action
✓ Auditing
Identify the data that needs to be audited
Execution of SQL command
Configure audit settings
Review audit
Analyse audit data
Corrective action
• Implementation of Data encryption
✓ Description of data encryption
✓ Application of encryption technics
Symmetric Encryption
Asymmetric Encryption
Hashing
• Configuration of database backup and restore
✓ Introduction of data backup and restore
✓ Backup Method
12 | P a g e
Full backup
Differential backup
Incremental backup
✓ Backup schedule
✓ Create Backup
✓ Perform recovery method
Full database recovery
Rollback recovery
Point-in-time recovery
✓ Test your backup and recovery plan
Resources required for the learning outcome
Equipment ▪ Computer
Materials ▪ Internet
Tools ▪ MySQL
▪ Apache
▪ XAMPP
▪ WAMP
▪ LAMP
▪ Browsers
Facilitation techniques ▪ Brainstorming
▪ Demonstration
▪ Group discussion practical work
Formative assessment ▪ Written assessment
methods /(CAT) ▪ Oral presentation
▪ Performance assessment
▪ Project based assessment
13 | P a g e
Integrated/Summative assessment
Integrated situation
D-STORE Ltd is a company that generates revenue from selling furniture products; It’s located in
Nyabihu district, western province.
D-STORE Ltd uses file system (books) to record information about the sales and inventory.
Company has a problem of non-efficient security, accessibility and management of information about
the stock-in, stock-out and customers. D-STORE Ltd company wants to switch to a digital system that
can be accessed easily by authorized employees.
They hired a database developer who develops for them the database that can be used to manage
sales, inventory, customer data, and to generate an invoice for all any placed order.
Tasks to be performed:
1. Analyze the database requirements and identify the different data sources that need to be
integrated.
2. Based on the requirements, design a database that includes conceptual database schema and
logic database schema. also prepare a MySQL DBMS environment
3. In this stage, create the physical database schema by applying SQL commands in DBMS and
Developer must developer a query to insert customer order with the following information:
Customer information and product information. And make another query to retrieve data to
be on invoicing such us: Customer information and product information, after that a developer
also must generate a product status report.
4. Perform database access control: apply database user authentication, database authorization
user and audit logs
14 | P a g e
5. Configuration of database backup
Instruction
1. Database is needed in 6 hours
2. Build the database with respect of table constraints
3. No Data redundancy allowed in database
4. All query performed must documented
5. D-STORE Ltd has the following employees’ positions:
o Managing director: They manage all company activities
o Stock manager: Manage and control all product in and out of the stock
o Stocker worker: Record sold products
Tools ▪ MySQL, Apache, XAMPP, WAMP, LAMP, Browsers, E-Draw
max
Equipment ▪ Computer
Materials/ Consumables ▪ Internet
Assessable Assessment criteria (Based Observation Marks allocation
Indicator
outcomes on performance criteria) Yes No
Learning [Link] requirements Ind.1: Database 5
outcome1: requirement
Analyse documentation is
database: papered
(10%)
Learning [Link] and logical Ind.1: Entities are 3
Outcome2: database schema design drown
Database Ind.2: Attributes are 2
design: drown
15 | P a g e
Ind.3: Primary key 1
(30%) are shown
Ind.4: Foreign key 1
are shown
Ind.5: Relationship 2
are identified
Ind.6: logic database 2
schema is sketched
[Link] DBMS Ind.1: XAMPP is 2
Environment installed
Ind.2: Apache is 1
started
Ind.3: MySQL is 1
started
Learning [Link] SQL Commands Ind.1: Database is 2
Outcome3: created
Implement Ind.2: Tables are 2
database: created
Ind.3: Relationship is 2
(40%) created
Ind.4: Data are 2
inserted
Ind.5: Product Order 4
is performed
Ind.6: Invoice data 4
are Fetched
Ind.7: Payment 2
status are updated
16 | P a g e
Ind.8: Product 2
quantities are
updated
Ind.1: Database 2
users are created
Ind.2: Roles are 1
created
Learning
[Link] database access Ind.3: Privileges are 2
Outcome 4:
control assigned to roles
Secure
Ind.4: Roles are 2
database:
assigned to users
(20%)
Ind.5: Logs are 1
defined
[Link] backup and Ind.1: Backup is 2
restore created
Total marks 50
Percentage Weightage 100%
Minimum Passing line % (Aggregate): 70%
17 | P a g e
Reference:
1. Afyouni, H. A., 2008 January 01. Database security and Auditing: Cengage Learning.
2. Afyouni, H. A., 2008 January 01. Database security and Auditing. s.l.:Cengage Learning.
3. Carlos Coronel, S. M., 2018-01-01. Database System. s.l.:Cengage Learning.
4. [Link], n.d. What is a database schema?. [Online]
Available at: [Link]
5. [Link] n.d. TCL Commands. [Online]
Available at: [Link]
6. IBM, n.d. Authentication versus access control. [Online]
Available at: [Link]
versus-access-control
7. Radmacher, M. A., 2017. Developing SQL Databases. s.l.:Mary Anne Radmacher.
8. Reagan, R., 2012. SQL Server® 2012 T-SQL Fundamentals. s.l.:Ronald Reagan.
9. Intellipaat, n.d. SQL Commands. [Online]
Available at: [Link]
sheet/?US
10. JavatPoint, n.d. Database Schema. [Online]
Available at: [Link]
11. [Link], n.d. Description of the database normalization basics. [Online]
Available at: [Link]
normalization-description
18 | P a g e
12. [Link], n.d. RelationalDBDesign. [Online]
Available at: [Link]
requirements-
[Link]#:~:text=Requirements%20Analysis%20is%20the%20stage,data%20needs%
20to%20be%20accessed.
19 | P a g e
