CSCE-608 Database SystemsSpring 2024 Instructor: Jianer Chen Office:

CSCE-608 Database SystemsSpring 2024 Instructor: Jianer Chen Office: HRBB 338C Phone: 845-4259 Email: [email protected] Notes 2: Relational Database

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 2

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 3

How Should Data be Stored? Large volume: cannot fit in memory, thus in general stored in disks; 4

How Should Data be Stored? Large volume: cannot fit in memory, thus in general stored in disks; Regular files are not organized enough; 5

How Should Data be Stored? Large volume: cannot fit in memory, thus in general stored in disks; Regular files are not organized enough; Hierarchical structure and network structure were considered, but became less popular; 6

How Should Data be Stored? Large volume: cannot fit in memory, thus in general stored in disks; Regular files are not organized enough; Hierarchical structure and network structure were considered, but became less popular; Table structures (2-dimensional arrays) are currently the most 7 popular model (relational model);

How Should Data be Stored? How can everything be given by tables? 8

How Should Data be Stored? How can everything be given by tables? Well, information consists of objects/items and relationships among items; 9

How Should Data be Stored? How can everything be given by tables? Well, information consists of objects/items and relationships among items; An item has certain properties, which can be listed in a row. Thus, a collection of similar items can be given as a table, one row for an item; 10

How Should Data be Stored? How can everything be given by tables? Well, information consists of objects/items and relationships among items; An item has certain properties, which can be listed in a row. Thus, a collection of similar items can be given as a table, one row for an item; A relationship can be given by listing each item group that satisfies the relationship; 11

How Should Data be Stored? How can everything be given by tables? Well, information consists of objects/items and relationships among items; An item has certain properties, which can be listed in a row. Thus, a collection of similar items can be given as a table, one row for an item; A relationship can be given by listing each item group that satisfies the relationship; 12

Table structures (terminologies) Information is stored in tables ( relations); Each column of a table is an attribute, with an attribute name; 13

Table structures (terminologies) Information is stored in tables ( relations); Each column of a table is an attribute, with an attribute name; A database is a collection of relations; 14

Table structures (terminologies) Information is stored in tables ( relations); Each column of a table is an attribute, with an attribute name; A database is a collection of relations; An example table (Account): Account No. Balance Type 12345 20,520 checking 23456 1,000,000 CD 34567 26 savings 45678 7,023 checking 15

Table structures (terminologies) A relation schema gives the name of the relation and its attributes; 16

Table structures (terminologies) A relation schema gives the name of the relation and its attributes; A database schema gives the schemas for all relations in the database; 17

Table structures (terminologies) A relation schema gives the name of the relation and its attributes; A database schema gives the schemas for all relations in the database; Schemas affects how the data is stored; 18

Table structures (terminologies) A relation schema gives the name of the relation and its attributes; A database schema gives the schemas for all relations in the database; Schemas affects how the data is stored; Database schemas are given and altered using database definition language (DDL); 19

Table structures (terminologies) A relation schema gives the name of the relation and its attributes; A database schema gives the schemas for all relations in the database; Schemas affects how the data is stored; Database schemas are given and altered using database definition language (DDL); Only database administrator can use20

What is a good DB schema? 21

What is a good DB schema? What is a good table structure? Fat or thin? 22

What is a good DB schema? What is a good table structure? Fat or thin? More important: how can tables guarantee information consistency? 23

What is a good DB schema? What is a good table structure? Fat or thin? More important: how can tables guarantee information consistency? There is a beautiful theory (functional dependency) for constructing a good database schema (Chapter 3). 24

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 25

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 26

What are the operations? 27

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in 28

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in * identify relations that are related to the queried information; 29

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in * identify relations that are related to the queried information; * search/organize the relations and collect the related information; 30

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in * identify relations that are related to the queried information; * search/organize the relations and collect the related information; * edit and output the queried information. 31

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in * identify relations that are related to the queried information; * search/organize the relations and collect the related information; * edit and output the queried information. Less often: modifications on relations; 32

What are the operations? Most DB operations are queries, e.g., “what is the balance of the account 12345?” which is involved in * identify relations that are related to the queried information; * search/organize the relations and collect the related information; * edit and output the queried information. Less often: modifications on relations; These operations are given by data manipulation language (DML) 33

What are the operations? The DML operations on relations can be implemented by the basic operations: * projection π and selection σ * set operations * joins * renaming ρ 34

What are the operations? The DML operations on relations can be implemented by the basic operations: * projection π and selection σ * set operations * joins * renaming ρ There is a beautiful (mathematical) theory (relational algebra) that sets the foundation for these basic operations (Chapters 2 & 5). 35

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 36

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 37

Structured Query Language (SQL)

Structured Query Language (SQL) SQL provides both DDL and DML.

Structured Query Language (SQL) SQL provides both DDL and DML. CREAT TABLE Accounts { accountNo INT, balance REAL ， type CHAR(10) } 40

Structured Query Language (SQL) SQL provides both DDL and DML. accountNo balance type Accounts CREAT TABLE Accounts { accountNo INT, balance REAL ， type CHAR(10) } 41

Structured Query Language (SQL) SQL provides both DDL and DML. Accounts accountNo balance type 12345 20,520 checking 23456 1,000,000 savings 34567 -26 savings CREAT TABLE Accounts { accountNo INT, balance REAL ， type CHAR(10) } 42

Structured Query Language (SQL) SQL provides both DDL and DML. Accounts accountNo balance type 12345 20,520 checking 23456 1,000,000 savings 34567 -26 savings CREAT TABLE Accounts { SELECT accountNo FROM Accounts accountNo INT, WHERE type 'savings' balance REAL ， AND balance 0; type CHAR(10) } 43

Structured Query Language (SQL) SQL provides both DDL and DML. Accounts accountNo balance type 12345 20,520 checking 23456 1,000,000 savings 34567 -26 savings CREAT TABLE Accounts { SELECT accountNo FROM Accounts accountNo INT, WHERE type 'savings' balance REAL ， AND balance 0; type CHAR(10) } Output: 34567 44

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 45

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) 46

Database is just another model of information processing Then why study DB? Much more data, regular data techs would be very inefficient: How should data be stored? Operations are simpler & more specific: How do we take advantage of it? New programming languages for the above. π, σ, ρ, set ops, joins SQL Data (in disks) Get to this in details later 47

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 48

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 49

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 50

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 51

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 52

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 53

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 54

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 55

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 56

Process of Database Development Description of the database application Chapter 4 High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 3 Relation normalization Developing database application user interface Chapter 2 Defining database schema (using DDL) Testing Chapters 6-8 Chapter 9 57