The way a user might perceive and use data and the optimal way a computer system might store it are often very different. In this Database Design Succinctly, learn how to model the user’s information into data in a computer database system in such a way as to allow the system to produce useful results for the end user. Joseph D. Booth will cover how to design a database system to allow businesses to get better reporting and control over their information, as well as how to improve their data to make sure it is as accurate as possible.
Data processing, computer information systems, and information technology. They’re all phrases describing computer work, and they share a common theme—they work with data/information. However, the way a user might perceive and use that information and the optimal way a computer system might store the information are often very different. In this book, we are going to discuss how to model the user’s information into data in a computer database system in such a way as to allow the system to produce useful results for the end user.
Author(s): Joseph D. Booth
Series: SUCCINCTLTY EBOOK SERIES
Publisher: Syncfusion Inc.
Year: 2022
Language: English
Pages: 87
Table of Contents
The Story Behind the Succinctly Series of Books
Information is plentiful but harder to digest
The Succinctly series
The best authors, the best content
Free forever
Free? What is the catch?
Let us know what you think
About the Author
Chapter 1 Introduction
Index cards to database tables
Spreadsheets
Small databases
SQL database
Summary
Chapter 2 Data Models
Conceptual data model
Logical data model
Entities
Attributes
Relationships
One-to-one (1:1)
One-to-many (1:n)
Many-to-many (n:n)
Physical data model
Summary
Chapter 3 Conceptual Model
Use cases
Implied entities
SIPOC diagram
Flow charts
Workflow diagrams
Whiteboard
Back of the napkin
Summary
Chapter 4 Logical Model
Attributes and keys
Type of keys
Primary key
Candidate keys
Superkey
Composite key
Surrogate key
Foreign key
Data normalization
Flexible data
Redundant data
First normal form
Repeating groups
Create a new entity
Link to the primary table
Second normal form
Third normal form
Why bother?
Boyce-Codd normal form (BCNF or 3.5 NF)
Fourth normal form
Functionality dependency
Denormalization
Summary
Chapter 5 Physical Data Model
Tables
Naming
Use singular form
Avoid database keywords
Be consistent with naming
Fields
Data types
String data types
Unicode characters
Collation
When to use character types
Numeric types
Date and time
Boolean
Constraints
Foreign key
Unique constraint
Check constraint
Defaults
Computed fields
Logical entities to physical table
Customer table
Customer ID
Address table
Phone number table
Indexes
Views
Summary
Chapter 6 Data Standards
Internal standards
User defined type
Create a rule
Create a default
User-defined table types
External standards
ISO standards
Country codes
Currency codes
Language codes
Phone number standards
Address standards
Be lazy
Pick a country
Multiple countries
Government standards
United States
European classification codes
ISIC codes
Summary
Chapter 7 Sample Data Models
Human resources
Conceptual model
Logical model
Accounting systems
Conceptual model
Logical model
Inventory
Conceptual models
Cost of goods sold
Specific ID
LIFO (last in, first out)
FIFO (first in, first out)
Logical model
Customer orders
Conceptual model
Logical model
Reservations
Conceptual model
Logical model
Sports
Conceptual model
Logical model
Summary
Chapter 8 Physical Model
Lookup tables
Journal type
Account type
Chart of accounts
Transaction
Entry
Putting it together
Summary
Chapter 9 Summary
Appendix A DBMS Field Types
