Magnetic Hard Disk Mechanism

 Magnetic Hard Disk Mechanism

Hard disk drive interface

Disks usually connected directly to computer system

Disk interface standards families

Parallel ATA (PATA) ( AT Attachment) also known as ATA or IDE is standard interface for IBM computers

SATA (Serial ATA) 

SATA revision 1.0 (1.5 Gbit/s, 150 MB/s, Serial ATA-150)

SATA revision 2.0 (3 Gbit/s, 300 MB/s, Serial ATA-300)

SATA revision 2.5

SATA revision 2.6

SATA revision 3.0 (6 Gbit/s, 600 MB/s, Serial ATA-600)

SATA 3 supports data transfer speeds of up to 6 gigabits/sec

Serial Attached SCSI

SAS Version 3 supports 12 gigabits/sec 

Non-Volatile Memory Express

The NVMe protocol connects SSDs to servers via the PCIe bus and offers much faster performance. NVMe technology has become a key player in today's storage infrastructure.

 Works with PCIe connectors to support lower latency and higher transfer rates  Supports data transfer rates of up to 24 gigabits/sec

Functional dependency

 

Query Language

In simple words, a Language which is used to store and retrieve data from database is known as query language. For example – SQL

There are two types of query language:
1.Procedural Query language
2.Non-procedural query language

1. Procedural Query language:

In procedural query language, user instructs the system to perform a series of operations to produce the desired results. Here users tells what data to be retrieved from database and how to retrieve it.

For example – Let’s take a real world example to understand the procedural language, you are asking your younger brother to make a cup of tea, if you are just telling him to make a tea and not telling the process then it is a non-procedural language, however if you are telling the step by step process like switch on the stove, boil the water, add milk etc. then it is a procedural language.

Relational Algebra:

Relational algebra is a conceptual procedural query language used on relational model.

2. Non-procedural query language:

In Non-procedural query language, user instructs the system to produce the desired result without telling the step by step process. Here users tells what data to be retrieved from database but doesn’t tell how to retrieve it.

Relational Calculus:

Relational calculus is a conceptual non-procedural query language used on relational model.

Note: while describing relational algebra and relational calculus, because they are theoretical mathematical system or query language, they are not the practical implementation, SQL is a practical implementation of relational algebra and relational calculus.

Relational Algebra, Calculus, RDBMS & SQL:

Relational algebra and calculus are the theoretical concepts used on relational model.

RDBMS is a practical implementation of relational model.

SQL is a practical implementation of relational algebra and calculus.

Relational Calculus: Tuple Relational Calculus, Domain Relational Calculus

 

Relational Calculus

• Relational calculus is a non-procedural query language. In the non-procedural query language, the user is concerned with the details of how to obtain the end results.
• The relational calculus tells what to do but never explains how to do.

Types of Relational calculus:

PPT

Relational algebra

 Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator can be either unary or binary. They accept relations as their input and yield relations as their output.

PPT

DBMS: Assignment 3 Relational Algebra

 Link

Last date: 5 October 2020

Upload it on gnomio

Unit 2: Keys

Types of keys in DBMS 

Super Key – A super key is a set of one of more columns (attributes) to uniquely identify rows in a table. Candidate Key – A super key with no redundant attribute is known as candidate key

Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table. 

PPT

Study Material:

10 Different Keys in DBMS

Finding Closure of Attribute Set

Finding Candidate Keys of a Relation

Unit 2: Integrity Constraints

 

Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.

PPT

https://drive.google.com/file/d/12TyGNuG0lgGqG6B95qZgi5LBwagkN9KQ/view?usp=sharing

https://drive.google.com/file/d/12TyGNuG0lgGqG6B95qZgi5LBwagkN9KQ/view?usp=sharing

Unit 2: Relational Model (Introduction)

 Relational Model was proposed by E.F. Codd to model data in the form of relations or tables. After designing the conceptual model of Database using ER diagram, we need to convert the conceptual model in the relational model which can be implemented using any RDMBS languages like Oracle SQL, MySQL etc. So we will see what Relational Model is.

PPT

DBMS

Syllabus
Text Book	Reference Book

---------------------------------------------------------------------------------------------------------------

Introduction: [Basic concepts, Advantages of DBMS over file-processing systems, Data abstraction, Data models and data independence, Components of DBMS and overall structure of DBMS]

 Introduction (PPT) 

Question Bank

Data modeling, entities, attributes, relationships, constraints, Keys, E-R diagrams, and components of the E-R model

Entity-Relationship Model (study Material and PPT)

--------------------------------------------------------------------------------------------------------------

Relational Model: Basic concepts, Attributes and domains, Concept of integrity and referential constraints, Schema diagram, Relational query languages, Relational Algebra and Relational Calculus: Tuple relational and domain relational calculus

• Unit 2: Relational Model Overview

  Unit 2: Integrity Constraints 

  Unit 2: Keys 

  Unit 2: Relational algebra

  Unit 2: Relational Calculus : Tuple Relational calculus, Domain relational calculus 

• Unit 2: Query Languages

• candidate Key Question Bank

• ----------------------------------------------------------------------------------------------------------

• Introduction to SQL, characteristics, advantages, data types, and literals
• DDL, creating, modifying, and deleting tables, Views: Creating, Dropping, Updation using views, DML, Operators, SQL DML queries, SELECT query and clauses. Structured Query Language- II: Set operations, Predicates and joins, Set membership, Tuple variables, Set comparison, Ordering of tuples, Aggregate functions, Nested queries, Database modification using SQL Insert, Update and Delete queries, Dynamic and SQL and concept of stored procedures, Queryby-example

1. Introduction to SQL	ppt
2. Intermediate SQL	ppt
3. Advanced SQL	ppt

-------------------------------------------------------------------------------------------------------

UNIT IV: Relational Database Design and Storage Systems

• Notion of normalized relations
• Functional dependency, decomposition, and properties
• • Functional Dependency 
    Classification of functional Dependencies  
    More questions on functional dependency 
    More questions on functional dependency
  • Questions [Explain trivial dependency? 2 marks
  • What is meant by the closure of functional dependencies? Illustrate with an

    example.
  • What is Functional Dependency? Explain types and properties of FD’s.
  • ]
• Normalization using functional dependency, multi-valued dependency, and join dependency
• Normalizations ( 1 nf,2nf,3nf,bcnf,4nf,5nf) and examples
• Questions:
• What is normalization? Explain its need.
• Explain insertion, deletion and modification anomalies with suitable examples.
• State BCNF. How does it differ from 3NF?
• State 1NF, 2NF & 3NF and explain with examples.

extra questions[What is normalization? What is need for normalization. Define data Anomalies.

Define 1nf.Define 2nf.Define 3 nf. Define BCNF. Define 5nf. List the decomposition properties. Define lossless joins (or) What is non additive property. Explain BCNF with example(or) How to convert a relation into BCNF. Why must multivalued dependencies exist in pairs?nComparison of BCNF and 3NF.Why 4NF in Normal Form is more desirable than BCNF?]

• Storage systems: secondary storage, RAID,
• 1. physical storage in database
  2.magnetic-hard-disk-mechanism
  3. Hard disk drive interface
  4. RAID
• 
  
• file organization :
Click here
• 
  
• Indices, static and dynamic hashing
• click here
• Distinguish between: i) Primary and Secondary indexing. ii) Ordered indexing and hashing.
• 
  
• B-Trees and B+ Trees

Examples and Questions

-------------------------------------------------------------------------------------------------------------

UNIT V: Query Processing and Transaction Management

• Measures of query cost
• Selection operation, sorting, and join operation

click Here

Question [Outline the steps involved in query processing.]

• Transaction concept, components of transaction management

click here

• Concurrency and recovery system
• Concurrency control protocols (timestamps, locking), validation
• click here
• Multiple granularity, deadlock handling
• click here
• 
  
• Crash recovery methods (log-based recovery, shadow-paging)
• Buffer management and
• 
  
• remote backup system
• Click here
• [Question : Write in detail about Remote Backup systems?]

MCQs (Unit 1 to 3)

Syllabus: DBMS

 Course Objectives:

1. Understand database management systems architecture and functioning.
2. Learn the relational model.
3. Use structured query language (SQL) and its syntax, transactions, database recovery, and query optimization techniques.
4. Acquaint with various normalization forms and query processing.
5. Learn indexing methods.

Course Outcomes: After completing the course, students should be able to:

1. Explain the need for database management.
2. Design and implement a database schema for a given problem domain.
3. Normalize a database.
4. Create and query a database using SQL DML/DDL commands, stored procedures, and functions.
5. Declare and enforce integrity constraints on a database.
6. Demonstrate an understanding of indexing methods.

Course Contents: UNIT I: Introduction and Entity-Relationship Model

• Basic concepts, advantages of DBMS over file-processing systems
• Data abstraction, data models, and data independence
• Components and overall structure of DBMS
• Data modeling, entities, attributes, relationships, constraints
• Keys, E-R diagrams, and components of the E-R model

UNIT II: Relational Model and SQL-I

• Basic concepts of the relational model
• Attributes, domains, integrity and referential constraints
• Schema diagram, relational query languages
• Relational algebra and relational calculus
• Tuple and domain relational calculus
• Introduction to SQL, characteristics, advantages, data types, and literals
• DDL, creating, modifying, and deleting tables

UNIT III: Views and SQL-II

• Creating, dropping, and updating views
• DML operators, SQL DML queries, SELECT query and clauses
• Set operations, predicates, and joins
• Set membership, tuple variables, set comparison
• Ordering of tuples, aggregate functions
• Nested queries, database modification using SQL
• Insert, update, and delete queries
• Dynamic SQL and stored procedures
• Query-by-example

UNIT IV: Relational Database Design and Storage Systems

• Notion of normalized relations
• Functional dependency, decomposition, and properties
• Normalization using functional dependency, multi-valued dependency, and join dependency
• Storage systems: secondary storage, RAID, file organization
• Indices, static and dynamic hashing
• B-Trees and B+ Trees

UNIT V: Query Processing and Transaction Management

• Measures of query cost
• Selection operation, sorting, and join operation
• Transaction concept, components of transaction management
• Concurrency and recovery system
• Concurrency control protocols (timestamps, locking), validation
• Multiple granularity, deadlock handling
• Crash recovery methods (log-based recovery, shadow-paging)
• Buffer management and remote backup system

Text Books:

1. Abraham Silberschatz, Henry F. Korth, and S. Sudarshan, "Database System Concepts," McGraw Hill Education, 6th Edition, 2011.
2. Ramez Elmasri and Shamkant B. Navathe, "Fundamental Database Systems," Pearson Education, 7th Edition, 2015.
3. Raghu Ramkrishnan, Johannes Gehrke, "Database Management Systems," McGraw Hill Education, 3rd Edition, 2007.

Reference Books:

1. Carlos Coronel, Steven Morris, "Database Systems: Design Implementation and Management," Cengage Learning Press, 11th Edition, 2014.
2. J. Murach, "Murach's MySQL," Shroff Publication, 2nd Edition, 2016.
3. J. Murach, "Murach's Oracle SQL and PL/SQL: Works with All Versions Through 11g," Shroff Publication, 2008.