Introduction to DBs
sonia@[Link]
If an organization does not use a database, then its data is usually kept in spreadsheets.
A relational database is a collection of relations, which are very similar to spreadsheets
as they also have rows and columns. Relations are often informally called tables. The
database below has 3 relations: Authors has 3 rows, 1 for each author. Books has 4
rows, 1 for each book. Sales has 5 rows, 1 for each book sold.
Here the ID column in each relation is a
unique identifier for its rows. These IDs are
used in the Author ID and Book ID columns
to indicate a specific author and book
Authors
respectively. This is the way rows reference ID First Name Last Name
each other in a relational database. 1 Saurav Zwane
2 Skosana Mfiki
3 Nicole Brogan
Books Sales
ID Title Author ID Price ID Book ID Date
1 How To Read 3 R 300 1 2 1/1/2021
2 Database Design 1 R 285 2 2 1/1/2021
3 The Lion, the Witch and 1 R 290 3 2 2/1/2021
the First-Years
4 4 1/1/2021
4 Why You Need Robot 2 R310
Horses 5 2 3/1/2021
Why do we need databases?
Aren’t files good enough?
• Files are usually designed for certain applications, not for
the whole organisation
• Different applications often duplicate data in separate
files, and inconsistencies arise when these files
disagree
• For every new need, a new program must be written
and tested
• Efficiency, security, reliability and concurrent access by
multiple users are very hard to provide
Without a database, different systems in an organisation have their own programs,
each using their own data files. Some data ends up stored in more than one file.
File-Based Processing
This makes it hard to work with all the data together, and usually results in
different copies of the same fact getting out of sync so that one copy has its
latest value and another copy has an older value for the same data. When this
happens, it is impossible to know which of these value is the correct (latest)
value!
© Figure fromPearsonEducation Limited 1995, 2005
Using a database instead, there is a Database Management System(DBMS)
that keeps all the data in one place and gives easy and safe access to that
data, and also allows thousands to use the same data at the same time.
© Figure fromPearsonEducation Limited 1995, 2005
Relational Database Example
The next 4 slides show how easily this example database can have tuples
created, retrieved, updated or deleted (often called “CRUD” operations)
© Figure from Pearson Education Limited 1995,
2005
INSERT INTO BRANCH
VALUES (‘B008’, ‘4 Ivy Rd’, ‘London’, ‘NW4’);
© Figure from
Education Limited 1995,
2005
select branchNo, street FROM BRANCH
WHERE city = ‘London’;
© Figure from
Education Limited 1995,
2005
UPDATE STAFF SET salary = salary * 1.1
WHERE position = ‘Assistant’;
© Figure
from
Education
Limited 1995,
2005
DELETE FROM STAFF WHERE staffNo = ‘SA9’;
© Figure from
Education Limited 1995,
2005
A DBMS is a complex software system that is doing a lot of work for us behind
the scenes. However we will not study its components in this course.
© Figure fromPearsonEducation Limited 1995,
2005
Entities (things) and Relationships are represented the same way: as relations!
Student and Course tables store Student
and Course entities respectively.
The Grade table stores the relationship
between Students and the Courses they are
taking.
We will be working with the following database example:
RateMyProf Example
Profs-schema = (Ename, EID, Papers, Topic )
Tutors-schema = (Ename, EID, Papers, Topic )
Students-schema = (Sname, SID, Fac, GPA)
Ratings-schema = ( EID, SID, Score,Attended )
EID is the unique ID of a university employee. SID is the unique ID of a student.
Ename is the name of an employee. Sname is the name of a student.
Topic is the topic taught e.g. “Unix”. Papers is no. of research papers published.
Fac is the student’s faculty. GPA is a student’ Grade Point Average.
Score is how high that student rated that prof/tutor.
Attended is what % of their lectures that student attended.
Ename EID Papers Topic
Sname SID Fac GPA Profs:
Students: Li 107 10 Java
Marx 23 SCI 52 Berman 108 50 Databases
Martin 25 EBE 71 Doe 109 40 Java
Adams 27 SCI 66 Roy 103 20 Java
Carrey 33 HUM 82
Ename EID Papers Topic
Tutors:
SID EID Score Attended Hu 211 1 Java
Ratings:
23 107 4 60 Fox 212 5 Databases
23 108 6 70 Codd 213 4 Java
25 108 3 40 Ben 214 0 Java
27 108 9 100
27 107 4 20
Example RateMyProf Database
33 107 7 80
33 103 5 40
Basic way to access data (see examples that follow) :
SELECT <columns you want> FROM <these tables> WHERE <rows are of interest>
Students Profs
Sname SID Fac GPA Ename EID Papers Topic
Marx 23 SCI 52 Li 107 10 Java
Martin 25 EBE 71 Berman 108 50 Databases
Adams 27 SCI 66 Doe 109 40 Java
Carrey 33 HUM 82 Roy 103 20 Java
SELECT * FROM Students - - gives the whole Students relation as result
SELECT SID, Sname, GPA/10 FROM Students ORDER BY SID DESC
SELECT SNAME FROM Students WHERE GPA <= 70
SELECT SUM (Papers) AS VALUE, COUNT (Papers) AS AMOUNT FROM Profs
Sname SID Fac GPA
Students
Marx 23 SCI 52
Martin 25 EBE 71
Adams 27 SCI 66
Carrey 33 HUM 82
SELECT Sname, SID, GPA/10 FROM Students ORDER BY SID DESC
Sname SID GPA/10
Carrey 33 8
Adams 27 6
Martin 25 7
Marx 23 5
Sname SID Fac GPA
Students
Marx 23 SCI 52
Martin 25 EBE 71
Adams 27 SCI 66
Carrey 33 HUM 82
SELECT SNAME FROM Students WHERE GPA <= 70
Sname
Marx
Adams
Ename EID Papers Topic Profs
Li 107 10 Java
Berman 108 50 Databases
Doe 109 40 Java
Roy 103 20 Java
SELECT SUM (Papers) AS PapersTotal, COUNT (Papers) AS NumAuthors
FROM Profs
PapersTotal NumAuthors
120 4
AS is used to give a meaningful name to a column. COUNT(Papers) AS AMOUNT
makes the name of the resulting column AMOUNT instead of COUNT(Papers).
SUM(Papers) gives the total of all values in the Papers column.
COUNT(Papers) gives how many values there are in that column.
COUNT(DISTINCT Papers) will give how many unique values there are in the Papers
column – so if everyone had written exactly 20 papers, this value would be 1.
COUNT(DISTINCT Topic) will give 2 (there is only “Java” and “Databases”).
COUNT(Topic) will give 4 (there are 4 rows).
Ename EID Papers Topic Formally, query languages are
based on the notion of a relation
Li 107 10 Java
being a set, but SQL includes
Berman 108 50 Databases duplicates as the default.
Doe 109 40 Java
To force elimination of duplicates,
Roy 103 20 Java use select distinct instead
SELECT Topic FROM Profs
SELECT DISTINCT Topic FROM Profs
Topic
Java Topic
Databases
Java
Java
Databases
Java
select * from Profs order by Topic, Ename
• Asterisk * means all attributes of the relation
• The above result will be ordered alphabetically by topic, and
alphabetically by Ename within a topic
• For descending order use order by … desc
select SID, EID
from Ratings
where Score <= 4 or Attended between 60 and 80
Operators that can be used are: and, or, not, =, <, <=, >, >=, < >
Between can be used (as SQL it meant to be similar to English) but
between is not advisable, as it isn’t clear that both boundary values
are included.
Wildcard Operations with LIKE
• Use like instead of = to do pattern matching
– percent (%) matches any sub-string, including the empty string
– underscore ( _ ) matches any one character
• Examples:
– “%7700%” matches any string containing 7700
– “_ _ _” matches any string exactly 3 characters long
– “%\%” matches any string ending in the % character
– “%9_\%%” matches any string containing a 9 and a % as the 2nd
character after that 9 (e.g. containing 90%, 99%, etc)
– example below uses this to get at UCT email addresses
– wildcards are also useful for finding words easily misspelt
select distinct name, email
from organisation
where email like “%[Link]”
Null Values
NULL signifies an unknown value or a value that does not exist.
Instead of a fake value like “” or -9999 to represent missing data, the
special word NULL is used.
So if you insert a tuple and leave out one of its values, the DBMS puts the
special value NULL in that cell.
We can use that special value NULL in our SQL statements to mean
missing/empty cell.
Note that “is” must be used instead of = when testing for NULL values.
select EID, Ename
from Profs
where Papers is not null
Null Values
• The result of an arithmetic expression involving null is null
• Any comparison with null returns unknown
• (true or unknown) = true
• (false or unknown) = unknown
• (unknown or unknown) = unknown
• (unknown and unknown) = unknown
• true and unknown = unknown
• false and unknown = false
• if a where clause evaluates to unknown it is treated as false
• min, max, avg, sum, count ignore tuples with null values
• count(*) is the exception that does count nulls as it returns how
many rows there are in the relation
Students Profs
Sname SID Fac GPA Ename EID Papers Topic
Marx 23 SCI 52 Li 107 10 Java
Martin 25 EBE NULL Berman 108 50 Databases
Adams 27 SCI NULL Doe 109 40 Java
Carrey 33 HUM 82 Roy 103 20 Java
SELECT * FROM Profs WHERE LOWER(Topic) LIKE “data%base%”
The above Select will pick up “database”, “Data Base”,
“Databases”, “Data Bases”, ‘dataabase”, “Data Bbase” …
SELECT Sname FROM Students WHERE GPA IS NOT NULL
The above Select will return Marx and Carrey.
Note “IS” must be used, rather than =, when testing for NULL