Lecture No. 7

1. Lecture No. 7 An Introduction to Database

2. April Events Wednesday 21st April :Semester 1 ‘HECS liability statement’ forwarded to HECS liable students

3. Objectives of this Lecture This lecture will be focussed mostly on database as a means of storing and retrieving data. The term ‘database’ is always associated with the term ‘database management system’ (DBMS) which indicates that not only is data stored but it is controlled and managed by software. • You will (probably) be using Microsoft Access for your assignment. This DBMS is aimed at the lower end of data volumes and processing. • You will also use Oracle in your laboratory work. This is a medium to large client-server DBMS

5. Before I start the ‘hard sell’ United Airlines is the world’s largest air carrier It manages many complex operations The company has nearly 60 separate applications from back office services to customer-facing Web applications which run on 9 different database platforms The Company had IT staff cutbacks in 2001, recognised the need for a serious plan for simplification, and has now started on a multiyear process of migrating and consolidating all the systems on two database platforms

6. Some Aspects of Data A major benefit of Computing is Storage and Retrieval of Data We need to have answers to these questions - WHAT are we going to produce as ‘output’ ? WHAT data is to be stored ? WHAT is the level of detail (Name, Address, Height …?) HOW long is to be retained - and is it ‘volatile’? WHAT form is it to be stored - on line, off line ? WHO is going to access it ?

7. Some Aspects of Data HOW often will it be accessed ? WHAT security of access is to be applied ? WHAT are the starting volumes - what is the growth or decay factor ? WHAT response time is expected ? HOW accurate is the data content ? HOW current is it ? (e.g. on line sales retailing)

8. Information / Data A General Definition: DATA - raw (unprocessed or partly processed) facts which represent the state of entities (things) which have occurred INFORMATION - data which has been processed into a form USEFUL TO THE USER What is Information to one user may be Data to another user.

9. Audit Trail General Definition: ‘The presence of data processing media and procedures which allow any and / or all transaction(s) to be traced through ALL STAGES of processing’ This infers that the following devices / techniques are in place: 1. A logging device which ‘traps’ all transactions 2. Some way of tagging each transaction so that it can be identified 3. Some way of retrieving the required transaction(s) 4. Some way of archiving - what is the required period ? 5. Control procedures and processes to ensure integrity

10. Data Base A Database is a shared collection of Inter-Related data designed to meet the needs of multiple types of users and applications. This implies that multiple user VIEWS can be defined Data stored is independent of the programs which use it Data is structured to provide a basis for future applications DATABASE = Stored Collection of Related Data May be physically distributed

11. Database Management System A DBMS is SOFTWARE which provides access to the database in an integrated and controlled manner A DBMS must contain : 1. Data Definition and Structure capabilities 2. Data Manipulation capabilities

12. Data Definition and Manipulation Data Definition Language (DDL) used to describe data at the database level Schema level - complete database description Sub-Schema level - user views (restricted) Data Manipulation Language (DML) Provides for Create Insert Update Retrieve (extract) Delete Drop Modify Calculation (limited) Report capabilities

13. Three Level Architecture 1. External Schema - User Views 2. Conceptual Schema - Total database description 3. Internal Schema - Physical database

14. The Many Faces of Database Databases can be: 1. Transaction Intensive - ATM’s Checkouts 2. Decision Support - Browsing for trends 3. Mixed-Load - Combination of both 4. Small databases - Few thousand records 5. Very Large Database - Many millions or trillions (VLDB) of records (Banks) 6. Non Traditional - Weather bureau, flight plans Computer Aided Design data

15. The Many Faces of Database • They can be: • Data Warehouses • Data Marts and Data Martlets • How is a database size measured ? There are a number of ‘measurements’ Raw data size Total database size Total usable disk space size (which includes media protection such as mirroring)

16. Hardware Database Raw Data Total Disk HP9000 Oracle 100GB 643GB Digital 8400 Oracle 100GB 361GB IBM SP2 DB2/6000 100GB 377GB NCR5100 Teradata 100GB 880GB NCR5100 Teradata 1,000GB 3,280GB The Many Faces of Database

17. DBMS Capabilities Querying Capabilities Data Displays (Presentation) Data entry Data Validation Data Deletion Committing Procedures (of changes) AND Data Integrity, Security, Consistency and Concurrency Capabilities

18. Important Database Features • Data Integrity • Data Independence • Referential Integrity - Relational Database Model • Concurrency Control - Multiple Users • Consistency - multi users - distributed database - replicated database - partitioned database - mobile database • Recovery from failure (Transaction and Media) • Security

19. File Processing (non database) Purchasing Program Billing Program Accounts Buyers Inventory Vendors Receivable Customers Accounts Payable Sales Order Payroll Processing Vendors Invoice Customers Inventory Employee

20. File Processing With Database Technology Orders Department Accounting Department Program Program Program Program Program A B C A B Order Filling Invoicing system system Inventory Back Inventory Customer Master Orders Pricing Master Customers

21. File Processing With Database Technology Payroll Department Program Program A B Payroll System Employee Master

22. InterRelated File Outline Sales Stats Accounts Receivable Customer Salesperson Buyers Inventory Vendor Accounts Payable General Ledger Purchase Order

23. Conceptual Data Model customer order invoice product work order raw material vendor

24. User Views customer customer product vendor customer order order invoice raw material

25. Database Models - Hierarchical owner/parent child /parent owner member child child/parent

26. Data Base Models - Network ‘set’ of data owner member owner member set of data Note: Only linked sets can be accessed

27. Data Base Models - Relational table table table table table A B C D E Any table(s) can be joined to any other table(s), provided there is a means of effecting the join Primary key / Foreign key concept. Minimal Data redundancy No fixed linkages

28. Table Name: EMP Table Name: DEPT EMPNOENAMEJOB DEPTNO 7839 KING EXECUTIVE 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 DEPTNODNAME LOC 10 ACCOUNTING CLAYTON 20 RESEARCH CAULFIELD 30 SALES PENINSULA 40 OPERATIONS GIPPSLAND Definition of a Relational Database • A relational database is a collection of relations or two-dimensional tables. Database

29. Server Data Models Model ofsystemin client’smind Entity model ofclient’s model Table modelof entity model Tables on disk

30. SQL statementis entered Statement is sent to database SQL> SELECT loc FROM dept; Database Data is displayed LOC ------------- CLAYTON CAULFIELD PENINSULA GIPPSLAND Communicating with a RDBMS Using SQL

31. Relational Database Management System Server User tables Data dictionary

32. A Primary Key - What’s that ? • McFadden, Hoffer and Prescott define a Primary Key as : An attribute (or combination of attributes) which uniquely identifies each row in a relation. (table) • Richard T. Watson has this to say: The primary key definition block specifies a set of column values comprising the primary key. Once a Primary Key is defined, the system enforces its uniqueness by checking that the Primary Key of any new row does not already exist in the table.

33. And - A Foreign Key ?? • McFadden, Hoffer and Prescott’s definition: An attribute (or attributes) in a relation (table) of a database which serves as the Primary Key of another relation (table) in the same database. • Richard T. Watson says: An attribute (or attributes) that is a Primary Key in the same table, or another table. It is the method of recording relations in a relational database. And, both the Primary and Foreign Key(s) should be drawn from the same Domain.

34. 2 Relations EMPNUM NAME Date of Birth DEPTNUM 3 JONES 16-05-1956 605 7 SMITH 23-09-1965 432 11 ADAMS 11-08-1972 201 15 NGUYEN 23-10-1964 314 18 PHAN 16-11-1976 201 Relation (Table) Name : EMP Relation Schema: EMP(empnum,name,date of birth,deptnum) DEPTNUM DEPTNAME 201 Production 314 Finance 432 Information Systems 605 Administration Relation (Table) Name : DEPT Relation Schema: DEPT(deptnum, deptname)

35. Relational DataBase Data is represented in ROW and ATTRIBUTE form (matrix) (tuple) (column, field) Collections of related data ---> TABLES (relations) 1 or more tables ----> DATA BASE ATTRIBUTES are generally static ROWS are DYNAMIC and Time-Varying The number of Attributes = DEGREE of a table The number of Rows = CARDINALITY of a table

36. Some RDB Considerations • Data is held in tables • No order of data in tables - row or attribute • Concept of Foreign Key - Primary Key relationship • Data Typing - including nulls • Query Access - insert, update, delete, retrieval • Indexing on candidate (and Primary) keys • Integrity Constraints • Attribute value ranges • Referential Integrity • Entity Integrity • User Defined Integrity • Set retention constraints

37. Some RDB Considerations • Domain constraints • User defined ‘Rules’ e.g. no examination result can be negative or > 100 • Recovery procedures. Used to restore a database after a failure • No explicit linkages between tables • Linking or embedding database operations in a procedural language (Cobol, C ..) • Databases may be distributed across similar or different DBMS’s • Security features

38. DBMS Components users Database Management System Program Language Interface Application Programs Utility Programs Retrieval Update Database

39. A Practical Development Scenario In 198n , a Company decided to develop and introduce a Payroll system using database technology. It looked this this : Payroll System Payroll Data

40. Payroll System Payroll System Personnel System Personnel Data Payroll Data A Practical Development Scenario The Company grew in numbers and expertise and decided to introduce a Personnel System. The ‘new’ design was this

41. Payroll System Payroll System Personnel System Personnel Data Payroll Data A Practical Development Scenario In the next few years, these components were added Job History Labor Distribution Labor Analysis Data Employee Tracing Data

42. Data Description Language Used to describe data at the Database level • Structure Attributes • Schema : Complete description of the database using DDL • SubSchema : Describes data in the database as it is ‘known’ to individual programs(processes) or users • The segment of logical data record(s) required • commonly known as a VIEW

43. Data Manipulation Language Language (commands and syntax) used to cause transfers of data from the Database and the Operating Environment and vice versa Variety of Languages - Access, DB2, dBASEV, SQLServer, VisualDataBase, DataBoss, Ingres, Oracle, Informix, MySQL .... Windows versions provide Icons and Menu options which are translated by the DBMS software to Database manipulation commands • Typical commands: get, put, replace, seek, update,delete, insert, drop, find, modify

44. Application Systems users(ATM’s) users users users C programs DBMS Cobol Access

45. Advantages of DataBase • Reduced Data Redundancy • Data Integrity • Data Independence • Data Security • Data Consistency • Easier use of Data via DBMS Tools (Query languages, 4GL's)

46. Disadvantages of Database • Complexity • Expense • Vulnerability • Size of - disk storage, processor memory • Training Costs • Compatibility • Technology Lock In

48. And Something Different

49. Data Types • Used to augment an attribute description and to provide a means of Integrity ’Normal’ data types are : Character (or text) Numeric - Integer, Decimal, Money, Float (in Access Long and Short Integer), Decimal with options of a number of ‘decimal’ places Date - Standard date format - various forms Logical - Yes/No True/False Exists/Does Not Exist

50. Attribute Size In many cases this is set by the Data Definition facilities e.g.. Date, Short Integer, Long Integer, Logical, Number Others are set by the Designer: Number of Characters, Size of a ‘Decimal Number’ Access allows for a Default value Duplicates/ No Duplicates allowed Primary Key nomination Indexing ‘Required’ Status of an Attribute