DATABASE MANAGEMENT

Database:

 A collection of data files that may be used by a number of applications with storage and access
to the data controlled by a set of programs known as Database Management System (DBMS).
These are used to store large amounts of data that can be easily manipulated with speed and
accuracy. Mainly used by large organization e.g. banks, companies etc.

Database Management System (DBMS):

 DBMS – is an electronic database that stores large volumes of data for easy manipulation and
retrieval.

 Microsoft Access is an example of a DBMS where data is stored in tables.

Functions of a DBMS:

 Data storage, retrieval and updates

 Backup and recovery

 Security

 Managing facilities for sharing a database

MANUAL DATABASES:

 Telephone Books

 Dictionaries

 Recipe Cards

 Television Guides

Computerised Databases:

 Flight Information Systems

 Database Systems in Public Libraries

 Databases are more used for applications with a large amount of text, whereas spreadsheets
can handle complex numerical calculations more easily.

 Very large applications with thousands of entries are more often handled in databases.

Advantages of Computerised Databases:

 Saves paper and filing space

 Easy data entry – Keyboard or scanner

 Speed – data can be found, calculated and sorted very quickly

 Data can easily be changed and updated

 Data can be checked on entry

 Data can be imported or exported to other programs

Disadvantages of Computerised Databases:

 Can be expensive to set up and maintain

 If the computer is down the system is useless

 Vulnerable to computer viruses

 Size is often limited

 Some may be complicated to use

 Some require staff training which can be costly

Database Terms and definitions:

 Object – Components that make up a database (i.e., tables, forms, queries, reports, macros, and
modules).

 Database – A database can consists of multiple tables.

 Table – also referred to as a Relation or file. A table is a collection of records about a specific
topic, such as students or vehicles.

 Record – also referred to as a Tuple or Row. Represents a single item in a table.

 Fields – also referred to as an Attribute or Column. Is used to label an element of a Record e.g.
Address or Date of Birth.
  Query – Asks specific questions about the data in the database.

 Form – A graphical user interface designed specifically for entering, displaying, and searching
data. This is an alternative to entering data in the spreadsheet-like view.

 Report – Summarises and formats data from either table or query data

Table Properties:

 Field name – The name of each field which identifies the data and should be meaningful e.g.
FirstName, Cost

 Data Type – Determines what kind of data can be entered as well as what operations the
database can perform with the data e.g. text, currency

 Field Length – Specifies the maximum length of a field known as field size

 Field Description – This is a brief comment about the purpose of the field or the data that should
be stored in it.

 Field Properties – is a set of properties that control the way a table stores, handles and displays
data.

 Primary Key – this uniquely specifies a record within a table. Usually one particular field of each
record contains an item which is used to identify the record. This field is called the primary key
field and must uniquely identify each record e.g. the ItemNumber or IdNumber.

 Secondary Key – This exists in a table if there is another uniquely identifying field other than the
primary key e.g. Telephone Number.

 Candidate Key – this is any key in a table that can be used as a primary key e.g. Telephone
Number. Does not accept null value.

 Alternate Key - This exists in a table if there are other uniquely identifying fields other than the
primary key and secondary key e.g. a combination of first, middle and last name fields.

 Composite Key – This is when two or more fields are combined together to form a key in a table.

 Foreign Key – is a field in a relational table that matches the primary key column of another
table

What is a Form:

 It is a graphical representation of a table where you can also add, update and delete records in
your table as with a datasheet.
Relationship:

 Frequently, a meaningful relationship exists between two different types of entities (tables).

For Example:

ITEMs are stored in a STOREROOM

EMPLOYEEs work in a DEPARTMENT

LAWYERs advise CLIENTs

EQUIPMENT is allocated to PROJECTs

TRUCK is a type of VEHICLE

Types of Relationships:

 There are potentially two types of relationships which can exist between two entities (tables).

• one-to-one relationships

• one-to-many relationships

Searching a Database:

 This is sometimes called interrogating or querying data and is done by giving clear criteria
(conditions) for the search.

 A Query (also known as a filter or search) is used to answer a question using the data in a
database. The database is searched to find all the records that match a particular condition
(criteria).

 Found set – this is a set of records (or a record) that you end up with after conducting a search

 Sorting records – temporarily reorders the database file. Sorting allows you to browse, update,
export or print records in a particular order.

 Microsoft Access Continuation Calculated Fields a virtual field in a query or report, the value in
this field is a function of one or more fields from a table or query.

 The syntax of a calculated field is always the same:

New field name: [definition]

e.g. Stock: [Cost]*[NumberInStock]

Price: [Cost]*1.15

Aggregate Query:

 Is a special type of query within Microsoft Access that allows you to group your numerical data
to perform specific mathematical functions.
Operators
Operators used for selecting data (criterion/criteria), some examples are:

Operators in DB Function

Additional Arithmetic \ Round to integer e.g. 5\2 = 2

Character concatenation [Initials] &“ ”&[Surname]

Logical and, or, not town =“Kin” and sex =“m”

Between between #1/3/93# and #4/5/12#;

between 28 and 39

in in(“St. Thomas”,“Portland”)

S* Any string starting with S

*S Any string ending with S

? A single character

# A single digit

* Any number of any character

like Like “6/8/1992”

Report:

 This is an effective way to present your data in a printed format, because you have control over
the size and appearance of the data and headings. Fields can be grouped with different kinds of
sub-totals including sum, minimum, maximum, average.

Importing:

 You can import data from another database file, or from another application, into an existing
database. You can also merge (combine) two or more database files.

 Exporting is the opposite of importing where a file is sent to another database or application.