Database management systems

1. INTRODUCTION Database management systems

2. Course Information • 5 Lectures • Introduction • Tables and Filters • Relationships and Normalisation • Queries • Forms and Reports • 1 Practical • 1 Test (Component) • Exam (Written and Practical)

3. DIKW Hierarchy • DATA • Raw numbers, symbols or characters, which are not supported by any context or meaning. • E.g. 9.7 • INFORMATION • Contextual / Meaningful data. Data which can be understood. • E.g. Today’s Temperature: 9.7 degrees Celsius • KNOWLEDGE • Information that has been understood in its context, usually with reference to other information or knowledge. Essentially, it is applied information. • E.g. It is going to be cold today. • WISDOM • Knowledge that has been applied to increase effectiveness or efficiency, or adds value through insightful application of knowledge. • E.g. I should take my coat to lectures with me today. • Wisdom is sometimes ignored, or otherwise grouped with knowledge.

4. What is a Database? • A database is an ordered collection of data. • The ordered (organised) nature of databases allows for them to be used to derive information on demand. • i.e. A DBMS helps transform Data into Information. • Often, the information to be derived is not known when capturing data, and so common practice is to capture as much as is feasible / reasonable.

5. Examples of Databases • Library • OPAC searchable catalogue • Outstanding Books • Air travel • Customer Bookings records • Flight information • E Mail Accounts • Account information • Login details • Pick n’ Pay • Stock Control • Customer Loyalty • Many Many More

6. Issues of Privacy • Who is collecting what data about you? • How are they collecting it? • What will be done with this data? • The Promotion of Access to Information Act, 2000 • To give effect to the constitutional right of access to: • any information held by the State; and • any information that is held by another person and that is required for the exercise or protection of any rights;

7. What is a Database Management System (DBMS)? • Computer software that allows you to use a computer to • create (add) data and • processdata in a database. • E.g: MySQL, SQL, Microsoft Access, OpenOffice.org Base, Oracle

8. With a DBMS, you can: • DATA ENTRY AND UPDATE: • add data, change data and delete data. • QUERIES: • ask complex questions about the data in the database. • E.g. Show me the student numbers of female students who enrolled in 2010 and are South African citizens. • FORMS: • produce attractive and useful forms for entering, viewing and updating data • REPORTS: • create sophisticated reports for presenting data

9. Evolution of Database Models • Hierarchical model • Each Parent node may have many children • Each Child node may have only one parent • Network model • Each Parent node may have many children • Each Child node may have many parents • Relational model • A mathematical model defined in terms of predicate logic and set theory. • Most implementations only approximate this model.

10. Other Models • Flat Model • One giant table with all information in it. • E.g. Excel Spreadsheet • Pretty much useless in all but trivial cases. • Object Model • Relatively new model which attempts to bridge the gap between application programming and database software. • Little standardisation and technological inertia has resulted in limited use. Mostly found in specialised database applications.

11. Relational Databases • A relational database consists of a collection of tables. • e.g. A university database may contain • a Student Details table, • a Staff Details table • Etc • In the Relational Model, tables are termed “Relations”.

12. Table • Each table is comprised of rows and columns, e.g. columns rows

13. Table – Rows/Records • A row in a table is a collection of different fields all relating to one specific person or thing (an entity), and is called a record • e.g. a Student record may contain • a Student Number field, • a Name field and a Surname field • a Degree field, etc

14. Table – Columns field name • A column is a collection of factoids of a specific type containing specific pieces of data within a record, e.g. Names, Ages etc. These are called fields. • As each row is an entity, each column contains a single attribute of that entity (and any other entities in the table, or course). fields

15. Table • Thus each table contains a collection of records. • e.g. The Student Details table contains a collection of Student records. • Each record is information about a specific student.

16. Database = one or more tables Table Table Table Record Field Field Field Record Record Record Record Record Field Field Field Field Field Field Field Field Field Field Field RELATIONAL DATABASE

17. The Concept of Keys • A key is a field or set of fields which uniquely identify a record (row) in a table. • E.g. • Student Number • ID Number • First name + Last name + Date of Birth? • This is probably not a good idea - replication is possible, although very unlikely • Student Number + ID Number • It may be redundant, since both its fields are themselves keys, but it is still a key. • An entire row • This may also be a key, but its not typically a very efficient one.

18. Types of Keys • Superkeys • Any fields or combinations of fields which uniquely identify a row. • All keys are Superkeys. • E.g. • Student Number • Student Number + ID Number • ID Number • The Entire Row • Candidate Keys • Any Superkey which does not itself contain a smaller Superkey. • Put differently, it is a minimal set of columns necessary to identify a row. • There may be more than one per table. • Student number is Candidate Key • Department Code + Department ID Number is a Candidate Key. • Student Number + First Name is NOT a Candidate Key • Student Number + ID is NOT a Candidate Key

19. Types of Keys continued • Primary Key • A specific Candidate Key selected to uniquely identify a particular row in a table. • For instance, if you had both Student Number and ID Number as Candidate keys, you might select Student Number as your primary key. • Most of the time, the primary key will be obvious. • NOTE: When two all more fields are used to create a primary key, we refer to it as a “Composite Key”. • Foreign Key • A Primary key from a different table, used for referencing purposes. • A Primary Key may also be the Foreign Key for another table. • E.g. Two different tables which both use student number as a primary key.

20. Keys – The Big Picture Superkeys Candidate Keys Primary Key

21. Overview of Relationships • Relationships refer to the Primary Key / Foreign Key relationships that exist between tables • To relate two tables, the primarykeyof one table is used as a connecting or foreignkeyin another table. • The advantage of this is that, because they are related, it is easy to find data in both.

22. Example You COULD put Department Code in here, but it may be redundant. Primary Keys Foreign Keys

23. What is Wrong with this? Students Table Enrolments Table • Email Address is replicated in both tables. • If the students email address changes in the Students table, we have to change it in the Enrolments table too. • What if no one told the person in charge of Enrolments? • Lesson: Do not Replicate the same data in multiple tables, as it makes ensuring the integrity of data much more difficult.

24. Sorting Data • Once you have created a database and entered the relevant data, you can sort, retrieve and use the data. • Sorting organizes a set of records into Ascending or Descending Ordere.g. • Alphabetical (A- Z or Z-A) • Numeric (0-9 or 9-0)

25. Retrieving Data • Specific information can be obtained by retrieving (fetching) data that matches certain criteria. • This can be achieved in Microsoft Access by using a filter or a query.