Relational Databases – Key Ideas and Examples (Slides 1–14)

Introduction to Relational Databases

Overview of the topic as presented on Pages 1–2.
- Why do we have databases (DBs)? To organize, store, and retrieve data efficiently.
- What is a DBMS (Database Management System)? Software that manages databases and provides an interface for querying, updating, and administering data.
- How do we arrange data into a DB? Organization into relations (tables) with defined schemas, keys, and constraints to enable structured access.

Why Databases? What is a DBMS? How do we arrange data into a DB?

Core questions introduced for framing the study of relational databases.
Emphasis on data organization, integrity, and the ability to link data across related lists via keys.

Relational Database Terminology

Relation and schema concepts (as illustrated by the Employee example):
- A relation is a table consisting of attributes (columns) and tuples (rows).
- Example attributes visible in the slides include: Soc Sec, Emp Name, Title, Office, Dept Code, Dept Name, Dept Office, etc.
- Example tuples shown include individuals like John Smith, Jane Doe, Smitty, Foncey, Goody, with associated attributes.
Core data concepts shown in a typical Employee-like relation:
- Soc Sec (Social Security Number) as an identifier for an employee.
- Emp Name (Employee Name).
- Title.
- Office (office location or number).
- Dept Code and Dept Name (department-related attributes).
Note on data snapshots: the slides present a compact, partially denormalized view to illustrate how data can be spread across columns and rows, highlighting the need for proper keys and relationships.

An issue… Student data and data redundancy

Example illustrating redundancy and update anomalies in a non-relational view:
- A Student relation with attributes: First Name, Last Name, Major, Began, etc.
- Multiple rows for the same student (John Smith) across different Began years: 2011, 2018, 2019, etc.
- A row for a 2022 CIS Graduate demonstrates a possible final state or graduation-tracking row.
Implications:
- Redundancy: the same student’s name and major appear in multiple rows.
- Anomalies during updates, inserts, and deletes become more likely when data is not properly normalized.

An issue… Company Vehicles

Example showing a Vehicles relation with attributes like Make, Model, Color, Purchased (year):
- Ford F150 Grey 2017
- Ford F150 Red 2017
- Ford F150 Grey 2018
- Ford F150 Grey 2018
- The summary row may list a final purchasing record (2017) for reference.
Implications:
- Redundancy and potential inconsistencies in vehicle records if color or purchase year changes are not propagated across all related rows.
- Illustrates the need for a stable key and normalization to avoid duplicate/ambiguous vehicle records.

Selecting a Key (Employee, Department, Course examples)

Concept: a key is a minimal set of attributes that uniquely identifies a tuple in a relation.
Page 6 shows an exercise: choosing a key for an Employee-like relation and a Department-like view, including candidate keys and how they map to tuples.
- For the Employee portion, Soc Sec is shown as a candidate key in the example (distinct, uniquely identifying an employee).
- The Department portion shows Dept Code as a candidate key (with Dept Office and Dept Name provided alongside).
Page 6 also lists Dept Office values (e.g., 2, 6, 9) and Dept Name values (Marketing, Accounting, Info Systems), illustrating how keys tie to other attributes.
Page 7 extends the key-selection exercise to a Course relation:
- Attributes include Course Discipline, Number, and Credit Hrs.
- Example rows include entries like ENGL 101, 3; MATH 105, etc.; CIS 250, ACCT 205, HIST 102.
- The Number field appears to serve as a primary key for the Course relation (e.g., 3333 as a key candidate).

Employee and Course layout improvements (Is this better?)

Page 8 shows an initial Employee-like relation with many attributes listed side-by-side, highlighting potential redundancy.
Page 9 shows a neater, more compact layout:
- Employee relation with columns such as Soc Sec, Emp Name, Title, Office, Dept Code, Dept Office, Dept Name.
- This cleaner layout helps identify the relationships to the Department entity and reduces visible redundancy.
Practical takeaway:
- Clearer schema organization supports easier application of normalization steps and easier querying.

Relationships in the Relational Model

Core idea: relationships are established via common fields that connect tuples across relations.
Foreign Key (FK):
- An attribute (or set of attributes) in one relation that references the primary key of another relation.
- Represents a relationship between two relations.
- Usually drawn as a directed arc in an Entity-Relationship (ER) view.
- The FK value must either exist in the referenced table or be NULL (i.e., the relationship may be optional).
Consequences:
- Enforces referential integrity between related tables.
- Enables joins to reconstruct related information across relations.

Foreign Key Example (High-level conceptual view)

Two relations are shown to illustrate FK usage:
- EmployeeRelation (OfAnotherCompany) with attributes such as Name, SSN, Bdate, Address, Salary, SuperSSN, Dno.
- DepartmentRelation (OfAnotherCompany) with attributes such as Dname, Dnumber, MgrSSN, MgrStartDate.
Takeaway:
- Dno in Employee references the Department relation (i.e., which department the employee belongs to).
- SuperSSN in Employee can reference another Employee within the same Employee relation (self-referential FK).
- MgrSSN in Department references the Employee relation (the manager is an employee).

More detailed Foreign Key examples (data-oriented illustration)

Employee relation (sample schema):
- Name, SSN, Bdate, Address, Salary, SuperSSN, Dno
Department relation (sample schema):
- Dname, Dnumber, MgrSSN, MgrStartDate
Example rows (illustrative):
- Maria, $111111111$ , 1/1/1972, Thibodaux, 99000, MgrSSN?, Dno?
- Nushi, $222222222$ , 2/2/1988, Houma, 71000, MgrSSN?, Dno?
- Yan, $333333333$ , 3/3/1979, Gray, 95000, MgrSSN?, Dno?
- Jose, $444444444$ , 4/4/1991, Chackbay, 83000, MgrSSN?, Dno?
- Wei, $555555555$ , 5/5/1987, Baton Rouge, 60000, MgrSSN?, Dno?
- Ahmed, $666666666$ , 6/6/1994, Thibodaux, 65000, MgrSSN?, Dno?
Department relation (sample schema):
- Sales, Dnumber 1, MgrSSN ?, MgrStartDate $8/6/2019$
- Manufacturing, Dnumber 2, MgrSSN ?, MgrStartDate $1/15/2012$
Core lesson:
- These examples demonstrate how foreign keys connect Employee to Department and how manager information is tied back to Employee records (self-references and cross-references).
- They also illustrate how referential integrity is maintained across multiple relations.

DB Example and practical context

Page 14 references a DB Example named Premier Products DB.
It is framed as Understanding Relational Data Problems, suggesting:
- Practice with a realistic data problem to apply relational concepts.
- Use of keys, foreign keys, and relation definitions to reason about data integrity.

Key takeaways and connections

Relational database core ideas:
- Data is organized into relations with clearly defined attributes and keys.
- A primary key uniquely identifies each tuple within a relation.
- Candidate keys are minimal sets of attributes that can uniquely identify a tuple; one or more become the primary key.
- Foreign keys create links between relations and enforce referential integrity.
- Real-world data often exhibits redundancy and anomalies when not properly normalized; normalization seeks to reduce redundancy and improve data integrity.
Practical relevance:
- Correct use of keys and foreign keys enables reliable joins, updates, and deletions across related data.
- Clear schema design supports scalability and maintainability in database systems.
Ethical and practical implications (implicit):
- Data integrity and accuracy are critical; improper design can lead to inconsistencies and erroneous conclusions.
- Proper data modeling supports auditability and accountability in information systems.

Summary of key terms and examples to remember

Relation: a table of tuples with a schema of attributes.
Attribute: a column in a relation (e.g., Soc Sec, Emp Name, Dept Code).
Tuple: a row in a relation.
Primary key: a unique identifier for a tuple in a relation (e.g., Soc Sec in the Employee example).
Foreign key: an attribute in one relation that references the primary key of another relation (e.g., Dno in Employee -> Department).
Self-referential foreign key: a foreign key that references the same relation (e.g., SuperSSN in Employee referencing Employee).
Normalization (implied): process of organizing data to reduce redundancy and improve data integrity, illustrated by the progression from the denormalized Employee view to the cleaner, more normalized layout.
Examples used in slides:
- Employee relation with keys such as Soc Sec; Department relation with Dept Code; Course relation with Number; Vehicle example illustrating attribute redundancy.
- Data examples include individuals like John Smith, Jane Doe, Smitty, Foncey, Goody, and vehicles like Ford F150 across colors and years.
- SSNs used in examples include $111223333, 444556666, 123121234, 436110000, $ 435001111 $, etc., to illustrate unique identifiers.</li></ul></li></ul><h3 id="6a01ae7a-525e-4139-b389-8acd65449d0f" data-toc-id="6a01ae7a-525e-4139-b389-8acd65449d0f" collapsed="false" seolevelmigrated="true">Quick formulas and references (LaTeX)</h3><ul><li>Foreign Key constraint: for relations R and S, with FK in R referencing PK in S:<ul><li>FKR → PKS</li></ul></li><li>Nullability rule for FK: values in FKR ∈ {existing PKS values} ∪ {NULL}</li><li>Example primary/foreign key relationships mentioned in the slides:<ul><li>Dno in Employee references Department Dnumber:$ Dno
 ightarrow Dnumber $</li><li>SuperSSN in Employee references Employee (self-reference):$ SuperSSN
 ightarrow SSN $</li><li>MgrSSN in Department references Employee:$ MgrSSN
 ightarrow SSN$$