SIM Example: Organization Schema

1. Person Name (First-Name, Mid-Initial, Last-Name) Current-Residence (Street, City, State, ZipCode) Education (Degree-Obtained, Year-Obtained, GPA) MV Next-of-Kin (Relationship, Phone, Name-Of-Kin (First-Name, Mid_Initial, Last-Name)) Marital-Status US-Citizen Soc-Sec-No Birth-Date Gender Age Child Spouse Parent Previous-Employee Employee Employee-Manager Termination-Reason Leave-Status Last-Work-Date Hire-Date Employee-ID Employee-Hire-Date Employee-Salary Employee-Status Employees-Managed Project-Employee Interim-Manager Manager Interim-History (Start-Date, End-Date) MV Manager-Title Bonus Title Overall-Rating Dept-Managers Current-Project Dept-In Dept-Staff Project-Manager Projects-Managing Project-Team Department Project Managers-Department Dept-Assigned (Next-Project-No) Project-No Project-Title Dept-No Dept-Title Dept-Location Assignment Sub-Project-Of Sub-Projects Assignment-Record Project-Of Start-Date Assignment-No End-Date Rating Est-Person-Hours Assignment-History Staff-Assigned SIM Example: Organization Schema

2. Unisys SIMOrigins and Evolution Doug TolbertUnisys CorporationMission Viejo, CA

3. SIM Motivations: Objectives (1983) Ease of Use • Integrated, high level interface • Query & schema flexibility • Data Independence Productivity • Set-oriented, non-procedural interface • Naturally handle complexity • Easy to learn • Minimal programming effort • Retrieval without programming Data Integrity • System enforced integrity • Shared data definitions • Referential integrity Coexistence • No impact on DMS II customer base • New capabilities for existing data Performance • Must support production level transaction processing systems • Fast • Fast • Fast

4. SIM Motivations: Options (1983) Enhance DMS II Relational System Something New • Improve ease of use • Gain productivity & data integrity without sacrificing installed base • Performance thought to be near architectural limits (but wasn’t true!) • Not likely to expand customer base • Ease of use would improve • Productivity gains marginal • Data integrity very limited • Extensive data engine rework • No migration path for customer base • Performance? • Late to market • Designed to meet Ease of use, productivity, and data integrity goals • New features for existing databases without migration • Performance based on proven DMS II algorithms • Semantic Data Model provided best framework

5. SIM: Something New! Semantic Models SDM Hammer & McLeod Artificial Intelligence Semantic Networks Quillian, Brachman SIM Graphical Data Models Entity Relationship Chen Programmatic Model DAPLEX Shipman Set Data Models RM/T Codd Query Languages GORDAS El-Masri

6. SIM: A Semantic Data Model • What makes a data model “semantic”? • “During the last few years numerous investigations have been aimed at capturing (in a reasonably formal way) more of the meaning of the data, while preserving independence of implementation. This activity is sometimes called semantic data modelling. Actually, the task of capturing the meaning of data is a never-ending one. So the label ‘semantic’ must not be interpreted in a absolute way.” • -- E. F. Codd, 1979 • SDMs capture the meaning of data, not just its physical representation • So, the more meaning a data model captures, the more “semantic” it is • How does a semantic data model capture meaning? • “... it is appropriate that the structure of a database mirror the structure of the system that it models. A database whose organization is based on naturally occurring structures will be easier for a database designer to construct and modify than one that forces him to translate the primitives of his problem domain into artificial specification construction.” • -- Hammer & McLeod, 1981 • Schema objects should look like corresponding real world objects • Meaning captured by object types, relationships, attribute types, cardinality constraints, active rules

7. SIM Concepts: Entities & Attributes Entity • An object or idea of interest in the application environment • Does not imply a particular physical implementation But functional analogs are records and tuples in other data models • e.g., The employee named John Doe The Accounting department Attribute • A characteristic of an entity • John Doe has an address, an employee ID, a salary, ...

8. SIM Concepts: Classes & Subclasses Class • A collection of entities of the same type • Employee, the set of all employees working for a company Subclass • A subset of entities in a class • Project-Employees are employees that work on projects Employee ISA Project-Employee

9. SIM Concepts: Attributes Attribute Inheritance • Subclasses inherit attributes from all their superclasses • Subclasses may declare additional attributes Attribute Types • Data-Valued Attributes (DVA) Have a printable value • Entity-Valued Attributes (EVA) Entities in another class Model relationships Employee Name EmployeeID Project-Employee Title Department Dept-In

10. SIM Concepts: Extended Attributes Perspective Class • A point of view class chosen for a query • Same data may have different meanings from different perspective classes Extended Attributes • Attributes of related entities may be considered extended attributes of the perspective class Employee Name EmployeeID Project-Employee Title Department Dept-No Dept-In Dept-No of Dept-In of Project-Employee = 123 Dept-No of Department = 123

11. SIM Concepts: Constraints & Relations Attribute Constraints • Isomorphic for EVA/DVA • Single-Valued • Multi-Valued • Required • Unique Relationships (EVAs) • Always bi-directional • Automatic referential integrity • Transitive closure function Employee Name EmployeeID Project-Employee Title Department Dept-No Dept-In Dept-Staff Title of INVERSE(Dept-In) of Department Title of Dept-Staff of Department

12. SIM Concepts: Data Types Class Attributes • One value per class, not associated with any entity, DVA only Data Types • Similar to Pascal, with relaxed string type checking System Defined User Defined • Integer • Real • Boolean • Date • Time • Character • Number • String (fixed) • String (variable) • Kanji • Symbolic • Ordered Symbolic • Based on system defined types • Set membership for strings • WeekDay : String(“Mon”, ..., “Fri”) • Enforced subrange • Employee-Age : Integer(18..70)

13. SIM Concepts: Verify Verify • General constraints not related to schema structure • Full power of SIM expression syntax in WHERE clause • For example Spouses of employees hired after December 31, 1995, may not work for the company VERIFY NoCouples ON Employee ASSERT Not (Spouse ISA Employee) WHERE Employee-Hire-Date > 12/31/95 ELSE “Spouse may not work for company”

14. SIM Concepts: Security Security • Accesses limit visibility of and operations on attributes • Permissions associate Accesses with users or programs • For example The Accounting Department may examine but not change the salary of journeyman and higher employees ACCESS LookButDontTouch ON Employee (Employee-Salary) RETRIEVE WHERE Employee-Status >= Journeyman PERMISSION USERCODE = Accounting, ACCESS = LookButDontTouch

15. SIM Concepts: Indexes Indexes • Multiple DVA keys, ascending or descending • For performance improvement • Visible to database administrators & query optimizer • Not visible to programmers or query users INDEX (Employee-ID) ON Employee ASCENDING

16. SIM Concepts: Expressions Operators Arithmetic +, -, *, /, DIV, MOD, ** Boolean NOT, AND, OR Relational <, >, =, <=, >=, <> String &, pattern matching Existence EXISTS Functions Arithmetic ABS, ROUND, TRUNC, SQRT String LENGTH, EXT, POS, RPT Symbolic PRED, SUCC Date YEAR, MONTH, DAY, ELAPSED_DAYS, ADD_DAYS, DAY_OF_WEEK, MONTH_NAME, CURRENT_DATE Time HOUR, MINUTE, SECOND, ELAPSED_TIME, ADD_TIME, CURRENT_TIME Relationships INVERSE, TRANSITIVE Multi-Valued Operators INCLUDE, EXCLUDE Aggregates AVG, SUM, COUNT, MIN, MAX Quantifiers SOME, ALL, NO

17. SIM Concepts: Advanced Query Topics Subrole Attribute (Readonly enumeration of subclasses in a class) Role Testing Role Qualification Retrieve the names of employees that are managers. RETRIEVE Name of Employee WHERE Profession = Manager Retrieve the names of employees whose spouses are managers. RETRIEVE Name of Employee WHERE Spouse ISA Manager Retrieve the names of US citizens and the employee IDs of their spouses. RETRIEVE Name of Person, Employee-ID of Spouse AS Employee WHERE US-Citizen

18. SIM Concepts: Advanced Query Topics Multiple Perspective Queries (aka Joins) Reference Variables Local Selection Retrieve the names of project employees and managers that are the same age. RETRIEVE Name of Project-Employee, Name of Manager WHERE Age of Project-Employee = Age of Manager Retrieve the names of all managers who manage employees making more than $40,000 and employees making less than $20,000. RETRIEVE Name of Manager WHERE Employee-Salary of Employees-Managing > 40000 AND Employee-Salary of Employees-Managing CALLED Underpaid < 20000 Retrieve the names of managers of all departments and the salaries of only the division managers. RETRIEVE Name of Dept-Managers of Department, Employee-Salary of Dept-Managers WITH (Manager-Title of Dept-Managers = Division-Manager)

19. SIM Concepts: Update Insert Entity: Create a new employee named John Doe and assign him to the manager named Smith. INSERT Employee (Name := (First-Name := “John”, Last-Name := “Doe”), Gender := Male, Soc-Sec-No := 123-45-6789, US-Citizen := True, Employee-ID := 726478, Spouse := Person WITH (First-Name of Name = “Mary” AND Last-Name of Name = “Doe”), Child := INCLUDE Person WITH (First-Name of Name = “Junior” AND Last-Name of Name = “Doe”), Employee-Manager := Manager WITH (Last-Name of Name = “Smith”) Insert Role: Promote John Doe to department manager with a bonus of $5000. INSERT Manager FROM Employee WHERE Last-Name of Name = “Doe” (Manager-Title := Department-Manager, Bonus := 5000) Modify: Reassign all Journeyman Project-Employees in the Construction Department to the Maintenance Department. MODIFY Project-Employee (Dept-In := Department WITH (Dept-Title = “Maintenance”)) WHERE Title = Journeyman AND Dept-Title of Dept-In = “Construction” Delete: Remove all projects assigned to managers that manage departments located in Los Angeles. DELETE Project WHERE Dept-Location of Managers-Department of Project-Manager = “Los Angeles”

20. SIM Example Organization: a projects and employee database Database descriptions • DMS II A record oriented DBMS • DB2 A relational DBMS • SIM A semantic DBMS Query Comparisons • Three retrieval queries of increasing complexity • Application program sizes

21. SIM Example Organization Basic Facts • Employees are People • Project-Employees are Employees who are assigned to Projects • Managers are Employees who manage Projects • Sometimes a Project-Employee can act as a Manager on an interim basis • Projects may have Subprojects • Historical records of Previous Employees must be retained

22. Parent-SSN Child-SSN Family Soc-Sec-No Spouse-SSN Managers-SSN Assignment-No Dept-No Employed Employee-ID Person Assignment-No Project-No Assignment Family-Set Assignment-Set Soc-Sec-No Education Project-No Dept-No SuperProject-No Project Project-Person Employee-ID Project-No Education-Set Person-Set ProjPerson-Set Interim-Manager Employee-ID Project-Set Duration StartDate EndDate Dept-No Department Department-Set InterimHist-Set Interim-Set Legend Attributes Data Set Name Primary Index SIM Example: DMS II Schema

23. Family Person Assignment Parent-SSN Child-SSN Soc-Sec-No Spouse-No Managers-SSN Assignment-No Dept-No Employed Employee-ID Assignment-No Project-No Project-Person Project Education Employee-ID Project-No Project-No Dept-No SuperProject-No Soc-Sec-No Interim-History Department Employee-ID StartDate EndDate Dept-No Legend Table Name Column Name SIM Example: DB2 Schema

24. Person Name (First-Name, Mid-Initial, Last-Name) Current-Residence (Street, City, State, ZipCode) Education (Degree-Obtained, Year-Obtained, GPA) MV Next-of-Kin (Relationship, Phone, Name-Of-Kin (First-Name, Mid_Initial, Last-Name)) Marital-Status US-Citizen Soc-Sec-No Birth-Date Gender Age Child Spouse Parent Previous-Employee Employee Employee-Manager Termination-Reason Leave-Status Last-Work-Date Hire-Date Employee-ID Employee-Hire-Date Employee-Salary Employee-Status Employees-Managed Project-Employee Interim-Manager Manager Interim-History (Start-Date, End-Date) MV Manager-Title Bonus Title Overall-Rating Dept-Managers Current-Project Dept-In Dept-Staff Project-Manager Projects-Managing Project-Team Department Project Managers-Department Dept-Assigned (Next-Project-No) Project-No Project-Title Dept-No Dept-Title Dept-Location Assignment Sub-Project-Of Sub-Projects Assignment-Record Project-Of Start-Date Assignment-No End-Date Rating Est-Person-Hours Assignment-History Staff-Assigned SIM Example: Organization Schema

25. Simple Query Print the names of all non-managers and the title of the department in which they work. Complex Query Print the names of employees and the titles of all their projects if they work on any project assigned to the Accounting Department. Hard Query For the Annual Report Preparation project, print the titles of its subprojects and the names of employees currently assigned. SIM Example: Comparison Queries

26. DMS II RELATE Person TO Department BY MATCHING Dept-No WITH Dept-No AS Person-Dept; TAB First-Name, Mid-Initial, Last-Name, Dept-Title WHERE Employed NEQ “Manager” FROM Person TO Department; DB2 SELECT First-Name, Mid-Initial, Last-Name, Title FROM Person, Department WHERE Person.Dept-No = Department.Dept-No AND Person.Employed NEQ “Manager”; SIM RETRIEVE Name of Project-Employee, Dept-Title of Dept-In SIM Example: Simple Query Print the names of all non-managers and the title of the department in which they work.

27. SIM Example: Complex Query Print the names of employees and the titles of all their projects if they work on any project assigned to the Accounting Department. DMS II EXTRACT Soc-Sec-No, COUNT AS SSN-Cnt WHERE Dept-TItle “Accounting” FROM Person TO Project-Person TO Project TO Department : EXTRACTFILE = ExtFile; OPEN FILE ExtFile; RELATE ExtFile TO Project-Person BY MATCHING Soc-Sec-No WITH Soc-Sec-No AS Extrel; TAB First-Name, Mid-Initial, Last-Name, Project-Title FROM ExtFile TO Project-Person TO Project WHERE SSN-Cnt > 0; RELATE Department TO Project BY MATCHING Dept-No WITH Dept-No AS Dept-Proj; RELATE Project TO Project-Person BY MATCHING Project-No WITH Project-No AS Proj-Person; RELATE Project-Person TO Person BY MATCHING Soc-Sec-No WITH Soc-Sec-No AS Proj-Emp; DB2 SELECT FIrst-Name, MId-Initial, Last-Name, Project-Title FROM Person, Project-Person, Project WHERE Person.Soc-Sec-No = Project-Person.Soc-Sec-No AND Project.Project-No = Project-Person.Project-No AND EXISTS (SELECT * FROM Project-Person, Project, Department WHERE Project-Person.Soc-Sec-No = Person.Soc-Sec-No AND Project-Person.Project-No = Project.Project-No AND Department.Dept-No = Project.Dept-No AND Department.Dept-Title = “Accounting”) SIM RETRIEVE Name of Project-Employee, Project-TItle of Current-Project WHERE Dept-TItle of SOME(Dept-Assigned of Current-Project) = “Accounting”

28. SIM Example: Hard Query For the Annual Report Preparation project, print the titles of its subprojects and the names of employees currently assigned. DMS II OPEN DMSII Oranization; OPEN DMSII OrgCopy (DMI =DMINTERPRETER/ORGANIZATION); RELATE Project of Organization TO Project of OrgCopy BY MATCHING Project-No WITH SuperProject-No AS SubProj; RELATE Project of Organization TO Person of OrgCopy BY MATCHING Project-No WITH Project-No AS Project-Emp; RELATE Project-Person of OrgCopy TO Person of OrgCopy BY MATCHING Employee-ID WITH Employee-ID AS Proj-Person; TAB Project-Title of Project of OrgCopy, First-Name of Person of OrgCopy, Mid-Initial of Person of OrgCopy, Last-Name of Person of OrgCopy FROM Project of Organization TO Project of OrgCopy TO Project-Person of OrgCopy TO Person of OrgCopy WHERE Project-Title of Project of Organization = “Annual Report Preparation” DB2 SELECT Project-Title, First-Name, Mid-Initial, Last-Name FROM Project, Project, SubProj, Person, Project-Person WHERE Project.Project-No = SubProj.SuperProject-No AND SubProj.Project-No = Project-Person.Project-No AND Project-Person.Soc-Sec-No = Person.Soc-Sec-No AND Project.Project-Title = “Annual Report Preparation” SIM RETRIEVE Project-Title of Sub-Projects of Project, Name of Project-Team of Sub-Projects WHERE Project-Title of Project = “Annual Report Preparation”

29. Unisys is a registered trademark of Unisys Corporation. All other brand or product names are trademarks or registered trademarks of their respective companies.

30. Unisys