1. Introduction
ProjectsRus case study is an illustration of a dynamic business environment whereby staff members are assigned to various client-sponsored projects and their roles varied based on the project requirements. A conceptual schema that captures the key objects such as Staff, Project, Client and Role and associations have been developed through Object-Role Modelling (ORM). The representation of all entity types, attributes and association were made in correct ways resulting in the indirect alignment between the ORM model and a relational database schema. This mapping utilises a Conceptual Schema Design Procedure (CSDP) and results into an organised relational design that assures data integrity and operational requirements of the real world.
Students looking for academic guidance can explore best assignment help Australia for support with understanding complex assignment requirements and structuring their work.
2. Task 1: Conceptual Data Model for ProjectsRus
2.1 Case Study Overview
The case study of the ProjectsRus offers a business context in which employees are assigned to work on projects on behalf of various clients. The project sponsor, approval date, revision date, the project description, period and scope are all recorded in the form of a project charter with the personal and employment details of respective staff members. Their roles vary according to the project and a member of staff might be performing one role in a given project and a different role in another project (Ding et al. 2025). According to this case study, using the first three processes of the Conceptual Schema Design Procedure (CSDP) on Object-Role Modeling, a conceptual schema was designed.
2.2 Transforming Examples into Elementary Facts
The first step was the conversion of familiar examples of the case study into elementary facts. Such as Stuff member S100 is called Charlie summarizes the fact that staff have a name and an identifier. Equally, the project PRJ_515 is sponsored by Acme Chemist explains the relationship between project and clients, and Charlie (S100) is the Project Manager in PRJ- 515 explains that staff participates in a project (Khennoufa & Hoang 2025). Other notable facts are the periods when the staff workers are employed, project description, and project duration.
2.3 Fact Types and Population Check
The second step was to graphically sketch the types of facts and conduct a population check, i.e., to ascertain completeness and correctness. The types of facts determined are these: Staffhas StaffID, StaffName, StaffPhone, StartDate, EndDate; Project has ProjectID, ProjectName, ProjectSponsor, DateApproval, DateRevision,Description, Period, Scope; Staff plays Role in Project; and Project is sponsored by Client (Lu 2025). Population checks were conducted with the help of filling example data with the earned staff and project charters so that in every facet of the case all types of facts could be filled with realistic values. Such as the validity of the person-project-role relationship can be confirmed with Charlie (S100) is a Project Manager in PRJ_515 and Michelle (S106) is a Database Developer in PRJ_515.
2.4 Entity Types and Derivations
The third step involved the check of the entity types that would be merged and the arithmetic derivations. The key participants identified in this model are said to be Staff, Project, Client and Role. Other forms of facts were employed to identify relationships like employment and project membership (Ma et al. 2025). In the attribute ProjectPeriodYears the arithmetic derivation could be identified and could be computed using the values of project start and termination dates. There was no additional entity merging to do because each type symbolises a real-world object.
2.5 Resulting ORM Schema
(Source: Self-Created)
The resulting ORM conceptual schema is shown in the diagram below and gives the visual picture of the structure of the business domain. The figure encapsulates the fundamental actors including Staff, Project, Client and Role as well as the attributes and roles they support in relationships. All the connections emphasise the allocation of the staff to the projects, the role played by the clients in financing the projects and the allocation of roles among people in the project (Mancas 2025). Semantic correctness of this schema means the reflection of the actual facts of the real world, which were outlined in the case study, and syntactic correctness through the correct ORM notation, indecipherable to the stakeholders.
2.6 Uniqueness Constraints
The fourth step concerned the introduction of uniqueness constraints to make sure that entities and relations were distincting well in the schema. Individually, the Staff members have a uniquely defined StaffID identification, and Group members a ProjectID. On the same note, Role in a project is distinctly discernible by the multiplication of Staff and Project with Role because one staff can perform varied roles in various projects. These limitations make semantic accuracy more robust in that no records with duplicates or ambiguity will be found in the database. Moreover, the uniqueness was also utilised to other attributes like StaffPhone where every number is unique to identify contacts (Song & Lee 2024). These precautions ensured the logical structure of the ORM drawing, and the connexion of the model with the business regulations of ProjectsRus.
2.7 Mandatory Role Constraints
Introducing rules of compulsory role restriction was the fifth step in order to preserve structural completeness. In the schema, all the Staffs should be linked with StaffID such that there is no staff in the schema without an identification. Each of the Projects should similarly be required to possess a ProjectName and at least be associated with a Client sponsor. There are also mandated shares in the staff to projects that would manifest through Charlie (S100) assuming a role of Project Manager within PRJ155. These restrictions help to ensure there are no incomplete instances in the schema and that all the critical relations needed to have an accurate representation of ProjectsRus are maintained (Zhou and Pan 2025). Mandatory roles are even more to the benefit of the syntactic correctness of the model in that obligatory associations are given a clear definition.
2.8 Value, Set Comparison and Subtyping Constraints
The sixth step entailed adding value, set comparison and subtyping constraints to narrow the conceptual schema. Employment start and end dates were modelled where the start date should always be earlier than the end date, which added Tomes of chronological requirements. In a similar manner, project years were found to be an attribute derived, through project commencement and termination information. One also added subtyping constraints in which the (Project Manager, System Analyst, Database Developer, or any other Project Role) is a subtype of the general Role entity. This is a classification that assists in logical arrangement of the duties, and making the schema easier to interpret by stakeholders. Comparison of sets also meant that no employee was allowed to be working on conflicting tasks in the same project hence preserving semantic integrity (Wang et al. 2025). These refinements have the benefits of animalising the schema in such a way that it captures more than just structural accuracy but also behavioural rules of ProjectsRus business environment.
3. Task 2: Mapping ORM to Relational Schema
3.1 Overview of ORM to Relational Mapping
The Employee ORM model has been translated into the schemas of relational databases systematically based on the identification of key entities and their attributes and the relation existing among those entities. All the entities in the ORM diagram are modelled as a table, with the primary key being identifiers of records and foreign keys (Fks) that can create a connection between records in tables (Mbanugo et al. 2025). Other limitations, which include composite primary keys on junction tables, are needed to make sure that many-to-many relationships are treated properly. Not only does this mapping process retain the semantics of the original ORM model but it is also able to enforce referential integrity, provide normalisation and offer a clear, well-structured baseline upon which database implementation may occur.
3.2 Department, Employee, and Award Tables
The relational schema is starting with the table of Department with DeptCode as the primary key and DeptName as a compulsory attribute. The Employee table has EmpCode inside it as the primary key, besides attributes of EmpName, EmpPhone and date of hire namely. The foreign key indicating Department(DeptCode) is used in the connection between employees and departments based on the DeptCode. Award table is an interaction with AwdCode over AwardName as its primary key and AwardName as optional attribute. In order to represent the many-to-many connection between the employees and the awards earned, the junction table of the EmployeeAward is added (Miedema et al. 2025). This table is taken as the primary key of EmpCode and AwdCode as a foreign key where AwdCode is referenced in the award table and EmpCode in the employee table.
3.3 Address Table
Address table is used to record employee address data where AddressID is the first key to identify the address record. EmpCode is a foreign key of Employee(EmpCode), one employee may be associated with one or more addresses (Muppala 2025). This design has the benefit of flexibility so that many addresses can be stored on the same employee and still has the advantage of referential integrity between the tables.
The final relational schema can be represented in SQL form as follows: -- Department table CREATE TABLE Department ( deptcode INT PRIMARY KEY, -- Department code (PK) deptname VARCHAR(100) NOT NULL -- Department name (mandatory) ); -- Employee table CREATE TABLE Employee ( empcode INT PRIMARY KEY, -- Employee code (PK) empname VARCHAR(100) NOT NULL, -- Employee name (mandatory) empphone VARCHAR(20), -- Employee phone (optional) hiredate DATE NOT NULL, -- Hire date (mandatory) deptcode INT, -- FK to Department FOREIGN KEY (deptcode) REFERENCES Department(deptcode) ); -- Award table CREATE TABLE Award ( awdcode INT PRIMARY KEY, -- Award code (PK) awardname VARCHAR(100) -- Award name (optional) ); -- EmployeeAward table (to model the many-to-many between Employee and Award) CREATE TABLE EmployeeAward ( empcode INT, -- FK to Employee awdcode INT, -- FK to Award PRIMARY KEY (empcode, awdcode), -- Composite PK FOREIGN KEY (empcode) REFERENCES Employee(empcode), FOREIGN KEY (awdcode) REFERENCES Award(awdcode) ); -- Address table (assuming one-to-one or one-to-many with Employee) CREATE TABLE Address ( addressid INT PRIMARY KEY, -- Unique Address ID empcode INT, -- FK to Employee (assuming one employee can have multiple addresses) address VARCHAR(255) NOT NULL, -- Address details FOREIGN KEY (empcode) REFERENCES Employee(empcode) );
This relational schema has been found to map the Employee ORM model to a well received database design with the defined entities, attributes and relationships having the suitable constraints inhibited (Ochola et al. 2025). The Department table is the table that stores department information and DeptCode as the primary key is used to make uniquely identified departments. The employee table records employee information that includes the required fields- EmpName, HireDate and the third field is the foreign key DeptCode which defines relationships between employees and departments.
(Source: Self-Created)
The Award table is used to administer employee awards and the many to many employee v employee award relationship is solved by the use of the EmployeeAward junction table where the relationship uses a composite primary key of EmpCode and AwdCode. Equally, the Address table contains the address information about the employees, among others, where the approach further takes the form of AddressID as the primary key, and EmpCode as the foreign key to identify one particular employee to his or her address (Robens-Radermacher et al. 2025). This design guarantees the data integrity by the adequately defined primary and foreign keys, and mandatory constraints (where needed or applicable).
Students who need additional guidance with database concepts, SQL implementation, and relational schema mapping can explore options to do my database assignment with appropriate academic support.
Conclusion
To summarise, the conversion of the ProjectsRus ORM conceptual schema into a relational database schema manages to maintain the integrity and the semantics of the initial example. All the tables, both in terms of department, employees, awards, addresses and their relationships with each other, were well formatted by considering the features of highlighting the primary and foreign keys to implement referential integrity and to facilitate normalisation. The many-to-many relationships which are between rewards and employees were well addressed through junction tables. It represents a strong or better said a scalable basis to build the system in the future as all the important business rules and relationships are realised; and the data as it is is not lost, manipulated or inconsistent and is easy to query.
Reference List
Journals
- Ding, L., Xiao, G., Pano, A., Fumagalli, M., Chen, D., Feng, Y., ... & Meng, L. (2025). Integrating 3D city data through knowledge graphs. Geo-spatial Information Science, 28(2), 780-799. [Retrieved From: https://www.tandfonline.com/doi/abs/10.1080/10095020.2024.2337360] [Retrieved On: 18.08.2025]
- Khennoufa, A., & Hoang, H. H. (2025). Towards a knowledge-based system for database design. WIT Transactions on Information and Communication Technologies, 6. [Retrieved From: https://www.witpress.com/elibrary/wit-transactions-on-information-and-communication-technologies/6/10950] [Retrieved On: 18.08.2025]
- Lu, J. (2025). A Categorical Unification for Multi-Model Data: Part I Categorical Model and Normal Forms. arXiv preprint arXiv:2502.19131. [Retrieved From: https://arxiv.org/abs/2502.19131] [Retrieved On: 18.08.2025]
- Ma, C., Chakrabarti, S., Khan, A., & Molnár, B. (2025). Knowledge graph-based retrieval-augmented generation for schema matching. arXiv preprint arXiv:2501.08686. [Retrieved From: https://arxiv.org/abs/2501.08686] [Retrieved On: 18.08.2025]
- Mancas, C. (2025). MatBase Metadata Catalog Management. arXiv preprint arXiv:2504.07243. [Retrieved From: https://arxiv.org/abs/2504.07243] [Retrieved On: 18.08.2025]
- Mbanugo, O. J., Taylor, A., & Sneha, S. (2025). Buttressing the power of entity relationships model in database structure and information visualization: Insights from the Technology Association of Georgia’s Digital Health Ecosystem. World J Adv Res Rev, 25(02), 1294-1313. [Retrieved From: https://www.researchgate.net/profile/Olu-Mbanugo/publication/389395883_Buttressing_the_power_of_entity_relationships_model_in_database_structure_and_information_visualization_Insights_from_the_Technology_Association_of_Georgia's_Digital_Health_Ecosystem/links/67c0c2b3207c0c20fa9aac34/Buttressing-the-power-of-entity-relationships-model-in-database-structure-and-information-visualization-Insights-from-the-Technology-Association-of-Georgias-Digital-Health-Ecosystem.pdf] [Retrieved On: 18.08.2025]
- Miedema, D., Taipalus, T., Ajanovski, V. V., Alawini, A., Goodfellow, M., Liut, M., ... & Young, T. (2025). Data systems education: Curriculum recommendations, course syllabi, and industry needs. In 2024 Working Group Reports on Innovation and Technology in Computer Science Education (pp. 95-123). [Retrieved From: https://dl.acm.org/doi/abs/10.1145/3689187.3709609] [Retrieved On: 18.08.2025]
- Muppala, M. (2025). SQL Database Mastery: Relational Architectures, Optimization Techniques, and Cloud-Based Applications. Deep Science Publishing. [Retrieved From: https://books.google.com/books?hl=en&lr=&id=hXB2EQAAQBAJ&oi=fnd&pg=PA1&dq=CONCEPTUAL+DATA+MODELING+AND+RELATIONAL+SCHEMA+MAPPING+FOR+DATABASE+DESIGN&ots=FJdyPniQ9E&sig=PVYXcI5fXlgZeTcWy7SrQ21Igfo] [Retrieved On: 18.08.2025]
- Ochola, M., Kiwuwa-Muyingo, S., Bhattacharjee, T., Amadi, D., Ng’etich, M., Kadengye, D., ... & Kiragga, A. (2025). Harmonizing population health data into OMOP common data model: a demonstration using COVID-19 sero-surveillance data from Nairobi Urban Health and Demographic Surveillance System. Frontiers in digital health, 7, 1423621. [Retrieved From: https://www.frontiersin.org/journals/digital-health/articles/10.3389/fdgth.2025.1423621/full] [Retrieved On: 18.08.2025]
- Robens-Radermacher, A., Kujath, C., Bos, F., Mechtcherine, V., & Unger, J. F. (2025). Mechanical properties of 3D printed concrete: a RILEM TC 304-ADC interlaboratory study-Design and implementation of a database system for querying, sharing, and analyzing experimental data. Materials and Structures, 58(5), 184. [Retrieved From: https://link.springer.com/article/10.1617/s11527-025-02650-9] [Retrieved On: 18.08.2025]
- Skavantzos, P., & Link, S. (2025). Entity/relationship graphs: Principled design, modeling, and data integrity management of graph databases. Proceedings of the ACM on Management of Data, 3(1), 1-26. [Retrieved From: https://dl.acm.org/doi/abs/10.1145/3709690] [Retrieved On: 18.08.2025]
- Wickett, K. M. (2025). Critical data modeling and the basic representation model. Journal of the Association for Information Science and Technology, 76(2), 364-374. [Retrieved From: https://asistdl.onlinelibrary.wiley.com/doi/abs/10.1002/asi.24745] [Retrieved On: 18.08.2025]
