Introduction to Relational Databases

1. IntroductiontoRelational Databases

2. Database: An Introduction • Why Use Computers? • What is Data? • What is a Database/DBMS? • What is SQL?

3. Why Use Computers? • To do calculations.What computers were originally built to do. ‘Computer’ = ‘Calculator’.Examples: scientific calculations, decypher codes. • To ‘switch’ communication links.Examples:telephone exchanges, route computer data on networks. • To store data.Examples:names and addresses, electricity account details.

4. Numbers Dates Pictures Videos Text Maps Audio etc. 1, -99, 1,035,758 ‘A3*=<32’ 14 January 2001 What is Data ? Might wish to store all of these kinds of data about something. Example : species of bird.Number of birds observed, textual description of characteristics, dates of observations, map of migration routes, pictures of birds, audio recording of bird calls, video of flight.

5. Logical Data and Physical Data Logical Data Physical Data Character Field Record File Bit Byte Word Block Area

6. Logical Data NameTelRoom emailHrs Black, Paul 3637 D100 cmu5 5 Taylor, Ian 3667 P203 cmu5 15 Bennet, Carol 3384 E202 ciz9 0 Evans, Dave 3667 E200 ciz8 10 Borthwick, Brenda 4750 E200 ciz8 0 Jenkins, Haydn 3643 D314 ciz5 15 Wooley, Adrian 4726 D314 ciz6 5 NameTelRoom emailHrs Black, Paul 3637 D100 cmu5 5 Taylor, Ian 3667 P203 cmu5 15 Bennet, Carol 3384 E202 ciz9 0 Evans, Dave 3667 E200 ciz8 10 Borthwick, Brenda 4750 E200 ciz8 0 Jenkins, Haydn 3643 D314 ciz5 15 Wooley, Adrian 4726 D314 ciz6 5 NameTelRoom emailHrs Black, Paul 3637 D100 cmu5 5 Taylor, Ian 3667 P203 cmu5 15 Bennet, Carol 3384 E202 ciz9 0 Evans, Dave 3667 E200 ciz8 10 Borthwick, Brenda 4750 E200 ciz8 0 Jenkins, Haydn 3643 D314 ciz5 15 Wooley, Adrian 4726 D314 ciz6 5 NameTelRoom emailHrs Black, Paul 3637 D100 cmu5 5 Taylor, Ian 3667 P203 cmu5 15 Bennet, Carol 3384 E202 ciz9 0 Evans, Dave 3667 E200 ciz8 10 Borthwick, Brenda 4750 E200 ciz8 0 Jenkins, Haydn 3643 D314 ciz5 15 Wooley, Adrian 4726 D314 ciz6 5 NameTelRoom emailHrs Black, Paul 3637 D100 cmu5 5 Taylor, Ian 3667 P203 cmu5 15 Bennet, Carol 3384 E202 ciz9 0 Evans, Dave 3667 E200 ciz8 10 Borthwick, Brenda 4750 E200 ciz8 0 Jenkins, Haydn 3643 D314 ciz5 15 Wooley, Adrian 4726 D314 ciz6 5

7. Relationships Between Data • Bird example showed that we store items of data (= facts). • Not just interested in isolated items of data.Want to know relationships between the data items. Example : species of birdWhich bird numbers, descriptions, etc relate to which bird species ?Useful to know which birds have similar migration routes. • All sorts of relationships possible : some essential to make sense of data, some useful for different purposes.

8. Quantities of Data Amounts of data may be large or small: • Example (small) :names and addresses of members of a golf club (say 500 members). • Example (large) :electricity company stores all customers’ account details (say name & address, a/c no., meter reading, amount owed, payment method, …. ; 1,000,000 customers). • Example (very large) :supermarket stores customer purchase details for analysis (say average 10 purchases × 1000 customers × 400 stores × 355 days × 5 years = 7.1 billion purchase details).

9. What is a Database ? Definition:“A collection of data that is permanently stored”. In a database we should be able to have : • data items of different types, • relationships between data items, • different sized data collections.

10. Computerised Databases What is the benefit of a computerised database (= DB) compared to a manual paper-based collection of data ? • Compact. Less bulky than filing cabinets. • Speedy. Computer can retrieve and change data faster than a human being. • Less drudgery. Removes tedium. • Current. Easier to ensure data is up-to-date.

11. A SharedComputerised Database Some databases are shared by several/many users. Then there are further benefits : • Consistency. All users have the same data. • Centralised management. The data collection can be properly managed on behalf of the whole organisation. Sharing consistent and well-managed data is much easier to achieve if the database is computerised.

12. Database Management Systems • Need special software to manage the DB if the benefits are to be obtained. • Don’t want to have to write our own computer programs to do this. Therefore buy aDatabase Management System (= a DBMS). A DBMS should provide the means to : • change the DB contents to keep them up-to-date; • find and retrieve data from the DB; • protect the data from accidents and intruders; • store it in an efficient way on the computer.

13. DBMS • DataBase Management System • A body of software which manages a database • Examples of commercial DBMSs • MS Access • Oracle • Foxpro • Each manages Relational Databases • Relational DBMSs

14. DBMS In order to provide these facilities and obtain the benefits, a DBMS should provide the following : • A Data Description Language (= DDL).A means of telling the DBMS what sort of data to store.Example:For each species of bird, store the number of birds observed, a description of their characteristics, ..... • A Data Manipulation Language (= DML).Has two tasks : • Manipulation : to specify the data of interest.Example: the migration route of the ‘great white swift’. • Action : what to do with the specified data.Example: retrieve it, only permit certain users to see it.

15. DBMS A DBMS Manages: • Access • Everything to do with physical access to data Stored Database

16. DBMS A DBMS Manages: • Security • Who has what sort of access: read, write, read/write Stored Database

17. A DBMS Manages: • Integrity • Completeness • Consistency • Dealing with System Failure • Synchronisation • Multi-processing • Simultaneity

18. Relational DBMS Why relational ? Because : • relational DBMSs are based on simple mathematical principles; • so they are easy to learn and use, and can be very powerful and flexible. Therefore : • they are the most common type of database (over 90% of the market); • they are very important in practice.

19. What is SQL? • Structured Query Language • A Language for communicating with a Relational DBMS • Provides a Data Definition Language (DDL) . . . • . . . and a Data Manipulation Language (DML)

20. EMP NO EMP NAME SALARY E01 Jim Jones 24000 E02 Jane Doe 32000 E10 Helen Smith 35000 . . . . . . . . . Data Definition in SQL CREATE TABLE EMP ( EMP_NO CHAR(3), EMP_NAME CHAR(20), SALARY INTEGER); INSERT INTO EMP VALUES(“E01”, “Jim Jones”,24000);

21. EMP NAME SALARY Helen Smith 35000 Jane Doe 32000 Data Manipulation in SQL SELECT EMP_NAME,SALARY FROM EMP WHERE SALARY > 25000 ORDER BY SALARY DESCENDING;

22. Review Note the following key points from the introduction and ensure you focus on them in the following material : • The need for a DBMS to cope with different kinds of data, relationships between data, and different volumes of data. • The benefits of a computerised DB. • A DBMS should provide an effective DDL and DML, in order to provide an optimal way of storing data on a computer. Read Chapter 1 in the set text - Elmasri/Navathe, Fundamentals of Database Systems 2nd edition, Addison Wesley or Read Chapter 1 in the text – Date, An Introduction to Database Systems 7th edition, Addison Wesley.

23. Summary • Data • Physical Data • Logical Data • Database • A Definition • DBMS • Functions • SQL

24. Exercises Here is an Exercise that you should try, based on the material covered in this topic. When you have performed the exercise, you will be given a solution to look at.