Assignment 1
Maria20
DatabaseDesignConcepts.pptxDatabase DESIGN CONCEPTS
Dr. Dexter Francis
2
Data Design Concepts
Data Structures
Framework for organizing, storing, and managing data
Comprises of files or tables that interact in various ways
Each file or table contains data about people, places, things, or events
2
Mario and Danica - A Data Design Example
Mario’s auto shop uses file-oriented systems
MECHANIC SYSTEM uses the MECHANIC file to store data about shop employees
JOB SYSTEM uses the JOB file to store data about work performed at the shop
Danica’s auto shop uses a relational model
SHOP OPERATIONS SYSTEM - Tables are linked by a common field named Mechanic No field
3
Data Design Concepts (Cont. 1)
3
4
Data Design Concepts (Cont. 2)
FIGURE 9-4 Danica’s SHOP OPERATIONS 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.
FIGURE 9-2 Mario’s shop uses two separate systems, so certain data must be entered twice. This redundancy is inefficient and can produce data errors.
Mario’s Auto Shop
Danica’s Auto Shop
4
Is File Processing Still Important?
Used by some companies to handle large volumes of structured data on a regular basis
Cost-effective in certain situations
5
Data Design Concepts (Cont. 3)
FIGURE 9-4 A credit card company that posts thousands of daily transactions might consider a file processing option.
5
The Database Environment
Database management system (DBMS): Collection of tools, features, and interfaces that enables users to add, update, manage, access, and analyze data
DBMS advantages
Scalability - System can be expanded, modified, or downsized
Economy of scale
Database design allows better utilization of hardware
6
Data Design Concepts (Cont. 4)
Figure 9-5 In this example, a sales database can
support four separate business systems.
6
Enterprise-wide application
A database administrator (DBA) assesses overall requirements and maintains the database
Stronger standards
Standards for data names, formats, and documentation are followed uniformly throughout the organization
Better security
Only legitimate users can access the database
Different users have different levels of access
Data independence
Systems that interact with a DBMS are relatively independent of how physical data is maintained
7
Data Design Concepts (Cont. 5)
7
8
DBMS Components
Interfaces for Users, Database Administrators, and Related Systems
Users
Work with predefined queries and switchboard commands
Use query languages to access stored data
Database administrators
Responsible for DBMS
management and support
Related information systems
DBMS provides support to
related information systems
FIGURE 9-6 In addition to interfaces or users, database administrators, and related information systems, a DBMS also has a data manipulation language, a schema and subschemas, and a physical data repository.
8
Data Manipulation Language (DML)
Controls database operations
Schema
Descriptions of all fields, tables, and relationships
Subschema: Portions of the database that a particular system or user needs or is allowed to access
Physical Data Repository
Contains the schema and subschemas
Can be centralized or distributed at several locations
Uses open database connectivity (ODBC)-compliant software that enables communication among the systems and DBMSs
9
DBMS Components (Cont.)
9
10
Data Design Terms
Definitions
Entity - Person, place, thing, or event for which data is collected and maintained
Table or file: Contains a set of related records that store data about a specific entity
Field (attribute) - Single characteristic or fact about an entity
Common field: Attribute that appears in more than one entity
Tuple (record): Set of related fields that describes one instance, or occurrence, of an entity
10
Key Fields
Primary key: Field or combination of fields that uniquely and minimally identifies a particular member of an entity
Called a combination key
Candidate key: Any field that could serve as a primary key
Foreign key: Field in one table that must match a primary key value in another table for a relationship between the two tables to exist
Secondary key: Field or combination of fields that can be used to access or retrieve records
11
Data Design Terms (Cont. 1)
11
Referential Integrity
Set of rules that avoids data inconsistency and quality problems
12
Data Design Terms (Cont. 2)
FIGURE 9-11 Microsoft Access allows a user to specify that referential integrity rules will be enforced in a relational database design.
12
13
Entity-Relationship Diagrams
Drawing an ERD
List the entities that were identified during the systems analysis phase
Consider the nature of the relationships that link them
FIGURE 9-12 In an entity-relationship diagram, entities are labeled with singular nouns and relationships are labeled with verbs. The relationship is interpreted as a simple English sentence.
13
Types of Relationships
One-to-one relationship: Exists when exactly one of the second entity occurs for each instance of the first entity
Abbreviated 1:1
14
Entity-Relationship Diagrams (Cont. 1)
FIGURE 9-13 Examples of one-to-one (1:1) relationships.
14
One-to-many relationship: Exists when one occurrence of the first entity can relate to many instances of the second entity
Each instance of the second entity can associate with only one instance of the first entity
Abbreviated 1:M
15
Entity-Relationship Diagrams (Cont. 2)
FIGURE 9-14 Examples of one-to-many (1:M) relationships.
15
Many-to-many relationship
Exists when one instance of the first entity can relate to many instances of the second entity, and vice versa
Abbreviated M:N
16
Entity-Relationship Diagrams (Cont. 3)
FIGURE 9-15 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
16
17
Entity-Relationship Diagrams (Cont. 4)
FIGURE 9-16 An entity-relationship diagram for SALES REP, CUSTOMER, ORDER, PRODUCT, and WAREHOUSE. Notice that the ORDER and PRODUCT entities are joined by an associative entity named ORDER LINE.
17
Cardinality
Describes the numeric relationship between two entities
Shows how instances of one entity relate to instances of another entity
Crow’s foot notation indicates various possibilities using circles, bars, and symbols
18
Entity-Relationship Diagrams (Cont. 5)
FIGURE 9-17 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.
18
19
Entity-Relationship Diagrams (Cont. 6)
FIGURE 9-18 In the first example of cardinality notation, one and only one CUSTOMER can place anywhere from zero 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.
19
20
Entity-Relationship Diagrams (Cont. 7)
FIGURE 9-19 An ERD for a library system drawn with Visible Analyst. Notice that crow’s foot notation has been used and relationships are described in both directions.
20
21
Data Normalization
Normalization: Process of creating table designs by assigning specific fields or attributes to each table in the database
Table design: Specifies fields
Identifies the primary key in a particular table or file
Stages in a normalization process
Unnormalized design
First normal form
Second normal form
Third normal form
21
Standard Notation Format
Used to show a table’s structure, fields, and primary key
The primary key field(s) is underlined
NAME (FIELD 1, FIELD 2, FIELD 3)
Recognition of repeating group fields is important
Repeating group: Set of one or more fields that can occur any number of times in a single record
Each occurrence would possess different values
22
Data Normalization (Cont. 1)
22
23
Data Normalization (Cont. 2)
FIGURE 9-20 In the ORDER table design, two orders have repeating groups that contain several products. ORDER is the primary key for the ORDER table, and PRODUCT NUMBER serves as a primary key for the repeating group. Because it contains repeating groups, the ORDER table is unnormalized.
23
First Normal Form (1NF)
Does not contain a repeating group
Converting an unnormalized design to 1NF requires expansion of the table’s primary key to include the primary key of the repeating group
24
Data Normalization (Cont. 3)
24
25
Data Normalization (Cont. 4)
FIGURE 9-21 The ORDER table as it appears in 1NF. The repeating groups have been eliminated. Notice that the repeating group for order 86223 has become three separate records, and the repeating group for order 86390 has become two separate records. The 1NF primary key is a combination of ORDER and PRODUCT NUMBER, which uniquely identifies each record.
25
Second Normal Form (2NF)
Examples of functional dependence
Field A is functionally dependent on Field B if the value of Field A depends on Field B
A table design is in 2NF if:
It is in 1NF
All fields not part of the primary key are functionally dependent on the entire primary key
26
Data Normalization (Cont. 5)
26
27
Data Normalization (Cont. 6)
FIGURE 9-22 ORDER, PRODUCT, and ORDER LINE tables in 2NF. All fields are functionally dependent on the primary key.
27
Third Normal Form (3NF)
A design is in 3NF if it is in 2NF and if no nonkey field is dependent on another nonkey field
Avoids redundancy and data integrity problems that still can exist in 2NF designs
28
Data Normalization (Cont. 7)
28
29
Data Normalization (Cont. 8)
FIGURE 9-23 When the PRODUCT table is transformed from 2NF to 3F, the result is two separate tables:
PRODUCT and SUPPLIER. Note that in 3NF, all fields depend on the key, the whole key, and nothing but the key!
29
30
Two Real-World Examples
Crossroads College
FIGURE 9-25 An initial entity-relationship diagram for ADVISOR,
STUDENT, and COURSE.
FIGURE 9-26 The STUDENT table is unnormalized because it contains a repeating group that represents the courses each student has taken.
30
31
Two Real-World Examples (Cont. 1)
FIGURE 9-27 The STUDENT table in 1NF. Notice that the primary key has been expanded to include STUDENT NUMBER and COURSE NUMBER.
31
32
Two Real-World Examples (Cont. 2)
FIGURE 9-28 The STUDENT, COURSE, and GRADE tables in 2NF. Notice that all fields are functionally dependent on the entire primary key of their respective tables.
32
33
Two Real-World Examples (Cont. 3)
FIGURE 9-29 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.
33
34
Two Real-World Examples (Cont. 4)
FIGURE 9-30 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.
34
35
Two Real-World Examples (Cont. 5)
Example 2: Magic Maintenance
FIGURE 9-31 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.
35
36
Two Real-World Examples (Cont. 6)
FIGURE 9-32 Sample data, primary keys, and common fields for the database shown in Figure 9-31.
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.
36
37
Data Storage and Access
Tools and Techniques
Data warehouse
An integrated collection of data that can include seemingly unrelated information, no matter where it is stored in the company
Data mart
Designed to serve the needs of a specific department
FIGURE 9-37 A data warehouse stores data from several systems. By selecting data dimensions, a user can retrieve specific information without having to know how or where the data is stored.
37
38
Data Storage and Access (Cont. 1)
Data Mining (clickstream storage)
Looks for meaningful data patterns and relationships
Suggested goals for data mining
Increase the number of pages viewed per session and referred customers
Reduce clicks to close
Increase checkouts per visit and average profit per checkout
Can be used to build a profile of new customers
Copyright ©2017 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
38
Questions?
