Report On Relational Databases: Analysis, Benefits & Trends Sample

Introduction :Report On Relational Databases

A relational database can be a form of database that stores all the information in tables which can be related to every different. The tables consist of rows and columns, with the columns talking to properties and the rows representing facts. The relational database lies in its ability to install connections between the tables through the utilization of crucial keys and out of doors keys. Essential keys incredibly distinguish every push in a table whereas outdoor keys interface the facts between tables. To plan a successful social database, the technique of normalization is utilized to prepare the statistics into tables and diminish facts extra. Normalization consists of breaking down large tables into littler, associated tables and characterizing connections between them. Extra ideas that are key in social database plan incorporate referential astuteness, which ensures consistency among related data, and the make use of suitable records types for desk regions. The social display offers an effective manner to save and get to statistics in a prepared way. The connections empower quick questions and active sees of associated statistics focused over exceptional tables. Here the five tables can be created in the part.

Discussion

Task 1: The normalization process

In the normalization process there in the student portal database table there are five tables created which are basically student table, modules table, assessment table. Enrollment table and courses table. These five relational databases are created and store the data in the database. The student table is the central table in this student portal database design. It stores key information about each student enrolled in the college. This includes attributes like their student ID, first name of students, last name of students, and date of birth (Cao et al. 2020). The student ID field serves as a primary key which uniquely identifies each student record. Additional details like their assessment, courses, enrollments and modules connect to the student table via relationships with other tables in the database. The student table enables consolidating core student data in one place to allow easy access and analysis.

Figure 1: Student table in relational database

(Source: Self-created in LibreOffice Base)

The modules table stores all the data about the different subject modules offered to students at the college. Each module has a unique module ID, module name, module description of the subject topics covered, the teacher taking the class and the semester it will be taught. The module ID serves as the primary key which uniquely identifies each module's id (Freitag et al. 2020). The module table connects to the student table via module enrollment and a bridge table to depict the many-to-many relationship between students and modules. This modules table enables standardized storage of module metadata and linking student enrollment across college terms.

Figure 2: Module table in relational database

(Source: Self-created in LibreOffice Base)

The Course table in the relational database contains the details about the academic courses offered at the college. This includes attributes like a unique course ID, course name, and course description of the full course, the broad department it belongs to, web development, mobile app design eligibility criteria, total fees, and an indication if it is a full-time or part-time course option (Hulsebos et al. 2023). The course ID serves as the primary key. The course table is linked to the student table to track student enrollment and completed courses over their academic journey. The centralized course data aids curriculum planning and student enrollment management.

Figure 3: Course table in relational database

(Source: Self-created in LibreOffice Base)

This entity relationship diagram (ERD) represents a student portal database with centralized tables for storing student, course and module data, as well as bridging tables that capture enrollments and assessment (Imtiaz et al. 2021). The one-to-many relationships allow queried linkage. For example, each course has multiple enrolled students, hence the double line.

Figure 4: ERD of Student portal database

(Source: Self-created in LibreOffice Base)

Attributes and primary keys that uniquely identify records are indicated. This ERD visualizes a robust relational database design that enables efficient data storage, querying and analysis needed by the student portal database administration of students.

Task 2: Relational database

Relational database organizes the data into one or more tables with columns and rows. Each row appears for a record with a unique ID called the primary key. The columns represent the details and attributes tied to each record. Relationships can be created between tables by using foreign keys that connect records across tables (Cappuzzo et al. 2020). For example, a Student table and Course table can connect via a Course ID primary key. Joins enable associating related data across these table relationships. Additional key concepts include data integrity constraints like not null values to enforce data completeness. Normalization principles guide how to best structure tables and minimize duplications (Al-Kasasbeh et al. 2021). Organizing data into predefined schema and simple tables with relations between them enables efficient storage, integrity and flexible access of data at scale. The relational model's simple structure and access capabilities form the basis of relational database management systems like Sql Server, Oracle and MySQL that are widely used today.

The theory behind the types of relationships and fields

Figure 5: Course table field type

(Source: Self-created in LibreOffice Base)

This student portal database engages one-to-many relationships depicted by a single line from course to students, indicating each course has multiple enrolled students. Many-to-many relationships are established using bridge tables like enrollment which connects students and modules (Reddy et al. 2022). The datatypes are varchar for character types, numbers for phone numbers and ID and date data type is used for date containing values. Appropriate data types are assigned to attributes considering usage varchar for names to allow varied lengths, number and date types for IDs, scores and dates requiring calculations. Referential integrity ensures consistency among connected data via enforcement of foreign key matches to primary keys when inserting or updating records. Normalization splits data into multiple tables, like separate student and module tables connected by enrollment, to remove redundancies and improve integrity. These theories of relationships, data types, integrity constraints and normalization principles enable optimal database performance and reliability.

Get Extra 10% OFF on your WhatsApp order!

use my discount

Apply techniques to ensure data validation

Ensuring th? data validation and consist?ncy in this school databas? can b? achi?v?d through s?v?ral t?chniqu?s lik? s?tting data typ?s and fi?ld constraints on tabl?s such as allowing only dat? valu?s in dat? fi?lds and r?stricting invalid ?ntri?s. D?fining th? NOT NULL constraints to avoiding incompl?t? r?cords and c?rtain attribut?s. For?ign k?y r?lationships b?tw??n tabl?s will limit invalid r?f?r?nc?s through r?f?r?ntial int?grity ch?cks (Lin et al. 2020). Normalization will r?duc? data duplication across tabl?s that can introduc? inconsist?nci?s for ?xampl? and ?xtracting subj?ct modul?s into an s?parat? normaliz?d tabl? avoids r?p?at?d modul? d?tails across multipl? cours?s. Adding a uniqu? constraint onto fi?lds lik? stud?nt ID and cours? cod? will ?nsur? no duplicat? ?ntri?s ar? cr?at?d. Trigg?rs can validat? ?ntri?s add?d/updat?d in th? background b?for? p?rmitting chang?s. Applying th?s? validation ch?cks ?nsur?s high?r data accuracy standards and consist?ncy in th? databas? and whil? normalization and k?ys minimiz? r?dundanci?s.

Figure 6: Query table and connection between tables

(Source: Self-created in LibreOffice Base)

This Query diagram represents the interrelated query tables that extract meaningful views from a potentially large student portal database. The central student table connects with enrollment, course, assessment and other tables. The enrollment query table joins student, course and module data (Barbosa et al. 2020). The assessment overview query provides a snapshot of student assessment complication rate by connecting their assessment Id with personal information from the student table. The course summary view condenses course and participating student information in one place by joining those tables. These query tables are created by applying relationships and filters to simplify data for the end user, avoiding slower run-time queries. Allowing reusable queries optimizes performance while securing the normalized base tables behind-the-scenes. Predefined query tables grant easy access to desired projections of the database for administrators, faculty and students.

Justify techniques used to ensure data validation, consistency

Justifying th? t?chniqu?s Impl?m?nting th? r?f?r?ntial int?grity via primary k?y and for?ign k?y r?lationships validat?s th? consist?ncy of int?rr?lat?d data. Enforcing for?ign k?ys from ?nrollm?nt to r?f?r?nc? ?xisting primary stud?nt and modul? r?cords ?nsur?s invalid r?f?r?nc?s cannot b? cr?at?d. Applying NOT NULL constraints and data typ? ch?cks also validat? compl?t?n?ss and accuracy as incorr?ct data is r?j?ct?d. Normalization promot?s storing data lik? stud?nt d?tails only onc? in a mast?r tabl? and avoiding duplication across cours? modul?s and ?liminating updat? anomali?s. Allowing only uniqu? stud?nt ID valu?s is critical to avoid confusing id?ntiti?s. Trigg?rs that automatically validat? ?ntri?s on cr?ation furth?r ?nhanc? int?grity ch?cks (Seabolt et al. 2020). Th?s? t?chniqu?s to validat? accuracy and uphold consist?ncy across r?lat?d data and ?liminat? r?dundant unstabl? copi?s ultimat?ly improv? th? r?liability of this student portal databas?. By donating validity rul?s and locking r?lat?d data mod?ls and consolidating non atomic data and issu?s from bad and contradictory or duplicat?d data can b? minimiz?d ?ff?ctiv?ly.

Figure 7: Reports of the assessment

(Source: Self-created in LibreOffice Base)

These assessment reports provide the critical visibility into student performance and submission of assessment. The student assessment report joins assessment ID, due date and assessment name with personal details in a printable report format for progress reviews and analyzing this discussion of the performance of students. The attendance frequency report allows quickly reviewing absenteeism rates for at-risk student intervention. The student transcript summarizes courses completed by each student helpful for new student orientation. These reports become possible by linking normalized data in this student portal database using primary-foreign key relationships that connect student,course, assessment and enrollment details scattered across tables. Report generation tools simplify report creation without SQL scripts. Configurable reporting allows drilling down into student assessment due date results and courses by customizable parameters for a targeted analysis. Customizable reports unlock deeper analytics from harmonized backend data.

Task 3: Enhance the functionality of a database

Enhanc? th? functionality databas? through t?sting is vital to validat? all compon?nts of this stud?nt portal databas? work as int?nd?d. 10 t?st cas?s validat? critical functionality bas?d on th? sp?cifications. T?st 1 ch?cks if th? und?rlying stud?nt tabl? structur? ?xists. T?st 2 v?rifi?s th? stud?nt ID auto numb?ring qu?ry functions prop?rly wh?n adding n?w r?cords. T?st 3 ins?rts sampl? stud?nt data and validat?s accurat? storag?. T?st 4 ch?cks primary k?ys w?r? cr?at?d corr?ctly to ?nforc? uniqu?n?ss of stud?nts. Updating a stud?nt phon? numb?r maps to R?quir?m?nt 5 to stor? contact d?tails. T?st 6 validat?s th? ass?ssm?nt dat? fi?ld format adh?r?s to th? dat? typ? constraint. Cr?ating and ?diting t?st forms ch?cks form functionality p?r R?quir?m?nt 7. T?st 8 p?rforms ?nd to ?nd t?sting by modifying r?cords and ch?cking updat?d valu?s sav?d corr?ctly across r?lationships (Ma and Molnár, 2022). T?sts 9 and 10 v?rify r?f?r?ntial int?grity constraints on cours? cod?s and ?nrollm?nt links b?tw??n tabl?s to m??t data consist?ncy n??ds. R?cordin' pass/fail status and priority l?v?ls allows tracking t?st cov?rag? and r?sults. This plan t?sts critical compon?nts not?d in sp?cifications through sampl? data and us? cas?s. Maintaining this standardiz?d log facilitat?s r?gr?ssion t?sting with futur? softwar? updat?s to ?nsur? no unint?nd?d impacts or r?gr?ssions occur.

Figure 8: Testing on test log

(Source: Self-created in LibreOffice Base)

Based on the complaint that the student table contained a restricted data field, falling flat to meet organizational desires, the table plan requires improvement. Particularly, directors and affirmation agencies stated requiring understudy contact numbers of students for improved outreach functionality. Upgrading the table by means of SQL instructions to encompass a current varchar kind PhoneNumber discipline empowers capturing this element (Elkins and Ostrom, 2021). At that factor a form to maintain understudy statistics must implant this unused area for admins to populate numbers per understudy. Activating an observer that fires whilst a document is covered without a cellphone number can uphold completeness.

Figure 9: Updating the phone number

(Source: Self-created in LibreOffice Base)

Alternatively, a partitioned understudy contact bridge desk can interconnect understudies with boundless touch techniques like emails and addresses, encouraging a many-to-many dating planned for future touch channel extension. Changing table systems dangers affecting existing objects like enrollment records. Intensive relapse trying out will approve no issues engender over referential keenness imperatives. Overhauling per unequivocal proposals concentrates vital understudy information already excluded. This straightforwardly progresses law proficiency moreover partner utility of this scholastic database framework.

Figure 10: Data display form

(Source: Self-created in LibreOffice Base)

The above snip of the form provides a user-friendly interface to view and search student details from the student portal database, centralizing all information in one place. Key fields are surfaced like student ID, first name of student, last name of student, date of birth, contact number added based on user feedback. Navigation allows browsing student records one by one or via a text search. An admin can verify information accuracy and take appropriate actions like updating details through integrated edit capability. Data visibility is enhanced by showing module enrollment and attendance percentages for each student without needing to scan multiple tables. Aggregating critical student data components into a centralized form gives administrators a convenient consolidated snapshot to manage student records effectively. Provided with search and visualization tools, this creates a comprehensive dashboard view.

Conclusion

The relational databases provide an organized student portal database and flexible way to store, access and manage data by structuring information into interconnected tables. Implementing normalization requirements and integrity constraints guarantee optimal performance and reliability. The student portal database displays these concepts via entities for students, assessment courses and modules linked through carefully modeled relationships. Data validation techniques like defining Not Null, field data types and unique constraints accurately validate information as per specifications, while foreign keys enforce referential integrity between tables. Together these database theory elements enable consolidating student data previously scattered, now queried through predefined views for easy access. Updating the student table by adding phone numbers directly improves administration capabilities. Encompassing testing procedures validate all components function as intended, without negatively impacting the enhanced system. The productivity unlocked via data consistency, redundancy elimination and queried visibility confirms the enduring value of relational databases for managing enterprise information. Additional functionality and analytics can be incorporated to continue leveraging its solid relational foundations.

References

Journals

• Cao, W., Liu, Y., Cheng, Z., Zheng, N., Li, W., Wu, W., Ouyang, L., Wang, P., Wang, Y., Kuan, R. and Liu, Z., 2020. {POLARDB} Meets Computational Storage: Efficiently Support Analytical Workloads in {Cloud-Native} Relational Database. In 18th USENIX conference on file and storage technologies (FAST 20) (pp. 29-41).
• Freitag, M., Bandle, M., Schmidt, T., Kemper, A. and Neumann, T., 2020. Adopting worst-case optimal joins in relational database systems. Proceedings of the VLDB Endowment, 13(12), pp.1891-1904.
• Hulsebos, M., Demiralp, Ç. and Groth, P., 2023. Gittables: A large-scale corpus of relational tables. Proceedings of the ACM on Management of Data, 1(1), pp.1-17.
• Imtiaz, N., Thorn, S. and Williams, L., 2021, October. A comparative study of vulnerability reporting by software composition analysis tools. In Proceedings of the 15th ACM/IEEE International Symposium on Empirical Software Engineering and Measurement (ESEM) (pp. 1-11).
• Cappuzzo, R., Papotti, P. and Thirumuruganathan, S., 2020, June. Creating embeddings of heterogeneous relational datasets for data integration tasks. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (pp. 1335-1349).
• Al-Kasasbeh, M., Abudayyeh, O. and Liu, H., 2021. An integrated decision support system for building asset management based on BIM and Work Breakdown Structure. Journal of Building Engineering, 34, p.101959.
• Reddy, H.B.S., Reddy, R.R.S., Jonnalagadda, R., Singh, P. and Gogineni, A., 2022. Analysis of the Unexplored Security Issues Common to All Types of NoSQL Databases. Asian Journal of Research in Computer Science, 14(1), pp.1-12.
• Ma, C. and Molnár, B., 2022. Ontology learning from relational database: Opportunities for semantic information integration. Vietnam Journal of Computer Science, 9(01), pp.31-57.
• Elkins, G.E. and Ostrom, B., 2021. Long-term pavement performance information management system user guide (No. FHWA-HRT-21-038). United States. Federal Highway Administration. Office of Infrastructure Research and Development.
• Lin, X.V., Socher, R. and Xiong, C., 2020. Bridging textual and tabular data for cross-domain text-to-sql semantic parsing. arXiv preprint arXiv:2012.12627.
• Barbosa, M.H.G. and Maia, P.H.M., 2020, March. Towards identifying microservice candidates from business rules implemented in stored procedures. In 2020 IEEE International Conference on Software Architecture Companion (ICSA-C) (pp. 41-48). IEEE.
• Seabolt, E.E., Nayar, G., Krishnareddy, H., Agarwal, A., Beck, K.L., Terrizzano, I., Kandogan, E., Kunitomi, M., Roth, M., Mukherjee, V. and Kaufman, J.H., 2020. Functional genomics platform, a cloud-based platform for studying microbial life at scale. IEEE/ACM Transactions on Computational Biology and Bioinformatics, 19(2), pp.940-952.