Basic Concept of Database Management Systems
|
|
Introduction
Database Design
- Designing a database starts with understanding how the data are to be used
by the users
- In particular, the designer needs to work out how the users will want to
- modify (add/edit/delete) data
- retrieve data
- Information retrieval from a 'true' database (rather than from a data
file) requires the use of various keys
- A key allows records to be found in the database
- There are several types of keys:
- A primary key is a database field that uniquely
identifies a record; that is, no two records are allowed to have the same
primary key
- This guarantees the ability to retrieve a single record
- Each table must have a primary key
- Often no appropriate 'natural' primary key exists, so the design must
include a procedure for generating an artificial one (e.g. 97563425VX)
- Even if there several 'candidates' for primary key, only one can be used
- A secondary key is a database field that identifies a
group of records; that is, several (many) records can have the same value
for that key (e.g. course code=S370, gender=female, age=20¡K)
- Secondary keys are normally 'natural' fields (e.g. gender, age¡K)
- Not all fields in the database need be keys: only those that will be
used for retrieval
- A foreign key is used to link tables
- A field that is a foreign key in one table must be the primary
key in the 'other' table
- A foreign key must also be a secondary key
- Keys are identified to the DBMS during the creation of the database
structure
- So when designing the database field structure, you must
- choose - or create - a field to act as the primary key
- select which fields (if any) are to act as secondary keys
- select which primary keys are to be used to link tables as foreign keys
Example table structure with keys
Types of DBMS
There are two major categories of DBMS (although only the second is a 'real'
DBMS):
- Record Management ("flat file") systems:
- Work with a single data file at a time
- No database integration is possible
- Databases using the network and hierarchical data models were flat file
systems
- Relational DBMSs:
- Work with one or more files, each organised into rows and columns in a
table
- Can function as flat file system
- Allow databases to be integrated
- Guarantees data integerity by eliminating data duplication
- Each data file (or table) is also called a relation
- Each table is linked (related) to the other tables by a primary/foreign
key combination
Operations in a DBMS
- Data Entry (usually through a screen-based form)
/
- Data display (through a form or a table):
- Data Retrieval: generally called a database query. Three
main methods:
- Search: scan database for all records (if any) matching a
particular value
- Query-by-example: user completes a form to show what data are
required; the DBMS matches records against the example.
- Query language: use a special-purpose "language" to control
which records are to be retrieved. Most common: SQL (Structured
Query Language). Example: Select * from customers where
(sales>1000 AND location=Melbourne)
- Data Reports:
- A formal 'printout' of some or all of the data in the database
- May be generated by a query, or display the results of a query
- May combine data from several tables
- Report generators support various summary statistics
- May include complex layout (appearance) options:
Integrated Packages
- Bring together several related applications (word processor, spreadsheet,
database, communications, graphics)
- Initially a way to allow communication between diverse applications
- But as OS increasingly provides such support (clipboard, DDE,
OLE, ActiveX, Publish-and-Subscribe), this is no longer such a necessity
- 'Modern' approach: define a protocol for communicating and create a suite
of compatible applications