130 likes | 236 Views
Database Management. Supplement 1. 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). II. Relational Database.
E N D
Database Management Supplement 1
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)
II. 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.
What are Tasks for Database Administrator? • Define data characteristics • Establish standards • Coordinate users and designers • Create, test and document programs • Data backup and recovery • Security controls
What are standards 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.
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.
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.
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 a 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.
2. Design a query • QBE (Query By Example) vs. SQL: 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.
To design a query, you need to answer four questions: • 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)
IV. Database Design What are goals for database design? • Capability of storing all pertinent data • Reduce redundant data • Keep the number of tables to a minimum • Normalize the tables so that to minimize update and deletion problems
V. Database Design What is 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.