Systems for Business Enterprises Introduction

1. Systemsfor Business Enterprises Introduction 2019 By PhD. Samia Chehbi Gamoura Associate Professor – EM Strasbourg France

2. Agenda PhD. Samia Gamoura

3. What is ‘Information System’ ?! 5min to answer…

4. Information System  Introductive Example • We want to manage customers in autonomous way • A customer is identified by a code. He has a name, an address, a zip code, and a city. • We attribute to a customer : • - One Distributor (identified by a code and has a name, and a locality) • - One or more Geographical areas (identified by a code and has a name) • Develop an Excel spreadsheet for the management of the described context (Customer and attributes). • Warning: Try to make customers belonging to more than one geographical area - An example of 2 customers is provided in Customers_Distribution_Example.xlsx • Constitute a set of some varied test casesbased on the provided file (Geographic_organization_Metropolitan_France.xlsx) • Enter at least 10 customers with sorting of distributors and geographical areas • Remember: try examples of customers who have many areas PhD. Samia Gamoura

5. Information System  Introductive Example Open : Customers_Distribution_Example.xlsx PhD. Samia Gamoura

6. Information System  Introductive Example Open : Geographic_organization_Metropolitan_France_Example.xlsx PhD. Samia Gamoura

7. Information System  Introductive Example PhD. Samia Gamoura

8. Information System  Introductive Example • Questions: • What are the advantages and disadvantages of using spreadsheets (Excel) to manage customers in this example? • What are consequences in case of changes in codes or names of the localities, cities and/or geographical areas? • What solutions would you suggest ? PhD. Samia Gamoura

9. Information System  Introductive Example Repetition is not smart to store information (redundancy) ! Storage is not optimal Too much time to introduce data in spreadsheet files ! A mistake in orthography or missing in a single line generate errors in the system ! PhD. Samia Gamoura

10. Information System  Introductive Example The most important concept in Information System is that : to write the data only one time, even if it is used by several records. To do so, data must be split into several sets (objects) to keep one reference object in the system PhD. Samia Gamoura

11. Information System  Introductive Example Object 1 : Customers Object 2 : Cities Object 5: Distributors Object 3 : Localities Object 4 : Geographical areas PhD. Samia Gamoura

12. Information System  Introductive Example  Applicative Exercise 1 Groups Work. Duration 30 minutes. Score +0.25 1. Try to split the management table of customers into 5 tables following the selected objects: Customers, Cities, Localities, Geographical Areas, and distributors. Each table should have one ID as a reference and no redundancy in tables. 2. Do you an additional Table? Why ? Give the additional table structure PhD. Samia Gamoura

13. Information System  Concept • Design level • Description of WHAT ? (business goal) • Ignore the organization of the enterprise but mention all the objects in the context as it is described •  Conceptual Data Modeling (CDM): Entity-Relationships Diagram • Logical level • Description of WHO does WHAT ? • Taking into account the Human/Machine operating and actions between entitites •  Logicial Data Modeling (LDM): Data Base Diagram • Physical level • Description of WHO DOING ? • Technical decisions to define the concrete objects and relations •  Physical Data Modeling (LDM): Software Diagram

14. Information System  Modeling Conceptual Data Modeling (CDM) Conceptual Design Logical Data Modeling (LDM) Logicial Design Data Base Management System (DBMS) Physical Design

15. Information System  ConceptualData Modeling (CDM) • Why the Conceptual Data Modeling in IS • Creating a schema for all the objects in the described system (system) Ralph – HR Manager (45 yearsold) Betty – Office Assistant (27 yearsold)

16. Information System  ConceptualData Modeling (CDM) • The objective is that to split and organize data into entities (objects) and relationships as described in the system. Employee ID Name Date of birth • Outcome: Entity Relationships Diagram (ERD)

17. Information System  ConceptualData Modeling (CDM)  Entity Relationship Model (ERM) 1..1 1..N N..N N..N 1..1 1..1 1..1 1..N 1..N 1..1 N..N 1..1 N..N 1..1 1..1 1..N 1..N N..N 1..1 N..N N..N 1..1 N..N

18. Information System  ConceptualData Modeling (CDM)  Entity Relationship Model (ERM) 1-1 relationship Two entities Ent1 and Ent2 have 1..1 relationship when there is only one realization of this relationship in both ways: Ent1Ent2: 1 Ent2Ent1: 1 Example: In France, Men can't have many wives (no polygamy) (By using https://www.draw.io/ of Google)

19. Information System  ConceptualData Modeling (CDM)  Entity Relationship Model (ERM) 1-N relationship Two entities Ent1 and Ent2 have 1..N relationship when there is only one realization in a way and more than one realizations in the other way: Ent1Ent2: 1 Ent2Ent1: N Example: The contract of professors regarding law in France, doesn’t allow to them working in more than one university. (By using https://www.draw.io/ of Google)

20. Information System  ConceptualData Modeling (CDM)  Entity Relationship Model (ERM) N-N relationship Two entities Ent1 and Ent2 have N..N relationship when there are more than one realization of this relationship in both ways: Ent1Ent2: N Ent2Ent1: N Example: One or more students can register in universities. The laws in Fance allow to students to be registered in one or many universities. (By using https://www.draw.io/ of Google)

21. Information System  ConceptualData Modeling (CDM)  Entity Relationship Model (ERM)  Applicative Exercise 2 Groups Work. Duration 30 minutes. Score +0.25 • In the example of customers management, try to create the entity-relationships diagram • (By using https://www.draw.io/ of Google) PhD. Samia Gamoura

22. Information System  Logical Data Modeling (LDM)  Structure • A set of normalized objects logically connected. • The structure of an object is represented in a table with : • Rows : Records (Data) • Columns : Attributes with values

23. Information System  Logical Data Modeling (LDM)  Structure • Object Stratic structure Attributes Values Object: Runner

24. Information System  Logical Data Modeling (LDM)  Structure • Relationship Exist between objects. Examples: Registration, having Contract, Employment Object 1 Relationship Object 2

25. Information System  Logical Data Modeling (LDM)  Structure • Values Set of values that can be taken by an attribute • text string • digital strings • date and time values A value « NULL » indicates no value for an attribute * Note: « NULL » does not signify zero « 0 » or empty string.

26. Information System  Logical Data Modeling (LDM)  Structure • Primary key • A minimal set of attributes that can uniquely identify an object. It must be unique and mandatory ex: Student ID, SS ID for citizens in France, etc.

27. Information System  Logical Data Modeling (LDM)  Structure • Simple primary key • A primary key formed by a single attribute. It must be attribute must be unique and mandatory • Composite primary key • A primary key formed by multiple attributes. The couple of values must be unique. ex: (idRunner,IdRace)

28. Information System  Logical Data Modeling (LDM)  Structure • Foreign key • The primary key of an object is related to the same value in another object to Ensure the logical relationship. This value defined an attribute named Foreign key Ex : IdClub in the table RUNNER represents the foreign key Club(IdClub, nameClub) Runner(IdRunner, firstnameRunner, lastnameRunner,…., IdClub)

29. Information System  Logical Data Modeling (LDM)  Organization In Line representation Object (#key, Attribute1, attribute2, …, #foreignKey1, #foreignKey2 …) Example Student(#codeStudent, firstName, lastName, adress1, adress2, zipCode, city, #CodeClass) Class (#CodeClass, NameClass)

30. Information System  Logical Data Modeling (LDM)  Organization Graphical representation

31. Information System  Logical Data Modeling (LDM)  Organization Graphical representation Example:

32. Information System  Logical Data Modeling (LDM)  Organization Graphical representation Objects and Data structured that are in a relationship 1-1 are generally put in the same Object as an object of relation ship with one composite primary key referring to the both primary keys (1-1 in both sides)  Rule 1

33. Information System  Logical Data Modeling (LDM)  Organization Graphical representation Objects and Data structured that are in a relationship 1-N are generally separated into the 2 reference objects  Rule 2

34. Information System  Logical Data Modeling (LDM)  Organization Graphical representation Objects and Data structured that are in a relationship N-N are generally put in the same Object as an object of relation ship with one composite primary key referring to the both primary keys (1-N in both sides)  Rule 3

35. Information System  Logical Data Modeling (LDM)  Organization  Applicative Exercise 3 Groups Work. Duration 30 minutes. Score +0.25 Using the example of Customer management, propose a Logical Data Model • 1. Online representation • 2. Graphical representation (By using https://www.draw.io/ of Google)

36. Information System  Logical Data Modeling (LDM)  Organization • Integrity Constraints: A set of rules of consistency check • By the attributes. This is related to the values in the domain : integrity constraints of values • Ex. > 0, not null. • By the primary keys of relations : integrity constraints of relations. Ex. Unique, no duplicates and non-zero • By foreign keys of relations. This is related to other values in the other relations : • Ex. The foreign key in a relation R1 can’t take other values than the primary key in the relation R2.

37. What is ‘DBMS’ ?! 5min to answer…

38. Data Base Management System (DBMS) Data is stored and organized in tables, A table is a set of data, organized in rows and columns. We can store in a table any type of information (text, numbers, graphics, sound, etc.) Each table is divided into records. Each record is divided into fields.

39. Data Base Management System (DBMS)  Concept • A DB is a structured set of data minimally interconnected and optimally redundant. • The data is stored on digital media centralized or distributed • This stored data is used for the purpose of one or more applications • This data is retrievable and modifiable by one or more users

40. Data Base Management System (DBMS)  Concept • A DBMS is a software that supports : • Structuring • Storage • Updating • DB maintaining • This software must be the single point of contact between developers and data (data base schemas, applications programming), • Also it must be the single point of contact between users and data (reviewing and updating)

41. Data Base Management System (DBMS)  Concept • Some DBMS: • Access • Oracle • MySQL • PostgreSQL • MicrosoftSQL Server

42. Data Base Management System (DBMS)  Concept DBMS and applications Data Base Management System (DBMS) Data base Applications Users

43. Data Base Management System (DBMS)  Concept Design process • Information system • Analysis (description) • Data dictionary (list) • Design schema (diagram) • Relational schema (relations) • Database study Identifying needs structuring Translation Implementation

44. Data Base Management System (DBMS)  Concept DB life cycle Logical Data Model Design Data Model Real-world Designing Creating structure Description Data Language Specific queries Desginer Implementing Maintaining Administrator DBMS indexing tools … User Manipulating Data Language Optimizing Manipulating

45. Data Base Management System (DBMS)  Structure Data base Record 1 Record 2 Fie ld 1 Fie ld 2 Fie ld 3

46. Data Base Management System (DBMS)  Data • Data Definition Language (DDL) • This is the role of the Data Definition Language (DDL). It allows to describe and identify objects of the DB. This language allows : • Creating conceptual objects and paths • Description of physical structures • Definition of views (sub-sets of data) • For an existing base, editing/deletion : • Conceptual objects • Paths • views • All descriptions of the objects constitute the data dictionary. It is a part of the base, structured and called meta-base. PhD. Samia Gamoura

47. Data Base Management System (DBMS)  Data • Data manipulation • This includes exchanges of data between users and the base. • These are essentially operations (queries) for consultation and updates. • Manipulation is done throw a Data Manipulating Language (DML) • The relational model provides a set-theoretic operations called relational algebra, which are both easy to use powerful. • The best knownlanguageis : SQL PhD. Samia Gamoura

48. Data Base Management System (DBMS)  Organization Is it enough to store data in tables ?

49. Data Base Management System (DBMS)  Organization  Queries • Query • is a question • Users ask questions to a DBMS, so they make requests of queries • A querycan select, aggregate, create, delete or update data in one or many tables • There are 2 types of queries : • Selectionqueries: for retrieval and sort of data from • Change queries: performing an action of update and/or delete data

50. Data Base Management System (DBMS)  Organization  Queries • Example • (By using MS Access) • Questions • How manyorderswere made by the customer xxx ? • Whenwas the last order made by the customer xxx ? • Whatis the annual turnover of 2010, of the supplier yyyy ?