Efficient Data Storage and Retrieval in Databases
Diving into the world of database systems, this content delves into the significance of studying databases despite data processing models. It explores the challenges of storing large volumes of data, the simplicity and specificity of operations, advantages of relational models, and the shift from hierarchical and network structures to table structures. Emphasizing the importance of efficient data storage and retrieval, it highlights the evolution of programming languages and key aspects like reliability, security, and more in managing data effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: HRBB 338C Phone: 845-4259 Email: chen@cse.tamu.edu 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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 popular model (relational model); 7
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; High-level representation of information (E-R Diagram: Chapter 4) 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 use DDL. 20
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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 26
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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 37
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. accountNo balance type 12345 20,520 checking Accounts 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. accountNo balance type 12345 20,520 checking Accounts 23456 1,000,000 savings 34567 -26 savings SELECT accountNo FROM Accounts WHERE type = 'savings' AND balance < 0; CREAT TABLE Accounts { accountNo INT, balance REAL type CHAR(10) } 43
Structured Query Language (SQL) SQL provides both DDL and DML. accountNo balance type 12345 20,520 checking Accounts 23456 1,000,000 savings 34567 -26 savings SELECT accountNo FROM Accounts WHERE type = 'savings' AND balance < 0; CREAT TABLE Accounts { accountNo INT, balance REAL type CHAR(10) } 44 34567 Output:
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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL 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. (ACID) Reliability, security, consistency, currency , , , set ops, joins Data (in disks) SQL Get to this in details later 47
Process of Database Development Description of the database application 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 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 48
Process of Database Development Description of the database application 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 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 49
Process of Database Development Description of the database application 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 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 50
