Advanced Database Systems Course Quiz

Advanced Data Base Systems Course

• Course Objectives:
  • Introduce database concepts and techniques.
  • Focus on database modeling, design, and implementation.
  • Demonstrate advanced topics in database systems.
  • Examples of advanced topics include transactions, concurrency control techniques, joining methods, distributed databases, and database tuning.
• Required Textbooks:
  • Fundamentals of Database system - ALMASRI, NAVATHE - 6th Edition
  • Database system Concepts - SILBERSCHATZ, KORTH, SUNDARSHA - 5th Edition
  • Database systems Practical Approach to Design, Implementation and Management - TOOMAS, CAROLYN, ANESTRACHAN- 2nd Edition

Course Contents

• Unit One:
  • What is a database?
  • Database Management Systems (DBMS).
  • Database vs. DBMS.
  • File Manager vs. DBMS.
• Unit Two:
  • Data Models (ERD) and kinds of DBMS
  • Normalization
• Unit Three:
  • Database Languages
  • Advanced Topics in Database (Lectures + Seminars):
    • Transactions
    • Inner and Outer Join.
    • Concurrency Control Techniques.
    • Database Recovery Techniques.
    • Distributed Databases
    • Temporal Database.
    • Spatial Database.
    • Database Tuning
    • Object Oriented Database (OOD).

What is a Database?

• A database is a collection of data.
• Example databases:
  • Attendance Register (at office reception)
  • Shopping list (in your pocket)
  • Resumes (on HR manager's desk)
  • Bank Accounts (in a bank)
  • ColorNote Data (on your smart phone)
  • Contact list (on your smart phone)

Attendance Register as a Database

• An attendance register contains at least one record for each employee.
• It contains multiple record entries daily, effectively storing data and making it a database.
• This database is manually maintained.

Scroll of Shopping List as a Database

• A shopping list contains data.
• It is a collection of data, and therefore, a database.
• This type of database is not computer-based.

Bunch of Resumes as a Database

• A collection of resumes, for example those of an HR manager, is a database.
• Each resume contains information such as name of candidate, date of birth, qualifications etc.
• Treated as a manually maintained database.

Bank Accounts as a Database

• Banks store customer data (name, account number, balance) in a computer-based database.
• They may also store additional information such as fixed deposit details, interest rates, loan amounts.

ColorNote notes as a Database

• ColorNote is a mobile app.
• Used to store various kinds of notes, including bank details, passport details etc.
• These notes form a database, which is a collection of related data items.

Contact List as a Database

• A contact list, for example on a phone, tablet or PC, represents a database.
• Stores data such as names, phone numbers and email ID of persons.

Summary

• A database is a collection of data.
• Databases can be manual or computerized.
• Attendance register and bank accounts are examples of manual and computerized databases, respectively.

Database Management System (DBMS)

• A DBMS is software used to create, query, manipulate and control databases.
• Short name for DBMS is DBMS.

Database Environment

• Interaction between applications and databases happens through DBMSs.
• Applications interact with DBMS to access database.

Database Creation

• Database creation involves specifying a name for the database and other parameters such as owner, size and growth rate.

Database Querying

• Retrieving required data from the database.
• Examples include retrieving customer names and account numbers with balances over a specified amount.

Database Manipulation

• Adding, modifying, or deleting data.
• SQL commands allow modifications to the data.

Database Control

• Limiting access to data to authorized users.
• Sharing data among users to maintain database integrity based on user privileges.

DBMS Components

• Interfaces between application programs and DBMS (Application Development Interfaces), ISQL(SQL variants), Ad-hoc query tools for data manipulation, Administration tools(LOAD, DUMP, SCHEMA, BACKUP, RESTORE), Communication network and Database Servers that store the data.

Database Vs DBMS

• Database is a collection of data which reside on files.
• Database is not software, while DBMS is software implementing database management functions.

File Managers Vs DBMS

• File management systems manage files without the sophisticated functionalities of DBMS, like data integrity, security, ACID properties.
• Examples include c-ISAM, ObjectTrieve, C-Tree and BerkeleyDB.

File Managers Vs. DBMS Function comparison

• Compared to file managers, retrieval of data in DBMSs is easier.
• DBMSs have better data integrity and security features than file managers.
• DBMSs offer much better tuning capabilities for applications compared to file managers.
• DBMSs offer much better application portability.
• DBMSs offer much better programming productivity advantages over File managers.

Data Models & DBMS

• Data model: Structure and operations to define record structures and field types along with keys etc.
• Include operations like retrieval, addition , modification , deletion. (data manipulation).
• Examples include Network, Hierarchical , Relational and Object-Oriented data models among others.

Relational Data Model

• Has mathematical foundations based on relations.
• Based on the concept of tables and relationships.
• Data is organized in tables with rows (tuples/records) and columns (attributes/fields).
• Relational databases are further categorized as RDBMSs.

Relational Database Management (summary).

• Tables are relations in relational databases.
• Tables are constructed by linking with other shared fields.
• Columns or attributes make up each field, which also comprises the fields.
• Records are rows or tuples.

Relational Data Database- Sample Data & Tables

• Sample tables include Departments, Employees, Resumes, and Projects.
• Tables have corresponding attributes like department's name, employees' name and roles, resumes' contents and projects' details, respectively.

Relational Database Terminology

• General term (vs academic term) for table, column, and row are relation, attribute/field, and tuple/record respectively.

Relational Database Management System Products

• Oracle Database
• MS SQL Server
• Access 2010
• DB2
• Sybase
• MySQL
• PostgreSQL
• SQLite (often used with Android apps).

Normalization

• Background and terminology relating to primary keys and foreign keys.
• Steps of normalization process and its advantages.
• Types of relationships, namely one-to-one, one-to-many and many-to-many.

Primary and Foreign Key Fields

• Primary key field in each table must be unique and cannot contain a null value.
• A primary key must uniquely identify each record in a table.
• A foreign key field must match the primary key of another table.

What is Normalization?

• The goal of normalization is to organize data to eliminate redundancy, ensure data integrity, and avoid data modification issues.
• Normalization involves restructuring database tables by breaking them down into smaller, well-organized tables, linked by relationships, eliminating data redundancy and maintaining data integrity.
• It involves different levels. 3NF (third normal form) is typically sufficient for standard database applications.

First Normal Form (1NF)

• Repeating and duplicate fields should be eliminated.
• Each cell must contain one atomic value.

Second Normal Form (2NF)

• All non-key fields must depend on all components of the entire primary key (not just part of the primary key).

Third Normal Form (3NF)

• No non-key field should depend upon another non-key field.

Relationships

• One-to-one Relationships
• One-to-many Relationships
• Many-to-many Relationships.

Database Languages

• Purpose of database languages: communicate with DBMS to retrieve, add, modify and delete data from the database.
• Database language components: DDL(Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language).

SQL - Structured Query Language

• SQL is the standard language for relational database systems.
• SQL covers DDL, DML, and DCL components.
• International standard for the language.

QUEL - Query Language

• QUEL, a query language from the INGRES and SPREADS systems, is not used as much any more.

QBE - Query By Example

• QBE is a graphical user interface for data definition, retrieval and manipulation.

History of Database systems

• Highlighting key milestones and advances in database systems' evolution.
• Include the various models, types and products that have emerged and been proposed over time.

Test your knowledge on advanced database concepts and techniques covered in the Advanced Database Systems Course. This quiz focuses on topics like database modeling, design, transactions, and concurrency control, ensuring a comprehensive understanding of systems and their applications. Prepare to apply your learning from key textbooks and course materials.