Download RELATIONAL DATABASE MANAGAMENT and more Essays (university) Database Programming in PDF only on Docsity!
Unit 18: Relational Database
Development
Assignment 1: Learning Aim A
- CONTENTS............................................................................................................................. CONTENTS
- Introduction............................................................................................................................
- Evaluation of Relational Database Models..........................................................................
- Types of relational database management systems..........................................................
- MySQL...............................................................................................................................
- Microsoft SQL Server.........................................................................................................
- Oracle database.................................................................................................................
- PostgreSQL........................................................................................................................
- Relational Data Structures.................................................................................................
- Relation..............................................................................................................................
- Attribute..............................................................................................................................
- Domain...............................................................................................................................
- Tuple...................................................................................................................................
- Degree................................................................................................................................
- Cardinality..........................................................................................................................
- Relational Database...........................................................................................................
- Relational Algebra: Sets and Symbols...............................................................................
- Entity-Relationship Models in workplace4training.............................................................
- system. Important types of relationships include:............................................................. ER models are utilised to visually depict the data and its interconnections within a
- One-to-One (1:1)................................................................................................................
- One-to-Many (1:M).............................................................................................................
- Many-to-Many (M: N).........................................................................................................
- Data manipulation in Relational databases and structures...............................................
- Insertion..............................................................................................................................
- Updating.............................................................................................................................
- Modifying and Deleting.......................................................................................................
- Retrieving Data...................................................................................................................
- Administration.....................................................................................................................
- Security..............................................................................................................................
- Integrity...............................................................................................................................
- Normalisation process..........................................................................................................
- Stages of normalisation......................................................................................................
- Unnormalized Form (UNF)...........................................................................................
First Normal Form (1NF).............................................................................................. 7 Second Normal Form (2NF)......................................................................................... 7 Third Normal Form (3NF)............................................................................................. 7 Boyce-Codd Normal Form (BCNF).............................................................................. 7 Example Using workplace4training Data............................................................................. Unnormalised vs Normalised data..................................................................................... 8 Importance of Normalisation................................................................................................ 9 Anomalies........................................................................................................................... Update anomaly:.......................................................................................................... 9 Insertion anomaly:........................................................................................................ 9 Deletion anomaly:......................................................................................................... Keys and Referential.......................................................................................................... 9 Primary Key.................................................................................................................. 9 Foreign key................................................................................................................. Composite Key........................................................................................................... 10 Indexing and data dictionary............................................................................................ 10 Indexing...................................................................................................................... Data Dictionary........................................................................................................... 10 Cascading Updates and Query operation........................................................................ 10 Cascading and Deletion............................................................................................. 10 Query operations........................................................................................................ Advantages of Normalisation............................................................................................. 10 Disadvantages of Normalisation......................................................................................... Conclusion............................................................................................................................
Introduction
As part of my assignment, l have been asked to evaluate the principles of relational databases and the relevance of normalisation, emphasising how these concepts result in reliable and efficient data structures. The relational database is essential in the context of Workplace4training, a training provider with a broad range of data( such as assessors, courses, venues, schedules, and students). In this report, l will evaluate the relational database models, types of relational database management systems, the Structure of Relational databases, entity-relationship Models, Data manipulation and Normalisation, which will help reduce redundancy and prevent data anomalies that could affect daily operations.
Evaluation of Relational Database Models
Relational Database Management System (RDBMS) allows you to organise and manipulate data using tables and is compatible with a variety of operating systems like Windows, macOS, and Linux. For example, systems such as Microsoft SQL
Attribute
Attributes are the columns in a table, each specifying a data field that describes a property of the entity represented by the table. For example, in an "Employees" table, attributes might include "Employee ID," "Name," and "Department."
Domain
A domain restricts what an attribute can hold. It also maintains data integrity by limiting the type of data that can be entered into a column. For example, the domain for the “Age” attribute could be: any integer ranging from 18 to 65 or the allowed number for phone number format.
Tuple
A tuple is a single record, or row of a table, that consists accurately as many elements as the fields in the associated table. For instance, a record in the "Employees" table could be used to record details about a particular employee.
Degree
The degree of a relation indicates how many attributes (columns) it holds. For example, if a table consists of four columns, its degree is 4.
Cardinality
Cardinality indicates the number of tuples (rows) in a relation (table). For example, if a "Customers" table has 50 rows, its cardinality is 50. This helps assess the size and scalability of the dataset.
Relational Database
A relational database is a group of relations (tables) that are related to each other through common attributes, enabling efficient data retrieval and manipulation
Relational Algebra: Sets and Symbols
Relational algebra provides a set of operations (such as select, project, union, and join) to query and manipulate data in relational databases that underpin SQL queries. These operations are foundational for formulating queries and understanding query optimisation.These operations are essential for both querying and modifying data in a relational database.
Entity-Relationship Models in workplace4training
ER models are utilised to visually depict the data and its interconnections within a system. Important types of relationships include:
One-to-One (1:1)
Each entity in the initial set has a connection to at most one entity in the following set, and the reverse is also true. For example, each assessor has one speciality course.
One-to-Many (1:M)
An entity in the first set can be associated with several entities in the second set, but each entity in the second set corresponds to only one in the first set. For instance, one venue can host many courses.
Many-to-Many (M: N)
Records in one table can be associated with multiple records in another table, and vice versa. For example, students and courses, where each student can enrol in multiple courses, and each course can have multiple students. This relationship typically requires a junction table like StudentCourse to implement.
Data manipulation in Relational databases and
structures
Relational databases easily handle dynamic data operations. They are each specifically designed for such a purpose. In a typical system like Workplace4training, database administration and users may perform:
Insertion
Creating new entries when a course or student is registered
Normalisation process
Is an important process in database design that aims to improve the databases' efficiency, consistency, as well as the accuracy through organizing attributes for reducing or eliminating data redundancy; this process ensures that the database adapts to the changing business needs as it helps effectively manage and properly maintain data; the primary stages are:
Stages of normalisation
Unnormalized Form (UNF)
Data in an unnormalized format might be stored when one large table has redundant information, which is repeated (for example, the same assessor appears within multiple course records). Anomalies are usual characteristics of such a format.
First Normal Form (1NF)
Each field must contain solely atomic values. 1NF thus requires values that are, in fact, indivisible. For Workplace4training, to make certain that there are no multi-valued fields in existence, every single column in the assessors’ table has to contain only one value for each record.
Second Normal Form (2NF)
Beyond atomicity, 2NF eliminates any partial dependencies that exist. Each non-key attribute has to be fully dependent on the entirety of the composite key within a table that has a composite key. This is true, such as if a course schedule table used both Course ID as well as Venue ID as its primary key, although not part of it.
Third Normal Form (3NF)
3NF removes transitive dependencies. This means that non-key attributes, therefore, must not depend on zero on other non-key attributes. For example, a unique Venue ID is what should link a separate table in which venue address details reside, rather than repeatedly storing those said details in the course schedule table.
Boyce-Codd Normal Form (BCNF)
It is a stricter version of 3NF, ensuring that for every functional dependency in the table, the determinant is a candidate key. This improves data consistency, particularly in complicated data sets where several candidates may exist.
Example Using workplace4training Data
Unnormalised vs Normalised data
Importance of Normalisation
Anomalies
Update anomaly:
Without normalisation, it is necessary to update multiple rows across tables to change an assessor's phone number. Normalisation guarantees that the assessor's data is kept in a single location.
Insertion anomaly:
occurs when new data cannot be added without including unnecessary or unavailable information. Properly structured tables allow new records to be added without unnecessary duplication. In this case, without a properly structured table, a new course cannot be inserted without a student.
Query operations
Joins, unions, and intersections built upon normalised tables greatly improve the precision as well as performance with data retrieval, thereby ensuring reports to decision-making stay accurate and up-to-date. For example, to show course details with student names.
Advantages of Normalisation
● Normalisation supports data consistency across applications, making it easier to integrate different applications and ensuring that all users have access to the data. ● Normalisation eliminates duplicate data, making it easier and consistent to maintain small databases. ● As datasets are reduced in size, accessing data becomes faster and shorter, which helps improve time and speed. ● Normalisation maintains consistency and accuracy by separating data into logical groupings and linking tables.
Disadvantages of Normalisation
● More storage is needed as normalisation requires more tables and additional join operations to access data, leading to higher hardware costs required to support the database. ● Normalisation can result in data loss as it requires data to be split, making it harder to understand the relationships between different pieces of data. ● Proper implementation of normalisation requires expert knowledge of database design and the normalisation process. ● Normalisation requires data to be organised, which could limit flexibility if the database is not updated regularly ● Over-normalisation can result in excessive table fragmentation, making the database harder to understand.
Conclusion
In conclusion, by using relational database concepts and normalising data, Workplace4training can implement a flexible, scalable, and effective data management process. By using relational tables to place data, and normalising data
(within constraints of each table), Workplace4training can reduce redundancy, avoid anomalies and produce consistent information across several departments. This effective data management process allows no matter where sequentially through the organisation's structure (assessors, students, courses, venues) to retrieve and amend and track data, without data loss or duplication. Each of these improvements will have a positive impact on the decision-making processes of the organisation, operational efficiencies and ultimately, improvements to the training delivery conducted by staff, where there is an improvement in training outcomes. Normalisation is an important part of relational database management that significantly improves data accuracy, consistency, and integrity. There are so many benefits of normalisation to Workplace4training, including reliable data storage and better efficiency.
