Relational Model Notes
History of Data Management
Late 1960s: Companies began using computers for data management due to their cost-effectiveness.
Early Data Management: Files were used to store and process data.
Example: A file containing data of patients in a hospital.
Strategies for File Management
Flat Files: All files stored at the same level.
Hierarchical: Files stored in a tree structure for navigation.
Network: Similar to hierarchical but using a general graph instead of a tree.
Drawbacks of Early File Systems
File Content Changes: Modifications required changes in all programs reading the file.
Example: Switching first and last name columns.
Navigation: Mandatory navigation through the file system to access data.
Example: Accessing a specific bill requires traversing different nodes.
File Structure Changes: Modifications required changes in all programs performing navigations.
Example: Bills accessed from patients; changes to bill access path necessitate program updates.
Edgar F. Codd and the Relational Model
Edgar F. Codd (1923 – 2003): Devised the relational model to address file system problems.
1970: Proposed the relational model in "A Relational Model of Data for Large Shared Data Banks".
IBM Almaden: Where Codd worked at the time of the proposal.
1981: Received the Turing Award.
The Relational Model
Consists of "tables" called relations, comprising fixed‐length tuples.
Each relation represents a different type of entity.
Keys: Uniquely identify a tuple in a relation, enabling references to different tuples.
Example:
Patient Relation: Attributes include SSN, First Name, Middle Name, Last Name.
SSN is the key for the patient relation.
Visit Relation: Attributes include SSN, Scheduled, Weight.
The SSN allows reference to each patient in the Visit relation.
Initial Reception of the Relational Model
Codd’s paper was initially rejected.
Reviewer's Comments
Doubt about the model's ability to represent complex, practical scenarios.
Concern that realistic models would require numerous interconnected tables, making it impractical compared to formatted files.
Recommendation for rejection.
Accurate Criticisms
Lack of real-world examples and evaluation in the paper.
Absence of implementation due to limited computing power at the time.
Main Drawback: Complex queries require potentially large number of joins.
Join Operation
Joining two relations by one or more attributes to produce a single relation.
*Example:
*Joining the Patient and Visite relations using SSN.
Popularity of the Relational Model
Despite initial rejection, the relational model became popular.
Evidenced by the numerous commercial relational databases:
Oracle
MySQL
MS SQL Server
PostgreSQL
IBM DB2
MS Access
New Data Management Needs
Companies like Facebook and Google found relational databases unsuitable due to the large number of joins required.
NoSQL databases: Aim to avoid the relational model due to join-related problems.
Claim that relational databases focus on structured data, while there is a growing need for storing semi‐structured data like documents.
Conceptual Model
Notations
Different ways to represent the same concepts.
Diagrams may represent attributes as bubbles or group them as part of the entity set.
Diagrams differ in their use of specializations, arrows, and explicit cardinalities.
Entities and Entity Sets
Entity: A "thing" or "object" in the real world that is distinguishable from other entities.
Example: A doctor working for a hospital.
May be physical (person, book) or non‐physical (visit, course offering, flight reservation).
Has descriptive properties or attributes.
Entity Set: A set of entities of the same type that share the same attributes.
Example: All doctors of a hospital.
Represented as a rectangle divided into two parts, with the entity set name in the first part.
Singular or Plural Names
Consistency is key: Use either singular or plural names consistently throughout the model.
Object‐Oriented programming recommends using singular names.
Entity Set Attributes
Assigning an attribute to an entity set means the database stores similar information for each entity in the set.
Each entity may have its own value for each attribute.
Represented by their names in the second part of the entity set rectangle.
Example: A Patient entity set may have attributes like name, date of birth, or gender.
Each entity has a value for each attribute.
*Jacob Jones's birthdate is 08/01/2003.
Issues When Handling Attributes
Legal and privacy issues related to some attributes.
Protected health information in the US and other countries (name, address, birth date, Social Security Number).
Check: https://www.hhs.gov/hipaa/for‐professionals/privacy/special‐topics/de‐identification/index.html
Check out: https://en.wikipedia.org/wiki/Hoangv.Amazon.com,_Inc
Check also the code of conduct of the data scientist: http://www.code‐of‐ethics.org/code‐of‐conduct/
Relationships and Relationship Sets
Relationship: An association among several entities.
Example: A patient has a primary doctor.
Relationship Set: A set of relationships of the same type, involving two or more entity sets.
Role: The function an entity plays in a relationship.
Represented as a diamond, with the entity sets connected by lines.
Names of Relationship Sets
Must be meaningful and not repeated, providing a good idea of what is being modeled.
Non-binary Relationship Sets
Usually involve two entity sets, but can involve more.
Example: In a clinical trial, doctors and patients are involved, with each doctor responsible for some patients.
*Nonbinary relationship sets can be complex to understand, so it's better to avoid them.
Relationship Set Attributes
May also comprise descriptive attributes.
Example: Specifying the policy number of a patient in a given insurance company.
Represented by connecting a rectangle with the name of the attribute using a dashed line.
Roles
Implicit when entity sets are distinct; useful for clarification when the same entity set participates multiple times.
Example: The Supervised by relationship set involves the Doctor entity set twice (supervisor and supervisees).
Cardinalities
Represent the number of entities that may be involved in a relationship.
*1 to 1: each visit generates one single bill. Represented using 1.
*1 to N: a doctor may be the primary doctor of several patients.
*M to N: a bill can be paid by one or more payments and a payment can pay several bills.
Reading Cardinalities
Focus on the relationship set and consider how many entities of the entity set may appear in the relation.
Example: In the Primary relationship set, a patient will appear only once, but a doctor may appear multiple times.
Inheritance
Allows an entity set to be specialized into multiple entity sets that share common attributes.
Example: Doctors and patients share attributes but are involved in different relationship sets.
Represented as a triangle connecting entity sets, with the "root" and "children".
Identity: Keys
Uniquely identify an entity in a given entity set using only the identified attributes.
No two entities in the same entity set should have the same value for these attributes.
Key: The set of attributes that uniquely identify an entity in an entity set.
Example of What NOT to do: Avoid using the name of a person as a key.
Acceptable Use: SSN can be used because it is expected to be unique.
Use attributes whose values rarely change.
Types of Keys
Super Key: A collection of one or more attributes whose values together uniquely identify an entity.
Example: SSN, first, middle, and last names of a person.
Candidate Key: Similar to a super key but using only those attributes whose subsets do not form a super key (minimal).
Example: SSN and email are two candidate keys.
Primary Key: The final key used to identify a person.
Example: SSN.
Representing Primary Keys
Underline the attributes that form the primary key.
No Candidate Keys
What happens if there are no candidate keys in the entity sets?
Example: Inability to refer to a bill by its billing date, due date, and amount.
Solution #1: Create an Artificial ID
Assign an id attribute to the entity set and ensure it is unique using an id authority.
Example: Creating an id for bills.
Solution #2: Weak Entity Sets
Use another entity set plus a relationship set to define a key instead of creating an artificial id.
Example: The Visit entity set has a scheduled date, but that is not enough to identify each site.
The Visit is linked to a Patient and is scheduled for only one patient, this way it can be uniquely identified.
Represented using a double line diamond for the relationship set and a dashed line for the key of the weak entity set.
The identifying relationship set is many‐to‐one from the weak entity set to the identifying entity set, and the participation of the weak entity set is total.
Design Decisions
Design decisions during model creation can have future impacts.
Examples:
Creating an artificial id vs. using a weak entity set.
Storing a person's address as a single chunk of text vs. breaking it down into components.
Logical Model
Technology
MySQL will be used to implement our logical model.
Relations
Define relations, each with a unique name and number of attributes.
Example: Patient relation with attributes ssn, firstName, middleName, and lastName.
Specify the primary key using PK.
Relation Instances (Tuples)
Represent data using the relations.
Each row is called a tuple, consisting of an entity of the relation.
Null Values: Indicate missing or unknown values.
Foreign Keys
Connect other relations.
Example: Including the SSN of a doctor in the patient relation to model that a given doctor is the primary doctor of a patient.
Represented using FK and an arrow.
Relation Instances
Use the SSNs of doctors to identify them as primary doctors of patients using foreign keys.
Referential Integrity
Foreign keys are used to maintain referential integrity.
Values appearing in one relation for a set of attributes also appear for another set of attributes in another relation.
Every time the database is updated, referential integrity is checked.
Structured Query Language (SQL)
A programming language designed for managing data in a relational database.
Divided into data definition and data manipulation languages.
Declarative language: define what to do, not how to do it.
Data Definition Language (DDL): CREATE, DROP, ALTER queries.
Data Manipulation Language (DML): INSERT, UPDATE, DELETE, SELECT queries (CRUD operations).
Create and Selecting a Database
CREATE
Example of how to create the Patient relation:
CREATE TABLE Patient ( ssn CHARACTER(9), firstName VARCHAR(75) NOT NULL, middleName VARCHAR(75), lastName VARCHAR(75) NOT NULL, primaryDoctor CHARACTER(9), PRIMARY KEY (ssn), FOREIGN KEY (primaryDoctor) REFERENCES Doctor(ssn) );Relations are also known as tables.
Each attribute (column) has a type, and a "NOT NULL" constraint can be added.
Specify the primary key and foreign key relationships.
Types
Several data types for attributes:
String:
CHARACTER(n): fixed size string of n characters.
VARCHAR(n): variable size string of up to n characters.
Numbers:
INTEGER
FLOAT
Dates:
DATE: for a specific day (09/15/2015).
TIME: for a specific time (1:45:19).
TIMESTAMP: both date and time (09/15/2015 – 1:45:19).
DROP
Use DROP TABLE to remove a relation from our database.
DROP TABLE Patient;
ALTER
Use ALTER TABLE to change the specification of a relation.
Remove an attribute using DROP COLUMN.
Add a new attribute using ADD.
ALTER TABLE Patient DROP COLUMN middleName; ALTER TABLE Patient ADD middleName VARCHAR(50);
INSERT
Use INSERT INTO query to create new tuples in a given relation.
Specify the names of the attributes for referring to the values.
INSERT INTO Patient (ssn, firstName, lastName) VALUES (‘235147854’, ’Sandra’, ’Smith’);
UPDATE
Update a set of tuples in a relation using UPDATE.
Define the values to update and a filtering condition using WHERE.
UPDATE Patient SET firstName = ‘Sarah’, lastName = ‘Morrison’ WHERE ssn = ‘235147854’;
DELETE
Remove tuples from a relation using a DELETE query.
Specify the table and a condition over the tuples to remove.
DELETE FROM Patient WHERE firstName = ‘Sandra’;
SELECT
Read‐only queries.
From Conceptual to Logical
"Strong" Entity Sets
Transform each “strong” entity set into a relation and use the primary keys that have been identified.
Patient PK ssn firstName lastName middleName
Weak Entity Sets
Transform weak entity sets into a relation.
Add the primary keys of the “strong” entity set and a foreign key to the primary key of that “strong” entity set.
Both attributes patient and scheduled form the primary key of Visit.
"Strong" Relationship Sets
Create a new relation for every relationship set.
Add the primary keys of both entity sets to the new relation, including foreign keys.
Add the relationship set attributes, if any.
Optimization
Exceptions to the general rule to make the relational database more efficient by avoiding redundant relations.
Instead of creating a Primary relationship set, add it as an attribute to the Patient relation and its corresponding foreign key.
This only works for one‐to‐one and one‐to‐many relationship sets.
Check if every patient has a primary doctor, otherwise, create patients with a null value in the primaryDoctor attribute.
Roles
Used to name attributes on the resulting relation from the relationship set.
In this case, we use supervisor and supervisee as the names of the attributes of the SupervisedBy relation.
Weak Relationship Sets
Discard weak relationship sets since they are usually redundant.
They are already considered when transforming the weak entity sets they are related to.
Inheritance Strategies
Whole
Top
Bottom
Transform inheritance into relations in a relational database.
Whole Hierarchy
Map the whole hierarchy into relations.
One relation for each set.
Foreign keys for all relations that are not at the top level.
Specific attributes only appear in their corresponding sets.
Top of the Hierarchy
Create a single relation with all attributes from all sets combined.
New patient tuples will have null values for salary.
Bottom of the Hierarchy
Create relations just for the bottom entity sets.
Have all common attributes in the relation.
Repeat all attributes except salary for both relations.
You Have Your Brains!
Apply the previous rules using your intelligence.
Think of solutions if the resulting relations are not viable.
Do not blindly stick to the rules.
Physical Model
How a Database Organizes Data
The physical model contains how a database organizes the data.
Physical Storage Media
Organized from small and fast to large and slow access.
Cache
Main
Flash
Magnetic
Optic
Tape
Cache memory is managed by the computer system hardware.
General‐purpose machine instructions operate in main memory.
Flash memory differs from main memory: stored data is retained even if power is turned off (or fails).
Magnetic disks store the whole database.
In optic disks, data is read by a laser.
Tape storage is used primarily for backup and archival data.
Physical Characteristics of Disks
Organized with platters, each having a flat, circular shape.
Surfaces are covered with a magnetic material.
Drive motor spins the disk at a constant high speed.
Read–write head positioned just above the surface of the platter.
Disk surface is logically divided into tracks, which are subdivided into sectors.
Sector: The smallest unit of information that can be read from or written to the disk.
I/O Requests
A disk I/O request specifies the address on the disk to be referenced (block number).
Block: A logical unit consisting of a fixed number of contiguous sectors.
Data is transferred between disk and main memory in units of blocks.
Mappings
A database is mapped into a number of different files that are maintained by the underlying operating system.
These files reside permanently on disks.
A file is organized logically as a sequence of records.
These records are mapped onto disk blocks.
Miscellaneous
Transactions
A set of operations over a database that are treated as a single operation.
Properties (ACID)
Atomic: Every operation in the group must succeed; if not, all must be undone (rollback).
Consistent: If the data was consistent before the transaction, it must be consistent after it.
Isolated: The effects of a transaction that is in progress are hidden from other transactions.
Durable: The results of a complete transaction are persistent.
How to Implement It Using MySQL?
Transform a conceptual model into a logical model in MySQL.
Using SQL
Create a new database, connect to the database, and use SQL to generate the relations (tables).
How to Insert Data in MySQL?
*To insert data programmatically in the new database using MySQL, you need to follow these steps.
User Management in MySQL
Must have a database user to connect to it.
Give the user privileges to work with the database.
Grant access to that specific user.
CREATE USER ‘USERNAME’@’localhost’ IDENTIFIED BY ‘PASSWORD’; USE hospMng; GRANT ALL ON hospMng.* TO ‘USERNAME’@’localhost’;
Programmatic Access to MySQL (1)
Refer to the MySQL/J JDBC connector.
Use code similar to the one presented in the slide to have such access.
Avoid having the database password embedded in your code.
Connection con = null; PreparedStatement st = null; ResultSet rs = null; String url = “jdbc:mysql://localhost:3306/hospMng”; String user = “USERNAME”; String pwd = “PASSWORD”;
Programmatic Access to MySQL (2)
```java
try {
con = DriverManager.getConnection(url, user, pwd);
st = con.prepareStatement(“SELECT ssn FROM Patient”);
rs = st.executeQuery();
if (rs.hasNext())
System.out.println(rs.getString(“ssn”));
}
```
Programmatic Access to MySQL (3)
```java
catch (SQLException oops) {
System.out.println(“Something went really wrong.”);
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (SQLException oops) {
System.out.println(“Something went really wrong.”);
}
}
```
Transactions (1)
A transaction: a set of one or more statements that is executed as a unit.
Either all of the statements are executed, or none of the statements is executed.
By default, all statements in JDBC have auto commit set to true.
Change that behavior by setting it to false.
try { con.setAutoCommit(false); st = con.prepareStatement(“INSERT INTO “ + “Payment(id, paymentDate, amount, method) ” + “VALUES (?,?,?,?)”); st.setInt(1, 743); st.setString(2, ’09/02/16’); st.setInt(3, 536); st.setString(4, “Check”); st.executeUpdate(); st.close();
Transactions (2)
```java
st = con.prepareStatement(“INSERT INTO IsPaidBy(bill, payment) VALUES (?,?)”);
st.setInt(1, 123);
st.setInt(2, 743)
st.executeUpdate();
st.close();
con.commit();
} catch (SQLException oops) {
…
con.rollback();
…
```
Perform a commit if everything goes smoothly (both inserts will be saved in the database), otherwise we perform a rollback to leave the database as it was before the transaction.
Batch Processing (1)
Modeling Tips
Avoid redundancy as much as you can.
Never use arrays or anything of the sort: all data should be “plain”.
Your queries should easily fit (next unit!).