Database Tables The building blocks of the relational

Database Tables The building blocks of the relational database. DISCOVER! Texas State Technical College

Database Tables Table Purpose The table is the basic structure of all relational databases. One table represents one type of object in the real world domain. The table’s main purposes are: • Define the object and all its attributes • Store all the instances of that object type DISCOVER! Texas State Technical College

Database Tables Table Purpose Illustration Generic Object Type (defined by the table) • Engine Instances of the Engine Generic Type (stored in the table) • Ford 4 cylinder • Ford V-6 • Ford V-8 • Dodge V-12 DISCOVER! Texas State Technical College

Database Tables Table Composition Each table has two primary components. • Table Definition • Attribute (field) • Attribute type (datatype) • Relationship (key) • Table Data • Instances (records) DISCOVER! Texas State Technical College

Database Tables Table Definition Defines the characteristics of one generic type of object. The definition for a table is a list of pairs {attribute name, attribute type} Any attribute/characteristic listed must be common to all objects of the object type. DISCOVER! Texas State Technical College

Database Tables Table Definition Example [ ENGINE ] Think of all the engines that exist or that you wish to model. The engine table describes a generic engine. It must define all the attributes that are common for all automotive engines. Below are only a few of the common characteristics/attributes in both a graphical and text based representation. Name Volume Cylinders Max Torque Max RPM Text Decimal Integer [ {Name, Text}, {Volume, Decimal}, {Cylinders, Integer}, {Max Torque, Integer}, {Max RPM, Integer} ] DISCOVER! Texas State Technical College

Database Tables Table Data Stores all specific instances of the object type. Each instance or record represents one existing or actual object of the object type specified by the table. Each instance or record is a list of pairs {attribute name, attribute value} DISCOVER! Texas State Technical College

Database Tables Table Data Example [ ENGINE ] Each instance of the object type is referred to as a record. Each record is visualized as below in two different ways, as graphical and as text. There are two records, one describing a Ford 4 cylinder engine and the other a Ford V-6 engine. RECORD #1 Name Volume Cylinders Max Torque Max RPM Ford 4 Cylinder 2. 6 4 800 5200 [ {Name, Ford 4 Cylinder}, {Volume, 2. 6}, {Cylinders, 4}, {Max Torque, 800}, {Max RPM, 5200} ] RECORD #2 Name Volume Cylinders Max Torque Max RPM Ford V-6 3. 6 6 1400 7200 [ {Name, Ford V-6}, {Volume, 3. 6}, {Cylinders, 6}, {Max Torque, 1400}, {Max RPM, 7200} ] DISCOVER! Texas State Technical College

Database Tables Table Composition [ ENGINE ] When we combine the two components, we get something most of us are a little more used to seeing. It is a lot like an Excel spreadsheet, except in a table all the cells or fields in a row are related because they describe one actual object. DEFINITION DATA Name Volume Cylinders Max Torque Max RPM Text Decimal Integer Ford V-6 3. 6 litre 6 1400 7200 Ford 4 Cylinder 2. 6 litre 4 800 5200 DISCOVER! Texas State Technical College

Database Tables In Summary… • The two basic components of tables are the table definition and the table data. • Relational databases define object types and store occurrences of that object type through the use of tables. • Tables are composed of fields and records. • Fields are used to define the attributes of an object type. • Records are used to store individual occurrences or instances of an object type. DISCOVER! Texas State Technical College