Database Management

1. Database Management Chapter 6

2. 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)

3. 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.

4. 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

5. 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.

6. 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)

7. 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

8. 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.

9. III. Microsoft Access 1. Basic definitions • Application generators: It enables youto 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.

10. 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.

11. 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.

12. 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.

13. 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)

14. 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)

15. 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”

16. 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”.

17. 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”.

18. 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”.