PRE6 HIGHLIGHTED.pdf

MODULE 3.1: DATABASE MANAGEMENT Databases and Normalization SYSTEMS Now that we know what databases and DATABASE database management systems are, let us Datab...

MODULE 3.1: DATABASE MANAGEMENT Databases and Normalization SYSTEMS Now that we know what databases and DATABASE database management systems are, let us Database Management Systems DBMS = Records take a closer look at the data that goes into In order to understand what database Records a database. Our databases will be made up management systems are, we must first = fields of records which are in turn made up of understand what a database is. In computer fields. You can think of records as the terms, a database is a collection of data. individual items that go into the databases. Typically, it is the data of one specific In designing databases, it is helpful to first enterprise. identify what records should be included in A database is not necessarily always the system. Once you have identified these stored in a computer. Records stored in a records, you can then start to create a filing cabinet, in a notebook, or what cannot DESIGN for the database. be considered a database. But often, this The design should be NORMALIZED. manual method of storing information is not There are several levels of as efficient as using a computer, and it is normalization: 1st Normal Form, 2nd not as efficient as using a database Normal Form, 3rd Normal Form, Boyce and management system. Codd Normal Form (BCNF), 4th Normal What then are database management Form, and even 5th Normal Form. We systems or DBMSs? normalize in order to reduce data ​ A DBMS is a collection of interrelated redundancy and improve data integrity. data plus the software and hardware used Normalizing until 3rd Normal Form will be to access the data in a useful manner. enough. Do you agree that using a DBMS is more efficient Why is it important for managers to know how compared to using manual methods of storing normalized databases are created? information? Why or why not? Entity Relationship Diagrams and Relational What are the functions and components of a Modeling database management system? ERD Entity Relationship Diagrams are DBMS main functions include the following composed of: (among many others): ENTITIES a. Entities - the representation we use to a. The manipulation of data; = RECTANGLES contain information on one real-world b. The definition of your database; person, object, place, etc. These are c. The processing of your data; and represented by rectangles in an ERD. d. The sharing of your data Attributes b. Attributes – properties that describe = ovals entities. They correspond to the fields in Note that a DBMS is only one component of what is records and are represented by ovals. known as a database system. This database c. Relationships – how each entities are Relationship system therefore has these four components: connected. They are represented by = Diamonds a. Users diamonds. b. Database Application c. DBMS d. Database Concept In Business 1. How does the DBMS perform the functionalities listed in this module? 2. How do the different components of a database system relate to one another? MODULE 3.2: DATABASE MANAGEMENT SYSTEMS Data Design Concepts Data Structures ○ A framework for organizing, storing, and managing data ○ Consists of files or tables that interact in various ways Each file or table contains FIGURE 9-2 In the example shown here, data data about people, places, about the mechanic, the customer, and the brake things, or events job might be stored in a file-oriented system or in a database system Mario’s Auto Shop FIGURE 9-1 Typical data design task list Mario and Danica: A Data Design Example ○ Mario’s Auto Shop Mario relies on two file-oriented systems, that store data in separate files that are not connected The MECHANIC SYSTEM uses the MECHANIC file to FIGURE 9-3 Mario’s shop uses two separate store data about shop systems, so certain data must be entered twice. employees This redundancy is inefficient, and can produce The JOB SYSTEM uses the data errors JOB file to store data about work performed at the shop Danica’s Auto Shop ○ Danica’s Auto Shop Uses a database management system (DBMS) with two separate tables that are joined, so they act like one large table In Danica’s SHOP OPERATIONS SYSTEM, the tables are linked by the Mechanic No field, which is called a common field because it connects the FIGURE 9-4 Danica’s SHOP OPERATIONS tables SYSTEM uses a database design, which avoids duplication. The data can be viewed as if it were one large table, regardless of where the data is stored physically  Is File Processing Still Important? DBMS Components ○ Handles large volumes of structured Interfaces for Users, Database data on a regular basis Administrators, and Related Systems ○ Can be cost-effective ○ USERS ○ Great for transaction processing Typically work with The Database Environment predefined queries and ○ A database management system switchboard commands, but (DBMS) is a collection of tools, also use query languages to features, and interfaces that enables access stored data users to add, update, manage, ○ DATABASE ADMINISTRATORS access, and analyze data Concerned with data security and integrity, preventing unauthorized access, providing backup and recovery, audit trails, maintaining the database, and supporting user needs ○ RELATED INFORMATION SYSTEMS A DBMS can support several FIGURE 9-5 A credit card company that posts related information systems thousands of daily transactions might consider a that provide input to, and file processing option require specific data from, the DBMS DBMS Advantages ○ Scalability - A system can be expanded, modified, or downsized ○ Economy of scale - Database design allows better utilization of hardware ○ Enterprise-wide application - A database administrator (DBA) assesses overall requirements and maintains the database for the entire ○ Stronger standards - Standards for data names, formats, and FIGURE 9-7 In addition to interfaces for documentation are followed users, database administrators, and uniformly throughout the related information systems, a DBMS also organization has a data manipulation language, a ○ Better security - The DBA ensures schema and subschemas, and a physical that only legitimate users access the data repository database and different users have different levels of access Data Manipulation Language ○ Data independence - Systems that ○ A data manipulation language (DML) interact with a DBMS are relatively controls database operations, independent of how the physical including storing, retrieving, data is maintained updating, and deleting data That design provides the Schema DBA flexibility to alter data ○ The complete definition of a structures without modifying database, including descriptions of information systems that use all fields, tables, and relationships, is the data called a schema  Physical Data Repository Data Design Terms ○ The complete definition of a Definitions: database, including descriptions of ○ ENTITY all fields, tables, and relationships, is An entity is a person, place, called a schema thing, or event for which data is collected and maintained Web-Based Data Design ○ TABLE OR FILE Overview A table, or file, contains a set ○ A data manipulation language of related records that store (DML) controls database operations, data about a specific entity including storing, retrieving, ○ FIELD updating, and deleting data A field, also called an Connecting to the Web attribute, is a single ○ The objective is to connect the characteristic or fact about database to the Web and enable an entity data to be viewed and updated ○ RECORD ○ Middleware - software that A record, also called a tuple integrates different applications and (rhymes with couple), is a set allows them to exchange data and of related fields that interpret client requests in HTML describes one instance, or form; then translate the requests into occurrence, of an entity, such commands that the database can as one customer, one order, execute or one product Data Security Key Fields: ○ Web-based data must be secure, ○ PRIMARY KEY yet easily accessible to authorized A field or combination of users fields that uniquely and minimally identifies a particular member of an entity ○ CANDIDATE KEY Any field that could serve as a primary key is called a candidate key ○ FOREIGN KEY A common field that exists in more than one table and can be used to form a relationship, or link, between the tables FIGURE 9-10 When a client workstation requests a ○ SECONDARY KEY Web page (1), the Web server uses middleware to A field or combination of generate a data query to the database server (2). fields that can be used to The database server responds (3), and middleware access or retrieve records translates the retrieved data into an HTML page that can be sent by the Web server and displayed by the user’s browser (4)  Referential Integrity: relationship is interpreted as a simple English ○ A set of rules that avoids data sentence. inconsistency and quality problems. In a relational database, referential A one-to-one relationship, abbreviated 1:1, integrity means that a foreign key exists when exactly one of the second entity value cannot be entered in one table occurs for each instance of the first entity unless it matches an existing Figure 9-15 shows examples of several 1:1 primary key in another table relationships A number 1 is placed alongside each of the two connecting lines to indicate the 1:1 relationship FIGURE 9-13 Microsoft Access allows a user to specify that referential integrity rules FIGURE 9-15 Examples of one-to-one (1:1) will be enforced in a relational database relationships design A one-to-many relationship, abbreviated Entity-Relationship Diagrams 1:M, exists when one occurrence of the first Drawing an ERD entity can relate to many instances of the ○ The first step is to list the entities second entity, but each instance of the that you identified during the second entity can associate with only one systems analysis phase and to instance of the first entity consider the nature of the relationships that link them Types of Relationships ○ Three types of relationships can exist between entities: One-to-one One-to-many Many-to-many FIGURE 9-16 Examples of one-to-many (1:M) relationships FIGURE 9-14 In an entity-relationship diagram, entities are labeled with singular nouns and relationships are labeled with verbs. The  A many-to-many relationship, abbreviated Cardinality M:N, exists when one instance of the first ○ Describes the numeric relationship entity can relate to many instances of the between two entities and shows how second entity, and one instance of the instances of one entity relate to second entity can relate to many instances instances of another entity of the first entity ○ A common method of cardinality notation is called crow’s foot notation because of the shapes, which include circles, bars, and symbols, that indicate various possibilities FIGURE 9-17 Examples of many-to-many (M:N) relationships. Notice that the event or transaction that links the two entities is an associative entity with its own set of attributes and characteristics FIGURE 9-19 Crow’s foot notation is a common method of indicating cardinality. The four examples show how you can use various symbols to describe the relationships between entities FIGURE 9-18 An entity-relationship diagram for SALES REP, CUSTOMER, ORDER, PRODUCT, and WAREHOUSE. Notice that FIGURE 9-20 In the first example of the ORDER and PRODUCT entities are cardinality notation, one and only one joined by an associative entity named CUSTOMER can place anywhere from zero ORDER LINE to many of the ORDER entity. In the second example, one and only one ORDER can include one ITEM ORDERED or many. In the third example, one and only one EMPLOYEE can have one SPOUSE or none. In the fourth example, one EMPLOYEE, or many employees, or none, can be assigned to one PROJECT, or many projects, or none  FIGURE 9-22 In the ORDER table design, two orders have repeating groups that contain several products. ORDER is the primary key for the ORDER table, and FIGURE 9-21 An ERD for a library system PRODUCT NUMBER serves as a primary drawn with Visible Analyst. Notice that key for the repeating group. Because it crow’s foot notation has been used and contains repeating groups, the ORDER relationships are described in both table is unnormalized directions First Normal Form (1NF) Data Normalization ○ A table is in first normal form (1NF) if Normalization is the process of creating it does not contain a repeating group table designs by assigning specific fields or ○ When you eliminate the repeating attributes to each table in the database group, additional records emerge — Normalization involves applying a set of one for each combination of a rules that can help you identify and correct specific order and a specific product inherent problems and complexities in your The result is more records, table designs but a greatly simplified The normalization process typically involves design four stages: ○ Unnormalized design ○ First normal form ○ Second normal form ○ Third normal form Standard Notation Format ○ Starts with the name of the table, followed by a parenthetical expression that contains the field names separated by commas. The primary key field(s) is underlined, like this: NAME (FIELD 1, FIELD 2, FIGURE 9-23 The ORDER table as it FIELD 3) appears in 1NF. The repeating groups have ○ A repeating group is a set of one or been eliminated. Notice that the repeating more fields that can occur any group for order 86223 has become three number of times in a single record, separate records, and the repeating group with each occurrence having for order 86390 has become two separate different values records. The 1NF primary key is a combination of ORDER and PRODUCT NUMBER, which uniquely identifies each record  Second Normal Form (2NF) ○ Must understand the concept of functional Dependence Field A is functionally dependent on Field B if the value of Field A depends on Field B A DATE value is functionally dependent on an ORDER, because for a specific order number, there can be only one date Objective is to break the original table into two or FIGURE 9-25 When the PRODUCT table is more new tables and transformed from 2NF to 3F, the result is reassign the fields so that two separate tables: PRODUCT and each non-key field will SUPPLIER. Note that in 3NF, all fields depend on the entire primary depend on the key, the whole key, and key in its table nothing but the key! Two Real-World Examples Example 1: Crossroads College FIGURE 9-24 ORDER, PRODUCT, and ORDER LINE tables in 2NF. All fields are FIGURE 9-27 An initial entity-relationship diagram functionally dependent on the primary key for ADVISOR, STUDENT, and COURSE Third Normal Form (3NF) ○ A design is in 3NF if every non-key field depends on the key, the whole key, and nothing but the key ○ A 3NF design avoids redundancy and data integrity problems that still can exist in 2NF designs ○ To convert the table to 3NF, you must remove all fields from the 2NF table that depend on another non-key field and place them in a new table that uses the non-key field as a primary key FIGURE 9-28 The STUDENT table is unnormalized because it contains a repeating group that represents the courses each student has taken  FIGURE 9-31 STUDENT, ADVISOR, COURSE, and GRADE tables in 3NF. When the STUDENT table is transformed from 2NF to 3NF, the result is two tables: STUDENT and ADVISOR FIGURE 9-29 The STUDENT table in 1NF. Notice that the primary key has been expanded to include STUDENT NUMBER and COURSE NUMBER FIGURE 9-32 The entity-relationship diagram for STUDENT, ADVISOR, and COURSE after normalization. The GRADE entity was identified during the normalization process. GRADE is an associative entity that links the STUDENT and COURSE tables Example 2: Magic Maintenance FIGURE 9-30 The STUDENT, COURSE, and GRADE tables in 2NF. Notice that all fields are functionally dependent on the entire primary key of their respective tables FIGURE 9-33 A relational database design for a computer service company uses common fields to link the tables and form an overall data structure. Notice the one-to-many notation symbols, and the primary keys, which are indicated with gold- colored key symbols  FIGURE 9-35 Question 1 FIGURE 9-34 Sample data, primary keys, and common fields for the database shown in Figure 9-33. The design is in 3NF. Notice that all nonkey fields functionally depend on a primary key, the whole primary key, and nothing but the primary key Working with a Relational Database Suppose you work in IT, and the sales team needs answers to three specific questions ○ Did any customers receive service FIGURE 9-36 Question 2 after 12/14/2013? If so, who were they? ○ Did technician Marie Johnson put in more than six hours of labor on any service calls? If so, which ones? ○ Were any parts used on service calls in Washington? If so, what were the part numbers, descriptions, and quantities? FIGURE 9-37 Question 3 Summary A database consists of linked tables that form an overall data structure A database management system (DBMS) is a collection of tools, features, and interfaces that enable users to add, update, manage, access, and analyze data in a database  DBMS designs are more powerful and flexible than traditional file-oriented systems DBMS components include interfaces for users, database administrators, and related systems; a data manipulation language; a schema; and a physical data repository In an information system, an entity is a person, place, thing, or event for which data is collected and maintained A primary key is the field or field combination that uniquely and minimally identifies a specific record; a candidate key is any field that could serve as a primary key An entity-relationship diagram (ERD) is a graphic representation of all system entities and the relationships among them The relationship between two entities also is referred to as cardinality Normalization is a process for avoiding problems in data design Data design tasks include creating an initial ERD; assigning data elements to an entity; normalizing all table designs; and completing the data dictionary entries for files, records, and data elements