Podcast
Questions and Answers
Which of the following levels of data abstraction is also known as the physical level?
Which of the following levels of data abstraction is also known as the physical level?
The conceptual level of abstraction is concerned with the physical implementation of the data.
The conceptual level of abstraction is concerned with the physical implementation of the data.
False
What is the responsibility of a Database Administrator (DBA)?
What is the responsibility of a Database Administrator (DBA)?
To manage the database schema, enforce security, perform data analysis, and ensure routine maintenance checks.
The interface between various levels in a database should be well defined to prevent changes from affecting other levels, which is known as _____.
The interface between various levels in a database should be well defined to prevent changes from affecting other levels, which is known as _____.
Signup and view all the answers
Match the following levels of data abstraction with their correct descriptions:
Match the following levels of data abstraction with their correct descriptions:
Signup and view all the answers
What is the difference between schema and instance?
What is the difference between schema and instance?
Signup and view all the answers
Which of the following is NOT a purpose of a database system?
Which of the following is NOT a purpose of a database system?
Signup and view all the answers
Data isolation refers to the ability to access data from multiple files and formats seamlessly.
Data isolation refers to the ability to access data from multiple files and formats seamlessly.
Signup and view all the answers
What is DBMS an abbreviation for?
What is DBMS an abbreviation for?
Signup and view all the answers
The primary language for defining database structure is called _____ Language.
The primary language for defining database structure is called _____ Language.
Signup and view all the answers
Match the database examples with their appropriate categories:
Match the database examples with their appropriate categories:
Signup and view all the answers
Study Notes
Three Level Architecture
- A database system has three levels of architecture: External/View Level, Conceptual/Logical Level, and Internal/Physical Level
- Each level has a specific function and they are connected through mappings
Internal/Physical Level
- Lowest level of data abstraction that deals with physical representation of the database on the computer
- Deals with physical storage of data, including structure of records on disk (files, pages, blocks), indexes, and ordering of records
- Used by database system programmers
- Example of Internal Schema: RECORD EMP with specific lengths and offsets for each field (HEADER, NAME, SALARY, DEPT)
Conceptual/Logical Level
- Deals with logical structure of entire database
- Describes what data are stored in the database, without concern for physical implementation
- Used by DBAs and application programmers
- Example of Conceptual Schema: CREATE TABLE Employee (Name VARCHAR(25), Salary REAL, Dept_Name VARCHAR(10))
External/View Level
- Highest level of abstraction that deals with user’s view
- Provides a view of the database tailored to a user, hiding certain details and presenting data in a useful form
- Used by end users and application programmers
- Example of External Schema: View 1 with Employee data (Name, Address) and View 2 with Employee data (SSN, Name, Address, Salary)
Data Abstraction
- Interfaces between levels and components should be well defined to prevent changes in one part affecting others
- Database users are provided with an abstract view of data, hiding physical storage details
- Enables users to manipulate data without worrying about physical storage
Mapping
- Translates information from one level to the next (External/Conceptual and Conceptual/Internal)
- Provides data independence (physical and logical)
- Physical data independence: changes to internal level don't affect conceptual level
- Logical data independence: conceptual level changes don't affect external levels
Instances & Schema
- Schema: description of database structure (logical structure of database)
- Instance: set of data that fits in description (actual contents of database at a particular point of time)
- One schema can have many instances
- Difference between schema and state: schema is defined at design time, state changes each time data is inserted or updated
Examples
- Roll Number, Name, and Contact data with different instances at time T1, T2, and T3
Database Schema
- Bad design: combining multiple tables into one
- Good design: separate tables for each entity (Student, Course, Marks)
DBA: Database Administrator
- Responsible for central control of data and application programs
- Functions: schema definition and modification, security enforcement and administration, data analysis, and routine maintenance checks
Database Management System (DBMS)
- A collection of interrelated data and a set of programs to access the data
- DBMS contains information about a particular enterprise
- Provides an environment that is both convenient and efficient to use
Database Applications
- Banking: all transactions
- Airlines: reservations, schedules
- Universities: registration, grades
- Sales: customers, products, purchases
- Manufacturing: production, inventory, orders, supply chain
- Human resources: employee records, salaries, tax deductions
Purpose of Database System
- In the early days, database applications were built on top of file systems
- Drawbacks of using file systems to store data:
- Data redundancy and inconsistency
- Difficulty in accessing data
- Data isolation
- Integrity problems
- Atomicity of updates
- Concurrent access by multiple users
- Security problems
- Database systems offer solutions to all the above problems
Examples of Databases
- Web indexes
- Library catalogues
- Medical records
- Bank accounts
- Stock control
- Payroll systems
- Product catalogues
- Telephone directories
- Train timetables
- Airline bookings
- Credit card details
- Student records
- Customer histories
- Stock market prices
- Discussion boards
Database Management Systems
- Allow users to store, update, retrieve, organise, and protect their data
- Examples: Oracle, DB2 (IBM), MS SQL Server, MS Access, Ingres, PostgreSQL, MySQL
Major Components of a Database System
- Data: integrated and shared
- Hardware: disk, CPU, Main Memory, ...
- Software: DBMS
- Users:
- Application programmers
- End users
- Database systems programmer
- Database administrator (DBA)
Functions of the DBMS
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Security and Integrity
- Data Recovery and Concurrency
- Data Dictionary
- Performance
Three Level Architecture
- External Level: user view
- Conceptual/Logical Level: DBA view
- Internal/Physical Level: database system programmer view
Internal/Physical Level
- Deals with physical representation of the database on the computer
- Deals with physical storage of data
- Examples: structure of records on disk, indexes and ordering of records
Conceptual/Logical Level
- Deals with logical structure of entire database
- Describes what data are stored in the database, without concern for physical implementation
- Used by DBAs and application programmers
- Example: CREATE TABLE Employee (Name VARCHAR(25), Salary REAL, Dept_Name VARCHAR(10))
External/View Level
- Provides a view of the database tailored to a user
- Parts of the data may be hidden
- Data is presented in a useful form
- Used by end users and application programmers
Mappings
- Translate information from one level to the next
- Provide data independence
- Types:
- Physical data independence: changes to internal level shouldn’t affect conceptual level
- Logical data independence: conceptual level changes shouldn’t affect external levels
Data Abstraction
- Interfaces between levels and components should be well-defined
- Database users are provided with an abstract view of the data by hiding certain details of how data are physically stored
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
This quiz covers the three-level architecture of a database system, including the External/View Level, Conceptual/Logical Level, and Internal/Physical Level. Understand the functions of each level and how they are connected through mappings.
