Lesson 17

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.

Question: What are relational constructs within the context of database design?
Relational constructs are fundamental building blocks of a relational database, and describe the relationships between tables and the rules that govern these relationships. Relational constructs are used in database design to ensure that data is stored and retrieved in a consistent and accurate manner.
Here are some of the key relational constructs used in database design:

1. Tables: Tables are the basic building blocks of a relational database, and contain data organized into rows and columns. Each table represents a distinct set of related data.
2. Primary Keys: A primary key is a unique identifier for a row in a table. It is used to ensure that each row in a table can be uniquely identified and to enforce referential integrity.
3. Foreign Keys: A foreign key is a field or set of fields in one table that refers to the primary key of another table. This is used to link tables together and enforce referential integrity.
4. Relationships: Relationships describe the connections between tables in a database. There are three types of relationships: one-to-one, one-to-many, and many-to-many.
5. Normalization: Normalization is the process of organizing data in a database to minimize redundancy and ensure data integrity. This involves breaking tables down into smaller, more focused tables and creating relationships between them.
6. Constraints: Constraints are rules that are applied to data to ensure that it meets certain requirements. This includes rules around data types, null values, and uniqueness.

Overall, relational constructs are used in database design to ensure that data is organized and maintained in a consistent and accurate manner, and that relationships between tables are properly established and enforced.

Now that you have completed the lessons in this module, you should be able to:

1. List the steps in the database life cycle (DBLC)
2. Explain why an ER diagram is translated into relational constructs
3. Describe the characteristics of tables
4. List the rules for table rows and columns
5. Define data domains and identify the domains that support calculations
6. Differentiate between nulls, blanks, and zeros
7. Define and identify key columns and descriptor columns
8. Define primary keys
9. Define concatenated primary keys
10. Describe all-key relations
11. Identify foreign keys
12. Define the concept of referential integrity

This module introduced you to the following terms:

1. 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.
2. base table: A table stored in a database.
3. business objects: Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.).
4. concatenated primary key: A primary key made up of more than one field.
5. 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.
6. descriptor column: A descriptor column contains a non-key attribute of an entity.
7. domain: Determines the type of data values that are permitted for that attribute.
8. Entity: A single stand-alone unit or a business object about which data are stored in a database; usually synonymous with a database table.
9. 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.
10. field: The smallest structure in a table; contains data describing the subject of a table.
11. foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.
12. integer division: A mathematical operation that returns the integer portion of a division and discards the remainder.
13. key column: A key column contains information that distinguishes that person, place, or thing from every other item represented in the table.
14. normalize: To break up large tables into smaller, more efficient tables without losing any information.
15. null value: An unknown value in a column or field.
16. 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.
17. primary key: A field (or combination of fields) that uniquely identifies a record in a table.
18. query: To extract data from a database; (n.) a set of SQL statements for extracting particular data from a database.
19. record: A particular instance of the subject of a table.
20. 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.
21. 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.
22. Relational notation: Relations are expressed in a shorthand called relational notation, a textual interpretation of the ER diagram.
23. relation: Another word for table. Relations represent entities in database design.
24. relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.
25. set theory: A branch of mathematics that studies how groups of objects interact.
26. Structured Query Language (SQL): The standard language used by all relational databases, including Microsoft SQL Server 2012.
27. Table: A collection of data arranged in rows and columns. A table is the largest structure in a relational database.
28. tuple: In the lexicon of relational database design another word for rows or records.
29. user views: Saved queries created with SQL. User views specify which users are permitted access to what data in a database.
30. 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.

Before moving to the next module, click the Quiz link below to check your knowledge of relational constructs.
Relational Constructs - Quiz