Application of databases

Students must be able to create a database management system (DBMS) and relational database management system (RDBMS) considering one-to-one, one-to-many, and many-to-many relationships.[edit]

To create a database management system (DBMS) and relational database management system (RDBMS), you will need to consider the relationships between the data that you want to store. There are three main types of relationships: one-to-one, one-to-many, and many-to-many.

Here is an overview of each type of relationship:

One-to-one: In a one-to-one relationship, each record in one table is related to exactly one record in another table. For example, you might have a table of employees and a table of their addresses, where each employee has one address.
One-to-many: In a one-to-many relationship, each record in one table is related to one or more records in another table. For example, you might have a table of customers and a table of orders, where each customer can have multiple orders.
Many-to-many: In a many-to-many relationship, each record in one table is related to one or more records in another table, and each record in the second table is also related to one or more records in the first table. For example, you might have a table of students and a table of courses, where each student can take multiple courses and each course can have multiple students.

To create a DBMS and RDBMS, you will need to design the tables and relationships that make up the database. This will involve defining the data types and attributes of each table, and specifying the relationships between the tables using foreign keys. You will also need to consider the constraints and rules that should be applied to the data, such as uniqueness, nullability, and referential integrity.

Once you have designed the database, you can use a DBMS or RDBMS software tool to create and manage the database. This will typically involve creating the tables and relationships in the database, as well as writing SQL (Structured Query Language) queries to insert, update, and retrieve data from the database.

Students must be able to use a range of management functions and tools available within a DBMS by focusing on the creation, manipulation and interrogation of a database.[edit]

There are a range of management functions and tools available within a database management system (DBMS) that can be used to create, manipulate, and interrogate a database. Some examples of these functions and tools include:

SQL (Structured Query Language): SQL is a programming language that is used to create, modify, and query databases. It is the standard language for interacting with most DBMSs and is used to perform a wide range of tasks, including creating tables and relationships, inserting and updating data, and retrieving data from the database.
Data definition language (DDL): DDL is a set of SQL commands that are used to define the structure of a database, including the tables, columns, and relationships between them. Examples of DDL commands include CREATE, ALTER, and DROP.
Data manipulation language (DML): DML is a set of SQL commands that are used to manipulate data in a database, including inserting, updating, and deleting records. Examples of DML commands include INSERT, UPDATE, and DELETE.
Data control language (DCL): DCL is a set of SQL commands that are used to control access to a database, including granting and revoking permissions and creating and managing users and roles. Examples of DCL commands include GRANT, REVOKE, and CREATE USER.
Data query language (DQL): DQL is a set of SQL commands that are used to retrieve data from a database. Examples of DQL commands include SELECT and SET.
Data dictionary: A data dictionary is a collection of metadata about a database, including definitions of tables, columns, and relationships. A data dictionary can be used to document the structure and content of a database, as well as to manage the database itself.

By using these management functions and tools, you can create, manipulate, and interrogate a database to store, organize, and retrieve data in a structured and efficient way.

Students must be able to use appropriate DBMS features to secure the database such as data validation, access rights and data locking.[edit]

There are several DBMS features that can be used to secure a database and protect the data it contains. Some examples of these features include:

Data validation: Data validation is the process of checking that data meets certain criteria before it is stored in the database. This can help to ensure that the data is accurate, complete, and consistent. DBMSs often include tools for defining and enforcing data validation rules, such as data type and length constraints, to ensure that only valid data is stored in the database.
Access rights: Access rights refer to the permissions that users or groups of users have to access and modify data in the database. DBMSs typically include tools for defining and managing access rights, such as granting and revoking permissions and creating and managing users and roles. This can help to control who has access to which data and what they are allowed to do with it.
Data locking: Data locking is a mechanism that is used to prevent multiple users from accessing and modifying the same data simultaneously, which can lead to conflicts and data inconsistencies. DBMSs often include tools for managing data locking, such as locks on rows or tables, to ensure that data is accessed and modified in a consistent and controlled way.

By using these DBMS features to secure the database, you can help to ensure that the data is protected from unauthorized access, errors, and inconsistencies, and that the integrity of the database is maintained.

Students must be able to: create table(s), record(s), field(s), primary key, secondary key, foreign key, composite primary key, forms, queries, and reports in the context of a database; and justify the selection of a particular data type in a specified situation, for example, integer or floating point.[edit]

To create a database, you will need to create tables, records, and fields to store and organize your data. Some specific concepts that you may need to consider when creating a database include:

Tables: A table is a collection of data that is organized into rows and columns. Each row represents a record, and each column represents a field. Tables are used to store data about a specific entity or subject, such as customers or orders.
Records: A record is a collection of data that represents a single entity or subject. For example, in a customer table, each record might represent a single customer, with fields such as name, address, and phone number.
Fields: A field is a single piece of data within a record. Fields are used to store specific types of data, such as text, numbers, or dates.
Primary key: A primary key is a field or set of fields that is used to uniquely identify each record in a table. A primary key cannot be null and must be unique within a table.
Secondary key: A secondary key is a field or set of fields that is used to support searching and sorting of records in a table. Secondary keys are not unique and can be null.
Foreign key: A foreign key is a field or set of fields that is used to establish a relationship between two tables. It references the primary key of another table and is used to ensure data integrity by preventing the creation of records that do not have a corresponding primary key in the related table.
Composite primary key: A composite primary key is a primary key that is made up of two or more fields. This is used when a single field is not sufficient to uniquely identify a record.
Forms: Forms are user interfaces that are used to input, update, and view data in a database. Forms can be used to simplify the process of entering data and ensure that it is entered consistently.
Queries: Queries are requests for specific data from a database. They can be used to filter, sort, and aggregate data, and to perform calculations on it.
Reports: Reports are formatted outputs of data from a database. They can be used to present data in a clear and organized way, such as for analysis or presentation.

When selecting data types for fields in a database, you should consider the type of data that you will be storing and the operations that you will be performing on that data. Some common data types include:

Integer: An integer is a whole number that can be positive, negative, or zero. Integer data types are often used to store data that can be counted or sequentially ordered, such as ages or product quantities.

Floating point: A floating point number is a number with a decimal point, such as 3.14 or 1.2345. Floating point data types are often used to store data that has a fractional component, such as prices or measurements.

Text: Text data types are used to store character strings, such as names or addresses. Text data types can vary in size and may include options for storing fixed-length or variable-length strings, as well as for defining the character encoding used.

Date/time: Date/time data types are used to store date and time values, such as birthdays or order dates. Date/time data types can vary in the level of detail they store, such as the year, month, day, hour, minute, and second.

When selecting a data type for a field in a database, you should consider the type of data that you will be storing and the operations that you will be performing on that data. For example, if you will be storing numeric data that requires precise calculations, you may want to use a floating point data type. On the other hand, if you will be storing data that can be counted or sequentially ordered, you may want to use an integer data type.

Students must be able to implement data verification and create validation methods appropriate to a given context.[edit]

Data verification is the process of checking that data meets certain criteria before it is stored in a database. This can help to ensure that the data is accurate, complete, and consistent. There are several methods that can be used to implement data verification and create validation methods appropriate to a given context. Some examples include:

Data entry rules: Data entry rules can be used to ensure that data is entered consistently and in the correct format. For example, you might use data entry rules to require that dates are entered in a specific format or that phone numbers include a certain number of digits.
Data type constraints: Data type constraints can be used to ensure that data is of the correct type. For example, you might use a data type constraint to ensure that a field that is intended to store numbers only contains numeric values.
Range constraints: Range constraints can be used to ensure that data falls within a specific range. For example, you might use a range constraint to ensure that a field that stores ages only contains values between 0 and 120.
Lookup tables: Lookup tables can be used to provide a list of valid values for a field. For example, you might use a lookup table to provide a list of valid countries that can be selected for a field that stores a customer's country of residence.
Data cleansing: Data cleansing is the process of identifying and correcting or removing errors or inconsistencies in data. This can involve methods such as standardizing formats, identifying and removing duplicates, and correcting spelling errors.

By implementing data verification and creating appropriate validation methods, you can help to ensure that the data in your database is accurate and consistent, which can improve the integrity and reliability of the database as a whole.

Students must be able to normalise a database by creating 1st Normal Form (1NF), 2nd Normal Form (2NF) and 3rd Normal form (3NF).[edit]

Database normalization is the process of organizing a database in a way that minimizes redundancy and dependency. Normalization typically involves breaking a large, complex table into smaller, simpler tables and defining relationships between them. There are several levels of normalization, including First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

Here is an overview of each level of normalization:

First Normal Form (1NF): In 1NF, a table is considered to be in first normal form if it meets the following criteria:

The table has a primary key.
There are no repeating groups of data.
All data in the table is atomic, meaning that it cannot be further divided into smaller pieces.

To achieve 1NF, you may need to break a table into smaller tables and define relationships between them using primary and foreign keys.

Second Normal Form (2NF): In 2NF, a table is considered to be in second normal form if it meets the following criteria:

The table is already in 1NF.
All non-key fields are dependent on the entire primary key.

To achieve 2NF, you may need to further break down tables that are in 1NF to remove partial dependencies on the primary key.

Third Normal Form (3NF): In 3NF, a table is considered to be in third normal form if it meets the following criteria:

The table is already in 2NF.
There are no transitive dependencies, meaning that all non-key fields are directly dependent on the primary key and do not depend on other non-key fields.

To achieve 3NF, you may need to further break down tables that are in 2NF to remove transitive dependencies.

Normalization is an important process for designing a database because it can help to minimize redundancy, improve data integrity, and simplify the structure of the database. By creating 1NF, 2NF, and 3NF tables, you can ensure that the database is organized in a structured and efficient way, which can make it easier to insert, update, and retrieve data, and reduce the risk of data inconsistencies.

It's worth noting that normalization is a trade-off between simplicity and complexity. While normalization can help to simplify the structure of a database, it can also make it more complex to query and retrieve data from the database. Therefore, it is important to consider the specific needs and goals of the database when deciding how much normalization is appropriate.

Students must be able to construct entity-relationship diagrams appropriate to a given context.[edit]

Students must be able to: create forms for inputting data, simple and complex queries to access data in a file, and create reports for a given purpose; use Boolean operators such as AND, OR, NOT, create parameter queries and create derived fields; and discuss the language as a tool for data interrogation, for example, writing queries on SQL.[edit]

Students must be able to import data from a sequential file to a random access database file, and export the data back into a sequential file after processing.[edit]