CSC 931 Computing Science I Databases Objectives Know

CSC 931: Computing Science I Databases • Objectives: – – Know what a database does and when to use one. Understand how a database is composed of tables and fields. Understand the idea of a primary key. Understand how to retrieve data. • (The practical will provide an introduction to Microsoft Access, a particular relational database. ) © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 1

Why use Databases? (or, Can’t I use a Spreadsheet? ) • Consider storing information about borrowing books from the Library. Name Alan Reg. num Address Title ISBN Location 8847230 Comp. Sci None None Easy Access 123912312 Floor 2 Charles 9999554 ASH Being Cool 456234562 Floor 1 Charles 9999554 ASH DIY TNT 564564523 Floor 1 Alan 0045673 Geddes Pasta World 234234234 Floor 3 • Problems of this approach – Empty partial rows – Duplicate information © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 2

What is a Database? • An electronic version of a filing cabinet / a computerised recordkeeping system. • A collection of related information (or data), which the computer can store, search and sort. • Examples: – Banking Records – Airline Reservations – Student Records • Every large organisation nowadays has a database. • (including the University - actually we have lots of databases, for Student Records, for Estates and Campus Services (accommodation and maintenance), for Finance, for Staff Records, to log calls to Information Services …) • And Databases are used on the WWW to provide information (as mentioned before), e. g. Amazon. © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 3

What are the benefits of Databases? • Volume of information handled • Speed • Less drudgery for end users • By centralising the database, organisations gain the following benefits – – No duplication of data Maintain data integrity and security of information Controlled access Strengthened management control • Weaknesses of centralisation are: – susceptibility to hardware failure – greater security risk © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 4

Database Structure • Data is stored in tables. • Each row in a table contains a record of information about some particular thing (entity). – (These might be relationships between things too - see later) • Each record consists of a number of fields, each of which contains the value of some attribute. • A column in a table contains all of the values for one of the attributes. Each column is headed by the name of the attribute. • A collection of records together make up a table. • All of the records must have the same structure – same number of fields, same kind of data in each field. © University of Stirling 2007 Reg. num Name Address 8847230 Alan Comp Sci 9999554 Charles ASH 0045673 Alan Geddes CSC 931 Computing Science I Databases/Slide 5

Example: A Library • Things involved (entities) are – Publication and Person • These are related by people borrowing publications. • Information is held (attributes) for each individual Person – e. g. name, address, registration number – so there’ll be a row (record) for each Person entity. • Information is held (attributes) for individual Publications – e. g. title, location, ISBN – so there’ll be a row (record) for each Publication entity. © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 6

Creating a Database • Usually, each entity identified has an associated table. • So the library requires a “Publication” table and a “Person” table. • The layout of a table, i. e. the list of headers for the columns, and the kind of data (e. g. numbers, text) in each column is known as the schema for the table. • For the database to work properly, we need to be able to identify each row in a table uniquely. • To provide this unique identification, one or more columns of the table is designated the primary key. • When we specify values for the primary key columns, there will be only one row containing those values in those columns. • Examples: © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 7

Separate Tables Publication Person Reg. num Name 8847230 Alan Title Address Comp. Sci ISBN Location Easy Access 123912312 Floor 2 9999554 Charles ASH Being Cool 456234562 Floor 1 0045673 Alan Geddes DIY TNT 564564523 Floor 1 Pasta World 234234234 Floor 3 Primary Key Columns © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 8

Tables for Relationships • We are also interested in relationships between entities. • In the Library example, there is a relationship between person and publication – “borrows” • To represent this relationship we can construct a Borrows table, in effect as a link between the other two tables. • This table will have at least two fields, namely the primary key fields from the tables we are trying to link. • We can also add other attributes – Date borrowed and date returned • An entry in the Borrows table means – that person (whatever registration number) has borrowed – that book (whatever ISBN) – On the date given (and possibly returned it on the second date). © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 9

Library Example (again) Publication Person Reg. num Name Address Alan Comp. Sci 9999554 Charles 0045673 Alan 8847230 ISBN Location Easy Access 123912312 Floor 2 ASH Being Cool 456234562 Floor 1 Geddes DIY TNT 564564523 Floor 1 Pasta World 234234234 Floor 3 Date-out Title Reg. num ISBN Date-in 9999554 456234562 4 -11 -06 6 -11 -06 9999554 564564523 15 -11 -06 - 0045673 234234234 31 -8 -06 - Borrows © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 10

Queries (or, Getting the Information back out again) • Database systems provide a query facility – this allows data to be retrieved as required by users. • There is a standard language for this called Structured Query Language (SQL). • In Microsoft Access, this query language is hidden, and we are provided with a helpful user interface to carry out the corresponding operations. • Operations of interest include: – Select – Project – Join © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 11

Relation Operation- Select • This makes a new table containing those rows from a given table which satisfy some condition(s) (criteria). • Used to pick out certain rows in tables, and ignore other rows. • “Criteria” include – Having a particular value for an attribute (e. g. books stored on Floor 2). – Having attribute values in certain ranges (e. g. age > 21). – Combinations using logical operators ( AND, OR, NOT). • A Select operation never gives you more than you started with. • Selects whole rows. © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 12

Example Select • “Select from Publications where Location = Floor 2” Title ISBN Location Wonders of the WWW 789789789 Floor 2 101 Ways with Beetroot 763728564 Floor 3 Easy Access 123912312 Floor 2 Killer Websites 846729874 Floor 2 Being Cool 456234562 Floor 1 DIY TNT 564564523 Floor 1 Pasta World 234234234 Floor 3 © University of Stirling 2007 Title ISBN Location Wonders of the WWW 789789789 Floor 2 Easy Access 123912312 Floor 2 Killer Websites 846729874 Floor 2 CSC 931 Computing Science I Databases/Slide 13

Relation Operation - Project • Makes a new table by restricting a given table to those columns which satisfy some condition. • Used to pick out attributes (columns) which are of interest. • There may be duplicates in the result. Normally such duplicates will be removed. • “Project Location over Publications” Title ISBN Location Wonders of the WWW 789789789 Floor 2 101 Ways with Beetroot 763728564 Floor 3 Location Easy Access 123912312 Floor 2 Killer Websites 846729874 Floor 2 Floor 3 Being Cool 456234562 Floor 1 DIY TNT 564564523 Floor 1 Pasta World 234234234 Floor 3 © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 14

Relation Operation - Join • This joins two tables to form a new table. The tables must have one or more columns with the same sort of values. • E. g. pairs of rows (one from each table) for which the “b” values are the same are combined. © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 15

Relation Operation - Join • Example: – Person table of reg. num, name, address – Borrows table of reg. num, isbn, date-out, date-in – Put them together to see how names link to books borrowed. Reg. num Name Address Reg. num ISBN Date-out Date-in 8847230 Alan Comp Sci 9999554 456234562 4 -11 -06 6 -11 -06 9999554 Charles ASH 9999554 564564523 15 -11 -06 - 0045673 Alan Geddes 0045673 234234234 31 -8 -06 - Join Reg. num Name Address ISBN Date-out Date-in 9999554 Charles ASH 456234562 4 -11 -06 6 -11 -06 9999554 Charles ASH 564564523 15 -11 -06 - 0045673 Alan Geddes 234234234 31 -8 -06 - © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 16

Using Relation Operations • In the book-borrowing example we can see how Join is useful in retrieving data from the database. • Suppose we want to find the titles of the books borrowed by 9999554: – First do a Select on Borrows to yield all the rows containing ‘ 9999554’ Reg. num ISBN Date-out Date-in 9999554 456234562 4 -11 -06 6 -11 -06 9999554 564564523 15 -11 -06 - – Now Project on this table to keep only Reg. num and ISBN © University of Stirling 2007 Reg. num ISBN 9999554 456234562 9999554 564564523 CSC 931 Computing Science I Databases/Slide 17

Using Relation Operators Reg. num ISBN 9999554 456234562 9999554 564564523 – To get the names of the books, we need to Join with Publication (the ISBN column being common) Reg. num ISBN Title Location 9999554 456234562 Being Cool Floor 1 9999554 564564523 DIY TNT Floor 1 – Then the Project operation will isolate the Title column Title Being Cool DIY TNT – and that’s it © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 18

Database Support Tools • Most Database systems provide simple ways to access data: • To support everyday users, there are tools to create data entry forms • This creates a blank form on the screen, where the operator fills in the blanks as appropriate • For managers, there are report writers which can be used to summarise data and print it in some desired format (or incorporate details extracted from a database into another document). • (You will see reports in the practical. ) © University of Stirling 2007 CSC 931 Computing Science I Databases/Slide 19