Transcript Database management system (DBMS)

Chapter 6
Database Management
I. The Hierarchy of Data
•
•
•
•
•
Database
File (Entity, Table)
Record (info for a specific entity, Row)
Field (Attribute, Column)
Data Types (only one type for a field)
2
II. Database and DBMS
•Database definition
A collection of data organized to service many
applications efficiently by centralizing and
relating data, minimizing redundancy, and
being independent from application programs.
3
•Database management system (DBMS)
The software that enables an org. to create,
maintain, use (query & reporting) and manage a
database.
•Data and program independence (p.209)
– Data is independent of application program
– Therefore programs are relatively independent
– Report can be modified without changing data
4
• Three Components of a DBMS
– Data definition language: specify objects and
structure of database.
– Data manipulation language: (p.212) extract
information to answer certain questions,
such as QBE and SQL.
– Data dictionary: file that stores definitions of
data elements, such as field name and record
that contains this field, data type, format,
description, ownership, security and so on.
5
Relational Database
• Relational Database Model: Most popular
database model. Data are stored in multiple
tables. One table can be related to another table if
they have a common field (usually primary key
and foreign key).
• Primary key: one field or a combination of fields
(column in a table) that uniquely identifies each
record (row in a table). If there is no one single
field that can be used to uniquely identify a
record, a primary key is made of more than one
field and called composite (concatenated) keys.
(p.208-209)
6
Tasks for Database Administrator
(P.229-p.231)
•
•
•
•
•
•
Define data characteristics
Establish standards
Coordinate users and designers
Create, test and document programs
Data backup and recovery
Security controls
7
Standards are important
• Databases and tables were developed at different
points in time by different people
• Consistent user interfaces
• Standard reports
• Data definition
• Document changes in programs and databases.
8
III. Microsoft Access
1. Basic definitions
• Application generators: It enables you to
create applications without writing any
programs. For Access database applications,
you can create tables, queries, reports, forms,
even macros without writing any
programming commands.
• Seven objects of Access database: table, query,
report, form, macro, VB module, and page.
9
• Table: stores data for an entity (e.g., customer,
order, loan, etc.) in rows (records) and columns
(fields). A record includes all information about an
instance of an entity. A field is a piece of
information in a record.
• Query: answers questions by retrieving certain
fields of certain records from one or more tables
that satisfy a set of criteria.
10
• Form: A form provides a user-friendly interface
to enter, maintain and view records, and it is
based on a table or a query.
• Report: It presents data from tables or queries in
the way that you want.
• Page: It is s Web interface supported by Access
and stored as a separate HTML file.
• Macro: a set of Access macro commands that
automates tasks.
• Module: a program written in Visual Basic to
automate tasks.
11
2. Design a query
• QBE (Query By Example) vs. SQL: (Prob. 7)
– QBE is part of application generator. It
enables you to generate queries without
writing any programming commands
– SQL is a query language for programming.
– QBE is easier to use. SQL is more flexible
and powerful. Managers may prefer to use
QBE.
12
• Logical view versus physical view:
A logical view of data is the way data is
perceived by users (tables, records, fields, etc.)
while a physical view of data is the way the
data are actually organized in physical storage
(disk, track, sector, etc.).
• To design a query, you need to answer four
questions (Prob. 3):
– What output do you want to see? (fields to show
and how to sort)
– What tables are involved?
– What constraints are given? (criteria and specified
by developer or user)
– How are the tables joined together? (directly or
through other tables)
13
• Join tables with QBE
(Prob. 4, p.217, p.218, Fig. 6.16)
• Enter criteria
(Prob. 5, p.213 Fig. 6.8)
Exercise 2:
• Calculation Field
(Prob. 6, P.215, Fig. 6.13)
14
V. Database Design
Reading: Handout: Design a Relational Database and
text (P.219 - P.225)
Normalization:
• Normalization is a design process that minimizes
redundant data by separating data into interrelated
tables.
• The normalization process can be defined by seven
categories called normal forms in the order from the
least restrictive to the most restrictive: Unnormal
form, *First normal form, *Second normal form,
*Third normal form, Boyce-Codd normal form,
Fourth normal form, and Fifth normal form.
*: Post et. al., “Management Information Systems”
15
• First normal form (p.222)
– Unnormal form: a table is in the Unnormal
form when the table has repeating sections.
– Example: project/project 2, download Excel
file “Normalization.
– 1NF: a table is in 1NF when the table
contains no repeating sections.
– Split a table.
– Example: Excel file “Normalization”.
16
• Second normal form (p.223)
– When a table is in 2NF, it must be in 1NF.
– A table is in 2NF when each nonkey data
field in this table depends on the entire key
(not partial).
– To be in the second normal form, a table
may be further split.
– Example: Excel file “Normalization”.
17
• Third normal form (p.223)
– When a table is in 3NF, it must be in 1NF.
– When a table is in 3NF, it must be in 2NF.
– When a table is in 3NF, no dependencies
among nonkey data fields in this table.
– To be in the third normal form, a table may
be further split.
– Example: Excel file “Normalization”.
18