Teradata Certification Series-3. The concept of relational database

The learning objectives of this lesson

• Define the terminology associated with a relational database
• Discuss the function of the primary key
• Discuss the functions of foreign keys
• List the benefits of a relational database
• Describe the difference between a star schema and a third-paradigm data model

What is a database?
A database is a collection of persistent data that is used by an application to represent:

• Logical Association
• Shares
• be protected
• Can be managed

The database includes logically associated data, which means that the database is designed for special purposes. The database supports multi-user sharing. One feature is that many users often use it for different purposes. The database is protected and managed by Access control permissions to maintain its integrity and value.

Logical/Relational Model

Logical model

• The design should guarantee the use of the way independence
• Adaptable to a wide range of front end tools
• Allow databases to be created faster
• Data volume independence should be ensured
• Organize data to express real-world forms (relational) data
• Contains all data definitions within the scope of enterprise application use
• Generalized-logical model is a template for physical implementation on any RDBMS platform

Standardization

• The process of weakening complex data structures into simple, stable, single structures
• Remove redundant attributes, keys, and relationships from the conceptual data model

relational database

• Use table to represent a relationship
• Use row to represent a tuple
• Use column to represent a property

Third Paradigm vs Star architecture

A model can be refined in paradigm, and a paradigm model includes:

• Entity-a row of data in a data table
• Properties-Columns
• Relationships-Between data tables

First paradigm: Each attribute of an entity instance can have only one value, and duplicate groups are not allowed within the entity

Second paradigm: must conform to the first paradigm, the non-primary key attribute in an entity all depends on the primary key attribute, not its subset

The third paradigm: must conform to the second paradigm, within the same entity, without a non-key attribute dependent on another non-key attribute

TD database can support any data model that is processed by SQL; One advantage of the paradigm data model is the ability to support unknown queries.

Star-Schema:

Sometimes also known as the Star Connection architecture, is one of the simplest data warehouse architectures, the star schema consists of a few tables (perhaps only one) associated with any dimension of the data table. Star architecture is a special form of snowflake architecture.

Star Architecture Features: Fact, dimensions, snowflakes

Features of the Star Architecture model:

• tends to have fewer entities
• Advocating a higher level of anti-normalization

Primary key

Primary KEY rules:

The primary key is a single row within the indicated table. A primary key is required because each row in the table must be uniquely marked.

Duplicate values are not allowed. Because the primary key is used for labeling, the primary key cannot be empty.

The value of the primary key cannot be changed. Because there may be foreign keys between the other entities.

The primary key can include more than one column. In fact, there is no limit to the number of columns included.

Select a primary key

A column in a row is called a property of that row. The designer of the database can choose any attribute as the primary key, but according to the above rules, many attributes do not work as candidates for the primary key. For example, the last name is not suitable, it is likely that two people are a surname, even if we add a name, it is possible to have a duplicate name of the person, and some people will change their names.

Some designers recommend using a system-assigned integer sequence as the primary key, which guarantees uniqueness but also increases the spatial requirements of the database.

Summarize the characteristics of the primary key:

• Primary keys are required for each table
• A table can have only one primary key
• A primary key may consist of one or more columns
• The primary key cannot have duplicate values
• Primary key cannot be null
• A primary key is a value that is thought to never change

FOREIGN key

The relationship between the foreign key indicators table

FOREIGN KEY rules:

• Foreign keys are optional
• A table may have more than one foreign key
• A foreign key may consist of more than one column
• Foreign keys allow duplicate values to occur
• Foreign keys allow null values
• The value of the foreign key may change
• The value of each foreign key must exist in a primary key

These terms are not unique to Teradata, they are relational database concepts in a broad sense.

Use relational databases to answer questions

A relational database is a collection of tables, stored in a single-installation relational database management system (RDBMS). The word "management system" indicates that this is a relational database that uses a range of software to provide additional support such as transactions, security, and reporting.

The connection of data appears in the matching of data values between tables, in relational database theory, this is called connection (join)

For example, the employee table and the Employee Department table have a column for the department ID, which allows the database to answer the question: What is the department name of the department where the employee works?

The advantages of the relationship type

• More flexible than other types
• Allows business logic to quickly accept conditional changes
• Data-driven vs application-driven
• Modular business, not process
• Makes it easier to build applications because data has done a lot of work
• A single copy of the data can serve a variety of purposes
• Support for end-user computing trends
• Easy to understand
• No need to know the access path
• Firmly established in the set theory

Finish this lesson, the progress is finished 14.9%, don't ask me how to calculate out.

Teradata Certification Series-3. The concept of relational database