Data persistence III

Quadrimestre 4 - Information management systems option

24 hours

Coordinator : ADT

Teacher : ADT

Course Outline

Chapter 1: Introduction (4 hours)

  • Limits of a file management system
  • Specifications for a database management system (SGDB)

Chapter 2: ANSI/SPARC Model (2 hours)

  • Internal, conceptual and external diagrams
  • Correspondence rules
  • Data dictionary

Chapter 3: Relational Model (12 hours)

  • Various generations of database management system (SGDB)
  • Basic concepts [fields, relation (diagram, extension, semantics), etc.]
  • Relational algebra (Union, product, selection, projection, juncture, etc.)
  • Relational languages
  • Standardization (functional dependency, relation keys, etc.)

Chapter 4: SQL (14 hours)

  • Components (DDL, DML, DCL)
  • DML (questioning, data changes)
  • DDL (Create Table, view, synonym, Alter, Drop, representation of integrity constraints)
  • DCL principle of transaction, allocation of privileges
  • Catalogue

Chapter 5: Embedded SQL (2 hours)

  • Principles, compilation, vocabulary
  • Syntax
  • cursors

Chapter 6: Stored Procedures and Functions, Active Databases (4 hours)

  • principles of Client/Server database management systems (SGDB)
  • advantages of the centralization of functionalities
  • examples of stored procedures and stored functions in a specific environment
  • active databases, triggers

Chapter 7: Data Security (6 hours)

  • physical security
  • access security
  • logical security
  • issue of the logical security of data in the development of applications

Chapter 8: Transformation of Diagrams (4 hours)

  • conceptual, logical and physical models
  • shift from a formalized conceptual model in UML to the logical model
  • shift from the logical model to the physical model

Chapter 9: Conclusion

  • Presentation of concepts not discussed in the course: non-relational databases (hierarchic, network, object), distributed relational databases, tuning of a relational database, etc., as well as a brief outline of the course in the third year

Exercises (50 hours)

  • Intensive exercises using Select (Oracle, Access, DB2/400)
  • Example of Middleware: Odbc
  • Standardization exercises
  • Use of DDL SQL (Oracle, Access, DB2/400)
  • Programming procedures and functions at server level (in PL/SQL for Oracle)
  • Writing triggers for Oracle
  • Diagram transformation exercises