Transcript See further instructions in accompanying Word doc under

DATABASES
IGHSAAN FRANCIS
What is a database?
A database is a
computer-based
programme that
specialises in
managing and
Most databases come with
multiple components:



manipulating

structured information.
Tables for structuring the
data
Forms for entering data
Queries for asking
questions of that data
Reports for displaying the
results of a query.
Why use a database?



A database helps you to
organize your data in a
logical manner (Tables)
Allows you to specify
how different data
relates to each other
(Tables)
Ensures that data
remains consistent
when you add, update
or delete data (Tables)



An advantage is fast
and efficient data
retrieval (Queries)
Retrieve the data you
want in the way you
want it (Queries)
Allows you to feed back
on the results of queries
(Reports)
(Purpose of database)
Who uses databases?



Schools use them to
keep a record of
students and teachers.
Doctor’s surgery uses
them to keep a record
of patients details.
Libraries use them to
keep a record the
books that members of
the public borrow.


Shops use them to
keep a record of the
items that have been
bought throughout the
day.
Police stations use
them to keep a record
a the criminal's record.
Database vs Spreadsheet
Why can’t I just use a spreadsheet?
Databases are much more powerful than spreadsheets in
the way you’re able to manipulate data.
Here are just a few of the actions that you can perform on a
database that would be difficult if not impossible to perform on
a spreadsheet.




Retrieve all records that match certain criteria
Update records in bulk
Cross-reference records in different tables
Perform complex aggregate calculations
Tables


A table is a collection of data about a specific topic,
such as students or contacts. Using a separate table
for each topic means that you store that data only
once, which makes your database more efficient, and
reduces data-entry errors.
Tables organise data into columns (called fields) and
rows (called records).
Primary Key


Every table in your database should have a
primary key — a field or set of fields with a
unique value for each record stored in the
table. You can use the primary key to
identify and refer to each record.
Use a field that is unique, where the items
for the field cannot be repeated, such as



An ID number
A cell number (telephone number)
Persal /staff number
Forms


An input interface to guide users to enter
data and to minimise errors when doing so.
Elements to a form:





Good layout less errors: Aesthetically functional
Uncomplicated
Uncluttered
Avoid having users to scroll
No jargon
Queries



Informally, a query is a question
In a database the same, a query is a request
for information from the database.
Elements to a query:




Select appropriate fields (either for query or
display)
Be clear on the criteria
Summary options (group/sum/avg/count/etc.)
Sorting
Reports

Feedback on results of a query

Elements to a report:





Format of reporting (electronic/hard copy)
Layout and detail (headers and footers)
Appropriate fields
Summary options (group/sum/avg/etc.)
Sorting
Lets create our own database

Scenario:
“ Music is love in search of a word ”
Sidonie Gabrielle
With so many ways in which music is accessible to us
since the Internet (Napster, You Tube, iTunes, Nokia Music, etc.),
and the multiple of music storage and playing devices
(CDs, iPod, MP3 players, cell phones, etc.) we need to find ways in
sorting and storing the kind of music we like in a music
collection.
Scenario
You are about to create your own music collection, of
which some CDs are from The MetroFM Top 40. Look at
the following slides (15 -17) and create a database to
organise the storing of your CDs.
(Some of the data is thumb-sucked to facilitate querying of the database)


In designing your database make use of appropriate
data types
Make the Item no. your primary key
(See further instructions in accompanying Word doc under Informal Assessment)
Instructions
Metro FM Top 40
Data Items (1)
Song: Soul In Mind
Artist(s): Lira
Item no.: 90876
Date Released: 12/06/2008
Price Was: R109.95
Price Now: R104.45
Country: South Africa
Contact no: 021 2365489
Song: Perceptions of Pacha
Artist(s): Goldfish
Item no.: 13456
Date Released: 20/12/2008
Price Was: R115.95
Price Now: R104.36
Country: South Africa
Contact no: 031 9632145
Song: Tswack stick em
Artist(s): Khulichana
Item no.: 23450
Date Released: 15/05/2009
Price Was: R153.95
Price Now: R126.36
Country: South Africa
Contact no: 011 2587413
Song: Mad
Artist(s): Ne Yo
Item no.: 21508
Date Released: 19/01/2009
Price Was: R89.95
Price Now: R80.00
Country: America
Contact no: 011 8897112
Data Items (2)
Song: Heartless
Artist(s): Khanye West
Item no.: 56782
Date Released: 30/01/2009
Price Was: R155.95
Price Now: R105.95
Country: America
Contact no: 061 2673945
Song: Diva
Artist(s): Beyonce
Item no.: 77892
Date Released: 12/12/2008
Price Was: R215.95
Price Now: R115.95
Country: America
Contact no: 021 2255889
Song: Blame It
Artist(s): Jamie Foxx feat Busta & T-Pain
Item no.: 76341
Date Released: 14/02/2009
Price Was: R150.00
Price Now: R110.00
Country: America
Contact no: 031 6497489
Song: Change The World
Artist(s): Master Jam feat RJ Benjamin
Item no.: 65432
Date Released: 01/01/2009
Price Was: R110.00
Price Now: R104.00
Country: England
Contact no: 041 2699114
Data Items (3)
Song: Dead & Gone
Artist(s): T.I feat Justine Timberlake
Item no.: 44561
Date Released: 05/05/2009
Price Was: R180.50
Price Now: R130.50
Country: America
Contact no: 091 6489744
Song: Turning Me On
Artist(s): Keri Hilson feat Lil Wayne
Item no.: 11479
Date Released: 21/11/2008
Price Was: R95.95
Price Now: R85.00
Country: America
Contact no: 011 0326580
Song: War
Artist(s): Sean Kingston feat Lil Wayne
Item no.: 65312
Date Released: 10/11/2008
Price Was: R160.95
Price Now: R130.55
Country: America
Contact no: 021 6633221
Song: Chocolate High
Artist(s): India Arie feat Musiq Souldchild
Item no.: 98112
Date Released: 20/01/2009
Price Was: R195.95
Price Now: R155.95
Country: America
Contact no: 051 2388589
Table
Further Instructions
Instructions - Queries
Further instructions – Queries/Reports