Relational Constructs and Constraints Conclusion
In this module you learned about relational constructs (such as tables, rows, and columns) and how they are created.
You also learned the characteristics of tables, the rules for table columns and rows, and which data domains can be assigned to the columns.
After learning the difference between null values, blanks, and zeros, you learned how to identify candidate and primary keys for database tables.
Finally, you learned that enforcing referential integrity ensures that users will not inadvertently enter data that refers to non-existent records in other tables.
Module 2 Learning Objectives
Now that you have completed the lessons in this module, you should be able to:
- List the steps in the database life cycle (DBLC)
- Explain why an ER diagram is translated into relational constructs
- Describe the characteristics of tables
- List the rules for table rows and columns
- Define data domains and identify the domains that support calculations
- Differentiate between nulls, blanks, and zeros
- Define and identify key columns and descriptor columns
- Define primary keys
- Define concatenated primary keys
- Describe all-key relations
- Identify foreign keys
- Define the concept of referential integrity
This module introduced you to the following terms:
- attribute: A characteristic of an entity; data that identifies or describes an entity. Usually represented as a column in a table, attributes store data values.
- base table:A table stored in a database.
- business objects: Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.).
- concatenated primary key: A primary key made up of more than one field.
- data type: The category of data stored in a database. Each field in a database must have a data type assigned to it, such as Integer, Text, or Date/Time.
- descriptor column: A descriptor column contains a non-key attribute of an entity.
- domain: Determines the type of data values that are permitted for that attribute.
- Entity: A single stand-alone unit or a business object about which data are stored in a database; usually synonymous with a database table.
- entity-relationship (ER) diagram: A diagram used during the design phase of database development to illustrate the organization of and relationships between data during database design.
- field: The smallest structure in a table; contains data describing the subject of a table.
- foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.
- integer division: A mathematical operation that returns the integer portion of a division and discards the remainder.
- key column: A key column contains information that distinguishes that person, place, or thing from every other item represented in the table.
- normalize: To break up large tables into smaller, more efficient tables without losing any information.
- null value: An unknown value in a column or field.
- one-to-many relationship: In relational database design, a one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B,
there exists zero or one instance of entity A.
- primary key: A field (or combination of fields) that uniquely identifies a record in a table.
- query: To extract data from a database; (n.) a set of SQL statements for extracting particular data from a database.
- record: A particular instance of the subject of a table.
- Referential integrity: The means of maintaining the integrity of data between one or more tables that relate to each other, in other words,
that a column of data in a table has a null or matching value in a corresponding table. Referential integrity is usually enforced with foreign keys.
- Relational Database Management System (RDBMS): A software package that manages and provides access to a database.
These packages follow Codd's 12 rules of relational databases and normally use SQL to access data.
- Relational notation: Relations are expressed in a shorthand called relational notation, a textual interpretation of the ER diagram.
- relation: Another word for table. Relations represent entities in database design.
- relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.
- set theory: A branch of mathematics that studies how groups of objects interact.
- Structured Query Language (SQL): The standard language used by all relational databases, including Microsoft SQL Server 2012.
- Table: A collection of data arranged in rows and columns. A table is the largest structure in a relational database.
- tuple: In the lexicon of relational database design another word for rows or records.
- user views: Saved queries created with SQL. User views specify which users are permitted access to what data in a database.
- Virtual table: A table stored in the computer’s memory. Virtual tables themselves are not stored in the database; rather, the definition of the view is stored and given a name. Users call up that name,
and the view is created (from base tables) on the fly. When a user closes the view, the view disappears from memory and is recreated the next time its name is invoked.
In the next module, database normalization and first, second, and third normal forms will be discussed.
Relational Constructs - Quiz