Computer Science 380:

Principles of Database Systems

Chapter 2

Gregory M. Kapfhammer

Relational Databases

Collection of tables, each with a unique name

Review the examples in Figures 2.1 through 2.4

What observations can you make about these tables?

Why do scientists uses relational databases?

Standardized Terms

These words have the same meaning!

Relation and table

Tuple and row

Attribute and column

Accessing Data

How can we distinguish one tuple from another?

Can the values of an attribute uniquely identify a tuple?

We will identify keys for our database tables!

Database Keys

What is a superkey?

Is this an example of a superkey?

Wait! A superkey may have extraneous attributes!

Why is this an important issue to consider?

We need to identify a minimal superkey!

Database Keys

What is a candidate key?

But, many candidate keys are possible!

So, we have to pick one of them.

What is a primary key?

Often, primary keys are never, or rarely modified. Why?

Good and bad examples of primary keys.

Let's Try It!

Create a relational schema

INSERT data into the database

Try to violate the constraints of the schema

What did your experiments reveal?

SQL Code

   CREATE TABLE Writers (
       id                INTEGER NOT NULL PRIMARY KEY,
       first_name        VARCHAR(15) NOT NULL,
       middle_name       VARCHAR(15),
       last_name         VARCHAR(15) NOT NULL,
       birth_date        VARCHAR(10) NOT NULL,
       death_date        VARCHAR(10),
       country_of_origin VARCHAR(20) NOT NULL
);
                    
                    

Run the sqlite3 command and create this schema

What output does the .tables command produce?

What output does the .schema command produce?

SQL Code

   CREATE TABLE Writers (
       id                INTEGER NOT NULL PRIMARY KEY,
       first_name        VARCHAR(15) NOT NULL,
       middle_name       VARCHAR(15),
       last_name         VARCHAR(15) NOT NULL,
       birth_date        VARCHAR(10) NOT NULL,
       death_date        VARCHAR(10),
       country_of_origin VARCHAR(20) NOT NULL
);
                    
                    

Run this command: INSERT into Writers VALUES(1, 'Ezra', 'Weston Loomis', 'Pound', '30/10/1885', '1/11/1972', 'United States of America');

Further Explorations

Data that violates the primary key constraint

Data that adheres to the primary key constraint

Try multiple INSERT statements

Try to run UPDATE and DELETE statements

What have you learned about relational databases?

Do you have any questions about using sqlite3?

Database Keys

What is a foreign key?

Creating a referencing relationship in the tables

Using both primary keys and foreign keys

Why is it useful to define the schema this way?

Any questions about key definition in relational schemas?

A Complete Example

Use git pull in the course repository

Find the Writers.sql file

Use the .read command to input this schema

Review the use of .schema and .tables

How is this schema different than the previous one?

Exploring the Database

Use INSERT statements to add data to all of the tables

Try to both violate and satisfy the schema's constraints

How does SQLite handles keys? Why?

Can you save the state of the database to a file?

Schema Diagrams

Informal and formal

The benefits of visualization

Showing the primary and foreign keys

Many cool tools for this: SQLFairy and SchemaSpy!

Join Operators

Natural join

Cartesian product

Following the schema's keys!

Efficiency concerns for performing joins

Once again, what is the purpose of a join operator?