Database Fundamentals: Relational Databases, Keys, and Data Models

What is a record?

A collection of information represented by a row within a two dimensional grid of a database table

Referential Integrity

A database concept that ensures the quality and consistency of relationships between tables

Advantages of using a database

Increased flexibility Increased scalability and performance Reduced information redundance Increased information integrity (quality) Increase information security

Two views of a database table

Model: An abstract representation of the structure. Two dimensional grid: Shown with records

Characteristics of foreign keys

Must match a primary key value in the parent table.

Which attribute should be selected as a primary key?

Must serve as the specific indicator for that record, and be unique

Database vs lists (spreadsheets)

The problem with lists is that multiple objects (can be) in the same row which creates modification problems

Parent table and child table

The table that gives its primary key is the parent table, the other table is the child table with the foreign key

How is the design of a database communicated?

Through data models

What is the purpose of a database?

To store data, to provide ab organizational structure for data, to provide a mechanism for querying, creating, modifying, and deleting data

Well Structured vs Ill Structured tables

Well: Use a rational model to store, organize, and manage large amounts of information effectively. Ill: (Lists) often store multiple types of objects in the same row, which leads to "modification problems"

Well-structured vs ill-structured data fields

Well: follows the principle of being the "smallest or most basic unit of information" stored about an object. Ill: Often combine multiple pieces of information or objects into a single space, leading to management difficulties

What is a primary key?

a data field that unique identifies each row in a table

What is a data model?

graphical, logical structures that detail their relationships among data fields

Database Keys

primary key and foreign key

What is a foreign key?

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

Crow's Foot Notation

A representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship.

What can be stored in a database?

Customers, products, and buildings

What is a data field(attribute)?

the smallest or most basic unit of information that is stored about an object

Characteristics of primary key

- Uniquely identifies each row in a table. - Value is used to create relationships between tables

What is included in a data model?

-Tables -Data Fields/Attributes -Keys (primary and foreign) -Relationships

Which attributes should be added as the foreign key(s)?

Same as the primary key in the other table

What is a database table?(entity)

Stores information about an object in fields

What is a relational database?

Stores, organizes, and manages large amounts of information in a single software application

How to determine parent and child tables?

Table that gives its primary key to the other (Parent) and it has a perpendicular line on the outer edge of its end of the line. The other table is the child table and it has a crows foot on the outer edge of its end of the line

How to read/understand data models?

Tables, two different views of a table, model or two dimensional grid