Database Fundamentals: Keys, Models, and Design Communication

what is a data field

(also called an attribute) is one specific piece of information stored about an entity, represented as a column in a database table. Customer name Order date Product price Account balance

Characteristics of primary key

1️⃣ Unique 2️⃣ Not Null 3️⃣ Identifies exactly one record 4️⃣ Stable (should not change) 5️⃣ Minimal 6️⃣ One per table 7️⃣ Enforced by the DBMS

Crow's foot notation

It's a visual system used in Entity-Relationship Diagrams (ERDs) to show: Which table is the parent and which is the child

Characteristics of foreign keys

Links tables together Matches a primary key in another table Enforces referential integrity Can contain duplicate values Can allow NULL values Does NOT have to be unique Data type must match the referenced key Can reference the same table (self-referencing)

Logical view (structure / design)

This view focuses on how the table is defined, not the actual data inside it. It shows: Column names (attributes) Data types (text, number, date, etc.) Primary key Foreign keys Rules/constraints

Physical view (data / instance)

This view focuses on the actual data stored in the table. It shows:

Lists

flat table repeating data no built in relationship

What is a data model?

is a blueprint that defines how data is structured, stored, and related in a database.

What is a database table(entity)?

is a collection of related data about one specific thing, organized into rows and columns. Customer Order Product Employee

What is a primary key?

is a field (or combination of fields) that uniquely identifies each record in a database table.

Database keys

is a field (or combination of fields) used to identify records and define relationships between tables.Primary Key (PK) Uniquely identifies each record in a table Cannot be NULL Cannot be duplicated One per table

What is a foreign key?

is a field in one table that references the primary key of another table to create and enforce a relationship between the tables.

What is a relational database?

is a way of storing data in tables that are connected to each other by relationships

How is the design of a database communicated?

is communicated using a diagram, data models most commonly an Entity-Relationship Diagram (ERD).

What is the purpose of a database?

is to store, organize, protect, and retrieve data efficiently so people and systems can actually use it without chaos.

databases

multiple related tables uses primary and forgein keys referential integrity

Referential integrity

s a database rule that ensures relationships between tables stay valid. You can't have a child record that points to a parent record that doesn't exist.

child table

Contains foreign key from parent table

What is included in a data model?

Entities,Attributes,Relationships,. Primary Keys (PK) Foreign Keys (FK)

Which attribute should be selected as a primary key?

you pick the attribute that best uniquely, reliably, and permanently identifies each record. Good choices CustomerID OrderID EmployeeID

Which attribute(s) should be added as a foreign key(s)?

Add the PRIMARY KEY of the parent table into the CHILD table as a foreign key.

What can be stored in a database?

People & organizations Business & accounting data Products & inventory Orders & operations Time-based data

parent table

contains a primary key