Modern Database Management Systems

1. Modern Database Management Systems Lecture No 3 By Engineer Bilal Ahmad Greetings For You

2. Recap So far so good we are about to complete Part 1 that was very much based on Database Concepts. Now we are heading towards the Design Concepts and you will have a flavor of Implementation as most of you do want to bring laptops ;) and this will happen next week.

3. Aim for Today In this session you will learn; The Evolution of Data Models “This is quite an interesting topic as you will come to know how this advancement took place in Data Base Systems”

4. The Evolution of Data Models We all know that in the initial phases there were short comings in the file system. Because of this issue the school of thought had to shuffle their brains to come up with a comprehensive solution. The models represents what a database is? What should it do and type of structures that it should employ.

5. Some Examples I want to share some information with you all, last night I was reading an article by Infinite Group Ltd that is one of the leading field marketing agencies in United Kingdom and Europe. They gathered some of the information that sounds very interested to me. Just have a look how those databases looked like in the initial phases that many people now can’t live without using them.

10. The Hierarchical Model It was developed to manage the large amount of data for complex manufacturing projects. This model was presented for Apollo Rocket that was landed on the moon in 1969. You must know that what is a Hierarchy? Logically and Practically. It consists of levels or segments that is very much based on File System. Can you challenge your brain and come up with a real time environment for this model? I am sure you will.

11. Constraint This model can only be used in one type of environment that is 1:M It might be ideal for this environment but can an organization of this modern era survive on a model just based on this model?

12. The Network Model To represent complex data relationships more effectively The Network Model was created. Though it is based on Hierarchical Model but the modification is it allows a record to have more than one parent. Though the Network Model is not currently used in the modern sophisticated environment but still some important concepts are still used and they are;

13. Concepts of Network Model The schema which is the conceptual organization of the entire database as viewed by the database administrator. The subschema defines the portion of the database seen by the application programmers. A data management language DML that defines the environment in which data can be managed and to work with the data in the data base. A schema data definition language DDL which enables the database admin to define the schema components.

14. Constraint Can you imagine why Network Model became too cumbersome? The problem is for the programmers to generate even the simple reports because of the lack of ad hoc query capability.

15. Ad hoc Queries What is a query? It is a question that you ask a database in the form of SQL Statement. When software developers develop any software they define certain queries as reports, forms or accessible via the query window. Ad hoc queries are those that are not defined as they are not needed on regular basis so the user can create their own scope outside the scope of the delivered software.

16. The Relational Model Historically it was introduces in 1970 by E.F. Codd of IBM. There will be more discussion in the coming chapter but for the time being you can use an analogy “This very model produced an automatic transmission database to replace the standard transmission database”.

17. The Relational Model It is a mathematical concept known as relation. Just think about rows and columns in a Table as a relation and tables can be simplified using matrix. In short you map the data more effectively and efficiently and mathematical formulas are defined to explain the relations.

18. Constraint Yes you can introduce the simplicity in defining the relations but at the expense of computer overhead. But this issue doesn’t exist now as you have so many powerful systems that do have the capacity and ability to tackle massive information. Just think about Pentium 1 and Windows 98 and now Windows 8 Business Editions and powerful tools like Oracle, My SQL etc.

19. Implementation of RDM The implementation is via a very strong and sophisticated relational database management system, basic functions are used from the previous two models (Can you name them?) and in addition to a host of other functions as well. The most important function of RDBMS is to hide the complexity to end user from the relational model.

21. How to Relate Tables They are related through the sharing of common attribute (value in a column) for example in the figure the table is linked via AGENT_CODE that exists in both the Customer and Agent Table. Even both Customer and Agent have different tables but still they are linked. In complex scenarios data can be handled more effectively and efficiently.

22. A relational Diagram

23. Relational Diagram A relational diagram is a representation of Database entities, the attributes within those entities and the relationship between those entities. In the previous figure can you explain what relationship type has been used. NB: The symbol to indicate many is Infinity.

24. Table and File: Difference As a relational table stores a collection of related entities, in this respect it is very much similar to a File. The critical difference between a file and a table is that “A table yields complete data and structure independence as it is purely logical. How the data is physically stored is of no concern to user all we need as a user is end result” SQL is based on what it does instead of How it do that is called an Abstraction and you all know why Abstraction is important.

25. SQL based Relational Database Application Involves three parts; 1. The End User Interface Allows the end user to interact with the data by auto generating SQL Code. 2. A collection of Tables stored in the Database 3. SQL Engine Hidden from the end user and executes all queries or data requests.

26. The Entity Relation Model Yes, the Relational database model has an edge on the HM and NM but the problem arise when the architecture of a Database is complex and data handling is difficult. In order to represent things graphically the Entity Relationship ER Model has become a widely accepted standard for data modeling.

27. ER Model Peter Chan introduced this model in 1976 and it gain the popularity as it complements the Relational Model. The Entities and Relationships are represented by ERD, Entity Relationship Diagram.

28. ER Components Based on Entities and Relationships, You must know what is an Entity and Relationship. We will just focus on how to represent the Entities and Relationship in ER Models. In ERD the entity is represented as a Rectangle also known as an entity box. It is written in CAPTIAL letters and singular forms as an example PAINTER instead of PAINTERS.

29. Relationships Can you remind what can be the business rules, what are the examples of Relationships and how they are represented. Hint: Three types of Relationships? If you can all write the three relationships and how they are represented and one example from each relationship.

30. Connectivity An interesting aspect is how to label the relationship types, this is known as Connectivity in ER Diagram. Two types of notations are used while designing the ERD. The Chen Notation by Peter Chan. The Crow’s foot Notation.

32. Information Just to give you some information that Many to Many relationships exists in the conceptual level. You should know how to recognize them you will learn at later stages that these relationships are not appropriate for relational models.

35. The Conceptual Model Can you make a Blueprint or ERD for a Tiny College that must have the following tables. Professor, Course, Class, Student, Enroll and Room? Each entity must have tables but for the time being you don’t have to worry just try to make a sketch of a tiny college. Try to make a logic and try to connect them. You can use the crow’s foot notation.

37. Reminder If you are interested to explore some of the tactical work you can send your Resume to infinite field marketing Ltd. There website is; http://www.infinitegroup.co.uk Incase if you apply good luck  might be difficult but not impossible as “hard work is the key to success”.