CS377: Database Design - Relational Data Model
Activity Goals
The goals of this activity are:- To express database table relationships through mathematical abstraction
- To describe the advantages and disadvantages of different database engines
Supplemental Reading
Feel free to visit these resources for supplemental background reading material.The Activity
Directions
Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity, and compare with your group to prepare for our whole-class discussion. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.Model 1: Domains and Relations
\(FirstName \times LastName \times Age = \{(firstname, lastname, age) : firstname \in \{a-zA-Z\}+, lastname \in \{a-zA-Z\}+, age \in \mathbb{Z}^{+}\}\)
Questions
- If the domain of the
Agefield is all positive numbersAge > 0, what is the domain ofFirstName? - Translate the cartesian product formula into your own words describing the nature of the table it defines.
- This table relation has degree 3. What do you think that means?
- Is it ever appropriate to have the same row in a table more than once? What is the alternative if such a situation is needed?
- Suppose you wish to track the history and provenance of a record. For example, if someone changes their name, a record of both names and the dates during which each name was valid would be included. What tables and fields might you add to do this?
Relational Database Model
The relational database model is a popular and widely used approach to structuring and organizing data. It consists of tables that store data in rows and columns, where each row represents a record or tuple, and each column represents a specific attribute or field. The relationships between tables are established using primary and foreign keys. Relational databases provide a structured way to store, retrieve, and manage large amounts of data. This report serves as course notes for understanding the relational database model and database programming using Python.
Key Concepts
The relational database model is based on the principles of logic and set theory. It organizes data into tables, where each table represents an entity, and each row represents a specific instance of that entity. The relationships between entities are established through keys, such as primary and foreign keys.
- Tables: A table represents a specific entity and consists of columns (attributes) and rows (records).
- Keys: Keys are used to uniquely identify records in a table. Primary keys are unique identifiers for a table, while foreign keys establish relationships with other tables.
- Relationships: Relationships define how tables are linked together using keys. Common relationships include one-to-one, one-to-many, and many-to-many.
Database Programming with Python
To manipulate and interact with relational databases programmatically in Python, we can utilize various libraries and APIs. Here are some key resources that provide useful information on this topic:
-
PyMySQL Python Library: PyMySQL is a pure-Python MySQL client library that provides a straightforward way to connect to and interact with MySQL databases. It offers a comprehensive set of functions and methods for executing SQL statements, managing transactions, and handling errors. More information can be found in the PyMySQL documentation.
-
Sqlite3 Python Library: Sqlite3 is a built-in Python library that provides an interface for working with SQLite databases. SQLite is a serverless, file-based database engine that allows for easy implementation and usage of relational databases. The Sqlite3 library offers functions for creating, querying, and modifying SQLite databases. Detailed documentation is available in the Python documentation.
-
PostgreSQL Python Library: PostgreSQL is a powerful and feature-rich open-source relational database system. There are multiple Python libraries available for interacting with PostgreSQL databases. One of the most commonly used libraries is psycopg2, which provides a simple and efficient way to connect to and work with PostgreSQL databases in Python. A tutorial on using psycopg2 can be found at TutorialsPoint.
PyMySQL Python Library
PyMySQL is a pure Python client library for MySQL databases. It provides a convenient way to interact with MySQL databases using Python.
Example:
import pymysql
# Connect to the MySQL database
connection = pymysql.connect(host='localhost', user='username', password='password', database='dbname')
# Execute a SQL query
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
# Fetch all records
results = cursor.fetchall()
# Close the connection
connection.close()
Sqlite3 Python Library
Sqlite3 is a built-in Python library that provides a lightweight and self-contained SQLite database module. It allows Python programs to access the SQLite database files.
Example:
import sqlite3
# Connect or create a SQLite database
connection = sqlite3.connect('database.db')
# Create a cursor object
cursor = connection.cursor()
# Execute a SQL query
cursor.execute("SELECT * FROM users")
# Fetch all records
results = cursor.fetchall()
# Close the connection
connection.close()
PostgreSQL Python Library
psycopg2 is a PostgreSQL adapter for the Python programming language. It allows Python programs to interact with PostgreSQL databases.
Example:
import psycopg2
# Connect to the PostgreSQL database
connection = psycopg2.connect(host='localhost', user='username', password='password', database='dbname')
# Create a cursor object
cursor = connection.cursor()
# Execute a SQL query
cursor.execute("SELECT * FROM users")
# Fetch all records
results = cursor.fetchall()
# Close the connection
connection.close()