Database Management Systems Fanny Widadie S P M
Database Management Systems Fanny Widadie, S. P, M. Agr 1
The Data Hierarchy ► Data field is the smallest unit of data. ► Record is a collection of related data fields. ► File is a collection of related records. ► Database is a collection of related files. § General definition § Restrictive definition 2
Database ► Table of rows and columns can be represented in a spreadsheet. ► Relational database structure is conceptually similar to a collection of related tables. ► Flat file is a table that does not have repeating columns; 1 st normal form. ► Normalization is a formal process for eliminating redundant data fields while preserving the ability of the database to add, delete, and modify records without causing errors. 3
Figure 6. 1 Spreadsheet Example of the COURSE Table 4
Database (Cont’d) ► Key in a table is a field (or combination of fields) that contain a value that uniquely identifies each record in the table. ► Candidate key is a field that uniquely identifies each table row but is not the chosen key. ► Relating tables is done through sharing a common field and the value of the field determines which rows in the tables are logically joined. 5
Database Structures ► Database management system (DBMS) is a software application that stores the structure of the database, the data itself, relationships among data in the database, and forms and reports pertaining to the database. § Self-describing set of related data. 6
Hierarchical Database Structures ► Hierarchical is formed by data groups, subgroups, and further subgroups; like branches on a tree. § Worked well with TPSs § Utilized computer resources efficiently ► Network allows retrieval of specific records; allows a given record to point to any other record in the database. 7
Figure 6. 2 The Hierarchical Structure Between the DEPARTMENT and COURSE Tables 8
Database Structures (Cont’d) ► Relational is when the relationship between tables are implicit. ► Physical relationship is when the database structure (hierarchical, network) rely on storage addresses. ► Implicit relationship is when the database structure (relational) can be implied from the data. 9
A Relational Database Example database named Schedule has been created from tables used earlier in the chapter and some others ► The database is implemented in Microsoft Access 2002 (also known as Access XP). ► Databases break information into multiple tables because if information were stored in a single table, many data field values would be duplicated. ►A 10
The Schedule Database The example is implemented on Microsoft Access DBMS but would be similar on any relational DBMS product. ► The COURSE table in Access (Figure 6. 4) is a list of data field values. The table itself had to be defined in Access before values were entered into the data fields. ► Figure 6. 5 shows the definition of the Code field. ► Figure 6. 6 illustrates that Abbreviation field values will be looked up from a list of values in the DEPARTMENT table. ► Table 6. 7 shows a single table of course and department fields before they were separated into different tables. ► 11
Figure 6. 4 The COURSE Table in Access 12
Figure 6. 5 Defining the CODE Field 13
Figure 6. 6 Look-up Values © 2007 by Prentice Hall Management Information Systems, 10/e Raymond Mc. Leod and George Schell 14
Table 6. 7 Unseperated Table of Course and Department Data Fields 15
Figure 6. 7 Access View of Tables, Fields, and their Relationships 16
The Database Concept ► Database concept is the logical integration of records across multiple physical locations. ► Data independence is the ability to make changes in the data structure without making changes to the application programs that access the data. ► Data dictionary includes the definition of the data stored within the database and controlled by the database management system. 17
Creating a Database ► Determine data that needs to be collected and stored is a key step. ► Process-oriented approach § Define the problem. § Identify necessary decisions. § Describe information needs. § Determine the necessary processing. § Specify data needs. 18
Determine Data Needs ► Enterprise modeling approach takes a broad view of the firm’s data resources; all areas are considered, and synergy of data resources between business areas can be leveraged. § Result: Enterprise data model 19
Figure 6. 8 Creating an Enterprise Data Model © 2007 by Prentice Hall 20
Data Modeling Techniques ► Entity-relationship diagrams (ERDs) is a graphical representation of data in entities and the relationships between entities. ► Entity is a conceptual collection of related data fields. ► Relationship is defined between entities. § One-to-one – 1: 1 § One-to-many – 1: M § Many-to-many – M: N 21
Figure 6. 11 Entity-Relationship Diagram 22
Diagramming Techniques ► Class Diagram is a graphical representation of both the data used in an application and the actions associated with the data; object-oriented design model. ► Objects are the data, actions taken on the data, and relationship between objects. ► Class diagrams consist of the named class, fields in the class, and actions (methods) that act upon the class. 23
Figure 6. 13 Class Diagram 24
Using the Database ► Forms show one record at a time and can be used to add, delete, or modify database records. § Navigation § Accuracy § Consistency § Filtering § Subforms 25
Figure 6. 15 Combined Data Entry Form for the COURSE and PROJECT Tables 26
Using the Database (Cont’d) ► Reports are aggregated data from the database that are formatted in a manner that aids decision making. ► Queries is a request for the database to display selected records. ► Query-by-example (QBE) presents a standardized form that the user completes so the system can generate a true query. 27
Figure 6. 16 Report of Departments Showing Courses Offered and Course Projects 28
- Slides: 28