Database Management Systems

1. Database Management Systems • Without databases data and programs are dependent upon each other • An input file makes no sense without the program to give it structure and meaning • Data is not self describing • Ex. 10 1011 Jones 1502 Evans ABC • What does this mean?

2. Database Management Systems • Sometimes comments in program convey meaning or documentation. • Same data element appears in many files • Data storage is wasted • Data integrity is a key concern • If an element is updated in one file what happens to the other files??

3. Database Management Systems • Need to write programs to “recover” relationships between data elements • Ex. Personnel file and projects file. Need to write a program to determine which people work on which project • Time consuming and redundant • Each programmer “owns” the data file associated with his program

4. Database Management Systems • Programmers us special/unique conventions for coding which might not be documented • Security issues • Who is permitted to view data? • Who is permitted to change data? • Data deletions or updates can produce inconsistencies • Dangling references

5. Database Management Systems • Terminated processes can result in data inconsistencies • Ex. A banking transaction deducts money from one account then terminates before it can complete transfer of funds to second account. • Physical concerns: • Adding new fields, storage devices,extra memory.

6. Database Management Systems • Definition • A database is a self describing collection of data elements that presents a uniform service interface • A database management system is a software product that implements access to a database.

7. File 1(Registar) File2(St. Accts) File3(Housing)

8. Questions for Files • What file structure for each? • Should we use codes, prefix or suffix? • How would we list class ranking of students • How often is Bob Perkoski name in file • Is it consistent? • If Bob drops a course what gets updated? • If school changes ID # what gets updated? • An RA wants to get a list of Bob’s professors?

9. Database Management Systems • Database Models • Definition • A set of concepts that can be used to describe the structure of the database. • Data types • Relationships • Constraints • Manipulation of the data

10. Database Management Systems • Database Models • Relational Database • Object Oriented Database Model • Deductive Database Model • Hierarchial Database Model • Network Datbase Model

11. Database Management Systems • Relational Database Model • Uses tables to organize data • Each table corresponds to an entity • Each row represents an instance of that entity • Each column represents an attribute of the entity • Tables can be related to each other

12. Database Management Systems • Relational Model Example:Salespeople Snum= unique number for each salesperson Sname=name of salesperson Comm = commission rate

13. Database Management Systems • Object Oriented Database Model • Represents an entity as a class • A class captures both attributes and behavior • Instances of the class are called objects • Within an object the class attributes take on specific values which distinguish one object from another • Does not restrict to native data types such as real,integer, can use other objects

14. Database Management Systems • Object Oriented Example • Class • Cat • Attributes • Color, weight, breed • Behavior • Scratches, sleeps,purrs • An instance of the cat class is an object with specific attributes • Example – an attribute of a cat can be owner and the owner can be a student object

15. Database Management Systems • Deductive Database Model • Also known as inferential model • Stores data and elementary facts called axioms • Other facts can be derived as needed • Ex. Fact Student owns cat :stuOwnsCat(S,C) • Can deduct if two students own the same cat and deduct new relationship:catmates.

16. Database Management Systems • Hierarchial Database Model • Relatively old, dating from 1950’s • Uses a tree structure such as a company org chart • President –> VPresident ->Dept1-Dept2-Dept3->Employees • Can be translated into a linear list

17. Database Management Systems • Hierarchial Continued • Data elements organized as tabular rows • Each row for each instance of an entity • Row position implies a relationship to other rows • Relationships represented by logical proximity in the linearized tree

18. Database Management Systems • Hierarchial Continued • Example • President ( name = jones, phone =223-3332) • Vice President ( name = boyd,…) • Department ( name =marketing) • Employee ( name = Smith) • Employee(name=Jones) • Department ( name = manufacturing) • Employee ( name = Williams) • Vice President ( name = Graham, phone =345-6789) • Department ( name =human resources)

19. Database Management Systems • Network Database Model • Replaces the hierarchial tree with a graph network • If employee works for two depts in previous example the hierarchial model breaks down. • Network model maintains relationships with a system of intersecting chains

20. Database Management Systems Department Electrical Employee Charlie Other workers Charlie works for Electrical Department Other departments Charlie works for

21. Database Management Systems DB Designer DB User Storage/Retrieval Services Schema Compiler User Interface Operating System Data Dictionary Data

22. Database Management Systems • Components of DB System • Lowest level is data and meta-data ( data that describes the data) • Cat and student example. Store attributes of cat like color , weight Store that Cat is name of a storage table and name of attributes

23. Database Management Systems • Conceptual Schema • App designer provides names of entities, attributes and relationships in a machine readable script called a conceptual schema • Ex. For the student –Cat example • Would describe the Cat table and Student Table and how Student rows relate to Cat rows • DBMS compiles the schema into compact form for reference by other parts of the system

24. Database management Systems • Interactive Interface • Users request services via the interface • Uniform to users • It uses the data dictionary to confirm existence and compatibility of data lements used in the request • The interface uses second module to process storage and retrieval requests, optimization

25. Database management Systems • Three Levels of Database Services • Physical Level • Furthest removed from application entities • Not always accessible to designer • If accessible the physical schema is a machine readable script that addresses issues like location of files, buffer size, proximity of storage, file structures

26. Database management Systems • Conceptual Level • Since the storage details are isolated the conceptual level doesn’t need to worry about it • Includes tables, objects, inference rules, entities, relationships, constraints, and security • Physical Data Independence – the decoupling of the application programs from the underlying hardware and data structures

27. Database Management Systems • External Level • Different external views of the same conceptual data • Example: some views may have all attributes of an entity while others have a subset • External Level should adapt to changes in the conceptual schema – Logical Data Independence • Ex. Add new attributes to an entity but old view still works

28. Database Management Systems • Relational Database Models • Example:

29. Salespeople Customers Orders