1. Fundamental Concepts of Database System
Fundamental Concepts of Database System
- Applications of database technology
- Key definitions
- Elements of a database system
- SQL Overview
Applications of database technology
- Big Data applications (e.g., Walmart)
- Storage and retrieval of traditional numeric and alphanumeric data in an inventory application
- Multimedia applications (e.g., YouTube, Spotify)
- Biometric applications (e.g., fingerprints, retina scans)
- Wearable applications (e.g., FitBit, Apple Watch)
- Geographical information systems (GIS) applications (e.g., Google Maps)
- Sensor applications (e.g., nuclear reactor)
- Internet of Things (IoT) applications
- Database is a collection of interrelated data, which
- Is logically coherent with some inherent meaning
- Represent some aspects of (a subset of) the real word: Universe of Discourse (UoD)
- Has an intended group of users and applications
Database Management System (DBMS)
- A database management system (DBMS) is a software package used to define, create, use, and maintain a database.
- E.g. Microsoft SQL Server, MySQL, MS-Access, PostgreSQL
- Popular DBMS vendors are Oracle, Microsoft, and IBM.
- MySQL is a well-known open-source DBMS.
- The combination of a DBMS and a database is often called a database system.
- Database + DBMS = database system
Elements of a Database System
- Database model or database schema provides the description of the database data at different levels of detail.
- It specifies the various data items, their characteristics, and relationships, constraints, storage details, etc.
- Specified during database design and not expected to change too frequently.
- Stored in the catalog
- Database state represents the data in the database at a particular moment.
- Begins with an “initial” state
- Also called the current set of instance
- Typically changes on an ongoing basis
- A database model comprises different data models, each describing the data from different perspectives
- A data model provides a clear and unambiguous description of the data items, their relationships, and various data constraints from a particular perspective
A conceptual data model
- Provides a high-level description of the data items (e.g Student, Course, Sections) with their characteristics(e.g StudID, Name,..) and relationships (e.g. STUDENT take Sections)
- Communication instrument between information architect and business user
- Should be implementation-independent, user-friendly, and close to how the business user perceives the data
- Usually represented using an enhanced-entity relationship (EER) model, or an object-oriented model e.g., UML
Logical data model
A logical data model is a translation or mapping of the conceptual data model toward a specific implementation environment
Can be a hierarchical, CODASYL, relational, object-oriented, extended relational, XML, or NoSQL model
Internal data model
- Represents the data’s physical storage details.
- It clearly describes :
- which data are stored where,
- in what format,
- which indexes are provided to speed up retrieval
- Heart of the DBMS
- Contains the data definitions, or metadata, of your database application
- Stores the definitions of the views, logical and internal data models, and synchronizes these three data models to make sure their consistency is guaranteed
- Information architect designs the conceptual data model
- Closely interacts with the business user
- Database designer translates the conceptual data model into a logical and internal data model
- Database administrator (DBA) is responsible for the implementation and monitoring of the database
- Application developer develops database applications in a programming language such as Java or Python
- Business user will run these applications to perform specific database operations
- Data Definition Language (DDL) is used by the DBA to express the database’s external, logical, and internal data models
Definitions are stored in the catalog
- Data Manipulation Language (DML) is used to retrieve, insert, delete, and modify data
DML statements can be embedded in a programming language, or entered interactively through a front-end querying tool
- Structured Query Language (SQL) offers both DDL and DML statements for relational database systems
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulates databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- execute queries against a database
- retrieve data from a database
- insert records in a database
- update records in a database
- delete records from a database
- create new databases
- create new tables in a database
- create stored procedures in a database
- create views in a database
- set permissions on tables, procedures, and views
- SQL Commands can be grouped into five major categories depending on their functionality.
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Transaction Control Language (TCL)
- Data Control Language (DCL)
1. Data Definition Language (DDL)
- These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes, etc.
- In this category we have CREATE, ALTER, DROP, and TRUNCATE commands.
2. Data Manipulation Language (DML)
- These SQL commands are used to store, modify, and delete data from database tables.
- In this category, we have INSERT, UPDATE, and DELETE commands.
3. Data Query Language (DQL)
- These SQL commands are used to fetch/retrieve data from database tables.
- In this category, we have only SELECT command.
4. Transaction Control Language (TCL)
- These SQL commands are used to handle changes that affect the data in the database.
- Basically, we use these commands within the transaction or to make a stable point during changes in the database at which we can roll back the database state if required.
- In this category, we have SAVEPOINT, ROLLBACK, and COMMIT commands.
5. Data Control Language (DCL)
- These SQL commands are used to implement security on database objects like table, view, stored procedure, etc.
- In this category, we have GRANT and REVOKE commands.