Task 1: Specify An ORM Conceptual Schema For The UoD ✓ Solved

Task 1: Specify an ORM conceptual schema for the UoD using t

Task 1: Specify an ORM conceptual schema for the UoD using the following tables of employee information.

EMP_PROJ

Ssn Pnumber Hours Ename Pname Plocation


.5 Smith, John B. ProductX Bellaire


.5 Smith, John B. ProductY Sugarland


.0 Narayan, Ramesh K. ProductZ Houston


.0 English, Joyce A. ProductX Bellaire


.0 English, Joyce A. ProductY Sugarland


.0 Wong, Franklin T. ProductY Sugarland


.0 Wong, Franklin T. ProductZ Houston


.0 Wong, Franklin T. Computerization Stafford


.0 Wong, Franklin T. Reorganization Houston


.0 Zekata, Alicia J. Newbenefits Stafford


.0 Zekata, Alicia J. Computerization Stafford


.0 Jabbar, Ahmad V. Computerization Stafford


.0 Jabbar, Ahmad V. Newbenefits Stafford


.0 Wallace, Jennifer S. Newbenefits Stafford


.0 Wallace, Jennifer S. Reorganization Houston


Null Borg, James E. Reorganization Houston


EMP_DEPT


Ename Ssn Bdate Address Dnumber Dname Dmgr_ssn


Smith, John B. Fondren, Houston, TX 5 Research Wong, Franklin T.


Zekata, Alicia J. Castle, Spring, TX 4 Administration


Wallace, Jennifer S. Berry, Bellaire, TX 4 Administration


Narayan, Ramesh K. FireOak, Humble, TX 5 Research


English, Joyce A. Rice, Houston, TX 5 Research


Jabbar, Ahmad V. Dallas, Houston, TX 4 Administration


Borg, James E. Ston, Houston, TX 1 Headquarters


Task 2: Map the following ORM schema to a relational database schema.

Paper For Above Instructions

Introduction and approach

This document provides a conceptual ORM (Object-Role Modeling) solution for the provided employee domain (Task 1) and a clear mapping from that conceptual model to a relational database schema (Task 2). Where the assignment requested mapping “the following ORM schema” but did not supply a separate diagram, I map the ORM conceptualization produced from Task 1 into a normalized relational schema. The design follows canonical ORM-to-relational mapping principles (Halpin & Morgan, 2008; Elmasri & Navathe, 2015).

Task 1 — Deriving an ORM conceptual schema

From the EMP_PROJ and EMP_DEPT tables we derive the following factual constructs (fact types) and object types:

  • Employee(Ssn, Ename, Bdate, Address) — Employee is an object type identified by Ssn.
  • Department(Dnumber, Dname) — Department identified by Dnumber.
  • Project(Pnumber, Pname, Plocation) — Project identified by Pnumber.
  • Employee-WorksOn-Project(Ssn, Pnumber, Hours) — a ternary/associative fact type (employee assigned to project with hours).
  • Employee-AssignedTo-Department(Ssn, Dnumber) — employee works in a department (role fact type).
  • Department-ManagedBy(Employee.Ssn as Dmgr_ssn) — Department has a manager role pointing to Employee.

Key constraints and role constraints inferred from the data and domain semantics:

  • Ssn uniquely identifies an Employee (uniqueness constraint).
  • Dnumber uniquely identifies a Department.
  • Pnumber uniquely identifies a Project; Pname and Plocation are properties of Project.
  • WorksOn has a uniqueness constraint on the pair (Ssn, Pnumber) — an employee may have at most one Hours value per project.
  • Employee AssignedTo Department: typically each employee belongs to one department in the data, so a mandatory single-valued role (Ssn → Dnumber) is assumed unless business rules say otherwise.
  • Department ManagedBy Employee is a 0..1 role: not every department row shows a manager value present in the sample; where present Dmgr_ssn references an Employee Ssn.

Notes on anomalies in the sample data

The EMP_PROJ data includes a row with Ssn = Null for “Borg, James E. Reorganization Houston”. This could indicate missing/masked SSN, a data-entry error, or an external contractor record. In the conceptual model we keep Ssn as the identifying property of Employee (mandatory). Instances with missing Ssn should be treated as incomplete data and resolved (e.g., by assigning a surrogate EmployeeID or requiring Ssn before enforcing referential integrity) (Harrington, 2016).

Visual description of the ORM diagram

The ORM diagram would present object types Employee, Department, Project. Value types: Ename, Bdate, Address, Pname, Plocation, Hours. Fact types connect these objects: Employee has Ename, Bdate, Address; Department has Dname and Dmgr (role linked to Employee); Project has Pname and Plocation; WorksOn fact type links Employee and Project with Hours as a value type. Uniqueness constraints are shown on Ssn, Dnumber, Pnumber and on (Ssn, Pnumber) for WorksOn. Mandatory role constraints mark Employee→AssignedTo→Department as mandatory if that business rule is enforced.

Task 2 — Mapping ORM to a relational schema

Mapping rules applied: value types attached to an object map to columns on that object’s relation; object types with identifying value(s) map to relations with those values as primary keys; n-ary fact types map to relations with foreign keys referencing component object relations and with any value roles as non-key attributes; uniqueness constraints map to primary or candidate keys; mandatory roles map to NOT NULL constraints where appropriate (Halpin & Morgan, 2008; Elmasri & Navathe, 2015).

Proposed relational schema (DDL-like notation with PK, FK and constraints):

Employee (

Ssn VARCHAR PRIMARY KEY,


Ename VARCHAR NOT NULL,


Bdate DATE,


Address VARCHAR,


Dnumber INT NOT NULL, -- FK to Department (if Dept membership is mandatory)


CONSTRAINT fk_emp_dept FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber)


);


Department (


Dnumber INT PRIMARY KEY,


Dname VARCHAR NOT NULL,


Dmgr_ssn VARCHAR, -- FK to Employee.Ssn, optional


CONSTRAINT fk_dept_mgr FOREIGN KEY (Dmgr_ssn) REFERENCES Employee(Ssn)


);


Project (


Pnumber INT PRIMARY KEY,


Pname VARCHAR NOT NULL,


Plocation VARCHAR


);


WorksOn (


Ssn VARCHAR NOT NULL,


Pnumber INT NOT NULL,


Hours DECIMAL(4,2),


PRIMARY KEY (Ssn, Pnumber),


FOREIGN KEY (Ssn) REFERENCES Employee(Ssn),


FOREIGN KEY (Pnumber) REFERENCES Project(Pnumber)


);

Rationale and constraints

  • The WorksOn relation captures the many-to-many relationship between employees and projects; Hours is a non-key attribute of that relationship (Kent, 1983).
  • Department.Dmgr_ssn references Employee.Ssn; if a strict acyclic constraint is needed (department manager must belong to the same department), a business rule and additional check or trigger is required to enforce that Employee.Dnumber = Department.Dnumber for the manager (Elmasri & Navathe, 2015).
  • Null Ssn rows in EMP_PROJ require action: either populate Employee with a proper Ssn, create a surrogate EmployeeID to identify such records, or disallow null and mark data as invalid. Referential integrity recommends not permitting orphan WorksOn rows (Codd, 1970).
  • Uniqueness constraints from ORM are implemented as primary keys or unique constraints in the relational schema; mandatory role constraints become NOT NULL columns where appropriate.

Normalization and design validation

The schema above conforms to third normal form because each non-key attribute (Ename, Bdate, Address, Pname, Plocation, Hours) depends on the whole key of its relation and not on other non-key attributes (Date, 2003). ORM’s conceptual constraints help ensure that the mapping preserves intended uniqueness, mandatory, and role participation constraints (Halpin, 2001).

Implementation considerations and recommendations

  • Use explicit foreign keys and transactions to preserve data consistency when inserting or updating employees, projects, and works-on assignments.
  • Decide a policy for missing Ssn values: prefer resolving missing identifiers at the source; otherwise implement a surrogate key (EmployeeID) and map Ssn as an optional attribute.
  • Document business rules (e.g., single-department membership, manager must be an employee of that department) and enforce them via constraints, triggers, or application logic depending on DBMS capability (Harrington, 2016).
  • If the domain requires historical tracking of hours across time, consider adding an assignment-date or timesheet entity to model temporal changes rather than overwriting Hours (Batini et al., 1992).

Conclusion

The ORM conceptual model extracted from the sample tables yields a clear set of object and fact types that map neatly to a normalized relational schema. The mapping preserves uniqueness, mandatory and referential constraints by using PKs, FKs, and NOT NULL constraints. Data anomalies such as missing SSNs should be resolved through domain-level policy or the introduction of surrogate keys before enforcing strict referential integrity (Halpin & Morgan, 2008; Elmasri & Navathe, 2015).

References

  • Halpin, T. (2008). Information Modeling and Relational Databases. Morgan Kaufmann. (Halpin & Morgan, 2008)
  • Elmasri, R., & Navathe, S. (2015). Fundamentals of Database Systems (7th ed.). Pearson. (Elmasri & Navathe, 2015)
  • Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Addison-Wesley. (Date, 2003)
  • Harrington, J. L. (2016). Relational Database Design and Implementation (4th ed.). Morgan Kaufmann. (Harrington, 2016)
  • Kent, W. (1983). A Simple Guide to Five Normal Forms in Relational Database Theory. Communications of the ACM. (Kent, 1983)
  • Batini, C., Ceri, S., & Navathe, S. (1992). Conceptual Database Design: An Entity-relationship Approach. Benjamin/Cummings. (Batini et al., 1992)
  • Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM. (Codd, 1970)
  • Halpin, T. (2001). ORM: An Introduction. Information Systems Research. (Halpin, 2001)
  • Melnik, S., & Garcia-Molina, H. (2001). Model Management: Abstracting from the Parochialities of Data Models. VLDB. (Melnik & Garcia-Molina, 2001)
  • Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson. (Hoffer et al., 2016)

Related Assignments