Defining Associations Between CDS Views

Define a CDS view entity for department data (suggested name: Z##_R_Department, where ## is your group number). Let the new CDS view entity read all fields from the database table that you created in the previous exercise (suggested name was: Z##DEPMENT).

1. In your own package, create a new data definition (suggested name: Z##_R_DEPARTMENT, where ## is your group number). Specify your database table as Referenced Object and choose the Define View Entity template to generate the definition statement, some standard annotations and the element list.

   1. In the Project Explorer view, right-click your database table definition Z##DEPMENT to open the context menu.

   2. From the context menu, choose New Data Definition.

   3. Confirm that the Package field contains the name of your package and that the Referenced Object field contains the name of your database table definition.

   4. In the Name field, enter the name for the CDS view entity (Z##_R_Department, where ## is your group number).

   5. Enter Department (Entity) in the Description field and choose Next.

   6. Confirm the transport request and choose Next.

      Caution

      Make sure you don't choose Finish yet. If you do, you are not able to choose the template that you want to use.

   7. From the list of Templates, choose Define View Entity, then choose Finish.

2. Apply source code formatting.

   1. From the eclipse menu, choose Source Code → Format. Alternatively, choose Shift + F1.

3. Activate the data definition.

   1. Press Ctrl + F3 to activate the development object.

In your basic CDS view entity for employee data (suggested name was: Z##_R_Employee), define and expose an association using your new CDS view entity for department data as target (suggested association name: _Department). Choose a cardinality that matches the cardinality used in the foreign key relationship for the DEPARTMENT_ID table field.

1. Open the basic data definition for employee data (suggested name was: Z##_R_EMPLOYEE).

   1. Press Ctrl + Shift + A.

   2. Enter Z##_R_.

   3. From the list of matching items, choose Z##_R_EMPLOYEE (Data Definition) and choose Next.

2. Define an association to your CDS view entity for department data with association name _Department.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      12345
      
      define view entity Z##_R_Employee
        as select from z##employ
        association to Z##_R_Department as _Department
      

3. Define the ON-condition, using the department ID field in both view entities.

   Hint

   Make use of the $projection. prefix to address the element of the source view entity and the_Department. prefix to address the element of the target view entity.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      123456
      
      define view entity Z##_R_Employee
        as select from z##employ
        association to Z##_R_Department as _Department
          on $projection.DepartmentId = _Department.Id
      

4. Add a cardinality that matches the cardinality of the foreign key relationship for the DEPARTMENT_ID table field.

   Note

   The cardinality of the foreign key relationship is 0..*,1. The first part specifies the number employees per department. The second part expresses the number of departments per employee.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      123456
      
      define view entity Z##_R_Employee
        as select from z##employ
        association[1..1] to Z##_R_Department as _Department
          on $projection.DepartmentId = _Department.Id
      

5. Expose the association.

   1. At the end of the element list, add a comma and the association name.

   2. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      12345
      
         last_changed_at       as LastChangedAt,
          _Department
      }
      

6. Activate the CDS data definition.

   1. Press Ctrl + F3 to activate the development object.

In your basic CDS view entity for department data, define and expose three associations: an association for all employees (suggested association name: _Employee), an association for the department head (suggested name: _Head), and an association for the department assistant (suggested name: _Assistant). Choose the cardinalities based on the corresponding foreign key relationships in the underlying database tables.

1. Return to the data definition for department data. Define association _Employee to address all employees that are assigned to this department.

   Note

   Take into account that a department can have any number of employees, including zero.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      1234567
      
      define view entity Z##_R_Department
        as select from z##depment
        association [0..*] to Z##_R_Employee as _Employee
          on $projection.Id = _Employee.DepartmentId
      
      

2. Define an association to address the details of the department head. Set the cardinality based on the cardinality of the foreign key relationship between the underlying database tables.

   Note

   In our model, it is NOT necessary that a department has a department head.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      123456789
      
      define view entity Z##_R_Department
        as select from z##depment
        association [0..*] to Z##_R_Employee as _Employee
          on $projection.Id = _Employee.DepartmentId
        association [0..1] to Z##_R_Employee as _Head
          on $projection.HeadId = _Head.EmployeeId
      
      

3. Define an association to address the details of the department assistant. Set the cardinality based on the cardinality of the foreign key relationship between the underlying database tables.

   Note

   In our model, a department cannot exist without a department assistant.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      1234567891011
      
      define view entity Z##_R_Department
        as select from z##depment
        association [0..*] to Z##_R_Employee as _Employee
          on $projection.Id = _Employee.DepartmentId
        association [0..1] to Z##_R_Employee as _Head
          on $projection.HeadId = _Head.EmployeeId
        association [1..1] to Z##_R_Employee as _Assistant
          on $projection.AssistantId = _Assistant.EmployeeId
      
      

4. Expose the association.

   1. At the end of the element list, add a comma and the three association names (comma-separated).

   2. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      1234567
      
         last_changed_at       as LastChangedAt,
          _Employee,
          _Head,
          _Assistant
      }
      

5. Activate the CDS data definition.

   1. Press Ctrl + F3 to activate the development object.

Analyze the technical implementation of your CDS view for department data and use the associations for navigation in the Data Preview tool.

1. Display the SQL statement with which the technical representation of the view entity is created in the database.

   1. Right-click anywhere in the source code of the data definition and choose Show SQL CREATE Statement.

2. Open the Data Preview for your CDS view for department data.

   1. Right-click anywhere in the source code of the data definition and choose Open With → Data Preview. Alternatively, place the cursor anywhere in the source code of the data definition and press F8.

3. Display all employees that work for the Sales and Distribution department.

   1. Right-click anywhere in the row with value SALE in the Id column and choose Follow Association. Alternatively, place the cursor anywhere in the row and press Ctrl + G.

   2. Choose _Employee → Z##_R_Employee [0..*].

4. Return to the department list and display the assistant of the Administration department.

   1. On the navigation path that is displayed above the Data Preview tab (sometimes referred to as "Breadcrumb naviation"), choose Z##_R_DEPARTMENT to return to the list of departments.

   2. Right-click anywhere in the row with value ADMIN in the Id column and choose Follow Association. Alternatively, place the cursor anywhere in the row and press Ctrl + G.

   3. Choose _Assistant → Z##_R_Employee [1..1].

5. Without returning to the list of departments, navigate to the department, this person works for and from there to the department head.

   1. Right-click anywhere in the single row that is displayed and choose Follow Association.

   2. Choose _Department → Z##_R_Department [1..1].

   3. Right-click anywhere in the single row that is displayed and choose Follow Association.

   4. Choose _Head → Z##_R_Employee [0..1].