1 Universita’ di Milano Bicocca Carlo Batini Course in Data Base Design Part 1 – Introduction to Data Bases

2 © Carlo Batini, 2015 This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License. To view a copy of this license, visit

3 High level conceptual map

4 Low-level conceptual map

5 A thermometer

6 Organizational system and Information SystemInput: Goods or requests of Services Output: Goods or delivered Services Processes Human Resources Infor- mation ICT Technologies Hardware Software Network Data Information System

7 Computer structure

8 Two tables

9 Two tables

10 Two software applications using dataCourses of Professors Salaries of Professors Course Assignment Salary Management

11 The new set of tables

12 The data base approach Courses Course Assignment ProfessorsSalaries Course Assignment Salary Management Professors

13 Database Management Systems allow concurrent and efficient access to the data baseCourses Salaries Course Assignment Salary Management Professors Database Management System

14 The new set of tables Salaries Professors Courses Level Yearly Salary1 20.000 2 25.000 3 30.000 4 35.000 Course # Prof Id Prof. Surname Level 37 1 Xu 4 29 16 2 Smith 3 Courses Student Id Prof Id Grade 37 1 80 29 95 16 2 85

15 The new set of tables

16 Database Management System and its relationship with a computerSoftware applications Users DBMS Secondary Memory Computer

17 Hierarchical and relational models of dataStudent Id Course Id Grade 12127 37 80 29 90 18 85 23344 95 Student Id Course Id Grade 12127 37 80 29 95 18 85 23344

18 Hierarchical Relational Student Id Course Id Grade 12127 37 A 29 C 18B 23344 Student Id Course Id Grade 12127 37 A 29 C 18 B 23344

19 Databases as representation of a piece of reality using a data model

20 A relation (or table)

21 Reality, database, schema and instanceRepresentation Model Database Concepts Schema Values of concepts Instance

22 Exercise 1.1 - Given the following database, reply to the following questions

23 Design phase of the Database life cycleDatabase schema Schema Design Students, with Student Id and Surname Relational model Student (Student Id, Surname)

24 Instance update phase

25 Student Data Schema Data Values Student Id Course Id Grade 12127 37 8029 95 18 85 23344 Data Values

26 DB Design LC Conceptual design Conceptual schema Logical designLogical schema Relational model Conceptual design Requirements Entity Relationship Model DB Design LC

27 Assume we are in a Chinese University (where grades are among 0 and 100). You want to represent:three exams passed by a student with Student Id = referring to: 1. Course with Course Id = 37 and exam passed with grade 80. 2. Course with Course Id = 29 and exam passed with grade 90. 3. Course with Course Id 18 and exam passed with grade 85. one exam passed by a student with Student Id = referring to: 1. Course with Course Id = 29 and Exam passed with grade 95.

28 Query phase Database instance Query Query languageE.g. Which surname has the Student with StudentId = 29? Table result of query E.g. Xu

29 Databases as representation of a piece of reality using a data model

30 Central Processing Secondary Memory Unit Channels Main memory Input10-8/10-9 seconds 10-2 External World Input Output Devices

31 Conceptual design and Logical designConceptual schema Logical design Logical schema Relational model (closer to the DBMS) Conceptual design Requirements Entity Relationship Model (closer to the user) Conceptual design and Logical design

32 Concepts introduced in Part 1

33 Resti

34 Student Schema Data Student Id Course Id Grade 12127 37 80 29 95 18 8523344 Data

35 Student Data Schema Data Values Student Id Course Id Grade 12127 37 8029 95 18 85 23344 Data Values

36 Design phase of the Database life cycle

37 Instance Update phase Database schema Create and updateRelational instances Manipulation language Relation instance (e.g. Student Id = 37, Surname = Wang Student Id = 29, Surnaqme = Xu Database Instance

38 three exams passed by a student with Student Id = 12127 referring to: Assume we are in a Chinese University (where grades are among 0 and 100). You want to represent: three exams passed by a student with Student Id = referring to: 1. Course with Course Id = 37 and exam passed with grade 80. 2. Course with Course Id = 29 and exam passed with grade 90. 3. Course with Course Id 18 and exam passed with grade 85. one exam passed by a student with Student Id = referring to: 1. Course with Course Id = 29 and Exam passed with grade 95. Assume we are in a Chinese University (where grades are between 0 and 100). You want to represent: three exams passed by a student with Student Id = referring to: 1. Course with Course Id = 37 and exam passed with grade 80. 2. Course with Course Id = 29 and exam passed with grade 90. 3. Course with Course Id 18 and exam passed with grade 85. one exam passed by a student with Student Id = referring to: 1. Course with Course Id = 29 and Exam passed with grade 95.

39 Modeling Relational Model Student Student Id Course Id Grade 12127 37Assume we are in a Chinese University (where grades are between 0 and 100). You want to represent: three exams passed by a student with Student Id = referring to: 1. Course with Course Id = 37 and exam passed with grade 80. 2. Course with Course Id = 29 and exam passed with grade 90. 3. Course with Course Id 18 and exam passed with grade 85. one exam passed by a student with Student Id = referring to: 1. Course with Course Id = 29 and Exam passed with grade 95. Relational Model Modeling Student Student Id Course Id Grade 12127 37 80 29 95 18 85 23344

40 Courses Course Assignment Professors Salary Management Salaries

41 Hierarchical and relational models of dataStudent Student Student Id Course Id Grade 12127 37 80 29 90 18 85 23344 95 Student Id Course Id Grade 12127 37 80 29 90 18 85 23344 95 Hierarchical Model Relational Model