Teradata basic mod03

Module 3 Relational Database Concepts
Relational Database Concepts
After completing this module, you should be able to: • • • • Define the terms associated with relational databases. Discuss the function of the Primary Key. Discuss the function of Foreign Keys. List the advantages of a relational database.
Foreign Key
EMPLOYEE (partial listing)
MANAGER EMPLOYEE EMPLOYEE DEPARTMENT NUMBER NUMBER NUMBER PK 1006 1008 1005 1004 1007 1003 FK 1019 1019 0801 1003 0801 FK 301 301 403 401 401 JOB LAST CODE NAME FK 312101 312102 431100 412101 411100 Stein Kanieski Ryan Johnson Villegas Trader John Carol Loretta Darlene Arnando James 861015 870201 861015 861015 870102 860731 631015 680517 650910 560423 470131 570619 3945000 3925000 4120000 4630000 5970000 4785000 FIRST NAME HIRE DATE BIRTH DATE SALARY AMOUNT
DEPARTMENT DEPARTMENT NUMBER NAME PK 501 301 302 403 402 401 201 marketing sales research and development product planning education software support customer support technical operations
MANAGER BUDGET EMPLOYEE AMOUNT NUMBER
80050000 46560000 22600000 93200000 30800000 98230000 29380000 FK 1017 1019 1016 1005 1011 1003 1025
•
• •
Missing (null) FK values may be allowed.
Column
EMPLOYEE MANAGER EMPLOYEE EMPLOYEE DEPARTMENT NUMBER NUMBER NUMBER 1006 1008 1005 1004 1007 1003 1019 1019 0801 1003 0801 301 301 403 401 401 JOB LAST CODE NAME 312101 312102 431100 412101 411100 Stein Kanieski Ryan Johnson Villegas Trader FIRST NAME John Carol Loretta Darlene Arnando James HIRE DATE 861015 870201 861015 861015 870102 860731 BIRTH DATE 631015 680517 650910 560423 470131 570619 SALARY AMOUNT 3945000 3925000 4120000 4630000 5970000 4785000
Primary Key
Primary Key (PK) values uniquely identify each row in a table.
EMPLOYEE
MANAGER EMPLOYEE EMPLOYEE DEPARTMENT NUMBER NUMBER NUMBER PK 1006 1008 1005 1004 1007 1003 1019 1019 0801 1003 0801 301 301 403 401 401 312101 312102 431100 412101 411100 Stein Kanieski Ryan Johnson Villegas Trader John Carol Loretta Darlene Arnando James 861015 870201 861015 861015 870102 860731 631015 680517 650910 560423 470131 570619 3945000 3925000 4120000 4630000 5970000 4785000 JOB LAST CODE NAME FIRST NAME HIRE DATE BIRTH DATE SALARY AMOUNT
Relational Advantages
Advantages of a Relational Database compared to other database methodologies include: • More flexible than other types • Allowing businesses to quickly respond to changing conditions • Being data-driven vs. application driven • Modeling the business, not the processes • Makes applications easier to build because the data does more of the work • Being easy to understand • Supporting trend toward end-user computing • DBA’s and Users don’t need to know the access path • Solidly founded in Set Theory
1. Name the department in which James Trader works. 2. Who manages the Education Department? 3. Identify by name an employee who works for James Trader. 4. James Trader manages which department?
What Is a Teradata Database?
Database—A collection of permanently stored data that is:
> > > >
Logically related—data relates to other data. Shared—many users may access data. Protected—access to data is controlled. Managed—data has integrity and value.
Logical/Relational Modeling
The Logical Model: • Should be designed without regard to usage • accommodates a wide variety of front end tools • allows the database to be created more quickly
Row
The employee table has: > Nine columns of data > Six rows of data—one per employee > No prescribed order for the rows of the table > Only one row format for the entire table > Missing data values represented by nulls
IBM/MVS
Teradata DATABASE
UNIX
Win98
WinNT
W2K
WinXP
Relational Databases
• • A Relational Database consists of a set of logically related tables. A table is a two dimensional representation of data consisting of rows and columns.
• Should be the same regardless of data volume
Teradata Supports Fully Normalized Logical Models: • ability to perform 64 table joins • ability to perform large aggregations during a query
Primary Key Rules
> > > > > >
A Primary Key is required for every table. Only one Primary Key is allowed in a table. Primary Keys may consist of one or more columns. Primary Keys cannot have duplicate values. Primary Keys cannot be null. Primary Keys are considered “non-changing” values.