Welcome to CSE3180 ‘Principles of Database Systems’ First Semester 2005

1. Welcome to CSE3180 ‘Principles of Database Systems’ First Semester 2005 This NOT This 

2. Introduction to CSE3180 This unit covers many aspects associated with database, and in this series of lectures, those related to Relational Data Base (but not exclusively) I’m : Rod Simpson My Office is : Room C 4.46 My Phone contact number is : (03)990 32352 My email is rod.simpson@csse.monash.edu.au My contact times are very limited as I’m running another unit concurrently.

3. Which stands for School of Computer Science and Software Engineering Faculty of Information Technology

4. The Time Table Laboratory Sessions will take place from 9.30am to midday • Monday, November 22nd to Wednesday November 24th • Monday, November 29th to Wednesday December 1st • Monday, December 6th to Wednesday December 8th • The laboratories are • The examination will be held on Friday 10th December from 1.30pm to 3.45pm

5. Assessment A 2 hour written examination Friday 10th December) Contribution 50% A 2 part assignment Contribution 50% - 5 short questions on database related terms and application (15%) - The development of a database and queries relative to Monash University Laboratory facilities (35%)

6. The Time Table Lectures will take place from 1.00pm to 4.00pm • Monday, November 22nd to Wednesday November 24th • Monday, December 29th to Wednesday December 1st • Monday, December 6th to Wednesday December 8th • The examination will be held on Friday 10th December from 1.30pm to 3.45pm

7. No relationship

8. PRINCIPLES OF DATABASE

9. Introduction Part 2 Special and Important Notice Today is the LAST DAY for withdrawing from this unit without penalty

10. Introduction Part 3 The notes for this unit can be found on the Monash Web Page at this address: http://www.csse.monash.edu.au/courseware/cse3180s The overheads are in PowerPoint (Office 97) format and can be viewed on the File Server software. There are other notes and papers in Microsoft Word

11. Lecture Objectives • This lecture will cover: • some thoughts on data storage and retrieval constraints • what is the form of the ‘data’ • some definitions of ‘data’, ‘information’, ‘audit trail’ • what is a data base - who would want one

12. Lecture Objectives • some of the functions of the database management software • different models (commercial) • the relational model • advantages and disadvantages of database • some practical aspects of your assignment

13. Introduction Part 4 Course Outline As you will see from the notes, the recommended text is Hoffer, Prescott and McFadden’s ‘Database Management’. Edition 7. The examples and the exercises at the end of most chapters are well worth a read, and they form the basis of the examination questions (the exam is scheduled for Friday 12th December) Another recommended text is Thomas Connolly and Caroline Beggs ‘DataBase Systems’ 3rd Edition.

14. Introduction Part 4 There is one assignment, with 2 Parts, and it is expected that you will work in groups - your tutors should have arranged that this morning. The assignment support software is either Oracle or MS Access. If you wish to use some other DBMS, (such as SQLServer or MySQL) you will need to come to some amicable arrangement with your tutor

15. Introduction Features of database such as • Recovery • Security • Consistency • Concurrency • Database Management architecture • Background processes will be based on Oracle’s version 8i. You will be using a 9i client in the labs.

16. Database This morning’s exercise would have alerted you to the need to understand what requirements are made of data analyses, who needs these analyses, when and in what form. In all this there is an expectation that the results of ‘queries’, which is the same as saying data supported by the database, is accurate, timely and complete. In these lectures, you will see how these requirements can be built into a database - as you will do with your database model. And that is what it is - a model which accurately reflects data as it occurs and is processed in the ‘real world’.

17. Some Thoughts on Data Storage • A major benefit of Computing is the ability to STORE and RETRIEVE large amounts of data • However, there are a number of processes and other considerations which need to be worked together to maximise this benefit • Some very early items are • What data ? • What are its sources ? • What are the volumes /frequency ? • How long is to be stored and Why this period ? • In what FORM is it to be stored ?

18. Some Thoughts on Data Retrieval • Who is going to ‘access’ (retrieve or query) this data ? • How often ? • From where ? • Why is data to be accessed - for what purpose ?

19. Some Thoughts on Data Retrieval • How is it to be accessed ? Voice inquiry, remote, by formal request, normal processing schedule, randomly, whenever the ‘need’ arises ? • Is the data to be freely available ? • Are there some limitations on access ? • How are these access limitations managed ? • What value is inherent in the data ?

20. Some General Thoughts • What time base or volume spread is to be represented by the data ? • What levels of accuracy are to be expected ? • Is data to be available 7 days a week, 24 hours/day ? (24x7) • What response time is expected ? Minimum ? / Tolerable ? And just what does that mean in real value terms ? • How is new or altered data to be directed to existing data ?

21. Some General Thoughts • How is input access to be controlled ? • When and why is data deleted - who authorises such deletions ? • What does the ‘data’ consist of - characters, objects, audio visual, TV, audio, animation ? • What is the optimum method of storage (organisation) ?

22. Some Definitions 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/STAKEHOLDER What is Information to one user may be Data to another user.

23. Possible influence of ABC Channel 2 ?

24. 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

25. Database 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

26. Database Management Software 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

27. 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 these Create Insert capabilities Update Retrieve (extract) Delete Drop Modify Calculation Report

29. 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 7. Mobile - Able to ‘move around’

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

31. The Many Faces of Database • They can be: • Data Warehouses • Data Marts (or 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)

32. 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

33. 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

34. Data Base Models - Hierarchical owner / parent child / parent owner member child child / parent

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

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

37. 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. Data redundancy No fixed linkages

38. A quick introduction to the developer of the Relational Data Base The late Dr. E. Codd

39. A Primary Key - What’s that ? • Hoffer, Prescott and McFadden 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.

40. And - A Foreign Key ?? • Hoffer, Prescott and McFadden’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.

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

42. What are these ? They are the Entities EMP DEPT An Employee MUST be associated with one Department Must a Department have AT LEAST one Employee ? Or can there be More Than 1 Employee ? Can there be a Department with NO employees ? Employee Details are : Employee Number, Employee Name, Date of Birth and the Department Code the Employee works in. Department Details are Department Number and Department Name

43. Relational Database Data is represented in ROW and COLUMN form (matrix) (tuple) (attribute) 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

44. 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 (number, character ..) - including nulls • Query Access - insert, update, delete, retrieval • Indexing on candidate (and Primary) keys • Integrity Constraints Attribute value ranges Referential Integrity (Foreign Key - Primary Key) Entity Integrity User Defined Integrity • ‘Sets of Data’ retention constraints

45. Some RDB Considerations • Domain constraints • User defined ‘Rules’ e.g. quantities and values must not be negative; pricing rate must not be zero • Recovery procedures • 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

46. Database Components 1. Back End Engine Used for Disk Input/Output processes (Read/Write/Find) 2. Front End Processor Data manipulation String/Arithmetic/Statistical operations 3. DBMS Interface Data Definition Language (DDL) Data Manipulation Language (DML) 4.Programmer Interface Applications Environment (4GL’s, Embedded capability)

47. 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 is commonly known as a VIEW

48. 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 - Cobol, C, Java, and SQL as in Access, DB2, dBASEV, Informix, Oracle, VisualDataBase, SQLServer, MySQL Windows versions provide Icons and Menu options which are translated by the DBMS software to Database manipulation commands Typical functions: get, put, replace, seek, update,delete, insert, drop, find, modify

49. A Typical Database Model Users - keyboard direct DBMS Database I n t e r f a c e P r o g r a m Menu Options Users Database Query Access Language Programs written in Cobol, C, C++, Pascal Java etc.

50. 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)