Lesson 1

This course describes a design methodology for creating a database capable of producing useful information in a consistent manner. Current relational database software has brought point-and-click ease to the task of creating dabases. However, unless your database is properly designed, it will be characterized by data problems such as

1. missing data,
2. incorrect data and
3. mismatched data

that will make it almost useless, no matter how user-friendly the software is. This course on Relational Database Design is the first step in learning a design methodology that will help you create a database capable of producing useful information in a consistent manner. Specifically, I will discuss the principles of data modeling, culminating in the creation of a database diagram.

• Database Design Learning Objectives
  After completing the first course in this series, you will be able to:
  1. Explain relational database design concepts and terminology
  2. Explain the difference between conceptual and physical models
  3. Develop a conceptual design
  4. Analyze user information requirements
  5. Describe the entity/relationship modeling approach
  6. Identify entities
  7. Model relationships and identify attributes
  8. Use primary and foreign keys

Relational database design is pivotal in constructing efficient, reliable, and scalable databases. A relational database is a special type of database using structures called tables. Tables are linked together using what are called relationships. You can build tables with relationships between those tables, not only to organize your data, but also to allow later retrieval of information from the database.Properly designing a relational database ensures that data is stored logically, minimizes redundancy, and facilitates easy retrieval, modification, and deletion. Here are the foundational concepts essential for implementing relational database design:

1. Entities and Attributes:
   • Entity: Represents a real-world object or concept, such as a person, product, or event. Each entity corresponds to a table in the database.
   • Attribute: Characteristics or properties of an entity. For instance, for an entity "Student," attributes could be "StudentID," "Name," "DOB," etc.
2. Keys:
   • Primary Key (PK): A unique identifier for each record in a table. No two records can share the same primary key value.
   • Foreign Key (FK): An attribute in one table that refers to the primary key of another table, establishing relationships between tables.
   • Composite Key: A key that consists of two or more attributes to ensure uniqueness within the database.
   • Candidate Key: An attribute or set of attributes that can be a primary key. A table may have multiple candidate keys but only one primary key.
3. Normalization:
   • The process of efficiently organizing data to eliminate redundancy and ensure data integrity. It involves dividing larger tables into smaller, related tables and linking them using foreign keys.
   • Various Normal Forms (1NF, 2NF, 3NF, etc.) serve as guidelines to ensure the relational database is free from anomalies.
4. Relationships:
   • One-to-One (1:1): One record in Table A relates to one record in Table B.
   • One-to-Many (1:N): One record in Table A relates to multiple records in Table B.
   • Many-to-Many (M:N): Multiple records in Table A relate to multiple records in Table B. Implemented using a junction or bridge table.
5. Referential Integrity:
   • A set of rules ensuring relationships between records in related tables remain consistent. It guarantees that the relationship between primary and foreign keys is maintained.
6. Entity-Relationship (ER) Diagram:
   • A visual representation of the structure of a database. It illustrates entities, their attributes, and the relationships between entities.
7. Database Schema:
   • The blueprint of the database that outlines the logical design, tables, relationships, constraints, and other elements.
8. Data Integrity:
   • Ensures the accuracy and reliability of data. Types include domain integrity (valid data types and values), entity integrity (unique records), and referential integrity (consistent relationships).
9. Data Redundancy:
   • Occurs when the same piece of data is stored in more than one place. Proper normalization reduces redundancy.
10. Indexes:
    • Data structures enhancing data retrieval speeds. While they increase performance for READ operations, they may slow down WRITE operations due to the need to update the index.
11. Database Constraints:
    • Rules enforced on data columns in a table. Common constraints include `NOT NULL`, `UNIQUE`, `CHECK`, and `DEFAULT`.
12. Data Security:
    • Mechanisms to protect data from unauthorized access, including user permissions, encryption, and backup protocols.

Implementing these primary concepts in relational database design ensures that databases are structured logically, maintain data integrity, and support efficient and scalable operations. Proper database design is paramount for any system that relies on structured data storage.

A database consists of the physical files you set up on a computer when installing the database software. On the other hand, a database model is more of a concept than a physical object and is used to create the tables in your database. By definition, a database is a structured object and in the modern world it exists on a computer system. The structured object consists of data and metadata, with metadata being the structured part. Data in a database is the actual stored descriptive information, such as all the names and addresses of your customers. Metadata describes the structure applied by the database to the customer data. In other words, the metadata is the customer table definition. The customer table definition contains the fields for the names and addresses, the lengths of each of those fields, and datatypes. A datatype restricts values in fields, such as allowing only a date, or a number. Metadata applies structure and organization to raw data. A database is often represented graphically by a cylindrical disk where the database contains both metadata and raw data (See Figure 1.1 below). The physical implementation of the database itself is stored and executed on a database server computer.

In this course, I will discuss relational database design with the aid of two kinds of graphical displays,

1. a gallery consisting of a series of images and
2. detailed diagrams.

You will complete a course project that involves creating a database design diagram appropriate to the needs of Stories on CD, Inc. This is a fictional company that sells books on CD via mail order. You will complete the project incrementally via a series of exercises that provide opportunities to apply what you have learned to a real-world situation. While the company is small and its needs are relatively simple, the principles you apply to analyzing its requirements and designing a database to meet them are the same as those that database designers apply to much larger projects involving multi-national corporations.

• Database Series
  Data Modeling and Relational Database Design is the first of two courses in the Relational Database Design Fundamentals series.The second course in the series discusses Data Analysis and Normalization The next lesson outlines the prerequisites for this course.